qsqlmon/servertreeitem.cpp

   1 #include "theapp.h"
   2 #include "servertreeitem.h"
   3 #include "mainwindow.h"
   4
   5 //#include <qf.h>
   6 #include <qfpixmapcache.h>
   7 #include <qfexception.h>
   8 #include <qfdlgexception.h>
   9 #include <qfstring.h>
  10 #include <qfsqlquery.h>
  11 #include <qfsqlcatalog.h>
  12 #include <qfcrypt.h>
  13
  14 #include <QIcon>
  15 #include <QVariant>
  16 #include <QSqlDatabase>
  17 #include <QSqlError>
  18 #include <QSqlDriver>
  19
  20 //#define QF_NO_TRASH_OUTPUT
  21 #include <qflogcust.h>
  22
  23 //=============================================================
  24 //                     ServerTreeItem
  25 //=============================================================
  26 ServerTreeItem::ServerTreeItem(QObject *parent, const QString& name)
  27         : QObject(parent)
  28 {
  29         //qfTrash() <<  QF_FUNC_NAME << this << name;
  30         setObjectName(name);
  31 }
  32
  33 ServerTreeItem::~ServerTreeItem()
  34 {
  35         //qfTrash() <<  QF_FUNC_NAME << this << objectName();
  36 }
  37
  38 MainWindow * ServerTreeItem::mainWindow() throw( QFException )
  39 {
  40         MainWindow *ret = qfFindParent<MainWindow*>(model());
  41         return ret;
  42 }
  43
  44 QFObjectItemModel* ServerTreeItem::model()
  45 {
  46         //qfTrash() << QF_FUNC_NAME;
  47         QObject *o = this;
  48         while(o) {
  49                 QFObjectItemModelRoot *r = qobject_cast<QFObjectItemModelRoot*>(o);
  50                 if(r) {
  51                         //qfTrash() << "\tmodel:" << r->model();
  52                         //r->model()->dumpObjectInfo();
  53                         return r->model();
  54                 }
  55                 o = o->parent();
  56         }
  57         Q_ASSERT(o != NULL);
  58         return NULL;
  59 }
  60
  61 Database* ServerTreeItem::database()
  62 {
  63         QObject *o = this;
  64         Database *d = NULL;
  65         while(o) {
  66                 d = qobject_cast<Database*>(o);
  67                 if(d) break;
  68                 o = o->parent();
  69         }
  70         return d;
  71 }
  72
  73 void ServerTreeItem::driverDestroyed(QObject *o)
  74 {
  75         qfTrash() << QF_FUNC_NAME << "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!";
  76         qfTrash() << "\tdestroyed driver:" << o;
  77 }
  78
  79 //===================================================
  80 //                 Connection
  81 //===================================================
  82 Connection::~Connection()
  83 {
  84         //qDebug() <<  "destructor:" << this;
  85 }
  86
  87 Connection::Connection(const QFDomElement& _params, QObject *parent, const QString& name)
  88                 : ServerTreeItem(parent, name), params(_params)
  89 {
  90         setObjectName(param("description"));
  91         //qfInfo() << params.toString();
  92 }
  93
  94 bool Connection::isOpen()
  95 {
  96     return children().count() > 0;
  97 }
  98
  99 void Connection::close()
 100 {
 101         qfTrash() << QF_FUNC_NAME;
 102         QModelIndex ix = model()->object2index(this);
 103         model()->deleteChildren(ix);
 104         /*
 105         QModelIndex ix = model()->object2index(this);
 106         while(model()->rowCount(ix)) {
 107                 QObject *o = model()->take(model()->index(0, 0, ix));
 108                 qfTrash() << o->objectName();
 109                 delete o;
 110         }
 111         */
 112     //foreach(QObject *o, children()) delete o;
 113 }
 114
 115 Database* Connection::open() throw(QFException)
 116 {
 117         Database *d = NULL;
 118         QList<QObject*> olst;
 119         try {
 120                 close();
 121                 QFObjectItemModel *m = model();
 122                 QModelIndex ix = m->object2index(this);
 123                 d = new Database(this, param("database"));
 124                 olst << d;
 125                 d->open();
 126         QStringList sl = d->connection().databases();
 127         foreach(QString s, sl) {
 128             if(s == param("database")) continue;
 129                         olst << new Database(this, s);
 130                 }
 131                 m->append(olst, ix);
 132         }
 133     catch(QFException &e) {
 134         QFDlgException dlg; dlg.exec(e);
 135         qDeleteAll(olst);
 136                 d = NULL;
 137     }
 138         return d;
 139 }
 140
 141
 142 QVariant Connection::icon(int col)
 143 {
 144         static QIcon ico;
 145         static bool first_scan = true;
 146         if(first_scan) {
 147                 first_scan = false;
 148                 ico.addFile(":/images/server_on.png", QSize(), QIcon::Normal, QIcon::On);
 149                 ico.addFile(":/images/server_off.png", QSize(), QIcon::Normal, QIcon::Off);
 150         }
 151         if(col == 0) return qVariantFromValue(ico);
 152         return QVariant();
 153 }
 154
 155 QVariant Connection::text(int col)
 156 {
 157     QVariant ret;
 158     switch(col) {
 159                 case 0: ret = param("description"); break;
 160                 case 1: ret = param("user") + "@" + param("host"); break;
 161                 case 2: ret = param("database"); break;
 162                 case 3: ret = param("driver"); break;
 163     }
 164         return ret;
 165 }
 166
 167 QString Connection::param(const QString& name)
 168 {
 169         //qfInfo() << QFLog::stackTrace();
 170         QString default_value = "";
 171         if(name == "description") default_value = "New_Connection";
 172         else if(name == "host") default_value = "localhost";
 173         //qfInfo() << "default_value:" << default_value;
 174         QFString s = params.cd(name, !Qf::ThrowExc).value(default_value).toString();
 175         if(name == "password") {
 176                 s = QFCrypt().decrypt(s.toAscii());
 177                 //qfInfo() << "password:" << s;
 178         }
 179         return s;
 180 }
 181
 182 void Connection::setParam(const QString& name, const QString& value)
 183 {
 184         Q_ASSERT(params.isElement());
 185         QFString s = value;
 186         if(name == "password") {
 187                 //qfTrash() << "password:" << s;
 188                 s = QString::fromAscii(QFCrypt().crypt(s));
 189         }
 190         params.setValue(name, s);
 191 }
 192 //===================================================
 193
 194
 195 //===================================================
 196 //                 Database
 197 //===================================================
 198 Database::Database(QObject *parent, const QString& name)
 199         : ServerTreeItem(parent, name)
 200 {
 201         QObject *o = Qf::findParentOfType(this, "MainWindow");
 202         if(o) connect(this, SIGNAL(connectionInfo(const QString&)), o, SLOT(appendInfo(const QString&)));
 203 }
 204
 205 Database::~Database()
 206 {
 207         //qfTrash() << QF_FUNC_NAME << "############";
 208         //close();
 209         //qDebug() <<  "destructor:" << this;
 210 }
 211
 212 QVariant Database::icon(int col)
 213 {
 214         static QIcon ico_open;
 215         static QIcon ico_closed;
 216         static bool first_scan = true;
 217         if(first_scan) {
 218                 first_scan = false;
 219                 ico_open.addFile(":/images/database_on.png", QSize(), QIcon::Normal);//, QIcon::On);
 220                 ico_closed.addFile(":/images/database_off.png", QSize(), QIcon::Normal);//, QIcon::Off);
 221         }
 222         if(col == 0) {
 223                 if(isOpen()) return qVariantFromValue(ico_open);
 224                 return qVariantFromValue(ico_closed);
 225         }
 226         return QVariant();
 227 }
 228
 229 QVariant Database::text(int col)
 230 {
 231         QVariant ret;
 232         if(col == 0) ret = objectName();
 233         return ret;
 234 }
 235
 236 QString Database::getConnectionId()
 237 {
 238         QString s;
 239         Connection *c = qobject_cast<Connection*>(parent());
 240         if(c) {
 241                 s = objectName() + "[" + c->param("driver") + "]" + c->param("user") + "@" + c->param("host") + ":" + c->param("port");
 242         }
 243         return s;
 244 }
 245
 246 void Database::open() throw(QFException)
 247 {
 248         qfTrash() << QF_FUNC_NAME;
 249         Connection *c = qobject_cast<Connection*>(parent());
 250         if(!c) return;
 251         /*
 252         sqlConnection = QFSqlConnection(QSqlDatabase::database(getConnectionId()));
 253         if(!sqlConnection.isValid()) {
 254                 sqlConnection = QFSqlConnection(QSqlDatabase::addDatabase(c->param("driver"), getConnectionId()));
 255         }
 256         if(!sqlConnection.isValid()) {
 257                 QString s = tr("Error creating SQL connection %1").arg(getConnectionId())
 258                         + QFEOLN + QFEOLN
 259                         + sqlConnection.lastError().text();
 260                 throw QFException(s);
 261         }
 262         */
 263         sqlConnection.close();
 264         sqlConnection = QFSqlConnection(c->param("driver"));
 265         //QObject::connect(sqlConnection.driver(), SIGNAL(destroyed(QObject*)), this->parent(), SLOT(driverDestroyed(QObject*)));
 266         sqlConnection.setHostName(c->param("host"));
 267         sqlConnection.setPort(c->param("port").toInt());
 268         sqlConnection.setUserName(c->param("user"));
 269         sqlConnection.setPassword(c->param("password"));
 270         //qDebug() << sqlConnection.password();
 271         sqlConnection.setDatabaseName(objectName());
 272         qfTrash() << "\t" << sqlConnection.info();
 273         QFSqlConnection::ConnectionOptions opts;
 274         //opts["QF_CODEC_NAME"] = theApp()->config()->value("/i18n/dbtextcodec", "UTF-8").toString();
 275         QString codec_name = c->param("textcodec");
 276         if(!codec_name.isEmpty()) opts["QF_CODEC_NAME"] = codec_name;
 277         sqlConnection.setJournal(theApp()->sqlJournal());
 278         sqlConnection.open(opts);
 279         if(c->param("driver").endsWith("MYSQL")) {
 280                 QString set_names = c->param("mysqlSetNames");
 281                 if(!set_names.isEmpty() && set_names[0] != '<') {
 282                         QFSqlQuery q(sqlConnection);
 283                         q.exec("SET NAMES "SARG(set_names));
 284                 }
 285         }
 286         else if(c->param("driver").endsWith("SQLITE")) {
 287                 {
 288                         QString s = c->param("sqlite_pragma_full_column_names");
 289                         if(s != "1") s = "0";
 290                         QFSqlQuery q(sqlConnection);
 291                         //qfTrash() << "\texecuting:" << "PRAGMA full_column_names = 1";
 292                         q.exec("PRAGMA full_column_names = " + s);
 293                 }
 294                 {
 295                         QString s = c->param("sqlite_pragma_short_column_names");
 296                         if(s != "1") s = "0";
 297                         QFSqlQuery q(sqlConnection);
 298                         q.exec("PRAGMA short_column_names = " + s);
 299                 }
 300         }
 301         qfTrash() << "\tdriver:" << sqlConnection.driver();
 302         qfTrash() << "\tcodec:" << opts["QF_CODEC_NAME"];
 303         qfTrash() << "\tis open:" << sqlConnection.isOpen();
 304         QString s = sqlConnection.driver()->property("connectionInfo").toString();
 305         qfTrash() << "\tinfo:" << s;
 306         emit connectionInfo(s);
 307
 308         QFObjectItemModel *m = model();
 309         QModelIndex ix = m->object2index(this);
 310         QList<QObject*> olst;
 311         if(sqlConnection.driverName().endsWith("IBASE")) {
 312                 /// tables
 313                 QStringList sl;
 314                 sl = sqlConnection.tables("cokoli", QSql::Tables);
 315                 qSort(sl);
 316                 foreach(QString s, sl) olst << new Table(this, s, QFSql::TableRelation);
 317                 sl = sqlConnection.tables("cokoli", QSql::Views);
 318                 qSort(sl);
 319                 foreach(QString s, sl) olst << new Table(this, s, QFSql::ViewRelation);
 320         }
 321         else {
 322                 foreach(QString s, sqlConnection.schemas()) {
 323                         Schema *sch = new Schema(NULL, s);
 324                         olst << sch;
 325                         connect(sch, SIGNAL(progressValue(double, const QString&)), mainWindow(), SLOT(setProgressValue(double, const QString&)));
 326                 }
 327         }
 328         m->append(olst, ix);
 329
 330         //QFObjectItemModel *m = model();
 331         //int n = sl.count();
 332         //if(n > 0)
 333         //      m->emitRowsInserted(m->object2index(this), 0, n-1);
 334 }
 335
 336 void Database::close()
 337 {
 338         qfTrash() << this << "Database::close(): " << sqlConnection.info();
 339         sqlConnection.close();
 340         sqlConnection = QSqlDatabase(); // zrus referenci na databasi
 341
 342         // vymaz deti
 343         QModelIndex ix = model()->object2index(this);
 344         model()->deleteChildren(ix);
 345         //QSqlDatabase::removeDatabase(getConnectionId());
 346         // delete tables
 347         /*
 348         QObjectList chld_lst = children();
 349         while(!chld_lst.isEmpty())
 350         delete chld_lst.takeFirst();
 351         */
 352         //QFObjectItemModel *m = model();
 353         /*
 354         int n = children().count();
 355         if(n > 0) {
 356                 //m->emitRowsAboutToBeRemoved(m->object2index(this), 0, n-1);
 357                 // objekt se v destruktoru sam vyjme ze seznamu
 358                 foreach(QObject *o, children()) {
 359                         delete o; // nevim, jestli se to nebude sekat, nemelo by
 360                         //o->deleteLater(); // tohle je tutovka
 361                 }
 362         }
 363         */
 364 }
 365
 366 //===================================================
 367
 368 //=============================================================
 369 //                     Schema
 370 //=============================================================
 371 QVariant Schema::icon(int col)
 372 {
 373         static QIcon ico;
 374         static bool first_scan = true;
 375         if(first_scan) {
 376                 first_scan = false;
 377                 ico.addFile(":/images/schema.png");
 378         }
 379         if(col == 0) return qVariantFromValue(ico);
 380         return QVariant();
 381 }
 382
 383 QVariant Schema::text(int col)
 384 {
 385         QVariant ret;
 386         if(col == 0) ret = objectName();
 387         return ret;
 388 }
 389
 390 QString Schema::createScript(int flags) throw(QFException)
 391 {
 392         qfTrash() << QF_FUNC_NAME;
 393
 394         QString ret;
 395         if(!isOpen()) open();
 396
 397         // find parent database
 398         Database *d = database();
 399         if(!d) return "cann't find database";
 400         QFSqlConnection c = d->connection();
 401         if(c.driverName().endsWith("SQLITE")) {
 402                 QFSqlQuery q(c);
 403                 q.exec("SELECT sql FROM main.sqlite_master WHERE NAME NOT LIKE 'sqlite_%'");
 404                 QStringList sl;
 405                 while(q.next()) {
 406                         sl << q.value(0).toString() + ';';
 407                 }
 408                 ret = sl.join("\n");
 409         }
 410         else {
 411                 QFSqlCatalog &cat = c.catalog();
 412                 if(c.driverName().endsWith("MYSQL")) {
 413                         cat.setCurrentSchema(objectName());
 414                 }
 415                 QFSqlDbInfo di = cat.database(objectName());
 416                 QStringList sl = di.tables();
 417                 QStringList sl_tables;
 418                 QStringList sl_views;
 419                 foreach(QString s, sl) {
 420                         QFSqlTableInfo ti = di.table(s);
 421                         if(ti.relationKind() == QFSql::ViewRelation) sl_views << s;
 422                         else sl_tables << s;
 423                 }
 424                 foreach(QString s, sl_tables) {
 425                         QFSqlTableInfo ti = di.table(s);
 426                         new Table(this, s, ti.relationKind());
 427                 }
 428                 foreach(QString s, sl_views) {
 429                         new Table(this, s, QFSql::ViewRelation);
 430                 }
 431                 qfTrash() << "\t tables count:" << sl.count();
 432                 double cnt = sl.count();
 433                 int no = 1;
 434                 foreach(QString s, sl_tables) {
 435                         QFSqlTableInfo ti = di.table(s);
 436                         QString tbl_name = ti.fullName();
 437                         emit progressValue(no++/cnt, ti.tableName());
 438                         qfTrash() << "\t table name:" << tbl_name;
 439                         if(flags & CreateTableSql) {
 440                                 ret += c.createTableSqlCommand(tbl_name);
 441                                 ret += "\n\n";
 442                         }
 443                         if(flags & DumpTableSql) {
 444                                 ret += c.dumpTableSqlCommand(tbl_name);
 445                                 ret += "\n\n";
 446                         }
 447                 }
 448                 if(flags & IncludeViews) foreach(QString s, sl_views) {
 449                         QFSqlTableInfo ti = di.table(s);
 450                         QString tbl_name = ti.fullName();
 451                         emit progressValue(no++/cnt, ti.tableName());
 452                         qfTrash() << "\t table name:" << tbl_name;
 453                         if(flags & CreateTableSql) {
 454                                 ret += c.createTableSqlCommand(tbl_name);
 455                                 ret += "\n\n";
 456                         }
 457                 }
 458         }
 459         emit progressValue(-1);
 460         return ret;
 461 }
 462
 463 void Schema::open() throw(QFException)
 464 {
 465         qfLogFuncFrame();
 466         if(isOpen()) return;
 467         /// find parent database
 468         Database *d = database();
 469         if(!d) return;
 470         QFSqlConnection c = d->connection();
 471         QFSqlCatalog &cat = c.catalog();
 472         if(c.driverName().endsWith("MYSQL")) {
 473                 cat.setCurrentSchema(objectName());
 474         }
 475         QFSqlDbInfo di = cat.database(objectName());
 476         QStringList sl_tables = di.tables(QFSql::AllRelations & ~QFSql::ViewRelation);
 477         QStringList sl_views = di.tables(QFSql::ViewRelation);
 478         qSort(sl_tables);
 479         QFObjectItemModel *m = model();
 480         QModelIndex ix = m->object2index(this);
 481         QList<QObject*> olst;
 482         foreach(QString s, sl_tables) {
 483                 qfTrash() << "\t adding table" << s;
 484                 olst << new Table(NULL, s, QFSql::TableRelation);
 485         }
 486         qSort(sl_views);
 487         foreach(QString s, sl_views) {
 488                 qfTrash() << "\t adding view" << s;
 489                 olst << new Table(NULL, s, QFSql::ViewRelation);
 490         }
 491         m->append(olst, ix);
 492         /*
 493         if(c.driverType() == QFSqlConnection::PSQL) {
 494                 QString s = "SELECT n.nspname,  c.relname,"
 495                                                         "       CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END AS type,"
 496                                                         "       u.usename,"
 497                                                         "  pg_catalog.obj_description(c.oid, 'pg_class') AS descr"
 498                                                         " FROM pg_catalog.pg_class c"
 499                                                         " LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner"
 500                                                         " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace"
 501                                                         " WHERE c.relkind IN ('r','v')"
 502                                                         "  AND n.nspname = '%1'"
 503                                                         " ORDER BY 1,2;";
 504                 QFSqlQuery q(c);
 505                 q.exec(s.arg(objectName()));
 506                 foreach(QFSqlQuery::Row row, q.rows()) {
 507                         QString kind = row.value(2).toString();
 508                         if(kind == "view") new Table(this, row.value(1).toString(), Table::KindView);
 509                         if(kind == "table") new Table(this, row.value(1).toString(), Table::KindTable);
 510                 }
 511         }
 512         */
 513
 514         isopen = true;
 515 }
 516
 517 void Schema::close()
 518 {
 519         //qfTrash() << QF_FUNC_NAME;
 520         QModelIndex ix = model()->object2index(this);
 521         model()->deleteChildren(ix);
 522         isopen = false;
 523         Database *d = database();
 524         if(!d) return;
 525         QFSqlConnection c = d->connection();
 526         QFSqlCatalog &cat = c.catalog();
 527         cat.forgetDatabase(objectName());
 528 }
 529 //===================================================
 530
 531 //===================================================
 532 //                 Table
 533 //===================================================
 534 Table::~Table()
 535 {
 536         //qDebug() <<  "destructor:" << this;
 537 }
 538
 539 QVariant Table::icon(int col)
 540 {
 541         if(col == 0) {
 542                 if(kind == QFSql::TableRelation) return qVariantFromValue(QFPixmapCache::icon("icon.table", ":/images/table.png"));
 543                 else if(kind == QFSql::ViewRelation) return qVariantFromValue(QFPixmapCache::icon("icon.view", ":/images/view.png"));
 544                 else if(kind == QFSql::SystemTableRelation) return qVariantFromValue(QFPixmapCache::icon("icon.systemTable", ":/images/systemtable.png"));
 545         }
 546         return QVariant();
 547 }
 548
 549 QVariant Table::text(int col)
 550 {
 551         QVariant ret;
 552         if(col == 0) ret = objectName();
 553         return ret;
 554 }
 555
 556 QString Table::schema() const
 557 {
 558         const QObject *o = this;
 559         const Schema *d = NULL;
 560         while(o) {
 561                 d = qobject_cast<const Schema*>(o);
 562                 if(d) return d->objectName();
 563                 o = o->parent();
 564         }
 565         return "";
 566 }
 567 //===================================================
 568
 569