Does it support Primary Key Conversion?

[xiaotingFu]

Hi,

Thanks a lot for this great tool. I have used it to convert MS Access database file to SQLite database file.

However I find some primary keys in ACCESS tables are not converted to the SQLite, will you support this function in the future or did you already have it but I miss it?

Appreciate it!

[clytras]

Hello @sisIFU,

I only create a primary key when there is an autoincrement number, so, it seems that it misses all of the rest . I need to dig on this a little bit so to fix it.

[johnfelipe]

how going primary and foreing key?

[platipusica]

Hello there! I would pay for adding a primary key in the case if not present on a table. Please let me know! Thanks!

[clytras]

Hello @platipusica,

You don't have to pay, it's an OS project. I'll check this again. Maybe it's not so big deal to support primary keys after all.

[platipusica]

Thank you. Right now, I am desperate for SQLite primary keys. Not using mysql at all atm.

Kind Regards

On Sun, Jul 21, 2024 at 1:01 AM Christos Lytras @.***> wrote:

Hello @platipusica https://github.com/platipusica,

You don't have to pay, it's an OS project. I'll check this again. Maybe it's not so big deal to support primary keys after all.

— Reply to this email directly, view it on GitHub https://github.com/clytras/AccessConverter/issues/1#issuecomment-2241325661, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACE3UNAB24AIG2V5SA4CXMLZNLT53AVCNFSM6AAAAABLBNXTHGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDENBRGMZDKNRWGE . You are receiving this because you were mentioned.Message ID: @.***>

[clytras]

@platipusica no worries, I will implement it for all supported output formats, including SQLite.

[clytras]

I have added some new features including the support for primary keys. It would be nice if anyone here can check it out and give some feed back. Go and check the releases for the v2.0 publiched JAR file.

[platipusica]

Cheers for that!

Just tried to compile it, this is the problem: maven install ... [ERROR] Failed to execute goal org.apache.maven.plugins:maven-compiler-plugin:3.1:compile (default-compile) on project accessconverter: Compilation failure: Compilation failure: [ERROR] /AccessConverter/AccessConverter/src/main/java/com/lytrax/accessconverter/SQLiteConverter.java:[260,16] illegal start of expression [ERROR] /AccessConverter/AccessConverter/src/main/java/com/lytrax/accessconverter/SQLiteConverter.java:[261,24] : expected [ERROR] /AccessConverter/AccessConverter/src/main/java/com/lytrax/accessconverter/SQLiteConverter.java:[263,25] : expected ... java -version openjdk version "11.0.24" 2024-07-16 OpenJDK Runtime Environment (build 11.0.24+8-post-Ubuntu-1ubuntu322.04) OpenJDK 64-Bit Server VM (build 11.0.24+8-post-Ubuntu-1ubuntu322.04, mixed mode, sharing)

I'm doing a jar with all dependencies, pls see pom.xml on my fork. Any thoughts?

On Wed, Jul 31, 2024 at 11:13 PM Christos Lytras @.***> wrote:

I have added some new features including the support for primary keys. It would be nice if anyone here can check it out and give some feed back. Go and check the releases for the v2.0 publiched JAR file.

— Reply to this email directly, view it on GitHub https://github.com/clytras/AccessConverter/issues/1#issuecomment-2261472002, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACE3UNC5YRREZFDZBFB3OGDZPFHO5AVCNFSM6AAAAABLBNXTHGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDENRRGQ3TEMBQGI . You are receiving this because you were mentioned.Message ID: @.***>

[clytras]

I have updated the JDK to JavaSE 17, so I assume the environment should be JRE/JDK SE 17, it's also inside the POM file https://github.com/clytras/AccessConverter/blob/ebee5ba0a0475c2ae38cb63f60ef734305c8bc78/pom.xml#L11-L14

I think Maven should pick this up and complaint about it but I'm not sure. I've had success building the JAR file using Maven with mvn install:

[INFO] --- install:3.1.2:install (default-install) @ accessconverter ---
[INFO] Installing \Java\AccessConverter\pom.xml to ~\.m2\repository\com\lytrax\accessconverter\2.0\accessconverter-2.0.pom
[INFO] Installing \Java\AccessConverter\target\accessconverter-2.0.jar to ~\.m2\repository\com\lytrax\accessconverter\2.0\accessconverter-2.0.jar
[INFO] Installing \Java\AccessConverter\target\accessconverter-2.0-jar-with-dependencies.jar to ~\.m2\repository\com\lytrax\accessconverter\2.0\accessconverter-2.0-jar-with-dependencies.jar
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  7.434 s
[INFO] Finished at: 2024-08-01T11:10:06+03:00
[INFO] ------------------------------------------------------------------------

❯ java -version
openjdk version "17.0.11" 2024-04-16 LTS
OpenJDK Runtime Environment Microsoft-9388408 (build 17.0.11+9-LTS)
OpenJDK 64-Bit Server VM Microsoft-9388408 (build 17.0.11+9-LTS, mixed mode, sharing)

Can you try maven install on an environment with JDK 17? I would build a docker command and a Github action to create artifacts, but unfortunately I don't have much time.

