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]
}
}
QDjangoCompiler::QDjangoCompiler(const char *modelName, const QSqlDatabase &db)
{
driver = db.driver();
baseModel = QDjango::metaModel(modelName);
}
QSqlDatabase Database::Connect() {
QMutexLocker l(&connect_mutex_);
// Create the directory if it doesn't exist
if (!QFile::exists(directory_)) {
QDir dir;
if (!dir.mkpath(directory_)) {
}
}
const QString connection_id = QString("%1_thread_%2").arg(connection_id_).arg(
reinterpret_cast<quint64>(QThread::currentThread()));
// Try to find an existing connection for this thread
QSqlDatabase db = QSqlDatabase::database(connection_id);
if (db.isOpen()) {
return db;
}
db = QSqlDatabase::addDatabase("QSQLITE", connection_id);
if (!injected_database_name_.isNull())
db.setDatabaseName(injected_database_name_);
else
db.setDatabaseName(directory_ + "/" + kDatabaseFilename);
if (!db.open()) {
app_->AddError("Database: " + db.lastError().text());
return db;
}
// Find Sqlite3 functions in the Qt plugin.
StaticInit();
{
#ifdef SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER
// In case sqlite>=3.12 is compiled without -DSQLITE_ENABLE_FTS3_TOKENIZER (generally a good idea
// due to security reasons) the fts3 support should be enabled explicitly.
// see https://github.com/clementine-player/Clementine/issues/5297
//
// See https://www.sqlite.org/fts3.html#custom_application_defined_tokenizers
QVariant v = db.driver()->handle();
if (v.isValid() && qstrcmp(v.typeName(), "sqlite3*") == 0) {
sqlite3* handle = *static_cast<sqlite3**>(v.data());
if (!handle || sqlite3_db_config(handle, SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, 1, nullptr) != SQLITE_OK) {
qLog(Fatal) << "Failed to enable FTS3 tokenizer";
}
}
#endif
QSqlQuery set_fts_tokenizer("SELECT fts3_tokenizer(:name, :pointer)", db);
set_fts_tokenizer.bindValue(":name", "unicode");
set_fts_tokenizer.bindValue(
":pointer", QByteArray(reinterpret_cast<const char*>(&sFTSTokenizer),
sizeof(&sFTSTokenizer)));
if (!set_fts_tokenizer.exec()) {
qLog(Warning) << "Couldn't register FTS3 tokenizer";
}
// Implicit invocation of ~QSqlQuery() when leaving the scope
// to release any remaining database locks!
}
if (db.tables().count() == 0) {
// Set up initial schema
qLog(Info) << "Creating initial database schema";
UpdateDatabaseSchema(0, db);
}
// Attach external databases
for (const QString& key : attached_databases_.keys()) {
QString filename = attached_databases_[key].filename_;
if (!injected_database_name_.isNull()) filename = injected_database_name_;
// Attach the db
QSqlQuery q("ATTACH DATABASE :filename AS :alias", db);
q.bindValue(":filename", filename);
q.bindValue(":alias", key);
if (!q.exec()) {
qFatal("Couldn't attach external database '%s'",
key.toAscii().constData());
}
}
if (startup_schema_version_ == -1) {
UpdateMainSchema(&db);
}
// We might have to initialise the schema in some attached databases now, if
// they were deleted and don't match up with the main schema version.
for (const QString& key : attached_databases_.keys()) {
if (attached_databases_[key].is_temporary_ &&
attached_databases_[key].schema_.isEmpty())
continue;
// Find out if there are any tables in this database
QSqlQuery q(QString(
"SELECT ROWID FROM %1.sqlite_master"
" WHERE type='table'").arg(key),
db);
if (!q.exec() || !q.next()) {
q.finish();
ExecSchemaCommandsFromFile(db, attached_databases_[key].schema_, 0);
}
}
return db;
}
bool DbRecepient::saveToFile( QSqlDatabase memdb, QString filename, bool save )
{
bool state = false;
QVariant v = memdb.driver()->handle();
if( v.isValid() && qstrcmp(v.typeName(),"sqlite3*") == 0 )
{
// v.data() returns a pointer to the handle
sqlite3 * handle = *static_cast<sqlite3 **>(v.data());
if( handle != 0 ) // check that it is not NULL
{
sqlite3 * pInMemory = handle;
QByteArray array = filename.toUtf8();
const char * zFilename = array.data();
int rc; /* Function return code */
sqlite3 *pFile; /* Database connection opened on zFilename */
sqlite3_backup *pBackup; /* Backup object used to copy data */
sqlite3 *pTo; /* Database to copy to (pFile or pInMemory) */
sqlite3 *pFrom; /* Database to copy from (pFile or pInMemory) */
/* Open the database file identified by zFilename. Exit early if this fails
** for any reason. */
rc = sqlite3_open( zFilename, &pFile );
if( rc==SQLITE_OK ){
/* If this is a 'load' operation (isSave==0), then data is copied
** from the database file just opened to database pInMemory.
** Otherwise, if this is a 'save' operation (isSave==1), then data
** is copied from pInMemory to pFile. Set the variables pFrom and
** pTo accordingly. */
pFrom = ( save ? pInMemory : pFile);
pTo = ( save ? pFile : pInMemory);
/* Set up the backup procedure to copy from the "main" database of
** connection pFile to the main database of connection pInMemory.
** If something goes wrong, pBackup will be set to NULL and an error
** code and message left in connection pTo.
**
** If the backup object is successfully created, call backup_step()
** to copy data from pFile to pInMemory. Then call backup_finish()
** to release resources associated with the pBackup object. If an
** error occurred, then an error code and message will be left in
** connection pTo. If no error occurred, then the error code belonging
** to pTo is set to SQLITE_OK.
*/
pBackup = sqlite3_backup_init(pTo, "main", pFrom, "main");
if( pBackup ){
(void)sqlite3_backup_step(pBackup, -1);
(void)sqlite3_backup_finish(pBackup);
}
rc = sqlite3_errcode(pTo);
}
/* Close the database connection opened on database file zFilename
** and return the result of this function. */
(void)sqlite3_close(pFile);
if( rc == SQLITE_OK ) state = true;
}
}
return state;
}
