static int compare_recs(const QSqlRecord* buf1, const QSqlRecord* buf2,
const QSqlIndex& idx)
{
int cmp = 0;
int i = 0;
const QString fn(idx.field(i).name());
const QSqlField f1 = buf1->field(fn);
if (f1.isValid()) {
switch (f1.type()) { // ### more types?
case QVariant::String:
cmp = f1.value().toString().trimmed().compare(
buf2->value(fn).toString().trimmed());
break;
default:
if (f1.value().toDouble() < buf2->value(fn).toDouble())
cmp = -1;
else if (f1.value().toDouble() > buf2->value(fn).toDouble())
cmp = 1;
}
}
if (idx.isDescending(i))
cmp = -cmp;
return cmp;
}
QSqlIndex Q3SqlCursor::index(const QStringList& fieldNames) const
{
QSqlIndex idx;
for (QStringList::ConstIterator it = fieldNames.begin(); it != fieldNames.end(); ++it) {
QSqlField f = field((*it));
if (!f.isValid()) { /* all fields must exist */
idx.clear();
break;
}
idx.append(f);
}
return idx;
}
int qtDatabase::populateTable(PARAM *p, int id)
{
int x,y,xmax,ymax;
if(db == NULL)
{
pvStatusMessage(p,255,0,0,"ERROR: qtDatabase::populateTable() db==NULL");
return -1;
}
// set table dimension
xmax = result->record().count();
//ymax = result->size();
ymax = result->numRowsAffected();
pvSetNumRows(p,id,ymax);
pvSetNumCols(p,id,xmax);
// populate table
QSqlRecord record = result->record();
if(record.isEmpty())
{
pvStatusMessage(p,255,0,0,"ERROR: qtDatabase::populateTable() record is empty");
return -1;
}
for(x=0; x<xmax; x++)
{ // write name of fields
pvSetTableText(p, id, x, -1, (const char *) record.fieldName(x).toUtf8());
}
result->next();
for(y=0; y<ymax; y++)
{ // write fields
QSqlRecord record = result->record();
for(x=0; x<xmax; x++)
{
QSqlField f = record.field(x);
if(f.isValid())
{
QVariant v = f.value();
pvSetTableText(p, id, x, y, (const char *) v.toString().toUtf8());
}
else
{
pvSetTableText(p, id, x, y, "ERROR:");
}
}
result->next();
}
return 0;
}
/*!
Returns an SQL \c{ORDER BY} clause based on the currently set
sort order.
\sa setSort(), selectStatement()
*/
QString QSqlTableModel::orderByClause() const
{
Q_D(const QSqlTableModel);
QSqlField f = d->rec.field(d->sortColumn);
if (!f.isValid())
return QString();
//we can safely escape the field because it would have been obtained from the database
//and have the correct case
QString field = d->db.driver()->escapeIdentifier(f.name(), QSqlDriver::FieldName);
field.prepend(QLatin1Char('.')).prepend(d->tableName);
field = d->sortOrder == Qt::AscendingOrder ? Sql::asc(field) : Sql::desc(field);
return Sql::orderBy(field);
}
/*!
Returns an SQL \c{ORDER BY} clause based on the currently set
sort order.
\sa setSort(), selectStatement()
*/
QString QSqlTableModel::orderByClause() const
{
Q_D(const QSqlTableModel);
QString s;
QSqlField f = d->rec.field(d->sortColumn);
if (!f.isValid())
return s;
QString table = d->db.driver()->escapeIdentifier(d->tableName, QSqlDriver::TableName);
QString field = d->db.driver()->escapeIdentifier(f.name(), QSqlDriver::FieldName);
s.append(QLatin1String("ORDER BY ")).append(table).append(QLatin1Char('.')).append(field);
s += d->sortOrder == Qt::AscendingOrder ? QLatin1String(" ASC") : QLatin1String(" DESC");
return s;
}
/*!
Returns an SQL \c{ORDER BY} clause based on the currently set
sort order.
\sa setSort(), selectStatement()
*/
QString QSqlTableModel::orderByClause() const
{
Q_D(const QSqlTableModel);
QString s;
QSqlField f = d->rec.field(d->sortColumn);
if (!f.isValid())
return s;
QString table = d->tableName;
//we can safely escape the field because it would have been obtained from the database
//and have the correct case
QString field = d->db.driver()->escapeIdentifier(f.name(), QSqlDriver::FieldName);
s.append(QLatin1String("ORDER BY ")).append(table).append(QLatin1Char('.')).append(field);
s += d->sortOrder == Qt::AscendingOrder ? QLatin1String(" ASC") : QLatin1String(" DESC");
return s;
}
const char *qtDatabase::recordFieldValue(PARAM *p, int x)
{
QSqlRecord record = result->record();
if(record.isEmpty())
{
pvStatusMessage(p,255,0,0,"ERROR: qtDatabase::recordFieldValue(%d) record is empty", x);
return "ERROR:";
}
QSqlField f = record.field(x);
if(f.isValid())
{
QVariant v = f.value();
return v.toString().toUtf8();
}
else
{
pvStatusMessage(p,255,0,0,"ERROR: qtDatabase::recordFieldValue(%d) field is invalid", x);
return "ERROR:";
}
}
static QSqlIndex indexFromStringList(const QStringList& l, const Q3SqlCursor* cursor)
{
QSqlIndex newSort;
for (int i = 0; i < l.count(); ++i) {
QString f = l[i];
bool desc = false;
if (f.mid(f.length()-3) == QLatin1String("ASC"))
f = f.mid(0, f.length()-3);
if (f.mid(f.length()-4) == QLatin1String("DESC")) {
desc = true;
f = f.mid(0, f.length()-4);
}
int dot = f.lastIndexOf(QLatin1Char('.'));
if (dot != -1)
f = f.mid(dot+1);
const QSqlField field = cursor->field(f.trimmed());
if (field.isValid())
newSort.append(field, desc);
else
qWarning("QSqlIndex::indexFromStringList: unknown field: '%s'", f.latin1());
}
return newSort;
}
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);
}
}
}
