Table of Contents

• Module lsqlite3

  • Asset

    • add_asset_folder

    • save_asset

    • delete_asset_folder

    • save_path_asset

  • Editor

  • Examples

27. Module lsqlite3

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.

lSQLite it is a binding to use Sqlite in Lua script.

This version of Lua SQLite contains embedded SQL functions for creation and manipulation of assets to be used in the engine.

The idea is to be able to make your assets in one unique file. Then, during runtime, it is possible to query and dump files from SQlite database to specific folder or even, anonymous folder. Some people or company want to make the assets not so easy to access for many reasons. This feature (although it is optional), could be used for that.

For that, there are some builtin function in sql and also there is an editor to facilitate the construction of your asset.

27.1. Asset

Asset might have images files, scripts files, music files, etc. The idea is to be able to make your assets in one unique file as mentioned before. For this happen, there are some functions builtin available.

An asset database is organized with the following tables:

TABLE dumped_folder(path TEXT);

TABLE paths(id      INTEGER PRIMARY KEY,
            path    TEXT);

TABLE assets(id INTEGER  PRIMARY KEY,
             name        TEXT,
             category    TEXT,
             content     BLOB,
             id_path     INTEGER REFERENCES paths(id) ON DELETE CASCADE);

Next, explanation for each function available.

27.1.1. add_asset_folder

Create a folder and register the path to the engine. This path will be used as the next folder to dump assets.

add_asset_folder(TEXT * folder)

Parameters

string – folder (optional). Might be omitted.

Note

If the folder path is not supplied, the engine will create in the temp system folder.

Syntax SQL:

SELECT ADD_ASSET_FOLDER();
SELECT ADD_ASSET_FOLDER('path');
SELECT ADD_ASSET_FOLDER('.');

Example LUA script:

sqlite3       =     require "lsqlite3"

local sDataBaseFilePath = 'test.asset'
local db = sqlite3.open(sDataBaseFilePath,sqlite3.OPEN_READWRITE)
if db then
    local result = db:exec(" SELECT ADD_ASSET_FOLDER('my_asset_folder'); ")
    if result ~= sqlite3.OK then
        print('error',db:errmsg())
    end
    db:close()
else
    print('error', 'Could not open the database:',sDataBaseFilePath)
end

Important

The argument '.' instruct to use the current path of the engine. It will not create any path.

For Android it will use the cache folder.

The argument 'path' instruct to create in the path that you passed. If not exist, the engine will try to create it.

27.1.2. save_asset

save_asset(TEXT name, BLOB content)

Parameters

• text – name

• blob – content

Note

The name and content are result of any query.

SELECT SAVE_ASSET(name,content) FROM assets;
SELECT SAVE_ASSET(name,content) FROM assets where name like '%.png';

sqlite3       =     require "lsqlite3"

local sDataBaseFilePath = 'my_resource.asset'
local db = sqlite3.open(sDataBaseFilePath,sqlite3.OPEN_READONLY)
if db then
    -- Dump all assets from SELECT query to a random folder. the engine will know the path.
    local result = db:exec([[ SELECT SAVE_ASSET(name,content) FROM assets; ]])
    if result ~= sqlite3.OK then
        print('error',db:errmsg())
    end
    db:close()
else
    print('error', 'Could not open the database:',sDataBaseFilePath)
end

Or you can specify the path before dump the assets from query:

sqlite3       =     require "lsqlite3"

local sDataBaseFilePath = 'my_resource.asset'
local db = sqlite3.open(sDataBaseFilePath,sqlite3.OPEN_READONLY)
if db then
    local result = db:exec(" SELECT ADD_ASSET_FOLDER('/tmp/my_folder'); ")
    if result == sqlite3.OK then
        -- Dump all assets from SELECT query to the last folder added.
        local result = db:exec([[ SELECT SAVE_ASSET(name,content) FROM assets; ]])
        if result ~= sqlite3.OK then
            print('error',db:errmsg())
        end
    else
        print('error',db:errmsg())
    end
    db:close()
else
    print('error', 'Could not open the database:',sDataBaseFilePath)
end

27.1.3. delete_asset_folder

Delete a folder previously created by the engine. You might specify the number of folder created (1 to total of folder created).

Negative numbers are accepted. -1 means the last folder created.

delete_asset_folder(INTEGER * number_folder_created)

Parameters

integer – folder identify, (optional). Might be omitted.

Note

If the number of folder is not supplied, the engine will delete all folders created to the current database.

Syntax SQL:

SELECT DELETE_ASSET_FOLDER();
SELECT DELETE_ASSET_FOLDER(1);
SELECT DELETE_ASSET_FOLDER(-1);

Example LUA script:

sqlite3       =     require "lsqlite3"

local sDataBaseFilePath = 'my_game.asset'
local db = sqlite3.open(sDataBaseFilePath,sqlite3.OPEN_READWRITE)
if db then
    --create some folder
    local result = db:exec([[ SELECT ADD_ASSET_FOLDER(); ]]) -- anonymous folder (the engine will know)
    if result ~= sqlite3.OK then
        print(result,db:errmsg())
        tUtil.showMessageWarn('Error:\n' .. msg)
    end

    -- delete all folder created (this example one)
    local result = db:exec(" SELECT DELETE_ASSET_FOLDER(); ")
    if result ~= sqlite3.OK then
        print('error',db:errmsg())
    end
    db:close()
else
    print('error', 'Could not open the database:',sDataBaseFilePath)
end

Delete specific folder:

sqlite3       =     require "lsqlite3"

