コード例 #1
0
ファイル: dbfunc.cpp プロジェクト: RudolfCardinal/camcops
void DbFunc::changeColumnTypes(const QSqlDatabase& db,
                               const QString& tablename,
                               const QList<QPair<QString, QString>>& changes,
                               const QString& tempsuffix)
{
    // changes: pairs <fieldname, newtype>
    if (!tableExists(db, tablename)) {
        qWarning() << "WARNING: ignoring changeColumnTypes for non-existent "
                      "table:" << tablename;
        return;
    }
    QString dummytable = tablename + tempsuffix;
    if (tableExists(db, dummytable)) {
        UiFunc::stopApp("DbFunc::changeColumnTypes: temporary table exists: " +
                        dummytable);
    }
    QList<SqlitePragmaInfoField> infolist = getPragmaInfo(db, tablename);
    qDebug() << "changeColumnTypes";
    qDebug() << "- pragma info:" << infolist;
    qDebug() << "- changes:" << changes;
    int n_changes = 0;
    for (int i = 0; i < changes.size(); ++i) {
        QString changefield = changes.at(i).first;
        for (int j = 0; i < infolist.size(); ++j) {
            SqlitePragmaInfoField& info = infolist[j];
            if (changefield.compare(info.name, Qt::CaseInsensitive) == 0) {
                QString newtype = changes.at(i).second;
                info.type = newtype;
                ++n_changes;
            }
        }
    }
    if (n_changes == 0) {
        qDebug() << "... nothing to do";
        return;
    }
    QString creation_sql = makeCreationSqlFromPragmaInfo(tablename, infolist);
    QString fieldnames = fieldNamesFromPragmaInfo(infolist, true).join(",");
    QString delimited_tablename = delimit(tablename);
    QString delimited_dummytable = delimit(dummytable);
    exec(db, "BEGIN TRANSACTION");
    exec(db, QString("ALTER TABLE %1 RENAME TO %2").arg(delimited_tablename,
                                                        delimited_dummytable));
    exec(db, creation_sql);  // make a new clean table
    exec(db, QString("INSERT INTO %1 (%2) SELECT %3 FROM %4").arg(
         delimited_tablename,
         fieldnames,
         fieldnames,
         delimited_dummytable));
    exec(db, QString("DROP TABLE %1").arg(delimited_dummytable));
    commit(db);
}
コード例 #2
0
ファイル: joiner.c プロジェクト: bowhan/kent
struct joinerPair *joinerRelate(struct joiner *joiner, char *database, 
	char *table)
