<GeeXLab Rootard Guide/>

SQLite3

Last update: 2018.12.10 by JeGX

>> Back <<



SQLite3 is a very popular SQL database engine and most of all, very useful because it can be embedded in any application (the famous browser Firefox uses SQLite intensively). In addition, each SQLite3 database resides entirely in a single file.

For all these reasons, SQLite3 support has been added to GeeXLab since version 0.22.1.

SQLite3 can be used in three ways: either by using the gh_sqlite3 library (available in Lua and Python), or by using LuaSQL (integrated with GeeXLab), or by using the sqlite3 module in Python (#import sqlite3).

How to use gh_sqlite3 ?

We will see how to create a small test database stored in a file (database01.db). This database contains a single table (users) with two columns (name and age). We will use SQL queries to manipulate the database.

First some constants:

local SQLITE_OK = 0 
local SQLITE_ERROR = 1
local SQLITE_ROW = 100
local SQLITE_DONE = 101 
 
local SQLITE_INTEGER = 1
local SQLITE_FLOAT = 2
local SQLITE_TEXT = 3
local SQLITE_BLOB = 4

Let's start by creating the database file (or simply open it if it already exists):

local demo_dir = gh_utils.get_demo_dir()
local db_filename = demo_dir .. "database01.db"
local rc = SQLITE_OK
dbid, rc = gh_sqlite3.db_open(db_filename)

dbid is the identifier of the database that we will use in all the other functions of gh_sqlite3.

Let's now create the users table:

local sql = "DROP TABLE users"
if (gh_sqlite3.db_exec(dbid, sql) == SQLITE_ERROR) then
  print("SQL error 1")
end  
 
sql = "CREATE TABLE users (name varchar(64), age int not null)"
if (gh_sqlite3.db_exec(dbid, sql) == SQLITE_ERROR) then
  print("SQL error 1")
end

Now that the users table is created, we will fill it with some records:

sql = "INSERT INTO users (name, age) VALUES ('toto', 12);"
if (gh_sqlite3.db_exec(dbid, sql) == SQLITE_ERROR) then
  print("SQL error 2")
end
 
sql = "INSERT INTO users (name, age) VALUES ('John', 53);"
if (gh_sqlite3.db_exec(dbid, sql) == SQLITE_ERROR) then
  print("SQL error 2")
end
 
sql = "INSERT INTO users (name, age) VALUES ('Viktor', 48);"
if (gh_sqlite3.db_exec(dbid, sql) == SQLITE_ERROR) then
  print("SQL error 2")
end
 
sql = "INSERT INTO users (name, age) VALUES ('Duke', 85);"
if (gh_sqlite3.db_exec(dbid, sql) == SQLITE_ERROR) then
  print("SQL error 2")
end

The base is now operational. We will now read it with a nice SELECT query

local sql = "SELECT * FROM users"
if (gh_sqlite3.db_prepare(dbid, sql) == SQLITE_OK) then
  local num_columns = gh_sqlite3.db_get_column_count(dbid)
  print("# columns: " .. num_columns)
 
  local row = 1
  rc = gh_sqlite3.db_step(dbid)
  while (rc ~= SQLITE_DONE) do
 
    if (rc == SQLITE_ROW) then
      for c=0, num_columns-1 do
 
        local type = gh_sqlite3.db_get_column_type(dbid, c)
        if (type == SQLITE_TEXT) then
          local v = gh_sqlite3.db_column_get_text(dbid, c)
          print("Col. " .. c .. " | TEXT | " .. v)
        elseif  (type == SQLITE_INTEGER) then
          local v = gh_sqlite3.db_column_get_int(dbid, c)
          print("Col. " .. c .. " | INT | " .. v)
        elseif  (type == SQLITE_FLOAT) then
          local v = gh_sqlite3.db_column_get_double(dbid, c)
          print("Col. " .. c .. " | REAL | " .. v)
        elseif  (type == SQLITE_BLOB) then
          local blob_ptr, blob_size = gh_sqlite3.db_column_get_blob(dbid, c)
          print("Col. " .. c .. " | BLOB | " .. blob_size)
        end
      end
    end
 
    -- next result.
    rc = gh_sqlite3.db_step(dbid)
 
    if ((rc == SQLITE_ERROR) or (rc == SQLITE_DONE)) then
      break
    end
  end
 
  gh_sqlite3.db_finalize(dbid)
end

When the database is no longer needed, it can be closed properly with:

gh_sqlite3.db_close(dbid)

The BLOB field type deserves more interest. A BLOB field is used to store any type of data. For example, you can store the contents of an image file.

Let's quickly see how to store a .jpg image in a blob field. We will create an image table containing 3 fields: id, name and data. We will next insert a jpg image into the blob field.

local sql = "CREATE TABLE images (id INTEGER PRIMARY KEY, name varchar(64), data BLOB)"
if (gh_sqlite3.db_exec(dbid, sql) == SQLITE_ERROR) then
	print("SQL error 1")
end  

sql = "INSERT INTO images (name, data) VALUES (?, ?);"
if (gh_sqlite3.db_prepare(dbid, sql) == SQLITE_OK) then

	local column = 1
	gh_sqlite3.db_bind_text(dbid, column, "image.jpg") -- Image name

	column = 2
	gh_sqlite3.db_bind_blob_from_file(dbid, column, demo_dir .. "assets/image.jpg") -- Image data

	rc = gh_sqlite3.db_step(dbid)

	gh_sqlite3.db_finalize(dbid)
end

The function db_bind_blob_from_file() does all the work: it allows very simply to store the contents of any file in a blob field.

One could also use the functions of manipulations of the buffers instead of db_bind_blob_from_file:

sql = "INSERT INTO images (name, data) VALUES (?, ?);"
if (gh_sqlite3.db_prepare(dbid, sql) == SQLITE_OK) then

	local column = 1
	gh_sqlite3.db_bind_text(dbid, column, "image.jpg") -- Image name

	buffer, buffer_size = gh_utils.file_buffer_create(demo_dir .. "assets/image.jpg")
  
	column = 2
	gh_sqlite3.db_bind_blob_from_buffer(dbid, column, buffer, buffer_size)

	rc = gh_sqlite3.db_step(dbid)

	gh_sqlite3.db_finalize(dbid)

	gh_utils.file_buffer_kill(buffer)
end

You can then create a texture from the previous blob field with the create_from_buffer function of the gh_texture library:

local buffer, buffer_size = gh_sqlite3.db_column_get_blob(dbid, c)

upload_to_gpu = 1
pixel_format = U8_RGBA
texture_unit = 0
create_srv = 0 -- for d3d12.
gen_mipmaps = 1
compressed_format = ""
free_cpu_memory = 1

gh_texture.create_from_buffer(buffer, buffer_size, upload_to_gpu, pixel_format, texture_unit, create_srv, gen_mipmaps, compressed_format, free_cpu_memory)