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

[Colin Guthrie]

Hi,

Just a quick development question.

What is the state of play regarding database support?

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;

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

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


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.


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");

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?

Col.


-- 

+------------------------+
|     Colin Guthrie      |
+------------------------+
| myth(at)colin.guthr.ie |
| http://colin.guthr.ie/ |
+------------------------+