Asset - Scripts GMSDB 3.3 - Database system

H

Homunculus

Guest


Updated May 23, 2018
Current version: 3.3


GMSDB is a database system written entirely in GML that can be used in any kind of project requiring a flexible solution to store and retrieve organized data.

With GMSDB you can easily perform basic CRUD (create,read,update,delete) operations on data while applying custom clauses like filters, sorting, limits, offsets, calculations and join operations.
GMSDB also takes care of loading and saving the database data from and to the file system, and includes functions to export and import the data in various formats (like CSV).

Features
  • Pure GML, doesn't require any external DLLs etc.
  • Flexible and customizable query system
  • Ability to sort, filter, limit, offset and join query results
  • Internal saving / loading mechanism to persist the database to file
  • Import and export table data from and to CSV file to be edited in excel or other editors
  • Memory management functionalities
  • Includes as an example a database viewer utility
  • Well commented code and extensive documentation

Resource link: GMSDB - Markeplace
Documentation:
Function reference, getting started and installation
Price: 4.99$

Version 3.3
May 23, 2018

This version is available only on GMS2. Please note that due to the scope of the changes, databases saved in the old versions are not compatible with this update.

Changes:
  • Table defaults: each column can be assigned a default value that will be used when no value is specified on record creation
  • Inserting records: you are no longer required to specify values for all columns (defaults will be used), and column order is no longer enforced
  • Script arguments: All scripts requiring arguments to be passed as string of comma separated values have now been converted to an array equivalent.
  • Memory management: it is now possible to load / unload specific tables from and to the file system, allowing better memory management
  • Sort by virtual attribute: db_cl_sort now accepts a custom script that allows sorting by a custom virtual attribute. It is for example possible to sort by string_length, or by the result of any GML expression.
  • CSV import: Importing data from CSV has been greatly improved. Headings are now used to match the table columns (instead of forcing a specific order like in previous versions). Extra columns in the CSV are skipped, while missing columns are accounted for by the new column defaults.
  • Database explorer utility: Included as an example, a database viewer that can be imported into your project to view and debug your database data
  • General minor fixes and improvements in stability and performance

Version 3.2
Uploaded March 10, 2017

Important: read the section about upgrading in the official docs before upgrading existing projects

Version 3.2 is focused on a new database saving mechanism, and GMS:2 compatibility. It is now much easier and faster to save your database, and a new option has been added to automatically save the database every time a change is made to its data and / or structure!

There have also been a number of changes and improvements in other aspect as well, here's a summary of the changes, but be sure to head to the provided docs for details:

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. Use db_query_select_ids instead
  • 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 accept 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
 
Last edited by a moderator:
B

bbkymm

Guest
I am interested in this asset. My question before buying: Works on any platform? (Eg Android).
 
H

Homunculus

Guest
I am interested in this asset. My question before buying: Works on any platform? (Eg Android).
I haven't tested it personally on ios nor android, but a few users working on those platforms confired it worked without problems for them. There's no reason in my opinion for it not to work, since it's all just regular GML.
 
Hey so lately I have this problem only occasionally.

I changed :
Code:
    db_table_load(working_directory+"/table.gdb");

    db_table_save("items",working_directory+'/table.gdb')
To :
Code:
    db_table_load(working_directory+"/logic.gdb");

    db_table_save("items",working_directory+'/logic.gdb')

and it crashes occasionally, that's the only thing I remember changing any reason why this would happen?
 
Last edited:
T

ThunkGames

Guest
I have been looking for a database system, and this seems pretty good. One review said that you can only have one database open at a time. Can you explain what this means? For my purposes I would need to be accessing multiple, separate databases simultaneously.
 
H

Homunculus

Guest
@studio furukawa , i can't see a reson why that change could produce an error like the one you posted, unless the new gd. file itself is corrupt or does not contain a particular table anymore. Can you provide some more info?
 
H

Homunculus

Guest
I have been looking for a database system, and this seems pretty good. One review said that you can only have one database open at a time. Can you explain what this means? For my purposes I would need to be accessing multiple, separate databases simultaneously.
It's true that you can only have a single database open at once, but you have to consider that a si gle database can hold any number of tables. For example you can have data for monsters, inventories, levels, etc... all stored independently in its table, and all belonging to one database
 
T

ThunkGames

Guest
It's true that you can only have a single database open at once, but you have to consider that a si gle database can hold any number of tables. For example you can have data for monsters, inventories, levels, etc... all stored independently in its table, and all belonging to one database
Ah! Thank you I mixed up tables and databases. I'll be purchasing shortly.
 
