SQLite database

Structure

Database file(s) (main/temp/attached) → Tables/Objects

• Database: SQLite is just a single-file database. When you connect, you are in one database. There’s no concept of multiple independent databases inside one connection.

• Schema: There is no concept of "schema" in SQLite, but it can be simulated by "attaching" databases. SQLite supports:

  • main (the connected file)

  • temp (temporary objects)

  • any attached databases (via ATTACH DATABASE 'file2.db' AS otherdb;)

  These act "like schemas" in terms of naming (main.table1, otherdb.table2), but they’re really separate database files.

Telosys typical configuration for a SQLite database

  - id: sqlite
    name: SQLite database 
    type: SQLITE 
    # JDBC config 
    driver: org.sqlite.JDBC
    url: jdbc:sqlite:D:\Z\DB-DATA\SQLite-data\sqlite-db-example.db
    # user: 
    # password: 
    # Metadata parameters
    tableNamePattern: '%'
    tableTypes: TABLE

JDBC driver

Download from https://github.com/xerial/sqlite-jdbc

JAR file example: sqlite-jdbc-3.50.3.0.jar

Boolean type

SQLite does not have a native BOOLEAN type.

It uses dynamic typing: a column can store any value regardless of declared type. Conventionally, booleans are stored as: 0 → false and 1 → true You can declare a column as BOOLEAN, INTEGER, or NUMERIC; SQLite just treats it as an affinity, not a strict type.

If you use Hibernate with SQLite dialect, a boolean or Boolean in your entity is usually mapped to INTEGER (or NUMERIC) in the DB. Hibernate automatically persists: true → 1 and false → 0 Reading from the column is automatically converted back to boolean. So no converter is needed for standard 0/1 boolean storage.

Notes

• Authentication: By default, SQLite databases are just files (.db or .sqlite) on disk. Anyone who can read/write the file has full access to the database.

• No "catalog" and "schema" There is no concept of schema and catalog in SQLite When Telosys retrieves the database model, 'catalog' and 'schema' are always 'null'.

• In-memory database It's possible to work only "in-memory". The database exists only in RAM, not on disk. To do so use JDBC URL like this: "jdbc:sqlite::memory:" NB: duration=connexion, as soon as the connection is closed, the entire database (all tables) disappears. SQLite supports a mode that allows multiple connections to share the same in-memory database: "jdbc:sqlite:file:memdb1?mode=memory&cache=shared" it will create an in-memory database named "memdb1" (this will not create any files on disk) But even with this mode a shared in-memory database only exists while at least one connection to it is still open. As soon as the last connection closes, SQLite frees the memory, and all tables vanish.