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

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

    schemaFile.close();
}

@

[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.