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;
}
