Describe column schema with Kuery? about swift-kuery HOT 13 CLOSED

The creation of the table directly from our Table description.

Does it mean that this feature will remove the need to manually create tables in database?

let id = Column(name: "id", type: .integer, isPrimaryKey: true, isNull: false)

This could probably also be done as:

let id = Column(name: "id", type: .integer, options: [.primary, .nullable, .default(10)])

But this could require more checks if array contains required option.

from swift-kuery.

Is extending basic types with some protocol, like SQLDataType, also considered? So the usage of type would look like:

extension Int: SQLDataType { ... }
extension String: SQLDataType { ... }
extension Double: SQLDataType { ... }
struct Geometry: SQLDataType { ... }

let integer = Column("integer", type: Int.self, options: [])
let string = Column("string", type: String.self, options: [])
let shape = Column("shape", type: Geometry.self, options: [])

from swift-kuery.

An initial work in progress is in issue_17 branch. It supports some column properties and table creation.
The following code creates MyTable in the database:

    class MyTable: Table {
        let a = Column("a", type: String.self, isPrimaryKey: true, defaultValue: "qiwi")
        let b = Column("b", type: Int.self)
        let c = Column("c", type: Double.self, defaultValue: 4.95)
        let tableName = "MyTable"
    }
    
   let t = MyTable()
   t.create(connection: connection) { result in ... }

We also started thinking about migration. Some initial code (with explanations) can be found in Migration.swift in the same branch.

from swift-kuery.

This is very nice. I think it would be very helpful to drive this in the direction of SQLAlchemy

from swift-kuery.

Yeah, this would be a huge change to the API- but I think it would make the object-mapping so much easier to do later. Plus, the ability to create tables would be nice, too. Any feedback, @irar2 and @shmuelk ?

from swift-kuery.

@naithar - yeah, one could use a method (borrowing from SQLAlchemy) like:

Users.create(engine)

where engine represents the connection to the database.

from swift-kuery.

@rfdickerson should this also provide an Index creation for database?
Like:

let indexes = [
    .unique(id, name),
    .default(id)
]

from swift-kuery.

Shouldn't enum SQLDataType also contain case like .raw(String) for custom types, like jsonb, geometry and others?

from swift-kuery.

Yeah, something like that or maybe this for indices as Alchemy does it:

let col2 = Column(name: 'col2', .integer, index: true, unique: true),

or if the index involves multiple tables, maybe use a list

let index = Index(name: 'idx_col34', columns: [mytable.col3, mytable.col4])

The usage of jsonb, geometry, etc. which are database-specifically supported data types is a bit problematic in the sense that Swift-Kuery should interchangably work on MySQL, PostgreSQL, SQLite, and DB2. Maybe if the argument for SQLDataType was a protocol instead of an enum. Then you can have different enums that support BasicSQLDataType, PostgreSQLDataType, etc that conform to SQLDataType. Then that enum can be used for the data value.

from swift-kuery.

Implementing .raw(String) case for SQLDataTypeshould be enough, if it would be used just for table creation:

let shape = Column("shape", type: .raw("geometry"), options: [])

But if type parameter will take part in something more than just table creation, mapping for example, then making SQLDataType a protocol would really be better and provide required functionality.

from swift-kuery.

This is really quite nice. We have to sit down and think about this a bit.

I don't see why this is an API breaking change. Why can't the DDL parameters of Column be optional?

As I think about, I am even more in favor of the DDL parameters being optional as some people may want to manage the tables outside of the code. This way we let them do things either way.

I like the idea of adding a static function create to Table that creates the table.

Our focus at this time is trying to get the basic functionality we have up and running on more than one database engine. We've run into issues with that already.

from swift-kuery.

Hello. I'm sorry to interrupt this conversation, but as the author of the SQLite application toolkit GRDB.swift, and being interested in the Kitura ecosystem, I'd like to warn you against mixing regular CRUD methods and schema-altering methods in a single type:

// Something has been lost here:
Users.create(engine)
let users = Select(from: Users())

The trouble is that both Swift code and database schema evolve, and not always at the same pace. As soon as the Swift code does not reflect any longer the actual schema of the database file, and yet pretends it does, users are in trouble. They trap themselves in their preciously crafted types and don't know how to escape. You can read some GRDB design notes here, and have a look at the resulting database schema DSL - which is totally separated from regular CRUD operations, on purpose. For other mainstream database libraries that take care of maintaining this strict separation, see Core Data and Ruby's ActiveRecord.

from swift-kuery.

For Column type definition I followed @naithar's suggestion to extend the Swift types or create new types:

public protocol SQLDataType {
    /// Return database specific description of the type using `QueryBuilder`.
    ///
    /// - Parameter queryBuilder: The QueryBuilder to use.
    /// - Returns: A String representation of the type.
    static func create(queryBuilder: QueryBuilder) -> String
}

extension Int16: SQLDataType {
    public static func create(queryBuilder: QueryBuilder) -> String {
        return "smallint"
    }
}

public struct Time: SQLDataType {
    public static func create(queryBuilder: QueryBuilder) -> String {
        return "time"
    }
}

Column constructor:

public init(_ name: String, type: SQLDataType.Type? = nil, isPrimaryKey: Bool = false, isNotNullable: Bool = false, isUnique: Bool = false, defaultValue: Any? = nil) {}

Table definition:

 class MyTable: Table {
        let a = Column("a", type: String.self, isPrimaryKey: true)
        let b = Column("b", type: Int16.self, defaultValue: 0)
        let c = Column("c", type: Time.self)
        
        let tableName = "MyTable"
 }

I prefer this approach over enum, because it is easily extendable by plugins and applications.

But there is a problem with types that have additional data, e.g. varchar(n). We can create a struct Varchar:

public struct Varchar: SQLDataType {
    public static func create(queryBuilder: QueryBuilder) -> String {
        return "varchar"
    }
}

but this way we can't store the length.

The best idea we have at the moment (suggested by @shmuelk) is to add length as an argument to the Column constructor:

 class MyTable: Table {
        let a = Column("a", type: Varchar.self, length: 30)
        ...

(We could also omit type label

 class MyTable: Table {
        let a = Column("a", Varchar.self, length: 30)
        ...

).

Any better ideas?

from swift-kuery.