About

GMSDB is a database system written entirely 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 a database table while applying custom filters, sorting, limits, offsets, calculations and joins on the result set.

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, it is also not comparable to real database solutions like the above.

Moreover, unlike most based database systems, a GMSDB database and all of its data is kept in the program memory as long as the program or the database are open. While the above allows to avoid the overhead of file operations, it also means that in general GMSDB may not the best solution for extremely large datasets.

Install

1. Open GameMaker: Studio, log in to the Marketplace and navigate to the Library tab. From here you can download the extension resource into your project.
2. Next, go into the extensions folder in the resource tree, double click the GMSDB extension and select the "Import resources" tab.
3. To include the database system into your project, just import all the scripts. If you want to look at the provided examples select "Import all".

Getting started

Initialize

The first thing to do when using GMSDB is to initialize the system somewhere in your game with db_init(), keeping in mind that you have to do this ONLY ONCE, unless you released the DB system with db_free(). After that the next step is to open a database, either existing or new:

db_init();
db_open("awesome_database");

Creating a table and adding some values

Once a database is open it is possible to start adding data, but first you need at least one table.
The following will create an "items" table and add a few records to it.
Note that every table is automatically assigned a column called "id", that holds a unique auto incrementing numeric value for every record stored in that table, which is a key aspect of DB management.
Important: table names are used as filenames when saving, so it is important not to have spaces in their names or special characters other than "_".

db_table_create("items","name:string","type:string","value:real","weight:real");

db_record_create("items","Claymore","Sword",1500,20);
db_record_create("items","Iron helm","Armor",120,8);
db_record_create("levels","Wooden bow,Bow,80,4"); //You can also pass values as a comma separated string

Perform a simple lookup and update a record

With some records in our table, we can then start working with the data. The simplest way to perform some basic operations is by using the db_record_* functions. These functions will generally provide a fast and easy way to get data back from a table without having to rely on a query.
If you need to work on single records, it is advised to use those functions instead of queries.

//Get the id of the first item of type "Sword"
var sword_id = db_record_first("levels","type","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 (not stored yet into the db!)
sword[? "value"] = sword[? "value"] * 2;

//Save the sword back to the database table
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);

Performing queries

Queries are the primary mechanism for retrieving and manipulating information from the database. For more in-depth information refer to the queries section. Here are a couple of use cases applied to our "items" table as created in the previous examples.

Get all the items and all their data ordered by weight.

//Create a select query and applay a sort clause
var query = db_query_select("items","id,name,weight");
db_cl_sort(query,"weight:asc");

//Finally, execute the query and return the results
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, holding the data in the grid x axis as specified in db_query_select. Note that you can also return only a ds_list of record ids with db_query_select_ids or a list of ds_maps with db_query_select_records (where every map is a record having its attributes as keys)

Get the first 10 items having a weight >= 20, sorted by value

var query = db_query_select("items");
db_cl_sort(query,"value:desc");
db_cl_where(query,db_op_gte,"weight","20");
db_cl_limit(query,10,0) items = db_query_exec(query);

Perform two similar queries by reusing the same query twice. First we get the 10 most valuable items of type "armor". Then we do the same but for items of type "sword"

//If no column is specified in the select query, all columns are returned.
var query = db_query_select("items");
db_cl_where(query,db_op_eq,"type","Armor");
db_cl_sort(query,"value:desc");
db_cl_limit(query,10,0);
//By passing "true" as second argument, the query will not be destroyed after execution
armors = db_query_exec(query,true);

//Now we change (overwrite) the filter we set before in order to apply the query to items of type "Sword" instead. Then the query is executed and automatically destroyed
db_cl_where(query,db_op_eq,"type","Sword");
swords = db_query_exec(query);

Set all items value to 1

var query = db_query_update("items","value",1);
db_query_exec(query);

Saving, importing and exporting the database and its data

Since version 3.2, you no longer need to specify a location where you want your database saved, calling db_save() will persist all the data to file in a folder of the game sandbox.

You can still however export and import the whole database to to and from a single file using db_export and db_import.

Opens a database named "rpg_database". If the database doesn't exists, a new one is created, otherwise the last saved data is loaded. The database is then saved and, since all the data is not stored in some files, it is closed.
Important: the database name is used as folder name when saving, so it is important not to have spaces or special characters other than "_".

It is also possible to set the database to save automatically every time a change in its structure or data occurs, with db_autosave(enabled). See reference for more information.

db_open("rpg_database");
//[...]do some stuff[...]
db_save();
db_close();

Export the whole database data to file, and afterwards import the data back into a database called "awesome_database".

db_export("database.gdb");
//[...]do some stuff[...]
db_import("database.gdb","awesome_database");

Export a database table as CSV in order to see its contents in programs like excel.

db_table_export_csv("items","items.csv",",");

Closing a database

When you don't need the database anymore, you can close it by calling db_close(). This will free all the database data from the memory. To load an existing database or create a new one, you now need to call db_open, or db_import.

If you have no plans on using the database system again, you can go a step further and delete all the db system data by calling db_free().

db_close();

Reference

Database

db_autosave([enabled])

If the autosave functionality is enabled (disabled by default), whever a change occurs in the database data or its structure, the database is saved automatically, so you don't need to call db_save() anymore.

By passing true or false as argument, you can enable or disable the autosave functionality.
If no argument is passed, the current state of autosave is returned.

Important: when adding, updating or removing records from a table using multiple calls to db_record_create, db_record_update / save, db_record_delete, remember to disable temorarily the autosave functionality, or the database will be saved every time one of this functions is called. See example below.

//Enable autosave
db_autosave(true);
//Now, after adding an item, the database is automatically saved to file. No need to call db_save()
db_record_create("items","Longbow","Bow",10,21);

//Since we perform a lot of changes to the items table in a row, it's advised to disable autosave so that the database is saved only once at the end of the operation, not every new record inserted.
db_autosave(false);
repeat(10) { db_record_create("items","Test item",1,1) }
//Enabling autosave automatically saves the database and the previous inserted data.
db_autosave(true);
db_close()

Closes the currently open database, freeing the memory used.

db_exists(db_name)

Returns true if the database with the given name exists.

db_export(filename)

Export the currently open database and all its data to file.

db_free()

Close the current database and free all database system variables.

db_import(filename,db_name)

Import database from a file saved using db_export(), as a new database called db_name. Not that any currently open databases will be closed. If a database named as db_name exists, it is replaced.

db_name can not contain any special characters other than "_" or spaces, since it is used as filename.

db_import("db.gdb","my_database");
db_import_legacy(filename,db_name)

Works exactly like db_import, but reads an old database format instead (saved with GMSDB less or equal 3.1)

db_init()

Initializes the database system. Must be called only once, and before any other function. Called ideally at game start.

db_list_tables()

Returns a ds_list holding the currently open database table names. The list must be freed to avoid memory leaks.

db_open(db_name)

Opens a new database called db_name, creating one if it doesn't exist, or load the data of an existing one.

Important: When a new database is opened, a directory with the same name is created in the sandbox folder of your game.
If you want to place the database in a subdirectory, you are free to use a name like "subfolder/database".
This also means that the database name can not contain any special characters other than "_" or spaces.

db_open("my_database");
db_save()

Saves the currently open database.

Tables

A table can be seen as a grid of values with named columns, kind of like an excel table. In GMSDB tables are identified by their name (a string) and are available globally.
in order to work with tables and table data you just need to provide the table name as parameter to the scripts, and the system will automatically address the right data, independently from the where you are making the call.

Tables always hold special column called "id". The id is a unique, auto incrementing identifier number assigned to each record of the table, and is automatically set when inserting a record into the table. ids should never be altered as they are managed internally by the system. Record ids are never re-assigned, even if all the records have been deleted from the table. (with the exception of db_table_clear())

The following functions exist to work with tables:

db_table_clear(table_name)

Deletes all the records in table, resetting the ids index (that will start from 1 again), but keeping the table structure intact.

db_table_clear("items");
db_table_column_add(table_name,column_name,type,default)

Adds a column to a table.
type is either "real" or "string", telling the column type. default is the value to insert in the new column for all existing records.

db_table_column_add("items","rarity","real",1);
db_table_column_names(table_name)

Returns a ds_list of ordered column names for the specified table. The list has to be destroyed after usage to avoid memory leaks.

var column_names = db_table_column_names("items");
db_table_column_remove(table_name,column_name)

Removes a column and all its values from a table

db_table_column_remove("items","rarity");
db_table_create(name,columns), db_table_create(name,column1,[column2],[columnN],...),

