Skip to content

Instantly share code, notes, and snippets.

@bennadel
Created May 12, 2024 11:56
Show Gist options
  • Save bennadel/17bdfbdb06e86b366c334c4980cd219b to your computer and use it in GitHub Desktop.
Save bennadel/17bdfbdb06e86b366c334c4980cd219b to your computer and use it in GitHub Desktop.
Experimenting With SQLite JDBC Connections In Lucee CFML
component {
this.name = "ColdFusionSqlite";
this.applicationTimeout = createTimeSpan( 1, 0, 0, 0 );
this.sessionManagement = false;
this.setClientCookies = false;
// A SQLite database connection pointing to `test.db`.
this.datasources[ "test" ] = {
class: "org.sqlite.JDBC",
connectionString: "jdbc:sqlite:/var/www/test.db"
};
}
component {
this.name = "ColdFusionSqlite";
this.applicationTimeout = createTimeSpan( 1, 0, 0, 0 );
this.sessionManagement = false;
this.setClientCookies = false;
// In order to decouple the ColdFusion application from the physical location of the
// SQLite databases, we're going to use a per-application mapping.
this.mappings = {
"/databases": "/var/www-databases"
};
}
bennadel@KXYFL7MY7G databases % pwd
/Users/bennadel/Sites/ColdFusion-Sqlite/databases
bennadel@KXYFL7MY7G databases % ls -l
total 80
-rw-r----- 1 bennadel staff 8192 May 12 07:00 master.db
-rw-r----- 1 bennadel staff 8192 May 12 07:15 user_1.db
-rw-r----- 1 bennadel staff 8192 May 12 07:16 user_2.db
-rw-r----- 1 bennadel staff 8192 May 12 07:15 user_3.db
-rw-r----- 1 bennadel staff 8192 May 12 07:15 user_4.db
version: "2.4"
services:
cfml:
build: "."
ports:
- "8888:8888"
volumes:
- ./app:/var/www
# Don't put the databases in a publicly accessible directory!
- ./databases:/var/www-databases
FROM lucee/lucee:5.4.4.38
# Setup the SQLite JDBC driver.
COPY ./docker/cfml/lib/sqlite-jdbc-3.45.3.0.jar /opt/lucee/server/lucee-server/bundles
# Note: We didn't need to copy the SLF4J JAR because Lucee already has one.
# COPY ./docker/cfml/lib/slf4j-api-1.7.36.jar /opt/lucee/server/lucee-server/bundles
<cfscript>
param name="form.action" type="string" default="";
param name="form.userID" type="numeric" default=0;
param name="form.userName" type="string" default="";
// ------------------------------------------------------------------------------- //
// ------------------------------------------------------------------------------- //
// In the root page, we're going to use a master database to track the list of users.
// Then, each user will receive their OWN DATABASE to record tasks.
masterDAO = new lib.MasterDAO();
masterDAO.ensureDatabase();
// ------------------------------------------------------------------------------- //
// ------------------------------------------------------------------------------- //
if ( form.action.len() ) {
// CAUTION: I'm not doing any data massaging or validation here - I just want to
// keep the code really simple so as to focus on the SQLite aspects.
switch ( form.action ) {
case "addUser":
masterDAO.addUser( form.userName );
break;
case "deleteUser":
user = masterDAO
.getUsersByFilter( id = val( form.userID ) )
.first()
;
userDAO = new lib.UserDAO( user.id );
userDAO.dropDatabase();
masterDAO.deleteUsersByFilter( id = user.id );
break;
}
// BEST PRACTICE: Always refresh the page so that a CMD+R doesn't resubmit the
// form and perform the same action twice.
location(
url = "./index.cfm",
addToken = false
);
}
// ------------------------------------------------------------------------------- //
// ------------------------------------------------------------------------------- //
users = masterDAO.getUsersByFilter();
</cfscript>
<cfoutput>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<link rel="stylesheet" type="text/css" href="./public/main.css" />
</head>
<body>
<h1>
Lucee CFML SQLite Exploration
</h1>
<form method="post">
<input
type="text"
name="userName"
placeholder="Name..."
value=""
autofocus
autocomplete="off"
size="20"
data-1p-ignore
/>
<button type="submit" name="action" value="addUser">
Add User
</button>
</form>
<h2>
Users
</h2>
<cfloop index="i" value="user" array="#users#">
<form method="post" class="item">
<input
type="hidden"
name="userID"
value="#encodeForHtmlAttribute( user.id )#"
/>
<a href="./tasks.cfm?userID=#encodeForUrl( user.id )#">
#encodeForHtml( user.name )#
</a>
<button
type="submit"
name="action"
value="deleteUser"
class="push-right">
Delete
</button>
</form>
</cfloop>
<cfif ! users.len()>
<p>
<em>No users.</em>
</p>
</cfif>
</body>
</html>
</cfoutput>
component
extends = "SQLiteDAO"
hint = "I provide data access methods for the master database (ie, the list of users)."
{
/**
* I initialize the data access object (DAO) for the root experience.
*/
public void function init() {
super.init( "master.db" );
}
// ---
// PUBLIC METHODS.
// ---
/**
* I add a new user with the given name. The auto-generated ID is returned.
*/
public numeric function addUser( required string name ) {
```
<cfquery name="local.results" result="local.metaResults" datasource="#datasource#">
/* DEBUG: masterDAO.addUser(). */
INSERT INTO user
(
name
) VALUES (
<cfqueryparam value="#name#" sqltype="varchar" />
)
RETURNING
ROWID
;
</cfquery>
```
// NOTE: SQLite doesn't return a "generatedKey". However, when a query ends with
// "RETURNING ROWID", it will return a query with a single column, id, which
// contains the generated value.
return val( results.id );
}
/**
* I delete the users that match the given filters.
*/
public void function deleteUsersByFilter(
numeric id
) {
```
<cfquery name="local.results" result="local.metaResults" datasource="#datasource#">
/* DEBUG: masterDAO.deleteUsersByFilter(). */
DELETE FROM
user
WHERE
TRUE
<cfif arguments.keyExists( "id" )>
AND
id = <cfqueryparam value="#id#" sqltype="integer" />
</cfif>
;
</cfquery>
```
}
/**
* I ensure that the database tables exist.
*/
public void function ensureDatabase() {
```
<cfquery name="local.results" result="local.metaResults" datasource="#datasource#">
/* DEBUG: masterDAO.ensureDatabase(). */
CREATE TABLE IF NOT EXISTS `user` (
`id` INTEGER PRIMARY KEY,
`name` TEXT NOT NULL
);
</cfquery>
```
}
/**
* I get the users that match the given filters.
*/
public array function getUsersByFilter(
numeric id
) {
```
<cfquery name="local.results" result="local.metaResults" datasource="#datasource#" returnType="array">
/* DEBUG: masterDAO.getUsersByFilter(). */
SELECT
u.id,
u.name
FROM
user u
WHERE
TRUE
<cfif arguments.keyExists( "id" )>
AND
u.id = <cfqueryparam value="#id#" sqltype="integer" />
</cfif>
ORDER BY
u.id ASC
;
</cfquery>
```
return results;
}
}
component
output = false
hint = "I provide the abstract base component for a SQLite data access layer."
{
/**
* I initialize the data access object (DAO) with the given database file.
*/
package void function init( required string sqliteFilename ) {
variables.sqliteFilename = arguments.sqliteFilename;
// NOTE: We're using a per-application mapping to decouple the actual storage
// location of the database files. That's why we're using expandPath() - so that
// Lucee resolves to server's root path.
variables.sqliteFilepath = expandPath( "/databases/#sqliteFilename#" );
variables.datasource = {
class: "org.sqlite.JDBC",
connectionString: "jdbc:sqlite:#sqliteFilepath#"
};
}
// ---
// PUBLIC METHODS.
// ---
/**
* I determine if the physical SQLite database file exists.
*/
public boolean function databaseExists() {
return fileExists( sqliteFilepath );
}
/**
* I delete the file representing the current database.
*/
public void function dropDatabase() {
if ( databaseExists() ) {
fileDelete( sqliteFilepath );
}
}
}
component
extends = "SQLiteDAO"
hint = "I provide data access methods for a specific user database."
{
/**
* I initialize the data access object (DAO) for the given user.
*/
public void function init( required numeric userID ) {
// Each user gets their own, unique SQLite database.
super.init( "user_#userID#.db" );
}
// ---
// PUBLIC METHODS.
// ---
/**
* I add a new task with the given name. The auto-generated ID is returned.
*/
public numeric function addTask( required string name ) {
```
<cfquery name="local.results" result="local.metaResults" datasource="#datasource#">
/* DEBUG: userDAO.addTask(). */
INSERT INTO task
(
name,
isComplete,
createdAt
) VALUES (
<cfqueryparam value="#name#" sqltype="varchar" />,
FALSE,
datetime( "now" )
)
RETURNING
ROWID
;
</cfquery>
```
// NOTE: SQLite doesn't return a "generatedKey". However, when a query ends with
// "RETURNING ROWID", it will return a query with a single column, id, which
// contains the generated value.
return val( results.id );
}
/**
* I delete the tasks that match the given filters.
*/
public void function deleteTasksByFilter(
numeric id,
boolean isComplete
) {
```
<cfquery name="local.results" result="local.metaResults" datasource="#datasource#">
/* DEBUG: userDAO.deleteTasksByFilter(). */
DELETE FROM
task
WHERE
TRUE
<cfif arguments.keyExists( "id" )>
AND
id = <cfqueryparam value="#id#" sqltype="integer" />
</cfif>
;
</cfquery>
```
}
/**
* I ensure that the database tables exist.
*/
public void function ensureDatabase() {
```
<cfquery name="local.results" result="local.metaResults" datasource="#datasource#">
/* DEBUG: userDAO.ensureDatabase(). */
CREATE TABLE IF NOT EXISTS `task` (
`id` INTEGER PRIMARY KEY,
`name` TEXT NOT NULL,
`isComplete` INTEGER NOT NULL CHECK ( isComplete IN ( 0, 1 ) ),
`createdAt` TEXT NOT NULL
);
</cfquery>
```
}
/**
* I get the tasks that match the given filters.
*/
public array function getTasksByFilter(
numeric id,
boolean isComplete
) {
```
<cfquery name="local.results" result="local.metaResults" datasource="#datasource#" returnType="array">
/* DEBUG: userDAO.getTasksByFilter(). */
SELECT
t.id,
t.name,
t.isComplete,
t.createdAt
FROM
task t
WHERE
TRUE
<cfif arguments.keyExists( "id" )>
AND
t.id = <cfqueryparam value="#id#" sqltype="integer" />
</cfif>
<cfif arguments.keyExists( "isComplete" )>
AND
t.isComplete = <cfqueryparam value="#isComplete#" sqltype="integer" />
</cfif>
ORDER BY
t.id ASC
;
</cfquery>
```
return results;
}
/**
* I update the task with the given ID.
*/
public void function updateTask(
required numeric id,
boolean isComplete
) {
```
<cfquery name="local.results" result="local.metaResults" datasource="#datasource#">
/* DEBUG: userDAO.updateTask(). */
UPDATE
task
SET
<cfif arguments.keyExists( "isComplete" )>
isComplete = <cfqueryparam value="#isComplete#" sqltype="integer" />,
</cfif>
id = id
WHERE
id = <cfqueryparam value="#id#" sqltype="integer" />
;
</cfquery>
```
}
}
<cfscript>
param name="url.userID" type="numeric" default=0;
param name="form.action" type="string" default="";
param name="form.taskID" type="string" default=0;
param name="form.taskName" type="string" default="";
// ------------------------------------------------------------------------------- //
// ------------------------------------------------------------------------------- //
users = new lib.MasterDAO()
.getUsersByFilter( id = val( url.userID ) )
;
if ( ! users.len() ) {
location(
url = "./index.cfm",
addToken = false
);
}
user = users.first();
userDAO = new lib.UserDAO( user.id );
userDAO.ensureDatabase();
// ------------------------------------------------------------------------------- //
// ------------------------------------------------------------------------------- //
if ( form.action.len() ) {
// CAUTION: I'm not doing any data massaging or validation here - I just want to
// keep the code really simple so as to focus on the SQLite aspects.
switch ( form.action ) {
case "addTask":
userDAO.addTask( form.taskName );
break;
case "deleteTask":
userDAO.deleteTasksByFilter( id = val( form.taskID ) );
break;
case "toggleTask":
task = userDAO
.getTasksByFilter( id = val( form.taskID ) )
.first()
;
userDAO.updateTask(
id = task.id,
isComplete = ! task.isComplete
);
break;
}
// BEST PRACTICE: Always refresh the page so that a CMD+R doesn't resubmit the
// form and perform the same action twice.
location(
url = "./tasks.cfm?userID=#encodeForUrl( user.id )#",
addToken = false
);
}
// ------------------------------------------------------------------------------- //
// ------------------------------------------------------------------------------- //
tasks = userDAO.getTasksByFilter();
</cfscript>
<cfoutput>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<link rel="stylesheet" type="text/css" href="./public/main.css" />
</head>
<body>
<h1>
#encodeForHtml( user.name )#
</h1>
<p>
&larr; <a href="./index.cfm">Back to users</a>
</p>
<form method="post" action="./tasks.cfm?userID=#encodeForUrl( user.id )#">
<input
type="text"
name="taskName"
placeholder="Task..."
value=""
autofocus
autocomplete="off"
size="40"
data-1p-ignore
/>
<button type="submit" name="action" value="addTask">
Add Task
</button>
</form>
<h2>
Tasks
</h2>
<cfloop index="i" value="task" array="#tasks#">
<form method="post" action="./tasks.cfm?userID=#encodeForUrl( user.id )#" class="item">
<input
type="hidden"
name="taskID"
value="#encodeForHtmlAttribute( task.id )#"
/>
<button
type="submit"
name="action"
value="toggleTask">
Toggle
</button>
<span data-completed="#yesNoFormat( task.isComplete )#">
#dateFormat( task.createdAt, "yyyy-mm-dd" )#:
#encodeForHtml( task.name )#
</span>
<button
type="submit"
name="action"
value="deleteTask"
class="push-right">
Delete
</button>
</form>
</cfloop>
<cfif ! tasks.len()>
<p>
<em>No tasks.</em>
</p>
</cfif>
</body>
</html>
</cfoutput>
<cfquery name="results" datasource="test">
SELECT
( 1 ) AS helloWorld
;
</cfquery>
<cfdump var="#results#" />
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment