📄 treewidget.cpp
字号:
{
QTreeWidgetItem *item=currentItem();
if(!item)
return;
QString tname=getTableName(item);
insertField(&db,tname);
loadTableName();
}
void TreeWidget::onEditTableName()
{
QTreeWidgetItem *item=currentItem();
if(!item)
return;
QTreeWidgetItem *pitem=item;
while(pitem->parent())
pitem=pitem->parent();
QString tname=pitem->text(0);
QString table_name=getTableName(item);
bool bOK=false;
tname=QInputDialog::getText(this,"输入表名称","输入表名称",QLineEdit::Normal,tname,&bOK);
if(bOK)
{
QString sql=tr("UPDATE dm_table set memo='%1' WHERE upper(table_name)=upper('%2') and connection_id=%3").arg(tname).arg(table_name).arg(connectionSetup.id);
global_db->exec(sql);
item->setText(0,tname);
}
}
void TreeWidget::onEditFieldName()
{
QTreeWidgetItem *item=currentItem();
if(!item)
return;
QTreeWidgetItem *pitem=item;
while(pitem->parent())
pitem=pitem->parent();
QString table_name=pitem->data(0,Qt::UserRole).toString();
QString field_name=item->text(1);
QString memo=item->text(0);
bool bOK=false;
memo=QInputDialog::getText(this,"输入字段名称","输入字段名称",QLineEdit::Normal,memo,&bOK);
if(bOK)
{
QString sql=tr("UPDATE dm_field set memo='%1' WHERE upper(field_name)=upper('%2') and upper(table_name)=upper('%3') and connection_id=%4").arg(memo).arg(field_name).arg(table_name).arg(connectionSetup.id);
global_db->exec(sql);
item->setText(0,memo);
//onEditTable();
}
}
void TreeWidget::onEditTable()
{
QTreeWidgetItem *item=currentItem();
if(!item)
return;
QString tname=getTableName(item);
if(!db.isOpen())
{
if(!reConnect())
return;
}
QStringList list=db.tables(QSql::Tables);
if(!list.contains(tname))
{
int ret = QMessageBox::question(this, "",
"表 "+tname+" 不存在,不能打开!\n是从缓存中删除此表？",
QMessageBox::Yes | QMessageBox::No);
if(ret==QMessageBox::Yes)
{
QString sql=tr("delete from dm_table where connection_id=%1 and upper(table_name)=upper('%2')").arg(connectionSetup.id).arg(tname);
global_db->exec(sql);
sql=tr("delete from dm_field where connection_id=%1 and upper(table_name)=upper('%2')").arg(connectionSetup.id).arg(tname);
global_db->exec(sql);
delete item;
}
return;
}
try{
QString sql;
model=new MySqlTableModel(0,db);
clock_t startTime,endTime;
startTime=clock();
model->connectName=connectName;
model->setTable(tname);
//model->setSort(0,Qt::AscendingOrder);
QApplication::setOverrideCursor(Qt::WaitCursor);
//qDebug()<<"here 1"<<model->tableName();
if(model->selectResult())
{
//qDebug()<<"here 2";
QSqlQuery query(*global_db);
sql=tr("select field_name from dm_field where connection_id=%1 and upper(table_name)=upper('%2') and upper(is_primarykey)='YES'").arg(connectionSetup.id).arg(tname);
QSqlIndex index;
if(query.exec(sql))
{
while(query.next())
{
index.append(model->record().field(query.record().field("field_name").value().toString()));
}
}
if(!index.isEmpty())
{
model->setKeyField(index);
}
setupRelation(model);
model->setEditStrategy(QSqlTableModel::OnManualSubmit);
endTime=clock();
double dseconds=(double)(endTime - startTime)/CLOCKS_PER_SEC;
QString message="编辑表 "+tname +tr(" 检索耗时 %1 秒").arg(dseconds);
emit showMessage(message);
//qDebug()<<"after\n----------------------------------------------";
QTreeWidgetItem *pitem=item;
while(pitem->parent())
pitem=pitem->parent();
QString caption=pitem->text(0);
emit setTableModel(model,caption);
}
else
{
delete model;
model=0;
//emit setTableModel(NULL);
QString message;
message="model select error: "+model->lastError().text();
emit showMessage(message);
}
}
catch(...)
{
QApplication::restoreOverrideCursor();
QMessageBox::warning(this,"",model->lastError().text());
}
QApplication::restoreOverrideCursor();
}
void TreeWidget::onCurrentItemChanged ( QTreeWidgetItem * curItem,QTreeWidgetItem * preItem)
{
if(!curItem)
{
refreshFieldAction->setEnabled(false);
editFieldNameAction->setEnabled(false);
setupAssoAction->setEnabled(false);
setupPrimayKey->setEnabled(false);
emit canEdit(false);
return;
}
if(curItem->parent())
{
editTableNameAction->setEnabled(false);
refreshFieldAction->setEnabled(false);
editFieldNameAction->setEnabled(true);
setupAssoAction->setEnabled(true);
setupPrimayKey->setEnabled(true);
}
else
{
editTableNameAction->setEnabled(true);
refreshFieldAction->setEnabled(true);
editFieldNameAction->setEnabled(false);
setupAssoAction->setEnabled(false);
setupPrimayKey->setEnabled(false);
}
emit canEdit(true);
}
bool TreeWidget::openDatabase()
{
QString message="正在连接 " + connectionSetup.memo + "...";
emit showMessage(message);
//qWarning(message.toLatin1());
if(db.open(connectionSetup.username,connectionSetup.password))
{
if(db.driver() && db.driver()->hasFeature(QSqlDriver::Transactions))
{
db.transaction();
}
message="";
emit showMessage(message);
}
else
{
message=db.lastError().text();
emit showMessage(message);
}
if(connectionSetup.database.toLower()==":memory:")
{
QString sql;
sql="create table TEST(ID INTEGER,NAME varchar,SEX int,constraint pk_test primary key(ID))";
db.exec(sql);
for(int i=0;i<100;i++)
{
sql=tr("insert into test(id,name,sex) values(%1,'test name %2',%3)").arg(i+1).arg(i+1).arg(i%2);
db.exec(sql);
}
sql="create table SEX(ID INTEGER ,NAME varchar(4),constraint pk_sex primary key(id))";
db.exec(sql);
sql="insert into sex(id,name) values(0,'女')";db.exec(sql);
sql="insert into sex(id,name) values(1,'男')";db.exec(sql);
sql=tr("delete from dm_table where connection_id=%1").arg(connectionSetup.id);
global_db->exec(sql);
sql=tr("delete from dm_field where connection_id=%1").arg(connectionSetup.id);
global_db->exec(sql);
insertTable();
sql=tr("update dm_table set memo='测试表' where connection_id=%1 and table_name='TEST'").arg(connectionSetup.id);
global_db->exec(sql);
sql=tr("update dm_field set memo='姓名' where connection_id=%1 and table_name='TEST' and field_name='NAME'").arg(connectionSetup.id);
global_db->exec(sql);
sql=tr("update dm_field set memo='性别',asso_table_name='SEX',asso_field_name='ID',asso_field_display='NAME' where connection_id=%1 and table_name='TEST' and field_name='SEX'").arg(connectionSetup.id);
global_db->exec(sql);
if(db.driver() && db.driver()->hasFeature(QSqlDriver::Transactions))
{
db.commit();
db.transaction();
}
}
return db.isOpen();
}
void TreeWidget::onItemExpanded ( QTreeWidgetItem *item)
{
QTreeWidgetItem *child=item->child(0);
if(child->text(0).length()>0)
return;
QTreeWidgetItem * delItem=item->takeChild(0) ;
if(delItem) delete delItem;
QString tname=getTableName(item);
QSqlQuery queryField(*global_db);
QString sql=tr("select field_name,memo,lower(is_primarykey) as iskey,asso_table_name from dm_field where connection_id=%1 and upper(table_name)=upper('%2') order by field_xh").arg(connectionSetup.id).arg(tname);
QStringList list;
if(queryField.exec(sql))
{
while(queryField.next())
{
QString fieldName=queryField.record().field("field_name").value().toString().trimmed();
QString memo= queryField.record().field("memo").value().toString().trimmed();
QString isKey= queryField.record().field("iskey").value().toString().trimmed();
QString asso_table_name= queryField.record().field("asso_table_name").value().toString().trimmed();
if(memo.isNull()||memo.length()==0)
memo=fieldName;
list.clear();
list<<memo<<fieldName;
QTreeWidgetItem *fieldItem=new QTreeWidgetItem(item,list);
if(isKey=="yes")
fieldItem->setIcon(0,QIcon(":/image/key.png"));
else
fieldItem->setIcon(0,QIcon(":/image/dot.png"));
}
queryField.clear();
}
}
void TreeWidget::setupRelation(MySqlTableModel *tableModel)
{
QSqlQuery query(*global_db);
QString tname=tableModel->tableName();
for(int i=0;i<model->columnCount();i++)
{
QString sql=tr("SELECT field_name,memo,asso_table_name,asso_field_name,asso_field_display,lower(is_primarykey) as iskey FROM dm_field where connection_id=%1 and upper(table_name)=upper('%2') and upper(field_name)=upper('%3')" ).arg(connectionSetup.id).arg(tname).arg(model->headerData(i,Qt::Horizontal).toString());
query.exec(sql);
if(query.isSelect() && query.next())
{
QString field_name=query.record().field("field_name").value().toString().trimmed();
QString memo=query.record().field("memo").value().toString().trimmed();
QString iskey=query.record().field("iskey").value().toString().trimmed();
QString asso_table_name=query.record().field("asso_table_name").value().toString().trimmed();
QString asso_field_name=query.record().field("asso_field_name").value().toString().trimmed();
QString asso_field_display=query.record().field("asso_field_display").value().toString().trimmed();
if(iskey=="yes")
{
model->setHeaderData(i,Qt::Horizontal,QVariant(QIcon(":/image/key.png")),Qt::DecorationRole);
model->setHeaderData(i,Qt::Horizontal,QVariant(1),Qt::ToolTipRole);
//model->setHeaderData(i,Qt::Horizontal,QVariant(QColor(0,0,200)),Qt::TextColorRole);
//continue;
}
else
model->setHeaderData(i,Qt::Horizontal,QVariant(0),Qt::ToolTipRole);
if(asso_table_name.length()>0)
{
model->setHeaderData(i,Qt::Horizontal,QVariant(QIcon(":/image/asso.png")),Qt::DecorationRole);
}
if(memo.length()==0)
memo=field_name;
model->setHeaderData(i,Qt::Horizontal,QVariant(memo));
if(asso_table_name.length()>0)
{
model->setRelation(i, QSqlRelation(asso_table_name, asso_field_name, asso_field_display));
}
}
query.clear();
//qWarning("type:%d\n",model->record().field(i).type());
if(
model->record().field(i).type()==QVariant::Double||
model->record().field(i).type()==QVariant::Int||
model->record().field(i).type()==QVariant::UInt||
model->record().field(i).type()==QVariant::LongLong||
model->record().field(i).type()==QVariant::ULongLong
)
{
model->setHeaderData(i,Qt::Horizontal,QVariant(Qt::AlignRight|Qt::AlignVCenter),Qt::UserRole);
//qWarning("column:%d type:%d\n",i,model->record().field(i).type());
}
}
}
void TreeWidget::onSetupPrimaryKey()
{
QTreeWidgetItem *item=currentItem();
if(!item || !item->parent())
return;
QString table_name=getTableName(item);
QString field_name=item->text(1);
QSqlQuery query(*global_db);
QString sql;
sql=tr("select is_primarykey from dm_field where connection_id=%1 and upper(table_name)=upper('%2') and upper(field_name)=upper('%3')").arg(connectionSetup.id).arg(table_name).arg(field_name);
if(query.exec(sql) && query.next())
{
QString isKey=query.record().field(0).value().toString().trimmed().toLower();
if(isKey=="yes")
sql=tr("UPDATE dm_field set is_primarykey='' WHERE upper(field_name)=upper('%1') and upper(table_name)=upper('%2') and connection_id=%3").arg(field_name).arg(table_name).arg(connectionSetup.id);
else
sql=tr("UPDATE dm_field set is_primarykey='yes' WHERE upper(field_name)=upper('%1') and upper(table_name)=upper('%2') and connection_id=%3").arg(field_name).arg(table_name).arg(connectionSetup.id);
global_db->exec(sql);
if(global_db->lastError().isValid())
QMessageBox::warning(this,"",global_db->lastError().text());
else
{
if(isKey=="yes")
item->setIcon(0,QIcon(":/image/dot.png"));
else
item->setIcon(0,QIcon(":/image/key.png"));
}
}
else
QMessageBox::warning(this,"",query.lastError().text());
}
void TreeWidget::onSetupAsso()
{
QTreeWidgetItem *item=currentItem();
if(!item || !item->parent())
return;
QString table_name=getTableName(item);
QString field_name=item->text(1);
SetupAsso dlg(global_db,connectionSetup.id,table_name,field_name,this);
dlg.exec();
}
void TreeWidget::clearCache()
{
QString sql=tr("delete from dm_field where connection_id=%1").arg(connectionSetup.id);
global_db->exec(sql);
qDebug()<<sql;
sql=tr("delete from dm_table where connection_id=%1").arg(connectionSetup.id);
qDebug()<<sql;
global_db->exec(sql);
insertTable();
loadTableName();
}
void TreeWidget::reInsertField()
{
QString sql=tr("delete from dm_field where connection_id=%1").arg(connectionSetup.id);
qDebug()<<sql;
global_db->exec(sql);
//insertTable();
loadTableName();
}
void TreeWidget::reInsertTable()
{
QString sql=tr("delete from dm_table where connection_id=%1").arg(connectionSetup.id);
qDebug()<<sql;
global_db->exec(sql);
insertTable();
loadTableName();
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -