db_init();
GMSDB is a database system written entirely in GML that can be used in any kind of project
requiring a simple 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 GMSDB database itself lives in the program memory, and exists therefore only as long
as the program is running. It is possible however to save the database to file and load it
again at game start.
This means that the data retrieval is not affected by the overhead of file read/write
operations. Keep in mind however that it's still a simple GML implementation of a database,
and the speed of the operations, especially on large data sets, is not comparable to a real
database system.
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, import all the scripts. If you want to look at the provided examples
select "Import all".
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():
db_init();
We will create a table called "levels" that will hold (surprisingly) some
data about every level of our game. We specifically want to store name, difficulty (as a number from 0 to 2), if it has
been cleared, and the best score.
A table is automatically assigned a column called "id", that holds a unique auto incrementing numeric id for every record stored in that table.
db_table_create("levels","name|string,difficulty|real,completed|real,score|real");
db_query_insert("levels","Tutorial,0,0,220");
db_query_insert("levels","Earth,1,0,300");
db_query_insert("levels","Moon,0,0,990");
db_query_insert("levels","Mars,1,0,220");
db_query_insert("levels","Jupiter",2,0,310);
//You can pass values also as arguments instead of a single string
With some records in our table, we can then start working with the data. The simplest way to perform some operations is to use the db_record_* functions. These functions will provide a fast and easy way to get data back from the database, but are limited in functionality.
var level_id = db_record_first("levels","name","Tutorial");
//Get the id of the first level found with name "Tutorial"
var level_score = db_record_get("levels",level_id,"score");
//Get the score of the level with id level_id
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 "levels" table.
Get all the levels and all their data ordered by difficulty. Useful for a level selection screen
var query = db_query_select("levels","id,name,score,difficulty,completed");
//Create a select query
db_cl_sort(query,"difficulty","ASC");
//Add a sorting clause
levels = db_query_exec(query);
//Finally, execute the query and return the results
The "levels" 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. Something like:
| 1 | Tutorial | 220 | 0 | 0 |
| 2 | Moon | 990 | 0 | 0 |
| N | ... | ... | .. | ... |
Get all the easy levels ordered by score (best to worst):
var query = db_query_select("levels","id,name,score,completed");
db_cl_sort(query,"score","DESC");
db_cl_where(query,"difficulty","0");
levels = db_query_exec(query);
Perform two similar queries by reusing the same query twice. Here we get the completed level with the highest score and the one with the lowest score respectively.
var query = db_query_select("levels","id,name,difficulty,completed,score");
db_cl_where(query,db_op_eq,"completed","1");
//We only need completed levels
db_cl_sort(query,"score","DESC");
db_cl_limit(query,1,0);
//Limit our results to the single best level
best_level = db_query_exec(query,true);
//By passing "true" as second argument, we tell the system that the query should no be destroyed
after execution
db_cl_sort(query,"score","ASC");
//Now we change (overwrite) the sorting of the results to get the worst score, leaving other
query parameters as they are
worst_level = db_query_exec(query);
//Execute the query again with our new order applied. This time we let the the query be destroyed
after usage.
Reset all level scores to 0
var query = db_query_update("level","score",0);
db_query_exec(query);
Mark a single level as completed, and update the score
var new_score = 9999;
var level_id = 2;
var query = db_query_update("levels","completed,score",1,new_score);
db_cl_only(query,level_id);
//Since we need to update a specific level and we know its id, we use the "only" clause
to limit the scope of our update query.
db_query_exec(query);
Save the whole database to file and load it back:
db_save(working_directory+"/db.gdb");
[...]do something[...]
db_load(working_directory+"/db.gdb");
Export the database table as CSV in order to see its contents in programs like excel.
db_table_save_csv("levels",working_directory+"/level.csv",",");
When you don't need the database system anymore, you can unload it and free the memory by calling db_free(). This deletes all the database tables and values, freeing the memory.
db_free();
Functions to work with the database a whole.
Initialize the database. Must be called only once, and before any other function.
Unloads the database system, deleting all tables and values and freeing its memory.
Save the database to file. Note that it's not saved in human readble format.
Example
db_save(working_directory+"/db.gdb");
Loads the dabase from a file saved with db_save(). This will replace allo existing contents in your database (if any)
Example
db_load(working_directory+"/db.gdb");
A table can be imagined 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 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.
The following functions exist to work with tables:
Creates a table in the database with the specified columns. If a table already exists with
the same name, it is deleted and replaced.
The special column "id" is auto generated and should NOT be passed in the list of columns.
Columns have to be passed as a string of comma separated name|type values, like in
"firstname|string,lastname|string,age|real,".
A column type can either be "real" or "string".
/* Creates a table named fruits, holding three fields:
name, color and weight */
db_table_create("fruits","name|string,color|string,weight|real");
Deletes all the records in table, resetting the ids but keeping the table structure.
Example
db_table_clear("fruits");
Returns a ds_list of ordered column names for the specified table. The list has to be destroyed after usage.
Example
var names_list = db_table_column_names("fruits");
Deletes a table and all its data, freeing the memory.
Example
db_table_delete("fruits");
Returns true if a table with the provided name exists in the database, false otherwise.
Example
if(!db_table_exists("fruits")) {
//do something
}
Returns the number of records stored in the table.
Example
show_message("There are " + string(db_table_size("inventory")) + " items in your inventory");
Creates a table from a file saved with db_table_save();
Example
db_table_load(working_directory+"/table.gdb");
Saves a table and all its contents to a file. This file can be later used with the function db_table_load() to load the table, with the same name, into the database. The file is in non human readable format.
Example
db_table_save("fruits",working_directory+"/table.gdb");
Saves a table content to a CSV file. Values are separated based on the sep argument. Useful for debugging or manipulating the data in excel or similar programs.
Example
db_table_save_csv("fruits",working_directory+"/table.csv",",");
Adds the contents of a CSV file to an existing table. Existing table contents are not removed. Note that the CSV file has to be formatted as in the result of db_table_save(): the first row (labels) and first column (id) will be skipped.
Example
db_table_load_csv("fruits",working_directory+"/file.csv",",");
While queries allow for great flexibility and the definition of complex filters and result sets,
sometimes you just need to perform some really simple operations on specific a record.
Record operations allow just that, and although limited, they perform faster than queries.
Deletes the record with id from the table. Returns false if the record doesn't exist, true otherwise.
Example
if(db_record_delete("fruits",3)){
show_message("Fruit 3 deleted successfully");
}
Returns true if a record with the provided id exists into the table, false otherwise.
Example
if(db_record_exists("fruits",3)){
show_message("Fruit 3 exists in the table");
}
Returns the id of the first record having "value" at the specified column. Returns 0 if no record is found.
Example
var apple_id = db_record_first("fruits","name","Apple");
if(apple_id > 0){
show_message("Apple id: " + string(apple_id));
}
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 if you need the values of more records and
of many fields, a select query should be used instead.
var fruit_name = db_record_first("fruits",2,"name");
show_message(fruit_name);
Returns the id of a random record in the table. If no record with id is found, 0 is returned.
Example
var random_fruit_id = db_record_first("fruits");
Updates the value in the specified column of record id. Returns true if successful, false otherwise.
Example
if(db_record_update("fruits",5,"name","Banana")){
show_message("Fruit 5 updated successfully");
}
Queries are the primary mechanism for retrieving and manipulating information from the database. Queries provide a lot of flexibility when working with database data, allowing you to return and manipulate record data by applying filters like sorting, conditions, limits, etc...
There are 5 types of query operations: insert, select, update, delete and calculate
With the exception of insert and some calculate operations, all queries need to be created first and executed
with db_query_exec();
Inserts a new record in the database table with the provided values.
The values have 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 specification.
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.
This function executes a query directly and has no return value.
db_query_insert("fruits","Banana","Yellow",30); //values as arguments
db_query_insert("fruits","Apple,Red,20"); //values as string
var values = ds_list_create(); values[| 0] = "Orange"; values[| 1] = "Orange"; values[| 2] = 27;
db_query_insert("fruits",csv_compose_line(values,",")); //values from ds_list
ds_list_destroy(values);
Select queries are used to return a specific set of fields from the table records.
Fields need to be passed as a string of comma separated column names
(you can pass and empty string to return all the fields)
The function returns a query structure that needs to be passed to db_query_exec() to be executed and
retrieve the results.
The return value of db_query_exec() for a select query is either -1 if no record is found, or a ds_grid of values.
The result set can be filtered and sorted by using query clauses
Example
/* Selects the name and weight of
all the records in the fruits database. */
var query = db_query_select("fruits","name,weight");
results = db_query_exec(query);
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 needs to be executed with db_query_exec(), which will return the
actual number of records deleted from the query execution.
/* Deletes the fruits with id == 4 and id == 6 from the table */
var query = db_query_delete("fruits");
db_cl_only(query,"4,6");
number_of_deleted_fruits = db_query_exec(query);
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.
The generated query needs to be executed with db_query_exec(), which will return the
actual number of records affected.
/* Changes the color and the weight of all
the apples in the table */
query = db_query_update("fruits","color,weight","Green,60");
db_cl_where(query,db_op_eq,"name","Apple");
//applies a where clause telling to update only apples
number_of_updated_fruits = db_query_exec(query);
Calc queries are used to perform calculations on the records.
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).
/*
Example: Query for the first 10 items of type 'weapon' in an item database, but also get the total number
of weapons that will be returned by the query if no limits were applied.
*/
var query = ds_query_select("items","name,category,value");
db_cl_where(query,db_op_eq,"category","weapon");
db_cl_limit(query,10,0);
var total_weapons = db_query_calc(query,"count","",true);
// Before executing the select query, get how many weapons are there in total in the database
var first_ten_weapons = db_query_exec(query);// Execute the select query and get the results
show_message("Displaying 10 out of " + string(total_weapons) + " weapons");
Query clauses are used alter (filter,sort,etc..) the results of the select, update, delete and calc queries
described above. You can apply any number of clauses to a query or none at all, in no specific order.
Clauses can be overwritten, you apply the same caluse more than once to overwrite it.
This clause selects only the records of the table 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 specific 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 as ids resets the clause and applied no id filter.
Example
/* Updates the color of fruits with id 3, 4 and 8 */
var query = db_query_update("fruits","color","Red");
db_cl_only(query,"3,4,8");
db_query_exec(query);
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 new ones (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_between: checks inclusion of values between two specified numbers
Passing -1 as script resets the clause and no filter is applied.
Example
/* Deletes all red fruits */
var query = db_query_delete("fruits");
db_cl_where(query,db_op_eq,"color","Red");
db_query_exec(query);
Example
/* Gets all fruits with name != Apple and color != Green */
var query = db_query_select("fruits","id,name,color,weight");
db_cl_where(query,db_op_neq,"color,name","Green,Apple");
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 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.
Example
/* Find the first 5 red fruits */
var query = db_query_select("fruits","id,color,weight");
db_cl_where(query,db_op_eq,"color","Red");
db_cl_limit(query,5,0);
results = db_query_exec(query);
This clause applies an order to the result set. Results are sorted by the field specified
as sort_field. sort_order can either be the string "ASC" (sort in ascending order) or
"DESC" (sort in descending order).
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 lightest fruits).
If you pass the string "RAND" as sort_field, results are returned sorted randomly. An empty string as sort_field means no sorting applied.
Example
/* Finds all the fruits sorted by weight */
var query = db_query_select("fruits","id,name,color,weight");
db_cl_sort(query,"weight","ASC");
results = db_query_exec(query);
Example
/* Deletes the 2 heaviest fruits */
var query = db_query_delete("fruits");
db_cl_sort(query,"weight","DESC");
db_cl_limit(query,2,0);
db_query_exec(query);
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 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 query and other clauses need to include the table they belong to in the form "table_name.column_name". See the example below.
Example
/* 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);
Executes a query. The return value depends on the type of query passed as argument.
Insert queries don't need to be executed with this function as they are executed automatically,
as well as some calc queries (see queries specification).
The optional keep parameter can be either true or false, when true the query will not be destroyed
after the function call. This means that you can actually save a query and execute it as many
times as you want while changing the clauses. A query is actually data structure (ds_map), therefore if you decide to
keep a query, you then need to destroy it with the function db_query_free or execute it the last time with keep = false.
/* Executes a select query but keeps it for a further call. */
var query = db_query_select("fruits","id,color,weight");
db_cl_where(query,db_op_eq,"name","Apple");
db_cl_sort(query,"weight","ASC");
results = db_query_exec(query,true);
// keep the query, we will use it later
/* ... do some stuff ... */
db_cl_where(query,db_op_eq,"name","Banana");
//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
Deletes a query freeing the memory used (use only if you execute the query with keep == true) or never executing it.
Takes a ds_list of values and converts it into a standard CSV string, also escaping special characters.
Example
csv_compose_line(list,",");
Returns a ds_list of values from a standard CSV line.
Example
csv_parse_line("value1,value2,value3",",");
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);
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 fruits having a name with less than, or equal to, 5 characters */
query = db_query_select("fruits","id,name,color,weight");
db_cl_where(query,db_op_str_len,"name","5");
result = db_query_exec(query);
Contact me on the GMC forums or by email at simoneguerra<at>ekalia.com