Database Library
Comprehensive database management system with SQLite support for the Lilia framework.
Overview
The database library provides comprehensive database management functionality for the Lilia framework. It handles all database operations including connection management, table creation and modification, data insertion, updates, queries, and schema management. The library supports SQLite as the primary database engine with extensible module support for other database systems. It includes advanced features such as prepared statements, transactions, bulk operations, data type conversion, and database snapshots for backup and restore operations. The library ensures data persistence across server restarts and provides robust error handling with deferred promise-based operations for asynchronous database queries. It manages core gamemode tables for players, characters, inventories, items, configuration, logs, and administrative data while supporting dynamic schema modifications.
lia.db.connect(callback, reconnect)
Establishes a connection to the database using the configured database module and initializes the query system.
Called during gamemode initialization to set up the database connection, or when reconnecting to the database.
Parameters:
function callback Optional callback function to execute when the connection is established.boolean reconnect Optional flag to force reconnection even if already connected.
Example Usage:
lia.db.connect(function()
print("Database connected successfully!")
end)
lia.db.wipeTables(callback)
Completely removes all Lilia-related database tables from the database.
Called when performing a complete data wipe/reset of the gamemode's database.
Parameters:
function callback Optional callback function to execute when all tables have been wiped.Example Usage:
lia.db.wipeTables(function()
print("All Lilia tables have been wiped!")
end)
lia.db.loadTables()
Creates all core Lilia database tables if they don't exist, including tables for players, characters, inventories, items, configuration, logs, and administrative data.
Called during gamemode initialization to set up the database schema and prepare the database for use.
Example Usage:
lia.db.loadTables()
lia.db.waitForTablesToLoad()
Returns a deferred promise that resolves when all database tables have finished loading.
Called when code needs to wait for database tables to be fully initialized before proceeding with database operations.
Returns:
Deferred A promise that resolves when tables are loaded.Example Usage:
lia.db.waitForTablesToLoad():next(function()
-- Database tables are now ready
lia.db.select("*", "characters"):next(function(results)
print("Characters loaded:", #results)
end)
end)
lia.db.convertDataType(value, noEscape)
Converts Lua data types to SQL-compatible string formats for database queries.
Automatically called when building SQL queries to ensure proper data type conversion.
Parameters:
any value The value to convert (string, number, boolean, table, nil).boolean noEscape Optional flag to skip SQL escaping for strings and tables.
Returns:
string The converted value as a SQL-compatible string.Example Usage:
local sqlValue = lia.db.convertDataType("John's Name")
-- Returns: "'John''s Name'" (properly escaped)
local sqlValue2 = lia.db.convertDataType({health = 100})
-- Returns: "'{\"health\":100}'" (JSON encoded and escaped)
lia.db.insertTable(value, callback, dbTable)
Inserts a new row into the specified database table with the provided data.
Called when adding new records to database tables such as characters, items, or player data.
Parameters:
table value Table containing column-value pairs to insert.function callback Optional callback function called with (results, lastID) when the query completes.
string dbTable Optional table name (defaults to "characters" if not specified).
Returns:
Deferred A promise that resolves with {results = results, lastID = lastID}.Example Usage:
lia.db.insertTable({
steamID = "STEAM_0:1:12345678",
name = "John Doe",
money = "1000"
}, function(results, lastID)
print("Character created with ID:", lastID)
end, "characters"):next(function(result)
print("Insert completed, last ID:", result.lastID)
end)
lia.db.updateTable(value, callback, dbTable, condition)
Updates existing rows in the specified database table with the provided data.
Called when modifying existing records in database tables such as updating character data or player information.
Parameters:
table value Table containing column-value pairs to update.function callback Optional callback function called with (results, lastID) when the query completes.
string dbTable Optional table name without "lia_" prefix (defaults to "characters" if not specified).
table|string condition Optional WHERE condition to specify which rows to update.
Returns:
Deferred A promise that resolves with {results = results, lastID = lastID}.Example Usage:
-- Update character money
lia.db.updateTable({
money = "500"
}, nil, "characters", {id = 123}):next(function()
print("Character money updated")
end)
-- Update with string condition
lia.db.updateTable({
lastJoin = os.date("%Y-%m-%d %H:%M:%S")
}, nil, "players", "steamID = 'STEAM_0:1:12345678'")
lia.db.select(fields, dbTable, condition, limit)
Selects data from the specified database table with optional conditions and limits.
Called when retrieving data from database tables such as fetching character information or player data.
Parameters:
string|table fields Fields to select - either "*" for all fields, a string field name, or table of field names.string dbTable Table name without "lia_" prefix (defaults to "characters" if not specified).
table|string condition Optional WHERE condition to filter results.
number limit Optional LIMIT clause to restrict number of results.
Returns:
Deferred A promise that resolves with {results = results, lastID = lastID}.Example Usage:
-- Select all characters
lia.db.select("*", "characters"):next(function(result)
print("Found", #result.results, "characters")
end)
-- Select specific fields with condition
lia.db.select({"name", "money"}, "characters", {steamID = "STEAM_0:1:12345678"}):next(function(result)
for _, char in ipairs(result.results) do
print(char.name, "has", char.money, "money")
end
end)
-- Select with limit
lia.db.select("name", "characters", nil, 5):next(function(result)
print("First 5 characters:")
for _, char in ipairs(result.results) do
print("-", char.name)
end
end)
lia.db.selectWithCondition(fields, dbTable, conditions, limit, orderBy)
Selects data from the specified database table with complex conditions and optional ordering.
Called when needing advanced query conditions with operator support and ordering.
Parameters:
string|table fields Fields to select - either "*" for all fields, a string field name, or table of field names.string dbTable Table name without "lia_" prefix.
table|string conditions WHERE conditions - can be a string or table with field-operator-value structures.
number limit Optional LIMIT clause to restrict number of results.
string orderBy Optional ORDER BY clause for sorting results.
Returns:
Deferred A promise that resolves with {results = results, lastID = lastID}.Example Usage:
-- Select with complex conditions and ordering
lia.db.selectWithCondition("*", "characters", {
money = {operator = ">", value = 1000},
faction = "citizen"
}, 10, "name ASC"):next(function(result)
print("Found", #result.results, "rich citizens")
end)
lia.db.count(dbTable, condition)
Counts the number of rows in the specified database table that match the given condition.
Called when needing to determine how many records exist in a table, such as counting characters or items.
Parameters:
string dbTable Table name without "lia_" prefix.table|string condition Optional WHERE condition to filter which rows to count.
Returns:
Deferred A promise that resolves with the count as a number.Example Usage:
-- Count all characters
lia.db.count("characters"):next(function(count)
print("Total characters:", count)
end)
-- Count characters for a specific player
lia.db.count("characters", {steamID = "STEAM_0:1:12345678"}):next(function(count)
print("Player has", count, "characters")
end)
lia.db.addDatabaseFields()
Dynamically adds missing database fields to the characters table based on character variable definitions.
Called during database initialization to ensure all character variables have corresponding database columns.
Example Usage:
lia.db.addDatabaseFields()
lia.db.exists(dbTable, condition)
Checks if any records exist in the specified table that match the given condition.
Called to verify the existence of records before performing operations.
Parameters:
string dbTable Table name without "lia_" prefix.table|string condition WHERE condition to check for record existence.
Returns:
Deferred A promise that resolves to true if records exist, false otherwise.Example Usage:
lia.db.exists("characters", {steamID = "STEAM_0:1:12345678"}):next(function(exists)
if exists then
print("Player has characters")
else
print("Player has no characters")
end
end)
lia.db.selectOne(fields, dbTable, condition)
Selects a single row from the specified database table that matches the given condition.
Called when retrieving a specific record from database tables, such as finding a character by ID.
Parameters:
string|table fields Fields to select - either "*" for all fields, a string field name, or table of field names.string dbTable Table name without "lia_" prefix.
table|string condition Optional WHERE condition to filter results.
Returns:
Deferred A promise that resolves with the first matching row as a table, or nil if no rows found.Example Usage:
-- Get character by ID
lia.db.selectOne("*", "characters", {id = 123}):next(function(character)
if character then
print("Found character:", character.name)
else
print("Character not found")
end
end)
-- Get player data by SteamID
lia.db.selectOne({"name", "money"}, "players", {steamID = "STEAM_0:1:12345678"}):next(function(player)
if player then
print(player.name, "has", player.money, "money")
end
end)
lia.db.bulkInsert(dbTable, rows)
Inserts multiple rows into the specified database table in a single query for improved performance.
Called when inserting large amounts of data at once, such as bulk importing items or characters.
Parameters:
string dbTable Table name without "lia_" prefix.table rows Array of row data tables, where each table contains column-value pairs.
Returns:
Deferred A promise that resolves when the bulk insert completes.Example Usage:
local items = {
{uniqueID = "item1", invID = 1, quantity = 5},
{uniqueID = "item2", invID = 1, quantity = 3},
{uniqueID = "item3", invID = 2, quantity = 1}
}
lia.db.bulkInsert("items", items):next(function()
print("Bulk insert completed")
end)
lia.db.bulkUpsert(dbTable, rows)
Performs bulk insert or replace operations on multiple rows in a single query.
Called when bulk updating/inserting data where existing records should be replaced.
Parameters:
string dbTable Table name without "lia_" prefix.table rows Array of row data tables to insert or replace.
Returns:
Deferred A promise that resolves when the bulk upsert completes.Example Usage:
local data = {
{id = 1, name = "Item 1", value = 100},
{id = 2, name = "Item 2", value = 200}
}
lia.db.bulkUpsert("custom_items", data):next(function()
print("Bulk upsert completed")
end)
lia.db.insertOrIgnore(value, dbTable)
Inserts a new row into the database table, but ignores the operation if a conflict occurs (such as duplicate keys).
Called when inserting data that might already exist, where duplicates should be silently ignored.
Parameters:
table value Table containing column-value pairs to insert.string dbTable Optional table name without "lia_" prefix (defaults to "characters").
Returns:
Deferred A promise that resolves with {results = results, lastID = lastID}.Example Usage:
lia.db.insertOrIgnore({
steamID = "STEAM_0:1:12345678",
name = "Player Name"
}, "players"):next(function(result)
print("Player record inserted or already exists")
end)
lia.db.tableExists(tbl)
Checks if a database table with the specified name exists.
Called before performing operations on tables to verify they exist, or when checking schema state.
Parameters:
string tbl The table name to check for existence.Returns:
Deferred A promise that resolves to true if the table exists, false otherwise.Example Usage:
lia.db.tableExists("lia_characters"):next(function(exists)
if exists then
print("Characters table exists")
else
print("Characters table does not exist")
end
end)
lia.db.fieldExists(tbl, field)
Checks if a column/field with the specified name exists in the given database table.
Called before accessing table columns to verify they exist, or when checking schema modifications.
Parameters:
string tbl The table name to check (should include "lia_" prefix).string field The field/column name to check for existence.
Returns:
Deferred A promise that resolves to true if the field exists, false otherwise.Example Usage:
lia.db.fieldExists("lia_characters", "custom_field"):next(function(exists)
if exists then
print("Custom field exists")
else
print("Custom field does not exist")
end
end)
lia.db.getTables()
Retrieves a list of all Lilia database tables (tables starting with "lia_").
Called when needing to enumerate all database tables, such as for maintenance operations or schema inspection.
Returns:
Deferred A promise that resolves with an array of table names.Example Usage:
lia.db.getTables():next(function(tables)
print("Lilia tables:")
for _, tableName in ipairs(tables) do
print("-", tableName)
end
end)
lia.db.transaction(queries)
Executes multiple database queries as an atomic transaction - either all queries succeed or all are rolled back.
Called when performing multiple related database operations that must be atomic, such as transferring items between inventories.
Parameters:
table queries Array of SQL query strings to execute in sequence.Returns:
Deferred A promise that resolves when the transaction completes successfully, or rejects if any query fails.Example Usage:
local queries = {
"UPDATE lia_characters SET money = money - 100 WHERE id = 1",
"UPDATE lia_characters SET money = money + 100 WHERE id = 2",
"INSERT INTO lia_logs (message) VALUES ('Money transfer completed')"
}
lia.db.transaction(queries):next(function()
print("Transaction completed successfully")
end):catch(function(err)
print("Transaction failed:", err)
end)
lia.db.escapeIdentifier(id)
Escapes SQL identifiers (table and column names) by wrapping them in backticks and escaping any backticks within.
Automatically called when building SQL queries to safely handle identifiers that might contain special characters.
Parameters:
string id The identifier (table name, column name, etc.) to escape.Returns:
string The escaped identifier wrapped in backticks.Example Usage:
local escaped = lia.db.escapeIdentifier("user_name")
-- Returns: "`user_name`"
local escaped2 = lia.db.escapeIdentifier("table`with`ticks")
-- Returns: "`table``with``ticks`"
lia.db.upsert(value, dbTable)
Inserts a new row into the database table, or replaces the existing row if it already exists (SQLite UPSERT operation).
Called when you want to ensure a record exists with specific data, regardless of whether it was previously created.
Parameters:
table value Table containing column-value pairs to insert or update.string dbTable Optional table name without "lia_" prefix (defaults to "characters" if not specified).
Returns:
Deferred A promise that resolves with {results = results, lastID = lastID}.Example Usage:
-- Ensure a character exists with this data
lia.db.upsert({
id = 123,
steamID = "STEAM_0:1:12345678",
name = "John Doe",
money = "1000"
}, "characters"):next(function(result)
print("Character upserted, last ID:", result.lastID)
end)
lia.db.delete(dbTable, condition)
Deletes rows from the specified database table that match the given condition.
Called when removing records from database tables, such as deleting characters or cleaning up old data.
Parameters:
string dbTable Table name without "lia_" prefix (defaults to "character" if not specified).table|string condition WHERE condition to specify which rows to delete.
Returns:
Deferred A promise that resolves with {results = results, lastID = lastID}.Example Usage:
-- Delete a specific character
lia.db.delete("characters", {id = 123}):next(function()
print("Character deleted")
end)
-- Delete all characters for a player
lia.db.delete("characters", {steamID = "STEAM_0:1:12345678"}):next(function()
print("All player characters deleted")
end)
lia.db.createTable(dbName, primaryKey, schema)
Creates a new database table with the specified schema definition.
Called when creating custom tables for modules or extending the database schema.
Parameters:
string dbName Table name without "lia_" prefix.string primaryKey Optional name of the primary key column.
table schema Array of column definitions with name, type, not_null, and default properties.
Returns:
Deferred A promise that resolves to true when the table is created.Example Usage:
local schema = {
{name = "id", type = "integer", not_null = true},
{name = "name", type = "string", not_null = true},
{name = "value", type = "integer", default = 0}
}
lia.db.createTable("custom_data", "id", schema):next(function()
print("Custom table created")
end)
lia.db.createColumn(tableName, columnName, columnType, defaultValue)
Adds a new column to an existing database table.
Called when extending database schema by adding new fields to existing tables.
Parameters:
string tableName Table name without "lia_" prefix.string columnName Name of the new column to add.
string columnType SQL data type for the column (e.g., "VARCHAR(255)", "INTEGER", "TEXT").
any defaultValue Optional default value for the new column.
Returns:
Deferred A promise that resolves to true if column was added, false if it already exists.Example Usage:
lia.db.createColumn("characters", "custom_field", "VARCHAR(100)", "default_value"):next(function(success)
if success then
print("Column added successfully")
else
print("Column already exists")
end
end)
lia.db.removeTable(tableName)
Removes a database table from the database.
Called when cleaning up or removing custom tables from the database schema.
Parameters:
string tableName Table name without "lia_" prefix.Returns:
Deferred A promise that resolves to true if table was removed, false if it doesn't exist.Example Usage:
lia.db.removeTable("custom_data"):next(function(success)
if success then
print("Table removed successfully")
else
print("Table does not exist")
end
end)
lia.db.removeColumn(tableName, columnName)
Removes a column from an existing database table by recreating the table without the specified column.
Called when removing fields from database tables during schema cleanup or refactoring.
Parameters:
string tableName Table name without "lia_" prefix.string columnName Name of the column to remove.
Returns:
Deferred A promise that resolves to true if column was removed, false if table/column doesn't exist.Example Usage:
lia.db.removeColumn("characters", "old_field"):next(function(success)
if success then
print("Column removed successfully")
else
print("Column or table does not exist")
end
end)
lia.db.getCharacterTable(callback)
Retrieves the column information/schema for the characters table.
Called when needing to inspect the structure of the characters table for schema operations.
Parameters:
function callback Function to call with the array of column names.Example Usage:
lia.db.getCharacterTable(function(columns)
print("Character table columns:")
for _, column in ipairs(columns) do
print("-", column)
end
end)
lia.db.createSnapshot(tableName)
Creates a backup snapshot of all data in the specified table and saves it to a JSON file.
Called for backup purposes before major data operations or schema changes.
Parameters:
string tableName Table name without "lia_" prefix.Returns:
Deferred A promise that resolves with snapshot info {file = filename, path = filepath, records = count}.Example Usage:
lia.db.createSnapshot("characters"):next(function(snapshot)
print("Snapshot created:", snapshot.file, "with", snapshot.records, "records")
end)
lia.db.loadSnapshot(fileName)
Loads data from a snapshot file and restores it to the corresponding database table.
Called to restore database tables from backup snapshots.
Parameters:
string fileName Name of the snapshot file to load.Returns:
Deferred A promise that resolves with restore info {table = tableName, records = count, timestamp = timestamp}.Example Usage:
lia.db.loadSnapshot("snapshot_characters_1640995200.json"):next(function(result)
print("Restored", result.records, "records to", result.table)
end)