-
-
Save bennadel/17bdfbdb06e86b366c334c4980cd219b to your computer and use it in GitHub Desktop.
Experimenting With SQLite JDBC Connections In Lucee CFML
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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" | |
}; | |
} |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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" | |
}; | |
} |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<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> |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
} | |
} |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 ); | |
} | |
} | |
} |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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> | |
``` | |
} | |
} |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<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> | |
← <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> |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<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