QSqlQueryModel + set Write

[raphaelf]

Hello everybody,

How can i set a model (QSqlQueryModel) as write only. The model is read-only by default.


thanks

QSqlQueryModel *model = new QSqlQueryModel;
        model->setQuery(test);
        ui.tableView->setModel(model);
        ui.tableView->show();

QSqlQueryModel *model = new QSqlQueryModel;
        model->setQuery(test);
        ui.tableView->setModel(model);
        ui.tableView->show();

To copy to clipboard, switch view to plain text mode 

[jpn]

From docs:

The model is read-only by default. To make it read-write, you must subclass it and reimplement setData() and flags(). Another option is to use QSqlTableModel, which provides a read-write model based on a single database table.

[raphaelf]

hi jpn..
I am using QSqlTableModel too, and it works fine. But i would like to change values from a complex query. And i have not found a example how to subclass it
Should be possible to change values from a query with QSqlQueryModel

use QSqlTableModel, which provides a read-write model based on a single database table.

[patrik08]

Hello everybody,
How can i set a model (QSqlQueryModel) as write only. The model is read-only by default.

thanks

QSqlQueryModel *model = new QSqlQueryModel;
        model->setQuery(test);
        ui.tableView->setModel(model);
        ui.tableView->show();

QSqlQueryModel *model = new QSqlQueryModel;
        model->setQuery(test);
        ui.tableView->setModel(model);
        ui.tableView->show();

To copy to clipboard, switch view to plain text mode 

IMO ...
Same I do not have wasted time to study the model of sql qt ... but they are passed to the heart of sql and I have made a porting qt3 => qt4 from the super sql class from http://sourceforge.net/projects/sqlitebrowser/ the porting work is a week-end ....
the study of model is longer....
Have a look on http://sourceforge.net/forum/forum.p...orum_id=301849 now i undestand / learning sql heart and can display data on table forms tree ecc.... and i have a StaticLibs to not depend on dll or plug-in....

[raphaelf]

Hi Patrik08,
Thanks for your post, but i would like to know if it is possible to set QSqlQueryModel to write Mode. The Docs say that is possible by Subclassing it, but i have not much experience on qt.
So have somebody experience with QsqlQueryModel and write Mode..

[jpn]

The Docs say that is possible by Subclassing it, but i have not much experience on qt.

No offense but subclassing doesn't require any Qt experience.. only a bit of C++ knowledge.

[patrik08]

offense no .... my projekt http://sourceforge.net/projects/qtexcel-xslt/ work on the default qt4 sql model looks so... and put table on tabwidged... 1 or moore...

But i not like long wrapper ... my goal is to make raod fast and simple from db to table...
often i show db in a form .. not on table method ... whit method...

textList= this->findChildren<QLineEdit *>();
boxList= this->findChildren<QComboBox *>();

http://www.qtforum.de/forum/viewtopic.php?t=2192

#include "include_table.h"
 
 
Include_Table::Include_Table( QWidget* parent )
	: QWidget( parent )
{
	setupUi( this );
    noloop = 100;
    connect(insert_row, SIGNAL(clicked()), this , SLOT(AppendRow()));
    connect(create_index_button, SIGNAL(clicked()), this , SLOT(CreateIndexTable()));
 
}
 
