About setting up jdbc database as the storage system (sqlite)

[EnderRL]

Hi, I have been an ImageFrame supporter since your first release. Thank you for maintaining the plugin!

Anyways, I had a lot of images in the server. Since the plugin uses filetree as storage, transferring thousands of files is not ideal. That's why I been hopping the plugin to have a storage optimization update like storing data in database. Until not long ago, I realized the plugin has the Storage section in the config file. I had been trying to setup the database storage system, but failed. Unfortunately I don't see this feature exist on any wiki or documentation, or even someone mentioning this feature in the issues.

So I tried setting up the config myself - setting up the storage type as jdbc, JdbcUrl: jdbc:sqlite:plugins/ImageFrame/imageframe.db (I referred the bluemap wiki, something like connection-url: "jdbc:sqlite:bluemap/sqlite.db", and here i prefer using sqlite as my database instead of mysql)

This is what I got so far during server startup:

[20:29:56 INFO]: [com.loohp.imageframe.libs.com.zaxxer.hikari.HikariDataSource] HikariPool-1 - Starting...
[20:29:56 INFO]: [com.loohp.imageframe.libs.com.zaxxer.hikari.pool.HikariPool] HikariPool-1 - Added connection org.sqlite.jdbc4.JDBC4Connection@435b6f2a
[20:29:56 INFO]: [com.loohp.imageframe.libs.com.zaxxer.hikari.HikariDataSource] HikariPool-1 - Start completed.
[20:29:56 INFO]: [ImageFrame] Successfully connected to database.
[20:29:56 ERROR]: Error occurred while enabling ImageFrame v1.9.0.2 (Is it up to date?)
java.lang.RuntimeException: Unable to prepare database schema
        at ImageFrame-1.9.0.2.jar/com.loohp.imageframe.storage.JdbcImageFrameStorage.prepareDatabase(JdbcImageFrameStorage.java:199) ~[ImageFrame-1.9.0.2.jar:?]
        at ImageFrame-1.9.0.2.jar/com.loohp.imageframe.storage.JdbcImageFrameStorage.<init>(JdbcImageFrameStorage.java:127) ~[ImageFrame-1.9.0.2.jar:?]
        at ImageFrame-1.9.0.2.jar/com.loohp.imageframe.storage.JdbcImageFrameStorageLoader.create(JdbcImageFrameStorageLoader.java:62) ~[ImageFrame-1.9.0.2.jar:?]
        at ImageFrame-1.9.0.2.jar/com.loohp.imageframe.storage.JdbcImageFrameStorageLoader.create(JdbcImageFrameStorageLoader.java:28) ~[ImageFrame-1.9.0.2.jar:?]
        at ImageFrame-1.9.0.2.jar/com.loohp.imageframe.storage.ImageFrameStorageLoaders.create(ImageFrameStorageLoaders.java:56) ~[ImageFrame-1.9.0.2.jar:?]
        at ImageFrame-1.9.0.2.jar/com.loohp.imageframe.ImageFrame.onEnable(ImageFrame.java:273) ~[ImageFrame-1.9.0.2.jar:?]
        at org.bukkit.plugin.java.JavaPlugin.setEnabled(JavaPlugin.java:279) ~[paper-api-1.21.10-R0.1-SNAPSHOT.jar:?]
        at io.papermc.paper.plugin.manager.PaperPluginInstanceManager.enablePlugin(PaperPluginInstanceManager.java:202) ~[paper-1.21.10.jar:1.21.10-61-8339bb3]
        at io.papermc.paper.plugin.manager.PaperPluginManagerImpl.enablePlugin(PaperPluginManagerImpl.java:109) ~[paper-1.21.10.jar:1.21.10-61-8339bb3]
        at org.bukkit.plugin.SimplePluginManager.enablePlugin(SimplePluginManager.java:520) ~[paper-api-1.21.10-R0.1-SNAPSHOT.jar:?]
        at org.bukkit.craftbukkit.CraftServer.enablePlugin(CraftServer.java:640) ~[paper-1.21.10.jar:1.21.10-61-8339bb3]
        at org.bukkit.craftbukkit.CraftServer.enablePlugins(CraftServer.java:596) ~[paper-1.21.10.jar:1.21.10-61-8339bb3]
        at net.minecraft.server.MinecraftServer.initPostWorld(MinecraftServer.java:590) ~[paper-1.21.10.jar:1.21.10-61-8339bb3]
        at net.minecraft.server.dedicated.DedicatedServer.initServer(DedicatedServer.java:368) ~[paper-1.21.10.jar:1.21.10-61-8339bb3]
        at net.minecraft.server.MinecraftServer.runServer(MinecraftServer.java:1214) ~[paper-1.21.10.jar:1.21.10-61-8339bb3]
        at net.minecraft.server.MinecraftServer.lambda$spin$2(MinecraftServer.java:353) ~[paper-1.21.10.jar:1.21.10-61-8339bb3]
        at java.base/java.lang.Thread.run(Thread.java:1583) ~[?:?]
