Skip to content

inkyfox/SwiftySQL

master
Switch branches/tags

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?
Code

Latest commit

 

Git stats

Files

Permalink
Failed to load latest commit information.
Type
Name
Latest commit message
Commit time
 
 
 
 
db
 
 
 
 
 
 
 
 
 
 
 
 

SwiftySQL

======================================

Swift Travis CI platforms Version Carthage compatible Swift Package Manager compatible

Write your SQL in Swift

SwiftySQL is the easiest way to write SQL in Swift:

  • Minimize SQL String literals.
  • Use Swift variables for table and column names with auto completion and syntax highlighting.
  • Use Swift operators and expressions for SQL expressions.

SwiftySQL does not provide the ORM-like feature, just builds SQL statement strings.

For example, you can write Swift codes:

SQL.select(s.name)
  .from(s)
  .where(s.year >= 3 
         && s.id < 100)

to generate SQL string:

SELECT s.name 
FROM   student AS s 
WHERE  s.year >= 3 
       AND s.id < 100

More complex SQL:

Swift SQL
SQL.select(s.name, 
           s.birth)
  .from(s, a)
  .where(s.id == a.studentID)
  .orderBy(s.name.asc) 
SELECT s.name,
       s.birth
FROM   student AS s,
       attending AS a
WHERE  s = a.student_id
ORDER  BY s.name ASC 

Even more complex SQL:

Swift SQL
SQL.select(s.name,
           when(l.name.isNotNull,
                then: l.name)
            .else("N/A"),
           when(t.name.isNotNull,
                then: t.name)
            .else("N/A")
           )
  .from(s
    .leftJoin(a,
              on: s.id == a.studentID)
    .leftJoin(l, 
              on: l.id == a.lectureID)
    .leftJoin(t, 
              on: t.id == l.teatureID)
  )
  .where(s.year >= 2 
    && s.year <= 3
    && (t.office.hasPrefix("A")
        || t.office.isNull)
  )
  .orderBy(s.name.asc) 
SELECT s.name,
       CASE 
         WHEN l.name NOTNULL THEN l.name 
         ELSE 'N/A' 
       END,
       CASE 
         WHEN t.name NOTNULL THEN t.name 
         ELSE 'N/A' 
       END
FROM   student AS s
       LEFT JOIN attending AS a
              ON s.id = a.student_id
       LEFT JOIN lecture AS l
              ON l.id = a.lecture_id
       LEFT JOIN teature AS t
              ON t.id = l.teature_id
WHERE  s.year >= 2
       AND s.year <= 3
       AND ( t.office LIKE 'A%'
             OR t.office ISNULL )
ORDER  BY s.name ASC 

Requirements

  • iOS 8.0+ | macOS 10.10+ | tvOS 9.0+ | watchOS 2.0+
  • Xcode 8

Installation

CocoaPods

# Podfile
use_frameworks!

target 'YOUR_TARGET_NAME' do
    pod 'SwiftySQL'
end

Replace YOUR_TARGET_NAME and then, in the Podfile directory, type:

$ pod install

Carthage

Add this to Cartfile

github "inkyfox/SwiftySQL"
$ carthage update

Swift Package Manager

Create a Package.swift file.

import PackageDescription

let package = Package(
    name: "TestProject",
    targets: [],
    dependencies: [
        .Package(url: "https://github.com/inkyfox/SwiftySQL.git")
    ]
)
$ swift build

Usages

All public types of SwiftySQL comform SQLStringConvertible which privides var description: String that returns a raw query string and var debugDescription: String of a formatted (with indentation) query string.

SELECT Statement

Swift SQL
SQL.select() 
SELECT * 
SQL.select(1, "text", SQLHex(0x16)) 
SELECT 1, 'text', 0x16 
SQL.select()
  .from(s) 
SELECT *
FROM   student AS s 
SQL.select(from: s) 
    
SELECT *
FROM   student AS s 
SQL.select()
  .from(s, a) 
