1. Introduction

GMSDB is a database system written entirely in GML that can be used in any kind of project requiring a small and simple solution to store organized data.
GMSDB provides a set of scripts to create, read, update and delete data in a simple way by applying filters like custom conditions, sorting options, limits, offsets, etc.

The system offers functionalities to save the data to file, but the database itself is stored using the internal GM data structures, therefore it lives in the system memory and not on the HD as long as the game is running. This means that, when the game is closed, the data stored in the database completely disappears unless you explicitly save it to file. This also means however that the data retrieval is not affected by the file management overhead and allows for quite fast reading / writing operations.

2. Installation

First of all, download the last version of the extension, in install the right GEX for the version of GM you are using (either GM:Studio or GM8.1).
http://www.ekalia.com/gm/gmsdb/gmsdb_gex.zip

Next you need to import and include the GEX into your project, import the extra scripts provided with the package, and you are set to go!

If you want to get an idea of how this extension works, skip to section Example & getting started.

3. Reference

3.1. Database

Before calling any of the extension functions, you have to initialize the DB system. This creates the required data structures where the database is stored, and must be called only once.

The following functions allow to initialize and free the database system:

db_init()

Initialize the database. Must be called only once, and before any other function.

db_free()

Unloads the database system, deleting all tables and values and freeing its memory.

3.2. Tables