Creates a table in the database having the specified columns.
The "id" column should NOT be specified, as it is autogenerated.
If a table already exists with the same name, false is returned, true otherwise.
Fields can be passed as comma separated strings (Eg: "name:string,color:string,weight:real"), or as arguments (Eg: db_table_create("items","name:string","color:string","weight:real").
IMPORTANT: The name of the table can not contain spaces or special characters other than _ (underscore).

/* Creates a table named items, holding three fields: name, type and weight */
db_table_create("items","name:string","type:string","weight:real");
db_table_delete(table_name)

Deletes a table and all its data, freeing the memory.

db_table_delete("items");
db_table_exists(table_name)

Returns true if a table with the provided name exists in the database, false otherwise.

if(!db_table_exists("items")) {
//do something }
db_table_export(table_name,path)

Exports the table data to file. The table can be imported back using db_import.

db_table_export("items","items.gdt")
db_table_export_csv(table_name,path,sep)

Exports the contents of a table to a CSV file. Values are separated based on the sep argument. Useful for debugging or manipulating the data in excel or similar programs.

db_table_save_csv("items","items.csv",",");
db_table_import(path)

Imports a table from a file previously saved using db_table_export. If the table already exists, it is replaced.

db_table_import("items.gdt");
db_table_import_csv(table_name,path,sep)

Imports data into the specified table from a CSV file. Note that the table should already exist in the database, and the csv data is just added to the current table data.

db_table_import_csv("items.csv");
db_table_size(table_name)

Returns the number of records stored in the table.

show_message("There are " + string(db_table_size("items")) + " items in your database");

Record operations

Record function allow to read, update, create and get information about database records. If you need to perform some simple operations on your data (like updating a specific column of a specific record) you can use these functions instead of queries.

db_record_build(table,[name1:value1],[name2:value2],..)

Generates and returns a ds_map representing a record in the specified table, with columns as keys. You can optionally specify some predefined data in the form "name:value" where name is a column name in the table.
Note that calling this function does NOT automatically save the record in the specified table, you have to call db_record_save(record_map) for that, or simply use db_record_create() instead.

As long as the record is not saved, the "id" key of the ds_map is set to -1. After being saved, it will automatically be filled with the generated id.

You can change the record values at any time before and after saving. Saving an existing record will update its values instead of creating a new one.

Important: remember to delete the ds_map when not needed anymore, to avoid memory leaks. This does not delete the record from the table.

//create a new record (not yet saved to the database)
var new_record = db_record_build("items","type:Food","name:Banana","value:10","weight:2");
//change the weight and save to database
new_record[? "weight"] = 3;
db_record_save(new_record);
//delete the ds_map, not needed anymore
ds_map_destroy(new_record);
db_record_create(table,values)
db_record_create(table,value1,value2,value3,...)

Inserts a new record in the database table and returns the newly inserted record id.
The values need to be passed in the same order as you defined your table columns, and can either be passed as string of comma separates values, or as script arguments.
If passed as string of comma separated values, CSV format applies, this means you can skip special characters like in the CSV specifications.
It is also possible to generate and sanitized CSV formatted string by passing the values to the script csv_compose_line() in a ds_list.

db_record_create("items","Healing potion","Potion",30,1); //values as arguments
db_record_create("items","Antidote,Potion,40,1"); //values as string
db_record_delete(table_name,id)

Deletes the record with the given id from the table. Returns false if the record doesn't exist, true otherwise.

if(db_record_delete("items",3)){
show_message("Item deleted successfully");
}
db_record_destroy(record_map)

Deletes the given record represented as ds_map from the database, destroying the ds_map as well.

db_record_destroy(record);
db_record_exists(table_name,id)

Returns true if the record with the provided id exists in the specified table, false otherwise.

if(db_record_exists("items",3)){
show_message("Item with ID 3 exists!");
}
db_record_fetch(table,id,[columns])

Returns a ds_map representation of the record having the specified id, with all its data saved as values, and the respective columns as keys.
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_map), which will write the changes back.

Optionally, you can specify which columns to return. Normally you'd want all of them, but if you only need a few specific ones, you can do so for performance reasons. By default all the columns are returned.

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 "item_id" from the items table
var item = db_record_fetch("items",item_id);
//double its value
record[? "value"] *= 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);
db_record_fetch_all(table,ids,[columns])

Returns a ds_list of records, represented as ds_maps with record data as values, and the respective columns as keys.

The ds_maps contents can be altered without affecting the database, until you call db_record_save(record_map), which will write the changes back.