/* Get list of all ways to link table in given database to other tables,
 * possibly in other databases. */
{
struct joinerSet *js, *jsChain;
struct joinerField *jf, *jfBase;
struct joinerPair *jpList = NULL, *jp;
struct slRef *chainList, *chainEl;
/* Return list of self, children, and parents (but not siblings) */

#ifdef SCREWS_UP_SPLITS
if (!tableExists(database, table, NULL))
    errAbort("%s.%s - table doesn't exist", database, table);
#endif

for (js = joiner->jsList; js != NULL; js = js->next)
    {
    if ((jfBase = joinerSetIncludesTable(js, database, table)) != NULL)
        {
	chainList = joinerSetInheritanceChain(js);
	for (chainEl = chainList; chainEl != NULL; chainEl = chainEl->next)
	    {
	    jsChain = chainEl->val;
	    for (jf = jsChain->fieldList; jf != NULL; jf = jf->next)
		{
		struct slName *db;
		for (db = jf->dbList; db != NULL; db = db->next)
		    {
		    if (joinerExclusiveCheck(joiner, database, db->name))
			{
			if (!sameString(database, db->name) 
				|| !sameString(table, jf->table))
			    {
			    if (tableExists(db->name, jf->table,
					    jf->splitPrefix))
				{
				jp = joinerToField(database, jfBase, 
					db->name, jf, jsChain); 
				slAddHead(&jpList, jp);
				}
			    }
			}
		    }
		}
	    }
	slFreeList(&chainList);
	}
    }
slReverse(&jpList);
return jpList;
}
コード例 #3
0
ファイル: dbfunc.cpp プロジェクト: RudolfCardinal/camcops
void DbFunc::renameTable(const QSqlDatabase& db, const QString& from,
                         const QString& to)
{
    if (!tableExists(db, from)) {
        qWarning() << Q_FUNC_INFO
                   << "WARNING: ignoring renameTable for non-existent table:"
                   << from;
        return;
    }
    if (tableExists(db, to)) {
        UiFunc::stopApp("DbFunc::renameTable: destination table already "
                        "exists: " + to);
    }
    // http://stackoverflow.com/questions/426495
    exec(db, QString("ALTER TABLE %1 RENAME TO %2").arg(from, to));
    // don't COMMIT (error: "cannot commit - no transaction is active")
}
コード例 #4
0
ファイル: newproject.cpp プロジェクト: uesoft/AutoIPED
////////////////////////////////////////////////////////////////////////
//
// 从删除指定的工程
//
// TableName[in]	表名
// ProjectID[in]	工程名
//
// 函数成功返回TRUE,否则返回FALSE
//
BOOL newproject::DeleteProjectInTable(LPCTSTR TableName,LPCTSTR ProjectID)
{
    CString SQL;
    _RecordsetPtr IRecordset;
    HRESULT hr;
    hr=IRecordset.CreateInstance(__uuidof(Recordset));

    if(FAILED(hr))
    {
        ReportExceptionError(_com_error(hr));
        return FALSE;
    }

    //
    _ConnectionPtr pTemp;
    if ( tableExists( theApp.m_pConnection, TableName ) )
    {
        pTemp = theApp.m_pConnection;
    }
    else
    {
        pTemp = theApp.m_pConAllPrj;
    }

    if(TableName==NULL || ProjectID==NULL)
    {
        return FALSE;
    }


    try
    {
        SQL.Format(_T("SELECT * FROM %s WHERE EnginID='%s'"),TableName,ProjectID);

        IRecordset->Open(_variant_t(SQL),_variant_t((IDispatch*)pTemp),
                         adOpenDynamic,adLockOptimistic,adCmdText);

        if(IRecordset->adoEOF && IRecordset->BOF)
        {
            return TRUE;
        }

        IRecordset->Close();

        SQL.Format(_T("DELETE * FROM %s WHERE EnginID='%s'"),TableName,ProjectID);
        pTemp->Execute(_bstr_t(SQL),NULL,adCmdText);
    }
    catch(_com_error &e)
    {
        ReportExceptionError(e);
        return FALSE;
    }

    return TRUE;
}
コード例 #5
0
int32_t DatabaseManager::getDatabaseVersion()
{
	if (!tableExists("server_config")) {
		Database* db = Database::getInstance();
		db->executeQuery("CREATE TABLE `server_config` (`config` VARCHAR(50) NOT nullptr, `value` VARCHAR(256) NOT nullptr DEFAULT '', UNIQUE(`config`)) ENGINE = InnoDB");
		db->executeQuery("INSERT INTO `server_config` VALUES ('db_version', 0)");
		return 0;
	}

	int32_t version = 0;
	if (getDatabaseConfig("db_version", version)) {
		return version;
	}
	return -1;
}
コード例 #6
0
ファイル: dbfunc.cpp プロジェクト: RudolfCardinal/camcops
bool DbFunc::createIndex(const QSqlDatabase& db, const QString& indexname,
                         const QString& tablename, QStringList fieldnames)
{
    if (!tableExists(db, tablename)) {
        qWarning() << "WARNING: ignoring createIndex for non-existent table:"
                   << tablename;
        return false;
    }
    for (int i = 0; i < fieldnames.size(); ++i) {
        fieldnames[i] = delimit(fieldnames[i]);
    }
    QString sql = QString("CREATE INDEX IF NOT EXISTS %1 ON %2 (%3)").arg(
        delimit(indexname), delimit(tablename), fieldnames.join(""));
    return exec(db, sql);
}
コード例 #7
0
ファイル: eventsql.c プロジェクト: alisheikh/hornet
static void testTableExists(sqlite3 *db)
{
  size_t i;
  int defects = 0;
  int tests = 0;
  for (i=0; i < (sizeof(tableTests)/sizeof(tableTests[0])); ++i) {
    const char *name = tableTests[i].name;
    int exists = tableExists(db, name);
    if (exists)
      evlog(LOG_OFTEN, "Table exists: %s", name);
    else 
      evlog(LOG_OFTEN, "Table does not exist: %s", name);
    if (exists != tableTests[i].exists) {
      evlog(LOG_ALWAYS, "tableExists test failed on table %s", name);
      ++ defects;
    }
    ++ tests;
  }
  evlog(LOG_ALWAYS, "%d tests, %d defects found.", tests, defects);
}
コード例 #8
0
ファイル: databasemanager.cpp プロジェクト: CkyLua/tfs
int32_t DatabaseManager::getDatabaseVersion()
{
	if(!tableExists("server_config"))
	{
		Database* db = Database::getInstance();
		if(db->getDatabaseEngine() == DATABASE_ENGINE_MYSQL)
			db->executeQuery("CREATE TABLE `server_config` (`config` VARCHAR(50) NOT NULL, `value` VARCHAR(256) NOT NULL DEFAULT '', UNIQUE(`config`)) ENGINE = InnoDB;");
		else
			db->executeQuery("CREATE TABLE `server_config` (`config` VARCHAR(50) NOT NULL, `value` VARCHAR(256) NOT NULL DEFAULT '', UNIQUE(`config`));");

		db->executeQuery("INSERT INTO `server_config` VALUES ('db_version', 0);");
		return 0;
	}

	int32_t version = 0;
	if(getDatabaseConfig("db_version", version))
		return version;

	return -1;
}
コード例 #9
0
QgsAuxiliaryLayer *QgsAuxiliaryStorage::createAuxiliaryLayer( const QgsField &field, QgsVectorLayer *layer ) const
{
  QgsAuxiliaryLayer *alayer = nullptr;

  if ( mValid && layer )
  {
    const QString table( layer->id() );
    spatialite_database_unique_ptr database;
    database = openDB( currentFileName() );

    if ( !tableExists( table, database.get() ) )
    {
      if ( !createTable( field.typeName(), table, database.get() ) )
      {
        return alayer;
      }
    }

    alayer = new QgsAuxiliaryLayer( field.name(), currentFileName(), table, layer );
    alayer->startEditing();
  }

  return alayer;
}
コード例 #10
0
ファイル: dbfunc.cpp プロジェクト: RudolfCardinal/camcops
void DbFunc::createTable(const QSqlDatabase& db, const QString& tablename,
                         const QList<Field>& fieldlist,
                         const QString& tempsuffix)
{
    QString creation_sql = sqlCreateTable(tablename, fieldlist);
    if (!tableExists(db, tablename)) {
        // Create table from scratch.
        exec(db, creation_sql);
        return;
    }

    // Otherwise, it's a bit more complex...

    // 1. Create a list of plans. Start with the fields we want, which we
    //    will add (unless later it turns out they exist already).
    QList<FieldCreationPlan> planlist;
    QStringList goodfieldlist;
    for (int i = 0; i < fieldlist.size(); ++i) {
        const Field& field = fieldlist.at(i);
        FieldCreationPlan p;
        p.name = field.name();
        p.intended_field = &field;
        p.add = true;
        planlist.append(p);
        goodfieldlist.append(delimit(p.name));
    }

    // 2. Fetch a list of existing fields.
    // - If any are in our "desired" list, and we didn't know they were in
    //   the database, don't add them (but maybe change them if we want them
    //   to have a different type).
    // - If they're not in our "desired" list, then they're superfluous, so
    //   aim to drop them.
    QList<SqlitePragmaInfoField> infolist = getPragmaInfo(db, tablename);
    for (int i = 0; i < infolist.size(); ++i) {
        const SqlitePragmaInfoField& info = infolist.at(i);
        bool existing_is_superfluous = true;
        for (int j = 0; j < planlist.size(); ++j) {
            FieldCreationPlan& plan = planlist[j];
            const Field* intended_field = plan.intended_field;
            if (!intended_field) {
                // This shouldn't happen!
                continue;
            }
            if (!plan.exists_in_db && intended_field->name() == info.name) {
                plan.exists_in_db = true;
                plan.add = false;
                plan.change = (
                    info.type != intended_field->sqlColumnType() ||
                    info.notnull != intended_field->allowsNull() ||
                    info.pk != intended_field->isPk()
                );
                plan.existing_type = info.type;
                plan.existing_not_null = info.notnull;
                existing_is_superfluous = false;
            }
        }
        if (existing_is_superfluous) {
            FieldCreationPlan plan;
            plan.name = info.name;
            plan.exists_in_db = true;
            plan.existing_type = info.type;
            plan.drop = true;
            planlist.append(plan);
        }
    }

    // 3. For any fields that require adding: add them.
    //    For any that require dropping or altering, make a note for the
    //    complex step.
    bool drop_or_change_mods_required = false;
    for (int i = 0; i < planlist.size(); ++i) {
        const FieldCreationPlan& plan = planlist.at(i);
        if (plan.add && plan.intended_field) {
            if (plan.intended_field->isPk()) {
                UiFunc::stopApp(QString(
                    "DbFunc::createTable: Cannot add a PRIMARY KEY column "
                    "(%s.%s)").arg(tablename, plan.name));
            }
            exec(db, QString("ALTER TABLE %1 ADD COLUMN %2 %3").arg(
                tablename,
                delimit(plan.name),
                plan.intended_field->sqlColumnDef()));
        }
        if (plan.drop || plan.change) {
            drop_or_change_mods_required = true;
        }
    }

    /*
    qDebug() << Q_FUNC_INFO
             << "tablename:" << tablename
             << "goodfieldlist:" << goodfieldlist
             << "infolist:" << infolist
             << "modifications_required:" << drop_or_change_mods_required
             << "plan:" << planlist;
    */

    if (!drop_or_change_mods_required) {
        qDebug() << "Table" << tablename
                 << "OK; no drop/change alteration required";
        return;
    }

    // 4. Implement drop/change modifications (via a temporary table).
    qDebug().nospace() << "Amendment plan for " << tablename
                       << ": " << planlist;
    // Deleting columns: http://www.sqlite.org/faq.html#q11
    // ... also http://stackoverflow.com/questions/8442147/
    // Basically, requires (a) copy data to temporary table; (b) drop original;
    // (c) create new; (d) copy back.
    // Or, another method: (a) rename table; (b) create new; (c) copy data
    // across; (d) drop temporary.
    // We deal with fields of incorrect type similarly (in this case, any
    // conversion occurs as we SELECT back the values into the new, proper
    // fields). Not sure it really is important, though:
    // http://sqlite.org/datatype3.html
    QString dummytable = tablename + tempsuffix;
    if (tableExists(db, dummytable)) {
        UiFunc::stopApp("DbFunc::createTable: temporary table exists: " +
                        dummytable);
    }
    QString delimited_tablename = delimit(tablename);
    QString delimited_dummytable = delimit(dummytable);
    QString goodfieldstring = goodfieldlist.join(",");
    exec(db, "BEGIN TRANSACTION");
    exec(db, QString("ALTER TABLE %1 RENAME TO %2").arg(delimited_tablename,
                                                        delimited_dummytable));
    exec(db, creation_sql);  // make a new clean table
    exec(db, QString("INSERT INTO %1 (%2) SELECT %3 FROM %4").arg(
         delimited_tablename,
         goodfieldstring,
         goodfieldstring,
         delimited_dummytable));
    exec(db, QString("DROP TABLE %1").arg(delimited_dummytable));
    commit(db);
}
コード例 #11
0
ファイル: dbfunc.cpp プロジェクト: RudolfCardinal/camcops
void DbFunc::renameColumns(const QSqlDatabase& db, QString tablename,
                           const QList<QPair<QString, QString>>& from_to,
                           const QString& tempsuffix)
{
    if (!tableExists(db, tablename)) {
        qWarning() << "WARNING: ignoring renameColumns for non-existent table:"
                   << tablename;
        return;
    }
    QString creation_sql = dbTableDefinitionSql(db, tablename);
    QStringList old_fieldnames = dbFieldNames(db, tablename);
    QStringList new_fieldnames = old_fieldnames;
    QString dummytable = tablename + tempsuffix;
    if (tableExists(db, dummytable)) {
        UiFunc::stopApp("DbFunc::renameColumns: temporary table exists: " +
                        dummytable);
    }
    int n_changes = 0;
    for (int i = 0; i < from_to.size(); ++i) {  // For each rename...
        QString from = from_to.at(i).first;
        QString to = from_to.at(i).second;
        if (from == to) {
            continue;
        }
        // Check the source is valid
        if (!old_fieldnames.contains(from)) {
            UiFunc::stopApp("DbFunc::renameColumns: 'from' field doesn't "
                            "exist: " + tablename + "." + from);
        }
        // Check the destination doesn't exist already
        if (new_fieldnames.contains(to)) {
            UiFunc::stopApp(
                "DbFunc::renameColumns: destination field already exists (or "
                "attempt to rename two columns to the same name): " +
                tablename + "." + to);
        }
        // Rename the fieldname in the new_fieldnames list, and in the SQL
        new_fieldnames[new_fieldnames.indexOf(from)] = to;
        creation_sql.replace(delimit(from), delimit(to));
        ++n_changes;
    }
    if (n_changes == 0) {
        qDebug() << "renameColumns: nothing to do:" << tablename;
        return;
    }
    qDebug() << Q_FUNC_INFO;
    qDebug() << "- table:" << tablename;
    qDebug() << "- from_to:" << from_to;
    qDebug() << "- old_fieldnames:" << old_fieldnames;
    qDebug() << "- new_fieldnames:" << new_fieldnames;
    // Delimit everything
    QString delimited_tablename = delimit(tablename);
    QString delimited_dummytable = delimit(dummytable);
    for (int i = 0; i < old_fieldnames.size(); ++i) {
        old_fieldnames[i] = delimit(old_fieldnames[i]);
        new_fieldnames[i] = delimit(new_fieldnames[i]);
    }
    exec(db, "BEGIN TRANSACTION");
    exec(db, QString("ALTER TABLE %1 RENAME TO %2").arg(delimited_tablename,
                                                        delimited_dummytable));
    // Make a new, clean table:
    exec(db, creation_sql);
    // Copy the data across:
    exec(db, QString("INSERT INTO %1 (%2) SELECT %3 FROM %4").arg(
             delimited_tablename,
             new_fieldnames.join(","),
             old_fieldnames.join(","),
             delimited_dummytable));
    // Drop the temporary table:
    exec(db, QString("DROP TABLE %1").arg(delimited_dummytable));
    commit(db);
}