void MainWindow::setModel(QTableView *tableViewName, QString modelName, QString tableName, QStringList *tableFields, QString filter, int orderNum)
{
QSqlTableModel *model;
if (modelName == "modelEdit") {
modelEdit = new QSqlTableModel;
model = modelEdit;
} else if (modelName == "modelChoose") {
modelChoose = new QSqlTableModel;
model = modelChoose;
} else if (modelName == "modelQ") {
modelQ = new QSqlTableModel;
model = modelQ;
} else if (modelName == "modelQE") {
modelQE = new QSqlTableModel;
model = modelQE;
} else if (modelName == "modelNote") {
modelNote = new QSqlTableModel;
model = modelNote;
} else if (modelName== "modelNotes") {
modelNotes = new QSqlTableModel;
model = modelNotes;
} else {
return;
}
model= new QSqlTableModel(this);
model->setTable(tableName);
model->setEditStrategy(QSqlTableModel::OnFieldChange);
model->setSort(orderNum, Qt::DescendingOrder);
int tableFieldsNum = tableFields->length();
for (int i = 0; i < tableFieldsNum; i++) {
model->setHeaderData(i, Qt::Horizontal, tableFields->at(i));
}
tableViewName->setModel(model);
tableViewName->alternatingRowColors();
tableViewName->horizontalHeader()->setStretchLastSection(true);
model->setFilter(filter);
model->select();
tableViewName->reset();
qDebug() << modelName << model << model->rowCount();
if (modelName == "modelEdit") {
modelEdit = model;
} else if (modelName == "modelChoose") {
modelChoose = model;
} else if (modelName == "modelQ") {
modelQ = model;
} else if (modelName == "modelQE") {
modelQE = model;
} else if (modelName == "modelNote") {
modelNote = model;
} else if (modelName == "modelNotes") {
modelNotes = model;
} else return;
}
void FenPrincipale::afficherTable()
{
// database = FenPrincipale::newdatabase;
// createConnection();
//QMessageBox::information(0,QObject::tr("debug"),
// "debug "+database);
QSqlTableModel *model = new QSqlTableModel ();
model->setTable( "stationStationnee" );
model->setEditStrategy(QSqlTableModel::OnManualSubmit);
model->setSort(0,Qt::AscendingOrder);
model->select();
QSqlTableModel *model1 = new QSqlTableModel ();
model1->setTable("mesures");
model1->setEditStrategy(QSqlTableModel::OnManualSubmit);
model1->setSort(0,Qt::AscendingOrder);
model1->select();
tabless->setModel( model );
tabless->show();
// tabless->setColumnHidden(0,true);
tabless->setColumnHidden(5,true);
tabless->setColumnHidden(6,true);
tabless->setColumnHidden(7,true);
tabless->resizeColumnToContents(1);
tablemesures->setModel( model1 );
// tablemesures->setColumnHidden(0,true);
tablemesures->setColumnHidden(19,true);
tablemesures->resizeColumnsToContents();
QSqlQuery query;
QModelIndex index;
// query.exec("select id from mesures where classe = 30");
// int id=0;
// while (query.next()) {
// id = query.value(0).toInt();
// }
// if (index.row() == id)
// {
// tablemesures->setBackgroundRole(QPalette::Text);
// }tmpAreteList->count()
tablemesures->show();
// QString date = "";
// QFileInfo file(database);
// QDateTime lastModified = file.lastModified();
// date = lastModified.toString();
// bool valid = QFile::copy (database, database+"."+date);
}
void CollectionTreeWidget::showChildrenOf(QModelIndex index) {
CollectionTreeWidgetItem *item = (CollectionTreeWidgetItem*)itemFromIndex(index);
// If the item pressed was an artist, add albums
if (item->getNodeLevel() == LevelArtist) {
QString artist = item->text(0).toUtf8();
// Looks for artist id
QString artistId = QString::number(item->getId());
// Looks for artist albums
QSqlTableModel *albumModel = service->albumModel();
albumModel->setFilter("id_artist = " + artistId);
albumModel->select();
while (albumModel->canFetchMore()) albumModel->fetchMore();
int total = albumModel->rowCount();
for (int i = 0; i < total; i++) {
QString album = albumModel->record(i).value(albumModel->fieldIndex("title")).toString();
unsigned int id = albumModel->record(i).value(albumModel->fieldIndex("id")).toInt();
addAlbum(artist, album, id);
}
delete albumModel;
}
// If the item pressed was an album, add songs
else if (item->getNodeLevel() == LevelAlbum) {
QString albumId = QString::number(item->getId());
QSqlTableModel *musicModel = service->musicModel();
musicModel->setFilter("id_album = " + albumId);
musicModel->setSort(musicModel->fieldIndex("track_number"), Qt::AscendingOrder);
musicModel->select();
while (musicModel->canFetchMore()) musicModel->fetchMore();
int total = musicModel->rowCount();
for (int i = 0; i < total; i++) {
QString path = musicModel->record(i).value(musicModel->fieldIndex("path")).toString();
unsigned int id = musicModel->record(i).value(musicModel->fieldIndex("id")).toInt();
Music *music = new Music(QUrl(path));
addMusic(music, id);
delete music;
}
}
expand(index);
}
void QgsBookmarks::addClicked()
{
QSqlTableModel *model = qobject_cast<QSqlTableModel *>( lstBookmarks->model() );
Q_ASSERT( model );
QgsMapCanvas *canvas = QgisApp::instance()->mapCanvas();
Q_ASSERT( canvas );
QSqlQuery query( "INSERT INTO tbl_bookmarks(bookmark_id,name,project_name,xmin,ymin,xmax,ymax,projection_srid)"
" VALUES (NULL,:name,:project_name,:xmin,:xmax,:ymin,:ymax,:projection_srid)",
model->database() );
QString projStr( "" );
if ( QgsProject::instance() )
{
if ( !QgsProject::instance()->title().isEmpty() )
{
projStr = QgsProject::instance()->title();
}
else if ( !QgsProject::instance()->fileName().isEmpty() )
{
QFileInfo fi( QgsProject::instance()->fileName() );
projStr = fi.exists() ? fi.fileName() : "";
}
}
query.bindValue( ":name", tr( "New bookmark" ) );
query.bindValue( ":project_name", projStr );
query.bindValue( ":xmin", canvas->extent().xMinimum() );
query.bindValue( ":ymin", canvas->extent().yMinimum() );
query.bindValue( ":xmax", canvas->extent().xMaximum() );
query.bindValue( ":ymax", canvas->extent().yMaximum() );
query.bindValue( ":projection_srid", QVariant::fromValue( canvas->mapSettings().destinationCrs().srsid() ) );
if ( query.exec() )
{
model->setSort( 0, Qt::AscendingOrder );
model->select();
lstBookmarks->scrollToBottom();
lstBookmarks->setCurrentIndex( model->index( model->rowCount() - 1, 1 ) );
lstBookmarks->edit( model->index( model->rowCount() - 1, 1 ) );
}
else
{
QMessageBox::warning( this, tr( "Error" ), tr( "Unable to create the bookmark.\nDriver:%1\nDatabase:%2" )
.arg( query.lastError().driverText() )
.arg( query.lastError().databaseText() ) );
}
}
TableView::TableView()
: QWidget()
{
if (! this->createConnection()) {
QString msg = "** Create Database Error";
throw msg;
}
setWindowTitle(tr("Table View Model"));
setMinimumSize(300, 350);
//
QSqlTableModel *model = new QSqlTableModel(this, m_db);
QStringList tempX = m_db.tables();
model->setTable(tempX.at(0));
QSqlError error = model->lastError();
if ( error.isValid() ) {
ksMsg( this, "Table View - SQL Error", error.text() );
}
model->setEditStrategy(QSqlTableModel::OnManualSubmit);
model->setSort(2, Qt::AscendingOrder);
model->select();
model->setHeaderData(0, Qt::Horizontal, QObject::tr("ID"));
model->setHeaderData(1, Qt::Horizontal, QObject::tr("First name"));
model->setHeaderData(2, Qt::Horizontal, QObject::tr("Last name"));
//
QTableView *view = new QTableView;
view->setModel(model);
view->resizeColumnsToContents();
QHeaderView *temp = view->horizontalHeader();
temp->setStretchLastSection(true);
QHBoxLayout *mainLayout = new QHBoxLayout;
mainLayout->addWidget(view);
setLayout(mainLayout);
}
void sql_intro_snippets()
{
{
//! [26]
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("bigblue");
db.setDatabaseName("flightdb");
db.setUserName("acarlson");
db.setPassword("1uTbSbAs");
bool ok = db.open();
//! [26]
Q_UNUSED(ok);
}
{
//! [27]
QSqlDatabase firstDB = QSqlDatabase::addDatabase("QMYSQL", "first");
QSqlDatabase secondDB = QSqlDatabase::addDatabase("QMYSQL", "second");
//! [27]
}
{
//! [28]
QSqlDatabase defaultDB = QSqlDatabase::database();
//! [28] //! [29]
QSqlDatabase firstDB = QSqlDatabase::database("first");
//! [29] //! [30]
QSqlDatabase secondDB = QSqlDatabase::database("second");
//! [30]
}
{
// SELECT1
//! [31]
QSqlQuery query;
query.exec("SELECT name, salary FROM employee WHERE salary > 50000");
//! [31]
//! [32]
while (query.next()) {
QString name = query.value(0).toString();
int salary = query.value(1).toInt();
qDebug() << name << salary;
}
//! [32]
}
{
// FEATURE
//! [33]
QSqlQuery query;
int numRows;
query.exec("SELECT name, salary FROM employee WHERE salary > 50000");
QSqlDatabase defaultDB = QSqlDatabase::database();
if (defaultDB.driver()->hasFeature(QSqlDriver::QuerySize)) {
numRows = query.size();
} else {
// this can be very slow
query.last();
numRows = query.at() + 1;
}
//! [33]
}
{
// INSERT1
//! [34]
QSqlQuery query;
query.exec("INSERT INTO employee (id, name, salary) "
"VALUES (1001, 'Thad Beaumont', 65000)");
//! [34]
}
{
// NAMED BINDING
//! [35]
QSqlQuery query;
query.prepare("INSERT INTO employee (id, name, salary) "
"VALUES (:id, :name, :salary)");
query.bindValue(":id", 1001);
query.bindValue(":name", "Thad Beaumont");
query.bindValue(":salary", 65000);
query.exec();
//! [35]
}
{
// POSITIONAL BINDING
//! [36]
QSqlQuery query;
query.prepare("INSERT INTO employee (id, name, salary) "
"VALUES (?, ?, ?)");
query.addBindValue(1001);
query.addBindValue("Thad Beaumont");
query.addBindValue(65000);
query.exec();
//! [36]
}
{
// UPDATE1
//! [37]
QSqlQuery query;
query.exec("UPDATE employee SET salary = 70000 WHERE id = 1003");
//! [37]
}
{
// DELETE1
//! [38]
QSqlQuery query;
query.exec("DELETE FROM employee WHERE id = 1007");
//! [38]
}
{
// TRANSACTION
//! [39]
QSqlDatabase::database().transaction();
QSqlQuery query;
query.exec("SELECT id FROM employee WHERE name = 'Torild Halvorsen'");
if (query.next()) {
int employeeId = query.value(0).toInt();
query.exec("INSERT INTO project (id, name, ownerid) "
"VALUES (201, 'Manhattan Project', "
+ QString::number(employeeId) + ')');
}
QSqlDatabase::database().commit();
//! [39]
}
{
// SQLQUERYMODEL1
//! [40]
QSqlQueryModel model;
model.setQuery("SELECT * FROM employee");
for (int i = 0; i < model.rowCount(); ++i) {
int id = model.record(i).value("id").toInt();
QString name = model.record(i).value("name").toString();
qDebug() << id << name;
}
//! [40]
}
{
// SQLTABLEMODEL1
//! [41]
QSqlTableModel model;
model.setTable("employee");
model.setFilter("salary > 50000");
model.setSort(2, Qt::DescendingOrder);
model.select();
for (int i = 0; i < model.rowCount(); ++i) {
QString name = model.record(i).value("name").toString();
int salary = model.record(i).value("salary").toInt();
qDebug() << name << salary;
}
//! [41]
}
{
// SQLTABLEMODEL2
QSqlTableModel model;
model.setTable("employee");
//! [42]
for (int i = 0; i < model.rowCount(); ++i) {
QSqlRecord record = model.record(i);
double salary = record.value("salary").toInt();
salary *= 1.1;
record.setValue("salary", salary);
model.setRecord(i, record);
}
model.submitAll();
//! [42]
// SQLTABLEMODEL3
int row = 1;
int column = 2;
//! [43]
model.setData(model.index(row, column), 75000);
model.submitAll();
//! [43]
// SQLTABLEMODEL4
//! [44]
model.insertRows(row, 1);
model.setData(model.index(row, 0), 1013);
model.setData(model.index(row, 1), "Peter Gordon");
model.setData(model.index(row, 2), 68500);
model.submitAll();
//! [44]
//! [45]
model.removeRows(row, 5);
//! [45] //! [46]
model.submitAll();
//! [46]
}
}
int IDResource::getSingleIdRes(QString& idType)
{
QSqlTableModel model;
model.setTable(idType);
QString filter1="";
int minNum,maxNum;
if (idType==tr("ACLID"))
{
filter1=QString("ID>=%1 and ID<=%2").arg(g_minACLID).arg(g_maxACLID);
minNum=g_minACLID;
maxNum=g_maxACLID;
}
else if (idType==tr("XACLID"))
{
filter1=QString("ID>=%1 and ID<=%2").arg(g_minXACLID).arg(g_maxXACLID);
minNum=g_minXACLID;
maxNum=g_maxXACLID;
}
else if (idType==tr("PLID"))
{
filter1=QString("ID>=%1 and ID<=%2").arg(g_minPLID).arg(g_maxPLID);
minNum=g_minPLID;
maxNum=g_maxPLID;
}
model.setFilter(filter1);
model.setSort( 0, Qt::AscendingOrder);
model.select();
QSet<int>idSet;
for(int j=0;j<model.rowCount();++j)
{
QSqlRecord record=model.record(j);
idSet<<record.value("id").toInt();
}
QQueue<int>queueId;
for(int j=minNum;j!=maxNum+1;++j)
{
if(!idSet.contains(j))
{
queueId.enqueue(j);
}
}
idSet.clear();
if(queueId.count()<=0)
{
QMessageBox::information(this, tr("information"), tr("无可用ID资源"));
}
while(true)
{
int resId=queueId.dequeue();
if(!idConflict(resId,idType))
return resId;
}
return 0;
}
QgsBookmarks::QgsBookmarks( QWidget *parent ) : QDockWidget( parent )
{
setupUi( this );
restorePosition();
QToolButton* btnImpExp = new QToolButton;
btnImpExp->setAutoRaise( true );
btnImpExp->setToolTip( tr( "Import/Export Bookmarks" ) );
btnImpExp->setIcon( QgsApplication::getThemeIcon( "/mActionSharing.svg" ) );
btnImpExp->setPopupMode( QToolButton::InstantPopup );
QMenu *share = new QMenu( this );
QAction *btnExport = share->addAction( tr( "&Export" ) );
QAction *btnImport = share->addAction( tr( "&Import" ) );
btnExport->setIcon( QgsApplication::getThemeIcon( "/mActionSharingExport.svg" ) );
btnImport->setIcon( QgsApplication::getThemeIcon( "/mActionSharingImport.svg" ) );
connect( btnExport, SIGNAL( triggered() ), this, SLOT( exportToXML() ) );
connect( btnImport, SIGNAL( triggered() ), this, SLOT( importFromXML() ) );
btnImpExp->setMenu( share );
connect( actionAdd, SIGNAL( triggered() ), this, SLOT( addClicked() ) );
connect( actionDelete, SIGNAL( triggered() ), this, SLOT( deleteClicked() ) );
connect( actionZoomTo, SIGNAL( triggered() ), this, SLOT( zoomToBookmark() ) );
mBookmarkToolbar->addWidget( btnImpExp );
mBookmarkToolbar->addAction( actionHelp );
// open the database
QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE", "bookmarks" );
db.setDatabaseName( QgsApplication::qgisUserDbFilePath() );
if ( !db.open() )
{
QMessageBox::warning( this, tr( "Error" ),
tr( "Unable to open bookmarks database.\nDatabase: %1\nDriver: %2\nDatabase: %3" )
.arg( QgsApplication::qgisUserDbFilePath() )
.arg( db.lastError().driverText() )
.arg( db.lastError().databaseText() )
);
deleteLater();
return;
}
QSqlTableModel *model = new QSqlTableModel( this, db );
model->setTable( "tbl_bookmarks" );
model->setSort( 0, Qt::AscendingOrder );
model->setEditStrategy( QSqlTableModel::OnFieldChange );
model->select();
// set better headers then column names from table
model->setHeaderData( 0, Qt::Horizontal, tr( "ID" ) );
model->setHeaderData( 1, Qt::Horizontal, tr( "Name" ) );
model->setHeaderData( 2, Qt::Horizontal, tr( "Project" ) );
model->setHeaderData( 3, Qt::Horizontal, tr( "xMin" ) );
model->setHeaderData( 4, Qt::Horizontal, tr( "yMin" ) );
model->setHeaderData( 5, Qt::Horizontal, tr( "xMax" ) );
model->setHeaderData( 6, Qt::Horizontal, tr( "yMax" ) );
model->setHeaderData( 7, Qt::Horizontal, tr( "SRID" ) );
lstBookmarks->setModel( model );
QSettings settings;
lstBookmarks->header()->restoreState( settings.value( "/Windows/Bookmarks/headerstate" ).toByteArray() );
#ifndef QGISDEBUG
lstBookmarks->setColumnHidden( 0, true );
#endif
}
QgsBookmarks::QgsBookmarks( QWidget *parent, Qt::WindowFlags fl )
: QDialog( parent, fl )
{
setupUi( this );
restorePosition();
//
// Create the zoomto and delete buttons and add them to the
// toolbar
//
QPushButton *btnAdd = new QPushButton( tr( "&Add" ) );
QPushButton *btnDelete = new QPushButton( tr( "&Delete" ) );
QPushButton *btnZoomTo = new QPushButton( tr( "&Zoom to" ) );
QPushButton *btnImpExp = new QPushButton( tr( "&Share" ) );
btnZoomTo->setDefault( true );
buttonBox->addButton( btnAdd, QDialogButtonBox::ActionRole );
buttonBox->addButton( btnDelete, QDialogButtonBox::ActionRole );
buttonBox->addButton( btnZoomTo, QDialogButtonBox::ActionRole );
buttonBox->addButton( btnImpExp, QDialogButtonBox::ActionRole );
QMenu *share = new QMenu();
QAction *btnExport = share->addAction( tr( "&Export" ) );
QAction *btnImport = share->addAction( tr( "&Import" ) );
connect( btnExport, SIGNAL( triggered() ), this, SLOT( exportToXML() ) );
connect( btnImport, SIGNAL( triggered() ), this, SLOT( importFromXML() ) );
btnImpExp->setMenu( share );
connect( btnAdd, SIGNAL( clicked() ), this, SLOT( addClicked() ) );
connect( btnDelete, SIGNAL( clicked() ), this, SLOT( deleteClicked() ) );
connect( btnZoomTo, SIGNAL( clicked() ), this, SLOT( zoomToBookmark() ) );
// open the database
QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE", "bookmarks" );
db.setDatabaseName( QgsApplication::qgisUserDbFilePath() );
if ( !db.open() )
{
QMessageBox::warning( this, tr( "Error" ),
tr( "Unable to open bookmarks database.\nDatabase: %1\nDriver: %2\nDatabase: %3" )
.arg( QgsApplication::qgisUserDbFilePath() )
.arg( db.lastError().driverText() )
.arg( db.lastError().databaseText() )
);
deleteLater();
return;
}
QSqlTableModel *model = new QSqlTableModel( this, db );
model->setTable( "tbl_bookmarks" );
model->setSort( 0, Qt::AscendingOrder );
model->setEditStrategy( QSqlTableModel::OnFieldChange );
model->select();
// set better headers then column names from table
model->setHeaderData( 0, Qt::Horizontal, tr( "ID" ) );
model->setHeaderData( 1, Qt::Horizontal, tr( "Name" ) );
model->setHeaderData( 2, Qt::Horizontal, tr( "Project" ) );
model->setHeaderData( 3, Qt::Horizontal, tr( "xMin" ) );
model->setHeaderData( 4, Qt::Horizontal, tr( "yMin" ) );
model->setHeaderData( 5, Qt::Horizontal, tr( "xMax" ) );
model->setHeaderData( 6, Qt::Horizontal, tr( "yMax" ) );
model->setHeaderData( 7, Qt::Horizontal, tr( "SRID" ) );
lstBookmarks->setModel( model );
QSettings settings;
lstBookmarks->header()->restoreState( settings.value( "/Windows/Bookmarks/headerstate" ).toByteArray() );
#ifndef QGISDEBUG
lstBookmarks->setColumnHidden( 0, true );
#endif
}
