SQLite Adapter

SQLite Adapter for NexJ

Project Goal

Develop an adapter to enable NexJ Express's model interaction with SQLite databases.

Contributors

• Carl Desautels
• Grace Batumbya
• Abhishek Bhatnagar
• Wei Song

Supported Versions

• sqlite.3.7.10
  • Compiled with the following flags:
    • SQLITE_ENABLE_LOAD_EXTENSION=1
    • SQLITE_ENABLE_UPDATE_DELETE_LIMIT
    • SQLITE_ENABLE_COLUMN_METADATA
    • SQLITE_CORE
    • SQLITE_ENABLE_FTS4
    • SQLITE_ENABLE_FTS3_PARENTHESIS
    • SQLITE_ENABLE_RTREE
    • SQLITE_ENABLE_STAT3
    • SQLITE_SOUNDEX=1
    • SQLITE_MAX_VARIABLE_NUMBER=4000
    • SQLITE_USE_URI
    • SQLITE_ENABLE_ICU
    • SQLITE_DEFAULT_FOREIGN_KEYS=1
    • DSQLITE_THREADSAFE=1

Project Status

• Current Sprint : Code Review 1

• Stand Up Reports

Project Backlog

Investigation (DONE)

• Research SQLite / JDBC driver
• Get requirements from NexJ (Meeting Friday Sept 23rd)
• Research Summary

Sprint: 1

User Story	Score	Details
Initial skeleton of the SQLite adapter	2	Running the jUnit test shows in the console that the adapter is loaded an running.
Use database schema tool to generate the SQL to setup the database	1	Running the DB Schema Tool's "setup" command, appropriate artifacts are created (e.g. sql or bat script) for generating the SQLite database.
Use database schema tool to generate the SQL to create the Core datasource's tables	5	Running the DB Schema Tool's "create" command, appropriate artifacts are created (e.g. a sql file) for creating the tables in an existing SQLite database.

Sprint 2

User Story	Score	Details
Use data schema tool to generate the SQL to insert the data from a dump file	0	Running the DB schema Tool's 'insert' command, appropriate artifacts are created in the database
Use the scheme console to insert into and read from the database	0	Running the schema console, perform multi-transactional inserts with commits and rollbacks. Reading from the database to confirm updates
Use the data load tool to extract information in a SQLite database and persist it to a dump file	0	Running the data load tool, read information from the database and export it to a dump file
Use the database schema tool to create an insert script for all the instances in in a SQLite database	0	Running the data load tool, read in information from a dump file
Add an attribute to the User class, add the column to the datasource and Main.upgrade. Use the data load tool to upgrade the SQLite database. The database now has the new column add to the User Table	3	Running the database schema tool alter the user class and perform an upgrade
Run each of the data load tool commands to validate their use	5	Running all of the data load tool's commands, checking for expected results
Run each of the database schema tool commands to validate their use	5	Running all of the database schema tools commands, checking for expected results

Sprint 3

User Story	Score	Details
Retrieve a record using fulltext search in the where clause	0	Using the SQlite Adapter perform a query to verify the full test search options have been configured properly
Demonstrate unicode support in sqlite functions	5	Using the SQlite Adapter perform a query to verify unicode support is as expected in sqlite functions
Run all SQLiteSchemaManager unit tests, to validate correctness	2	Using studio perform all the tests as Junit tests, ensuring no failures.
Run all the SQLiteAdapter unit tests, to validate correctness	7	Using studio perform all the tests as Junit tests, ensuring no failures.

Project Repository

• Central: https://bitbucket.org/cwdesautels/nexj-express-sqlite-adapter

Building JDBC from source

• HowTo

Resources

• Dowloads:
  • http://www.sqlite.org/download.html
• Documentation:
  • Root - http://www.sqlite.org/docs.html
  • About - http://www.sqlite.org/about.html
  • Types - http://www.sqlite.org/datatype3.html
  • Limits - http://www.sqlite.org/limits.html
  • Null Handling - http://www.sqlite.org/nulls.html
  • Omitted Features - http://www.sqlite.org/omitted.html
  • SQL to SQLite - http://www.sqlite.org/lang_expr.html
  • API - http://www.sqlite.org/c3ref/intro.html
• QuickStart:
  • Usage - http://www.sqlite.org/cintro.html
  • C Interface - http://www.sqlite.org/cintro.html
• SQLite JDBC Driver:
  • (Unoffical) http://www.xerial.org/trac/Xerial