#include "include_table.h"
Include_Table
::Include_Table( 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()
{
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 !*/
/* check if as INTEGER PRIMARY KEY */
sqlmodel->setTable( tablesql );
sqlmodel->select();
tabella_core->setModel(sqlmodel);
horizontalheader
= new QHeaderView(Qt
::Horizontal,
this);
horizontalheader = tabella_core->horizontalHeader();
connect (horizontalheader,SIGNAL(sectionClicked(int)),this,SLOT(sortColumn(int)));
if (have_index) {
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 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 */
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);
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 = "";
}
<< "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" );
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
Bookmarks