void fCacheDB::createTable()
{
beginTransaction();
executeSQL(std::string("CREATE TABLE IF NOT EXISTS " + std::string(TABLE_NAME) +
"(id TEXT PRIMARY KEY, timestamp INTEGER, persistent INTEGER DEFAULT 0, data BLOB)").c_str());
executeSQL(std::string("CREATE INDEX IF NOT EXISTS idx_id ON '" + std::string(TABLE_NAME) + "' (id);").c_str());
executeSQL(std::string("CREATE INDEX IF NOT EXISTS idx_timestamp ON '" + std::string(TABLE_NAME) + "' (timestamp);").c_str());
executeSQL(std::string("CREATE INDEX IF NOT EXISTS idx_persistent ON '" + std::string(TABLE_NAME) + "' (persistent);").c_str());
commitTransaction();
}
void SQLTransaction::executeSql(ScriptState* scriptState, const String& sqlStatement, const Nullable<Vector<ScriptValue>>& arguments, SQLStatementCallback* callback, SQLStatementErrorCallback* callbackError, ExceptionState& exceptionState)
{
Vector<SQLValue> sqlValues;
if (!arguments.isNull())
sqlValues = toImplArray<Vector<SQLValue>>(arguments.get(), scriptState->isolate(), exceptionState);
executeSQL(sqlStatement, sqlValues, callback, callbackError, exceptionState);
}
bool DBBrowserDB::addColumn(const QString& tablename, const sqlb::FieldPtr& field)
{
QString sql = QString("ALTER TABLE `%1` ADD COLUMN %2").arg(tablename).arg(field->toString());
// Execute it and update the schema
bool result = executeSQL(sql);
updateSchema();
return result;
}
int SqlServerConnection_execute(T C, const char *sql, va_list ap) {
va_list ap_copy;
assert(C);
StringBuffer_clear(C->sb);
va_copy(ap_copy, ap);
StringBuffer_vappend(C->sb, sql, ap_copy);
va_end(ap_copy);
executeSQL(C, StringBuffer_toString(C->sb));
return (SQLSERVERSUCCESS(C->lastError));
}
bool DBBrowserDB::setPragma(const QString& pragma, const QString& value)
{
// Set the pragma value
QString sql = QString("PRAGMA %1 = \"%2\";").arg(pragma).arg(value);
save();
bool res = executeSQL(sql, false, true); // PRAGMA statements are usually not transaction bound, so we can't revert
if( !res )
qWarning() << QObject::tr("Error setting pragma %1 to %2: %3").arg(pragma).arg(value).arg(lastErrorMessage);
return res;
}
size_t AMySQLServer::executeSQL(const AString& sqlQuery, AResultSet& target, AString& error)
{
target.clear();
MYSQL_RES *pmyresult = executeSQL(sqlQuery, error);
if (!pmyresult && !error.isEmpty())
return AConstant::npos;
target.useSQL().assign(sqlQuery);
return (pmyresult ? _processQueryAllRows(pmyresult, target) : 0);
}
bool DBBrowserDB::createTable(const QString& name, const sqlb::FieldVector& structure)
{
// Build SQL statement
sqlb::Table table(name);
for(int i=0;i<structure.size();i++)
table.addField(structure.at(i));
// Execute it and update the schema
bool result = executeSQL(table.sql());
updateSchema();
return result;
}
bool DBBrowserDB::renameTable(const QString& from_table, const QString& to_table)
{
QString sql = QString("ALTER TABLE `%1` RENAME TO `%2`").arg(from_table, to_table);
if(!executeSQL(sql))
{
QString error = QObject::tr("Error renaming table '%1' to '%2'."
"Message from database engine:\n%3").arg(from_table).arg(to_table).arg(lastErrorMessage);
lastErrorMessage = error;
qWarning() << lastErrorMessage;
return false;
} else {
updateSchema();
return true;
}
}
bool AMySQLServer::getFields(const AString& table, VECTOR_AString& sv, AString& error)
{
if (!isInitialized())
{
error.assign("Database has not been initialized;");
return false;
}
if (table.isEmpty())
{
error = "Please use a namespace;";
return false;
}
sv.clear();
AString query("SHOW COLUMNS FROM `");
query += table;
query += "`";
MYSQL_RES *pmyresult = executeSQL(query, error);
if (pmyresult)
{
MYSQL_ROW myrow;
int iSize = (int)mysql_num_rows(pmyresult);
for (int i=0; i < iSize; ++i)
{
myrow = mysql_fetch_row(pmyresult);
if (myrow)
{
sv.push_back(myrow[0]);
}
}
mysql_free_result(pmyresult);
}
else
return false;
return true;
}
long long int SqlServerConnection_lastRowId(T C) {
assert(C);
executeSQL(C, "select scope_identity()");
return (SQLSERVERSUCCESS(C->lastError));
//return SqlServer3_last_insert_rowid(C->db);
}
int SqlServerConnection_rollback(T C) {
assert(C);
executeSQL(C, "ROLLBACK TRANSACTION;");
return (SQLSERVERSUCCESS(C->lastError));
}
int SqlServerConnection_commit(T C) {
assert(C);
executeSQL(C, "COMMIT TRANSACTION;");
return (SQLSERVERSUCCESS(C->lastError));
}
int SqlServerConnection_beginTransaction(T C) {
assert(C);
executeSQL(C, "BEGIN TRANSACTION;");
return (SQLSERVERSUCCESS(C->lastError));
}
bool DBBrowserDB::renameColumn(const QString& tablename, const QString& name, sqlb::FieldPtr to, int move)
{
// NOTE: This function is working around the incomplete ALTER TABLE command in SQLite.
// If SQLite should fully support this command one day, this entire
// function can be changed to executing something like this:
//QString sql;
//if(to.isNull())
// sql = QString("ALTER TABLE `%1` DROP COLUMN `%2`;").arg(table).arg(column);
//else
// sql = QString("ALTER TABLE `%1` MODIFY `%2` %3").arg(tablename).arg(to).arg(type); // This is wrong...
//return executeSQL(sql);
// Collect information on the current DB layout
QString tableSql = getObjectByName(tablename).getsql();
if(tableSql.isEmpty())
{
lastErrorMessage = QObject::tr("renameColumn: cannot find table %1.").arg(tablename);
qWarning() << lastErrorMessage;
return false;
}
// Create table schema
sqlb::Table oldSchema = sqlb::Table::parseSQL(tableSql).first;
// Check if field actually exists
if(oldSchema.findField(name) == -1)
{
lastErrorMessage = QObject::tr("renameColumn: cannot find column %1.").arg(name);
qWarning() << lastErrorMessage;
return false;
}
// Create savepoint to be able to go back to it in case of any error
if(!executeSQL("SAVEPOINT sqlitebrowser_rename_column"))
{
lastErrorMessage = QObject::tr("renameColumn: creating savepoint failed. DB says: %1").arg(lastErrorMessage);
qWarning() << lastErrorMessage;
return false;
}
// Create a new table with a name that hopefully doesn't exist yet.
// Its layout is exactly the same as the one of the table to change - except for the column to change
// of course
sqlb::Table newSchema = oldSchema;
newSchema.setName("sqlitebrowser_rename_column_new_table");
QString select_cols;
if(to.isNull())
{
// We want drop the column - so just remove the field
newSchema.removeField(name);
for(int i=0;i<newSchema.fields().count();++i)
select_cols.append(QString("`%1`,").arg(newSchema.fields().at(i)->name()));
select_cols.chop(1); // remove last comma
} else {
// We want to modify it
// Move field
int index = newSchema.findField(name);
sqlb::FieldPtr temp = newSchema.fields().at(index);
newSchema.setField(index, newSchema.fields().at(index + move));
newSchema.setField(index + move, temp);
// Get names of fields to select from old table now - after the field has been moved and before it might be renamed
for(int i=0;i<newSchema.fields().count();++i)
select_cols.append(QString("`%1`,").arg(newSchema.fields().at(i)->name()));
select_cols.chop(1); // remove last comma
// Modify field
newSchema.setField(index + move, to);
}
// Create the new table
if(!executeSQL(newSchema.sql()))
{
lastErrorMessage = QObject::tr("renameColumn: creating new table failed. DB says: %1").arg(lastErrorMessage);
qWarning() << lastErrorMessage;
executeSQL("ROLLBACK TO SAVEPOINT sqlitebrowser_rename_column;");
return false;
}
// Copy the data from the old table to the new one
if(!executeSQL(QString("INSERT INTO sqlitebrowser_rename_column_new_table SELECT %1 FROM `%2`;").arg(select_cols).arg(tablename)))
{
lastErrorMessage = QObject::tr("renameColumn: copying data to new table failed. DB says:\n"
"%1").arg(lastErrorMessage);
qWarning() << lastErrorMessage;
executeSQL("ROLLBACK TO SAVEPOINT sqlitebrowser_rename_column;");
return false;
}
// Save all indices, triggers and views associated with this table because SQLite deletes them when we drop the table in the next step
QString otherObjectsSql;
for(objectMap::ConstIterator it=objMap.begin();it!=objMap.end();++it)
{
// If this object references the table and it's not the table itself save it's SQL string
if((*it).getTableName() == tablename && (*it).gettype() != "table")
otherObjectsSql += (*it).getsql() + "\n";
}
// Delete the old table
if(!executeSQL(QString("DROP TABLE `%1`;").arg(tablename)))
{
lastErrorMessage = QObject::tr("renameColumn: deleting old table failed. DB says: %1").arg(lastErrorMessage);
qWarning() << lastErrorMessage;
executeSQL("ROLLBACK TO SAVEPOINT sqlitebrowser_rename_column;");
return false;
}
// Rename the temporary table
if(!renameTable("sqlitebrowser_rename_column_new_table", tablename))
{
executeSQL("ROLLBACK TO SAVEPOINT sqlitebrowser_rename_column;");
return false;
}
// Restore the saved triggers, views and indices
if(!executeMultiSQL(otherObjectsSql, true, true))
{
QMessageBox::information(0, qApp->applicationName(), QObject::tr("Restoring some of the objects associated with this table failed. "
"This is most likely because some column names changed. "
"Here's the SQL statement which you might want to fix and execute manually:\n\n")
+ otherObjectsSql);
}
// Release the savepoint - everything went fine
if(!executeSQL("RELEASE SAVEPOINT sqlitebrowser_rename_column;"))
{
lastErrorMessage = QObject::tr("renameColumn: releasing savepoint failed. DB says: %1").arg(lastErrorMessage);
qWarning() << lastErrorMessage;
return false;
}
// Success, update the DB schema before returning
updateSchema();
return true;
}
void SQLTransaction::executeSql(ScriptState* scriptState, const String& sqlStatement, ExceptionState& exceptionState)
{
executeSQL(sqlStatement, Vector<SQLValue>(), nullptr, nullptr, exceptionState);
}
