Architectural design with stacks and a database

[kunicki]

Ok, please forgive me. i am new to Revolution but not to programming. I have been learning now for 2 weeks and love what I am seeing. I am strugging a little though to map my "old" way of thinking to how I would do things in revolution. Given the following scenario:

+ SQLite database with 10 to 15 tables
+ Stack with about 10 to 15 cards allowing for editing data in database and reporting

In most languages we would create a database layer to manage the data access: connection, disconnection, query, execute, etc. I am trying to figure out where I would centrally store my database access functionality so that upon loadup of the stack the database is initialized and the entire stack (and all substacks and cards) can use it.

So the question comes down to what is the best way in Revolution to create shared data access layer? Where does the data access layer sit? (Part of the main stack, a substack to function like a library).

Thanks for any architectural guidance in advance!

K

[Mark]

Hi Katherine,

Welcome to Revolution. Great to see you here.

Revolution works very much the same as HyperCard, as you have surely noticed. So, you will understand that RunRev doesn't work with a data structure, which subsequently populated a table. Just like in HyperCard, all objects are also containers. You get the data out of the SQLite database and put it into a variable or field. When you're done, you put the data from the obects and variables back into the database.

Usually, when I make a front-end for a database, I make sure that all data are immediately saved, e.g. by catching the closeField messsage.

Does this answer your questions?

Best,

Mark

[trevordevore]

I typically put the database layer in a library script. A library script is just a stack script that is available to all objects in the message path. This means all of the code in your various cards could access the db layer commands/functions.

Look up 'start using' in the dictionary for more information. 'start using' will make the database library available at which point you can initialize the database.

[kunicki]

Usually, when I make a front-end for a database, I make sure that all data are immediately saved, e.g. by catching the closeField messsage.

Does this answer your questions?

Mark, I appreciate this insight, thanks! Addditionally,do you centralize the SQLite database access in a library and then all forms utilize that library?

Tnx!

[kunicki]

I typically put the database layer in a library script. A library script is just a stack script that is available to all objects in the message path. This means all of the code in your various cards could access the db layer commands/functions.

Look up 'start using' in the dictionary for more information. 'start using' will make the database library available at which point you can initialize the database.

Thanks for this advice. I will do that. Can I ask how you organize your library? Manage connections, make sql calls, inserts, etc?

Tnx!

[trevordevore]

For all the connection management, SQL calls, etc. I use SQL Yoga but I'm a *tad* biased

What I've done in the past is keep all database calls within a single script. I handle connecting/disconnecting within the script and store the database connection id(s) in a script local variable. I then write functions/commands for interfacing with different parts of the database, whether it be retrieving, creating, updating or deleting records. Examples -

Code: Select all

command lesson.create pLessonInfoA
    -- validate, insert, etc.
end lesson.create

function lesson.Search pSearchCriteria
    -- perform search and return results in array
end lesson.Search

[Janschenkel]

At the RunRevLive'09 conference, I did a presentation on using SQLite for desktop applications. The example application uses multiple stacks with datagrids and other controls, as well as some basic reporting mechanisms. You can download the slides and example code from the downloads section of my website: http://www.quartam.com/downloads.html (it's the Ticket Example Application link near the bottom)

HTH,

Jan Schenkel.

[Mark]

Hi Catherine,

I think that the answer depends on the complexity and size of your project.

Most of my database-related projects are fairly simple. Writing a library for them would be more work than talking to the database directly. Also, it doesn't make sense to include a third-party library of several thousands lines of code, if your actual project needs only 100 lines.

In a few cases, I noticed that I was using a lot of repetetive code and I simply copied this code to a library stack and tweaked it a little to make it generally applicable.

If you're completely unfamiliar with MySQL or SQlite, I could understand that you might want to use a special tool as a layer between your interface and your database. Make sure that you get the opportunity to test the tool extensively, before you buy it. Otherwise, you might get into trouble when the project is almost finished.

Best,

Mark