Making the move...

Kay C Lan lan.kc.macmail at gmail.com
Sun Mar 12 02:56:14 EST 2006


My qualifications to answer this question is based on the fact that I have
no formal programing experience, I script for a hobby, and use MySQL because
it is in my price range (free). I run OSX.

On 3/10/06, Mvreade <mvreade at earthlink.net> wrote:

> For users who have used Rev for databases, I was wondering what it's like
> to develop a system with, say, 20 tables and lots or relationships and
> methods?


My biggest project runs 2 DBs with a total of 16 tables with no problems.
One thing to get your head around is that you are really considering two
different things here:

1) The DB engine. If it can handle it, then you can create it; a DB with 20
tables is only scratching the surface of MySQL potential. If you can create
the query that joins the tables that extracts the data then you're OK, if
you can't, REV isn't going to help.

2)) REV as the GUI. If you can visualize how you want your data presented,
then you can build it with REV. I particularly like to use Master Stack ->
Sub Stack with Table -> joined Table.

There is really only two tricky bits with marrying Rev to DB:

1) Building and formatting the query correctly. To make life easy for me I
do all the 'dry runs' with CocoaMySQL (also free) until I know I have the
correct query structure before determining how to build that query in Rev
using buttons and fields. One reasons for this is getting the quotes right.

In CocoaMySQL (or Terminal)
SELECT * FROM products WHERE price = "9.99"

In a REV script
"SELECT * FROM products WHERE price = " & quote & "9.99" & quote

For complex queries if I try to do it straight in REV I invariable end up
with errors because of incorrect quoting, but if I create a correct query in
CocoaMySQL I can copy it and use REV's neat inbuilt 'Copy as Formatted
String' item in the Edit menu which will take the first line and turn it
into the second line. Easy!

2) Taking the data returned from a query and placing it in appropriate
fields and buttons. This is a little more complex and certainly more time
consuming, and worthy of an entire tutorial. Having said that though, once
you have figured out how to do it for one button or field, the rest will be
more of the same. The only real decision is whether the query will return a
cursor position, or the data itself.


> Where would one store all the methods?


I assume by methods you are referring to what xTalkers would call 'handlers'
To start with you'd probably store them in the most logical place, the
button, field or menu (which are in fact buttons) that corresponds with what
you want to achieve.

You may have three buttons, 'Name', 'Supplier','Price'

Then you could have something like this:

Button 'Name'
 on mouseUp
  put revDataFromQuery(,,currentDB,,"SELECT * FROM product ORDER BY Name"
into tMyDbData
  -- more script to place data in fields
 end mouseUp

Button 'Supplier'
 on mouseUp
  put revDataFromQuery(,,currentDB,,"SELECT * FROM product ORDER BY
Supplier" into tMyDbData
  -- more script to place data in fields
 end mouseUp

Button 'Price'
 on mouseUp
  put revDataFromQuery(,,currentDB,,"SELECT * FROM product ORDER by Price"
into tMyDbData
  -- more script to place data in fields
 end mouseUp

But, about 5 min after you complete your first prototype you'll realise a
better way you could have done things, like;

IN THE STACK SCRIPT, create a custom handler,

on hMyHandlerToSetTheSortOrder pMyButtonPressed
  put revDataFromQuery(,,currentDB,,"SELECT * FROM product ORDER by " &
pMyButtonPressed into tMyDbData
  -- more script to place the data in the desired fields, buttons etc
end hMyHandlerToSetTheSortOrder

Then in each button you'd have the same script;

on mouseUp
-- the short name of me will send the name of the button pressed
 hMyHandlerToSetTheSortOrder the short name of me
end mouseUp

For relationships, would one just keep a VERYlong list of Database Queries?


Yes you could do it that way, a custom property would be an ideal location.
If the queries are 'static' then that would be relatively easy, or as with
the example above, if a button only does one thing then the query would be
inbuilt into that script.

With my DBs I have numerous Option Buttons, some with 10's of options,
others with over a 100 options, plus check boxs and a field where I can
enter a number between 1 - 100. Overall the combinations must easily run
into the 100,000s if not the 1,000,000s. I build the final query on the fly
and activate it using a separate 'Query' button.

In summary, I find REV and MySQL (with CocoaSQL) such an easy combination to
work with that I regularly use it where it's really probably a bit of
overkill. Bit like driving the Suburban down to the local shops to pick up a
bottle of milk when riding my bike would have been better for me, the
environment and quicker considering Sunday morning traffic;-)

HTH



More information about the use-livecode mailing list