Thread
#exposed
    JP

    JP

    1 year ago
    Is there a way to dynamically modify tables? Instead of adding a new column in the table definition, can I do something like
    transaction{
       Table.addColumn(...)
    }
    ?
    For context, I'm trying to natively migrate my database. Currently, if I change the table definition, it won't matter because exposed will detect the table already exists and won't do anything. But I can't drop the table without losing all of the data. I know exposed doesn't have any define way of migrating databases, but I'm hoping it maybe has some way to change schemas like this?
    r

    redenergy

    1 year ago
    If my memory serves me well, exposed actually allows to create missing columns even if table already exists. It can just compare list of columns from database with registered columns in Table definition in code and execute ALTER statements (depending on the vendor) for columns which are not present in database. Wont it be sufficient?
    JP

    JP

    1 year ago
    Could you share an example? I tried adding the column to the table definition, but it didn't seem to modify the underlying schema
    I also didn't access it anywhere, so it could be that it's lazying loading the column (not until I reference it), but I doubt it. That seems too dangerous or over engineered to be the case (I would hope)
    r

    redenergy

    1 year ago
    Simple example Suppose I have the following program with table definition:
    import org.jetbrains.exposed.dao.id.IntIdTable
    import org.jetbrains.exposed.sql.Database
    import org.jetbrains.exposed.sql.SchemaUtils
    import org.jetbrains.exposed.sql.StdOutSqlLogger
    import org.jetbrains.exposed.sql.addLogger
    import org.jetbrains.exposed.sql.transactions.DEFAULT_REPETITION_ATTEMPTS
    import org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManager
    import org.jetbrains.exposed.sql.transactions.transaction
    import java.sql.Connection
    
    object SampleAddressTable: IntIdTable("sample") {
    
        val name = varchar("name", 64)
        val address = varchar("address", 128)
    
    }
    
    fun main(args: Array<String>) {
        Database.connect("jdbc:sqlite:sample.db",
            manager = { ThreadLocalTransactionManager(it, Connection.TRANSACTION_SERIALIZABLE, DEFAULT_REPETITION_ATTEMPTS) })
        transaction {
            addLogger(StdOutSqlLogger)
            SchemaUtils.createMissingTablesAndColumns(SampleAddressTable)
        }
    }
    Upon execution it outputs (notice db requests logging):
    [2020-11-17, 10:12:21] [INFO] Preparing create tables statements took 18ms
    SQL: CREATE TABLE IF NOT EXISTS sample (id INTEGER PRIMARY KEY AUTOINCREMENT, "name" VARCHAR(64) NOT NULL, address VARCHAR(128) NOT NULL)
    [2020-11-17, 10:12:21] [INFO] Executing create tables statements took 17ms
    [2020-11-17, 10:12:21] [INFO] Extracting table columns took 2ms
    [2020-11-17, 10:12:21] [INFO] Preparing alter table statements took 3ms
    [2020-11-17, 10:12:21] [INFO] Executing alter table statements took 0ms
    [2020-11-17, 10:12:21] [INFO] Checking mapping consistence took 6ms
    and creates specified table in database (see attached image) Now I'll add another column and table definition will look like this:
    object SampleAddressTable: IntIdTable("sample") {
    
        val name = varchar("name", 64)
        val address = varchar("address", 128)
        val description = varchar("description", 128).nullable()
    
    }
    When program executed again and SchemaUtils.createMissingTablesAndColumns is called Exposed will create our new column:
    [2020-11-17, 10:15:33] [INFO] Preparing create tables statements took 10ms
    [2020-11-17, 10:15:33] [INFO] Executing create tables statements took 0ms
    [2020-11-17, 10:15:33] [INFO] Extracting table columns took 2ms
    [2020-11-17, 10:15:33] [INFO] Preparing alter table statements took 9ms
    SQL: ALTER TABLE sample ADD description VARCHAR(128) NULL
    [2020-11-17, 10:15:33] [INFO] Executing alter table statements took 13ms
    [2020-11-17, 10:15:33] [INFO] Checking mapping consistence took 6ms
    JP

    JP

    1 year ago
    Thank you @redenergy ! That's a great example. I couldn't seem to get it working the same with my project, but Tapac had a great answer that seemed to fix my situation.