I want to alter table if specific column (state) not exists before. I read this link and I've tried but my query was not working. Here is my code:
QMutex m_dbMutex;
m_dbcGui = QSqlDatabase::addDatabase("QSQLITE");
QStringList tables = m_dbcGui.tables(QSql::Tables);
checktable(){
bool result = true;
bool result1 = true;
QSqlQuery query(m_dbcGui);
if (result && tables.contains("groups"))
{
m_dbMutex.lock();
m_dbcGui.transaction();
result1 = result && query.prepare("SELECT EXISTS(SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'groups ' and coloumn_name = 'kid')");
if (result1)
qDebug()<<"***exist kid***";
else{
qDebug()<<"***not exist kid***";
result = result && query.exec("ALTER TABLE 'groups ' ADD COLUMN 'kid");
if (result)
m_dbcGui.commit();
else
m_dbcGui.rollback();
query.finish();
m_dbMutex.unlock();
}
}
else if (result && !tables.contains("groups"))
{
m_dbMutex.lock();
m_dbcGui.transaction();
result = result && query.exec("CREATE TABLE groups ("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"name VARCHAR(256) NOT NULL, "
"pid VARCHAR(256) UNIQUE NOT NULL, "
"nickname VARCHAR(256) NOT NULL)"
"state INTEGER NOT NULL DEFAULT 0)");
if (result)
m_dbcGui.commit();
else
m_dbcGui.rollback();
query.finish();
m_dbMutex.unlock();
}
}
checktable();
checktable();
Firstly , I created groups table succesfully, then I wanted to alter table and add new column (kid) but it wasn't created. Any suggestion ?