SELECT *
FROM   student AS s,
       attending AS a 
SQL.select(s.name,
           s.birth)
  .from(s) 
SELECT s.name,
       s.birth
FROM   student AS s 
SQL.select(s.name,
           s.birth,
           a.lectureID)
  .from(s, a)
  .where(s.id == a.studentID)
  .groupBy(s.year, s.birth)
  .having(SQL.sum(s.year) < 4)
  .orderBy(s.name.asc, s.birth.desc)
  .limit(100, offset: 40) 
SELECT s.name,
       s.birth,
       a.lecture_id
FROM   student AS s,
       attending AS a
WHERE  s.id = a.student_id
GROUP  BY s.year,
          s.birth
HAVING SUM(s.year) < 4
ORDER  BY s.name ASC,
          s.birth DESC
LIMIT  100, 40 

INSERT Statement

Swift SQL
SQL.insert(into: s) 
INSERT INTO student
DEFAULT VALUES 
SQL.insert(into: s)
  .columns(s.id, s.name)
  .values(10, "Yongha")
    
INSERT INTO student
            ( id, name )
VALUES      ( 10,
              'Yongha' ) 
SQL.replace(into: s)
  .columns(s.id, s.name)
  .values(10, "Yongha")
    
REPLACE INTO student
             ( id, name )
VALUES       ( 10,
               'Yongha' ) 
SQL.insert(or: .replace, into: s)
  .columns(s.id, s.name)
  .values(10, "Yongha")
    
INSERT OR REPLACE INTO student
                  ( id, name )
VALUES            ( 10,
                    'Yongha' ) 
SQL.insert(or: .replace, into: s)
  .columns(s.id, s.name)
  .values(.prepared)
    
INSERT INTO student
            ( id, name )
VALUES      ( ?, 
              ? ) 
SQL.insert(into: s)
  .columns(s.id, s.name)
  .select(SQL.select(100, "inkyfox"))
    
INSERT INTO student
            ( id, name )
SELECT 100,
       'inkyfox' 
SQL.insert(into: s)
.columns(s.id, s.name)
.values(10, "Yongha")
.values(20, "Soyul")
.values(100, "inkyfox")
</pre></td>

  
INSERT INTO student
            ( id, name )
VALUES      ( 10,
              'Yongha' ),
            ( 20,
              'Soyul' ),
            ( 100,
              'inkyfox' ) 

UPDATE Statement

Swift SQL
SQL.update(s)
  .set(s.year, 4) 
UPDATE student
SET    year = 4 
SQL.update(s)
  .set(s.year, 4)
  .where(s.id == 10) 
UPDATE student
SET    year = 4
WHERE  id = 10 
SQL.update(s)
  .set(s.name, "Yongha")
  .set(s.year, 2)
  .where(s.id == 10) 
UPDATE student
SET    name = 'Yongha',
       year = 2
WHERE  id = 10 
SQL.update(s)
  .set([s.name, s.year],
       ["Yongha", 100])
  .where(s.id == 10)
    
UPDATE student
SET    ( name, year ) =
         ( 'Yongha',
           100 )
WHERE  id = 10 
SQL.update(s)
  .set([s.name, s.year],
       SQL.select(s.name, s.year)
          .from(s)
          .where(s.id == 20))
  .where(s.id == 10)
    
UPDATE student
SET    ( name, year ) =
         ( SELECT s.name,
                  s.year
           FROM   student AS s
           WHERE  s.id = 20 )
WHERE  id = 10 

DELETE Statement

Swift SQL
SQL.delete(from: s) 
DELETE FROM student 
SQL.delete(from: s)
  .where(s.id == 10) 
DELETE FROM student
WHERE  id = 10 

Native Types & Literals

Swift SQL
SQL.select(1,
           1.0, 
           "text", 
           SQLHex(0x1024),
           SQL.null) 
SELECT 1,
       1.0,
       'text',
       0x1024,
       NULL 

Unary Operators

