[mythtv] State of play Re. DB support (MySQL versions, Postgresql etc.)
usleepless at gmail.com
usleepless at gmail.com
Sun May 14 08:53:00 UTC 2006
> 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
> 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.
> 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".
> 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):
> 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))
> if (!mutlirow.Flush())
> 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.
> | Colin Guthrie |
> | myth(at)colin.guthr.ie |
> | http://colin.guthr.ie/ |
> mythtv-dev mailing list
> mythtv-dev at mythtv.org
More information about the mythtv-dev