Getting Started with a Database

[David Burgun]

Hi,

Thank you all so much! WIth this information (and all the other  
posts) I am just  bound to get something working for Monday!

On 15 Feb 2006, at 19:19, Trevor DeVore wrote:

> 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.
>

Great! I understand now! I couldn't figure out where CocoaMySQL came  
into it.


>> 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.
>

Absolutely FANTASTIC, exactly what I was hoping for! With this and a  
bit of playing I can get the demo up and running!

Once I have got this panic out of the way, I will spend some time and  
try to produce a "Getting Started" Stack that will build a sample  
database from Scratch and make it available to anyone that need to  
get started.

Thanks Again, IOU 6 beers!
All the Best
Dave