lundi 15 février 2016

QSqlQuery - alter table if column not exists before

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***";

            query.exec("ALTER TABLE 'groups ' ADD COLUMN 'kid");    
            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 ?

Aucun commentaire:

Enregistrer un commentaire