[best practices] How can insert a same type of record into 2 tables

[ajunjunandtmac]

For example i create two tables table ClassA and table ClassB Then i create a struct Student now i want to insert studentA entity into ClassA table and insert studentB into tableClassB PersistableRecord protocol allow us to define the associated table name, but i cannot define 2 table names How can i achieve this goal without using the raw SQL string?

Right now before i insert records to table ClassA, i will change the databaseTableName to ClassA first, also the same way when i want to insert records into table ClassB, is there a better way?

Also another question is how can i fetch the records from different tables with your convenience method not the SQL? Now i'm trying to use the below codes and the reference is here #993

let classATable = Table("ClassA")
classATable.fetchAll(db)

is this a right way?

Thanks very much~

[groue]

Hello @ajunjunandtmac,

There's two sides in your question, fetching and writing the same record type from two distinct tables.

As you have noticed, this is not a use case that has any built-in convenience. It's not that the the use case is wrong. Just, nothing exists that helps it.

On the fetching side, the Table type of #993 can indeed help. The linked PR has a sample code. Look for the "Typed table" example. You should end up with something similar to:

let table1 = Table<MyType>("myTable1")
let table2 = Table<MyType>("myTable2")

On the persistence side, there is no other built-in solution than custom SQL, or two distinct types (one per table).

I'm not opposed to consider the feature, if I'm presented with a compelling use case, and the suggestion of an API that would blend well in the current state of the library.

[ajunjunandtmac]

Thanks for your reply

Below is my sample code, i changed the databaseTableName every time before inserting, is this a workaround?

func insertTrackPoints(_ points: [TrackLineModel], trackLineType: TrackLineType, referenceId: String) async throws {
    TrackLineModel.databaseTableName = trackLineType.tableName
    try await localDB.dbWriter.write { db in
        let points = points.map {
            var temp = $0
            temp.referenceId = referenceId
            return temp
        }
        try points.forEach { point in
            try point.insert(db)
        }
    }
}

func testFunc() {
    Task {
        try? await insertTrackPoints(satellitePoints, trackLineType: .sat, referenceId: "satUniqueId")
        try? await insertTrackPoints(cellularPoints, trackLineType: .cell, referenceId: "cellUniqueId")
    }
}

enum TrackLineType {
    case sat, cell

    var tableName: String {
        switch self {
        case .cell:
            return "cell_track_points"
        case .sat:
            return "sat_track_points"
        }
    }
}

[groue]

Below is my sample code, i changed the databaseTableName every time before inserting, is this a workaround?

Yes, you can try this technique. 👍

Take care to only change the table name from within the write block. Modify your code accordingly.

This will make sure that no concurrent database access can change the database table name under your feet. Since all write blocks are serialized (only one can run at any given time), the table name will be guaranteed to have the value you want.

[ajunjunandtmac]

Below is my sample code, i changed the databaseTableName every time before inserting, is this a workaround?

Yes, you can try this technique. 👍

Take care to only change the table name from within the write block. Modify your code accordingly.

This will make sure that no concurrent database access can change the database table name under your feet. Since all write blocks are serialized (only one can run at any given time), the table name will be guaranteed to have the value you want.

ok i will try, thanks for your suggestion

[ajunjunandtmac]

Sorry to disturb you again another additional question

    private lazy var migrator: DatabaseMigrator = {
        var migrator = DatabaseMigrator()
        migrator.registerMigration("v202405091840") { db in
            // Create a table
            // See https://github.com/groue/GRDB.swift#create-tables
            try TrackLineType.allCases.forEach {
                try self.createTable(name: $0.tableName, db: db)
            }
        }
        return migrator
    }()

why not make ifNotExists option as default? i put the code inside registerMigration closure to make sure the table will be create only once i wonder if it is equivalent if i use ifNotExists option

[groue]

You do not need to use IF NOT EXIST when you use migrations. They stop being equivalent as your application evolves and you add migrations for new database needs. For example, there is no equivalent of IF NOT EXIST when you add or remove a column from a table. Finally, ifNotExists is not the default because migrations are the recommended way to setup the database schema.

[ajunjunandtmac]

You do not need to use IF NOT EXIST when you use migrations. They stop being equivalent as your application evolves and you add migrations for new database needs. For example, there is no equivalent of IF NOT EXIST when you add or remove a column from a table. Finally, ifNotExists is not the default because migrations are the recommended way to setup the database schema.

Yes Yes i know that, if use migrations, no need to add IF NOT EXIST option, every migration closure will only execute once unless you delete the app, i know i'm in a right way right now after your confirmation. thanks

[groue]

Great :) Happy GRDB, @ajunjunandtmac!