treewidget.cpp

跨平台,多数据库查看编辑和查询工具,平台支持windows,linux,数据库支持oracle,odbc,mysql,slite等

#include <QtSql>
#include <QtDebug>

#include "treewidget.h"
#include "setupasso.h"
#include <sys/types.h>
#include <time.h>

extern QString g_connectName;

TreeWidget::TreeWidget(QWidget *parent ):QTreeWidget(parent)
{
	//setAttribute(Qt::WA_DeleteOnClose);
	//global_db=QSqlDatabase::database(g_connectName);
	
	
	model=0;
	setColumnCount(2);
	header()->setResizeMode(QHeaderView::ResizeToContents);
	setContextMenuPolicy(Qt::ActionsContextMenu);
	connect(this,SIGNAL(itemDoubleClicked ( QTreeWidgetItem *, int)),this,SLOT(doubleClick( QTreeWidgetItem *, int)));
	
	QMenu *menuCache=new QMenu(this);
	QAction *action;
	
	action=new QAction(QIcon(":/image/table_edit.png"),"编辑表内容",this);	
	connect(action,SIGNAL(triggered()),this,SLOT(onEditTable()));
	addAction(action);

	action=new QAction(QIcon(":/image/refresh.png"),"清除缓存",this);	
	connect(action,SIGNAL(triggered()),this,SLOT(clearCache()));
	menuCache->addAction(action);

	action=new QAction(QIcon(":/image/refresh.png"),"刷新表名称缓存",this);	
	connect(action,SIGNAL(triggered()),this,SLOT(insertTable()));
	menuCache->addAction(action);

	action=new QAction(QIcon(":/image/refresh.png"),"重建表缓存",this);	
	connect(action,SIGNAL(triggered()),this,SLOT(reInsertTable()));
	menuCache->addAction(action);

	action=new QAction("编辑表名称",this);	
	connect(action,SIGNAL(triggered()),this,SLOT(onEditTableName()));
	addAction(action);
	editTableNameAction=action;
	

	action=new QAction(QIcon(":/image/refresh.png"),"刷新字段缓存",this);	
	connect(action,SIGNAL(triggered()),this,SLOT(onInsertField()));	
	refreshFieldAction=action;
	menuCache->addAction(refreshFieldAction);
	
	action=new QAction(QIcon(":/image/refresh.png"),"重建字段缓存",this);	
	connect(action,SIGNAL(triggered()),this,SLOT(reInsertField()));
	menuCache->addAction(action);

	//refreshFieldAction->setEnabled(false);

	action=new QAction(this);
  action->setSeparator(true);
  addAction(action);

	action=new QAction("编辑字段名称...",this);	
	connect(action,SIGNAL(triggered()),this,SLOT(onEditFieldName()));
	addAction(action);
	editFieldNameAction=action;

	action=new QAction(QIcon(":/image/key.png"),"主键设置与取消",this);	
	connect(action,SIGNAL(triggered()),this,SLOT(onSetupPrimaryKey()));
	addAction(action);
	setupPrimayKey=action;

	action=new QAction(QIcon(":/image/asso.png"),"设置关联属性...",this);	
	connect(action,SIGNAL(triggered()),this,SLOT(onSetupAsso()));
	addAction(action);
	setupAssoAction=action;
	
	action=new QAction(this);
  action->setSeparator(true);
  addAction(action);

	action=new QAction("重新连接",this);
	connect(action,SIGNAL(triggered()),this,SLOT(reConnect()));
	addAction(action);

	action=new QAction(this);
  action->setSeparator(true);
  addAction(action);
	
	
	
	
	action=new QAction("缓存管理",this);
  action->setMenu(menuCache);
  addAction(action);
	
	action=new QAction(this);
  action->setSeparator(true);
  addAction(action);

	action=new QAction(QIcon(":/image/refresh.png"),"刷新列表",this);
	connect(action,SIGNAL(triggered()),this,SLOT(loadTableName()));
	addAction(action);



  connect(this,SIGNAL(currentItemChanged(QTreeWidgetItem * , QTreeWidgetItem * )),this,SLOT(onCurrentItemChanged ( QTreeWidgetItem * ,QTreeWidgetItem * ) ));
  connect(this,SIGNAL(itemExpanded ( QTreeWidgetItem *)),this,SLOT(onItemExpanded ( QTreeWidgetItem *)));
}

bool TreeWidget::reConnect()
{
		setConnectionSetup(global_db,connectionSetup);
		return db.isOpen();

}

