QVariant SchemaModel::data(const QModelIndex &index, int role) const
{
if (!index.isValid ()) {
return QVariant ();
}
if (role == Qt::DisplayRole) {
if (index.row () >= record.count ()) {
return QVariant ();
}
QSqlField field = record.field(index.row());
switch (index.column ()) {
case 0:
return field.name ();
case 1:
return QLatin1String (QVariant::typeToName(field.type()));
case 2:
return field.length ();
case 3: {
QStringList mods;
if (pindex.indexOf (field.name ()) >= 0) {
mods << tr("PRIMARY KEY");
}
if (field.requiredStatus () == QSqlField::Required) {
mods << tr("NOT NULL");
}
else if (field.requiredStatus () == QSqlField::Optional) {
mods << tr ("NULL");
}
if (field.isAutoValue ()) {
mods << tr ("AUTO_INCREMENT");
}
return mods.join(", ");
}
case 4:
return field.defaultValue ();
}
}
else if (role == Qt::DecorationRole) {
}
return QVariant ();
}
QString Browser::StructureTable( QSqlRecord now )
{
QSqlRecord rec = now;
QString createtable;
QString sqltipename;
QStringList CreateTable;
int totalcools = rec.count();
if (totalcools > 0) {
/* sqlite3 table construct from odbc converter */
for (int i = 0; i < totalcools; ++i) {
QSqlField fld = rec.field(i);
QString name = Strtrimmed(fld.name());
QString typeoffield = QString(QVariant::typeToName(fld.type()));
/* fld.isAutoValue() never true from auto_increment mysql ??? */
if (fld.requiredStatus() and i < 2 and typeoffield == "int") {
sqltipename = QString("%1 INTEGER PRIMARY KEY").arg(name);
} else if ( typeoffield == "double" or typeoffield == "int" ) {
sqltipename = QString("%1 NUMERIC").arg(name);
} else if ( typeoffield == "QByteArray") {
sqltipename = QString("%1 BLOB").arg(name);
} else {
sqltipename = QString("%1 TEXT").arg(name);
}
CreateTable.append(sqltipename);
////////qDebug() << "### fieldname " << name;
///////////qDebug() << "### typeoffield " << typeoffield;
}
QString midlecreate = CreateTable.join(",");
midlecreate.prepend(QString("CREATE TABLE %1 (").arg(runningtable));
midlecreate.append(");");
createtable = midlecreate;
}
return createtable;
}
QDebug operator<<(QDebug dbg, const QSqlField &f)
{
dbg.nospace() << "QSqlField(" << f.name() << ", " << QMetaType::typeName(f.type());
if (f.length() >= 0)
dbg.nospace() << ", length: " << f.length();
if (f.precision() >= 0)
dbg.nospace() << ", precision: " << f.precision();
if (f.requiredStatus() != QSqlField::Unknown)
dbg.nospace() << ", required: "
<< (f.requiredStatus() == QSqlField::Required ? "yes" : "no");
dbg.nospace() << ", generated: " << (f.isGenerated() ? "yes" : "no");
if (f.typeID() >= 0)
dbg.nospace() << ", typeID: " << f.typeID();
if (!f.defaultValue().isNull())
dbg.nospace() << ", auto-value: \"" << f.defaultValue() << '\"';
dbg.nospace() << ')';
return dbg.space();
}
QString Browser::StructureMYSQLTable( QSqlRecord now )
{
QSqlRecord rec = now;
QString createtable;
QString sqltipename, mkey;
QStringList CreateTable;
int totalcools = rec.count();
if (totalcools > 0) {
/* sqlite3 table construct from odbc converter */
for (int i = 0; i < totalcools; ++i) {
QSqlField fld = rec.field(i);
QString name = Strtrimmed(fld.name());
name.toUpper();
if (name == "id") {
name = "ID";
}
QString typeoffield = QString(QVariant::typeToName(fld.type()));
int sizefe = fld.length();
if (sizefe > 255) {
sizefe = 255;
}
if (sizefe < 1) {
sizefe = 255;
}
/* fld.isAutoValue() never true from auto_increment mysql ??? */
if (fld.requiredStatus() and i < 2 and typeoffield == "int") {
sqltipename = QString(" `%1` INT(%2) NOT NULL auto_increment").arg(name).arg(sizefe);
mkey = QString(" KEY `%1` (`%1`)").arg(name);
} else if ( typeoffield == "int" ) {
sqltipename = QString(" `%1` int(%2) default NULL").arg(name).arg(sizefe);
} else if ( typeoffield == "double") {
sqltipename = QString(" `%1` int(%2) default NULL").arg(name).arg(sizefe);
} else if ( typeoffield == "QByteArray") {
sqltipename = QString(" `%1` LONGBLOB default NULL").arg(name);
} else {
sqltipename = QString(" `%1` VARCHAR(%2) default NULL").arg(name).arg(sizefe);
}
CreateTable.append(sqltipename);
////////qDebug() << "### fieldname " << name;
///////////qDebug() << "### typeoffield " << typeoffield;
}
if (mkey.size() > 0) {
CreateTable.append(mkey);
}
QString midlecreate = CreateTable.join(",\n");
midlecreate.prepend(QString("### mysql version 5 or > ###\nDROP TABLE IF EXISTS `%1`;\nCREATE TABLE `%1` (\n").arg(runningtable));
midlecreate.append("\n) TYPE=MyISAM;");
createtable = midlecreate;
}
return createtable;
}
void DataViewer::onDatabaseItemActivated(const DatabaseItem &item)
{
if ((DatabaseItem::Table != item.m_type) &&
(DatabaseItem::View != item.m_type))
{
return;
}
d_ptr->database = item.m_database;
// for table tableShema
QSqlRecord record = item.m_database.record(item.m_value);
d_ptr->m_shemaModel->removeRows(0, d_ptr->m_shemaModel->rowCount());
d_ptr->m_shemaModel->insertRows(0, record.count());
for (int i = 0; i < record.count(); ++i)
{
QSqlField field = record.field(i);
d_ptr->m_shemaModel->setData(d_ptr->m_shemaModel->index(i, 0), field.name());
d_ptr->m_shemaModel->setData(d_ptr->m_shemaModel->index(i, 1),
field.type() == QVariant::Invalid ? "???" : QString(QVariant::typeToName(field.type())));
d_ptr->m_shemaModel->setData(d_ptr->m_shemaModel->index(i, 2),
(field.length() < 0) ? QVariant("???") : field.length());
d_ptr->m_shemaModel->setData(d_ptr->m_shemaModel->index(i, 3),
(field.precision() < 0) ? QVariant("???") : field.precision());
d_ptr->m_shemaModel->setData(d_ptr->m_shemaModel->index(i, 4),
field.requiredStatus() == QSqlField::Unknown ? "???" : QVariant(bool(field.requiredStatus())));
d_ptr->m_shemaModel->setData(d_ptr->m_shemaModel->index(i, 5), field.isAutoValue());
d_ptr->m_shemaModel->setData(d_ptr->m_shemaModel->index(i, 6), field.defaultValue());
}
d_ptr->m_tableShema->setModel(d_ptr->m_shemaModel.data());
d_ptr->m_tableShema->setEditTriggers(QAbstractItemView::NoEditTriggers);
d_ptr->m_tableShema->resizeColumnsToContents();
d_ptr->m_tab->setTabText(::Scheme, tr(::c_schemeTable).arg(item.m_value));
// for table tableData
QSqlTableModel *modelData = new QSqlTableModel(0, item.m_database);
modelData->setEditStrategy(QSqlTableModel::OnRowChange);
modelData->setTable(item.m_value);
modelData->select();
d_ptr->m_tableData->setModel(modelData);
d_ptr->m_tableData->setEditTriggers(QAbstractItemView::DoubleClicked | QAbstractItemView::EditKeyPressed);
d_ptr->m_tableData->resizeColumnsToContents();
d_ptr->m_tab->setTabText(::Data, tr(::c_dataTable).arg(item.m_value));
d_ptr->m_sqlDataModel.reset(modelData);
}
QDebug operator<<(QDebug dbg, const QSqlField &f)
{
#ifndef Q_BROKEN_DEBUG_STREAM
dbg.nospace() << "QSqlField(" << f.name() << ", " << QVariant::typeToName(f.type());
if (f.length() >= 0)
dbg.nospace() << ", length: " << f.length();
if (f.precision() >= 0)
dbg.nospace() << ", precision: " << f.precision();
if (f.requiredStatus() != QSqlField::Unknown)
dbg.nospace() << ", required: "
<< (f.requiredStatus() == QSqlField::Required ? "yes" : "no");
dbg.nospace() << ", generated: " << (f.isGenerated() ? "yes" : "no");
if (f.typeID() >= 0)
dbg.nospace() << ", typeID: " << f.typeID();
if (!f.defaultValue().isNull())
dbg.nospace() << ", auto-value: \"" << f.defaultValue() << '\"';
dbg.nospace() << ')';
return dbg.space();
#else
qWarning("This compiler doesn't support streaming QSqlField to QDebug");
return dbg;
Q_UNUSED(f);
#endif
}
void Browser::showMetaData(const QString &t)
{
QSqlRecord rec = connectionWidget->currentDatabase().record(t);
QStandardItemModel *model = new QStandardItemModel(table);
model->insertRows(0, rec.count());
model->insertColumns(0, 7);
model->setHeaderData(0, Qt::Horizontal, "Fieldname");
model->setHeaderData(1, Qt::Horizontal, "Type");
model->setHeaderData(2, Qt::Horizontal, "Length");
model->setHeaderData(3, Qt::Horizontal, "Precision");
model->setHeaderData(4, Qt::Horizontal, "Required");
model->setHeaderData(5, Qt::Horizontal, "AutoValue");
model->setHeaderData(6, Qt::Horizontal, "DefaultValue");
for (int i = 0; i < rec.count(); ++i) {
QSqlField fld = rec.field(i);
model->setData(model->index(i, 0), fld.name());
model->setData(model->index(i, 1), fld.typeID() == -1
? QString(QVariant::typeToName(fld.type()))
: QString("%1 (%2)").arg(QVariant::typeToName(fld.type())).arg(fld.typeID()));
model->setData(model->index(i, 2), fld.length());
model->setData(model->index(i, 3), fld.precision());
model->setData(model->index(i, 4), fld.requiredStatus() == -1 ? QVariant("?")
: QVariant(bool(fld.requiredStatus())));
model->setData(model->index(i, 5), fld.isAutoValue());
model->setData(model->index(i, 6), fld.defaultValue());
}
table->setModel(model);
table->setEditTriggers(QAbstractItemView::NoEditTriggers);
updateActions();
}
toQColumnDescriptionList mysqlQuery::describe(QSqlRecord record)
{
ColumnDescriptions.clear();
for (unsigned int i = 0; i < record.count(); i++)
{
toCache::ColumnDescription desc;
desc.AlignRight = false;
desc.Name = record.fieldName(i);
int size = 1;
QSqlField info = record.field(desc.Name);
switch (info.typeID())
{
case FIELD_TYPE_DECIMAL:
desc.Datatype = QString::fromLatin1("DECIMAL");
break;
case FIELD_TYPE_TINY:
desc.Datatype = QString::fromLatin1("TINY");
break;
case FIELD_TYPE_SHORT:
desc.Datatype = QString::fromLatin1("SHORT");
break;
case FIELD_TYPE_LONG:
desc.Datatype = QString::fromLatin1("LONG");
break;
case FIELD_TYPE_FLOAT:
desc.Datatype = QString::fromLatin1("FLOAT");
break;
case FIELD_TYPE_DOUBLE:
desc.Datatype = QString::fromLatin1("DOUBLE");
break;
case FIELD_TYPE_NULL:
desc.Datatype = QString::fromLatin1("NULL");
break;
case FIELD_TYPE_TIMESTAMP:
desc.Datatype = QString::fromLatin1("TIMESTAMP");
break;
case FIELD_TYPE_LONGLONG:
desc.Datatype = QString::fromLatin1("LONGLONG");
break;
case FIELD_TYPE_INT24:
desc.Datatype = QString::fromLatin1("INT23");
break;
case FIELD_TYPE_DATE:
desc.Datatype = QString::fromLatin1("DATE");
break;
case FIELD_TYPE_TIME:
desc.Datatype = QString::fromLatin1("TIME");
break;
case FIELD_TYPE_DATETIME:
desc.Datatype = QString::fromLatin1("DATETIME");
break;
case FIELD_TYPE_YEAR:
desc.Datatype = QString::fromLatin1("YEAR");
break;
case FIELD_TYPE_NEWDATE:
desc.Datatype = QString::fromLatin1("NEWDATE");
break;
case FIELD_TYPE_ENUM:
desc.Datatype = QString::fromLatin1("ENUM");
break;
case FIELD_TYPE_SET:
desc.Datatype = QString::fromLatin1("SET");
break;
case FIELD_TYPE_TINY_BLOB:
desc.Datatype = QString::fromLatin1("TINY_BLOB");
break;
case FIELD_TYPE_MEDIUM_BLOB:
desc.Datatype = QString::fromLatin1("MEDIUM_BLOB");
break;
case FIELD_TYPE_LONG_BLOB:
desc.Datatype = QString::fromLatin1("LONG_BLOB");
break;
case FIELD_TYPE_BLOB:
desc.Datatype = QString::fromLatin1("BLOB");
break;
case FIELD_TYPE_VAR_STRING:
desc.Datatype = QString::fromLatin1("VAR_STRING");
break;
case FIELD_TYPE_STRING:
desc.Datatype = QString::fromLatin1("STRING");
break;
default:
desc.Datatype = QString::fromLatin1("UNKNOWN");
break;
}
if (info.length() > size)
{
desc.Datatype += QString::fromLatin1(" (");
if (info.length() % size == 0)
desc.Datatype += QString::number(info.length() / size);
else
desc.Datatype += QString::number(info.length());
if (info.precision() > 0)
{
desc.Datatype += QString::fromLatin1(",");
desc.Datatype += QString::number(info.precision());
}
desc.Datatype += QString::fromLatin1(")");
}
desc.Null = !info.requiredStatus();
ColumnDescriptions.append(desc);
}
return ColumnDescriptions;
}
void ODBC_Connection::LoadTableColumns(QTreeWidgetItem *item)
{
for (int i = 0, count = item->childCount(); i < count; i++)
item->removeChild(item->child(0));
QString sTableName = item->text(0);
if (m_db.isOpen())
{
if (!m_db.tables().contains(sTableName)) // if it isnt a table, return
return;
// set wait cursor
QApplication::setOverrideCursor(QCursor(Qt::WaitCursor));
QApplication::processEvents();
Logging::getInstance()->WriteLog(INFORMATION, QString("Retrieving tableinfo for table \"%1\" of connection \"%2\"...").arg(sTableName, m_sConnectionName));
bool bTableWarningShown = false;
QSqlRecord records = m_db.record(sTableName);
QSqlField field;
QSqlIndex index = m_db.primaryIndex(sTableName);
QString sName;
QSqlQuery query;
QString sTypeName;
QString sLength;
QString sNullable;
for (int i = 0, count = records.count(); i < count; i++)
{
field = records.field(i);
sName = field.name();
query = m_db.exec(QString("SELECT DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = '%1' AND TABLE_NAME = '%2'").arg(sName, sTableName));
if (query.next())
{
sTypeName = query.value(0).toString();
sLength = query.value(1).toString() != "0" ? query.value(1).toString() : query.value(2).toString();
sNullable = query.value(3).toString();
query.finish();
}
else
{
if (!bTableWarningShown)
{
Logging::getInstance()->WriteLog(WARNING, QString("Couldn't retrieve fieldinfo for table \"%1\" of connection \"%2\", database INFORMATION_SCHEMA.COLUMNS doesn't exist, starting fallback to manual type detection").arg(sTableName, m_sConnectionName));
#ifdef _DEBUG
qDebug() << QString("Couldn't retrieve fieldinfo for table \"%1\" of connection \"%2\", database INFORMATION_SCHEMA.COLUMNS doesn't exist, starting fallback to manual type detection").arg(sTableName, m_sConnectionName);
#endif
bTableWarningShown = true;
}
sTypeName = QVariant::typeToName(field.type());
sLength = QString().setNum(field.length());
sNullable = (field.requiredStatus() == 1 ? "YES" : "NO");
}
bool isPrimaryKey = index.contains(sName) ? true : false;
QTreeWidgetItem *pItem = new QTreeWidgetItem();
if (isPrimaryKey)
{
pItem->setText(0, QString("%1 (PS, %2(%3), %4)").arg(sName, sTypeName, sLength, (sNullable == "YES" ? "NULL" : "NOT NULL")));
pItem->setIcon(0, QIcon(":/ODBC_Query/Resources/primary_key.png"));
}
else
{
pItem->setText(0, QString("%1 (%2(%3), %4)").arg(sName, sTypeName, sLength, (sNullable == "YES" ? "NULL" : "NOT NULL")));
pItem->setIcon(0, QIcon(":/ODBC_Query/Resources/row.png"));
}
item->addChild(pItem);
}
m_ui.TableTreeWidget->expandItem(item);
Logging::getInstance()->WriteLog(INFORMATION, QString("Tableinfo for table \"%1\" of connection \"%2\" retrieved").arg(sTableName, m_sConnectionName));
}
else
{
Logging::getInstance()->WriteLog(ERR, QString("Couldn't retrieve tableinfo for table \"%1\" of connection \"%2\", connection isn't open").arg(sTableName, m_sConnectionName));
#ifdef _DEBUG
qDebug() << QString("Couldn't retrieve tableinfo for table \"%1\" of connection \"%2\", connection isn't open").arg(sTableName, m_sConnectionName);
#endif
}
// set back to arrow cursor
QApplication::restoreOverrideCursor();
}