local sDataBaseFilePath = 'my_game.asset'
local db = sqlite3.open(sDataBaseFilePath,sqlite3.OPEN_READWRITE)
if db then
    --create some folder
    local result = db:exec([[ SELECT ADD_ASSET_FOLDER('C:\\folder_A'); ]])
    if result ~= sqlite3.OK then
        print(result,db:errmsg())
        tUtil.showMessageWarn('Error:\n' .. msg)
    end

    --create some folder
    local result = db:exec([[ SELECT ADD_ASSET_FOLDER('C:\\folder_B'); ]])
    if result ~= sqlite3.OK then
        print(result,db:errmsg())
        tUtil.showMessageWarn('Error:\n' .. msg)
    end

    --create some folder
    local result = db:exec([[ SELECT ADD_ASSET_FOLDER('C:\\folder_C'); ]])
    if result ~= sqlite3.OK then
        print(result,db:errmsg())
        tUtil.showMessageWarn('Error:\n' .. msg)
    end

    -- delete all folder created (this example one)
    local result = db:exec(" SELECT DELETE_ASSET_FOLDER(2); ") --Delete the 'C:\\folder_B' folder
    if result ~= sqlite3.OK then
        print('error',db:errmsg())
    end
    db:close()
else
    print('error', 'Could not open the database:',sDataBaseFilePath)
end

Note

Now the internal list will have two folders. C:\\folder_A and C:\\folder_C

Important

If you close the database without save the internal path and load the database, the list will no longer exist.

The extended function does not save the internal path if not requested. The folder will be present.

You might use the function save_path_assets to register the folders to the database. Then, next time you open the database, you may delete also the previously folder created.

Important

This function requires that the database be open as read/write.

27.1.4. save_path_asset

Save the internal path created by the engine (encripted). It will create a table called internal_path_asset if not exist to save the path there.

save_path_asset

Syntax SQL:

SELECT SAVE_PATH_ASSETS();

Example LUA script:

sqlite3       =     require "lsqlite3"

local sDataBaseFilePath = 'my_game.asset'
local db = sqlite3.open(sDataBaseFilePath,sqlite3.OPEN_READWRITE)
if db then
    --create some folder
    local result = db:exec([[ SELECT ADD_ASSET_FOLDER(); ]]) -- anonymous folder (the engine will know)
    if result ~= sqlite3.OK then
        print(result,db:errmsg())
        tUtil.showMessageWarn('Error:\n' .. msg)
    end

    local result = db:exec(" SELECT SAVE_PATH_ASSETS(); ")
    if result ~= sqlite3.OK then
        print('error',db:errmsg())
    end
    db:close()
else
    print('error', 'Could not open the database:',sDataBaseFilePath)
end

-- open the data base again
local db = sqlite3.open(sDataBaseFilePath,sqlite3.OPEN_READWRITE)
if db then
    -- delete all folder created (this example one)
    local result = db:exec(" SELECT DELETE_ASSET_FOLDER(); ")
    if result ~= sqlite3.OK then
        print('error',db:errmsg())
    end
    db:close()
else
    print('error', 'Could not open the database:',sDataBaseFilePath)
end

Important

This function requires that the database be open as read/write.

27.2. Editor

There is an editor to create easily your asset package.

This is how it looks:

The editor is a lua script named asset_packager.lua and it is located at editor folder.

27.3. Examples

Here an example of how generate a random folder each time that you game runs:

main.lua:

sqlite3       =     require "lsqlite3"

local sAsset = mbm.getFullPath('my_package.asset')
--Important to use getFullPath
local db = sqlite3.open(sAsset,sqlite3.OPEN_READWRITE)
if db then
    -- extract all assets. You might select which one should be extracted
    local result = db:exec(" SELECT SAVE_ASSET(name,content) FROM assets; ")
    if result == sqlite3.OK then
        --now we have the scene 'start-scene.lua' (hypothetical) which were inside the asset, load it
        mbm.loadScene('start-scene.lua')
        --the game continues normally
    else
        print('error',db:errmsg())
    end
    db:close()
else
    print('error', 'Could not open the database:',sAsset)
end

Here an example of how extract the asset to the expected folder each time that you game runs:

main.lua:

sqlite3       =     require "lsqlite3"

local sAsset = mbm.getFullPath('my_package.asset')
--Important to use getFullPath
local db = sqlite3.open(sAsset,sqlite3.OPEN_READWRITE)
if db then
    local result = db:exec(" SELECT ADD_ASSET_FOLDER('my_asset_folder'); ")
    if result == sqlite3.OK then
        -- extract all assets. You might select which one should be extracted
        local result = db:exec(" SELECT SAVE_ASSET(name,content) FROM assets; ")
        if result == sqlite3.OK then
            --now we have the scene 'start-scene.lua' (hypothetical) which were inside the asset, load it
            mbm.loadScene('start-scene.lua')
            --the game continues normally
        else
            print('error',db:errmsg())
        end
    else
        print('error',db:errmsg())
    end
    db:close()
else
    print('error', 'Could not open the database:',sAsset)
end

On Android it is possible to list the files in the cache folder through command line using adb from folder ~/Android/Sdk/platform-tools:

./adb exec-out run-as com.mini.mbm.yourcompany ls -R /data/data/com.mini.mbm.yourcompany

More information at https://developer.android.com/studio/command-line/adb

Warning

known issue.

The current implementation does not load sound stream from cache folder in Android. This is a TODO.

You can leave the sound stream in asset folder (NOT inside of your database) then it will works.