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;
}
TSqlSchema::TSqlSchema(QObject *parent) : QObject(parent)
{
database = QSqlDatabase::addDatabase("QSQLITE");
database.setDatabaseName("imsit.sqlite");
if(!database.open())
{
currentErrorCode = database.lastError();
qDebug() << "Error code = " << currentErrorCode.text()
<< endl;
}
QString genre = "select * from genre";
QSqlQuery currentQuery(genre);
if(!currentQuery.exec())
{
currentErrorCode = currentQuery.lastError();
qDebug() << "Error code = " << currentErrorCode.text()
<< endl;
}
else
while(currentQuery.next())
{
const QSqlRecord currentRecord = currentQuery.record();
QSqlField genre = currentRecord.field("genre");
qDebug() << genre.value().toString();
}
}
/*!
Updates the widget \a widget with the value from the SQL field it
is mapped to. Nothing happens if no SQL field is mapped to the \a
widget.
*/
void Q3SqlForm::readField(QWidget * widget)
{
sync();
QSqlField * field = 0;
Q3SqlPropertyMap * pmap = (d->propertyMap == 0) ?
Q3SqlPropertyMap::defaultMap() : d->propertyMap;
field = widgetToField(widget);
if(field)
pmap->setProperty(widget, field->value());
}
QString repr(QSqlRecord record) {
QString record_str;
QSqlField field;
for(int i=0; i<record.count(); i++) {
field = record.field(i);
if (field.isNull())
record_str.append(QString("%1 = NULL, ").arg(field.name()));
else
record_str.append(QString("%1 = %2, ").arg(field.name(), field.value().toString()));
}
return record_str;
}
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;
}
/*!
Updates the widgets in the form with current values from the SQL
fields they are mapped to.
*/
void Q3SqlForm::readFields()
{
sync();
QSqlField * f;
QMap< QWidget *, QSqlField * >::Iterator it;
Q3SqlPropertyMap * pmap = (d->propertyMap == 0) ?
Q3SqlPropertyMap::defaultMap() : d->propertyMap;
for(it = d->map.begin() ; it != d->map.end(); ++it){
f = widgetToField(it.key());
if(!f)
continue;
pmap->setProperty(it.key(), f->value());
}
}
QString QTDSDriver::formatValue(const QSqlField &field,
bool trim) const
{
QString r;
if (field.isNull())
r = QLatin1String("NULL");
else if (field.type() == QVariant::DateTime) {
if (field.value().toDateTime().isValid()){
r = field.value().toDateTime().toString(QLatin1String("yyyyMMdd hh:mm:ss"));
r.prepend(QLatin1String("'"));
r.append(QLatin1String("'"));
} else
r = QLatin1String("NULL");
} else if (field.type() == QVariant::ByteArray) {
QByteArray ba = field.value().toByteArray();
QString res;
static const char hexchars[] = "0123456789abcdef";
for (int i = 0; i < ba.size(); ++i) {
uchar s = (uchar) ba[i];
res += QLatin1Char(hexchars[s >> 4]);
res += QLatin1Char(hexchars[s & 0x0f]);
}
r = QLatin1String("0x") + res;
} else {
int Q3SqlCursor::applyPrepared(const QString& q, bool invalidate)
{
int ar = 0;
QSqlQuery* sql = 0;
if (invalidate) {
sql = (QSqlQuery*)this;
d->lastAt = QSql::BeforeFirst;
} else {
sql = d->query();
}
if (!sql)
return 0;
if (invalidate || sql->lastQuery() != q) {
if (!sql->prepare(q))
return 0;
}
int cnt = 0;
int fieldCount = (int)count();
for (int j = 0; j < fieldCount; ++j) {
const QSqlField f = d->editBuffer.field(j);
if (d->editBuffer.isGenerated(j)) {
if (f.type() == QVariant::ByteArray)
sql->bindValue(cnt, f.value(), QSql::In | QSql::Binary);
else
sql->bindValue(cnt, f.value());
cnt++;
}
}
if (sql->exec()) {
ar = sql->numRowsAffected();
}
return ar;
}
static PyObject *meth_QSqlField_value(PyObject *sipSelf, PyObject *sipArgs)
{
PyObject *sipParseErr = NULL;
{
QSqlField *sipCpp;
if (sipParseArgs(&sipParseErr, sipArgs, "B", &sipSelf, sipType_QSqlField, &sipCpp))
{
QVariant *sipRes;
Py_BEGIN_ALLOW_THREADS
sipRes = new QVariant(sipCpp->value());
Py_END_ALLOW_THREADS
return sipConvertFromNewType(sipRes,sipType_QVariant,NULL);
}
}
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:";
}
}
//start id="findrecord"
MetaDataValue MetaDataTable::findRecord(QString fileName) {
using namespace DbUtils;
QFileInfo fi(fileName);
MetaDataObject f;
if (!fi.exists()) return f; /* Return a QObject by value? Don't forget, MetaDataValue is the base class of this particular QObject. */
QString abs = fi.absoluteFilePath();
QSqlDatabase db = DbConnectionSettings::lastSaved();
QString qs = QString("select * from %1 where FileName = \"%2\"")
.arg(m_tableName).arg(escape(abs));
QSqlQuery findQuery(qs);
if (!findQuery.isActive()) {
qDebug() << "Query Failed: " << findQuery.lastQuery()
<< findQuery.lastError().text();
return f;
}
if (!findQuery.first()) return f;
QSqlRecord rec = findQuery.record();
for (int i=rec.count() -1; i >= 0; --i) { /* Properties in QObject map to column names / field values in the table! */
QSqlField field = rec.field(i);
QString key = field.name();
QVariant value = field.value();
if (key == "Preference") {
int v = value.toInt();
Preference p(v);
f.setPreference(p);
}
else if (key == "TrackTime") { /* SQLite has no time type, so we must store as int. */
QTime trackTime;
trackTime = trackTime.addSecs(value.toInt());
f.setTrackTime(trackTime);
}
else {
f.setProperty(key, value); /* Using QObject setProperty for other columns. */
}
}
return f; /* Create a value type from this local stack QObject about to be destroyed. */
}
/*!
Returns a string representation of the \a field value for the
database. This is used, for example, when constructing INSERT and
UPDATE statements.
The default implementation returns the value formatted as a string
according to the following rules:
\list
\i If \a field is character data, the value is returned enclosed
in single quotation marks, which is appropriate for many SQL
databases. Any embedded single-quote characters are escaped
(replaced with two single-quote characters). If \a trimStrings is
true (the default is false), all trailing whitespace is trimmed
from the field.
\i If \a field is date/time data, the value is formatted in ISO
format and enclosed in single quotation marks. If the date/time
data is invalid, "NULL" is returned.
\i If \a field is \link QByteArray bytearray\endlink data, and the
driver can edit binary fields, the value is formatted as a
hexadecimal string.
\i For any other field type, toString() is called on its value
and the result of this is returned.
\endlist
\sa QVariant::toString()
*/
QString QSqlDriver::formatValue(const QSqlField &field, bool trimStrings) const
{
const QLatin1String nullTxt("NULL");
QString r;
if (field.isNull())
r = nullTxt;
else {
switch (field.type()) {
case QVariant::Int:
case QVariant::UInt:
if (field.value().type() == QVariant::Bool)
r = field.value().toBool() ? QLatin1String("1") : QLatin1String("0");
else
r = field.value().toString();
break;
#ifndef QT_NO_DATESTRING
case QVariant::Date:
if (field.value().toDate().isValid())
r = QLatin1Char('\'') + field.value().toDate().toString(Qt::ISODate)
+ QLatin1Char('\'');
else
r = nullTxt;
break;
case QVariant::Time:
if (field.value().toTime().isValid())
r = QLatin1Char('\'') + field.value().toTime().toString(Qt::ISODate)
+ QLatin1Char('\'');
else
r = nullTxt;
break;
case QVariant::DateTime:
if (field.value().toDateTime().isValid())
r = QLatin1Char('\'') +
field.value().toDateTime().toString(Qt::ISODate) + QLatin1Char('\'');
else
r = nullTxt;
break;
#endif
case QVariant::String:
case QVariant::Char:
{
QString result = field.value().toString();
if (trimStrings) {
int end = result.length();
while (end && result.at(end-1).isSpace()) /* skip white space from end */
end--;
result.truncate(end);
}
/* escape the "'" character */
result.replace(QLatin1Char('\''), QLatin1String("''"));
r = QLatin1Char('\'') + result + QLatin1Char('\'');
break;
}
case QVariant::Bool:
if (field.value().toBool())
r = QLatin1String("1");
else
r = QLatin1String("0");
break;
case QVariant::ByteArray : {
if (hasFeature(BLOB)) {
QByteArray ba = field.value().toByteArray();
QString res;
static const char hexchars[] = "0123456789abcdef";
for (int i = 0; i < ba.size(); ++i) {
uchar s = (uchar) ba[i];
res += QLatin1Char(hexchars[s >> 4]);
res += QLatin1Char(hexchars[s & 0x0f]);
}
r = QLatin1Char('\'') + res + QLatin1Char('\'');
break;
}
}
default:
r = field.value().toString();
break;
}
}
return r;
}
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);
}
}
}
