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
[EMAIL PROTECTED]
_______________________________________________
use-revolution mailing list
[email protected]
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-revolution
