86 replies to this topic

[darkelfv]

This is awesome, Thanks for making this. I plan on using it for the rougelike game I'm working on.

[Kolink]

Just curious: wouldn't it be better to use a ds_grid?

[darkelfv]

I just realized one thing that may hinder my development. How is the memory of the stored data used, Cleared, etc...

For example. A db structure is so we can have many data entries that are file based that means we make queries for the data and it quickly returns what we need with out having the entire thing in memory.

You have support for import export which is fine, but when it comes down to it, if i have to load the entire db file into memory to access the 3 lines i need then this isn't going to be any more efficient then keeping everything in memory.

If that is true i could break up the tables into multiple files, which would be ok. However if it takes a long time to load large tables into memory this would break the program as well. The question then is when you load a table into the game does it destroy delete the memory of the old one, assuming your using the same variable for the table? If i load 5 tables in a row, how do i go about getting rid of the old table data from memory. Do i have to call db_table_destroy(id) each time and create a new one when importing a table? Or is this handled automatically when i import to the same table with structure.

Finally my suggestion is to make a couple of calls that allow you to grab queried data into a new table that reads from a file instead of loading the table into memory then grabbing the data you want and deleting the data. I think this would complete your system and allow for minimal memory usage with large db tables.

Thanks

[benetonmovie]

darkelfv, your reply is confusing me a bit. I don't think any table would be too big to be kept in memory (especially these days).

You have support for import export which is fine, but when it comes down to it, if i have to load the entire db file into memory to access the 3 lines i need then this isn't going to be any more efficient then keeping everything in memory.

Well normally you would just import all your tables at the beginning of the game/program, and export them before closing (if you want to save the data, that is). I can't tell you how "efficient" it is but I use GM's ds_list_read() function to read the data of every column, and it seems pretty fast.

The question then is when you load a table into the game does it destroy delete the memory of the old one, assuming your using the same variable for the table? If i load 5 tables in a row, how do i go about getting rid of the old table data from memory. Do i have to call db_table_destroy(id) each time and create a new one when importing a table? Or is this handled automatically when i import to the same table with structure.

It's handled automatically. No useless data is kept in memory.

Finally my suggestion is to make a couple of calls that allow you to grab queried data into a new table that reads from a file instead of loading the table into memory then grabbing the data you want and deleting the data. I think this would complete your system and allow for minimal memory usage with large db tables.

This is where I really don't follow you, sorry. You want everything to be stored directly in files instead of memory?

[Schyler]

Any chance you may support SQL, for us PHP junkies?

[benetonmovie]

Any chance you may support SQL, for us PHP junkies?

Haha, well it would be a completely different thing. My extension is really for offline tables, and has nothing to do with SQL. Sorry, I have no idea how to do that, let alone do it securely.

[darkelfv]

The game I'm making will expand into a large world where every mob, item, object (including trees walls) will be independently recorded into the db. This also does not include the 1000 year history I'm creating per game which could be many entries per year.

Loading from a file lets me query data i need and only load that into memory, streaming in the land, objects, object details, mobs, mob equipment, mob current status, hp stats, afflictions, bonuses.

For instance if i need to grab a time period between 200-300 of the 1000 year history, loading the entire thing alone would take a while. Assuming a bare minimum of 3 races, with a minimum of 24 history events per year each. That is 72000 records at the least. That is just one of my tables as well, the history table will have to tie into sub tables that have details on some of the records which could double or triple the amount of records. This is of course per game.

Think of a mmo - except the mobs remember how much health they have. Also if i cut a tree down it will be recorded. If a mob kills me and i make a new character. 10 years will pass and i will be able to find and kill the mob that slain my old character.

I am also doing a detailed event history of every move you make in the game to help generate a detailed story book of your adventure once you complete the game.

There is also alot of data going to be saved for main quest and sub quest generation.

I'm used to working with sql tables, or mdb access tables, File based tables allow you to store lots of records, but trying to load them all at once is not really optimal. Maybe i'm the only one that is trying to make a massive game, but the whole point of having a db imo is so you don't have to have all the data in memory at the same time.

The other reason is i intend to try to make this work on a psp at some point, They haven't released it yet but they are working on it. So making the game as fast and optimized as possible is important. Idk if gex will be supported by psp but it would be cool to get it to work on psp either way. So my target is low memory use.