A table can be seen as a 2D array (or 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. This means that you don't need to store table ids or anything like that when creating tables, 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.

When creating a table, you have to provide a name and the fields it will hold. For every field you also need to specify a type of data stored (either string or real).

When creating a table, it creates a special field called "id". The id is just a unique, auto incrementing identifier number assigned to each record of the table, and is automatically set when inserting a record into the table.

The following functions exist to work with tables:

db_table_create(name,columns)

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 consists of a name and a type, where the type can either be the "string" or "real", telling the type of contents to be held in that column.


					
						/* Creates a table named fruits, holding three fields: 
						name, color and weight */
					

					db_table_create("fruits","name|string,color|string,weight|real");

db_table_clear(table_name)

Deletes all the records in table, resetting the index but keeping the table structure.

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.

db_table_delete(table_name)

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

db_table_exists(table_name)

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

db_table_read(filename)

Loads a table and all its contents from a file created with the function db_table_write().

db_table_size(table_name)

Returns the number of records stored in the table.

db_table_to_csv(table_name,path,sep)

Saves a table content to a CSV file. Values are separated based on the sep argument. Useful for debugging or viewing data in excel or similar programs.

db_table_write(table_name,path)

Saves a table and all its contents to a file. This file can be later used with the function db_table_read() to load the table into the database.

3.3. Queries

Queries are operations that apply to table records and are the functions you are going to use more extensively, but they are not always easy to understand. Queries provide however 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 four main types of query operations: insert, select, update and delete.
With the exception of insert operations, all queries generally need to be created first, then optionally filtered with some clauses, and then executed with db_query_exec().

3.3.1. Query operations

db_query_insert(table,values)

Inserts a new record in the database table with the provided values (should be passed in column order).
Values 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 sanitize the values by passing them to the script csv_compose_line().
This function executes a query directly and has no return value.


					/* Inserts two fruits into the table */

					db_query_insert("fruits","Banana","Yellow",30); //values as arguments

					db_query_insert("fruits","Apple,Red,20"); //values as fields

db_query_insert_csv(table,csv_file,separator)

Populates a table in the database with the contents of a CSV file.

db_query_select(table,fields)

Returns a select query. Select queries are used to retrieve data from records, you can specify which specific fields you want by passing them as comma separated values.
The query generated from this function call needs to be passed to db_query_exec() in order to get the results, that will be returned as a ds_grid of the specified field values.
If an empty string is passed for the fields, the query assumes you want ALL the fields returned.

clauses (see clauses section below) can be applied to the query to filter and sort the results.


					
						/* Selects the name and weight of 
						all the records in the fruits database. */
					

					query = db_query_select("fruits","name,weight");

					results = db_query_exec(query);

db_query_delete(table)

Returns a delete 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 the whole 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 fruit with id == 4 and id == 6 from the table */
					

					query = db_query_delete("fruits");

					db_cl_only(query,"4,6");

					number_of_deleted_fruits = db_query_exec(query);

db_query_update(table,fields,value)

Returns an update query. Update queries are used to update specific fields of records. This kind of query is usually associated with some clauses (see clauses section below) in order to restrict the range of the updated records. The fields parameter is a string of comma separated fields to update, can values either be passed as a string of comma separated values to insert into the specified fields, or as arguments to the function.
The generated query needs to be executed with db_query_exec(), which will return the actual number of records updated from the query execution.


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

db_query_join(table,fields,grid,column)

Returns a join query. Join queries are used to generate a result set (as always a ds_grid) that's composite of two or more queries, and allows for complex queries that take multiple tables into consideration.

The join query works exactly like a normal select query, you have to specify a single database column, the fields you want to retrieve. Moreover, you have to pass also a ds_grid containing the results of a previous select (or join) query; this grid has to include a column with the ids of the records you need to join.
This is probably best explained by a meaningful example:


					
						/* 
							In the following example we want to create an inventory system, we setup therefore two tables:

							items - keeps a list of every item in the game

							inventory - the player inventory, a reference to the items database that also stores the number of items
						*/
					

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

					db_table_create("inventory","item_id|real,quantity|real");

					

					db_query_insert("items","Claymore,Sword,150");

					db_query_insert("items","Gladius,Sword,60");

					db_query_insert("items","Katana,Sword,80");

					db_query_insert("items","Panacea,Potion,10");

					db_query_insert("items","Venom,Potion,10");

					db_query_insert("items","Tower shield,Shield,200");

					db_query_insert("items","Fur shield","Shield",150);

					db_query_insert("items","Harpoon,Spear,190");

					

					//this is a bit of a hack, we are actually inserting had coded item_ids for simplicity purposes, don't do that in your game ;)

					db_query_insert("inventory","2,1");//insert 1 Gladius into inventory

					db_query_insert("inventory","4,16");//insert 16 Panacea into inventory

					db_query_insert("inventory","7,2");//insert 2 Fur shields into inventory


					
						/* 
							Now we want to display the contents of the inventory, but showing only the item_id and the quantity is
							not really useful for the player. What we need to do is attach the item information to every record
							in the inventory. This is done as follows:
						*/
					


					//First we execute a regular select query on the inventory, getting all its contents

					var query = db_query_select("inventory","item_id,quantity");

					inventory_contents = db_query_exec(query); 

					

					//Next, using the results if the previous query, we perform a join query on the items table, telling that the ids of the items are in the first column of inventory_contents (column number 0)

					var join_query = db_query_select("items","name,type,weight",inventory_contents,0);

					db_cl_sort(join_query,"type","ASC");//we also want the results sorted by item type

					db_query_exec(join_query);//note that we don't need to save the value returned by db_query_exec
						since this query modifies inventory_contents directly

					

					
						/* 
							Now inventory_contents is a ds_grid containing a row for every item in the inventory, complete with item_id, quantity,
							name, type and weight, ordered by type:

							4,16,Panacea,Potion,10

							7,2,Fur shield,Shield,150

							2,1,Gladius,Sword,60
						*/

Please keep in mind that this is a simple join functionality, don't expect the complexityof SQL joins.

Some more important info about how join queries work:
- You SHOULD NOT use db_cl_only clause on join queries. This clause is already applied automatically by looking at the results passed as arguments to the function.
- You can apply conditions as usual via clauses to join queries. This applies conditions to the final result set.
- You can perform multiple joins by passing the results of a join query to another join query.

3.3.2. Query clauses

Query clauses are used to apply filters and sorting to the select, update and delete queries described above. Depending on the result you need, you can either apply any number of clauses to a query or none at all, in no specific order.

db_cl_only(query,ids)

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, and is therefore many times faster.

Passing an empty string or -1 as ids applies no filter to results.


					/* Updates the color of fruits records with id 3, 4 and 8 */

					query = db_query_update("fruits","color","Red");

					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 conditions provided as arguments.
script is the name of the script used for comparing the values (db_op_* scripts), fields is a string of comma separated fields to be used for comparison, args 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 "defining your own condition scripts" below):

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 means no filter applied.


					/* Deletes all red fruits */

					query = db_query_delete("fruits");

					db_cl_where(query,db_op_eq,"color","Red");

					db_query_exec(query);


					/* Finds all fruits with name != Apple and color != Green */

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

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 skip a number of results. Useful for instance for pagination, where, aling with limit, you can select specific chunks of your result set.
0 as limit means no limit to results. 0 as offset means no offset applied.


					/* Find the first fruit having name "Apple" */

					query = db_query_select("fruits","id,color,weight");

					db_cl_where(query,db_op_eq,"name","Apple");

					db_cl_limit(query,1,0);

					results = db_query_exec(query);

