Releases · SwifQL/SwifQL

Now we have an ability to add custom functions and cast types with extensions like this

// Adds custom cast type
extension CastType {
    static var dayOfWeekArray: CastType = .init("dayofweek[]")
}
// Adds custom function name
extension Fn.Name {
    static var helloWorld: Fn.Name { return .init("hello_world") }
}
// Adds function builder method
extension Fn {
    static func row_to_json(_ record: SwifQLable, pretty: Bool? = nil) -> SwifQLable {
        var parts: [SwifQLPart] = record.parts
        if let pretty = pretty {
            parts.append(o: .comma)
            parts.append(o: .space)
            parts.append(safe: pretty)
        }
        return build(.row_to_json, body: parts)
    }
}

Now build it this way

Case.when(\CarBrands.name == "BMW")
    .then("Crazy driver")
    .when(\CarBrands.name == "Tesla")
    .then("Fancy driver")
    .else("Normal driver")
    .end

or this

Case(\CarBrands.name)
    .when("BMW")
    .then("Crazy driver")
    .when("Tesla")
    .then("Fancy driver")
    .else("Normal driver")
    .end

Closes #8

For postgres it takes timeIntervalFrom1970 and converts it into TIMESTAMP WITHOUT TIMEZONE
Fox mysql it takes timeIntervalFrom1970 and converts it into TIMESTAMP by using FROM_UNIXTIME function

So now you can easily use dates in your queries like this

SwifQL.select(Date() => "currentTime")
SwifQL.select(User.table.*).from(User.table).where(\User.createdAt > Date(timeIntervalSinceNow: -86400))

Now you also could build keypath manually like this CarBrands.mkp("id")

Implement =>"aliasName" to have an ability to use aliases as select params

So if you want to use some alias in SELECT please write it as String with => prefix like this

SwifQL.select("hello", =>"aaa")
      .from(|SwifQL.select(\CarBrands.name).from(CarBrands.table))| => "aaa"

to reach

SELECT 'hello', "aaa" FROM (SELECT "CarBrands"."name" FROM "CarBrands") as "aaa"

In complex situations you could use it to reach complex paths

Example:

"User"."device"->"model"

Normally in Vapor it should work with

\User.device.model

but device should conform to ReflectionDecodable for that...

The new workaround is to use FormattedKeyPath like this

FormattedKeyPath(User.self, "device", "model")

For Vapor

Optionally conform your models to SwifQLTable or to just Tableable protocol to be sure that SwifQL takes the right table name. Without conforming it will use String(describing: Self.self) as a table name.

For pure swift

Please conform all your models to SwifQLTable

// (array_agg(to_jsonb("Attachment")))
let someArray = |Fn.array_agg(Fn.to_jsonb("Attachment"))|
// (array_agg(to_jsonb("Attachment")))[1]
let query = someArray[1]

So now you can build queries with time functions like this

// SELECT '2001-09-28'::date + 7::integer
SwifQL.select("2001-09-28" => .date + 7 => .integer)

or like this

// SELECT * FROM "User" WHERE "birthDay" BETWEEN now() AND now() + '24 hours'::interval
SwifQL.select(User.table.*)
      .from(User.table)
      .where(\User. birthDay <> Fn.now() && Fn.now() + "24 hours" => .interval)

etc.

⚠️ This may broke your project, please change your joins from

.join(.left, Role.table, \User.role == \Role.id)

to

.join(.left, Role.table, on: \User.role == \Role.id)

This syntax just feels more natural.

Releases: SwifQL/SwifQL

🎉 Refactored Fn and CastType

Uh oh!

BREAKING: reimplement CASE WHEN THEN ELSE END

Uh oh!

Implement `Date` support

Uh oh!

Improve `FormattedKeyPath`

Uh oh!

Support aliases in SELECT

Uh oh!

Implement `FormattedKeyPath`

Uh oh!

Implement `Tableable` protocol

For Vapor

For pure swift

Uh oh!

Implement ability to append something wrapped into square brackets

Uh oh!

Added Postgres time functions and `between` infix operator

Uh oh!

0.7.2: Change `.join` syntax by adding `on` keyword

Uh oh!