Can not copy SQLite database to Postgresql



  • 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();
    }


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

Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.