[DEV] dbms3.r 02

PROJECT NAME: dbms3.r (temporary) PURPOSE: writing a small and simple relational database management system in REBOL, for use with applications that don't need a full blown separate DBMS such as MySQL. With respect to dbms.r, it will be more efficient in the data storage to be usable with much more rows per table, and possibly will have a more powerful/abstracted interface. DEVELOPMENT, part 2 here i am again... didn't expect to cause all that traffic. i'll go off-list soon. first, let me make a correction to the low level interface: -- memory-table-funcs: context [ ; memory-table-funcs-code row-append: func [ "Append a row to an on-memory table" table [object!] row [block!] ; returns the row-id of the new row ] row-append-code row-pick: func [ "Pick a row from an on-memory table" table [object!] row-id [integer!] ; returns the row or none! for an invalid row-id ] row-pick-code row-change: func [ "Change a row in an on-memory table" table [object!] row-id [integer!] new-row [block!] ; returns the row-id of the changed row, or none! for ; an invalid row-id ] row-change-code row-remove: func [ "Remove a row from an on-memory table" table [object!] row-id [integer!] ; returns true, or false if the row-id is invalid ] row-remove-code ] -- you see i changed object! to block! for rows; object!s should be handled at a higher level than this. i also changed the argument name for row-change so that it is clearer that it needs a complete new row now. now we can start with the code; we'll need to know how an in memory table object! looks like. at this time we just need: -- create-memory-table: func [ "Create a new on-memory table" ; returns the new table object! ] [ context [ data: make block! 1024 ] ] -- so... -- row-append: func [ "Append a row to an on-memory table" table [object!] row [block!] ; returns the row-id of the new row ] [ ; row-append-code insert/only tail table/data row length? table/data ] -- wrt the discussions on the list, the row-id is the position in the block, so it is implicitly present; the status we are interested with in this case can only be "present" or "deleted": so it is present when the element in the data block is a block and is deleted when it is none. this way row-pick will magically work as: -- row-pick: func [ "Pick a row from an on-memory table" table [object!] row-id [integer!] ; returns the row or none! for an invalid row-id ] [ ; row-pick-code pick table/data row-id ] -- for in-memory tables the row-id does not need to change upon record changes, and row-change will be as simple as: -- row-change: func [ "Change a row in an on-memory table" table [object!] row-id [integer!] new-row [block!] ; returns the row-id of the changed row, or none! for ; an invalid row-id ] [ ; row-change-code poke table/data row-id new-row row-id ] -- well, actually it needs some error checking. row-id should not be greater than length? table/data and should not refer to a deleted record. if this happens, none! should be returned. pick handles both the cases well. -- row-change: func [ "Change a row in an on-memory table" table [object!] row-id [integer!] new-row [block!] ; returns the row-id of the changed row, or none! for ; an invalid row-id ] [ ; row-change-code if not found? pick table/data row-id [ return none ] poke table/data row-id new-row row-id ] -- as last: -- row-remove: func [ "Remove a row from an on-memory table" table [object!] row-id [integer!] ; returns true, or false if the row-id is invalid ] [ either found? pick table/data row-id [ poke table/data row-id none true ] [ false ] ] -- time for a bit of testing, heh? i'm using test code such as: -- test-code: func [code] [ print [ "Call: " trim/lines mold code newline "Result:" newline mold do code ] ] random/seed now print "On Memory low-level functions test" print "*********************************************" print "Creating table" test-code [ test-table: create-memory-table ] print "*********************************************" print "Simple test" test-code [row-append test-table [1 2 3]] test-code [row-pick test-table 1] test-code [row-change test-table 1 [4 5 6]] test-code [row-pick test-table 1] test-code [row-remove test-table 1] test-code [row-pick test-table 1] test-code [row-change test-table 1 [7 8 9]] test-code [row-pick test-table 1] print "*********************************************" print "RANDOM test" print "*********************************************" print ["Testing row-append for" n: random 1024 "runs"] loop n [ test-code compose/deep [ row-append test-table [ (random 10) (random 10) (random 10) ] ] ] print "*********************************************" print ["Testing row-pick for" n: random 100 "runs"] loop n [ test-code compose [row-pick test-table (random 1024)] ] print "*********************************************" print ["Testing row-change for" n: random 100 "runs"] loop n [ test-code compose/deep [ row-change test-table (random 1024) [ (random 10) (random 10) (random 10) ] ] ] print "*********************************************" print ["Testing row-pick for" n: random 100 "runs"] loop n [ test-code compose [row-pick test-table (random 1024)] ] print "*********************************************" print ["Testing row-remove for" n: random 100 "runs"] loop n [ test-code compose [row-remove test-table (random 1024)] ] print "*********************************************" print ["Testing row-change for" n: random 100 "runs"] loop n [ test-code compose/deep [ row-change test-table (random 1024) [ (random 10) (random 10) (random 10) ] ] ] print "*********************************************" print ["Testing row-pick for" n: random 100 "runs"] loop n [ test-code compose [row-pick test-table (random 1024)] ] print "On Memory low-level functions test - END" print "*********************************************" -- which seems to produce reasonable output. [TO BE CONTINUED]

Well, let's say that I am after simple db system like you created. But - I do have mental conflict with "in-memory" - What do I not understand is - when is status of db written to the disk? Apps tend to crash! If we will not have enough granularity for 'commit-db-to-disk, it will not be good ... -pekr-

Trying out Phil's mailer... :-)

>Well, let's say that I am after simple db system like you created. But - >I do have mental conflict with "in-memory" - What do I not understand is

A system I'm sure not many of you are very familiar with - MOO - has similar issues. Basically, it's another form of a MUD (multi-user dungeons/domains/etc - text-based multi-user gaming environment) which deals more with programming than gaming. In any event, the entire database file - which includes all code, 'rooms', players, etc - is loaded into memory at boot. The admin defines a certain 'checkpoint interval', say 3600 seconds (1 hour), after which a copy of the database is actually made in RAM, but locked (the primary still runs), and is written to disk (usually a file.db.new). It's not always perfect (of course). But, that's how they deal with it (granted the base system is probably around a decade old..). Depending upon system volatility and amount of data transfer and database size, you would need to alter the saving times, or even break the db into multiple files with a single prime 'index', so to speak, to keep in RAM. Err, course, these are comments from someone who's never done any actual DB coding.. *blushes* Just seem like semi-obvious solutions.