Caused by: org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "AUTO_INCREMENT": syntax error)
        at org.sqlite.core.DB.newSQLException(DB.java:1179) ~[sqlite-jdbc-3.49.1.0.jar:?]
        at org.sqlite.core.DB.newSQLException(DB.java:1190) ~[sqlite-jdbc-3.49.1.0.jar:?]
        at org.sqlite.core.DB.throwex(DB.java:1150) ~[sqlite-jdbc-3.49.1.0.jar:?]
        at org.sqlite.core.NativeDB._exec_utf8(Native Method) ~[sqlite-jdbc-3.49.1.0.jar:?]
        at org.sqlite.core.NativeDB._exec(NativeDB.java:99) ~[sqlite-jdbc-3.49.1.0.jar:?]
        at org.sqlite.jdbc3.JDBC3Statement.lambda$executeLargeUpdate$2(JDBC3Statement.java:139) ~[sqlite-jdbc-3.49.1.0.jar:?]
        at org.sqlite.jdbc3.JDBC3Statement.withConnectionTimeout(JDBC3Statement.java:458) ~[sqlite-jdbc-3.49.1.0.jar:?]
        at org.sqlite.jdbc3.JDBC3Statement.executeLargeUpdate(JDBC3Statement.java:126) ~[sqlite-jdbc-3.49.1.0.jar:?]
        at org.sqlite.jdbc3.JDBC3Statement.executeUpdate(JDBC3Statement.java:113) ~[sqlite-jdbc-3.49.1.0.jar:?]
        at ImageFrame-1.9.0.2.jar/com.loohp.imageframe.libs.com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:120) ~[ImageFrame-1.9.0.2.jar:?]
        at ImageFrame-1.9.0.2.jar/com.loohp.imageframe.libs.com.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java) ~[ImageFrame-1.9.0.2.jar:?]
        at ImageFrame-1.9.0.2.jar/com.loohp.imageframe.storage.JdbcImageFrameStorage.prepareDatabase(JdbcImageFrameStorage.java:164) ~[ImageFrame-1.9.0.2.jar:?]
        ... 16 more
[20:29:56 INFO]: [ImageFrame] Disabling ImageFrame v1.9.0.2
[20:29:56 INFO]: [ImageFrame] ImageFrame has been Disabled!

I am using the latest version dev build ImageFrame v1.9.0.2
And here is the storage config section:

Storage:
  # Supported storage systems: file (Default), jdbc
  Type: jdbc
  # Options are only used if the storage system type requires it, such as jdbc
  Options:
    JdbcUrl: jdbc:sqlite:plugins/ImageFrame/imageframe.db
    Username: imageframe
    Password: imageframe
    # In Seconds
    ActivePollInterval: 5

There's a 36kb database being created.

Now here's my question:

• What actually went wrong? Does sqlite even work? Do I need to install the jdbc driver in order to work?
• If the database finally working, how do I migrate my old data to database? Is it the new command /imageframe storagemigrate imageframe:jdbc normal/forced? This command is not documented nor mentioned anywhere.

I also hope that the jdbc database storage configuration will be documented. Looking forward for your response. Thanks!