Connecting SQLite in Panopticon Real-Time

Clinton Chee_22243 · April 2022

Panopticon connects to the SQLite database using JDBC drivers, just like many of the traditional RDBMS. This blog will provide a write-up on the one-time installation of the JDBC driver, followed by an example of making the connection to SQLite in the Panopticon Data Table interface.

SQLite
A brief introduction to SQLite is that it is a DB that is unlike the other DBs because the SQLite is a direct file-based DB. Its main site, https://sqlite.org/, explains a bit more:
"SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day.
The SQLite file format is stable, cross-platform, and backwards compatible and the developers pledge to keep it that way through the year 2050. SQLite database files are commonly used as containers to transfer rich content between systems [1] [2] [3] and as a long-term archival format for data [4]. There are over 1 trillion (1e12) SQLite databases in active use [5].
SQLite source code is in the public-domain and is free to everyone to use for any purpose."

Although it can be used via many languages, SQLite is pre-packaged for Python users hence there is no separate installation required in Python.

JDBC driver
The creators at sqlite.org does not provide the JDBC drivers themselves. Instead a widely used JDBC driver implementation is obtained from:
https://github.com/xerial/sqlite-jdbc/releases
The actual filename of the JDBC library is: sqlite-jdbc-3.36.0.3.jar
Being a Java library, it works both for Windows and Linux.

JDBC driver installation on Panopticon
The instructions here are given for Linux, but the Windows configuration would be similar.
Assume that the Apache Tomcat web server that runs Panopticon is installed at $TOMCAT_HOME.
So, copy sqlite-jdbc-3.36.0.3.jar onto $TOMCAT_HOME/lib/

Ensure ownership AND permissions are correct (depends on your system). The commands below are run as the root user.
cd $TOMCAT_HOME/lib/
chown tomcat:tomcat sqlite-jdbc-3.36.0.3.jar
chmod 644 sqlite-jdbc-3.36.0.3.jar # (rw-r--r--)

In this case, the Tomcat web server is running as the user called 'tomcat'.

Restart the Tomcat server (notify other users if necessary):
systemctl restart tomcat

Preparing the SQLite test data called test.db
A simple Python script, test_sqlite.py is used to generate a very simple table to be used for this test. The actual code is given below. The result of this code is to create the DB called test.db.

One of tricky points of failure in the configuration is the file permissions of the SQLite DB, test.db. The test.db needs to have the appropriate file permissions to be read by the TOMCAT user. For testing, test.db can be made to be totally exposed, i.e. permissions 777.

The test.db file needs to be in an area accessible by the user whom TOMCAT service is running under.
E.g. this will fail if the file is /home/bob/test.db while the user running TOMCAT service is 'tomcat'. This will FAIL even if test.db has 777 permissions, because 'tomcat' cannot read the /home/bob/ directory. The easiest way for this test is to put it where everyone can access: /tmp/test.db

Creating the connection in Panopticon
The general information for connecting to DBs using JDBC in Panopticon, is found in the Panopticon manuals and will not be repeated here.
The details here are specifically for SQLite and at the JDBC connector, the following fields are necessary:

Connection Settings -> choose URL (not JNDI) -> enter "jdbc:sqlite:/tmp/test.db"
Note the test database is located at /tmp/test.db

Driver Class Name: "org.sqlite.JDBC"

SqlDialect: "SQLite"

Click on the Load button. Then the Table drop down will be filled by the word 'COMPANY'

Click Generate Columns. This will show all the columns of the table.

Click the Query radio-button. The text box should say: SELECT * FROM 'COMPANY'

Click Refresh Preview. This will show the preview of the table.

########## Code for test_sqlite.py ##############

# Ref: https://www.tutorialspoint.com/sqlite/sqlite_python.htm
# JDBC from https://github.com/xerial/sqlite-jdbc/releases
import sqlite3

def dbconnect():
    conn = sqlite3.connect('test.db')
    print("Opened database successfully")
    return conn

# Create table schema
def dbcreate(conn):
    astr = '''CREATE TABLE COMPANY \
    (ID INT PRIMARY KEY NOT NULL,\
    NAME TEXT NOT NULL,\
    AGE INT NOT NULL,\
    ADDRESS CHAR(50),\
    SALARY REAL);''' 
    conn.execute(astr)
    print("Table created successfully")
    return

def dbinsert(conn):
    conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 )")
    conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 )")
    conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )")
    conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )")

    conn.commit()
    print("Records created successfully")
    return

def dbselect(conn):
    cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
    for row in cursor:
        print("ID = ", row[0])
        print("NAME = ", row[1])
        print("ADDRESS = ", row[2])
        print("SALARY = ", row[3], "\n")
   print("Operation done successfully")

if __name__ == '__main__':
    conn = dbconnect()
    dbcreate(conn)
    dbinsert(conn)
    dbselect(conn)
    # The End
    conn.close()
##########################################

Connecting SQLite in Panopticon Real-Time

Categories