Optionally, you can specify which columns to return for every record. Normally you'd want all of them, but if you only need a few specific ones, you can do so for performance reasons. By default all the columns are returned.

Note that you are responsible for deleting the ds_list once you don't need it anymore. Deleting the returned list will automatically delete all the ds_maps in it.

//Get a list of items from a previously generated list of ids
var records = db_record_fetch_all("items",ds_list_of_ids);

//increase the weight by 1 for all returned records
for(var i=0;i<ds_list_size(records);i++) {
var record = records[| i]; record[? "weight"] = record[? "weight"]+1; db_record_save(record); }
ds_list_destroy(records);
db_record_first(table_name,column,value)

Returns the id of the first record having "value" at the specified column. Returns 0 if no record is found.

var apple_id = db_record_first("items","name","Apple");
if(apple_id > 0){
show_message("Apple id: " + string(apple_id)); }
db_record_get(table_name,id,column)

Returns the value in the specified column of the record with the given id. If there's no record with that id, undefined is returned.
This can be useful if you need a couple of specific values for a record id you already have, but for more complex cases a select query should be used instead.

var item_name = db_record_get("items",item_id,"name");
db_record_rnd(table_name,n)

If n is equal to 1, a random record id is returned, or 0 if no records found.
If n is greater than 1, the function returns a ds_list of non-unique n record ids. If no record is found, an empty list is returned.
Note that the function may return duplicate ids, if you need a list of unique records ids, use a query instead.

var random_item_id = db_record_rnd("items",1);
db_record_save(record_map)

Saves a ds_map representation of a record (obtained by one of the following: db_record_build(), db_record_fetch(), db_record_fetch_all()) back to the database.

If the record is new, it gets inserted, if it is an existing record, its values get updated.

The function returns true if successful (e.g: returns false if it has an invalid id or the record has been deleted meanwhile).

//Get the record with id "item_id" from the items table
var item = db_record_fetch("items",item_id);
//double its value
record[? "value"] *= 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);
db_record_update(table_name,id,columns,values)
db_record_update(table_name,id,columns,value1,value2,...)

Updates the values in the specified columns of a record with the specified id. Returns true if successful, false otherwise.
The columns parameter is a string of comma separated columns. Values can also be passed as a single string, or as arguments.

if(db_record_update("items",item_id,"name,value","Super banana",2500)){
show_message("Item updated successfully");
}

Queries

Queries are the primary mechanism for retrieving and manipulating information in the database. Queries provide a lot of flexibility when working with database data, allowing you to return and change multiple records in a single call by applying filters like sorting, conditions, limits, etc...

There are 4 main types of query operations: select, update, delete and calculate
All queries, except calc, need to be created first and executed with db_query_exec();

Query operations

db_query_select(table,[fields])

Select queries are used to retrieve a specific set of fields from a table. Fields need to be passed as a string of comma separated column names (you can pass and empty string, or omit the argument, to return all the fields)
The return value of db_query_exec() for a select query is either -1 if no record is found, or a ds_grid of the requested values.

The function returns a query structure that needs to be passed to db_query_exec() to be executed and retrieve the results, and can be passed to query clauses for filtering, sorting, etc...

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);

/* Selects all the items of type "Sword", with all their attributes, from the table. Results are sorted by name. */
var query = db_query_select("items");
db_cl_sort(query,"name:asc");
db_cl_where(query,db_op_eq,"type","Sword");
results = db_query_exec(query);
db_query_select_ids(table)

Works exactly like db_query_select, but the query execution returns a ds_list of record ids instead of a ds_grid.

Important: Remember to destroy the returned ds_list after you are finished to avoid memory leaks.

db_query_select_records(table,[fields])

Works exactly like db_query_select, but the query execution returns a ds_list of record ds_maps instead of a ds_grid (like db_record_fetch_all()).

Important: Remember to destroy the returned ds_list after you are finished to avoid memory leaks. This will also destroy the record ds_map automatically.

db_query_delete(table)

Delete queries are used to delete records from the database. This kind of query is usually associated with some clauses (see clauses section below) in order to restrict the range of the deleted records, otherwise all table records are deleted.
The generated query, like all other queries, needs to be executed with db_query_exec(), which will return the actual number of records deleted from the query execution.

