Issues with storage of data in stack

[Mark Talluto]

Hi Lagi,

Our LiveCode array database does not use SQL or any other database. In terms of local only database, we do not rely on anything but LiveCode. It is purely a LiveCode derived system. Data is manipulated using familiar methodologies to other databases. The data is encrypted using ‘encrypt’ and stored using 'arrayEncode()’.  

The full array of the database is stored in memory. This method provides very quick access to your data thanks to the amazing performance provided by LiveCode. 

This might get a little long. I am happy to take this off list for more details. I will try to be as succinct as possible.

-A little more explanation on storing data-
Each record is stored in array that looks like this: tableID/clusterID/recordID/recordData…
When a given record/s is updated, we cache the recordIDs that were touched. All data is updated first in memory then cached to disk. We then refer to the cached records and conclude which clusterIDs we affected. Thus, you can very quickly save only the clusters that have been modified. Each cluster will have one or more records associated with it. The clusters are the first ’n’ characters of the recordIDs. We use UUIDs as recordIDs. The cluster sizes are definable, giving us the power to decide where to apply the optimization. Clusters of only one or two characters will generate less clusters to be stored. This makes loading of tables from disk to RAM very fast. A cluster of 3 chars or more allows for less records per cluster thus improving saving from RAM to disk to be faster.

The pseudo code for this might looks like this:
-receive request for update in your API
-store the changes to your master array in RAM
-remember the recordIDs touched in a variable
-calculate the clusters touched by taking the first ’n’ characters of the records touched and make a new list of the clusters you need to write to disk
-write appropriate clusters to disk
-return the results of the action (any errors, recordIDs…)

You will find this method to be very performant and easy to manage. This is not particularly complicated to write. Once you get it all working you might add other niceties like:
-error checking the input before storing anything
-store metadata on each updated record: recordVersion, recordTime, updateTime
-add security using ‘encrypt’
-build simple APIs to do your CRUD first
-add other APIs as needed to make accessing your data easier

Here is an example API for storing data that you may find useful when making your own system.

-Input (array)-
put “Steve” into tInputA[“firstName”]
put “Jobs” into tInputA[“lastName”]
put “rolodex” into tInputA[“cdbTableName”]
put “local” into tInputA[“cdbTarget”] —We would use ‘cloud’ when we want to store offsite.

Your system might verify that the keys ‘firstName' and ‘lastName' are actual keys. This is very SQL in terms of error checking. Or, do not check the keys and feel more noSQL in nature.

From here we take the array and pass it to a function. 
put cdb_create(tInputA) into tRecordID —returns the unique UUID representing the recordID

The ‘cdb_create()’ function basically runs the pseudo code proposed above. And voilà, you have your first record stored.

I hope this gives you some ideas. We have successfully used this in enterprise level scenarios. We nightly interchange data with our customer's database. We have not run into any issues with IT in terms of the type of database we are using in our backend. I fully support your experimentation with arrays as a data store.  :)

If you are interested in seeing other APIs we have found useful, here is a running list. Look under the API dropdown for more ideas.
http://canelasoftware.github.io/cassiaDB_docs/ <http://canelasoftware.github.io/cassiaDB_docs/>

-Mark


> On Mar 9, 2018, at 2:07 AM, Lagi Pittas <iphonelagi at gmail.com> wrote:
> 
> HI Mark,
> 
> I am intrigued by your way of saving only whats changed and also when
> you say save to disk after arrayencoded. Do you meanas a simple  save
> as a binfile or in an sqlite BLOB?
> 
> I would really like to see some exampleish code on saving to disk - if
> it's other than a single array in a single binfile - I think even I
> can do that. But your other ideas sound brilliant.
> 
> Regards Lagi