data-design question

[Trevor DeVore]

On Sep 27, 2005, at 3:44 AM, Alex Tweedly wrote:

> [NB - I'd use Trevor's libDatabase along with either 1, 2 or 3 -  
> looks really nice, esp. for someone who isn't an SQL expert. And it  
> does work with PostgreSQL, even thought there are a few places  
> where it only mentions MySQL.]

There are a couple of areas that you may experience problems with  
PostgreSQL:

1) Quoting strings.  libDatabase automatically quotes strings for you  
when updating/adding records.  I haven't looked up what characters  
need to be escaped for PostgreSQL so right now PostgreSQL uses the  
MySQL escape routine.

2) Exporting from PostgreSQL to another DB.  I haven't looked at all  
of the data types form PostgreSQL so some of the field type mappings  
might be off.  This would only be an issue if you were developing in  
PostgreSQL and deploying to SQLite, Valentia, etc.

I think that is about it.  I'm happy to correct these things if  
anyone who uses PostgreSQL wants to provide the details or point me  
to the proper place in their docs.

> Too many choices .....
> I've been meaning to write a real SQL app some day, so you've  
> triggered me into trying it. I'm trying a combination of 2 and 5,  
> partly to get a better idea of how "portable" SQL should be between  
> PostgreSQL and SQLite.

I'd imagine you shouldn't have too many problems with SQL depending  
on how complex your queries get.  One problem I ran into with MySQL  
and SQLite was that if you try to access columns by name given this  
query:

SELECT t1.ID, t2.Title FROM table1 t1, table2 t2 WHERE t1.LinkID = t2.ID

The MySQL driver would return them as "ID" and "Title" (This is the  
behavior in Revolution as well as PHP).  With the altSQLite driver  
the columns are named "t1.ID" and "t2.Title".  To get around that you  
have to do this:

SELECT t1.ID as ID, t2.Title as Title FROM table1 t1, table2 t2 WHERE  
t1.LinkID = t2.ID

When getting started, the thing to watch out for is how you handle  
fields for uniquely identifying records.  Each database vendor tends  
to have their own method for creating unique ids for records in  
tables.  If you rely on a vendor specific method then when you port  
you will be sad.  Best to right your own routine for getting unique  
ids for new records in tables.

libDatabase has a simple method built into it that you can use to get  
started.  It just uses one table that stores the names of all of the  
other tables along with the next id.  When you use  
libdb_addNextToTable then the library will handle getting the next  
available id.  The getting started doc explains this.

There are other issues of course, but that can be a big kink in your  
port.


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