I can break up my db tables into separate files, but it would be easier to directly get targeted data from a single table though.

I wonder how much 72000 records of string data would take up in memory...?

Thanks

I will do some tests when i have some time and get the full history set working. I also forgot to reply saying that i would not be loading data strictly from files, there would be some streaming and depending on where the character was and what quest he is on, there would be many tables loaded into memory. So not everything would be just from files, its more of just using what is needed in memory at the time. I think a lot of the hard core table creation / reading would be during the generation process and this is already taking a while to make the landmasses, so optimizing this is really my goal with out using alot of memory.

Edited by darkelfv, 04 May 2010 - 04:58 PM.

[darkelfv]

Well I did some testing and I think my worries have been resolved. While I will be using files to store the tables and I will break them up, I don't see as much of a issue as i first excepted. Keeping tables around 10,000 records or less shouldn't be a issue.

Creating a table with 72000 records took about 15 seconds and took about 20 megs of memory (including standard gamemaker program)
A 10 entry was about 11 megs of memory , basically the base size for a game maker game.

Interestingly enough loading took about 20 seconds and was around 30 megs

The table was a single string table with the string
"This is a test event string for checking how much memory a string record takes"

When exported the file was around 13 megs

The program was a bit unresponsive, however i think this was because the drawing function in the step event that tries to draw the table each frame. So not a bid deal.

So while it would be neat to have a function grab a result directly from a file, its not really required.

Thanks again for making this awesome tool.

[benetonmovie]

darkelfv, your project is very interesting! I'm glad you're using my database system.

So while it would be neat to have a function grab a result directly from a file, its not really required.

Well, I'm always open to suggestions, but I don't see how such a function would work. Can you explain how it would be called, what would be the arguments, etc. and I could try working on it?

Thanks again for making this awesome tool.

You're very welcome!

[darkelfv]

I would do a function similar to the one you already have.

