Database using only native Livecode?

[kerplop]

Hi, I didn't see anything on this board about my question, so I'm starting a new topic.

I was wondering about the feasibility of a relational-style database completely internal to LC. That is, using only LC stacks and the built-in functions and commands. No external mysql, sqlite, etc. interactions.

I'm thinking of scenarios of less than, say, 10,000 primary table records with an average of, say, 15 related secondary table line items.

The For Each function alone seems so fast with text data in memory that it might make a native db with finding, sorting, and reporting a real possibility.

So my question is, has anyone implemented this small native LC db scenario before, or have any links, insights, warnings, or tips on viability? Thanks!

[stam]

I'm sure many have used LC with something like this, but for a flat DB structure, or maybe smaller DBs with 1-2 levels of cross-"table" relationships.

The main issue I imagine, is that you'd need to load the whole thing into memory. If your databases are large that will definitely be an issue. I deal with some medium-large size databases in SQLite (80-100 Gb data files), would definitely not recommend using text for this.

SQLite also includes file compression, so the footprint of SQLite files will be a fraction of the size of the text files (without needing to be unzipped etc).

Then there's the question of 'why' since SQLite has such a very excellent job of it. What if you have to have multiple complex relationships? How do you prevent "SQL injection" type attacks? Do you manage parameters?

Why not use the best tools for the job? Or to put it differently, is it worth the significant effort? what would be the rewards?
To put it as our American cousins would: Is the juice worth the squeeze?
Is the effort better spent on creating a user-friendly ORM interface or something similar to abstract the sql syntax?

If wanting to stick to "pure" LC to access the db, the closest commercial database you'll find is LiveCloud (they have a free tier, have a look at http://LiveCloud.io), but again the backend is not LiveCode...

As for LC - as you know most things are completely possible but end up being a lot of work for little gain in things like this...

[kerplop]

Thanks, stam, that all seems like good general advice. In this particular case my preference is for something as simple as possible, and I figure text lists are the simplest and easiest to eyeball. I have some hazy thoughts about templating some generic primary and secondary table text file "relationships" and having the data online so that a user would pull it down and do the queries, reporting, updates, or whatever locally and push back up when necessary. And as I mentioned, file sizes would be pretty small, as there's not that much raw data. Thanks for the guidance, I think I will probably explore the idea a little more and see what happens.

[Oliver901]

Your idea of a relational-style database completely internal to LiveCode sounds intriguing. While I haven't personally implemented such a scenario, I can see the potential benefits of leveraging LiveCode's built-in functions and commands for smaller-scale databases. The speed of operations like the For Each function with in-memory text data does seem promising for handling data internally. I'd be interested to hear if anyone has experience with this approach or any insights to share on its viability.

[dunbarx]

Stam makes two valid points:

1- The built-in tools have their own learning curve apart from "basic" LC, but are already designed and optimized to do such tasks. That makes sense.

2- Rolling your own is certainly workable, more fun, and a far better learning tool, since you will be working with LC "itself" and not fitting SQL tools into some sort of LC foundation. But the point is what kind of database are we talking about? If really large, use the tools. If reasonable and relatively straightforward, use pure LC.

Craig

[stam]

Just to expand on this, I would suggest differentiating a datastore from a general use database.

Using LC's built in objects as a datastore is eminently feasible, quick and easy. We all do this. However almost always, this will be tailored to the solution, and using this in other situations in ways not anticipated will be increasingly difficult and likely not as efficient.

A well tested database like SQLite on the other hand (hands down the commonest in-app database across all programming languages), is extremely versatile, well tested and performant. It's nigh on impossible to break. SQL is an extremely versatile language that has been carefully crafted over the years to allow nearly any conceivable query and function relevant to the data.

My point being that a datastore is a very valid approach and may even be something that can be used across 1 developer's apps.
However to replicate the functionality of something like SQLite and make it generalisable to all developers in any conceivable scenario would be an extremely heavy undertaking and I doubt it would be as performant as just using SQLite.


-----------------------------------------------------------------------------------------------------------


However, there is no reason to be purist on how the data is CRUD'd. I think what most would really care about is having a database accessible via 'normal' liveCodeScript.
Instead of rolling your own from scratch, a different approach may be to create an intermediate library between the language and the database so you use for example liveCode native code and the library translates into something that drives SQLite or other chosen DBMS. This is a common design pattern in many languages and is often referred to as an ORM (Object Relational Mapping).

There is at least one LiveCode implementation of this as far as I know: Andre Garzia's dbLib: https://github.com/soapdog/livecode-dblib
I think revIgniter may include something similar for server, and liveCloud implements it's own version of something similar - you can see the syntax here: https://docs.livecloud.io.

Personally I find using a pure ORM a bit cumbersome and limiting because I'm relatively fluent in SQLite and would rather just use this for more complex actions. Plus, since SQL it very widely used across all languages, searching online for solution to a particular problem will invariably provide a result, as it would be extraordinary to conceive of a problem that hasn't already have been asked and answered.

No doubt a library like dbLib suits many though - and as it's openSource you can freely download the source and modify it to your liking.

What I personally do is implement an intermediate library that implements some simpler commands in generic LC (eg function getRecord pTable, pPrimaryKey -> returns an array for the record contained in table pTable for the record with primary key pPrimaryKey), as well as just allow complex SQL and return an array (eg command doSQL pSQL, pParamArray -> executes SQL optional with a parameter array, and returns either outcome as a string or data as array in the result).

The latter example doesn't really differ significantly in syntax from the built-in handlers, but I do this because the built in commands ether return a simple TSV, which is not usually suitable for my needs, or a database cursor, which still needs to move data into an LC usable format like an array so having this in a library just means I can keep my code leaner/cleaner.
It's still a work in progress that keeps changing with every project I create, but if I'm ever satisfied it's good enough to be used by anyone will likely just release it as openSource as well... along with this, I've been toying with creating something like the database browser in FileMaker Pro but sadly real life/work commitments have forced me to put that on the back burner for now.


So rather than investing time and effort in building a general use database from scratch, why not invest time in something like this? I'm sure may would want to contribute, so do consider putting it on GitHub where others can share code and submit features/fixes?