/* 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);
db_query_update(table,fields,values)
db_query_update(table,fields,value1,value2,valueN,...)

Update queries are used to update specific fields of one or more records. This kind of query is usually associated with some clauses in order to restrict the range of the updated records, Otherwise all records are affected.
The fields and values parameters are two strings of comma separated values representing column names to update and values to insert respectively. Values can also be passed as separate arguments instead.
Like all other queries, the generated query needs to be executed with db_query_exec(), which will return the actual number of records affected.

/* Changes the weight and the weight and value of all the swords in the table to 1 */
query = db_query_update("items","weight,value",1,1);
db_cl_where(query,db_op_eq,"type","Sword"); var number_of_updated_items = db_query_exec(query);
db_query_calc(table or query,operation,field,ignore_limits)

Calc queries are used to perform calculations on the records, and are executed immediately (no need to call db_query_exec())
The function accepts one of the following operations (as string): count,sum,min,max,mean .
You have to specify the column to consuder for the calculation in the field parameter (except for count).
The first argument can either be a table name or one of the other queries. In the first case in which case this function returns a regular calc query which can be filtered using clauses, in the second case it returns the result directly using the parameters of the other query (useful for example if you need to find out how many records will be deleted / updated before actually executing the query).
ignore_limits tells the query to ignore db_cl_limit and db_cl_offset clauses on calculations based on existing queries (useful to get the total number of records when doing a pagination).

/* Get the first 10 items of type 'Potion' in the items database, but also get the total number of potions that will be returned by the query if no limits were applied. */
var query = ds_query_select("items");
db_cl_where(query,db_op_eq,"type","Potion");
db_cl_limit(query,10,0);

var total_potions = db_query_calc(query,"count","",true);
var first_ten_potions = db_query_exec(query);

Query clauses

Query clauses (db_cl_* functions) are attributes assigned to queries that affect the query execution and scope. With clauses you can for example filter, limit, sort, offset and join the affected records.
You can apply any number of clauses to a query or none at all, in no specific order. Clauses can be overwritten by applying the same caluse more than once to overwrite it.

db_cl_only(query,ids)

This clause applies the query only to records having the specified ids.
The ids can be passed either as a ds_list of integers, or as a comma separated string.
This clause is extremely helpful when deleting or updating a specific set of records. Note that the same result can be obtained by using the where clause (see below) associated with the list of ids, but this clause accesses the records directly from the index, and is therefore a lot faster.

Passing -1 instead of a list of ids resets the clause.

/* Set the value and weight of items with id 3, 4 and 8 to 1 */
var query = db_query_update("items","value,weight",1,1);
db_cl_only(query,"3,4,8");
db_query_exec(query);
db_cl_where(query,script,fields,args)

This clause filters the records based on the specified conditions.
script is the name of the script used for comparing the values (e.g: db_op_eq tests for equality. See db_op_* scripts).
fields is a string of comma separated column names involved in the comparison.
args is a string of comma separated values to be tested against the fields.

The following comparison scripts are provided with the extension, but if those don't fit your requirements for a specific query, you can always define some very specific filters by creating new scripts (see section condition scripts):

db_op_eq: checks for equality
db_op_neq: checks for difference
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 resets the clause and no filter is applied.

/* Deletes all items of type "Potion" */
var query = db_query_delete("items");
db_cl_where(query,db_op_eq,"type","Potion");
db_query_exec(query);
/* Gets all items with type different than "Sword" and "Shield" */
var query = db_query_select("items");
db_cl_where(query,db_op_neq,"type,type","Sword,Shield");
results = db_query_exec(query);
db_cl_limit(query,limit,offset)

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 allows you to ignore the first N results.
Useful for instance for pagination to select specific chunks (pages) of your result set.

Passing 0 as limit and offest arguments resets the clause.

/* Find the first 5 Shields */
var query = db_query_select("items");
db_cl_where(query,db_op_eq,"type","Shield");
db_cl_limit(query,5,0);
results = db_query_exec(query);
db_cl_sort(query,sort_criteria)

This clause applies an order to the result set. Sorting criteria and order direction have to be specified as a string in the format: "name:order" where 'name' is a column name, and 'order' can either be 'desc' (descending) or 'asc' (ascending)
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 heaviest items).

You can also specify up to 4 sort criteria in order of priority, in the form "type:asc,name:asc": in this case, all data is sorted by type first, and then by name.

If you pass the string "RAND" as sort_criteria, results are returned sorted randomly. An empty string as sort_field means no sorting applied.

