src/queryeditordialog.cpp

   1 /*
   2  * This file is part of LineMan.
   3  *
   4  * Copyright 2006 Igor Khanin
   5  *
   6  * LiteMan is free software; you can redistribute it and/or modify
   7  * it under the terms of the GNU General Public License as published by
   8  * the Free Software Foundation; either version 2 of the License, or
   9  * (at your option) any later version.
  10  *
  11  * LiteMan is distributed in the hope that it will be useful,
  12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  14  * GNU General Public License for more details.
  15  *
  16  * You should have received a copy of the GNU General Public License
  17  * along with LiteMan; if not, write to the Free Software
  18  * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
  19  */
  20
  21 #include <QRadioButton>
  22 #include <QPushButton>
  23 #include <QLineEdit>
  24 #include <QComboBox>
  25 #include <QGroupBox>
  26 #include <QCheckBox>
  27 #include <QLabel>
  28
  29 #include <QGridLayout>
  30 #include <QHBoxLayout>
  31 #include <QVBoxLayout>
  32
  33 #include "queryeditordialog.h"
  34 #include "termeditor.h"
  35 #include "database.h"
  36
  37 /*!
  38  * @brief Creates the query editor.
  39  *
  40  * @param parent The parent widget for the dialog.
  41  */
  42 QueryEditorDialog::QueryEditorDialog(Database * dbase, Mode mode, QWidget * parent): QDialog(parent)
  43 {
  44         db = dbase;
  45         curMode = mode;
  46
  47         initUI();
  48
  49         QStringList tables = db->getTables();
  50         tableList->addItems(tables);
  51
  52         // If a database has at least one table. auto select it
  53         if(tables.size() > 0)
  54                 tableSelected(tables[0]);
  55
  56         if(mode == CreateView)
  57                 setWindowTitle(tr("Create View"));
  58         else
  59                 setWindowTitle(tr("Build Query"));
  60 }
  61
  62 QueryEditorDialog::~QueryEditorDialog()
  63 {
  64 }
  65
  66 /*!
  67  * @brief generates a valid SQL statement using the values in the dialog
  68  */
  69 QString QueryEditorDialog::statement()
  70 {
  71         QString logicWord;
  72         QString sql = "SELECT ";
  73
  74         // Add checked fields list
  75         for(int i = 0; i < checkLayout->count(); i++)
  76         {
  77                 QWidget * widget = checkLayout->itemAt(i)->widget();
  78
  79                 if(!widget)
  80                         continue;
  81
  82                 QCheckBox * check = qobject_cast<QCheckBox *>(widget);
  83                 if(check)
  84                         if(check->checkState() == Qt::Checked)
  85                                 sql += (check->text() + ", ");
  86         }
  87         sql = sql.remove(sql.size() - 2, 2);    // cut the extra ", "
  88
  89         // Add table name
  90         sql += (" FROM '" + tableList->currentText() + "'");
  91
  92         // Optionaly add terms
  93         if(termsLayout->count() > 0)
  94         {
  95                 // But first determine what is the chosen logic word (And/Or)
  96                 (andButton->isChecked()) ? logicWord = "AND" : logicWord = "OR";
  97
  98                 sql += " WHERE ";
  99
 100                 for(int i = 0; i < termsLayout->count(); i++)
 101                 {
 102                         QWidget * widget = termsLayout->itemAt(i)->widget();
 103
 104                         if(!widget)
 105                                 continue;
 106
 107                         TermEditor * term = qobject_cast<TermEditor *>(widget);
 108                         if(term)
 109                         {
 110                                 sql += term->selectedField();
 111
 112                                 switch(term->selectedRelation())
 113                                 {
 114                                         case 0:         // Contains
 115                                                 sql += (" LIKE '%" + term->selectedValue() + "%'");
 116                                                 break;
 117
 118                                         case 1:         // Doesn't contain
 119                                                 sql += (" NOT LIKE '%" + term->selectedValue() + "%'");
 120                                                 break;
 121
 122                                         case 2:         // Equals
 123                                                 sql += (" = '" + term->selectedValue() + "'");
 124                                                 break;
 125
 126                                         case 3:         // Not equals
 127                                                 sql += (" <> '" + term->selectedValue() + "'");
 128                                                 break;
 129
 130                                         case 4:         // Bigger than
 131                                                 sql += (" > '" + term->selectedValue() + "'");
 132                                                 break;
 133
 134                                         case 5:         // Smaller than
 135                                                 sql += (" < '" + term->selectedValue() + "'");
 136                                                 break;
 137                                 }
 138                         }
 139                         sql += (" " + logicWord + " ");
 140                 }
 141                 sql = sql.remove(sql.size() - (logicWord.size() + 2), logicWord.size() + 2); // cut the extra " AND " or " OR "
 142         }
 143         sql += ";";
 144
 145         return sql;
 146 }
 147
 148 QString QueryEditorDialog::viewName()
 149 {
 150         return viewNameEdit->text();
 151 }
 152
 153 void QueryEditorDialog::initUI()
 154 {
 155         QPushButton * okButton = new QPushButton(tr("OK"));
 156         QPushButton * cancelButton = new QPushButton(tr("Cancel"));
 157
 158         connect(okButton, SIGNAL(clicked()), this, SLOT(accept()));
 159         connect(cancelButton, SIGNAL(clicked()), this, SLOT(reject()));
 160
 161         QLabel * viewLabel = new QLabel(tr("View name: "));
 162         viewNameEdit = new QLineEdit();
 163
 164         QLabel * tableLabel = new QLabel(tr("Table to Query: "));
 165         tableList = new QComboBox();
 166
 167         connect(tableList, SIGNAL(activated(const QString &)), this, SLOT(tableSelected(const QString &)));
 168
 169         andButton = new QRadioButton(tr("Match all of the following terms"));
 170         orButton = new QRadioButton(tr("Match any of the following terms"));
 171
 172         andButton->setChecked(true);
 173
 174         QPushButton * moreButton = new QPushButton(tr("More"));
 175         lessButton = new QPushButton(tr("Less"));
 176
 177         lessButton->setEnabled(false);
 178
 179         connect(moreButton, SIGNAL(clicked()), this, SLOT(moreTerms()));
 180         connect(lessButton, SIGNAL(clicked()), this, SLOT(lessTerms()));
 181
 182         //
 183         // Layout
 184         //
 185         checkLayout = new QGridLayout();
 186         termsLayout = new QVBoxLayout();
 187
 188         QGridLayout * topLayout = new QGridLayout();
 189
 190         if(curMode == CreateView)
 191         {
 192                 topLayout->addWidget(viewLabel, 0, 0);
 193                 topLayout->addWidget(viewNameEdit, 0, 1);
 194         }
 195         topLayout->addWidget(tableLabel, 1, 0);
 196         topLayout->addWidget(tableList, 1, 1);
 197
 198         QGroupBox * fieldsBox = new QGroupBox(tr("Fields"));
 199         fieldsBox->setLayout(checkLayout);
 200
 201         QHBoxLayout * andOrLayout = new QHBoxLayout();
 202         andOrLayout->addWidget(andButton);
 203         andOrLayout->addWidget(orButton);
 204
 205         QHBoxLayout * termButtonsLayout = new QHBoxLayout();
 206         termButtonsLayout->addStretch(1);
 207         termButtonsLayout->addWidget(moreButton);
 208         termButtonsLayout->addWidget(lessButton);
 209
 210         QVBoxLayout * outerTermsLayout = new QVBoxLayout();
 211         outerTermsLayout->addLayout(andOrLayout);
 212         outerTermsLayout->addLayout(termsLayout, 1);
 213         outerTermsLayout->addLayout(termButtonsLayout);
 214
 215         QGroupBox * termsBox = new QGroupBox(tr("Terms"));
 216         termsBox->setLayout(outerTermsLayout);
 217
 218         QHBoxLayout * buttonLayout = new QHBoxLayout();
 219         buttonLayout->addStretch(1);
 220         buttonLayout->addWidget(okButton);
 221         buttonLayout->addWidget(cancelButton);
 222
 223         QVBoxLayout * mainLayout = new QVBoxLayout();
 224         mainLayout->addLayout(topLayout);
 225         mainLayout->addWidget(fieldsBox);
 226         mainLayout->addWidget(termsBox);
 227         mainLayout->addStretch(1);
 228         mainLayout->addLayout(buttonLayout);
 229
 230         setLayout(mainLayout);
 231 }
 232
 233 void QueryEditorDialog::tableSelected(const QString & table)
 234 {
 235         FieldList fields = db->tableFields(table);
 236
 237         curTable = table;
 238
 239         // Clear checkLayout
 240         QLayoutItem * child;
 241         while((child = checkLayout->takeAt(0)) != 0)
 242         {
 243                 delete child->widget();
 244                 delete child;
 245         }
 246         repaint(0, 0, height(), width());
 247
 248         int row = 0;
 249         for(int i = 0; i < fields.size(); i++)
 250         {
 251                 DatabaseTableField field = fields[i];
 252                 QCheckBox * check = new QCheckBox(field.name);
 253
 254                 check->setCheckState(Qt::Checked);
 255                 checkLayout->addWidget(check, row, i % 2);
 256
 257                 if((i + 1) % 2 == 0)
 258                         row++;
 259         }
 260 }
 261
 262 void QueryEditorDialog::moreTerms()
 263 {
 264         TermEditor * term = new TermEditor(db->tableFields(curTable));
 265
 266         termsLayout->addWidget(term);
 267         lessButton->setEnabled(true);
 268 }
 269
 270 void QueryEditorDialog::lessTerms()
 271 {
 272         QLayoutItem * child = termsLayout->takeAt(termsLayout->count() - 1);
 273         if(child)
 274         {
 275                 delete child->widget();
 276                 delete child;
 277         }
 278
 279         if(termsLayout->count() == 0)
 280                 lessButton->setEnabled(false);
 281 }