AlliedModders - [Windows] SQLite 3.38.3

AlliedModders (https://forums.alliedmods.net/index.php)

- Extensions (https://forums.alliedmods.net/forumdisplay.php?f=134)

- - [Windows] SQLite 3.38.3 - 01.05.2022 (https://forums.alliedmods.net/showthread.php?t=337588)

[Windows] SQLite 3.38.3 - 01.05.2022

SourceMod Extension SQLite 3.38.3
https://www.sqlite.org/changes.html

Install:
- Rename file ...sample\msvc12\Debug\sample.ext.dll to dbi.sqlite.ext.dll
- Shutdown your server.
- Add file into ...addons\sourcemod\extensions\

- Launch server, type sm exts list

Code:

[08] SQLite (1.11.0-manual): SQLite Driver

- If not loaded, to manually load extension sm exts load dbi.sqlite.ext.dll
- sm exts info index

Code:

sm exts info 08

 File: dbi.sqlite.ext.dll

 Loaded: Yes (version 1.11.0-manual)

 Name: SQLite (SQLite Driver)

 Author: Bacardi, AlliedModders LLC (http://www.sourcemod.net/)

 Binary info: API version 8 (compiled Apr 30 2022 23:46:11)

 Method: Loaded by SourceMod

- Important notes!
I am not coder, how this extension got compiled was pure luck.
I tried follow this https://wiki.alliedmods.net/Writing_..._Visual_Studio

- Using SQLite source code https://www.sqlite.org/download.html
I compiled libary and then compiled extension, using copy of SourceMod SQLite extension, with updated files.

- Use your own risk. This is just for experimental use.

Here test plugin, using SQLite new feature RETURNING https://www.sqlite.org/lang_returning.html

PHP Code:


		
			




public void OnPluginStart()

{

    RegConsoleCmd("sm_test", test);

}



Database MyDB;



public Action test(int client, int args)

{

    Database.Connect(connectcallback, "my_database", 0);



    return Plugin_Handled;

}



public void connectcallback(Database db, const char[] error, any data)

{

    if(MyDB != db)

        delete MyDB;



    MyDB = db;



    MyDB.Query(querytablecallback,

                        "CREATE TABLE IF NOT EXISTS my_table(    \

                        a INTEGER PRIMARY KEY,                    \

                        b DATE DEFAULT CURRENT_TIMESTAMP,        \

                        c INTEGER                                \

                        );",

                        0, DBPrio_Low);

}



public void querytablecallback(Database db, DBResultSet results, const char[] error, any data)

{

    if(results == null)

        return;



// SQLite random() return 64bit values!

// In this code, return value is limited between -2147483647 ~ 2147483647



    MyDB.Query(queryvaluecallback,

                        "INSERT INTO my_table(c) VALUES(    \

                        abs(random()) % (2147483647 - -2147483647) + -2147483647    \

                        ) RETURNING *;",

                        0, DBPrio_Low);

}



public void queryvaluecallback(Database db, DBResultSet results, const char[] error, any data)

{

    if(results == null)

        return;





    // table exist, look results and print those



    if(results.AffectedRows > 0)

    {

        char buffer[256];



        while(results.FetchRow())

        {

            for(int x = 0; x < results.FieldCount; x++)

            {

                results.FetchString(x, buffer, sizeof(buffer));

                PrintToServer("%s", buffer);

            }

        }

    }



}

output

Code:

sm_test

24

2022-04-30 22:07:50

-384232008

- I don't know how JSON feature works, I have not tested.

Re: [Windows] SQLite 3.38.3 - 01.05.2022

Here is JSON example: https://tirkarthi.github.io/programm...rovements.html

PHP Code:


		
			
/*
./sqlite3
SQLite version 3.38.0 2022-02-22 18:58:40
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table user(id integer primary key, name text, interests json);
sqlite> insert into user values(null, "John", '{"likes": ["skating", "reading", "swimming"], "dislikes": ["cooking"]}');
sqlite> insert into user values(null, "Kate", '{"likes": ["reading", "swimming"], "dislikes": ["skating"]}');
sqlite> insert into user values(null, "Jim", '{"likes": ["reading", "swimming"], "dislikes": ["cooking"]}');
sqlite> .mode column
sqlite> select * from user;
id  name  interests                                                   
--  ----  ------------------------------------------------------------
1   John  {"likes": ["skating", "reading", "swimming"], "dislikes": ["cooking"]}                                               
2   Kate  {"likes": ["reading", "swimming"], "dislikes": ["skating"]}
3   Jim   {"likes": ["reading", "swimming"], "dislikes": ["cooking"]}

*/


public void OnPluginStart()
{
    RegConsoleCmd("sm_test", test);
}

Database MyDB;

public Action test(int client, int args)
{
    Database.Connect(connectcallback, "my_database", 0);

    return Plugin_Handled;
}

public void connectcallback(Database db, const char[] error, any data)
{
    if(MyDB != db)
        delete MyDB;

    MyDB = db;

    MyDB.Query(querytablecallback,
                        "CREATE TABLE IF NOT EXISTS user(    \
                        id INTEGER PRIMARY KEY,                \
                        name TEXT,                            \
                        interests JSON                        \
                        );",
                        0, DBPrio_Low);
}

public void querytablecallback(Database db, DBResultSet results, const char[] error, any data)
{
    if(results == null)
        return;

    MyDB.Query(queryinsertcallback,
                        "INSERT INTO user VALUES(    \
                        null, \"John\", '{\"likes\": [\"skating\",\"reading\",\"swimming\"], \"dislikes\": [\"cooking\"]}\'    \
                        ),                            \
                        (                            \
                        null, \"Kate\", '{\"likes\": [\"reading\",\"swimming\"], \"dislikes\": [\"skating\"]}\'                \
                        ),                            \
                        (                            \
                        null, \"Jim\", '{\"likes\": [\"reading\",\"swimming\"], \"dislikes\": [\"cooking\"]}\'                \
                        );",
                        0, DBPrio_Low);
}



/*
select id, name, interests from user 
where interests->'$.likes'->>'$[0]' = 'reading';
*/
public void queryinsertcallback(Database db, DBResultSet results, const char[] error, any data)
{
    if(results == null)
        return;

    MyDB.Query(queryvaluecallback,
                        "SELECT id, name, interests FROM user            \
                        WHERE interests->'$.likes'->>'$[0]' = 'reading';",
                        0, DBPrio_Low);
}

public void queryvaluecallback(Database db, DBResultSet results, const char[] error, any data)
{
    if(results == null)
        return;


    // table exist, look results and print those

    if(results.AffectedRows > 0)
    {
        char buffer[256];

        while(results.FetchRow())
        {
            for(int x = 0; x < results.FieldCount; x++)
            {
                results.FetchString(x, buffer, sizeof(buffer));
                PrintToServer("%s", buffer);
            }
        }
    }

}

output

Code:

sm_test

2

Kate

{"likes": ["reading","swimming"], "dislikes": ["skating"]}

3

Jim

{"likes": ["reading","swimming"], "dislikes": ["cooking"]}

5

Kate

{"likes": ["reading","swimming"], "dislikes": ["skating"]}

6

Jim

{"likes": ["reading","swimming"], "dislikes": ["cooking"]}

8

Kate

{"likes": ["reading","swimming"], "dislikes": ["skating"]}

9

Jim

{"likes": ["reading","swimming"], "dislikes": ["cooking"]}

11

Kate

{"likes": ["reading","swimming"], "dislikes": ["skating"]}

12

Jim

{"likes": ["reading","swimming"], "dislikes": ["cooking"]}

14

Kate

{"likes": ["reading","swimming"], "dislikes": ["skating"]}

15

Jim

{"likes": ["reading","swimming"], "dislikes": ["cooking"]}

17

Kate

{"likes": ["reading","swimming"], "dislikes": ["skating"]}

18

Jim

{"likes": ["reading","swimming"], "dislikes": ["cooking"]}

20

Kate

{"likes": ["reading","swimming"], "dislikes": ["skating"]}

21

Jim

{"likes": ["reading","swimming"], "dislikes": ["cooking"]}

Re: [Windows] SQLite 3.38.3 - 01.05.2022

It would be very preferable if you would use a different extension/db/include name to not conflict with named used by first-party SM extensions.

Re: [Windows] SQLite 3.38.3 - 01.05.2022

I know... but I'm too dummy to make it.
I wanted to upgrade SM own extension, because it was easy way and this is for testing purpose. :S

Re: [Windows] SQLite 3.38.3 - 01.05.2022

Not need use this topic extension.

SourceMod extension update here Update SQLite library to 3.38.5 #1792
https://github.com/alliedmodders/sourcemod/pull/1792