/* line_table_name->setText( tablesql );  */
void Include_Table::AppendRow()
{
    QSqlQuery query;
    QString rowone = "NULL";
    QString line;
    int loop;
    int loopnewroow;
    bool ok = false;
    int appendrow = QInputDialog::getInteger(this, tr("New row append on table") + " "+tablename,
                                         tr("Select a nummer:"), 1, 1, 25, 1, &ok);
    if (ok) {
    for(loopnewroow=0; loopnewroow < appendrow; loopnewroow++) { 
 
                 for(loop=0; loop < summ_cools; loop++) {  
                      if (loop !=summ_cools-1) {
                        line = line+QString( "%1," ).arg( rowone ); /* construct query */
                      } else {
                       line = line+rowone;    /* construct query */
                      } 
                  }
 
    line.prepend("INSERT INTO "+tablename+" VALUES (");  /* construct query */
    line.append(")"); /* construct query */
    query.exec("BEGIN TRANSACTION");
    query.exec(line);
    query.exec("COMMIT");
    loop = 0; /* reset  */
    line = ""; /* reset  */
    }
    }
    RefillTable( tablename );  
}
void Include_Table::RefillTable( QString tablesql )
{
        noloop = 10;
        structuresql = GetStructureTable(tablesql);
        bool have_index = structuresql.contains("integer primary key", Qt::CaseInsensitive);
        tablename = tablesql; /*  save on class !*/
        QSqlQuery query;
        /* check if as INTEGER PRIMARY KEY */
 
        sqlmodel = new QSqlTableModel();
        sqlmodel->setTable( tablesql );
        sqlmodel->setEditStrategy(QSqlTableModel::OnRowChange);
        sqlmodel->select();
        tabella_core->setModel(sqlmodel);
        tabella_core->setItemDelegate(new QSqlRelationalDelegate(tabella_core));
        horizontalheader = new QHeaderView(Qt::Horizontal,this);
        horizontalheader = tabella_core->horizontalHeader();
        connect (horizontalheader,SIGNAL(sectionClicked(int)),this,SLOT(sortColumn(int)));
        if (have_index) {
        vertikalheader = new QHeaderView(Qt::Vertical,this);
        vertikalheader = tabella_core->verticalHeader();
        connect (vertikalheader,SIGNAL( sectionDoubleClicked(int)),this,SLOT(VertikalRow(int)));
        }
        /*  discovery total row/cools  register on class ..... start */
        summ_rows = sqlmodel->rowCount();  /*  save on class !*/
        summ_cools = sqlmodel->columnCount(); /*  save on class !*/
        /*  discovery total row/cools ..... end  */
        if (have_index) {
        insert_row->setEnabled(true);
        create_index_button->setEnabled(false);
        /* debugline->setText( QString( "%1 index ok" ).arg( int2char( summ_cools )  ) ); */
        row_total->display( QString( "%1" ).arg( int2char( summ_rows ) ) );   
        } else {
        insert_row->setEnabled(false);
        create_index_button->setEnabled(true);
        /* debugline->setText( QString( "%1 noindex" ).arg( int2char( summ_cools ) ) ); */
        row_total->display( QString( "%1" ).arg( int2char( summ_rows ) ) );  
        }            
}
 
 
void Include_Table::sortColumn(int i)
{
  if (i > 0) {
   sqlmodel->sort(i,Qt::AscendingOrder);
   sqlmodel->sort(i,Qt::AscendingOrder);
   }
}
 
void Include_Table::VertikalRow(int i)
{   /* Header QMessageBox *msgb; */
 
    sqlmodel->revertRow(i); /* not save or update nothing on this row! undo! */
    noloop++;
    if (noloop == 11) {
    noloop = 0;
    QString msgDB =tr("<p>Make a selection ..  </p><p>You Confirm to remove row:</p><p>Click Ok (remove) button or press Esc.</p>");
    int reply = msgb->question(this, tr("Confirm to Delete this row?")+QString( " row nr.%1" ).arg( int2char( i ) ),
                                      msgDB,
                                      msgb->Ok,
                                      msgb->No);
        if (reply == 1 and reply !=0x200)  {  /* 0x200 = ESC doc! */
                bool remove_confirm = sqlmodel->removeRows(i,1);
                if (!remove_confirm ) {
                msgb->information( this , tr("Error delete row!"),tr("Sqlite 3 cant remove this row ")+QString( "%1" ).arg( int2char( i ) )); 
                }  
        }
    }
noloop = 100;
RefillTable( tablename );  /*  set noloop to 10 here ...*/
RefillTable( tablename );  /*  set noloop to 10 here ...*/
}
 
 
 
void Include_Table::CreateIndexTable()
{
/*  structuresql = GetStructureTable(tablesql); */
QString only_name_one; 
QString only_name_all;
QString tmp_table_end = "CREATE TABLE "+tablename; 
int numer;
QString struct_table_original = structuresql;
QString      table_para = structuresql.replace(tmp_table_end, QString(""));
int          resize = table_para.lastIndexOf(")");
             table_para = table_para.left(resize);  /* remove () */
             table_para = table_para.right(resize - 2); /* remove () and space */
 
QString sendtolist = table_para;
        sendtolist.replace(QString(" "), QString("#"));
QStringList  allnamelist = sendtolist.split(",");
 
    for (int i = 0; i < allnamelist.size(); ++i) {
        only_name_one = XML_utf8(QString( allnamelist.at(i) ));
        numer = only_name_one.lastIndexOf("#");
        QString trimm_str = only_name_one.left(numer);
        numer = trimm_str.lastIndexOf("#");
        trimm_str.left(numer);
        trimm_str.replace(QString("#"), QString(""));
                  if (i !=allnamelist.size() - 1) {
                    only_name_all = only_name_all+QString( "%1," ).arg( trimm_str ); 
                  } else {
                   only_name_all = only_name_all+QString( "%1" ).arg( trimm_str );   
                  }
         numer =0;
         only_name_one ="";
         trimm_str = "";
    }
 
QStringList prepare_query = ( QStringList()
                             << "CREATE TEMPORARY TABLE TEMP_TABLE ("+only_name_all+")"
                             << "INSERT INTO TEMP_TABLE SELECT "+only_name_all+" FROM "+tablename
                             << "DROP TABLE "+tablename
                             << "CREATE TABLE "+tablename+" (ID INTEGER PRIMARY KEY,"+table_para+")"
                             << "INSERT INTO "+tablename+"("+only_name_all+") SELECT "+only_name_all+" FROM TEMP_TABLE"
                             << "DROP TABLE TEMP_TABLE" );
QSqlQuery query;
                 for (int i = 0; i < prepare_query.size(); ++i) {
                    query.exec("BEGIN TRANSACTION");
                    query.exec(QString( prepare_query.at(i) ));
                    query.exec("COMMIT");
                 }
/* reload qtableview*/
RefillTable( tablename );               
}
 
