qt 数据库增加字段且版本兼容

数据库使用过程中经常会出现需要增加新的字段，这个时候又要兼容旧的版本。
我们增加字段往往用到“ALTER TABLE tablename ADD COLUMN 字段名 类型”的sql语句，而在qt中增加字段除了这个语句还需要几个类的配合，QSqlDatabase、QSqlError、QSqlQuery、QSqlRecord、QSqlField。
QSqlDatabase用来连接数据库，获取表数和表名；
QSqlError用来sql的执行错误；
QSqlQuery用来执行语句和获取执行后的结果；
QSqlRecord获取表的字段名；
QSqlField获取表的字段类型。

如果不想用Qt封装的接口查询数据库中的字段名和字段类型，可以使用“SELECT * FROM pragma_table_info(‘tablename’)”的方法来查询字段名和类型。

实例如下：

#ifndef WIDGET_H
#define WIDGET_H

#include <QtWidgets>
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
#include <QSqlRecord>
#include <QSqlField>

QT_BEGIN_NAMESPACE
namespace Ui { class Widget; }
QT_END_NAMESPACE

class Widget : public QWidget
{
    Q_OBJECT

public:
    Widget(QWidget *parent = nullptr);
    ~Widget();

    bool initDb();
private:
    Ui::Widget *ui;
    QSqlDatabase db;
};
#endif // WIDGET_H

#include "widget.h"
#include "ui_widget.h"

#if 1
const QString CRESQL1 = "create table test1(id integer, age integer, name varchar)";
const QString CRESQL2 = "create table test2(id integer, size integer, data varchar)";
#else
const QString CRESQL1 = "create table test1(id integer, age integer, name varchar, title integer)";
const QString CRESQL2 = "create table test2(id integer, size integer, data varchar, type integer, value varchar)";
#endif

Widget::Widget(QWidget *parent)
    : QWidget(parent)
    , ui(new Ui::Widget)
{
    ui->setupUi(this);

    initDb();
}

Widget::~Widget()
{
    delete ui;
}

bool Widget::initDb()
{
    db = QSqlDatabase::addDatabase("QSQLITE","test_sqlite_connect");
    QString projectDir = QCoreApplication::applicationDirPath();
    QStringList tmpRootDirlist = QString(projectDir).split("/");
    if(tmpRootDirlist.size() >= 2){
        tmpRootDirlist.removeLast();
        tmpRootDirlist.removeLast();
    }

    QString userDataPath =  QApplication::applicationDirPath()+"/library";

    QString dbpath = userDataPath + "/test_sqlite.db";

    QDir userDataDir(userDataPath);
    if(!userDataDir.exists()){
        userDataDir.mkpath(userDataPath);
    }

    qDebug() << __FUNCTION__ << __LINE__ << " ============>>>>    " << dbpath;

    db.setDatabaseName(dbpath);
    db.setPassword("123456");
    db.setHostName("127.0.0.1");
    db.setUserName("CSDN");

    if (!db.open()){
        perror(db.lastError().text().toUtf8().data());
        return false;
    }

    QStringList tables = db.tables();
    qDebug() << __FUNCTION__ << __LINE__ << tables;

    QSqlQuery q(db);
    QStringList sqlist( QStringList() << CRESQL1 << CRESQL2);
    QStringList tablelist( QStringList() << "test1" << "test2");
    for (int i = 0; i < tablelist.size(); ++i)
    {
        QString tableName = tablelist.at(i);
        if (tables.contains(tableName))
        {
            QString selectSql = QString("select * from %1;").arg(tableName);
            if (!q.exec(selectSql))
            {
                perror(q.lastError().text().toUtf8().data());
                continue;
            }
            QStringList oldtitles;
            for (int j = 0; j < q.record().count(); ++j)
            {
                auto type = q.record().field(j).type();
                auto title = q.record().fieldName(j);
                oldtitles.append(title);
            }
            QString newtitle = sqlist.at(i);
            newtitle = newtitle.mid(newtitle.indexOf("(")+1,newtitle.indexOf(")")-newtitle.indexOf("(")-1);
            newtitle.remove("\n");
            QStringList newtitles = newtitle.split(",");
            if (newtitles.size() != oldtitles.size())
            {
                for (int k = oldtitles.size(); k < newtitles.size(); ++k)
                {
                    QString title = newtitles.at(k);
                    if (title.size())
                    {
                        if (title.mid(0,1) == " ")
                        {
                            title = title.mid(1,title.size());
                        }
                        title = title.split(" ").first();
                        if (!oldtitles.contains(title))
                        {
                            QString sql = QString("ALTER TABLE %1 ADD COLUMN %2").arg(tableName).arg(newtitles.at(k));
                            qDebug()<<__FUNCTION__<<__LINE__<<"数据库添加新字段"<<sql;
                            if(!q.exec(sql))
                            {
                                perror(q.lastError().text().toUtf8().data());
                                continue;
                            }
                        }
                    }
                }
            }
        }
        else
        {
            if(!q.exec(sqlist.at(i)))
            {
                perror(q.lastError().text().toUtf8().data());
                continue;
            }
        }
    }

    return true;
}

旧表：

将
const QString CRESQL1 = “create table test1(id integer, age integer, name varchar)”;
const QString CRESQL2 = “create table test2(id integer, size integer, data varchar)”;
改为
const QString CRESQL1 = “create table test1(id integer, age integer, name varchar, title integer)”;
const QString CRESQL2 = “create table test2(id integer, size integer, data varchar, type integer, value varchar)”;