db_entry_get_pos_ext(table id, "column1=variable1,column2=variable2,column3=v
ariable3", 0, list, ",");

Table id would be file name.
Instead of list it would be a table.
It would first load the structure of the file. Then search for the columns where the requirements matched.
Then it would load the entire row where it found a match into the new table.

*Notes and possible problems. Part of the reason file based db's work this way is because they have some sort of indexed system so they can do quick searches for records with out having to go through them one by one.

Secondly > < (greater then, less then) are supported by the queries. So i could say where Column1 > 100 and column1 < 200

Thinking about it now, that would be a lot of work on top of what you already have. Not to mention i don't know your current file structure but it seems like a encrypted ini file or some sort of bin file. So indexing it, figureing out how to do that on top of having some sort of greater then less then would probably not be practical for you to add.

As far as some sort of indexing strategy i suppose having something like this might work.

Column index section - list of all columns pointing to a seconded section that has sorted column data
Sorted column data index - Sorted list of data that is easy to search through and contains the pos of the record.

At the front of the file you might have a list of the columns with a index (pos) of each of the secondary column data indexs.
These secondary index records would be the the data with the first 5 characters of a string or all of the real numbers sorted From Low to high. Then it would also contain the pos of the real row of data.

So you could search for column2 (a real, say 100) even if the data contained 1000's of other rows. It would search the beginning of the file find that column2 index is stored at file pos x, then looking at the secondary index it would find the starting data is 1, it could skip 100 records and maybe some are missing so it finds record data 105 then searches backwards till it finds 100 then grabs the record pos and returns that entire column of data from the real table.

Like i said it would be more complex. Real data would be stored as a file byte position at the start of the file as well. For where the real table data started.

Here is a example of what im talking about to try to clearify it.

Column index
Column Name sorted alphabeticly , File posistion of column index , Type
Column1 , byte 10 , real
Column2 , byte 20 , string
Column3 , byte 30 , string

Column1 index data (sorted), row posistion
5 , 1
10, 3
15, 6
20, 2
25, 4
30, 5
35, 7

Column2 index data (sorted), row posistion
a , 3
b, 1
c, 4
d, 2
e, 6
f, 5
g, 7

Column3 index data (sorted), row posistion
bike , 2
carrot, 7
jump, 3
plant, 4
quick, 6
van, 5
zebra, 1

Then the real data would be following
Pos, Column1 , column2, column3
1 , 5 , b, zebra
2 , 20, d, bike
3 , 10, a, jump
4 , 25, c, plant
5 , 30, f, van
6 , 15, e, quick
7 , 35, g, carrot
So if i wanted column1 data 30 it would know it was on row 5 and could return column1 column2 and column3 only into a new table.
pos 5 , 30, f, van

Having ranges is important to minimize how many times you would call from a table.
Also maybe a search string like column3 contains "a" which would return 1 4 5 and 7
advanced contains would be like contains "ump" which would contain 3

These are just my thoughts on the matter as i do not know exactly how they are done in the professional world.

[benetonmovie]

Hmmm, that sounds really complex. And your indexing strategy certainly wouldn't work with the way it's done at the moment, because each column is encrypted as a ds_list exported to a string with the function ds_list_write. So (I think) each column would need to be decrypted first (using ds_list_read), so they would be loaded in memory (everything would be cleaned up at the end of the script, of course, but still...). In other words, if you really don't want anything more than the requested values to be loaded in memory, it would require a big change to the file system (unless I figure out a way to read specific parts of encrypted ds_lists manually).

[brod]

It's be very helpful if this functioned a bit more like mysql tables. Really, all I want is for rows to have a unique ID variable, and by that I mean no matter how many rows you delete or add, each row will always have some kind of variable(preferably numerical) that will never be the same as any other. Would be extremely useful.

Edited by brod, 21 May 2010 - 11:52 PM.

[benetonmovie]

Thanks for the suggestion, but what would you use it for? You can create your own "ID" column and assign unique ID's to every row.

Edited by benetonmovie, 22 May 2010 - 01:56 AM.

[brod]

I'd use it so that when it's in the game, I would never have to change anything to put it back in. For example for my game, I use the database as a reference for the inventory. So instead of doing an inventory like
array[0] = "Potion"
array[1] = 5;

instead, with the database, I assign the inventory ID's instead

array[0] = 0; //The row with and ID of 0

It's pretty useful, it makes it so I never have to replace the actual game data, I just have to mess with the database to get the changes in-game. And besides that, I also like to reorganize my rows a lot, and so since I do that I can't rely on the position value since it changes a lot.


True, I could make my own column, but I'm suggesting for conveniences sake
You could have a global ID variable that goes up by 1 whenever a row is added, and it's assigned to newly created rows. It should never decrease, though.
I already added it in, but I think it'd be a good idea.

Edited by brod, 22 May 2010 - 02:28 AM.

[benetonmovie]

There's something I don't understand. You say you can't refer to the row positions because you do a lot of changes to your tables; but how would unique ID's solve that problem at all? If they were automatically assigned, I guess they would be in creation order, so if you add rows in-between your existing ones, unique ID's would change just like the positions. What am I missing?

[brod]

Well Unique ID's would make it so I wouldn't ever have to change data in-game.
Like, say I have this set up:

POS  ID	Name
0	0	 Potion
1	1	 Pencil
2	2	 Super Tie
3	3	 Firefly

And I decide that I don't like Super Tie being there, so I bring it to the bottom. It would end up in this formation, instead:

POS  ID	Name
0	0	 Potion
1	1	 Pencil
2	3	 Firefly
3	2	 Super Tie

So now, even though I changed the order, the ID's still the same. So, if in my game I referred to all super ties as 2, I wouldn't have to change it to 3, as the position changed to.

Edited by brod, 22 May 2010 - 02:40 AM.

[benetonmovie]

Oh in-game, I get it. I thought you meant re-organize the rows in the code. Anyways, I really think this should be a custom feature, because not all tables need this and it would sometimes waste memory. Feel free to insist if you see a logic to it (is there another reason mySQL has this?), but to me it really sounds like something that should be custom to your project.

Edited by benetonmovie, 22 May 2010 - 02:49 AM.

[brod]

Yeah true, that's how it is in mysql too, actually. Just thought it'd be a neat idea

A neat thing would be an AUTO INCREMENT function for columns, though. (That's how mysql does ID's)

Edited by brod, 22 May 2010 - 02:52 AM.

[benetonmovie]

That's a good idea! I'm adding it to my to-do list for the next version. Thanks!

[brod]

Hah thanks aren't needed, everything that gets added makes it easier for me, SO ADD ON >:]