SwiftySQL 1.0.4

SwiftySQL 1.0.4

TestsTested
LangLanguage SwiftSwift
License MIT
ReleasedLast Release Oct 2016
SPMSupports SPM

Maintained by Yongha Yoo.


Downloads

Total111
Week0
Month5

Installs

Apps38
Test Targets1
powered by Segment

GitHub

Stars17
Watchers4
Forks1
Issues0
Contributors1
Pull Requests0

Code

Files44
LOCLines of Code 1,873


SwiftySQL 1.0.4

  • By
  • Yongha Yoo

SwiftySQL

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.

SwiftSQL 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

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")
  
    
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('%' || ?)