TreeWidget::~TreeWidget()
{
	if(model)
	{
		//model->submitAll();
		//delete model;
		//model=0;
	}
	closeDb();

}


void TreeWidget::setConnectionSetup(QSqlDatabase *gdb,ConnectionSetup cs)
{
	global_db=gdb;

	connectionSetup.id=cs.id;
	connectionSetup.type=cs.type;
	connectionSetup.server=cs.server;
	connectionSetup.database=cs.database;
	connectionSetup.username=cs.username;
	connectionSetup.password=cs.password;
	connectionSetup.memo=cs.memo;
	
	
	connectName=tr("dm_connect_%1").arg(connectionSetup.id);	
	//qWarning(connectName.toLatin1());
	//QSqlDatabase::removeDatabase(connectName);
	db=QSqlDatabase::addDatabase(connectionSetup.type,connectName);
	db.setHostName(connectionSetup.server);
	db.setDatabaseName(connectionSetup.database);
	
	//qWarning(connectName.toLatin1());

	openDatabase();

}

void TreeWidget::showEvent ( QShowEvent * event ) 
{
	if(topLevelItemCount()==0)
	  loadTableName();	
	emit onShow( connectName);
	
	bool bEdit;
	if(currentItem())
		bEdit=true;
	else
		bEdit=false;
	emit canEdit(bEdit);
}

void TreeWidget::doubleClick( QTreeWidgetItem *item , int col)
{
	//onEditTable();
}

void TreeWidget::loadTableName()
{
	clear();
	QString sql;
	
	
	if(!global_db->isOpen())
		return;
		
	QSqlQuery query(*global_db);
	
	if(!db.isOpen())
		openDatabase();
	if(!db.isOpen())
		return;
		
	//QApplication::setOverrideCursor(Qt::WaitCursor);
 	QString message="检索表......";
 	emit showMessage(message);
 	
 	QStringList list=db.tables(QSql::Tables);
 		
 	QStringList tableList;
 	sql=tr("SELECT table_name FROM dm_table where connection_id=%1 ").arg(connectionSetup.id);
 	if(query.exec(sql))
 	{
 		while(query.next())
 		   tableList.append(query.record().field(0).value().toString());
 		query.clear();
 	}
 	
 	
 	foreach(QString tname,list)
 	{
 		if(tname.contains("."))
 			continue;
 		if(!tableList.contains(tname))  //是新表,缓存中没有
 		{
 			//qDebug()<<tname;
 			insertTable(&db,tname);
 		} 		
 	}
 	
	sql=tr("SELECT count(*) FROM dm_field WHERE connection_id=%1").arg(connectionSetup.id);
	if(query.exec(sql) && query.next())
	{
		if(query.record().field(0).value().toInt()==0)
		{
			insertField();
		}
		query.clear();
	}

	  sql=tr("SELECT table_name,memo as display FROM dm_table where connection_id=%1 order by table_name ").arg(connectionSetup.id);
		//qWarning(sql.toLatin1().data());
		if(query.exec(sql))
		{
			while(query.next())
		  {
		  	QStringList list;
		  	QString tableName=query.record().field(0).value().toString().trimmed();
		  	QString display=query.record().field(1).value().toString().trimmed();
		  	if(display.length()==0)
		  		display=tableName;
		  	list<<display<<tableName;
			  QTreeWidgetItem *item=new QTreeWidgetItem(this,list);
			  item->setData(0,Qt::UserRole,QVariant(tableName));
			  item->setIcon(0,QIcon(":/image/table.png"));
			  item->setToolTip(0,tableName);
			  insertTopLevelItem(topLevelItemCount(),item);
			  item->insertChild(0,new QTreeWidgetItem(item));
		  }		  
		}		
 	  message="";
 	  emit showMessage(message);
 	  query.clear();

		//QApplication::restoreOverrideCursor();    
}

void TreeWidget::closeDb()
{
	clear();
	if(db.driver() && db.driver()->hasFeature(QSqlDriver::Transactions))
	{
		db.commit();
	}

	if(db.driver()) db.driver()->close();
	db.close();

	//QSqlDatabase::removeDatabase(connectName);	
}