/*
 result to have a new cool  index (id INTEGER PRIMARY KEY) and not restart db!
CREATE TEMPORARY TABLE TEMP_TABLE(Male, Sente);
INSERT INTO TEMP_TABLE SELECT Male, Sente FROM Mustermann;
DROP TABLE Mustermann;
CREATE TABLE Mustermann (id INTEGER PRIMARY KEY, Male TEXT, Sente TEXT);
INSERT INTO Mustermann(Male, Sente) SELECT Male, Sente FROM TEMP_TABLE;
DROP TABLE TEMP_TABLE;
*/

#include "include_table.h"


Include_Table::Include_Table( QWidget* parent )
	: QWidget( parent )
{
	setupUi( this );
    noloop = 100;
    connect(insert_row, SIGNAL(clicked()), this , SLOT(AppendRow()));
    connect(create_index_button, SIGNAL(clicked()), this , SLOT(CreateIndexTable()));
    
}

/* line_table_name->setText( tablesql );  */
void Include_Table::AppendRow()
{
    QSqlQuery query;
    QString rowone = "NULL";
    QString line;
    int loop;
    int loopnewroow;
    bool ok = false;
    int appendrow = QInputDialog::getInteger(this, tr("New row append on table") + " "+tablename,
                                         tr("Select a nummer:"), 1, 1, 25, 1, &ok);
    if (ok) {
    for(loopnewroow=0; loopnewroow < appendrow; loopnewroow++) { 
    
                 for(loop=0; loop < summ_cools; loop++) {  
                      if (loop !=summ_cools-1) {
                        line = line+QString( "%1," ).arg( rowone ); /* construct query */
                      } else {
                       line = line+rowone;    /* construct query */
                      } 
                  }
    
    line.prepend("INSERT INTO "+tablename+" VALUES (");  /* construct query */
    line.append(")"); /* construct query */
    query.exec("BEGIN TRANSACTION");
    query.exec(line);
    query.exec("COMMIT");
    loop = 0; /* reset  */
    line = ""; /* reset  */
    }
    }
    RefillTable( tablename );  
}
void Include_Table::RefillTable( QString tablesql )
{
        noloop = 10;
        structuresql = GetStructureTable(tablesql);
        bool have_index = structuresql.contains("integer primary key", Qt::CaseInsensitive);
        tablename = tablesql; /*  save on class !*/
        QSqlQuery query;
        /* check if as INTEGER PRIMARY KEY */
    
        sqlmodel = new QSqlTableModel();
        sqlmodel->setTable( tablesql );
        sqlmodel->setEditStrategy(QSqlTableModel::OnRowChange);
        sqlmodel->select();
        tabella_core->setModel(sqlmodel);
        tabella_core->setItemDelegate(new QSqlRelationalDelegate(tabella_core));
        horizontalheader = new QHeaderView(Qt::Horizontal,this);
        horizontalheader = tabella_core->horizontalHeader();
        connect (horizontalheader,SIGNAL(sectionClicked(int)),this,SLOT(sortColumn(int)));
        if (have_index) {
        vertikalheader = new QHeaderView(Qt::Vertical,this);
        vertikalheader = tabella_core->verticalHeader();
        connect (vertikalheader,SIGNAL( sectionDoubleClicked(int)),this,SLOT(VertikalRow(int)));
        }
        /*  discovery total row/cools  register on class ..... start */
        summ_rows = sqlmodel->rowCount();  /*  save on class !*/
        summ_cools = sqlmodel->columnCount(); /*  save on class !*/
        /*  discovery total row/cools ..... end  */
        if (have_index) {
        insert_row->setEnabled(true);
        create_index_button->setEnabled(false);
        /* debugline->setText( QString( "%1 index ok" ).arg( int2char( summ_cools )  ) ); */
        row_total->display( QString( "%1" ).arg( int2char( summ_rows ) ) );   
        } else {
        insert_row->setEnabled(false);
        create_index_button->setEnabled(true);
        /* debugline->setText( QString( "%1 noindex" ).arg( int2char( summ_cools ) ) ); */
        row_total->display( QString( "%1" ).arg( int2char( summ_rows ) ) );  
        }            
}