/* Finds all the items sorted by weight */
var query = db_query_select("items");
db_cl_sort(query,"weight:asc");
results = db_query_exec(query);
/* Deletes the 2 heaviest items */
var query = db_query_delete("items");
db_cl_sort(query,"weight:desc");
db_cl_limit(query,2,0);
db_query_exec(query);
db_cl_join(query,join_table,foreign_key)

The join clause allows to attach the values of another table to the queried table, based on a foreign key.
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 collected so far.
If you want to get all the cards in your deck, along with all their data, you actually have to involve both tables. You could do that with two queries, but that would be tricky in complex cases, or you could just use a join clause and return the merged results, filtered, and sorted as you prefer.

join_table is the other table to involve, and foreign_key is the name of the column of the query table that holds the record ids of join_table.

IMPORTANT: when using the join clause, all reference to columns in the select query and other clauses need to include the table they belong to in the form "table_name.column_name", as in the example below. It's worth noting that in case of an update query, you can only change the main table records, so you should not use said notation in this 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.value:desc");
db_cl_join(query,"cards","card_id");
results = db_query_exec(query);

Query execution

db_query_exec(query,[keep])

Executes a query. The return value depends on the type of query passed as argument. db_query_calc queries don't need to be executed since they are executed automatically. The optional keep parameter (defaults to false) can be either true or false: when true the query will not be destroyed 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. A query if kept needs to be destroyed manually afterwars with db_query_free, or by executing it the last time with keep = false, to avoid memory leaks.

/* Executes a select query but keeps it for a further call. */
var query = db_query_select("items","id,name,value");
db_cl_where(query,db_op_lte,"value","100");
db_cl_sort(query,"value:asc");
results = db_query_exec(query,true); // keep the query, we will use it later
/* ... do some stuff ... */
db_cl_where(query,db_op_gte,"value","200"); //changing the where clauses
results2 = db_query_exec(query); //here we don't keep the query, no need to call db_query_free() in this case
db_query_free(query)

Deletes a query freeing the memory used (use only if you execute the query with keep == true) or never executing it.

Helper functions

csv_compose_line(list,separator)

Takes a ds_list of values and converts it into a standard CSV string, also escaping special characters.

csv_compose_line(list,",");
csv_parse_line(line,separator)

Returns a ds_list of values from a standard CSV line.

csv_parse_line("value1,value2,value3",",");
string_parse(string,separator,ignore_null)

Splits a string based on a separator character, and returns a ds_list of split strings.
ignore_null remove empty strings from the result. Similar to csv_parse_line, but csv rules for escaping characters don't apply.

string_parse("first|second|third","|",true);
ds_grid_multisort(ds_grid,col,order,[col],[order],...)

Works like ds_grid_sort, but allows sorting by multiple columns, in order of priority.
col is the column index to sort, order is either true or false, telling is sort in ascending or descending order respectively.

ds_grid_multisort(grid,0,0,2,0,3,1);
string_parse(string,separator,ignore_null)

Splits a string based on a separator character, and returns a ds_list of split strings.
ignore_null remove empty strings from the result. Similar to csv_parse_line, but csv rules for escaping characters don't apply.

string_parse("first|second|third","|",true);

Defining your own condition scripts

One of the most common things you may want to do with your database records 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 a script used for comparing the database values (db_op_* scripts).
There are a number of common comparison scripts that come with the extension, but sometimes you may need some more complex filter applied to your query, and in those cases you are free to implement your own comparison script.

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, every record in the database is tested against the condition script, if the script returns true the record matches the condition and is kept, otherwise discarded.

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 field.

We start by creating our db_op_str_len script, keeping in mind the following rules:
- Our script has to return true if the provided string has less the N characters, false otherwise.
- The script will get all the parameters we need from two ds_lists passed as arguments. The first list contains value from our records that we need to test (only a single string in this case), the second the arguments we passed to the where clause (in our case, the max number of characters).

var str,max_chars;
str = ds_list_find_value(argument0,0); //Get the string of the record to compare
max_chars = real(ds_list_find_value(argument1,0)); //Get the number of max chars we defined in our where clause, as number

return string_length(str) <= max_chars; //Return true if the number of characters in our string is less (or equal) than the specified maximum

We can now create queries using our script as where clause:

/* Get all the items having a name with less than, or equal to, 5 characters */
query = db_query_select("items");
db_cl_where(query,db_op_str_len,"name","5");
result = db_query_exec(query);