@studio furukawa , i can't see a reson why that change could produce an error like the one you posted, unless the new gd. file itself is corrupt or does not contain a particular table anymore. Can you provide some more info?
Hmm ill try to give more details, im wondering if having multiple things save in one step ex (database, game saves, IAP saves) is corrupting the file's?
 
H

Homunculus

Guest
Hmm ill try to give more details, im wondering if having multiple things save in one step ex (database, game saves, IAP saves) is corrupting the file's?
Don't think so... You can write me an email with your code (at least the part that's relevant in the error you get) and we could sort out what's happening together.
 
R

Ralf Kostka

Guest
Hi! :)

First: Sorry for my poor english. I hope you understand it.

I bought your DB-System because it´s much easier to create a Citybuilder-like game with a database system ;) (for me ^^)

But i have a little problem ... Maybe it is none, and then i´m just blind.

My level structure is saved as table "Mainworld". Size is 50 x 50 (50 Rows with 50 Collumns each)
I read the table in a level init-script into a ds_grid with
Code:
query = db_query_select("Mainworld","");
mainworld = db_query_exec(query);
x and y coordinates in the grid are hold in mxg and myg (mxg is the collumn and myg is the row-id too).
Accessing to them with mainworld[# mxg,myg].

So far it works!
But...
When i update the grid with maybe mainworld[# mxg,myg] = 3 (that works) and try to update the table Mainworld with
Code:
db_record_update("Mainworld",myg,mxg,3);
ERROR:
string_length argument 1 incorrect type (0) expecting a String (YYGS)
at gml_Script_string_parse (line 17) - while( string_length( str) != 0) {

or
Code:
    query=db_query_update("Mainworld",""+string(mxg),3);
    db_cl_only(query,myg);
    db_query_exec(query);
ERROR:
Data structure with index does not exist.
at gml_Script_db_cl_only (line 14) - ds_list_copy(list,argument1);

i get the shown error messages.

Did i missed something in your Documentation (which is quite great) or how can i update a Table with the data of a ds_grid?

Thank your very much for your help!

Greetings, Ralf

**EDIT**
Aahh... Found the error myself :oops:
Code:
db_record_update("Mainworld",myg,mxg,3);
has to be
Code:
db_record_update("Mainworld",""+string(myg),""+string(mxg),3);
 
Last edited by a moderator:
H

Homunculus

Guest
Glad you fixed it. I'm a bit surprised your fix works though, the second argument of db_recurd_update is a record id, which is always an integer.

You are correct anyway that the columnto update argument, being the column name, has to be a string (you only need to use string(myg), the ""+ part does nothing).

Question: in this specific case, isn't it better to use a ds_grid than a database table?
 
R

Ralf Kostka

Guest
I am still in learning progress . I´m new to GM:S and GML (but not so new to programming, a bit HTML+PHP, a bit VisualBasic and a bit Delphi, all just as a hobby).

I find it very usefull to store all game data in a database, so i have all i need (Player-Infos, Inventory, Quests and so on) at one place (i mean in one file). So why not saving the level layout (witch is changing over time of playing) in a table too? I know, it will be more complicated to programm this. Then i have only one file to work with instead of many files which has just a few data saved each.

And when i read the mainworld-table (which is the level-layout) it is a ds_grid i have to work with. Am i right? So i can learn to handle your database extension AND to work with ds_grid :)

But I'm always open to suggestions :)
 
H

Homunculus

Guest
The reason I asked is because it is indeed more complex (for you as a programmer) to handle database data than a grid directly when all you need is in fact tabular data. Moreover, the database poses a greater performance hit than a ds_grid, because every time you get the data (even if it's the whole table data), it has to construct a copy of it.
Just to say that while it's totally possible to use a database for that, it may not be the best option, but if you prefer to do it this way to also help the learning process, go for it.

The above of course applies to the level layout, it makes perfect sense instead to have inventory, quests etc... in a database.
 

andulvar

Member
Question 1: Excellent system you have created. I'm still learning the overall system, but is there an easy way to match the column names to a query result?

Question 2: Also I noticed a record can be fetched and returned as a ds map, but there isn't a function to add a record to an existing table from a ds_map.

I'd like to be able to fetch a record (basically to get the record format) as a ds_map, modify a few fields and then use that modified map to create a new record in the table. Is there an easy way to do that? I am looking for functions since some of my tables have dozens of columns and it would be cumbersome to define string sets for specific types or record updating.

Edit: Question 2: found db_record_build! awesome. ;)

Question 3: If I create a new record using db_record_build and then db_record_save, is there an easy way to get the id back?

Currently I'm doing the following (seems inefficient)
Code:
//save and clear new data record
    db_record_save(new_record)
    ds_map_clear(new_record)

//find the newly created record (New characters named "New Character" at creation)
    var query = db_query_select("player_roster","id,name");
    db_cl_where(query,db_op_eq,"name","New Character")
    var results=db_query_exec(query,false);
    var record_id = results[# 0,0];

//fetch the new record and change the default name to ensure unique id is returned
    new_record = db_record_fetch("player_roster",record_id)
    new_record[? "name"] = "Thug"
    db_record_save(new_record)

//destroy ds items
    ds_map_destroy(new_record)
    ds_grid_destroy(results)

//return the id
    return(record_id)
 
Last edited:
H

Homunculus

Guest
Hi andulvar,

Question 1: Excellent system you have created. I'm still learning the overall system, but is there an easy way to match the column names to a query result?
If you return a ds_grid, the column order is the one you specify in the select query, therefore there's no built in way to match that. If you instead leave the select columns empty (returning all columns), you can match them to the ds_list returned from db_table_column_names().

Question 3: If I create a new record using db_record_build and then db_record_save, is there an easy way to get the id back?
It's really easy, once you call db_record_save, the field "id" in the ds_map gets filled with the record id. You can also use that to check wether a ds_map holds a new record ("id" == -1) or an existing record ("id" > 0)

Cheers
 

andulvar

Member
If you return a ds_grid, the column order is the one you specify in the select query, therefore there's no built in way to match that.
Ah I see.
It's really easy, once you call db_record_save, the field "id" in the ds_map gets filled with the record id. You can also use that to check wether a ds_map holds a new record ("id" == -1) or an existing record ("id" > 0)
That's a lot easier, thanks!
 
A

AOCGmutong

Guest
It's not work in html5. When finishing loading, It's all black. So I delete the event "initialize demo". And the room shows up. But, of course, no data.
So...help please...
 
H

Homunculus

Guest
Since the last time I checked, the HTML5 module has a few problems with data structures that make GMSDB unstable on that platform.

As of now, the asset works properly, but due to the difference between ds_*_write in windows and html5 module, the HTML5 version can't load the demo data.
The real problem right now is that on HTML5, using ds_map_write and afterward ds_map_read on the same data, simply doesn't work (see 2016 note here: http://bugs.yoyogames.com/view.php?id=16566). This is crucial to db_load as all the database meta information is stored in ds_maps.

The problem will eventually get fixed I suppose, but I'll look into releasing a new version with a workaround for this soon.
 
Last edited by a moderator:
A

AOCGmutong

Guest
Ah well,now I know the problem...Thanks a lot.
Still, I'm working on a html5 game project which must use an online database. And the deadline is coming soon:(...
So..I'm looking forward to the solution of this problem, eagerly.;)
 

andulvar

Member
Is there any reason why using: db_record_fetch, and then using the normal ds_map lookup on the record would yield different results than using db_record_get?

Edit: figured it out. I had duplicated a field causing the table to be one field smaller than expected which offset the fetch values by one row.

If I use the following
Code:
char_record = db_record_fetch("player_roster",e_char_id)

text = db_record_get("player_roster",e_char_id,"Sword")      show_debug_message("Get Sword Skill: "+string(text))

text2 = db_record_get("player_roster",e_char_id,"id")       show_debug_message("Get player id: "+string(text2))

text3 = char_record[? "Sword"]      show_debug_message("Fetch record Sword Skill: "+string(text3))

text4 = char_record[? "id"]     show_debug_message("Fetch record player id: "+string(text4))
I get the following output:
Code:
Get Sword Skill: 1
Get player id: 1
Fetch record Sword Skill: 0
Fetch record player id: 1
 
Last edited:

cmdrMarc

Member
@Catan Hey - great extension - thank you!

I've tried building up a string to use as the argument for db_record_create, but when I look at the DB afterwards, it's just put the whole string in as the first field, even though I formatted the string correctly with " at the beginning and end, and , between each field. Can I not pass it a single string in this way?

Eg the string looks like this: "John,Smith,M,28,38,57,4,5,7,5,6,9,5,7,3,8,2,9"

and I'm trying to use the command like this: db_record_create("stats", statString);


Woop I fixed it myself - it just didn't need the " at the start and end of the string :)
 
Last edited:
H

Homunculus

Guest
Hi guys, here is a short update on the status of the project. I'm currently working on version 3.2, an update focused mainly on two aspects: GMS:2 compatibility, and a batter saving system.

While the first is self explanatory, the latter changes quite a lot of things in how the database storage is managed, but it's a first step into something I've been wanting to do for quite a while, that is loading data on demand from file. I'm not quite there yet, but the current saving system certainly needs to be reworked into something a bit more structured and more HTML5 friendly.
You don't have to worry though about your old database files, as the update will include a legacy import script.

Stay tuned!
 
Last edited by a moderator:
H

Homunculus

Guest
Version 3.2 is out for GM:S 1.4 and GM:S 2! Be sure to check the (updated!) documentation and its section about upgrading and new features!

If you have any problems, just let me know, and as always feedback is welcome.
 
Last edited by a moderator:

cmdrMarc

Member
@Catan

Looking at your example for this: db_cl_where(query,db_op_eq,"name,color","apple,green")
In my code, the name and color I want to query are in variables - how do I pass these? At the moment my game is just assuming the variable names are what I want to query on, rather than what they contain. I feel like I'm missing something obvious..

Do I need to just use multiple wheres so I don't have to use " "? Does it support this?
 
H

Homunculus

Guest
@Catan

Looking at your example for this: db_cl_where(query,db_op_eq,"name,color","apple,green")
In my code, the name and color I want to query are in variables - how do I pass these? At the moment my game is just assuming the variable names are what I want to query on, rather than what they contain. I feel like I'm missing something obvious..

Do I need to just use multiple wheres so I don't have to use " "? Does it support this?
You just have to concatenate the values in your variables so that it matches the required string format. Assuming your variables are called name and color:

db_cl_where(query,db_op_eq,"name,color",name + "," + color);

If this happens frequently in your code, you could create a concat script that does this for you, so your call becomes:

db_cl_where(query,db_op_eq,"name,color",concat(name,color));

Edit:

Actually, that could be a nice script I could add to the system...
 
Last edited by a moderator:

cmdrMarc

Member
@Catan Me again :) Is there an easy way to get the ID of the DB record just created please? I need to use it to add to a new linking table, but I can't see an easy way to grab it without doing a query based on the values I just gave it to create the record, which doesn't seem very efficient..

EDIT wait I'm an idiot and didn't notice the db_record_create returns the ID. I'll leave this here in case other people are idiots too :)
 
H

Homunculus

Guest
Db_record_create returns the id. if you use db_record_build instead, you just need to access record[? "id"] after saving it.
 

cmdrMarc

Member
@Catan

When I'm using a join, i'm following the format of table.field when referring to fields, but db_query_update didn't like that - I had to use just the field name. Is that because the table.field format only applies to filters?
 
H

Homunculus

Guest
I need to confirm that, but i'm pretty sure you need that format only in select and where statements. When using update tou may want to use normal update columns.

I'll check to see if everything works as expected though, joins are mainly meant to be used with select.

You can always use a select to get the record ids, and run a regular update query with a only clause. That's probably the best idea as of now
 

cmdrMarc

Member
I need to confirm that, but i'm pretty sure you need that format only in select and where statements. When using update tou may want to use normal update columns.

I'll check to see if everything works as expected though, joins are mainly meant to be used with select.

You can always use a select to get the record ids, and run a regular update query with a only clause. That's probably the best idea as of now
To be clear, it is working without using the table.field formatting, so this is more me checking that's how you expected it.
 
H

Homunculus

Guest
That's how it is supposed to work (I double checked to be sure). You can't change the values of a join table when doing an update, therefore there's no point in using the table. prefix. You still need that for db_cl_where though.
 

rIKmAN

Member
Hey @Catan,

Would the upgrade to GMS2 compatibility have bought any speed improvements to the database queries when compared to the older 1.4 only asset?
 
H

Homunculus

Guest
@rIKmAN Not much as far as I could see, but I didn't run any thorough test. That's to be expected though, since one of the reasons for GMS2 speed boost is DX11, which is mainly related to graphics.
 

cmdrMarc

Member
That's how it is supposed to work (I double checked to be sure). You can't change the values of a join table when doing an update, therefore there's no point in using the table. prefix. You still need that for db_cl_where though.
Cool, it did sort of make sense. Might be worth making the documentation clearer because at the moment it says:

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

Homunculus

Guest
@cmdrMarc documentation updated. Thanks for pointing this out, I didn't realize it but it was in fact confusing.
 
N

Norby

Guest
Hello,
I'm having some good success with the DB. Thanks for the add on!
Here's a difficulty I'm having though, I'm currently trying to get the DB to delete the first row of data from a table every time I click delete and am using your example to test this. But I can't even get it to delete once.
I'm in the items database, ob_db_items_btn: User Event 2. I've noted where I'm having trouble and noted the working line of what I want to continue to happen in the table. I hope you can let me know what's off here.
Thank you very much!
Here's the code:

/// delete

//here's trouble!
var del_id = db_record_first("items", "type", "Weapon"); //Seems like this should work but doesn't

with(obj_db_items_controller) {
//if(show_question_async("This will delete all records you see now. If you want to delete a specific record or subset of records, try applying a filter. Continue?")) {
var q = db_query_delete(table);

//WORKING!
//db_cl_only(q, "1"); //This delted the first record. But only id #1 after that it fails. I want to continue to delete records.
//WORKING!

//TESTING
//Here's trouble!
db_cl_only(q, del_id);
//TESTING

db_cl_sort(q,query[? "sort_field"]);
db_cl_limit(q,query[? "limit"],query[? "offset"]);

//show_message(string(db_query_exec(q)) + " records deleted");
db_query_exec(q);

event_user(0);
}
 
H

Homunculus

Guest
Hi Norby,

first of all, have you tried to output the value of del_id every time this code is run, to check that the ID is actually fetched correctly?
Secondly, why use sort and limit if you want to delete a specific ID?
And lastly, the best way to do this is by using db_record_delete(table_name,id) , you don't have to use a query since it's a single record.
 
N

Norby

Guest
db_record_delete(table_name,id) was the solution - thanks! I think because the example was using query I was trying to stay in those parameters, not sure though.
What I'm getting ready to do is create DB that is like a deck of cards. I need to be able to delete records as card objects are created to avoid duplicates. In my testing, I just needed to figure out how to get a record to repeatedly delete (ie not be associated with a specific id but associated with a variable, like the first record. Here's the much-simplified code which works:

var del_id = db_record_first("items", "type", "Weapon"); //Select first record

with(obj_db_items_controller) {
db_record_delete(table,del_id) //delete record

event_user(0);//related to the DB example
}

Thanks again!
 
H

Homunculus

Guest
I also figured out why the previous code didn't work. db_cl_only(q, del_id); takes a string of comma separated ids as argument OR a ds_list of ids. If you pass a number as argument it assumes you are providing a ds_list, which is not the case here.
db_cl_only(q, string(del_id)); would work. In your case db_record_delete(table,del_id) is what you are looking for though :)
 
L

Linus Griebsch

Guest
Hi, i wanted to ask if there is a way to check if a certain field equals one of many possible values, i thought you could do it via db_op_eq like in the code below but this does not seem to be working.
var _query = db_query_select("modifier");
db_cl_where(_query, db_op_eq, "id_modlist,id_modlist", "7,8");
_mod_dsgrid = db_query_exec(_query);

This just returns nothing as if i was looking up a value thats not in the database, if i look up the values for 7 and 8 separately however i get the appropriate results.
 
H

Homunculus

Guest
That's because db_op_eq uses a AND comparator, but you need and OR.

You can easily do that by duplicating the db_op_eq and changing the operation accordingly. If you need a more specific explanation just ask.
 

rIKmAN

Member
Hey @Catan, you were kind enough to send me the DB Extension a while ago to see if would be suitable for what I had planned before purchasing it. Unfortunately it wasn't, but after I saw you updated it to work with GMS2, I bought it the other day anyway.

I still don't have a use for it right now, but the purchase was partly to say thank you for being so kind previously, and partly because I may find a use for it in a future project now that it was updated to work with GMS2.

You're one of the good guys, keep up the good work! :)
 
V

vwatson

Guest
Hello, I'm new to GameMaker and I'm using GMSDB. I have imported a csv file and am able to query it. So that is all working great. What I want to know is when I create a GameMaker program (for Windows/Mac), how do I include the gmsdb database in the distribution/installation setup?

Right now the database is in the sandbox folder, the user/appdata/local folder on my windows system. Can I export that database to a file, and then include that exported file in the project? When a user installs the program, I'd check to see if the database exists in the sandbox, and if not, import from that file I exported? Will db_import() be able to locate the included file?

I hope that makes sense. I have programming experience, just not much yet with GameMaker. :)
 
H

Homunculus

Guest
That shouldn't be a problem at all. As you said, you have to export the database using db_export and adding that to the project included files. db_import works essentially like the file functions, where it can locate file relative to the included files or sandbox.
You can also check if the db has already been imported by using db_exists
 
V

vwatson

Guest
Okay, thanks. Very nice extension. I'm probably using GameMaker in a non-typical way - for educational software, and the database is perfect for what I need. :)
 
Top