Getting Started with a Database

[Trevor DeVore]

On Feb 15, 2006, at 7:58 AM, David Burgun wrote:
> Ok, that's great, but I don't have any database to import, I want  
> to create a new one from scratch. Is there anyway to do this using  
> libDatabase (with either Version 2 or 1) ?

libDatabase has the functions necessary to execute the SQL commands  
necessary to create a database.  You would use libdb_executeSQL with  
something like this:

get lidb_executeSQL("DatabaseIdentifier", "CREATE TABLE myobjects (ID  
int(11) NOT NULL auto_increment, Name varchar(255) NOT NULL, Type char 
(4) NOT NULL, Size int(11), ImageFilePathName varchar(255) NOT NULL,  
PRIMARY KEY(ID))")

Usually it is easier to create your tables with CocoaMySQL though,  
then just use libDB to connect to the database.  For one table it  
isn't a big deal but the graphic interface helps you know what  
options are available when creating columns.

> Ok, I understand now, thanks. One question, will libDatabase 1.x  
> work with the version "MySQL" that I have already installed?

Yes.  libDB just sits on top of RevDB and the Valentina externals  
(1.x and 2.x).

> I tried that already! They like what they see as implemented as a  
> Card Database. An SQL database is the main feature of the app and  
> something that I said (maybe by mistake) would be no problem under  
> RunRev. The rest of the app I coded really quickly and have it  
> working just fine, now all they want is to see it working with an  
> SQL database. They will use the SQL database with other  
> applications, not just the one I am working on.

Okay, let's see if we can get this working then.

> All my Stack needs to do is:
>
> 1.  Create the Database.
> I have a list of image files in a folder, the name field is the  
> name of the file, the Type and Size fields are determined by  
> looking up the name in a text file and the path is just the local  
> path to the image file. This operation can (and will be slow) but  
> this is acceptable since it will only be performed once.

You can separate creating and populating the database into two  
different tasks if you would like.  You can create the database with  
something similar to the code I posted above or with CocoaMySQL.

You would then use libDB to populate the database.  So perhaps you  
could do something like this (libDB version 2 syntax):

--> REGISTER CONNECTION SETTINGS
put "mysql"     into tDBA["Type"]
put "localhost" into tDBA["Host"]
put "myobjects" into tDBA["Name"]
put "root"      into tDBA["Username"]
put ""          into tDBA["Password"]

libdb_registerDatabase "mydb", tDBA

try
   --> TO DO: CREATE LIST OF IMAGES.  tImages CONTAINS VALUES
   repeat for each line tImage in tImages
     --> TO DO: LOOKUP IMAGE TYPE AND SIZE. tType AND tSize CONTAIN  
VALUES
     put tImage       into tAddA["Name"]
     put tType        into tAddA["Type"]
     put tSize        into tAddA["Size"]
     put tPathToImageFolder & slash & tImage into tAddA 
["ImageFilePathName"]

     --> ADD TO DATABASE
     put libdb_addToTable("mydb", "myobjects", tAddA, "ID") into  
tAffectedRows
   end repeat
catch e
   answer "A database error occurred:" && e
end try

> 2.  Retrieve from the Database.
> The user enters a name, the record with this name is returned.

--> CREATE THE QUERY.  THIS IS A BASIC "CONTAINS" QUERY.
local tDataA

try
   put "SELECT * FROM myobjects WHERE Name LIKE '%" & tSearchString &  
"%'" into tSQL

   --> USE LIBDB FUNCTION FOR CONVERTING A QUERY TO AN ARRAY.  FOR  
BETTER PERFORMANCE, YOU CAN USE
   --> LIBDB CURSOR FUNCTIONS LATER ON IF YOU WANT.
   get libdb_getTableRecords("mydb", tDataA, tSQL)

   --> tDataA IS NOT AN ARRAY CONTAINING ALL RETURNED RECORDS.  THE  
"Length" KEY CONTAINS THE NUMBER
   --> OF RECORDS RETURNED.
   --> POPULATE FIELD WITH RESULTS.
   repeat with i = 1 to tDataA["Length"]
     put tDataA["Data",i,"Title"] & tab & tDataA["Data",i,"ID"] & cr  
after tResults
   end repeat
   delete the last char of tResults

   set the text of field "MyResults" to tResults
catch e
   answer "A database error occurred:" && e
end try

> Is this really a massive learning curve? I have until Monday to get  
> this and some other purely RunRev stuff working, surely that's  
> enough time to do this?

Well, this should get you started.  A list of things you will want to  
read up on eventually:

1) SQL syntax for creating databases and tables, altering tables,  
updating records, optimizing queries, etc.
2) Dealing with drivers.  It is good to understand what drivers you  
are distributing and why.  MySQL has the revdb files in addition to a  
mysql driver.  altSQLite just has the revdb driver.  Valentina is a  
separate external (I recommend against using Valentina with revDB).
3) Performance tuning.  Proper design and indexing of databases can  
really affect performance in some cases.  It is good to study up on  
these concepts.

There are probably other things as well but that is a good start.

-- 
Trevor DeVore
Blue Mango Multimedia
trevor at mangomultimedia.com