34 replies to this topic

[Catan]

GMSDB
GMSDB is a set of scripts for GMS and GM8.1 (for GM8.0 users, see this) I realized for my game Animus to manage the cards database. I decided to clean the scripts and generalize them so that they can be used as a generic database system written entirely in GML, without the need to use DLLs or other external resources.

The scripts are also available as a GEX extension.

Features

• Comprehensive database system with no external dll or extensions required
• Allows creation of an unlimited number of tables and table columns
• Data retrieval scripts that allow complex sorting and filtering of records
• Reads and saves database tables to the file system with its own format
• Can read an save records to CSV files, to be edited in Excel or text editors
• Tables and table columns are global and identified by name, no need to store and pass around table_id variables or similar.
• Works on GM8.1 and Studio

Download V1.2.0
The zip file contains examples and scripts for GM:Studio and GM8.1.

Documentation
An online version of the manual can be found HERE

Example snippet

Spoiler

db_init(); //initialize the database. Should be called before any other db call and only once.
db_table_create("weapons","name|string,weight|real,type|string"); //creates a table called "weapons" with 3 attributes (columns): name,weight,type
db_record_insert("weapons","excalibur",30,"sword"); //inserts a weapons of type sword and weight 30 named excalibur
db_record_insert("weapons","lucky sword",20,"sword"); //another sword
db_record_insert("weapons","golden axe",54,"axe"); //an axe

first_query = db_record_find_ids("weapons",-1,"","weight DESC",-1,0); //returns a ds_list of weapon ids ordered by weight

second_query = db_record_find_values("weapons","id,name",db_op_equal,"type|sword","",-1,0); //returns a ds_grid containing the id and the name of every sword in the database.

third_query = db_record_find_values("weapons","id,name,type,weight",db_op_gt,"weight|25","",-1,0); //returns a ds_grid containing id,name,type and weight of every weapon in the database with weight greater than 25.<br>

excalibur_id = db_record_find_first("weapons","name","excalibur"); //returns the id of the first weapon with name excalibur.
excalibur_weight = db_record_get("weapons",excalibur_id,"weight"); //returns the weight of the sword found in the query above.

Version history
v1.2.0 - 14 March 2013
 

• Better script descriptions Scripts include now an extensive and consistent description
• New documentation The zip file includes now a documentation file.
• New: db_record_find_values Performs a query in the database, but unlike the old db_record_find, this returns a ds_grid of values directly. It is also possible to specify which columns you want the script to return.
• New: db_record_select You can pass a ds_list of record ids to this function to get a ds_grid of column values
• New: db_record_insert_csv Populates a table with values from a CSV file
• Obsolete: db_record_row, db_row_value, db_table_add_column, db_table_grid, db_table_grid, db_table_index These functions are not present anymore due to streamlining of the system, and introduction of better alternatives.
• Changed: db_record_find The function is now called db_record_find_ids
• Changed: db_table_create The function now allows the creation of an unlimited amout of columns, by passing column names in a comma separated string instead of arguments. Also, since columns have a type now, also the GM8 version requires to specify a type for each column.
• Changed: db_record_insert The script now allows inserting values as a comma separated string. Passing values as arguments still works.
• Changed: db_record_update The script no longer updates a single column, you can now specify a list of columns to update, and N values to insert into them.

Older versions:

Spoiler

v1.1.1 - 9 March 2013

• Fixed problem when setting limit to 1
  Setting limit to 1 in the previous version with an offset caused wrong result to be fetched
• Removed return of id directly on limit 1
  Since there's already a function to return a single id, calls to db_record_find return always a ds_list, even when limit set to 1
• Improved sorting performance

v1.1.0 - 8 Mar 2013

• Added studio compatibility.
  GMSDB for Studio has some small differences with respect to the GM8 version, highlighted in the Manual. This means you CAN'T use GM8 scripts and/or databases for Studio and vice versa. In studio there are a couple more utility functions used internally, while the DB functions provided are the same, the only differences are in the db_table_create and db_table_add_column scripts, that now require also a type (real or string) associated with the column name.
• Added function db_table_to_csv
  This function saves a table contents to a CSV file that can be open with excel or text editors. Useful for debugging.

Edited by Catan, 18 April 2013 - 07:37 AM.

[peter henry]

Fine. Useful!
Thanks.

[Catan]

New version uploaded, db_record_find is much faster now with limit > 0 and no order is specified.
I also added the function db_record_find_first(table,column,value), which returns the id of the first record found in the database having value at the specified column. This function has the same results of a regular db_record_find with limit 1 and no order, but it's much faster on large data sets

Edited by Catan, 14 June 2012 - 12:51 PM.

[Kuhn]

i've been looking for this thanks so much :D

[Johnjwj]

Very nice system, Catan! I've been looking for something similar for a project of mine. Gave you some reputation

[famous]

Oh, i see this is similar to my Dynamic Structure List (Data-Structure) Solution. ^^ Havn't seen this before.

[Catan]

I'm happy to see someone found this useful. Right now I'm creating some functions to import/export the db to csv files, this makes it possible to see, create and edit a database directly in excel, which can be useful also for debugging purposes.

I've also been told that this doesn't work on GM Studio. I expected it to work since as far as i know the ds_ functions have not been changed (except internally), but sadly I don't have GM Studio therefore I can't really see what's going on.

