Page 1 of 1
Architectural design with stacks and a database
Posted: Tue Mar 16, 2010 10:05 pm
by 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
Re: Architectural design with stacks and a database
Posted: Wed Mar 17, 2010 1:07 am
by 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
Re: Architectural design with stacks and a database
Posted: Wed Mar 17, 2010 5:55 pm
by 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.
Re: Architectural design with stacks and a database
Posted: Thu Mar 18, 2010 6:57 pm
by kunicki
Mark wrote:
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!
Re: Architectural design with stacks and a database
Posted: Thu Mar 18, 2010 6:58 pm
by kunicki
trevordevore wrote: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!
Re: Architectural design with stacks and a database
Posted: Thu Mar 18, 2010 7:31 pm
by 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
Re: Architectural design with stacks and a database
Posted: Sat Mar 20, 2010 1:57 pm
by 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.
Re: Architectural design with stacks and a database
Posted: Sat Mar 20, 2010 4:14 pm
by 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