Exposed: Db Location/ existing database

[jagiellonczyk14]

Hi is it possible to use existing sqlite db or specify the location of created sqlite db?
I am developing desktop application for my own need (at least for now) so i am changing the program nearly everyday and making new versions of it but in work I would like to use one version of database all the time, that would be located in specific external location, is i possible with exposed?

[Prototik]

Please see docs for specific jdbc driver. You could use any jdbc url (or even DataSource) as you want:

Database.connect("jdbc:sqlite:/full/path/to/sqlite.db", "org.sqlite.JDBC")

btw H2 driver works for me better than sqlite, check it too. H2 is embedded database written in Java, so no problems with native libraries like in sqlite driver.

[jagiellonczyk14]

Thank You for Help! I will check it :)

[jagiellonczyk14]

One more question Advices from Prototik works like a charm, hovewer I have problems implementing SchemaUtils.createMissingTablesAndColumns
When I am using SchemaUtils.create everything is o.k. but when i try createMissingTablesAndColumns I get error NULL not allowed for column "CITY"; SQL statement:
ALTER TABLE USERS ADD CITY INT NOT NULL [23502-196]
Full eror and code below

`class DatabaseController {
    object Users : IntIdTable() {
        val name = varchar("name", 50).index()
        val city = reference("city", Cities)
        val age = integer("age")
    }

    object Cities : IntIdTable() {
        val name = varchar("name", 50)
    }

    class User(id: EntityID<Int>) : IntEntity(id) {
        companion object : IntEntityClass<User>(Users)

        var name by Users.name
        var city by City referencedOn Users.city
        var age by Users.age
    }

    class City(id: EntityID<Int>) : IntEntity(id) {
        companion object : IntEntityClass<City>(Cities)

        var name by Cities.name
        val users by User referrersOn Users.city
    }

    fun main() {
        Database.connect("jdbc:h2://home/oliwia/Downloads/kotlinGradleNucHelp/src/main/resources/NucHelpDb", driver = "org.h2.Driver")
      //  Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver")
      // Database.connect("jdbc:mysql://localhost:3306/test",                "com.mysql.jdbc.Driver")
        transaction {
            logger.addLogger(StdOutSqlLogger)

           // SchemaUtils.create(Cities, Users)
            SchemaUtils.createMissingTablesAndColumns(Cities, Users)
            val stPete = City.new {
                name = "St. Petersburg"
            }

            val munich = City.new {
                name = "Munich"
            }

            User.new {
                name = "a"
                city = stPete
                age = 5
            }

            User.new {
                name = "b"
                city = stPete
                age = 27
            }

            User.new {
                name = "c"
                city = munich
                age = 42
            }

            println("Cities: ${City.all().joinToString { it.name }}")
            println("Users in ${stPete.name}: ${stPete.users.joinToString { it.name }}")
            println("Adults: ${User.find { Users.age greaterEq 18 }.joinToString { it.name }}")
        }
    }


}`

Exception in Application constructor
Exception in thread "main" java.lang.reflect.InvocationTargetException
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at sun.launcher.LauncherHelper$FXHelper.main(LauncherHelper.java:767)
Caused by: java.lang.RuntimeException: Unable to construct Application instance: class MyMiniFramework.app.main.MyApp
    at com.sun.javafx.application.LauncherImpl.launchApplication1(LauncherImpl.java:907)
    at com.sun.javafx.application.LauncherImpl.lambda$launchApplication$154(LauncherImpl.java:182)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.reflect.InvocationTargetException
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$160(LauncherImpl.java:819)
    at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$174(PlatformImpl.java:326)
    at com.sun.javafx.application.PlatformImpl.lambda$null$172(PlatformImpl.java:295)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.application.PlatformImpl.lambda$runLater$173(PlatformImpl.java:294)
    at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
    at com.sun.glass.ui.gtk.GtkApplication._runLoop(Native Method)
    at com.sun.glass.ui.gtk.GtkApplication.lambda$null$48(GtkApplication.java:139)
    ... 1 more
