QSqlQueryModel + set Write

[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