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);
}
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;
}
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")
}
////////////////////////////////////////////////////////////////////////
//
// 从删除指定的工程
//
// 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;
}
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;
}
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);
}
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);
}
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;
}
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;
}
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);
}
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);
}
