Releases: SwifQL/SwifQL
🐼 Order by random using new `SwifQLHybridOperator` by @tierracero #53
This allows to use ORDER BY random() operator with one handler that adapts to the proper syntax of psql and mysql
e.g. .orderBy(.random) will be processed as: ORDER BY .rand() in MySQL, and ORDER BY .random() in Postgres
by @tierracero #53
Contributors
Assets 2
👨🔧Implement SwifQLNull
By default when you compare SwifQLable with nil it uses IS NULL
"hello" == nil
// produce: 'hello' IS NULLto reach exact = NULL use this way
"hello" == SwifQLNull
// produce: 'hello' = NULL⚡️Conform `Operator` to `SwifQLable`
So now you will be able to use operators right in query like this
SwifQL.select(Operator.null)Implement `KeypathEncodable`
In case if you use @column names with underscores but variable names are in camelCase you may experience problems when while encoding fetched data you're getting names with underscores.
To solve this problem now we have KeyPathEncodable protocol.
Simply conform your table to KeyPathEncodable and it will always be encoded with property names the same as variable names declared in your model.
Examples
To get as-is
final class User: Table {
@Column("id")
var id: Int
@Column("first_name")
var firstName: String
}this will be encoded as
{
"id": 1,
"first_name": "John"
}To get with variable names
final class User: Table, KeyPathEncodable {
@Column("id")
var id: Int
@Column("first_name")
var firstName: String
}this will be encoded as
{
"id": 1,
"firstName": "John"
}Implement Column, Alias, Table, and builders
Breaking changes
SwifQLAlias renamed to TableAlias
Now we're able to build table models
final class User: Table {
@Column("id")
var id: UUID
@Column("email")
var email: String
}and access table columns through keyPath like this \User.$id and \User.$email
New @Alias property wrapper
struct Result: Aliasable {
@Alias("anything") var emailAddress: String
}
let query = SwifQL.select(\User.$email => \Result.$emailAddress).from(User.table)
// SELECT "User"."email" as "anything" FROM "User"Implement schema support 🍾
Breaking change
Aliases syntax has been changed
was
let u = User.as("u")
u~\.$idbecame
let u = User.as("u")
u.$idSchema
schema for
postgresis the same as database formysql.
How to declare some schema
struct Deleted: Schemable {
static var schemaName: String { "deleted" }
}or right into your model (example for Bridges)
final class DeletedUser: Table, Schemable {
static var schemaName: String { "deleted" }
static var tableName: String { "user" }
@Column("id")
var id: UUID
@Column("email")
var email: String
}Usage examples
// if `User` model conforms to `Schemable` it will use `public` schema all the time
\User.$id // result: "public"."user"."id"
// otherwise it will be printed simply without any schema
\User.$id // result: "user"."id"
// `DeletedUser` model conforms to `Schemable` and has custom schema name
\DeletedUser.$id // result: "deleted"."user"."id"
// Alternatively we can wrap any model to any schema
User.inSchema("deleted").$id // result: "deleted"."user"."id"
// also we can use aliases with tables with schemas
DeletedUser.as("du") // result: "deleted"."user" as "du"
// or
User.inSchema("deleted").as("du") // result: "deleted"."user" as "du"New alias features
let u = User.as("u")
// to reach \User.$id we now can simply call
u.$id // result: "u"."id"
// the same for aliases with schemas
// declare aliases easily
let hu = User.inSchema("hello").as("hu")
// or even
struct HelloSchema: Schemable {
static var schemaName: String { "hello" }
}
let hu = User.inSchema(HelloSchema.self).as("hu")
// use alias as table
hu.table // result: "hello"."user" as "hu"
// or simply call its columns
hu.$id // result: "hu"."id"Custom aliases for subqueries
let u = SwifQLAlias("u")
let subQueryWithAlias = |SwifQL.select(User.table.*).from(User.table)| => u
// result: (SELECT "hello"."user".* FROM "hello"."user") as "u"
// and then also use its paths easily
u.id // result: "u"."id"
// even with any fantasy columns
u.heeeeey // result: "u"."heeeeey"Add UNION and WITH (#19)
union functionality
let table1 = Table("Table1")
let table2 = Table("Table2")
let table3 = Table("Table3")
let sql = Union(
SwifQL.select(table1.*).from(table1),
SwifQL.select(table2.*).from(table2),
SwifQL.select(table3.*).from(table3)
)will give
(SELECT "Table1".* FROM "Table1")
UNION
(SELECT "Table2".* FROM "Table2")
UNION
(SELECT "Table3".* FROM "Table3"with support
let with = With(
Table("Table1"),
SwifQL.select(Table("Table2").*).from(Table("Table2"))
)
SwifQL.with(with)
.select(Table("Table1").*)
.from(Table("Table1"))will give
WITH
"Table1" as (SELECT "Table2".* FROM "Table2")
SELECT "Table1".*
FROM "Table1"Thanks to @hiimtmac
Make OrderBy.Direction public (#18)
In case when you want to set order direction in runtime now you could do it this way
SwifQL.orderBy(.direction(.asc, \User.email))
SwifQL.orderBy(.direction(.desc, \User.email))Implement `Column` and `Table`
SwifQL.select(Column("id")) // SELECT "id"
SwifQL.select(Table("Todo")) // SELECT "Todo"
SwifQL.select(Table("Todo").column("id")) // SELECT "Todo"."id"Implement `generate_series` by @hiimtmac
Example 1
SwifQL.select(Fn.generate_series(1, 4)will generate
SELECT generate_series(1, 4)Example 2
SwifQL.select(Fn.generate_series("2019-10-01", "2019-10-04", "1 day"))will generate
SELECT generate_series('2019-10-01', '2019-10-04', '1 day')Example 3
SwifQL.select(Fn.generate_series("2019-10-01" => .date, "2019-10-04" => .date, "1 day"))will generate
SELECT generate_series('2019-10-01', '2019-10-04', '1 day')Example 4
let df = DateFormatter()
df.dateFormat = "yyyy-MM-dd HH:mm:ss"
df.timeZone = TimeZone(secondsFromGMT: 0)
SwifQL.select(Fn.generate_series(df.date(from: "2019-10-01 00:00:00")!, df.date(from: "2019-10-04 00:00:00")!, "1 day"))will generate
SELECT
generate_series(
(TIMESTAMP 'EPOCH' + make_interval(secs => 1569888000.0)),
(TIMESTAMP 'EPOCH' + make_interval(secs => 1570147200.0)),
'1 day'
)