Make class PostgreSQLSQLSerializer public

[vkill]

Hi

Sometimes we will query raw SQL like this, so should make class PostgreSQLSQLSerializer public.

The demo for 1.0.0-rc.2

let columns = [
    "bid",
    "time",
]

let sqlSerializer = PostgreSQLSQLSerializer()
var statement: [String] = []
var parameters: [PostgreSQLData] = []

let table = "table"
statement.append("INSERT INTO")
statement.append(sqlSerializer.makeEscapedString(from: table))

statement.append("(" + columns.map { sqlSerializer.makeEscapedString(from: $0) }.joined(separator: ", ") + ")")
statement.append("VALUES")

let now = Date()
statement.append("(" + columns.map { sqlSerializer.makePlaceholder(name: $0) }.joined(separator: ", ") + ")")

statement.append("ON CONFLICT (" + sqlSerializer.makeEscapedString(from: "bid") + ") DO NOTHING")

parameters += [
    try "bid".convertToPostgreSQLData(),
    try Date().convertToPostgreSQLData(),
]

let sqlString = statement.joined(separator: " ")
try conn.query(sqlString, parameters)

[tanner0101]

Discussion: maybe a better solution to this would be allowing the PostgreSQLConnection to accept an instance of SQL? That way we could keep this serializer internal but still allow for construction of SQL queries in a Swifty way

[vkill]

In this case, We can make a DataQuery instance then serialize to sql like https://docs.vapor.codes/3.0/sql/overview/.

Anyway, we should make a PostgreSQLSerializer class, so I suggest that make PostgreSQLSQLSerializer class public.

[vkill]

Sometimes We need more complex sql like bulk insert into, maybe provide tools for make raw sql is better.

[tanner0101]

PostgreSQL has been expanded to have more type safe SQL query types. We should work toward adding any missing behavior to those Swift types in a more type-safe way. 👍

[vkill]

The new demo for 1.0.0-rc.3

let columns = [
    "bid",
    "time",
]

var values: [[PostgreSQLQuery.Value]] = []
var parameters: [PostgreSQLData] = []

values.append([
    try "bid".convertToPostgreSQLData(),
    try Date().convertToPostgreSQLData(),
].map{ PostgreSQLQuery.Value.data($0) })

let query = PostgreSQLQuery.insert(.init(table: .init(name: "table"), columns: columns, values: values, returning: []))
let sqlPart = query.serialize(&parameters)
let sql = """
\(sqlPart ) ON CONFLICT ("bid") DO NOTHING
"""

db.query(.raw(query: sql, binds: parameters)

[vkill]

The new demo for 1.0.0-rc.4

let columns = [
    "bid",
    "time",
]
var values: [[PostgreSQLExpression]] = []

struct Model: Encodable {
    let bid: String
    let time: Date
}
let model = Model(
    bid: "bid",
    time: Date()
)
let dict = SQLQueryEncoder(PostgreSQLExpression.self).encode(model)
values.append(columns.map{ dict[$0]! })

var insert: PostgreSQLInsert = .insert(.table(.identifier("table")))
insert.columns = columns.map{ .column(nil, .identifier($0)) }
insert.values = values
insert.upsert = PostgreSQLUpsert.upsert([.column(nil, .identifier("bid"))], [
    (
        .identifier("time"),
        .literal(.numeric("""
        EXCLUDED."time"
        """))
    )
])
insert.returning = [
    .expression(.column(.column(nil, .identifier("bid"))), alias: nil)
]

db.query(PostgreSQLQuery.insert(insert))