Caused by: org.h2.jdbc.JdbcSQLException: NULL not allowed for column "CITY"; SQL statement:
ALTER TABLE USERS ADD CITY INT NOT NULL [23502-196]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)
    at org.h2.table.Column.validateConvertUpdateSequence(Column.java:345)
    at org.h2.table.Table.validateConvertUpdateSequence(Table.java:797)
    at org.h2.command.dml.Insert.addRow(Insert.java:195)
    at org.h2.command.dml.Select.queryFlat(Select.java:523)
    at org.h2.command.dml.Select.queryWithoutCache(Select.java:625)
    at org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:114)
    at org.h2.command.dml.Query.query(Query.java:371)
    at org.h2.command.dml.Insert.insertRows(Insert.java:167)
    at org.h2.command.dml.Insert.update(Insert.java:114)
    at org.h2.command.ddl.CreateTable.update(CreateTable.java:192)
    at org.h2.command.ddl.AlterTableAlterColumn.execute(AlterTableAlterColumn.java:501)
    at org.h2.command.ddl.AlterTableAlterColumn.cloneTableStructure(AlterTableAlterColumn.java:401)
    at org.h2.command.ddl.AlterTableAlterColumn.copyData(AlterTableAlterColumn.java:267)
    at org.h2.command.ddl.AlterTableAlterColumn.update(AlterTableAlterColumn.java:184)
    at org.h2.command.CommandContainer.update(CommandContainer.java:101)
    at org.h2.command.Command.executeUpdate(Command.java:260)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:164)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:150)
    at org.jetbrains.exposed.sql.Transaction$exec$2.executeInternal(Transaction.kt:88)
    at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed(Statement.kt:55)
    at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:111)
    at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:105)
    at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:84)
    at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:75)
    at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns(SchemaUtils.kt:142)
    at MyMiniFramework.app.DataBase.DatabaseController$main$1.invoke(DatabaseController.kt:54)
    at MyMiniFramework.app.DataBase.DatabaseController$main$1.invoke(DatabaseController.kt:20)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:101)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:72)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:55)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$default(ThreadLocalTransactionManager.kt:55)
    at MyMiniFramework.app.DataBase.DatabaseController.main(DatabaseController.kt:50)
    at MyMiniFramework.app.main.MyApp.<init>(MyApp.kt:26)
    ... 13 more

[Tapac]

If you already have records in table Users and then add a reference to City (not optional), then database can't create it because the default value is null. You should use optReference("city", City) or cleanup Users table before call createMissingTablesAndColumns.

If you need non-null reference, then try to make it in three steps:
1) use optReference + createMissingTablesAndColumns
2) select all users and link it to cities
3) change to reference + createMissingTablesAndColumns

[jagiellonczyk14]

Tank You For answering surely it was also my mistake, yet I created a simple code without referencing to Cities (and deleting the previous database) and now I have problem with SQL error
org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "ALTER TABLE USERS MODIFY COLUMN ID INT AUTO_INCREMENT PRIMARY[*] KEY"; SQL statement:
ALTER TABLE USERS MODIFY COLUMN ID INT AUTO_INCREMENT PRIMARY KEY [42000-196]

class DatabaseController {
    object Users : IntIdTable() {
        val name = varchar("name", 50).default("")
        val age = integer("age").default(0)
    }


    class User(id: EntityID<Int>) : IntEntity(id) {
        companion object : IntEntityClass<User>(Users)

        var name by Users.name
        var age by Users.age
    }


    fun main() {
        Database.connect("jdbc:h2://home/oliwia/Downloads/kotlinGradleNucHelp/src/main/resources/NucHelpDb", driver = "org.h2.Driver")

        transaction {
            logger.addLogger(StdOutSqlLogger)

            SchemaUtils.createMissingTablesAndColumns(Users)

            User.new {
                name = "a"
                age = 5
            }

            User.new {
                name = "b"
                age = 27
            }

        }


    }
}

[jagiellonczyk14]

Hovewer after some modifactions to the code, It works fine, I posted the message above beacuse it may be a bug (or my mistake), the code that works at least for now is

class DatabaseController {

    object Users : Table() {
        val id = integer("id").autoIncrement()// Column<String>
        val name = varchar("name", length = 50).default("") // Column<String>
        val nameB = varchar("nameB", length = 50).default("") // Column<String>

    }



    fun main() {
        Database.connect("jdbc:h2://home/oliwia/Downloads/kotlinGradleNucHelp/src/main/resources/NucHelpDb", driver = "org.h2.Driver")

        transaction {
            logger.addLogger(StdOutSqlLogger)

            SchemaUtils.createMissingTablesAndColumns(Users)


            Users.insert {
               it[name] = "Sergeyd"
                it[nameB] = "Sergeyd"

            }


        }


    }
}

[Prototik]

createMissingTablesAndColumns very buggy for me. I switched to simple create and it works in most cases, try it.
Probably createMissingTablesAndColumns works perfectly for well-tested dialects, postgres I suppose.

But you will need to manually write migration code (like with you trouble migrting nullable -> not null)

[jagiellonczyk14]

I suppose so, hovewer using this function is vary handy for me because program is evolving I am adding new columns to the records quite frequently and I still want to reference the old records , so using this is for me so convinient
Generally this API is fantastic

[Tapac]

@jagiellonczyk14 , createMissingTablesAndColumns not only changes schema automatically but also logs recommendation about excessive indexes or indexes not mapped in code.
If you have a reproducible scenario where your code fails with JdbcSQLException please provide it.
I will try to use one from above and will call createMissingTablesAndColumns multiple times in one transaction to ensure that it works fine.