Performance

The following are some notes on performance and how to optimize you use of GMSDB.

The first thing to know about GMSDB is that, although you can save all the data to file, while in use all the database data lies in the program memory, like and array or a data structure. This means that you are discouraged to use it for extremely large databases. The total amount of data varies depending on the target platform, but in general You should try to avoid having more than 50'000 / 100'000 records in a single table.

The above also means that, unlike MySQL and similar, unless you explicitly save the database, or set autosave to true, all data is lost when closing the game.

Here's a list of tips and things to avoid to improve the overall performance:

  • Try to perform as less queries as possibile, by using db_record_* functions or only when actually needed. Queries in the step or draw event are BAD.
  • In the db_query_select query, it's actually faster to return all columns (by leaving the second argument empty) than listing manually a lot or all of them. This does not apply to db_query_select_records.
  • Joins and multiple sorts are slow, use with care.
  • Whenever possible, make use of db_cl_only. Working only on specific ids reduces greatly the computation that queries need to perform.
  • Limit and offset are great for paginating results, returning only a subset (page) of the total records at a time. With the help of calc queries you can also get how many records are there in total for the same query, without considering the limit.
  • CSV files are great to debug and insert some starting data into the database during the development of your game. Just remember to switch to the internal database format when publishing the game.

Upgrading from version 3.1 to 3.2 or greater

Version 3.2 introduces some relevant changes to the saving mechanism, and a few minor changes to some other aspect of the database management. Be sure to read the following notes before updating existing projects.

Saving, importing, exporting

When saving, you are no longer required to explicitly specify the location where the database is saved to, because since 3.2 that's handled automatically inside the sandbox. In fact, the database is no longer saved to a single file, and this improves saving times greatly since now only the data that has changed since the last save is written to file.

If you have an old database, you need to import it into a new one using db_import_legacy(). You could do something like this to test if the old database has been converted:

if(!db_exists("new_database_name")) {
db_import_legacy("path_to_old_database","new_database_name"); db_save(); }
else {
db_open("new_database_name"); }

Note that db_save no longer requires a filename.
You can still however export and import the database as a single file with db_import and db_export.

Tables

Since there's no longer a single database file, every table is now saved independently. This is all handled automatically by the database system so you should not have to worry about how the data is stored, but it's important to know that the table name is used as filename when saving. This means you can not have any special character or space in table names other than _ (unserscore).
If you have an old database file that does not comply to this, it is suggested to open in with a text editor and edit the table names directly.
Remember to change the table names accordingly in your scripts.

The format of exported and imported tables has also changed, as well as the function names, in order to avoid conflicts with the planned updates and reflect their db export / import counterparts. db_table_save and db_table_load are now called db_table_export and db_table_import.

Select query

In version 3.1, if you specified only the column "id" in the fields argument, the query execution would return a ds_list of ids instead of a ds_grid. This is confusing, so this functionality has been moved to db_query_select_ids and db_query_select_records.
db_query_select("items","id") will now return a ds_grid with a single column. All other uses of db_query_select are left unchanged.

Other

  • A new example has been added that shows how to manage records using ds_maps
  • New and updated documentation

Obsolete or renamed functions

  • db_load has been removed in favor of db_import and db_open
  • db_table_save is now db_table_export
  • db_table_load is now db_table_import
  • db_table_save_csv is now db_table_export_csv
  • db_table_load_csv is now db_table_import_csv

Changed functions

  • db_save no longer requires a file name
  • db_query_select no longer returns a ds_list of ids if only "id" is provided as field
  • db_table_create now also accepts columns as separate arguments. Also returns false if the table already exists.
  • db_record_fetch, db_record_fetch_all now accepts a third optional argument telling which specific columns to return

New functions

  • db_open is now used to open or create databases
  • db_autosave enables or disables the new autosave functionality
  • db_list_tables returns the list of all tables in the db
  • db_exists checks if a database exists with a given name
  • db_close closes the current database
  • db_import_legacy imports the old database format
  • db_query_select_ids retuns the query results as a ds_list of record ids
  • db_query_select_records returns the query results as a ds_list of records (as ds_maps)
  • db_record_destroy deletes a record starting from its ds_map representation, and deletes the ds_map
  • ds_grid_multisort sorts a ds_grid by multiple columns

Credits

Contact me on the GMC forums or by email at simoneguerra<at>ekalia.com