Edited by Catan, 12 July 2012 - 10:53 AM.

[Hyomoto]

I'm happy to see someone found this useful. Right now I'm creating some functions to import/export the db to csv files, this makes it possible to see, create and edit a database directly in excel, which can be useful also for debugging purposes.

I've also been told that this doesn't work on GM Studio. I expected it to work since as far as i know the ds_ functions have not been changed (except internally), but sadly I don't have GM Studio therefore I can't really see what's going on.

I'm looking for a good database system to handle some things and I'm using Gamemaker Studio. I was able to import your example and run it without errors, but there is an anomaly. It shows seven rows, but the first one is Orange, and the next six are Banana. If you delete that index, they all become Pumpkin. It doesn't appear to be a problem with the data structures though, since the data still seems to be there and may just be a glitch in the display routines.

Edited by Hyomoto, 15 July 2012 - 11:14 AM.

[fastjack]

Since one can only pass a limited number of arguments to a script in gamemaker, I presume if I wanted to make a table with say 20 columns I'd be unable to do so? How would you recommend I store such data? Would there be a relatively easy way to split the 20 or so properties of an item amongst 2 tables?

[Catan]

Since one can only pass a limited number of arguments to a script in gamemaker, I presume if I wanted to make a table with say 20 columns I'd be unable to do so? How would you recommend I store such data? Would there be a relatively easy way to split the 20 or so properties of an item amongst 2 tables?

There's a function to add columns to a database table after it is created, meaning you can have more than 14 columns. The problem is that you will not be able to insert records in the table with a single call. Probably the only way to do this is to modify the table creation and record insertion scripts to accept a string of values divided by a separator, and parse this string.

[bryneauinfo]

in studio it appears quite a few of the functions are missing.

[Catan]

in studio it appears quite a few of the functions are missing.

I recently bought studio and I plan to make these scripts work well with it. Moreover i almost completed some functions to export and import data from csv files (useful to export and import data from excel tables).

As for the bugs on studio:
no functions are missing but some internal changes made the scripts unusable on Studio.
The main issue is the changes made to the ds_map data structure, as in GM8.1 my GMSDB assumes, for some functionalities, that the contents of the ds_map are stored sorted by key and that ds_map_find_first and ds_map_find_last return respectively the smallest key and the largest key. This is not the case anymore with GM:Studio and causes problems when sorting the results and inserting new records.
There's some work to do but it's not a big deal.

The other problem that causes this not to work is a bug with the function ds_grid_set_grid_region performed on the same ds_grid (I checked and there's already a bug report on the tracker for this), but that's a major problem since it makes impossible to delete records in a clean and fast way from the tables. I'll wait until this is fixed to release the studio version.

[SamsungSchermStuk]

Thanks for the information, realy love it.
Subscribed to this tread! (H)

[darkwalker247]

I love that it has powerful sorting and grouping abilities. I will definitely be using this.

[Primoz128]

Look really useful, 6/10 for code formating.

[Catan]

[...]6/10 for code formating.

I don't really get what this means, can you explain please?

[Catan]

UPDATE: GMSDB is now GM:Studio compatible! Due to the differences between Studio and GM8, affecting heavily the sorting implementation, there are two different set of scripts, one for Studio and one for GM8. The changes are mostly internal, there are almost no changes in the function calls. The only difference is that in Studio columns are typed, meaning that you have to specify if a column holds real values or string values. For this reason you also can't share databases between GM8 and Studio.

I also added a functionality to export the tables to a CSV file, this helps debugging since this way you can check the database contents in excel or with a text editor.

Edited by Catan, 08 March 2013 - 02:05 PM.

[frifrafrank]

UPDATE: GMSDB is now GM:Studio compatible! Due to the differences between Studio and GM8, affecting heavily the sorting implementation, there are two different set of scripts, one for Studio and one for GM8. The changes are mostly internal, there are almost no changes in the function calls. The only difference is that in Studio columns are typed, meaning that you have to specify if a column holds real values or string values. For this reason you also can't share databases between GM8 and Studio.

I also added a functionality to export the tables to a CSV file, this helps debugging since this way you can check the database contents in excel or with a text editor.

Thank you for your work, I start to look at it and study it.
And thanks to share with the community

[Catan]

I just updated version 1.1.1, since 1.1.0 had a problem when using limit = 1 and an offset > 0. This version also features a slight performance improvement to results sorting, and more features in the example.

[Catan]

Sorry for the frequent updates, but I've been working on this a lot lately. I just released version 1.2 that introduces some major changes and new functionalities.

This version includes a better data retrieval system, now it is possible to return values directly into a ds_grid instead of just record ids. It is also possible to select exactly which columns to return.
Scripts are now better documented, and an external documentation is available. Moreover, there's no limit now to the number of columns a table can have, and every column has a type (real or string) associated to it in order to perform an automatic cast on insertion or update.
It is also possible to update more than one column at a time now in a single script call, by selecting which specific columns to update.
Better CSV integration allows now to import values from CSV files directly into tables.

Just a note for those wanting to upgrade: many scripts are obsolete now, and since database columns are typed, you can not use older databases into the new version. You can still import old db values to a new one using CSV files.

For details, see the change log in the open post.