SwiftySQL 1.0.4

SwiftySQL 1.0.4

×

测试已测试✓
语言语言 SwiftSwift
许可证 MIT
发布最近发布2016年10月
SPM支持 SPM✓

由 Yongha Yoo 维护。

SwiftySQL 1.0.4

  • 由
  • Yongha Yoo

SwiftySQL

在 Swift 中编写 SQL

SwiftySQL 是在 Swift 中编写 SQL的最简单方式

  • 最小化 SQL 字符串字面量。
  • 使用具有 自动完成 和 语法高亮 的 Swift 变量 为表名和列名命名。
  • 使用 Swift 运算符 和表达式编写 SQL 表达式。

SwiftSQL 不提供类似 ORM 的功能，仅构建 SQL 语句字符串。

例如，您可以编写 Swift 代码

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

以生成 SQL 字符串

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

更复杂的 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

甚至更复杂的 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

需求

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

安装

Swift Package Manager

创建一个 Package.swift 文件。

import PackageDescription

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

$ swift build

用法

SwiftySQL 的所有公共类型都符合 SQLStringConvertible 协议，该协议提供了一个返回原始查询字符串的 var description: String，以及一个返回格式化的（带缩进的）查询字符串的 var debugDescription: String。

SELECT 语句

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 语句

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 语句

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 语句

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

本地类型和字面量

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

一元运算符

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

算术运算符

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

比较运算符

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和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 )

逻辑运算符

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

文本连接

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

文本匹配

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')

函数

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)

提供了常用的函数模板方法：SQL.count()、SQL.avg()、SQL.max()、SQL.min()、SQL.sum()、SQL.total()、SQL.abs()、SQL.length()、SQL.upper()、SQL.lower()

别名

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

预处理语句

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