void Include_Table::sortColumn(int i)
{
  if (i > 0) {
   sqlmodel->sort(i,Qt::AscendingOrder);
   sqlmodel->sort(i,Qt::AscendingOrder);
   }
}

void Include_Table::VertikalRow(int i)
{   /* Header QMessageBox *msgb; */
    
    sqlmodel->revertRow(i); /* not save or update nothing on this row! undo! */
    noloop++;
    if (noloop == 11) {
    noloop = 0;
    QString msgDB =tr("<p>Make a selection ..  </p><p>You Confirm to remove row:</p><p>Click Ok (remove) button or press Esc.</p>");
    int reply = msgb->question(this, tr("Confirm to Delete this row?")+QString( " row nr.%1" ).arg( int2char( i ) ),
                                      msgDB,
                                      msgb->Ok,
                                      msgb->No);
        if (reply == 1 and reply !=0x200)  {  /* 0x200 = ESC doc! */
                bool remove_confirm = sqlmodel->removeRows(i,1);
                if (!remove_confirm ) {
                msgb->information( this , tr("Error delete row!"),tr("Sqlite 3 cant remove this row ")+QString( "%1" ).arg( int2char( i ) )); 
                }  
        }
    }
noloop = 100;
RefillTable( tablename );  /*  set noloop to 10 here ...*/
RefillTable( tablename );  /*  set noloop to 10 here ...*/
}



void Include_Table::CreateIndexTable()
{
/*  structuresql = GetStructureTable(tablesql); */
QString only_name_one; 
QString only_name_all;
QString tmp_table_end = "CREATE TABLE "+tablename; 
int numer;
QString struct_table_original = structuresql;
QString      table_para = structuresql.replace(tmp_table_end, QString(""));
int          resize = table_para.lastIndexOf(")");
             table_para = table_para.left(resize);  /* remove () */
             table_para = table_para.right(resize - 2); /* remove () and space */
    
QString sendtolist = table_para;
        sendtolist.replace(QString(" "), QString("#"));
QStringList  allnamelist = sendtolist.split(",");

    for (int i = 0; i < allnamelist.size(); ++i) {
        only_name_one = XML_utf8(QString( allnamelist.at(i) ));
        numer = only_name_one.lastIndexOf("#");
        QString trimm_str = only_name_one.left(numer);
        numer = trimm_str.lastIndexOf("#");
        trimm_str.left(numer);
        trimm_str.replace(QString("#"), QString(""));
                  if (i !=allnamelist.size() - 1) {
                    only_name_all = only_name_all+QString( "%1," ).arg( trimm_str ); 
                  } else {
                   only_name_all = only_name_all+QString( "%1" ).arg( trimm_str );   
                  }
         numer =0;
         only_name_one ="";
         trimm_str = "";
    }

QStringList prepare_query = ( QStringList()
                             << "CREATE TEMPORARY TABLE TEMP_TABLE ("+only_name_all+")"
                             << "INSERT INTO TEMP_TABLE SELECT "+only_name_all+" FROM "+tablename
                             << "DROP TABLE "+tablename
                             << "CREATE TABLE "+tablename+" (ID INTEGER PRIMARY KEY,"+table_para+")"
                             << "INSERT INTO "+tablename+"("+only_name_all+") SELECT "+only_name_all+" FROM TEMP_TABLE"
                             << "DROP TABLE TEMP_TABLE" );
QSqlQuery query;
                 for (int i = 0; i < prepare_query.size(); ++i) {
                    query.exec("BEGIN TRANSACTION");
                    query.exec(QString( prepare_query.at(i) ));
                    query.exec("COMMIT");
                 }
/* reload qtableview*/
RefillTable( tablename );               
}

/*
 result to have a new cool  index (id INTEGER PRIMARY KEY) and not restart db!
CREATE TEMPORARY TABLE TEMP_TABLE(Male, Sente);
INSERT INTO TEMP_TABLE SELECT Male, Sente FROM Mustermann;
DROP TABLE Mustermann;
CREATE TABLE Mustermann (id INTEGER PRIMARY KEY, Male TEXT, Sente TEXT);
INSERT INTO Mustermann(Male, Sente) SELECT Male, Sente FROM TEMP_TABLE;
DROP TABLE TEMP_TABLE;
*/

To copy to clipboard, switch view to plain text mode 

[wysota]

hi jpn..
I am using QSqlTableModel too, and it works fine. But i would like to change values from a complex query. And i have not found a example how to subclass it
Should be possible to change values from a query with QSqlQueryModel

use QSqlTableModel, which provides a read-write model based on a single database table.

You have to subclass QSqlQueryModel and reimplement setData(). Just remember that it may be very hard to do it for complex queries and it might prove simpler to subclass QAbstractTableModel and hide the sql layer from the user.