db_cl_sort(query,sort_field,sort_order)

This clause applies a sort 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).
And empty string as sort fields means no sorting applied.


					/* Finds all the fruits sorted by weight */

					query = db_query_select("fruits","id,name,color,weight");

					db_cl_sort(query,"weight","ASC");

					results = db_query_exec(query);


					/* Deletes the three heaviest fruits */

					query = db_query_delete("fruits");

					db_cl_sort(query,"weight","DESC");

					db_cl_limit(query,3,0);

					db_query_exec(query);

3.3.3. Query execution

db_query_exec(query,(keep))

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.
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. Queries are global, therefore if you decide to keep a query, you then need to destroy it with the function db_query_free presented below.


					
						/* Executes a select query but keeps it for a further call. */
					

					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"); 
					
						//note that you can replace clauses on an existing query
					

					results2 = db_query_exec(query); 
					
						//since 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.
This has to be called only if you kept a query after its execution, or for some reason you never executed it.

The following are one line functions that execute (select, delete, update) queries directly, without returning a query. They are basically helper functions that construct (and execute) a query for you applying clauses based on the arguments passed.
Since all arguments are mandatory, and there are quite a lot of arguments, it is usually better to construct queries like described in the section above, but it's a matter of personal preference.

db_query_select_ext(table,fields,only,where_script,where_columns,where_args, sort_field,sort_order,limit,offset)

Selects records and returns a ds_grid with the values for the specified fields.
fields are specified as in db_query_select().
For a description of the other arguments, please refer to the query clauses described in the section above.


					
						/* Selects all Red fruits sorted by weight */
					

					db_query_select_ext("fruits","id,name,weight",-1,db_op_eq,"color","Red","weight","ASC",0,0);

db_query_delete_ext(table,only,where_script,where_columns,where_args, sort_field,sort_order,limit,offset)

Deletes some records from the database.
For a description of the arguments, please refer to the query clauses described in the section above.


					
						/* Delete fruits 1,3 and 4 from the table */
					

					db_query_delete_ext("fruits","1,3,4",-1,"","","","",0,0);

db_query_update_ext(table,fields,value,only,where_script,where_columns,where_args, sort_field,sort_order,limit,offset)

Updates a set of records in the database. fields and values parameters are specified as in db_query_update().
For a description of the other arguments, please refer to the query clauses described above.


					
						/* Update fruits 2,3 and 7, but only if their weight is grater than 50 */
					

					db_query_delete_ext("fruits","2,3,7",db_op_gt,"weight","50","","",0,0);

3.4. Records

Since queries, especially the more complex ones on large data sets, usually require some time to be executed, it can be useful to have a couple of functions for fast and simple operations directly on records.

db_record_exists(table,id)

Returns true if a record with the provided id exists into the database table.

db_record_first(table_name,column,value)

Returns the id of the first record having value at the specified column. -1 is returned if no record is found.

