TDE
/
kbarcode
mirror of https://mirror.git.trinitydesktop.org/gitea/TDE/kbarcode
0
0
Fork 0
Code Issues Releases Wiki Activity
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'r14.0.10'
${ noResults }
kbarcode/kbarcode/sqltables.cpp

661 lines
23 KiB
Raw Permalink Blame History

/***************************************************************************
sqltables.cpp - description
-------------------
begin : Son Dez 29 2002
copyright : (C) 2002 by Dominik Seichter
email : domseichter@web.de
***************************************************************************/
/***************************************************************************
* *
* This program is free software; you can redistribute it and/or modify *
* it under the terms of the GNU General Public License as published by *
* the Free Software Foundation; either version 2 of the License, or *
* (at your option) any later version. *
* *
***************************************************************************/
#include "sqltables.h"
#include "definition.h"
// TQt includes
#include <tqcheckbox.h>
#include <tqfile.h>
#include <tqgroupbox.h>
#include <tqlabel.h>
#include <tqlayout.h>
#include <tqmap.h>
#include <tqprogressdialog.h>
#include <tqsqldatabase.h>
// KDE includes
#include <tdeapplication.h>
#include <kcombobox.h>
#include <tdeconfig.h>
#include <klineedit.h>
#include <tdelocale.h>
#include <tdemessagebox.h>
#include <kpushbutton.h>
#include <kstandarddirs.h>
TQMap<TQString,SqlDescription*> drivers;
class MySqlDescription : public SqlDescription {
public:
virtual const TQString initdb( TQString ) const { return "mysql"; }
virtual const TQString autoIncrement() const { return "int(11) NOT NULL auto_increment"; }
virtual const TQString showColumns( const TQString & table ) const { return "SHOW COLUMNS FROM " + table; }
};
class PostgreSQLDescription : public SqlDescription {
public:
virtual const TQString initdb( TQString ) const { return "template1"; }
virtual const TQString autoIncrement() const { return "serial NOT NULL"; }
virtual const TQString showColumns( const TQString & table ) const {
return "select a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), "
"a.attnotnull, a.atthasdef, a.attnum, substring(d.adsrc for 128) "
"from pg_catalog.pg_attribute a "
"inner join pg_catalog.pg_class c on c.oid = a.attrelid "
"left join pg_catalog.pg_attrdef d on d.adrelid = c.oid and a.attnum = "
"d.adnum "
"where a.attnum > 0 and not a.attisdropped and c.relname ~ '^" + table + "$';";
}
};
/** should work for ODBC, too */
class SQLiteDescription : public SqlDescription {
public:
virtual const TQString initdb( TQString db = TQString() ) const { return db; }
virtual const TQString autoIncrement() const { return "serial NOT NULL"; }
virtual const TQString showColumns( const TQString & table ) const { return "SHOW COLUMNS FROM " + table; }
};
SqlTables::SqlTables( TQObject* parent )
: TQObject( parent, "sqltables" )
{
drivers.insert( "QMYSQL3", new MySqlDescription() );
drivers.insert( "QPSQL7", new PostgreSQLDescription() );
/* The same driver plugin is used for TQDBC and SQLite */
drivers.insert( "QSQLITE", new SQLiteDescription() );
drivers.insert( "QODBC3", new SQLiteDescription() );
drivers.insert( "TQMYSQL3", new MySqlDescription() );
drivers.insert( "TQPSQL7", new PostgreSQLDescription() );
/* The same driver plugin is used for TQDBC and SQLite */
drivers.insert( "TQSQLITE", new SQLiteDescription() );
drivers.insert( "TQODBC3", new SQLiteDescription() );
db = NULL;
connected = false;
loadConfig();
connect( kapp, TQT_SIGNAL( aboutToQuit() ), this, TQT_SLOT( saveConfig() ) );
}
SqlTables::~SqlTables()
{
}
SqlTables* SqlTables::instance = NULL;
SqlTables* SqlTables::getInstance()
{
if( !instance )
instance = new SqlTables();
return instance;
}
const SqlDescription* SqlTables::driver() const
{
return drivers[sqldata.driver];
}
bool SqlTables::connectMySQL()
{
db = TQSqlDatabase::addDatabase( sqldata.driver );
db->setDatabaseName( sqldata.database );
db->setUserName( sqldata.username );
db->setPassword( sqldata.password );
db->setHostName( sqldata.hostname );
if( !db->open() )
KMessageBox::error( 0, i18n("<qt>Unable to open database: ") + sqldata.database + "<br>" +
db->lastError().databaseText() + "</qt>");
connected = db->open();
if( connected ) {
updateTables();
emit connectedSQL();
// be sure that we have a clean list of producers
Definition::updateProducer();
}
return connected;
}
bool SqlTables::newTables()
{
return newTables( sqldata.username, sqldata.password, sqldata.hostname, sqldata.database, sqldata.driver );
}
bool SqlTables::newTables( const TQString & username, const TQString & password, const TQString & hostname, const TQString & database, const TQString & driver )
{
if( KMessageBox::warningContinueCancel( 0,
i18n("We are going to re-create the tables '") +
TQString( TABLE_BASIC "', '" TABLE_CUSTOMER "', '"
TABLE_CUSTOMER_TEXT) +i18n("' and '") + TQString(TABLE_LABEL_DEF "'")) == KMessageBox::Cancel )
return false;
if( !drivers[driver] )
return false;
TQSqlDatabase*dbase = TQSqlDatabase::addDatabase(driver, drivers[driver]->initdb( database ) );
dbase->setDatabaseName( drivers[driver]->initdb( database ) );
dbase->setUserName( username );
dbase->setPassword( password );
dbase->setHostName( hostname );
if (dbase->open()) {
if ((driver != "QSQLITE") && (driver != "TQSQLITE"))
{
bool found = false;
TQSqlQuery existing("SHOW DATABASES LIKE '" + database + "';");
while( existing.next() )
found = true;
TQSqlQuery firstquery( NULL, dbase );
if( !found && !firstquery.exec("CREATE DATABASE " + database + ";")) {
if( KMessageBox::warningContinueCancel( 0, i18n("<qt>Can't create database ") + database + i18n("<br>You can continue if the database exists already.</qt>")
+ firstquery.lastError().databaseText() ) == KMessageBox::Cancel ) {
dbase->close();
TQSqlDatabase::removeDatabase(drivers[driver]->initdb( database ));
return false;
}
}
}
dbase->close();
TQSqlDatabase::removeDatabase(drivers[driver]->initdb( database ));
} else {
TQSqlError dbError = dbase->lastError();
dbase->close();
TQSqlDatabase::removeDatabase(drivers[driver]->initdb( database ));
if (KMessageBox::warningContinueCancel(0, i18n("<qt>Can't connect to database to create table.") + "<p>" + dbError.driverText() + "<br>" + dbError.databaseText() + i18n("<br>You can continue if the table exists already.</qt>")) == KMessageBox::Cancel) {
return false;
}
}
// The database is created, now connect to the one specified by the user
dbase = TQSqlDatabase::addDatabase(driver, database );
dbase->setDatabaseName( database );
dbase->setUserName( username );
dbase->setPassword( password );
dbase->setHostName( hostname );
if(!dbase->open() || !dbase->isOpen()) {
KMessageBox::error( 0, i18n("KBarcode could not create the required database. Please create it manually.") + dbase->lastError().databaseText() );
TQSqlDatabase::removeDatabase( database );
return false;
}
TQSqlQuery query( NULL, dbase );
// barcode_basic
query.exec("DROP TABLE " TABLE_BASIC );
exec( &query, "CREATE TABLE " TABLE_BASIC " ("
" uid " + drivers[driver]->autoIncrement() + ","
" article_no varchar(50) DEFAULT NULL,"
" article_desc varchar(50) DEFAULT NULL,"
" barcode_no TEXT DEFAULT NULL,"
" encoding_type varchar(50) DEFAULT NULL,"
" field0 varchar(50) DEFAULT NULL,"
" field1 varchar(50) DEFAULT NULL,"
" field2 varchar(50) DEFAULT NULL,"
" field3 varchar(50) DEFAULT NULL,"
" field4 varchar(50) DEFAULT NULL,"
" field5 varchar(50) DEFAULT NULL,"
" field6 varchar(50) DEFAULT NULL,"
" field7 varchar(50) DEFAULT NULL,"
" field8 varchar(50) DEFAULT NULL,"
" field9 varchar(50) DEFAULT NULL,"
" PRIMARY KEY (uid)"
");" );
// customer
query.exec("DROP TABLE " TABLE_CUSTOMER );
exec( &query, "CREATE TABLE " TABLE_CUSTOMER " ("
" uid " + drivers[driver]->autoIncrement() + " ,"
" customer_no varchar(20) DEFAULT NULL,"
" customer_name varchar(20) DEFAULT NULL,"
" PRIMARY KEY (uid)"
");" );
// customer_text
query.exec("DROP TABLE " TABLE_CUSTOMER_TEXT );
exec( &query, "CREATE TABLE " TABLE_CUSTOMER_TEXT " ("
" uid " + drivers[driver]->autoIncrement() + ","
" customer_no varchar(20) DEFAULT NULL,"
" encoding_type varchar(50) DEFAULT NULL,"
" article_no varchar(50) DEFAULT NULL,"
" article_no_customer varchar(50) NULL,"
" barcode_no TEXT DEFAULT NULL,"
" line0 varchar(50) DEFAULT NULL,"
" line1 varchar(50) DEFAULT NULL,"
" line2 varchar(50) DEFAULT NULL,"
" line3 varchar(50) DEFAULT NULL,"
" line4 varchar(50) DEFAULT NULL,"
" line5 varchar(50) DEFAULT NULL,"
" line6 varchar(50) DEFAULT NULL,"
" line7 varchar(50) DEFAULT NULL,"
" line8 varchar(50) DEFAULT NULL,"
" line9 varchar(50) DEFAULT NULL,"
" PRIMARY KEY (uid)"
");" );
// label_def
query.exec("DROP TABLE " TABLE_LABEL_DEF );
exec( &query, "CREATE TABLE " TABLE_LABEL_DEF " ("
" label_no " + drivers[driver]->autoIncrement() + ","
" manufacture varchar(255) DEFAULT NULL,"
" type varchar(255) DEFAULT NULL,"
" paper char(1) DEFAULT NULL,"
" gap_top NUMERIC(10,4) NULL,"
" gap_left NUMERIC(10,4) NULL,"
" height NUMERIC(10,4) NULL,"
" width NUMERIC(10,4) NULL,"
" gap_v NUMERIC(10,4) NULL,"
" gap_h NUMERIC(10,4) NULL,"
" number_h int DEFAULT NULL," //smalint(6)
" number_v int DEFAULT NULL," //smalint(6)
" paper_type varchar(30) DEFAULT NULL,"
" compatibility varchar(10) DEFAULT NULL," // keep compatibility with older versions, was "remark text"
" PRIMARY KEY (label_no)"
");" );
dbase->close();
TQSqlDatabase::removeDatabase( database );
KMessageBox::information( 0, i18n("Created table ")+database+i18n(" successfully!") );
return true;
}
void SqlTables::importLabelDef()
{
if( KMessageBox::warningContinueCancel( 0, i18n("We are going to delete the complete table: " ) + TABLE_LABEL_DEF ) ==
KMessageBox::Cancel )
return;
TQSqlQuery query( TQString(), db );
exec( &query, "delete from " TABLE_LABEL_DEF );
TQString f = locateLocal( "data", "kbarcode/labeldefinitions.sql" );
if( !TQFile::exists( f ) ) {
TDEConfig* config = kapp->config();
config->setGroup( "Definitions" );
f = config->readEntry( "defpath", locate( "data", "kbarcode/labeldefinitions.sql" ) );
}
importData( f, db );
Definition::updateProducer();
}
void SqlTables::importExampleData()
{
if( KMessageBox::warningContinueCancel( 0,
i18n("We are going to delete the complete tables: " ) + TQString( TABLE_BASIC ", " TABLE_CUSTOMER ", " TABLE_CUSTOMER_TEXT ) ) ==
KMessageBox::Cancel )
return;
importData( locate("appdata", "exampledata.sql"), db );
}
void SqlTables::importData( const TQString & filename, TQSqlDatabase* db )
{
if( !db ) {
tqDebug("Can't import data, dabase not open!");
return;
}
if( filename.isEmpty() || !db->isOpen() ) // quick escape
{
KMessageBox::error( NULL, i18n("Data file for import not found. Continuing without importing data. Please check your KBarcode installation.") );
return;
}
TQFile data( filename);
TQProgressDialog* dlg = new TQProgressDialog( i18n("SQL import progress:"), TQString(), data.size(), 0, "dlg", true );
if( data.open( IO_ReadOnly ) ) {
TQString s;
TQSqlQuery query( TQString(), db );
while( data.readLine( s, 1024 ) != -1 )
if( !s.isEmpty() ) {
dlg->setProgress( dlg->progress() + s.length() );
exec( &query, s );
}
} else
KMessageBox::sorry( 0, i18n("Can't open the data file containing the label definitions.") );
dlg->close( true );
data.close();
}
void SqlTables::exec( TQSqlQuery* query, const TQString & text )
{
if( !query->exec( text ) )
KMessageBox::sorry( 0, i18n("<qt>Can't execute command:<br><b>") + text + "</b><br></qt>" + query->lastError().databaseText(), "sqlexecerror" );
}
void SqlTables::loadConfig()
{
TDEConfig* config = kapp->config();
config->setGroup("SQL");
sqldata.username = config->readEntry("username", "root");
sqldata.password = config->readEntry("password", "" );
sqldata.hostname = config->readEntry("hostname", "localhost" );
sqldata.database = config->readEntry("database", "kbarcode" );
sqldata.driver = config->readEntry("driver", "TQMYSQL3" );
sqldata.autoconnect = config->readBoolEntry("autoconnect", false );
}
void SqlTables::saveConfig()
{
TDEConfig* config = kapp->config();
config->setGroup("SQL");
config->writeEntry("username", sqldata.username );
config->writeEntry("password", sqldata.password );
config->writeEntry("hostname", sqldata.hostname );
config->writeEntry("database", sqldata.database );
config->writeEntry("driver", sqldata.driver );
config->writeEntry("autoconnect", sqldata.autoconnect );
config->sync();
}
void SqlTables::updateTables()
{
/* Older versions of KBarcode did only
* support MySQL. As a reason, databases
* do not need to be updated for other DBs
* as they have already been created with the most
* recent database structures.
*/
if ((sqldata.driver != "QMYSQL3") && (sqldata.driver != "TQMYSQL3"))
return;
bool changed = false;
// check for field0 - field9 (1.4.0)
TQStringList fields;
for( unsigned int i = 0; i < 10; i++ )
fields.append( TQString("field%1").arg( i ) );
TQSqlQuery query("SHOW FIELDS FROM " TABLE_BASIC );
while ( query.next() )
if( fields.grep( query.value( 0 ).toString(), false ).count() ) {
fields.remove( query.value( 0 ).toString() );
}
if( fields.count() ) {
TQSqlQuery q;
for( unsigned int i = 0; i < 10; i++ )
q.exec("ALTER TABLE " TABLE_BASIC " ADD " + fields[i] + " varchar(50)");
tqDebug("changing fields");
changed = true;
}
// change barcode_no from varchar to TEXT (1.5.0)
TQSqlQuery query2("SHOW FIELDS FROM " TABLE_BASIC );
while( query2.next() )
if( query2.value( 0 ).toString() == "barcode_no" && query2.value(1) == "varchar(50)" ) {
query2.exec( "ALTER TABLE " TABLE_BASIC " CHANGE barcode_no barcode_no TEXT" );
changed = true;
}
// change barcode_no from varchar to TEXT (1.5.0)
TQSqlQuery query3("SHOW FIELDS FROM " TABLE_CUSTOMER_TEXT );
while( query3.next() )
if( query3.value( 0 ).toString() == "barcode_no" && query3.value(1) == "varchar(50)" ) {
query3.exec( "ALTER TABLE " TABLE_CUSTOMER_TEXT " CHANGE barcode_no barcode_no TEXT" );
changed = true;
}
// change NUMERIC DEFAULT TO NUMERIC(10,4) (1.6.1)
TQStringList update;
update << "gap_top" << "gap_left" << "height" << "width" << "gap_v" << "gap_h";
TQSqlQuery query4("SHOW FIELDS FROM " TABLE_LABEL_DEF );
while( query4.next() )
if( update.contains( query4.value( 0 ).toString() ) && query4.value(1).toString() == "decimal(10,0)" ) {
TQSqlQuery q;
q.exec( TQString("ALTER TABLE " TABLE_LABEL_DEF " CHANGE ") +
query4.value( 0 ).toString() + " " + query4.value( 0 ).toString() + " NUMERIC(10,4)" );
changed = true;
}
if( changed )
KMessageBox::information( 0, i18n("The SQL tables of KBarcode have changed since the last version. "
"KBarcode updated them without any loss of data." ) );
}
bool SqlTables::testSettings( const TQString & username, const TQString & password, const TQString & hostname, const TQString & database, const TQString & driver )
{
TQSqlDatabase* db = TQSqlDatabase::addDatabase( driver );
if( !drivers[driver] )
return false;
db->setDatabaseName( database );
db->setUserName( username );
db->setPassword( password );
db->setHostName( hostname );
if( !db->open() )
{
TQSqlDatabase::removeDatabase( database );
}
else
{
KMessageBox::information( 0, i18n("Connected successfully to your database") );
db->close();
TQSqlDatabase::removeDatabase( database );
return true;
}
db = TQSqlDatabase::addDatabase( driver );
db->setDatabaseName( drivers[driver]->initdb( database ) );
db->setUserName( username );
db->setPassword( password );
db->setHostName( hostname );
if( !db->open() ) {
KMessageBox::error( 0, i18n("<qt>Connection failed:<br>") + database + "<br>" +
db->lastError().databaseText() + "</qt>" );
TQSqlDatabase::removeDatabase(drivers[driver]->initdb( database ));
return false;
} else {
KMessageBox::information( 0, i18n("Connected successfully to your database") );
db->close();
TQSqlDatabase::removeDatabase(drivers[driver]->initdb( database ));
return true;
}
}
const TQString SqlTables::getBarcodeMaxLength( const TQString & name )
{
if( SqlTables::isConnected() )
{
TQSqlQuery query("select uid, (length(barcode_no)) as LEN from " TABLE_BASIC
" where encoding_type = '" + name +"' ORDER by LEN DESC LIMIT 1" );
while( query.next() ) {
TQSqlQuery queryuid("select barcode_no from barcode_basic where uid = '"
+ query.value( 0 ).toString() + "'" );
while( queryuid.next() )
if(!queryuid.value( 0 ).toString().isEmpty())
return queryuid.value( 0 ).toString();
}
TQSqlQuery query1("select uid, (length(barcode_no)) as LEN from " TABLE_CUSTOMER_TEXT
" where encoding_type = '" + name +"' ORDER by LEN DESC LIMIT 1" );
while( query1.next() ) {
TQSqlQuery queryuid("select barcode_no from customer_text where uid = '"
+ query1.value( 0 ).toString() + "'" );
while( queryuid.next() )
if(!queryuid.value( 0 ).toString().isEmpty())
return queryuid.value( 0 ).toString();
}
}
return "1234567";
}
SqlWidget::SqlWidget( bool showlabel, TQWidget* parent, const char* name )
: TQWidget( parent, name )
{
TQVBoxLayout* layout = new TQVBoxLayout( this );
TQGroupBox* groupDatabase = new TQGroupBox( this );
groupDatabase->setTitle( i18n( "Database Settings" ) );
groupDatabase->setColumnLayout(0, Qt::Vertical );
groupDatabase->layout()->setSpacing( 6 );
groupDatabase->layout()->setMargin( 11 );
TQVBoxLayout* groupDatabaseLayout = new TQVBoxLayout( groupDatabase->layout() );
groupDatabaseLayout->setAlignment( TQt::AlignTop );
TQGridLayout* grid = new TQGridLayout( 2, 2 );
TQLabel* label = new TQLabel( groupDatabase );
label->setText( i18n("Username :") );
grid->addWidget( label, 0, 0 );
m_username = new KLineEdit( groupDatabase );
grid->addWidget( m_username, 0, 1 );
label = new TQLabel( groupDatabase );
label->setText( i18n("Password :") );
grid->addWidget( label, 1, 0 );
m_password = new KLineEdit( groupDatabase );
m_password->setEchoMode( KLineEdit::Password );
grid->addWidget( m_password, 1, 1 );
label = new TQLabel( groupDatabase );
label->setText( i18n("Database :") );
grid->addWidget( label, 2, 0 );
m_database = new KLineEdit( "kbarcode", groupDatabase );
grid->addWidget( m_database, 2, 1 );
label = new TQLabel( groupDatabase );
label->setText( i18n("Host :") );
grid->addWidget( label, 3, 0 );
m_hostname = new KLineEdit( "localhost", groupDatabase );
grid->addWidget( m_hostname, 3, 1 );
label = new TQLabel( groupDatabase );
label->setText( i18n("Driver :") );
grid->addWidget( label, 4, 0 );
m_driver = new KComboBox( false, groupDatabase );
TQStringList drList = TQSqlDatabase::drivers();
TQStringList::Iterator it = drList.begin();
while( it != drList.end() ) {
m_driver->insertItem( *it );
++it;
}
grid->addWidget( m_driver, 4, 1 );
groupDatabaseLayout->addLayout( grid );
m_autoconnect = new TQCheckBox( i18n("&Autoconnect on program start"), groupDatabase );
groupDatabaseLayout->addWidget( m_autoconnect );
buttonTest = new KPushButton( groupDatabase, "buttonTest" );
buttonTest->setText( i18n( "&Test Settings" ) );
groupDatabaseLayout->addWidget( buttonTest );
if( showlabel )
groupDatabaseLayout->addWidget( new TQLabel( i18n("<b>You have to test your database settings before you can procede.</b>"), groupDatabase ) );
layout->add( groupDatabase );
connect( buttonTest, TQT_SIGNAL( clicked() ), this, TQT_SLOT( testSettings() ) );
const mysqldata & sqldata = SqlTables::getInstance()->getData();
m_username->setText( sqldata.username );
m_password->setText( sqldata.password );
m_hostname->setText( sqldata.hostname );
m_database->setText( sqldata.database );
m_autoconnect->setChecked( sqldata.autoconnect );
for( int i = 0; i < m_driver->count(); i++ )
if( m_driver->text(i) == sqldata.driver )
m_driver->setCurrentItem( i );
}
SqlWidget::~SqlWidget()
{
}
void SqlWidget::save( bool usedb )
{
mysqldata sqldata = SqlTables::getInstance()->getData();
sqldata.username = m_username->text();
sqldata.password = m_password->text();
sqldata.hostname = m_hostname ->text();
sqldata.database = m_database->text();
sqldata.driver = m_driver->currentText();
sqldata.autoconnect = ( usedb ? m_autoconnect->isChecked() : false );
SqlTables::getInstance()->setData( sqldata );
}
void SqlWidget::testSettings()
{
emit databaseWorking(
SqlTables::getInstance()->testSettings( m_username->text(), m_password->text(),
m_hostname->text(), m_database->text(),
m_driver->currentText() ) );
}
const TQString SqlWidget::username() const
{
return m_username->text();
}
const TQString SqlWidget::driver() const
{
return m_driver->currentText();
}
const TQString SqlWidget::database() const
{
return m_database->text();
}
const TQString SqlWidget::hostname() const
{
return m_hostname->text();
}
const TQString SqlWidget::password() const
{
return m_password->text();
}
bool SqlWidget::autoconnect() const
{
return m_autoconnect->isChecked();
}
int SqlWidget::driverCount() const
{
return m_driver->count();
}
#include "sqltables.moc"
View Git Blame Copy Permalink