LiveCode Forums.

Hi,

I've just spent a long time trying to work out what Livecode's native database capability is, and I struggled to find an answer. Most of the promotional literature refers to the ability to connect with external database servers. I wanted to find out whether a relatively complex relational database could be embedded in, or stored with, a standalone livecode application.

I've eventually discovered that the inbuilt revDB database driver allows SQLite databases to be used by a standalone executable, although the .db file will need to accompany the .exe file. This information was 'hidden' in the SQLite sampler app, which is invisible to Google. Here's the front page text:

"dbSQLite is a revDB database driver which allows you to build and access SQLlite databases from within Revolution and your standalone. dbSQLite currently runs on Mac OS X and Windows XP. SQLlite is a great single-user database, and is perfect for creating desktop database applications which don't need any internet connectivity. SQLlite has many features:
1. Zero-configuration - no setup or administration needed!
2. It is very fast and compact. Supports databases up to 2 terabytes in size.
3. It is SQL92 compliant, so you can migrate to MySQL servers with a simple connection string change!
4. The complete database is stored in a single file which can be freely shared between multiple OS'es."

This is great news, I just wish I could have found it earlier! I've posted this so that hopefully I can save other new users some time.

However, I'm still unaware of what kind of database capability can be natively achieved within Livecode. Is it restricted to 2D tables, or are relational databases easy to construct through some clever use of cross-refernced arrays?! It would be convenient to be able to distribute a standalone application with the database contained in the single executable file.

Hi Tom,

I am no database expert, but I am sure that you can achieve as much with LiveCode
as the databe engine you use and your capabilities of SQL will allow


Best

Klaus

Hi Tom,

Embedded would give you ploblems with Vista and Win 7 which are not happy about modifications to files within the programs folder. I've used SQLite quite a lot and what you can de is really only limited to your SQL knowledge. Have a look at the SQLite site: http://www.sqlite.org/lang.html

Tom,

You should set up another folder outside away from of the Windows Program Files folder, for your project. Then you just put all your project files there.

Also, if you're going to be working with SQLite, you should pick up the FREE database manager: SQLiteMan. Just do a search for that name and you'll find it.

I can't post links-- I'm too new here. It's a big help when creating and managing database tables.

Tom4c wrote:However, I'm still unaware of what kind of database capability can be natively achieved within Livecode. Is it restricted to 2D tables, or are relational databases easy to construct through some clever use of cross-refernced arrays?!

You can write almost anything in any Turing-complete language. The question is: would you want to?

It's hard enough writing a good indexing scheme for large amounts of flat data; adding the complexity of relationality to that is not for the faint of heart.

If you need an RMDBS you will almost always have a higher ROI with an off-the-shelf solution. With LiveCode, the existing DB externals are the embedded solution.

I may be wrong, but I believe SQLite is not a fully relational database. It's very strong on keys, even multi-segmented keys, but I don't think you can do SQL Join commands.

For full relational capability you'll need to use mySQL or Postgres. And if you decide to to that way, it looks like your work can been greatly simplified, when you purchase the additional "SQL Yoga" plugin, found in the RunRev store.

The other SQL options are good if you need 'em, but using postGreSQL as an embedded solution seems a bit daunting, and the licensing fees for MySQL make it a non-starter for many projects.

With SQLite you can do some joins, just not all forms - a summary of the SQL it doesn't support is here:
http://www.sqlite.org/omitted.html

The more I learn about CouchDB and MongoDB, the more I tend to think outside of joins, so my opinions tend perhaps unfairly toward noSQL solutions.

That said, if the OP was considering writing his own DB subsystem, my hunch is that very little in the way of relationality is needed for that project. Though of course I could just as easily be wrong, since I don't know what type of data is being managed or what he wants to do with it.