Swift SQL
s.id == a.studentID 
s.id = a.student_id 
!(s.id == a.studentID) 
NOT (s.id = a.student_id) 
!s.name.hasPrefix("Yoo") 
NOT (s.name LIKE 'Yoo%') 
-s.year 
-s.year 
-SQL.select(s.year)
  .from(s)
  .limit(1) 
-( SELECT s.year
   FROM   student AS s
   LIMIT  1 ) 
~SQLHex(0x12) 
~0x12 
s.birth.isNull 
s.birth ISNULL 
s.birth.isNotNull 
s.birth ISNotNULL 
s.id.is(a.studentID) 
s.id IS a.student_id 
s.id.isNot(a.studentID) 
s.id IS NOT a.student_id 

Arithmetic Operators

Swift SQL
s.year + 0.5 
s.year + 0.5 
100 + s.grade 
100 + s.year 
s.year - 2 
s.year - 2 
s.year * 2 
s.year * 2 
s.year / 2 
s.year / 2 
s.year % 2 
s.year % 2 
s.year & SQLHex(0x1012) 
s.year & 0x1012 
s.year | SQLHex(0x1012) 
s.year | 0x1012 
s.year << 2 
s.year << 2 
s.year >> 2 
s.year >> 2 

Comparision Operators

Swift SQL
s.id + 100 < a.studentID 
|| s.id != 50 
s.id + 100 < a.student_id
OR s.id <> 50 
s.year <= 2 
s.year <= 2 

EXISTS, BETWEEN and IN

