SQL script and SQLite

p-himik

I've noticed that SQLite have unpleasant limitation - you can't feed it with a script containing sequential CREATE TABLE statements (only the first statement will be executed). Is there any way to bypass it with Qt's means or do i have to pass to QSqlQuery::exec() separate statements?
I really don't want to parse my .sql file (another bicycle, don't need it) or create artificial separators of statements (trying not to rely on my memory).
Also i can't find any information of this limitation with Google.

[edit : fixed typo in title, eddy]

p-himik

Just did some digging into SQLite sources. ".read" command of sqlite3 parses all .sql file and feed to sqlite3_exec() separate statements.

p-himik

And QSQLiteResult doesn't seem to use sqlite3_exec(). As i can see from it's sources for exec(QString) is uses sequential calls for prepare(QString) and exec(). And former uses sqlite3_prepare16_v2() for queries. I'll try to code with SQLite's API and figure out what's wrong.

p-himik

And bingo! sqlite3_exec() can handle sequential CREATE TABLE statements. And sqlite3_prepare16_v2() as it's fifth argument takes a pointer to a pointer where it can store unprepared part of the query! And QSQLiteResult::prepare(const QString &query) just passes there null! Why? Is it a bug?
Sorry for slander of SQLite.

Offtopic: didn't ever think that rather intensive coding implies so much digging in someone else's code.

p-himik

Just did a "bugreport":https://bugreports.qt.nokia.com/browse/QTBUG-21884

goetz

I wouldn't call it a bug. The exec call made this way is compatible to the other Qt SQL drivers, which may not support this feature.

lgeyer

[quote author="p-himik" date="1317963986"]I really don't want to parse my .sql file (another bicycle, don't need it) or create artificial separators of statements (trying not to rely on my memory).[/quote]

In case you decide to do so it -is- can be as easy as
@
if(_database.tables().isEmpty())
{
QFile schemaFile(":/sql/schema.sql");

    schemaFile.open(QFile::ReadOnly&#41;;

    QStringList schemaTableList = QString(schemaFile.readAll()).split(";");
    foreach(const QString schemaTable, schemaTableList)
    {
        if(!schemaTable.trimmed().isEmpty())
        {
            _database.exec&#40;schemaTable&#41;;
        }
    }

    schemaFile.close(&#41;;
}

@

p-himik

Volker, than how can you call this feature? I'm not an expert in SQL so i'm just curios. And why doesn't QSqlDriver::DriverFeature include such feature? Also, QPSQL driver successfully executes query with multiple CREATE TABLE so i'm not so sure about compatibility.

Lukas, what about CREATE TRIGGER? It may include ";" inside statement.

goetz

Be aware that this solution only works if there is no semikolon (";") contained in your SQL workload. It will fail on the following SQL:

@
CREATE TABLE qdn1 (
id int default 0,
name varchar(20) default 'not given;' -- it fails on the semicolon of the default value!
);

CREATE TABLE qdn2 (
id int
);
@

goetz

I didn't look into the sources, but it may be that postgresql can handle this on the server side or does the parsing and splitting internally in its own libs.

lgeyer

Well, that's true. The snippet posted will not work in all cases and might require some adjustments in cases where not only simple CREATE TABLE statements are used.

p-himik

[quote author="Volker" date="1317979869"]I didn't look into the sources, but it may be that postgresql can handle this on the server side or does the parsing and splitting internally in its own libs.[/quote]

Yes, maybe it is so. But i don not understand why exec(QString) is made from prepare(QString) and exec(). As i can see from sources, the only reason for this is unwillingness to write similar code in exec(QString) and prepare(QString). Actually i can write exec(QString) with native SQLite's sqlite3_exec() which, as i mentioned above, can handle multiple statements in a single query.