db_init();
This version of GMSDB is available only for Game Maker: Studio 2. Version 3.2 (GMS 1 compatible) is available as legacy documentation
GMSDB is a database system written in GML that can be used in any kind of project
requiring a solution to store and retrieve organized data.
With GMSDB you can easily perform basic CRUD (create,read,update,delete) operations on
data sets (tables) while applying custom clauses like filters, sorting, limits, offsets, calculations and
join operations on the result set.
It also takes care of saving and loading the data to and from the file system in a transparent way, as well as
importing data to and from CSV files.
What you need to know with respect to a real database system like MySQL or SQLite:
The first thing to keep in mind is that this is a database implementation that relies only on GML and current Game Maker features, and while it should in fact for this reason be quite easy to use and compatible with the different export modules, it is also different from real database solutions like the ones above, the main difference being that the data generally resides in the program memory, and is only persisted on the file system on request (in contrast with other solutions, where the data always resides on the file system and is read/written directly).
To add GMSDB to your game, import the "db" folder from the package scripts
Demo and DB explorer utilityGMSDB comes with a demo application in the form of a database viewer utility. You can safely add that to existing projects and use it to debug your databases. To do so, you need to import every resource from the package. (assets belonging to the database or database explorer are scoped with a db_ prefix, so you should not incour in any name clashing.)
To start using GMSDB, you have to initialize the system by calling db_init() in the game start event, or in an event that gets called only once per game run
db_init();
You can then open a database using db_open("db_name"); . If the database with the provided name does not exist yet, this
call creates one for you automatically (please note that the name of the database is used as folder name for the database
directory, therefore you should avoid characters incompatible with the file system).
Only a single database can be open at once. Calling db_open
when a database is already open will forcefully close the current one.
db_open("awesome_database");
Closing a database removes all the database data from the program memory. Since only one database can be open at once, you do not need to specify the name when closing. If the database has been saved prior to closing it, you can load the data back from the file system by calling db_open() again.
db_close();
Database data is store in tables. Tables are essentially collections of records stored in a grid structure with named columns, like an excel table.
Tables are created by specifying a name and its column, as well as each column default value, as follows:
db_table_create("items",[ ["name",""], ["weight",0], ["category","no category"] ]);
When creating a table, a special column named "id" gets auto generated. This column holds an auto incrementing unique record identifier (starting from 1). Records on the same table will never have the same id. Even if all the records in a table are deleted, the next id will continue from the last and highest one ever inserted.
Lastly, it is important to keep in mind that a single column can only hold values of a specific type, either string or real. This is determined by the type of the default value.
Like the database name, table names are also used as filenames when saving, and should not contain any character incompatible with the file system.
Records are generally created by using the script db_record_create(). The script allows you to specify the values of the record for each column, and will use the table defaults for columns not explicitly set. The order of the columns in the script call is not enforced.
db_record_create("items",[ ["name","Silver sword"], ["weight",20], ["category","Weapon"] ]);
The second way to create records is by using a ds_map, generated from a call to db_record_build().
This script does not create a record directly, but instead returns a custom ds_map structure that can be used
to access and set your record properties, using column names as keys.
Once ready, you can save the record to the database by using db_record_save(). Once saved, the "id" key of the ds_map
will be set and can be used to refer to that specific record.
This method can be used also for retrieving and updating records, more on this in the records section.
Important: please note that you are in charge is calling ds_map_destroy() once you no longer need the record ds_map. If the record has been saved, destroying the ds_map has no effect on the actual database data.
var record = db_record_build("items");
record[? "name"] = "Silver sword";
record[? "weight"] = 20;
record[? "category"] = "Weapon";
db_record_save(record);
show_debug_message("Record created. ID: " + string(record[? "id"]));
ds_map_destroy(record);
Operations on records are performed by using a set of scripts prefixed with "db_record_".
These scripts generally require the record ids to be known in advance, and are a fast way to manage record specific data or
operations on records that are simple and limited in scope.
Bulk operations should instead be done by using queries (more in this later).
//Get the id of the first item of category "Sword"
var sword_id = db_record_find_first("items","category","Sword");
//Get a ds_map containing the sword data
if(sword_id > 0) {
var sword = db_record_fetch("items",sword_id);
}
//Double the value of the item
sword[? "value"] = sword[? "weight"] * 2;
//Save the sword back to the database
db_record_save(sword);
//Destroy the ds_map of the sword since we don't need it anymore (doesn't destroy the record)
ds_map_destroy(sword);
Queries are customizable operations on the database that can change, delete, retireve or perform calculations on a database table. Queries should be considered as bulk operations, meaning that unless explicitly restricted by query clauses, they affect all the records of a specified table.
Queries are created and returned the following scripts: db_query_select, db_query_select_ids, db_query_select_records, db_query_update, db_query_delete, db_query_calc . All of these scripts return a query object that can be used to apply other directives like filters, sorting, etc..., called query clauses.
Once ready, queries can be executed by using db_query_exec(). The return value depends on the type of query. Please refer to the "Queries" section for the more info. The following are some examples to get you started:
Get all the items ordered first by category, and then by name for those belonging to the same category.
var query = db_query_select("items",["id","name","category","weight"]);
db_cl_sort(query,[ ["category","asc"], ["name","asc"] ]);
items = db_query_exec(query);
The "items" variable will now hold a ds_grid (or -1 if no results are found) with one record per row, with the grid columns ordered as specified in the query creation call ("id","name","category","weight").
Get a ds_list of ids of the 10 heaviest items belonging to the "Weapon" category.
var query = db_query_select_ids("items");
db_cl_sort(query,[ ["weight","desc"] ]);
db_cl_where(query,db_op_eq,["category"],["Weapon"]);
db_cl_limit(query,10,0);
items = db_query_exec(query);
Delete all the items having a weight greater than 20
var query = db_query_delete("items");
db_cl_where(query,db_op_gt,["weight"],[20]);
var deleted_items_count = db_query_exec(query);
Queries are also more flexible than you may think, allowing for example the definition of custom sort methods (allowing for example sorting by string_length or by a calculated value) and custom where conditions (more on that in the next sections).
Databases and their data is not automatically persisted to the file system by default. This means that you can use GMSDB to store temporary data that lives only as long as the program is running, as well as a totally synchronized database that persists every change to file, or a mix of the two.
Please note that Game Maker is sandboxed, and databases are always intended to be saved to the sandbox.
The name determines the location of the save files, creating a directory where the data gets stored.
By using a path prefix in the name, you can store it in a subfolder, like: db_open("db_folder/db_name");
You can save the database data by using the following scripts:
db_save(): saves the full database
You can also export and import a database to and from a single file by using
db_export and db_import, as well as single tables by using db_table_import and db_table_import.
These calls generate a custom formatted text file that uses json and data structures write operations
to store all of the required information.
Tables can also be exported to CSV, by using db_table_export_csv(), and records created starting from CSV files. These functions can be useful to debug and generate starting data in a user friendly format.
The currently open database information is held in a global variable called global.db.
If no database is open, this variable is set to -1.
While you should not set or alter the contents of this variable directly, it is relevant to note
that, when a database is open, it's a reference to a ds_map with some data you may find useful:
global.db[? "name"] holds the database name
global.db[? "table_names"] is a ds_list holding the names of all the available tables in the database
[enabled] {boolean} (optional) enables or disables the autosave feature
returns {boolean} true if autosave is enabled, false otherwise
If true or false is passed as argument, enables / disables the autosave feature.
When autosave is enabled, all changes to the database are automatically persisted to file.
If no argument is provided, it returns the current status of the autosave feature.
//enables autosave if not currently enabled
if(!db_autosave()) {
db_autosave(true);
}
Closes the currently open database, freeing the memory used.
db_name {string} name of the database
returns {boolean} true if successful, false otherwise
Returns true if the database with the given name exists on the file system.
filename {string} path and filename to export the database to
returns {boolean} true if successful, false otherwise
Exports the currently open database and all its data to file, having the specified path / filename.
filename {string} path and filename of the import file
db_name {string} name of the database where the data should be imported to
returns {boolean} true if successful, false otherwise
Closes the currently open database, and opens a new one with the provided name, importing
the data from the specified file.
Please note that db_name has to be a new database, you can't import the data into an existing
database (in which case, this function returns false)
Initializes the database system. Must be called only once, and before any other function, ideally at game start.
db_name {string} name of the database
returns {boolean} true if successful, false otherwise
Opens the database with the provided name. db_name is used as database folder name when saving,
therefore you are allowed to use a path prefix to store the database in a specific directory
like: db/database_name, but you should also use only characters that are compatible with the file system.
If the database already exists on the file system, it is loaded. Otherwise a new database is created.
db_open("db/my_database");
Persists the currently open database and all of its contents to the file system.
A table is essentially a collection of records stored in grid structure, where every row is a records
with a unique identifier, and every column represents one of the named table columns, much like and excel table.
In GMSDB tables are referred to by their name (as a string) and are available globally. The table name is
required in most of the functions.
Tables always have special column called "id". The id column holds for each record in a table a unique,
auto incrementing numeric identifier, and is automatically set when inserting a record
into the table. Ids should never be manually changed as they are managed internally by the system.
It is worth noting that ids (of a specific table) are NEVER re-assigned, even if the contents of the table
are completely removed (with the notable exception of db_table_clear(), as explained below).
table_name {string} name of the table to clear
reset_index {boolean} if true, the index is also reset, and record ids will be re-assigned starting from 1.
Removes all the data from a table, without altering the table structure.
db_table_clear("items",true);
table_name {string} name of the table
column_name {string} name of the column to add
default {string|real} default value of the new column
returns {boolean} true if successful, false if the column already exists
Adds a column to the specified table. If the column already exists, false is returned. The default value determines the type of data held by the column (either string or real values)
db_table_column_add("items","description","no description available");
table_name {string} name of the table
column_name {string} name of the column to remove
Removes a column and all its values from a table
db_table_column_remove("items","description");
table_name {string} name of the table
returns {array} an array holding the names of all the columns of the table
Returns an array of column names for the specified table
var column_names = db_table_columns("items");
name {string} name of the table to create
columns[] {array} array holding the column names and default values, as described below.
returns {real} -1 if the table already exists, a number >= 0 otherwise
Creates a new table in the currently open database. Note that the table name is used as filename when saving, avoid special characters not
compatible with the file system.
Columns should be passed as an array of arrays, where every entry defines the column name and its default value (either a string or a real value), determining also
the column data type.
db_table_create("items",[ ["name",""], ["value",0], ["category","no category"] ]);
table_name {string} name of the table
Deletes a table and all its contents from the memory AND file system (if present).
db_table_delete("items");
table_name {string} name of the table
returns {boolean} whether the table exists in the currently database or not
Returns true if a table with the provided name exists in the database, false otherwise.
if(!db_table_exists("items")) {
//do something
}
table_name {string} name of the table
path {string} path and filename
returns {boolean} true if export is successful
Exports a table to file at the specified path. The table can be imported back by using db_table_import.
db_table_export("items","exported_data/items.table")
table_name {string} name of the table
returns {boolean} true if export is successful
Exports the specified table to a CSV file, using the provided separator (generally ",").
db_table_export_csv("items","items.csv",",");
table_name {string} name of the table to flush
Flushes (removes) the table data from the memory, leaving the saved table data untouched.
Flushed tables will be automatically loaded back on the next request that requires the table data.
For more information, see the "Memory management" section.
IMPORTANT: flushing a table that has already been peristed to file has no effect
on such file, but will remove all unsaved changes from the memory, therefore you may want to save
the table before flushing.
The above is only true however for persisted tables. If a table has never been saved to file and gets flushed,
a save operation is automatically called beforehand, to prevent data loss.
db_table_flush("items");
path {string} path to the import file
returns {boolean} true if successful
Imports a table previously saved using db_table_export to the currently open database. If the table already exists, this function returns false and the data will not be imported.
db_table_import("items.table");
table_name {string} name of the table to import the data to
path {string} path to the CSV file
sep {string} CSV separator character
returns {boolean} true if successful
Adds the contents of a CSV file into the specified table as new records (note that the table
has to already exist).
The first row of the CSV file has to hold the column names in order to match those
with the table structure, but you are not required to list all of the table columns
or only the table columns in the CSV file, since extra columns will be ignored and missing columns
will get the default value.
db_table_import_csv("items","data/items.csv",",");
table_name {string} name of the table
returns {boolean} whether the table is loaded or flushed
Returns whether the table is loaded into memory. Tables get automatically loaded from the file system on the first request, but you can manually manage this process by using db_table_load and db_table_flush when appropriate. See "Memory management" section for details.
table_name {string} name of the table to load
Loads a table into memory from the file system, if not already loaded.
You are not required to call this since tables get loaded automatically on first use,
but you may want to do so manually for example as part of a loading process to avoid the file
reading overhead.
See "Memory management" section for details.
table_name {string} name of the table to save
returns {boolean} true if successful, false if file can't be opened or created
Save a table to the file system, at its predefined position.
table_name {string} name of the table
returns {real} the amount of records currently held by the table
Returns the number of records stored in the table.
While queries are useful for bulk operations, there are a number of functions that allow you to easily, and efficiently, manage your database records. These also include the means to create new records in a few different ways.
Some of the record functions return a representation of a database record as a
custom structured ds_map.
Records returned or created this way will have the column names as keys, and you can use
those to update or read the record values locally, and finally send the updated record to
the database using db_record_save.
Other than the table columns, a record ds_map defines the following keys:
record[? "_table_name"] holds the name of the table the record is linked to
record[? "id"] holds the record id. If the record has not been saved to the database
yet (as a result of db_record_build() for example), this key is not present in the ds_map.
Please note that unless stated otherwise by the function generating the record ds_map, you are in charge of destroying the ds_map when not needed anymore.
table_name {string} name of the table
[values[]] {array} (optional) array of column/value pairs (see example)
returns {ds_map} the ds_map representation of the record
Creates and returns a new record ds_map, without actually inserting
the record into the database table.
You can provide the record values directly by passing an array of column/value pairs
as function arguments, or set them manually afterwards (or both).
You are responsible for destroying the ds_map when not needed anymore.
var record = db_record_build("items",[ ["name","sword"], ["weight",20] ]);
record[? "category"] = "Weapon";
db_record_save(record);
ds_map_destroy(record);
table_name {string} name of the table
values[] {array} array of column/value pairs
returns {real} the record id
Inserts a new record directly into the table having the specified values.
Values should be passed an array, where every entry is itself an array with the column name
and corresponding value.
Table columns that are not specified in the values array will get the default value.
db_record_create("items",[ ["name","sword"], ["weight",20], ["category","Weapon"] ]);
table_name {string} name of the table
id {real} id of the record to delete
returns {boolean} false if the record does not exist, true if successful
Deletes the record with the given id from the table.
if(db_record_delete("items",3)){
show_message("Item deleted successfully");
}
record {ds_map} record ds_map
Deletes a record by using its ds_map representation, and destroys the record ds_map as well.
db_record_destroy(record);
table_name {string} name of the table
id {string} id of the record to check
returns {boolean} whether the record exists in the table or not
Returns true if the record with the provided id exists in the table, false otherwise.
if(db_record_exists("items",3)){
show_message("Item with ID 3 exists!");
}
table_name {string} name of the table
id {real} id of the record to fetch
[columns[]] {array} (optional) array of columns to fetch for the record
returns {ds_map|real} the record ds_map, or -1 if not found
Returns a ds_map representation of the record having the specified id.
If no valid record with id is found, -1 is returned.
The ds_map contents can be altered without affecting the database, until you call db_record_save(record),
which will write the changes back.
Note that you are responsible for deleting the ds_map once you don't need it anymore. This will not delete the record from the database.
//Get the record with id 3 from the items table
var item = db_record_fetch("items",3);
//double its weight
record[? "weight"] *= 2;
//update its name
record[? "name"] = "Refined " + record[? "name"];
//save the changes back to the database
db_record_save(record);
//delete the ds_map, not needed anymore
ds_map_destroy(record);
table_name {string} name of the table
ids {array|ds_list} ds_list or array of record ids
[columns[]] {array} (optional) array of columns to fetch for each record
returns {ds_list} a ds_list of record ds_maps
Works like db_record_fetch, but returns multiple records (as ds_maps) in a ds_list.
Note that you are responsible for deleting the ds_list once you don't need it anymore. Deleting the ds_list will automatically delete all the record ds_maps in it.
//Get the records with id 1, 4 and 9
var records = db_record_fetch_all("items",[1,4,9]);
table_name {string} name of the table
column {string} column name
value {string|real} value to look for
returns {real} id of the record, or -1 if not found
Returns the id of the first record having the specified value at the specified column. Returns -1 if no record is found.
var apple_id = db_record_first("items","name","Apple");
if(apple_id != -1){
show_message("Apple id: " + string(apple_id));
}
table_name {string} name of the table
amount {real} number of records to return
unique {boolean} tells whether you may or may not have duplicates in the returned data
returns {ds_list} ds_list of record ids
Returns a ds_list of N record ids, where N is the amount passed as argument.
If unique is set false, the list may contain duplicate ids. If set to true,
no duplicate ids will be returned.
//tries to return a random item from the items table
var result = db_record_find_rand("items",1,false);
if(!ds_list_empty(result)) {
var _rand_id = result[| 0];
}
ds_list_destroy(result);
table_name {string} name of the table
id {real} id of the record
column {string} column where the value is stored
returns {real|string|undefined} the value stored in the specified column for the specified record id.
Returns the value in a column for the record with the specified id. If the record is not found, undefined is returned.
var item_name = db_record_find_value("items",1,"name");
record {ds_map} record ds_map
Saves the specified record ds_map (as generated by the other record) to the database table it is linked to. If the record is new, it is created. In case of an existing record, the values are updated.
var record = db_record_build("items",[ ["name","sword"], ["weight",20] ]);
record[? "category"] = "Weapon";
db_record_save(record);
ds_map_destroy(record);
table_name {string} name of the table
values[] {array} array of column / value pairs
returns {boolean} true if the record exists and has been updated, false otherwise
Updates the values of the record having the specified in the specified id. Values should be passed as an array of arrays holding column / value pairs.
db_record_update("items", item_id, [ ["name","Heavy sword"], ["weight",999] ]);
Queries are structured requests performed on data held by one or more database tables. GMSDB allows 4 types of query:
SELECT: select queries are used to retireve data from a table, in the form of
a ds_grid of columns and values, record ds_maps, or ds_list of ids. Each of these cases has its own
select query function.
UPDATE: update queries can change the values of one or more columns of all records
that match the query parameters
DELETE: delete queries are used to destroy records that match query parameters
CALCULATE: calculate queries perform some kind of computation on the records, like count,
sum, min, max and mean.
All of the above need to be created by using their respective functions, and executed using db_query_exec(query) in order to get the results.
table_name {string} name of the table to query
columns[] {array} array of columns to retrieve
returns {real} the query id to be used in the next query calls
Executing the query with db_query_exec() returns a ds_grid where every row is a record with its values in the specified columns. If no results are found by the query, -1 is returned.
Important: Remember to destroy the returned ds_grid after you are finished to avoid memory leaks
/* Selects the name and weight of
all the records in the items database. */
var query = db_query_select("items",["name", "weight"]);
results = db_query_exec(query);
table_name {string} name of the table to query
returns {real} the query id to be used in the next query calls
Executing the query with db_query_exec() returns a ds_list of record ids matched by the query parameters.
Important: Remember to destroy the returned ds_list after you are finished to avoid memory leaks.
var query = db_query_select_ids("items");
table_name {string} name of the table to query
[columns[]] {array} (optional) array of columns to retrieve
returns {real} the query id to be used in the next query calls
Executing the query with db_query_exec() returns a ds_list of records (as ds_maps). The returned records will hold the values for the specified columns. If the columns argument is not provided, all columns are returned.
Important: Remember to destroy the returned ds_list after you are finished to avoid memory leaks. This will also destroy the record ds_maps automatically.
var query = db_query_select_records("items");
table_name {string} name of the table to query
returns {real} the query id to be used in the next query calls
Executing the query with db_query_exec() deletes all the matched records from the table.
This type of query is generally restricted by some clauses, otherwise all records in the table
will be deleted (if that's the goal, consider using db_table_clear).
Query execution returns the number of records deleted.
/* Deletes the items with id == 4 and id == 6 from the table */
var query = db_query_delete("items");
db_cl_only(query,[4,6]);
var number_of_deleted_items = db_query_exec(query);
table_name {string} name of the table to query
columns[] {array} array of columns to update
values[] {array} array of values to insert in the specified columns
returns {real} the query id to be used in the next query calls
Update queries replace the values held by the matched records at the specified columns.
columns and values have to be passed as an array, where every entry in the column array has
a counterpart at the same position in the values array.
Executing the query returns the number of affected records.
var query = db_query_update("items",["name","weight"],["new_name",10]);
table_name | query {string|real} either a table name or an existing query
operation {real} a calc operation from the DB_CALC enum
[column] {string} column to apply the calculation to (not required by the count operation)
returns {real} the query id to be used in the next query calls
Generates a calculation query, either new or by using the values of an existing query of any other type (as a copy).
The return value of this query is always a real number, computed based on the operation operation.
Valid operations:
DB_CALC.op_count: counts the number of records matched by the query
DB_CALC.op_sum: performs a sum of the values of held by the matched records at the specified column
DB_CALC.op_min: finds the lowest value in the specified column among the matched records
DB_CALC.op_max: finds the highest value in the specified column among the matched records
DB_CALC.op_mean: gets the mean of the values of held by the matched records at the specified column
// Counts the number of records of type Weapon in the items table, using a select query as input
var query = db_query_select("items");
db_cl_where(query,db_op_eq,["category"],["Weapon"]);
var count_query = db_query_calc(query,DB_CALC.op_count);
var weapons_count = db_query_exec(count_query);
A query by itself is not really useful unless you apply some clauses. Clauses can narrow down and refine the results of a query. With clauses you can filter, limit, offset, sort your results, as well as join them with records in another table.
Clauses can be overwritten by calling the clause function on the same record. Moreover, every clause has some default values that can be used to remove the effect of the clause on the query.
query {real} query id to apply the clause to
join_table {string} name of the table to join
foreign_key {string} name of the column in the queried table that holds the join_table ids
The join clause allows to merge the values of another table to the results of the query.
In order for this to work, the queried table has to define a column that holds the ids of the records reference by the join table.
That column is said to hold the "foreign keys".
This is best explained by a practical example: suppose you have two tables in a trading card game,
"cards" and "deck", where "cards" holds information about all the cards present in the game, and "deck" stores
the card ids from the card table that you have in your current deck.
You may want to perform a query on the deck, but the deck itself does not store the actual card information, only the
card ids, and therefore you need to "attach" the card data to each record of the deck table by using a join.
(see example below)
Passing an empty string as join table name will remove the clause from the query.
IMPORTANT: when using a join clause, you can sort, filter etc... your results by using not only the
main table data, but also the join table data. Due to this, when using a join clause, all references to columns HAVE to
include the table prefix, as in: "first_table.id" or "second_table.name".
The only exception to the above is represented by the values passed to db_query_update().
You are allowed to use a join on update queries, but you can only change the main table values, therefore you should
not use any prefix in this specific case.
/* Getting all the cards referenced in your "deck" table, along with their
information from the "cards" table */
var query = db_query_select("deck",["deck.card_id","deck.quantity","cards.name","cards.value"]);
db_cl_sort(query,[["cards.name","asc"]]);
db_cl_join(query,"cards","card_id");
results = db_query_exec(query);
This clause applies a limit and/or an offset to the result set. Apart from limiting the results for select queries, this can also be used on delete and update queries to limit the scope of the operation. You can for instance update/delete only the first N records matching your criteria.
Offset instead is used to ignore the first N records from the result set, useful for instance for paginating your results if used in conjunction with a limit.
Setting the limit and / or offset to 0 will remove its effect from the query.
/* Find the ids of the top 3 heaviest weapons */
var query = db_query_select_ids("items");
db_cl_where(query,db_op_eq,["type"],["Weapon"]);
db_cl_sort(query,[ ["weight", "desc"] ]);
db_cl_limit(query,3,0);
results = db_query_exec(query);
query {real} query id to apply the clause to
ids {ds_list | array} either a ds_list or array of record ids
This clause applies the query only to records having the specified ids.
The ids can be passed either in a ds_list, or in an array.
This clause is extremely helpful when deleting or updating a specific set of records, and
has a positive impact on the query peformance.
Passing -1 instead of a list or array as argument, removes the clause form the query.
/* Set the weight of the records with id 3, 4 and 8 to 0 */
var query = db_query_update("items",["weight"],[0]);
db_cl_only(query,[3,4,8]);
db_query_exec(query);
query {real} query id to apply the clause to
sort_by[] {array} an array of sorting directives
This clause determines the order of the results returned or considered by the query.
sort_by is an array where every entry is itself an array structured as follows: [column_name,order,(script)].
colum_name is the column used by the sort operation, order is either the string "asc" or "desc", and script is
an optional parameter representing a script index to be used for a custom sort (more on this in the "Custom sorting"
section).
You can sort by more than one column by providing more entries like the above to the sort_by array. The order of the entries
determines the precedence (from highest to lowest).
Example: db_cl_sort(query, [ ["category","asc"], ["name","asc"] ])
The above will sort the results first by category, and then by name for records having the same category.
Note that this clause is obviously useful when using select queries, but can also be applied to delete and update queries, where it has a meaning only if coupled with the limit clause (you can for instance delete/update only the first N records).
If you pass the string "RAND" instead of an array as sort_by argument, results are returned sorted randomly. An empty string as sort_by instead means no sorting will be applied.
/* Finds all the items sorted by name */
var query = db_query_select("items");
db_cl_sort(query,[ ["name","asc"] ]);
results = db_query_exec(query);
query {real} query id to apply the clause to
op_script {script} script used to filter the results
columns[] {array} array of columns involved in the filter
args[] {array} values to be used as filter arguments
This clause filters the records based on the specified conditions and values.
The filter applied depends on the operation script passed as argument. GMSDB comes with a few
common scripts that can be used to filter the results by comparing the contents of the
specified columns with the provided args, but any kind of custom filter can be added by
defining new script as described in the "Creating custom filters" section.
Available scripts:
db_op_eq: checks the provided columns against the args for equality
db_op_neq: checks the provided columns against the args for inequality
db_op_gt: checks for values grater than a number
db_op_lt: checks for values less than a number
db_op_gte: checks for values grater or equal than a number
db_op_lte: checks for values less or equal than a number
db_op_between: checks inclusion of values between two specified numbers
Passing -1 as script in the arguments resets the clause and no filter is applied.
/* Deletes all items having weight between 20 and 40 */
var query = db_query_delete("items");
db_cl_where(query,db_op_between,["weight"],[20,40]);
db_query_exec(query);
Queries are executed only by calling db_query_exec(). This function will return the results of the query. Queries by default are destroyed after execution, but you can instruct db_query_exec to avoid doing this and make the queries reusable (remember that clauses can be overwritten).
It is important to note that queries that are not executed or explicitly not destroyed by db_query_exec need to be destroyed manually (like data structures) by using db_query_destroy().
query {real} the query to run
[keep] {boolean} (optional) if set to true, the query will not be destroyed automatically after
execution. Defaults to false.
returns the query results (depending on the query)
Executes a query. The return value depends on the type of query passed as argument. The optional keep parameter (defaults: false) when true will instruct not destroy after the function call. This means that you can actually keep a query and execute it as many times as you want while changing the clauses meanwhile. A query if kept needs to be destroyed manually afterwars with db_query_destroy or by executing it the last time with keep set to false (or omitted).
/* Executes a select query but keeps it for a further call. */
var query = db_query_select_ids("items");
db_cl_where(query,db_op_eq,["category"],["Weapon"]);
var results = db_query_exec(query,true);
//run the query again, but changing the filter.
db_cl_where(query,db_op_eq,["category"],["Armor"]);
results2 = db_query_exec(query);
query {real} the query to destroy
Deletes a query freeing the memory used (use only if you execute the query with keep == true) or never executing it.
GMSDB, unlike other file based database systems like MySQL or SQLite, stores all the data of an open database into the memory (RAM) as long as the database is open, persisting that data to file on demand. open.
This approach grants a lot of advantages in terms of speed and flexibility, avoiding the overhead of reading / writing a file for every request,
and allowing you to manage your data without affecting the actual save files until explicitly requested.
The other side of the coin is that large databases end up a considerable amount of memory,
which can be a concern especially on devices with limited capacity.
To address this problem GMSDB provides a mixed approach to memory management, allowing some degree of control on what's loaded into memory on a per table basis.
When you open an existing database only the structure of the database gets loaded into memory.
The table data gets instead loaded from the database files only at the first
request on that specific table. This is generally ok in most cases, but sometimes it makes sense to
load the data into memory at a very specific time, as part for example of a loading screen, or simply to avoid
the overhead of reading the data from file during the gameplay.
You can do that by using db_table_load(table_name). This function forces the table data to be loaded into memory from the saved table file, instead of doing so on the first request.
Note that if the data is already loaded, this call has no effect.
It is also possible to check whether a table is loaded by using db_table_is_loaded(table_name).
Flushing a table with db_table_flush(table_name) deletes all the table data from the memory (without touching the saved table files), essentially reverting the table back to its unloaded state. The table will be automatically loaded back into memory on the next request, or by using db_table_load(table_name) as described above.
When flushing a table, you have two consider two scenarios to understand what exactly happens:
A. The table has never been saved to file
In this case, since no table file exists yet, the table is saved automatically before flushing in order to
prevent data loss and allowing the data to be loaded back when needed.
B. The table already has a save file
In this case, the current table data in the memory gets deleted without affecting the save file.
This means that any unsaved changes will be removed as well!. You can used this to your
advantage, but in most cases you may want to save the table manually before flushing.
By using db_cl_sort you can sort the result of your queries based on the value of one or more specific columns. Sometime though, you may want your data sorted in a more dynamic way, for example by using the result of a function on the actual column value as input instead of the value directly. This is possible by creating a simple script that gets the value as input, and returns the updated value to be used when sorting.
A script as simple as this can be used for example to sort by string length:
/// @desc my_string_length(str)
/// @arg str
return string_length(string(argument0));
The script can be then used in db_cl_sort as follows:
db_cl_sort(query,[ ["name","asc",my_string_length], ["name","asc"] ])
The clause in the code above applies two sorting criteria: first sorts the results by the string length of the name column in ascending order, and then, for those records having the same number of characters, sorts by name.
One of the most common things you may want to when performing queries is apply some
filters to the resul set. GMSDB lets you apply some basic filtering by using the where
clause (db_cl_where), where you are asked to specify one of the provided db_op_* scripts for comparing
the database values.
Sometimes though you may need some very specific login applied to your query filter, like a mix of AND / OR
expressions or applying the result of some other expression to the values (think for example filtering based
on string_length or using a modulo on a number).
In those cases, you are free to implement your own db_op script, by following a few rules as described below.
The first thing you may want to do is take a look at the db_op_* scripts provided with the extension
to get a grasp of how they work. The concept behind them is pretty simple: when you perform a query that
uses a where clause, every record in the database is tested against the condition script by passing
the values of the columns and the values specified in the clause as arguments.
If the script returns true, the record matches the condition and is kept, otherwise it is skipped.
Let's create an example condition script called db_op_str_len that filters out records having more than a certain number of characters in a specific column.
We start by creating our db_op_str_len script, keeping in mind the following:
1. The script has to return true if the provided string has less the N characters,
false otherwise.
2. The script (as all other db_op scripts) gets two arguments, both arrays. argument0
contains one or more column values, argument1 holds the values you specified in
db_cl_where(query,columns[],values[]). In this case the it's number of minimum characters.
3. For simplicity, we will assume only one column and one value will ever be passed as arguments.
to db_cl_where when using this filter.
/// @desc db_op_eq(columns[],values[])
/// @arg columns
/// @arg values
/*
Operation to be used in db_cl_where. Excludes strings having less than the specified amount of characters
*/
var _string = argument0[0];
var _length = argument1[0];
return string_length(_string) >= _length;
We can now create queries using our script as where clause:
/* Get all the items with a name having 5 or more characters */
var query = db_query_select("items",["id","name"]);
db_cl_where(query,db_op_str_len,["name"],[5]);
result = db_query_exec(query);
The following are some performance tips for using GMSDB efficiently.
Due to the major changes undergone by the system and database structure in the latest update, databases created in the older versions of the asset are no longer compatible. If you have some data that needs to be ported, consider exporting to CSV.
Contact me on the GMC forums or by email at simoneguerra<at>ekalia.com