sql/xml comparisons

[churchill]

Having been researching in the wiki and in the forum, I've noticed a couple of threads that indicate problems with SQL Lite, namely the ExecuteSQLQuery bug, mentioned here: viewtopic.php?f=95&t=23018

That's a pretty big obstacle in the way of doing some things, as some of the ideas I've got would probably require (well, not so much require, more best practice) multiple tables, and using inner joins to link IDs in one table to a primary key in another table.

for example, not that I'm actually doing this in my idea, I'm just using the structure as an example:

Table 1: PickupLocations

PickupID INTEGER PRIMARY KEY

X INTEGER

Y INTEGER

Z INTEGER

PickupTypeID INTEGER

Table 2: PickupTypes

TypeID INTEGER PRIMARY KEY

TypeDescription TEXT

so that you can do a query like:

"SELECT X, Y, Z, PT.TypeDescription FROM PickupLocations PL INNER JOIN ON PL.PickupTypeID = PT.TypeID"

Without ExecuteSQLQuery, doing an inner join like this would be pretty much impossible, wouldn't it? So, I now seem to have 3 options, and would like advice, as usual!

1) Avoid best practice, and have the descrption in the Pickup Locations table, thus reducing the need for inner joins, and be able to use executeSQLSelect instead, but potentially causing more problems with inconsistent naming, etc.

2) is XML flexible enough to do something like that? how difficult would it be to return a result like the one in the sql query, if the data is stored in two different xml files? or do I avoid the idea of "best practice", and do what I suggested above, but stored in XML instead of SQL?

3) Is one of these modules the answer? http://development.mtasa.com/index.php? ... /MTA-MySQL or http://development.mtasa.com/index.php? ... ules/MySQL ?

I'm still convinced that SQL is preferred when talking about the potential for a fair bit of data manipulation, whereas XML is best for reading more static content, but would love to know if there are any figures that prove one way or another whether SQL or XML is best suited for a particular use.