CDBProcess::CDBProcess(const QString strType) { __strDbType = strType.toUpper(); QString dbType("SQLITE"); if("SQLITE" == __strDbType) dbType = "QSQLITE"; //QTPLUGIN += qsqlite Q_IMPORT_PLUGIN(QSQLiteDriverPlugin) else if ("MYSQL" == __strDbType) dbType = "QMYSQL"; //QTPLUGIN += qsqlmysql Q_IMPORT_PLUGIN(QMYSQLDriverPlugin) else if ("SQLSERVER" == __strDbType) dbType = "QODBC"; //QTPLUGIN += qsqlodbc Q_IMPORT_PLUGIN(QODBCDriverPlugin) else if("ACCESS" == __strDbType) dbType = "QODBC"; //QTPLUGIN += qsqlodbc Q_IMPORT_PLUGIN(QODBCDriverPlugin) else { dbType = ""; QMessageBox::critical(0, "ERROR", "DB type name invalid!"); return; } m_mapQry.clear(); int m_siConnIdx = 0; while(1) { __strConnName = QString("MyDBProcessConn%1").arg(m_siConnIdx++); QSqlDatabase dbConn = QSqlDatabase::database(__strConnName, false); if(dbConn.isValid())//存在连接 { continue; } m_pDB = new QSqlDatabase(QSqlDatabase::addDatabase(dbType, __strConnName)); break; } }
void TcDatabase::loadEngine(const QString& filename) { m_sqls.clear(); if ( m_handle.isOpen() ) { if ( dbType() == SQLSERVER ) { // 取出存储过程名 QSqlQuery procedures_query = QSqlQuery(m_handle); QSqlQuery query = QSqlQuery(m_handle); if ( procedures_query.exec("exec sp_procedures_rowset2") ) { while(procedures_query.next()) { QSqlRecord procedures = procedures_query.record(); QSqlField fieldSchema = procedures.field("PROCEDURE_SCHEMA"); if ( fieldSchema.isValid() && fieldSchema.value() != "sys" ) { QSqlField field = procedures.field("PROCEDURE_NAME"); if ( field.isValid() ) { SQLContext cnt; QString procedureID = field.value().toString().toLower(); int index = procedureID.indexOf(";"); if ( index >= 0 ) { procedureID = procedureID.left(index); } cnt.id = procedureID; QString paramsLine; if ( query.exec("exec sp_procedure_params_rowset '"+procedureID+"'") ) { while(query.next()) { QSqlRecord paramField = query.record(); QSqlField paramName = paramField.field("PARAMETER_NAME"); QString pname = paramName.value().toString(); if ( pname.compare("@RETURN_VALUE", Qt::CaseInsensitive) != 0 ) { QSqlField paramType = paramField.field("TYPE_NAME"); pname.remove(0, 1); QString ptype = paramType.value().toString().toLower(); if ( ptype == "nvarchar" ) { ptype = "string"; } cnt.params.insert(pname, ptype); paramsLine += ", :" + pname; } } query.finish(); if ( ! paramsLine.isEmpty() ) { paramsLine.remove(0, 2); } } cnt.text = "exec " + procedureID + " " + paramsLine; m_sqls[procedureID] = cnt; Q_EMIT dbiLoading(m_sqls.count(), procedureID); } } } procedures_query.finish(); } }else if ( dbType() == MYSQL ) { // 取出存储过程名 QString sql = "SELECT `name`, `param_list`, `body`, `returns` FROM `mysql`.`proc` WHERE `language` = 'SQL' AND `db` = '"+dbName()+"';"; QSqlQuery query = QSqlQuery(m_handle); if ( query.exec(sql) ) { QSqlField field; while(query.next()) { QSqlRecord procedures = query.record(); QString param_line; SQLContext cnt; cnt.id = procedures.field("name").value().toString(); cnt.params = getParamList(param_line, procedures.field("param_list").value().toString()); cnt.text = "call " + cnt.id + "(" + param_line + ");"; //cnt.text = procedures.field("body").value().toString(); m_sqls[cnt.id] = cnt; Q_EMIT dbiLoading(m_sqls.count(), cnt.id); Q_EMIT dbiLoading(m_sqls.count(), cnt.text); } query.finish(); } }else if ( dbType() == POSTGRESQL ) { // 取出存储过程名 QSqlQuery storedproc_query = QSqlQuery(m_handle); QSqlQuery query = QSqlQuery(m_handle); if ( storedproc_query.exec("SELECT specific_name" " , routine_name" " FROM information_schema.routines" " WHERE specific_schema = 'public'" " AND routine_schema = 'public'" " AND routine_type = 'FUNCTION';") ) { while(storedproc_query.next()) { QSqlRecord storedprocs = storedproc_query.record(); QSqlField fieldspecific = storedprocs.field("specific_name"); QSqlField fieldsqlid = storedprocs.field("routine_name" ); if ( fieldspecific.isValid() && fieldsqlid.isValid() ) { SQLContext cnt; cnt.id = fieldsqlid.value().toString().toLower(); QString paramsLine; if ( query.exec("SELECT parameter_name" " , udt_name" " FROM information_schema.parameters" " WHERE specific_schema = 'public'" " AND specific_name = '"+fieldspecific.value().toString()+"'" " AND parameter_mode = 'IN'" " ORDER BY ordinal_position;") ) { while(query.next()) { QSqlRecord fields = query.record(); QSqlField paramName = fields.field("parameter_name"); QSqlField paramType = fields.field("udt_name" ); if ( paramName.isValid() && paramType.isValid() ) { cnt.params.insert(paramName.value().toString(), paramType.value().toString()); paramsLine += ", :" + paramName.value().toString(); } } query.finish(); if ( ! paramsLine.isEmpty() ) { paramsLine.remove(0, 2); } } cnt.text = "SELECT * FROM " + cnt.id + "(" + paramsLine + ");"; m_sqls[cnt.id] = cnt; Q_EMIT dbiLoading(m_sqls.count(), cnt.id); } } storedproc_query.finish(); } } } QStringList filenames; QDir dir(filename); if ( dir.exists() ) { filenames = dir.entryList(QStringList(), QDir::Files, QDir::Name); for( int i=0; i<filenames.count(); i++ ) { QString ddlfile = filenames.at(i); filenames[i] = dir.absolutePath() + QDir::separator() + ddlfile; } }else { QFileInfo sqlsInfo(filename); if ( sqlsInfo.exists() ) { filenames.append(filename); } } foreach(QString ddlfile, filenames) { QFile f(ddlfile); if ( f.open(QFile::Text | QFile::ReadOnly) ) { m_engineFile = ddlfile; QTextStream in(&f); int rdType = 0; QString sqlId; QString sqlText; QMultiHash<QString, QString> sqlParams; while(!in.atEnd()) { QString line = in.readLine().trimmed(); clearComment(line); if ( line.isEmpty() ) { }else if ( line.startsWith("SQL:", Qt::CaseInsensitive) ) { if ( ! sqlId.isEmpty() && ! sqlText.isEmpty() ) { sqlText.replace("@", ":"); SQLContext cnt; cnt.id = sqlId; cnt.text = sqlText; cnt.params = sqlParams; m_sqls[sqlId] = cnt; Q_EMIT dbiLoading(m_sqls.count(), sqlId); } sqlId = line.mid(4).trimmed().toLower(); sqlText.clear(); sqlParams.clear(); rdType = 1; }else if ( line.startsWith("Params:", Qt::CaseInsensitive) ) { rdType = 2; }else if ( ! sqlId.isEmpty() ) { switch(rdType) { case 1: sqlText += line + "\n"; break; case 2: { QString paramName; QString paramType = "string"; int pos; if ( (pos=line.indexOf(" ")) >0 ) { paramName = line.left(pos); line = line.remove(0, pos).trimmed(); if ( (pos=line.indexOf(" ")) >0 ) { paramType = line.left(pos); }else { paramType = line; } }else { paramName = line; } QChar c = paramName.at(0); if ( c == ':' || c == '@' ) { paramName.remove(0, 1); } sqlParams.insert(paramName, paramType.toLower()); break; } } } } f.close(); if ( ! sqlId.isEmpty() && ! sqlText.isEmpty() ) { SQLContext cnt; cnt.id = sqlId; cnt.text = sqlText; cnt.params = sqlParams; m_sqls[sqlId] = cnt; Q_EMIT dbiLoading(m_sqls.count(), sqlId); } } }
int TcDatabase::open() { int ret = 0; close(); switch(dbType()) { case SQLSERVER: //QSqlDatabase::removeDatabase("FriendSafe"); m_handle = QSqlDatabase::addDatabase("QODBC"); { QString dsn = QString("DRIVER={SQL SERVER};SERVER=%1;DATABASE=%2;").arg(hostName()).arg(dbName()); m_handle.setDatabaseName(dsn); m_handle.setUserName(m_username); m_handle.setPassword(m_password); } ret = m_handle.open() ? 1 : 0; break; case MYSQL: //QSqlDatabase::removeDatabase("qt_sql_default_connection"); m_handle = QSqlDatabase::addDatabase("QMYSQL");//, "qt_sql_default_connection");//, "allowMultiQueries=true"); m_handle.setHostName(hostName()); m_handle.setDatabaseName(dbName()); m_handle.setUserName(m_username); m_handle.setPassword(m_password); //m_handle.setConnectOptions("allowMultiQueries=true"); //m_handle.cloneDatabase() ret = m_handle.open() ? 1 : 0; //DEBUG_OUT(m_handle.connectionName()) //DEBUG_OUT(m_handle.connectionNames().join("\n")) break; case POSTGRESQL: //QSqlDatabase::removeDatabase(""); m_handle = QSqlDatabase::addDatabase("QPSQL"); m_handle.setHostName(hostName()); m_handle.setDatabaseName(dbName()); m_handle.setUserName(m_username); m_handle.setPassword(m_password); //m_handle.setConnectOptions("allowMultiQueries=true"); //m_handle.cloneDatabase() ret = m_handle.open() ? 1 : 0; //DEBUG_OUT(m_handle.connectionName()) //DEBUG_OUT(m_handle.connectionNames().join("\n")) break; case SQLTIE: //QSqlDatabase::removeDatabase("FriendSafe"); m_handle = QSqlDatabase::addDatabase("QSQLITE"); QFileInfo dbFile(m_dbName); QDir dir(dbFile.absoluteDir()); dir.mkpath(dbFile.absolutePath()); m_handle.setDatabaseName(m_dbName); m_handle.setUserName(m_username); m_handle.setPassword(m_password); if ( m_handle.open() ) { m_hostName = "localhost"; m_hostPort = 0; QStringList tables = m_handle.tables(); if ( tables.count() <= 0 ) { // 没有任何表,这个库是新建的。 QFile f(CreateDDL); if ( f.open(QFile::Text | QFile::ReadOnly) ) { QTextStream in(&f); QString fileText = in.readAll(); f.close(); ret = 1; QSqlQuery query = QSqlQuery(m_handle); int pos; while((pos=fileText.indexOf(";")) >-1) { QString sqlText = fileText.mid(0, pos).trimmed(); fileText.remove(0, pos+1); fileText = fileText.trimmed(); QStringList sqlLines = sqlText.split("\n", QString::SkipEmptyParts); QString sql; for( int i=0;i<sqlLines.count();i++ ) { QString s = sqlLines.at(i); if ( ! s.startsWith("--") ) { sql += s + "\n"; } } if ( ! sql.isEmpty() && ! query.exec(sql) ) { ret = -1; } } query.finish(); } }else { ret = 1; } } break; } m_accepted = ret >0; return ret; }