[platipusica]

Thanks! All good! So how to add the PK to the table which is missing it?

I see the indexes were created. If I may suggest this: for (String sql : statements) { try (Statement statement = connection.createStatement()) { statement.executeUpdate("PRAGMA synchronous = OFF"); statement.executeUpdate("PRAGMA journal_mode = MEMORY");

Without it: time java -jar /AccessConverter/AccessConverter/target/accessconverter-2.0-jar-with-dependencies.jar --access-file t.accdb --task convert-sqlite Result: Success .. Output file: /AccessConverter/t.sqlite3 Log file: /AccessConverter/t.log.json

real 3m13.546s user 0m6.724s sys 0m6.039s

With PRAGMAS: Output file: /AccessConverter/t.sqlite3 Log file: /AccessConverter/t.log.json

real 0m2.311s user 0m6.126s sys 0m0.461s

Cheers On Thu, Aug 1, 2024 at 10:17 AM Christos Lytras @.***> wrote:

I have updated the JDK to JavaSE 17, so I assume the environment should be JRE/JDK SE 17, it's also inside the POM file

https://github.com/clytras/AccessConverter/blob/ebee5ba0a0475c2ae38cb63f60ef734305c8bc78/pom.xml#L11-L14

I think Maven should pick this up and complaint about it but I'm not sure. I've had success building the JAR file using Maven with mvn install:

[INFO] --- install:3.1.2:install (default-install) @ accessconverter --- [INFO] Installing \Java\AccessConverter\pom.xml to ~.m2\repository\com\lytrax\accessconverter\2.0\accessconverter-2.0.pom [INFO] Installing \Java\AccessConverter\target\accessconverter-2.0.jar to ~.m2\repository\com\lytrax\accessconverter\2.0\accessconverter-2.0.jar [INFO] Installing \Java\AccessConverter\target\accessconverter-2.0-jar-with-dependencies.jar to ~.m2\repository\com\lytrax\accessconverter\2.0\accessconverter-2.0-jar-with-dependencies.jar [INFO] ------------------------------------------------------------------------ [INFO] BUILD SUCCESS [INFO] ------------------------------------------------------------------------ [INFO] Total time: 7.434 s [INFO] Finished at: 2024-08-01T11:10:06+03:00 [INFO] ------------------------------------------------------------------------

❯ java -version openjdk version "17.0.11" 2024-04-16 LTS OpenJDK Runtime Environment Microsoft-9388408 (build 17.0.11+9-LTS) OpenJDK 64-Bit Server VM Microsoft-9388408 (build 17.0.11+9-LTS, mixed mode, sharing)

Can you try maven install on an environment with JDK 17? I would build a docker command and a Github action to create artifacts, but unfortunately I don't have much time.

— Reply to this email directly, view it on GitHub https://github.com/clytras/AccessConverter/issues/1#issuecomment-2262333362, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACE3UNFFDL3ABAXWBSQUMOTZPHVKFAVCNFSM6AAAAABLBNXTHGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDENRSGMZTGMZWGI . You are receiving this because you were mentioned.Message ID: @.***>

[clytras]

The PKs are converted to UNIQUE index for now. I will try to make them a composite PK but it's the auto increment keys that get in the way. I'll figure this out and I'll let you know.

Regarding the MEMORY pragma for journal_mode, I wouldn't make this to be the default behavior, because it might cause RAM allocation issues when processing huge files, maybe we can make these optional and create some comand line arguments to alter them on demand.

[platipusica]

Cool. I'm using it for Jam.py migration. Since Jam requires a PK auto increment, that would mean a World since 99% migrated Access DB's have no PK. Would not hesitate at all to add a PK to those tables without it.

If you are interested, there are some Jam Greek users in here: Jam.py Users Mailing List - Google Groups https://groups.google.com/g/jam-py?pli=1

Cheers

On Thu, Aug 1, 2024 at 1:23 PM Christos Lytras @.***> wrote:

The PKs are converted to UNIQUE index for now. I will try to make them a composite PK but it's the auto increment keys that get in the way. I'll figure this out and I'll let you know.

Regarding the MEMORY pragma for journal_mode, I wouldn't make this to be the default behavior, because it might cause RAM allocation issues when processing huge files, maybe we can make these optional and create some comand line arguments to alter them on demand.

— Reply to this email directly, view it on GitHub https://github.com/clytras/AccessConverter/issues/1#issuecomment-2262798098, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACE3UNEFH3HNOEUGKGF6GD3ZPILDDAVCNFSM6AAAAABLBNXTHGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDENRSG44TQMBZHA . You are receiving this because you were mentioned.Message ID: @.***>

[clytras]

The problem is that SQLite does not support multiple primary keys if there is an auto increment column ID, that's why I made it creating a unique index instead.

We should define a behavior.

For example,

1. when there is an autoincrement PK with multiple other PKs, then it can only create a unique index like it does now
2. when there is no autoincrement PK, then all PKs can co-exists in a composite PK regardless of column types

Also, add autoincrement PKs if the access tables don't have such columns, which, if there are more PKs, then all will have a common unique index. This can be optional using a command line parameter.