[mythtv] State of play Re. DB support (MySQL versions, Postgresql etc.)

[usleepless at gmail.com]

Hi,

> Hi,
>
> Just a quick development question.
>
> What is the state of play regarding database support?

as far as i know, mysql only. the codebase is literally covered with
mysql specifics.

> I know mythweb is pretty much mysql only right now (any plans there
> Chris?), but I have a more general question regarding some the the SQL
> constructs we can use.
>
> 1)
> Is this syntax OK to use?
> INSERT INTO mytable SET column=value, othercolumn=value;

no, mysql specific, it's a syntax-mix off INSERT and UPDATE statement.

> Or is this preferred?
> INSERT INTO mytable(column,othercolumn) VALUES(value,value);

yes, SQL92 ( or whatever )

> The previous is MySQL specific I think (?), although I do see it in the
> code in places.

no, the insert into (...) values (....) is "standard".

> 2)
> LAST_INSERT_ID()/MSqlQuery::lastInsertId()
> Is the above OK to use? I've seen various workarounds in code to get the
> ID back out, but not sure if this is just older techniques or not.

obtaining the last assigned "autonumber", "serial" or "sequence" is
db-specific anyway. hiding the implementation in the MSqlQuery-class
seems to be the most appropiate.

> 3) Extended insert syntax.
> Not sure if this would be useful or not, but in another project, I have
> the need to insert several (hundred) rows into a MySQL table. Using
> MySQL's extended insert syntax seriously speeds up this operation. I
> created a wrapper (for use in PHP but easyily implemented in C++ too)
> that allows you to easily create a multirow insert operation (provided
> you don't need the auto_increment values):
>
> e.g.
>
> <pseudo>
> MSqlMutliRowInsert multirow("INSERT INTO mytable (col1, col2) VALUES");
>
> for (int i=0; i<10; i++)
> {
>    MSqlMultiRowInsertRow row("(:VAL1, :VAL2)");
>    row.bindValue(":VAL1", "a"+i);
>    row.bindValue(":VAL2", "b"+i);
>    if (!mutlirow.AddRow(row))
>    {
>      //Error
>    }
> }
>
> if (!mutlirow.Flush())
> {
>    //Error
> }
> </pseudo>
>
>
> This will create queries like:
> INSERT INTO mytable (col1, col2) VALUES ("a0","b0"), ("a1","b1"),
> ("a2","c2"), .... ("a9","b9");

this "extended" syntax seems to be mysql specific. that's what
"extended" suggests as well.

> As the query can get really, really long, it may be that a given AddRow
> call will decide to do a Flush with the data collected so far (hense why
> you need to check it's return value for errors);
>
> If a given database does not support multirow inserts, the wrapper can
> be aware of this and flush on each AddRow call.
>
> Nice? Do you want me to implement? Would it be useful in some areas of
> the code which do intensive inserts? Or is this kinda pointless?

i don't know where the source data is coming from, but if it is coming
from the same database, this construct might work better and faster:

 INSERT INTO mytable(col1,col2) SELECT col1, col2 FROM .......

if mythtv wants crossdatabase support, a lot of things need to be done:
1. only use std-sql
- SELECT ... FROM ....
- INSERT INTO table(col....) VALUES() or INSERT INTO table(col...)
SELECT ... FROM ...
- DELETE FROM table WHERE x=y
- UPDATE table SET field = val, field2 = val2 WHERE x = y

mysql has non-std UPDATE variants, non-std INSERT variants, a non-std
"REPLACE" keyword. these are all used in mythtv and need to be
converted to std-sql.

2. get rid of all query.size() calls. check the qt-docs: not
crossdatabase compatible. as far as i know most of these calls are
easily removed, since they just seem to be checks and are followed by
"while(query.next())" statements most of the time. if query.size() ==
0, query.next() will return false anyway.

3. the db-schema is a mess: reform to include only a handfull of
datatypes. get rid of INT(11), INT(2), tinyint etc....

4. regarding mysql-specific (date) functions: these might be handled
in the target-database: in postgresql for example you can define your
own functions, and there is a mysql-function-compatibility-module. i
don't know if this exists for mssql for example.

if anybody wonders if this is a rant: i am running my mythtv-0.18.2 on
freebsd, and have converted mythtv to run on postgresql. i did this
for 2 reasons, a postgresql installation was already running on the
target machine and i dislike mysql.

but it was a hell of a job.

i for one hope mythtv obtains multiple database support in the future
so i can run newer versions of mythtv.

regards,

usleep

>
> Col.
>
>
> --
>
> +------------------------+
> |     Colin Guthrie      |
> +------------------------+
> | myth(at)colin.guthr.ie |
> | http://colin.guthr.ie/ |
> +------------------------+
> _______________________________________________
> mythtv-dev mailing list
> mythtv-dev at mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-dev
>