search

ultorg / public_issues

Public issue tracker for the Ultorg Desktop application
12 stars 0 forks source link

SQLite support in Ultorg #1

Open eirikbakke opened 2 years ago

eirikbakke commented 2 years ago

Several users have asked to add support for SQLite in Ultorg.

Ultorg 1.0.6 has some experimental support for SQLite connections, though it has not yet been exposed in the user interface. SQLite support can be enabled as follows:

1) Download the Xerial SQLite JDBC driver (sqlite-jdbc-3.36.0.3.jar) from https://github.com/xerial/sqlite-jdbc . 2) In Ultorg, click Internals→Services→Databases (expand)→Drivers (right-click)→New Driver. 3) Click "Add" and locate the JDBC driver JAR file you downloaded in step (1). 4) Press OK. 5) Now click back on the "Folders" tab in the sidebar. 6) Click "Add Data Source" and "Connect to Database...", then select "SQLite" as the driver and "Next". 7) In the "JDBC URL" field, replace "" with the path to the SQLite file you'd like to connect to. E.g. " jdbc:sqlite:C:\Users\myusername\somedatabase.db".

The main limitation of experimental SQL dialects is that Ultorg has not yet been taught to translate every function in Ultorg's formula language to the vendor-specific SQL dialect.

SQLite also has some additional peculiarities due to the fact that columns are not strongly typed; for example, you may not be able to select filter values that are of a type that is different from the column's declared type (e.g. text values in an INTEGER column). A workaround for the latter is to create a formula field that wraps the value in the TEXT function, to convert all values to strings. Then you can filter on the formula field instead.

malcook commented 2 years ago

FWIW - the above instructions worked perfectly for this beta user.

eirikbakke commented 2 years ago

@malcook Great! I've had quite a few people ask about SQLite, so I might make this a bit easier in the future. There are a few other things that need to be implemented on SQLite, such as support for the date/time functions (e.g. to convert from UNIX epoch times or julian times to real displayable timestamps, as mentioned in https://github.com/ultorg/public_issues/issues/15 ).

scottrblock commented 2 years ago

this worked for me too @eirikbakke!

eirikbakke commented 2 years ago

Great, @scottrblock! Do let me know if there are specific features not working in SQLite that you'd like to see fixed.