Swift SQL
exists(SQL.select()
  .from(s)
  .where(s.year >= 3) 
EXISTS ( SELECT * 
         FROM student AS s
         WHERE s.year >= 3 ) 
notExists(SQL.select()
  .from(s)
  .where(s.year >= 3) 
NOT EXISTS ( SELECT * 
             FROM student AS s
             WHERE s.year >= 3 ) 
l.id.between(1, and: 100) 
l.id BETWEEN 1 AND 100 
l.id.notBetween(1, and: 100) 
l.id NOT BETWEEN 1 AND 100 
l.category.between("A", and: "F") 
l.category BETWEEN 'A' AND 'F' 
s.name.in("Steve",
          "Bill",
          "Mark") 
s.name IN ( 'Steve',
            'Bill',
            'Mark' ) 
s.name.notIn("Steve",
             "Bill",
             "Mark") 
s.name NOT IN ( 'Steve',
                'Bill',
                'Mark' ) 
s.id.in(SQL.select(a.studentID)
  .from(a)
  .where(a.lectureID == 1024) 
s.id IN ( SELECT a.student_id
          FROM   attending AS a
          WHERE  a.lecture_id = 1024 ) 

Logical Operators

Swift SQL
s.name == "Yongha"
  && s.id > 100
  && s.year <= 3
  && !s.name.hasSuffix(" Jack")
  && exists(SQL.select()
              .from(a)
              .where(a.studentID == s.id))
  && notExists(SQL.select()
                 .from(a)
                 .where(a.studentID == s.id
                        && a.lectureID == 9))
  && s.id + 30 < 200 
s.name = 'Yongha'
AND s.id > 100
AND s.year <= 3
AND NOT ( s.name LIKE '% Jack' )
AND EXISTS ( SELECT *
             FROM   attending AS a
             WHERE  a.student_id = s.id )
AND NOT EXISTS ( SELECT *
                 FROM   attending AS a
                 WHERE  a.student_id = s.id
                        AND a.lecture_id = 9 )
AND s.id + 30 < 200 
s.name == "Yongha"
  && (s.id > 100 
      || s.id < 70)
  && s.year * 2 <= s.id
  && (s.name.hasPrefix("A") 
      || s.name.hasPrefix("B"))
  || s.name.contains("Jones") 
s.name = 'Yongha'
AND ( s.id > 100
      OR s.id < 70 )
AND s.year * 2 <= s.id
AND ( s.name LIKE 'A%'
      OR s.name LIKE 'B%' )
OR s.name LIKE '%Jones%' 

CASE

Swift SQL
when(s.id <= 100, then: 100) 
CASE 
  WHEN s.id <= 100 THEN 100 
END 
when(s.id <= 100, then: 100)
.else(200)
CASE 
  WHEN s.id <= 100 THEN 100
  ELSE 200
END 
when(s.id <= 100 
  || s.year == 4, then: s.name)
  .else(s.name.concat(" *")) 
CASE 
  WHEN s.id <= 100
       OR s.year = 4 THEN s.name 
  ELSE s.name || ' *' 
END 
when(s.id <= 100, then: 100)
.when(s.id <= 200, then: 200)
.when(s.id <= 300, then: 300)
.else(400)
CASE 
  WHEN s.id <= 100 THEN 100 
  WHEN s.id <= 200 THEN 200 
  WHEN s.id <= 300 THEN 300 
  ELSE 400 
END 

Text Concatnation

Swift SQL
"Mrs.".concat(s.name).concat(s.year) 
'Mrs.' || s.name || s.year 

Text Matching

Swift SQL
s.name.like("Y%") 
s.name LIKE 'Y%' 
s.name.like("Y%", escape: "-") 
s.name LIKE 'Y%' ESCAPE '-' 
s.name.notLike("Y%") 
s.name NOT LIKE 'Y%' 
s.name.notLike("Y%", escape: "-") 
s.name NOT LIKE 'Y%' ESCAPE '-' 
s.name.contains("o") 
s.name LIKE '%o%' 
s.name.hasPrefix("Indy") 
s.name LIKE 'Indy%' 
s.name.hasSuffix("Jones") 
s.name LIKE '%Jones' 
s.name.likeIgnoreCase("Y%") 
UPPER(s.name) LIKE UPPER('Y%') 
s.name.notLikeIgnoreCase("Y%") 
UPPER(s.name) NOT LIKE UPPER('Y%') 
s.name.containsIgnoreCase("o") 
UPPER(s.name) LIKE UPPER('%o%') 
s.name.hasPrefixIgnoreCase("Indy") 
UPPER(s.name) LIKE UPPER('Indy%') 
s.name.hasSuffixIgnoreCase("Jones") 
UPPER(s.name) LIKE UPPER('%Jones') 

Functions

Swift SQL
SQLFunc("func") 
func() 
SQLFunc("func", 
        args: 1, "text", s.year)) 
        
func(1,
     'text',
     s.year) 
SQL.count(.all) 
COUNT(*) 
SQL.sum(l.hours) 
SUM(l.hours) 
SQL.length(s.name) 
LENGTH(s.name) 

Common function templete methods are provided: SQL.count(), SQL.avg(), SQL.max(), SQL.min(), SQL.sum(), SQL.total(), SQL.abs(), SQL.length(), SQL.upper(), SQL.lower()

Alias

Swift SQL
SQLAlias(SQL.select().from(s.table),
         alias: "sub") 
( SELECT *
  FROM   student ) AS sub 
SQLAlias(s.name, alias: "name") 
s.name AS name 
s.as("tbl_alias") 
student AS tbl_alias 
(s.year * 3).as("col_alias") 
( s.year * 3 ) AS col_alias 
SQLColumn(table: "tbl_alias", column: "name") 
tbl_alias.name 

Prepared Statement

Swift SQL
s.year == .prepared 
s.year = ? 
s.year + .prepared 
s.year + ? 
s.name.like(.prepared) 
s.name LIKE ? 
s.name.containsIgnoreCase(.prepared) 
UPPER(s.name) LIKE UPPER('%' || ? || '%') 
s.name.hasPrefixIgnoreCase(.prepared) 
UPPER(s.name) LIKE UPPER(? || '%') 
s.name.hasSuffixIgnoreCase(.prepared) 
UPPER(s.name) LIKE UPPER('%' || ?) 

About

Easy and safe way to write SQL in Swift

Resources

License

Stars

Watchers

Forks

Packages

No packages published

Languages