mysql表结构查看

项目开发时候要看着表结构写，实在是受不了phpmyadmin或者是navicat，就用qt写了一个看表结构的小玩意，主要是看表字段的注释。

使用过程中发现过一段时间，连接就会被mysqld自动断开，就在每次查询前多执行一个“select 1;”，反正是开发时候用，浪费资源就浪费吧。

#include <QApplication>
#include "mainwindow.h"

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    MainWindow w;
    w.show();
    
    return a.exec();
}

 

#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QtGui>
#include <QtSql>

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    MainWindow(QWidget *parent = 0);
    ~MainWindow();

private slots:
    void databaseRefleshActionTriggered();

    void tableRefleshActionTriggered();
    void tableActionGroupTriggered(QAction *);

    void removeTab(int i)
    {
        tabWidget->removeTab(i);
    }

private:
    QSqlQuery *query(const QString &sql)
    {
        if ((!db.isOpen() || !QSqlQuery("select 1;", db).isActive()) && !db.open())
            QMessageBox::warning(this, "Error", db.lastError().text());

        return new QSqlQuery(sql, db);
    }

private:
    QWidget *widget;
    QVBoxLayout *layout;
    QTabWidget *tabWidget;
    QMap<QString, QTableView *> commentViews;

    QMenu *databaseMenu;
    QAction *databaseRefleshAction;
    QActionGroup *databaseActionGroup;

    QMenu *tableMenu;
    QAction *tableRefleshAction;
    QActionGroup *tableActionGroup;

    QSqlDatabase db;
};

#endif // MAINWINDOW_H

#include "mainwindow.h"

MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent), db(QSqlDatabase::addDatabase("QMYSQL"))
{
    db.setHostName("localhost");
    db.setUserName("root");
    db.setPassword("");

    setWindowTitle("Table Comment Viewer");
    resize(600, 400);

    databaseMenu = menuBar()->addMenu("Database");
    databaseRefleshAction = databaseMenu->addAction("Reflesh");
    databaseMenu->addSeparator();
    databaseActionGroup = new QActionGroup(this);
    databaseActionGroup->setExclusive(true);

    connect(databaseRefleshAction, SIGNAL(triggered()), this, SLOT(databaseRefleshActionTriggered()));
    connect(databaseActionGroup, SIGNAL(triggered(QAction*)), this, SLOT(tableRefleshActionTriggered()));

    tableMenu = menuBar()->addMenu("Table");
    tableRefleshAction = tableMenu->addAction("Reflesh");
    tableMenu->addSeparator();
    tableActionGroup = new QActionGroup(this);

    connect(tableRefleshAction, SIGNAL(triggered()), this, SLOT(tableRefleshActionTriggered()));
    connect(tableActionGroup, SIGNAL(triggered(QAction*)), this, SLOT(tableActionGroupTriggered(QAction *)));

    widget = new QWidget;
    setCentralWidget(widget);

    layout = new QVBoxLayout;
    widget->setLayout(layout);

    tabWidget = new QTabWidget;
    tabWidget->setMovable(true);
    tabWidget->setTabsClosable(true);
    layout->addWidget(tabWidget);

    connect(tabWidget, SIGNAL(tabCloseRequested(int)), this, SLOT(removeTab(int)));

    databaseRefleshActionTriggered();
}

MainWindow::~MainWindow()
{
}

void MainWindow::databaseRefleshActionTriggered()
{
    QList<QAction *> acs = databaseActionGroup->actions();
    foreach (QAction *ac, acs)
    {
        databaseMenu->removeAction(ac);
        databaseActionGroup->removeAction(ac);
        delete ac;
    }
    acs.clear();

    QStringList ss;
    QSqlQuery *q = query("show databases;");

    while (q->next())
        ss << q->value(0).toString();

    foreach (const QString &s, ss)
    {
        databaseActionGroup->addAction(s)->setCheckable(true);
    }

    databaseMenu->addActions(databaseActionGroup->actions());
    tableRefleshActionTriggered();
}

void MainWindow::tableRefleshActionTriggered()
{
    int c = tabWidget->count();
    for (int i = 0; i < c; i++)
    {
        tabWidget->removeTab(i);
    }

    foreach (QTableView *v, commentViews.values())
    {
        delete v;
    }

    commentViews.clear();

    QList<QAction *> acs = tableActionGroup->actions();
    foreach (QAction *ac, acs)
    {
        tableMenu->removeAction(ac);
        tableActionGroup->removeAction(ac);
        delete ac;
    }
    acs.clear();

    QAction *ac = databaseActionGroup->checkedAction();
    if (!ac)
        return;

    QStringList ss;
    QSqlQuery *q = query(QString("show tables from %1").arg(ac->text()));

    while (q->next())
        ss << q->value(0).toString();

    foreach (const QString &s, ss)
    {
        tableActionGroup->addAction(s)->setWhatsThis("hahaha");
    }

    tableMenu->addActions(tableActionGroup->actions());
}

void MainWindow::tableActionGroupTriggered(QAction *ac)
{
    QAction *dac = databaseActionGroup->checkedAction();
    if (!dac)
        return;

    QString table = ac->text();

    int c = tabWidget->count();
    for (int i = 0; i < c; i++)
    {
        if (tabWidget->tabText(i) == table)
        {
            tabWidget->setCurrentIndex(i);
            return;
        }
    }

    if (!commentViews.contains(table))
    {
        QString sql = QString("select column_name, concat(column_type, ' ', ifnull(column_default, '')),"
        " concat(ifnull(character_set_name, ''), ' ', ifnull(collation_name, ''), ' ', column_key, ' ', extra), column_comment"
        " from information_schema.columns"
        " where table_schema = '%1' and table_name = '%2'"
        " order by ordinal_position;").arg(dac->text()).arg(table);

        QSqlQueryModel *model = new QSqlQueryModel;

        QSqlQuery *q = query(sql);
        model->setQuery(*q);

        model->setHeaderData(0, Qt::Horizontal, "Name");
        model->setHeaderData(1, Qt::Horizontal, "Type & Default");
        model->setHeaderData(2, Qt::Horizontal, "Extra");
        model->setHeaderData(3, Qt::Horizontal, "Comment");

        QTableView *view = new QTableView;
        view->setModel(model);
        view->show();

        commentViews[table] = view;
    }

    int i = tabWidget->addTab(commentViews[table], table);
    tabWidget->setCurrentIndex(i);
}