SQLite or other minimal-install db

dev
1

I’m thinking about a standalone desktop app, built in Lucee, that needs a simple db. I was thinking of using SQLite, for its simplicity and robustness, but I’m not sure how to make use of it from Lucee.

I found a SQLite CFC that appears to be part of the Lucee docs build process, and another older one that’s designed more to create CF datasources for SQLite dbs, as well as init a new db.

I haven’t worked with SQLite at all, from CF or any other way, just assumed it could be queried using standard SQL and cfquery. A quick scan through the Lucee docs CFC makes it seem like that that’s not the case, but the older one looks like it’s designed for exactly that.

Apparently ACF can access SQLite dbs if the JDBC driver is in the right location, but I don’t know if that’s true for Lucee too.

Anyone have experience doing this? What’s the recommended approach?

2

I always use H2 for standalone stuff on Lucee. It’s fast, in memory, and supports Hibernate which is really nice since ORM apps like ContentBox CMS can run on it.

1 Like
3

Thanks for the steer Brad. I haven’t worked w H2. Does it need to be installed, or can I ship it with the app?

The app is for non-developers, so ideally the jar or dll or whatever it is just comes with the app, users don’t need to know or do anything about it.

When you say in-memory, it still can write to disk I assume, it’s not only in your session, yes?

And yes I’ll R some FM, just trying to shortcut the decision process.

4

Does it need to be installed, or can I ship it with the app?

You don’t need to do anything to install H2 in Lucee, it comes bundled. I think that’s still true of Lucee 5.

When you say in-memory, it still can write to disk I assume

Yes, data is persisted.

Here’s an excerpt from an Application.cfc for a standalone site that used H2

this.datasources[ "myDSN" ] = {
	class 			: 'org.hsqldb.jdbcDriver',
	connectionString: 'jdbc:hsqldb:file:#COLDBOX_APP_ROOT_PATH#/config/ortopolis/ortopolis'
};

Note that was for Lucee 4. It may be a little different for Lucee 5 since the class comes from an OSGI bundle. Either way, add the datasource in your admin, then edit it and scroll to the bottom. Everything you need will be right there to copy paste it into your Application.cfc.

Also, another way to package your site’s settings (including datasources) is to export all your configuration to a .CFconfig.json file and distribute the site with CommandBox and the CFConfig module, which will load all the configuration in on startup automatically.

1 Like
5

For a CommandBox Lucee 5.2.2 install, it appears you have to install the H2 extension; that’s why I didn’t see it in the admin. What do you think the best approach would be for a self-contained end-user (non-developer) desktop app I’d like to distribute?

6

I think CommandBox would be your best bet. It can run off a thumb drive and be fully self contained. You can even get control over the menu items that show up for your servers. (pre-install a module that listens to onServerStart and modifies the menu JSON). You can script out all your settings and even datasource configs and open up whatever URL you want when the server starts. Heck, you can even customize the tray icon.

To pre-install extensions into Lucee 5 servers, add the JVM arg set out in this doc:
http://docs.lucee.org/guides/running-lucee/configuring-lucee/system-properties.html

1 Like
7

Thanks again Brad. Plan was to use CommandBox, I just didn’t know you could pre-install extensions too, very cool, need to investigate…

8

Jan jannek also made a mariadb commandbox command which has mariadb self contained In a jar

1 Like
9

Back at this again, still lost in unfamiliar space, thanks in advance for any help.

  • The org.hsqldb.jdbcDriver class given above appears to belong to HSQLDB, not H2. For H2 I think it’s org.h2.Driver.

  • Could you please be more specific when you say, “To pre-install extensions into Lucee 5 servers, add the JVM arg set out in this doc”. I see this: 'lucee-extensions - Commma-delimited list of GUID IDs that correspond to extensions Lucee should install automatically", and I have the ID for H2 from that link (465E1E35-2425-4F4E-8B3FAB638BD7280A), but I’m unclear as to where to put that info for a CommandBox Lucee instance I want to package as a standalone app.

  • Somewhat but not entirely related, as a test, I installed the H2 extension through Lucee Admin, created a datasource there, copied the settings from the Admin into Application.cfc, and it worked perfectly after removing the datasource from the Admin. I then had CommandBox upgrade to latest Lucee 5 (was 4.x), and now that same datasource gives this error:

Bundle symbolic name and version are not unique: org.lucee.commons.compress:1.9.0

What does that mean, and what’s the recommended way forward from here?

Thanks again, and apologies for all the questions.