.. contents:: Table of Contents


.. _lsqlite3:

Module lsqlite3
===============

`SQLite <https://www.sqlite.org/index.html>`__  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 <http://lua.sqlite.org/index.cgi/home>`__ it is a binding to use `Sqlite <https://www.sqlite.org/index.html>`__ in `Lua <https://www.lua.org/>`__ script.

This version of `Lua SQLite <http://lua.sqlite.org/index.cgi/home>`__ 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.


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:

.. code-block:: sql

    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.



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.


.. data:: add_asset_folder(TEXT * folder)

    :param 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:*

.. code-block:: sql

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


*Example LUA script:*

.. code-block:: lua

    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 :guilabel:`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.


save_asset
^^^^^^^^^^

.. data:: save_asset(TEXT name,BLOB content)

    :param text: **name**
    :param blob: **content**

.. Note:: The *name* and *content* are result of any query.

.. code-block:: sql

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


.. code-block:: lua

    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:

.. code-block:: lua

    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


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.


.. data:: delete_asset_folder(INTEGER * number_folder_created)

    :param 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:*

.. code-block:: sql

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


*Example LUA script:*

.. code-block:: lua

    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:*

.. code-block:: lua

    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.

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.


.. data:: save_path_asset()


*Syntax SQL:*

.. code-block:: sql

    SELECT SAVE_PATH_ASSETS();


*Example LUA script:*

.. code-block:: lua

    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.

Editor
------

There is an editor to create easily your asset package.

This is how it looks:

.. figure:: _static/sql_editor_1.png
   :align: center
   :figclass: align-center

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


Examples
--------

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

*main.lua*:

.. code-block:: 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*:

.. code-block:: 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 :guilabel:`Android` it is possible to list the files in the cache folder through command line using :guilabel:`adb` from folder ``~/Android/Sdk/platform-tools``:

.. prompt:: bash $
    
    ./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 :guilabel:`Android`. This is a **TODO**.

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