SwiftySQL 1.0.4

SwiftySQL 1.0.4

×

TestsTested ✓
LangLanguage SwiftSwift
License MIT
ReleasedLast Release Oct 2016
SPMSupports SPM ✓

Maintained by Yongha Yoo.

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