Veteran Member
Join Date: Jan 2010
Location: mom's basement
|
05-01-2022
, 08:07
Re: [Windows] SQLite 3.38.3 - 01.05.2022
|
#2
|
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"]}
__________________
Do not Private Message @me
Last edited by Bacardi; 05-01-2022 at 08:08.
|
|