db_record_get(table_name,id,column)

Returns the value in the specified column of record id. -1 is returned if no record with the provided id is found. 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.

3.5. Helper functions

csv_compose_line(list,separator)

Takes a ds_list of values and converts it into a standard CSV string.

csv_parse_line(line,separator)

Returns a ds_list of values from a standard CSV line.

3.6. Defining your own condition scripts

One of the most common things you may want to do with your database records is apply some conditional filters. 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.
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 value we received from our record
				

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

4. Examples & getting started

The first thing to do when using GMSDB is to initialize the system somewhere in your game, keeping in mind that you have to do this ONLY ONCE as long as the game is running (you can do it again only if you released the system before by calling db_free()).


					db_init();

Then we can start creating tables, we will create one called "levels" that will hold (surprisingly) some data about every level of our game. We specifically want to store the name of the level, its difficulty (from 0 to 2, 0 being the easiest), whether it has been completed or not by the player (1 or 0), and the score obtained the player.


					db_table_create("levels","name|string,difficulty|real,completed|real,score|real");

The next step will be to insert the levels into our new table, inserted in no specific order.


					db_query_insert("levels","Hurt me plenty,1,0,0");

					db_query_insert("levels","Tutorial,0,0,0");

					db_query_insert("levels","Nightmare!,2,0,0");

					db_query_insert("levels","I'm too young to die,0,0,0");

					db_query_insert("levels","Hey, not too rough,1,0,0");

					db_query_insert("levels","Ultra-Violence,2,0,0");

With some data into our table, we can then start performing some queries. Queries allow for a lot of flexibility when retrieving and manipulating data, here are some pointless examples to get you started, but for more in-depth information refer to the queries section.

For our level selection, we need to display all our levels ordered by difficulty (from easiest to hardest).
Since we don't show the score of the player in our level selection, we can leave that field out in our select query.


					query = db_query_select("levels","id,name,difficulty,completed");
							
					db_cl_sort(query,"difficulty","ASC");

					levels = db_query_exec(query);

In this example, we want to show two things to the player: the level with the highest score, and the level with the lowest.
To do this we execute two queries, but since the queries are similar, we decide to reuse the same query two times by changing just the sorting order applied.


					query = db_query_select("levels","id,name,difficulty,completed,score");
					
						//Create the select query
					

					db_cl_where(query,db_op_eq,"completed","1");
					
						//Apply a filter to select only completed levels
					

					db_cl_sort(query,"score","DESC");
					
						//Sort by highest score
					

					db_cl_limit(query,1,0);
					
						//Limit our results to the single best level
					

					best_level = db_query_exec(query,true); 
					
						//Execute the query, but telling to keep it since we want to execute 
						it again soon (with a slight change)
					

					

					db_cl_sort(query,"score","ASC");
					
						//Now we want the lowest score, so we just change the sorting order of the query above.
					

					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.

We added to our game a button that resets all the scores of our levels, we need a query that resets all scores to 0.


					query = db_query_update("level","score",0);

					db_query_exec(query);

In this example we suppose the player has just finished a level, we need therefore to mark it as completed and insert the player score.


					query = db_query_update("levels","completed,score",1,score);

					db_cl_only(query,level_id);
					
						//Since we need to update a specific level, where its id is known, we use the "only" clause
						to limit the scope of our query.
					

					db_query_exec(query);

Since a lot of operations alter the state of our database during the execution of the game, and we want to keep track of player scores and progress when the player closes and reopens it, we decide it's better to save the levels table to file in the game_end event.
Moreover, we are still debugging out game, and we decide to save it also as a CSV file in order to check with excel that everything worked as expected.


					db_table_write("levels",working_directory+"/levels.gdb");

					db_table_to_csv("levels",working_directory+"/levels.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, but we just saved it to file in order to reload it later.


					db_free();

5. Credits

Developed by Homunculus, no need to credit if used (both free and commercial), but I'd be happy if you send me a note when used in a released project.

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