MySQLite - Wrapper for SQLite and various MySQL modules

MySQLite - Abstraction mechanism for SQLite and MySQL

I’m seeing too many developers supporting ONLY SQLite, tmysql4 OR MySQLOO. This is here to solve that. It has actually been around for ages, being used in big projects such as FPP and DarkRP.

Reasons to use this
- Easy to use interface for MySQL. Very little boilerplate code
- No need to modify code when switching between SQLite and the different modules for MySQL
- Regularly updated to support new modules and more functionalities, while (hopefully) keeping the interface intact.
- Queued queries: execute a bunch of queries in sequential order an run the callback when all queries are done

License
LGPL V2.1 (read here: https://www.gnu.org/licenses/lgpl-2.1.html)
Basically you can use this in your project, even if you intend to sell it. However, changes made to the library are to be published. Let everyone enjoy your improvements.

Supported MySQL modules
- MySQLOO (http://forum.facepunch.com/showthread.php?t=1357773)
- tmysql4 (http://forum.facepunch.com/showthread.php?t=1442438) (Beta, please test and report errors here)

Note: When both MySQLOO and tmysql4 modules are installed, MySQLOO is used by default. This is for simplicity’s sake. This might change in the future if this is really wanted.

Documentation
[lua]
MySQLite.initialize([config :: table]) :: No value
Initialize MySQLite. Loads the config from either the config parameter OR the MySQLite_config global.
This loads the module (if necessary) and connects to the MySQL database (if set up).
The config must have this layout:
{
EnableMySQL :: Bool - set to true to use MySQL, false for SQLite
Host :: String - database hostname
Username :: String - database username
Password :: String - database password (keep away from clients!)
Database_name :: String - name of the database
Database_port :: Number - connection port (3306 by default)
}

----------------------------- Utility functions -----------------------------
MySQLite.isMySQL() :: Bool
Returns whether MySQLite is set up to use MySQL. True for MySQL, false for SQLite.
Use this when the query syntax between SQLite and MySQL differs (example: AUTOINCREMENT vs AUTO_INCREMENT)

MySQLite.SQLStr(str :: String) :: String
Escapes the string and puts it in quotes.
It uses the escaping method of the module that is currently being used.

MySQLite.tableExists(tbl :: String, callback :: function, errorCallback :: function)
Checks whether table tbl exists.

callback format: function(res :: Bool)
    res is a boolean indicating whether the table exists.

The errorCallback format is the same as in MySQLite.query.

----------------------------- Running queries -----------------------------
MySQLite.query(sqlText :: String, callback :: function, errorCallback :: function) :: No value
Runs a query. Calls the callback parameter when finished, calls errorCallback when an error occurs.

callback format:
    function(result :: table, lastInsert :: number)
    Result is the table with results (nil when there are no results or when the result list is empty)
    lastInsert is the row number of the last inserted value (use with AUTOINCREMENT)

    Note: lastInsert is NOT supported when using SQLite.

errorCallback format:
    function(error :: String, query :: String) :: Bool
    error is the error given by the database module.
    query is the query that triggered the error.

    Return true to suppress the error!

MySQLite.queryValue(sqlText :: String, callback :: function, errorCallback :: function) :: No value
Runs a query and returns the first value it comes across.

callback format:
    function(result :: any)
        where the result is either a string or a number, depending on the requested database field.

The errorCallback format is the same as in MySQLite.query.

----------------------------- Transactions -----------------------------
MySQLite.begin() :: No value
Starts a transaction. Use in combination with MySQLite.queueQuery and MySQLite.commit.

MySQLite.queueQuery(sqlText :: String, callback :: function, errorCallback :: function) :: No value
Queues a query in the transaction. Note: a transaction must be started with MySQLite.begin() for this to work.
The callback will be called when this specific query has been executed successfully.
The errorCallback function will be called when an error occurs in this specific query.

See MySQLite.query for the callback and errorCallback format.

MySQLite.commit(onFinished)
Commits a transaction and calls onFinished when EVERY queued query has finished.
onFinished is NOT called when an error occurs in one of the queued queries.

onFinished is called without arguments.

----------------------------- Hooks -----------------------------
DatabaseInitialized
Called when a successful connection to the database has been made.
[/lua]

Examples

Config, in DarkRP and FPP this is a Lua file end users can modify:
[lua]
MySQLite_config = {}
MySQLite_config.EnableMySQL = false – Set to true if you want to use an external MySQL database, false if you want to use the built in SQLite database (garrysmod/sv.db) of Garry’s mod.
MySQLite_config.Host = “127.0.0.1” – This is the IP address of the MySQL host. Make sure the IP address is correct and in quotation marks (" “)
MySQLite_config.Username = “user” – This is the username to log in on the MySQL server.
– contact the owner of the server about the username and password. Make sure it’s in quotation marks! (” “)
MySQLite_config.Password = “password” – This is the Password to log in on the MySQL server,
– Everyone who has access to FTP on the server can read this password.
– Make sure you know who to trust. Make sure it’s in quotation marks (” ")
MySQLite_config.Database_name = “DarkRP” – This is the name of the Database on the MySQL server. Contact the MySQL server host to find out what this is
MySQLite_config.Database_port = 3306
[/lua]

Starting a connection and running a query:
[lua]
– The hook is called on connection success. Connection failure leads to an error.
hook.Add(“DatabaseInitialized”, “CreateTables”, function()
MySQLite.query([[
CREATE TABLE IF NOT EXISTS playerinformation(
uid BIGINT NOT NULL,
steamID VARCHAR(50) NOT NULL PRIMARY KEY
)
]])

MySQLite.queryValue("SELECT 1 + 1", function(v) print("VALUE = " .. v) end, print)

end)

– Assumes global MySQLite_config is set. Config can also be given as first argument, see documentation
MySQLite.initialize()
[/lua]

Many more examples in this DarkRP file!

**
Download (not necessarily recommended)**

Recommended approach
This approach uses git, it allows you to update the module in the project very easily.

  • Make sure your project uses git
  • Use either subtree merging or submodules. Subtree merging is more difficult, but allows for more freedom. Submodules is a simpler mechanism but has to have its own folder. Besides, cloning requires an extra parameter. DarkRP uses subtree merging.

Thank you for this.

looks fancy, good job i like it

+1

http://www.facepunch.com/fp/ratings/lua_king.png

For You

Does this still work?

DarkRP uses it to this day.

Awesome!

Is this thing faster than sql.Query() ???

No, that’s an odd proposition too.