1

I downloaded and installed the SQLite ODBC Driver from http://ch-werner.de/sqliteodbc/. Works great with one issue. I can't seem to get an exclusive lock if another app has the database open.

I'm using a CAD application called Altium. I'm trying to build a prototype parts database in SQLITE to house data. I installed the ODBC driver referenced above and I can get to the data just fine. The issue is if I try to open the same database in an app like SQLITEStudio or DB Browser for SQLITE I can never get an exclusive lock to write new records. Altium never closes its connection and I don't know if that's the problem or not.

When I read https://www.sqlite.org/lockingv3.html it seems to indicate that multiple processes would need to communicate in order for one to obtain an exclusive lock (necessary for a write). This leads me to believe my issue is the first app never releasing it's shared lock so the second app can never get an exclusive lock. Am I interpreting this correctly?

user4157124
  • 2,809
  • 13
  • 27
  • 42
Shaolo
  • 1,180
  • 11
  • 13
  • 1
    It's possible that Altium keeps a lock to prevent other applications (like yours) from modifying the file. – CL. Jul 23 '17 at 19:54
  • That’s what I suspect. I’m wondering if there is a way to prevent that in the ODBC driver itself. It works correctly with MS Access. But that’s not saying much. – Shaolo Jul 23 '17 at 21:35
  • Altium is unlikely to use the ODBC driver. – CL. Jul 24 '17 at 06:37
  • 1
    Altium works fine. The issue is I can't get the lock to edit the data outside of Altium w/o shutting down Altium. If I didn't need to add/update parts with Altium open it would be a non issue. Until the developers modify Altium to release it's lock when it's not using the database this does not appear to be possible with SQLITE. – Shaolo Jul 25 '17 at 07:02

1 Answers1

1

I'm experiencing the same difficulties with SQLite as an Altium database. The database file can't be updated while Altium is open and had created a connection to the database. There are two workarounds I've found so far:

Enable Write Ahead Logging style database journal

The default database journal style, DELETE, creates a copy of the database, temporarily, writes to the database file, and, when the write is verified, deletes the temporary copy.

Write ahead logging, WAL, puts all changes in a temporary file while leaving the original copy of the database alone. Processes accessing the database parse the database file and the WAL temporary files, and return information reflecting all changes. I can confirm that writes outside of Altium are seen when doing a database refresh within Altium.

The writes stored in the WAL file are written to the original database when a checkpoint operation happens. Either when a process calls PRAGMA wal_checkpoint; or after a number of operations specified by the WAL Auto Checkpoint setting.

You can enable WAL journal mode in DB Browser's Edit Pragmas tab:

DB Browser Database Settings

The problem still isn't fixed, though. Checkpointing the database doesn't work while Altium is open. The database file is still locked from changes.

The caveat for us is that we want to backup and maintain a history of the database file in Git (yes, this is not a recommended use of Git). Writes to the journal files aren't tracked in Git, which doesn't see a change in the database file until it is checkpointed. This means that we can't push or pull from Git while Altium is open. We could track the database and journal files, but the other consequence is that a simple checkpoint with no data changes will be seen by Git as a change.

Use an alternative ODBC driver

I tested an alternative, proprietary ODBC driver from devart. When I configure Altium to connect to the SQLite database using the devart driver, DB Browser has no problems writing to the file while Altium is open, regardless of journal settings. Altium can also see those changes with a refresh operation.

Conclusion

Christian Werner's ODBC driver (as of version 0.9996) is locking the file in a manner that the devart driver does not, even while Atlium maintains a connection to the database through the driver. WAL journal mode can be a workaround for some situations.

dsquires_safari
  • 11
  • 2
  • Is there any chance you can help me setup Altium with a Sqlite database? I'm struggling a lot but it seems you have succeeded in this already... – nickagian Apr 18 '20 at 00:08
  • There is an example SQLite database library here: https://github.com/mikef5410/Altium_PassiveSMT_DbLib The best place to ask for more specific help is here: https://forum.live.altium.com/#start (must have a login) – dsquires_safari Apr 20 '20 at 12:21