Can not copy SQLite database to Postgresql

[bclay]

I was able to create an SQLite database using the QSql class and associated class and would like to be able to use that when off the LAN but I need to copy that to a Postgresql database when back on the LAN.
I have been able to access the SQLite database and find the columns and data in the SQLite DB and I can connect to the Postgresql database. I go through a series of steps to create the table in Postgresql from the SQLite DB and do not get any errors but the tables do not appear in Postgresql. I checked in the public schema since I can not find a way to create the schema in Postgresql. The SQLite DB has multiple tables and I want to keep them together in a schema.

The code I have tried follows.

QStringList tableList = mSqlDatabase.tables();

QStringList::iterator tableIter;
bool submitOk = false;

tableIter = tableList.begin();
while (tableIter != tableList.end())
{
QSqlRelationalTableModel *srcTableModel = new QSqlRelationalTableModel(this, mSqlDatabase);
QSqlRelationalTableModel *destTableModel = new QSqlRelationalTableModel(this, copyToDdatabase);

if ((srcTableModel != NULL) && (destTableModel != NULL))
{
	srcTableModel->setTable(*tableIter);
	srcTableModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
	srcTableModel->select();

	destTableModel->setTable(*tableIter);
	destTableModel->select();

	int srcColumns = srcTableModel->columnCount();
				
	QSqlRecord srcRecord = srcTableModel->record();
	QSqlRecord destRecord;

	destTableModel->database().transaction();

	for (int index = 0; index < srcColumns; index++)
	{
		QString fieldName = srcRecord.fieldName(index);
		QSqlField srcField = srcRecord.field(fieldName);

		QSqlField field = QSqlField(srcField);

		destRecord.append(field);
	}
	destTableModel->insertRecord(-1, destRecord);
	submitOk = destTableModel->database().commit();

	destTableModel->database().transaction();

	for (int index = 0; index < numSrcRows; index++)
	{
		QSqlRecord record = srcTableModel->record(index);
		QString fieldName;
		QVariant value;

		fieldName = record.fieldName(0);  // for debug only
		value = record.value(0);  // for debug only

		destTableModel->insertRecord(index, record);
	}
	
	submitOk =destTableModel->submit();

	submitOk = destTableModel->database().commit();
}

[panosk]

Hello,
I don't think it's that simple as you try to make it, at least if you want to include the data too.
Few years ago I had a similar problem and I wrote a function for that purpose. I'm copying-pasting the code from my old post after some reformatting to be readable. Although I have improved the code a bit since then in my own app, this should help you get started (please check the indentation, some fixes may be needed). This function doesn't transfer the schema, but I think it's easy to include it.

void DbManager::exportTables()
{
	QHash<QString,QStringList> tablesWithFields; //It holds the table name and its fields
	QStringList tables = sourceDb.tables();
	QSqlQuery query(sourceDb);
	foreach(const QString &table,tables) {
	    query.exec(QString("PRAGMA TABLE_INFO(%1)").arg(table));
	    QStringList fields;
	    while(query.next()) {
		  fields << query.value(1).toString();
	    }
	   tablesWithFields.insert(table,fields);
	}
	QFile f(QDir::homePath() + "/myDump.sql");
	f.open(QIODevice::Append | QIODevice::Text);
	QTextStream streamer(&f);

	//If constraints can't be dropped in the target database, some reordering of 
    //the INSERT statements may be needed
	QStringList sortedTables = tablesWithFields.keys();
	sortedTables.move(sorted.indexOf("table1"),0);
	...

	streamer << "BEGIN;\n";
	foreach(const QString &table,sortedTables) {
		if(table=="sqlite_sequence" /*|| table=="table4", etc*/) continue;
		QString statement = QString("INSERT INTO %1 VALUES('").arg(table);
		QStringList fields = tablesWithFields.value(table);
		QString fieldsString = fields.join(",");
		query.exec(QString("SELECT %1 FROM %2").arg(fieldsString).arg(table));
		if(!query.next()) continue;
		query.previous();
		while(query.next()) {
			for(int i=0; i < fields.size(); ++i) {
				QString value = query.value(i).toString();
				value.replace("'","''"); //Handle single quotes inside strings
				if(value.isEmpty()) {
					value = "NULL";
					statement.chop(1); //NULL should not appear inside quotes
					statement.append(value+",'");
				} else {
				statement.append(value+"','");
				}
			}
			statement.chop(2); //Remove comma and single quote from the end of value group
			statement.append("),('"); //Close the value group and start a new one
		}
		statement.chop(3);//Remove comma, opening parenthesis, single quote from the end
		streamer << statement << ";\n"; //Complete the INSERT statement
	}
	streamer << "COMMIT;";
	f.close();
}

And then batch execute the sql file like this:

...
exportTables();
QSqlQuery query(targetDb);
QFile f(QDir::homePath()+"/myDump.sql");
f.open(QIODevice::ReadOnly | QIODevice::Text);
if(!query.exec(f.readAll()))
		qCritical() << "Can't execute sql file: " << query.lastError().text();