SQLi library

Started by EK.IceFlake, Feb 23, 2017, 03:40 PM

Previous topic - Next topic

EK.IceFlake

To solve the political war between MySQL and SQLite, and to make life easier, I've made the SQLi library. The SQLi library is a cross-database (mysql or sqlite) abstracted library for using either MySQL or SQLite, with minimal differences between them both.
Here is the code to place in sqli.nut: http://pastebin.com/GPJNsdDP (also available as a module: http://pastebin.com/KVA5nENk)
Here is how to use it.
First, you instantiate an instance (this is one of the places where there is a difference between MySQL and SQLite). For example:
Inst <- mysqli("localhost:1444", "nub", "t0p532r37", "nubdb");or
Inst <- sqlitei("nub.sqli");Now, for doing a query, you'll call Inst.exec (which will throw an error describing what happened if the query fails):
Inst.exec("update players set kills = " + arguments + " where lower(name) = '" + player.Name.tolower() + "'");As you can see, this code is prone to SQL injection. However, this can easily be solved like this:
Inst.exec("update players set kills = ? where lower(name) = '?'", arguments, player.Name.tolower());You could also do multiple queries in a transaction
Inst.transact(["update players set kills = ? where lower(name) = '?'", arguments.tointeger(), player.Name.tolower()], ["update players set score = ? where lower(name) = '?'", arguments, player.Name.tolower()]);When you're doing a select query, this is the format for using its result: result[nRowID]["sColumnName"] (it returns an array which contains tables).
For example:
local result = Inst.exec("select * from players where name = 'EK.IceFlake'");
print(result[0]["Kills"]);
Note that we do not free the query. This is because the query is automatically freed by the exec function.
You can close the connection using:
Inst.close();and create a new connection (which would overwrite the old connection if it is still alive) using:
Inst.connect(host, username, password, database);or
Inst.connect(path);
Here are some MySQL only functions:
Inst.rowsaffected();
Inst.info();
Inst.use(database);
These functions will throw an error if you try to use them on an SQLite instance.

If you plan to switch to another database later on, then as long as you aren't using the MySQL only functions, it'll be as simple as changing the instantiation line and all connect calls.

You'll need the official MySQL plugin and SLC's SQLite plugin.

Make sure to post your suggestions (or bugs, if any) below.

EK.IceFlake

Update 02.23.2017: Added native transaction support.

KAKAN

Suggestion: Add some comments detailing the parameters.
It was hard to understand that i_exec part.
oh no

EK.IceFlake

#3
Quote from: KAKAN on Feb 23, 2017, 05:55 PMSuggestion: Add some comments detailing the parameters.
It was hard to understand that i_exec part.
The i_ in it means it's an internal function and you're not supposed to mess with it xD
Anyways, they are the same as for exec but instead of using vargv, you need to pass an explicit array.

EK.IceFlake

Update 03.18.2017: Removed redundant variable.