void TreeWidget::insertTable(QSqlDatabase *dbname,QString tableName)
{
	QString sql;
	if(!global_db->isOpen())
		return;
		
	if(!dbname->isOpen())
		return;

	QSqlQuery query(*global_db);
	

	QApplication::setOverrideCursor(Qt::WaitCursor);
 	QString message="检索表和字段......";
 	emit showMessage(message);
	
	  QStringList list=dbname->tables(QSql::Tables);
		foreach(QString tname,list)
		{
			if(tableName.length()>0)
			{
				if(tableName!=tname)
					continue;
			}
			if(!tname.contains("."))
			{
				sql=tr("SELECT count(*) FROM dm_table WHERE connection_id=%1 and upper(table_name)=upper('%2')").arg(connectionSetup.id).arg(tname);
				if(query.exec(sql) && query.next())
				{
					if(query.record().field(0).value().toInt()==0)
					{					
				    sql=tr("INSERT INTO dm_table(connection_id,table_name) values(%1,'%2')").arg(connectionSetup.id).arg(tname);				
				    global_db->exec(sql);
				    insertField(dbname,tname);
				  }
			  }				
		  }
	  }
	  
 	  message="";
 	  emit showMessage(message);
		QApplication::restoreOverrideCursor();    
}

void TreeWidget::insertTable()
{
	if(!db.isOpen())
		openDatabase();
	insertTable(&db);
	loadTableName();

}

void TreeWidget::insertField()
{
	QString sql;
	if(!global_db->isOpen())
		return;

	QSqlQuery query(*global_db);
	QSqlQuery query2(*global_db);
	
	
	if(!db.isOpen())
		openDatabase();

	QApplication::setOverrideCursor(Qt::WaitCursor);
 	QString message="检索表和字段......";
 	emit showMessage(message);
	
	sql=tr("SELECT table_name FROM dm_table WHERE connection_id=%1").arg(connectionSetup.id);
	if(query.exec(sql))
	{
		while(query.next())
		{
			QString tname=query.record().field(0).value().toString();
			insertField(&db,tname);
	  }
	}
	  
		
 	  message="";
 	  emit showMessage(message);
		QApplication::restoreOverrideCursor();    
	query.clear();
	
	
}

void TreeWidget::insertField(QSqlDatabase *dbname,QString tname)
{
	QString sql;
	if(!global_db->isOpen())
		return;

	if(!dbname->isOpen())
		return;

	QSqlQuery query(*global_db);
	QSqlQuery query2(*global_db);
	
	

	QApplication::setOverrideCursor(Qt::WaitCursor);
 	QString message="检索表和字段......";
 	emit showMessage(message);
	
	QSqlRecord record = dbname->record(tname);
	 	  for (int i = 0; i < record.count(); i++) 
		  {		  	
	   	  	QString fieldName=record.field(i).name();
	   	  	sql=tr("SELECT count(*) 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(fieldName);
				  if(query2.exec(sql) && query2.next())
				  {
  					if(query2.record().field(0).value().toInt()==0)
  					{
	   	  	    sql=tr("INSERT INTO dm_field(connection_id,table_name,field_name,field_xh) values(%1,'%2','%3',%4)").arg(connectionSetup.id).arg(tname).arg(fieldName).arg(i+1);
	   	  	    //qWarning(sql.toLatin1().data());
	   	  	    //qApp->processEvents();
	   	  	    global_db->exec(sql);
	   	  	    if(global_db->lastError().isValid())
	   	  	    {
	   	  	    	message=global_db->lastError().text();
	   	  	    	emit showMessage(message);
	   	  	    	break;
	   	  	    }
	   	      }
	   	      query2.clear();
	   	    
	   	    }
		  }
		
		if(!global_db->lastError().isValid())
		{
 	    message="";
 	    emit showMessage(message);
 	  }
 	  
 	 
	QSqlIndex index=dbname->primaryIndex(tname);
	if(!index.isEmpty())
	{
		for(int i=0;i<index.count();i++)
		{
			sql=tr("update dm_field set is_primarykey='yes',primarykey_name='%1' where connection_id=%2 and upper(table_name)=upper('%3') and upper(field_name)=upper('%4')").arg(index.name()).arg(connectionSetup.id).arg(tname).arg(index.fieldName(i));
			//qWarning(sql.toLatin1().data());
			global_db->exec(sql);
		}
	}
	

		QApplication::restoreOverrideCursor();    
	query.clear();
}

QString TreeWidget::getTableName(QTreeWidgetItem *item)
{
	QString tname;
	QTreeWidgetItem *pitem=item;
	while(pitem->parent())
	      pitem=pitem->parent();
	tname=pitem->data(0,Qt::UserRole).toString();
	return tname;
}

void TreeWidget::onInsertField()