1/****************************************************************************
2**
3** Copyright (C) 2020 The Qt Company Ltd.
4** Contact: https://www.qt.io/licensing/
5**
6** This file is part of the demonstration applications of the Qt Toolkit.
7**
8** $QT_BEGIN_LICENSE:BSD$
9** Commercial License Usage
10** Licensees holding valid commercial Qt licenses may use this file in
11** accordance with the commercial license agreement provided with the
12** Software or, alternatively, in accordance with the terms contained in
13** a written agreement between you and The Qt Company. For licensing terms
14** and conditions see https://www.qt.io/terms-conditions. For further
15** information use the contact form at https://www.qt.io/contact-us.
16**
17** BSD License Usage
18** Alternatively, you may use this file under the terms of the BSD license
19** as follows:
20**
21** "Redistribution and use in source and binary forms, with or without
22** modification, are permitted provided that the following conditions are
23** met:
24** * Redistributions of source code must retain the above copyright
25** notice, this list of conditions and the following disclaimer.
26** * Redistributions in binary form must reproduce the above copyright
27** notice, this list of conditions and the following disclaimer in
28** the documentation and/or other materials provided with the
29** distribution.
30** * Neither the name of The Qt Company Ltd nor the names of its
31** contributors may be used to endorse or promote products derived
32** from this software without specific prior written permission.
33**
34**
35** THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
36** "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
37** LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
38** A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
39** OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
40** SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
41** LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
42** DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
43** THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
44** (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
45** OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE."
46**
47** $QT_END_LICENSE$
48**
49****************************************************************************/
50
51#ifndef INITDB_H
52#define INITDB_H
53
54#include <QtSql>
55
56void addBook(QSqlQuery &q, const QString &title, int year, const QVariant &authorId,
57 const QVariant &genreId, int rating)
58{
59 q.addBindValue(val: title);
60 q.addBindValue(val: year);
61 q.addBindValue(val: authorId);
62 q.addBindValue(val: genreId);
63 q.addBindValue(val: rating);
64 q.exec();
65}
66
67QVariant addGenre(QSqlQuery &q, const QString &name)
68{
69 q.addBindValue(val: name);
70 q.exec();
71 return q.lastInsertId();
72}
73
74QVariant addAuthor(QSqlQuery &q, const QString &name, QDate birthdate)
75{
76 q.addBindValue(val: name);
77 q.addBindValue(val: birthdate);
78 q.exec();
79 return q.lastInsertId();
80}
81
82const auto BOOKS_SQL = QLatin1String(R"(
83 create table books(id integer primary key, title varchar, author integer,
84 genre integer, year integer, rating integer)
85 )");
86
87const auto AUTHORS_SQL = QLatin1String(R"(
88 create table authors(id integer primary key, name varchar, birthdate date)
89 )");
90
91const auto GENRES_SQL = QLatin1String(R"(
92 create table genres(id integer primary key, name varchar)
93 )");
94
95const auto INSERT_AUTHOR_SQL = QLatin1String(R"(
96 insert into authors(name, birthdate) values(?, ?)
97 )");
98
99const auto INSERT_BOOK_SQL = QLatin1String(R"(
100 insert into books(title, year, author, genre, rating)
101 values(?, ?, ?, ?, ?)
102 )");
103
104const auto INSERT_GENRE_SQL = QLatin1String(R"(
105 insert into genres(name) values(?)
106 )");
107
108QSqlError initDb()
109{
110 QSqlDatabase db = QSqlDatabase::addDatabase(type: "QSQLITE");
111 db.setDatabaseName(":memory:");
112
113 if (!db.open())
114 return db.lastError();
115
116 QStringList tables = db.tables();
117 if (tables.contains(str: "books", cs: Qt::CaseInsensitive)
118 && tables.contains(str: "authors", cs: Qt::CaseInsensitive))
119 return QSqlError();
120
121 QSqlQuery q;
122 if (!q.exec(query: BOOKS_SQL))
123 return q.lastError();
124 if (!q.exec(query: AUTHORS_SQL))
125 return q.lastError();
126 if (!q.exec(query: GENRES_SQL))
127 return q.lastError();
128
129 if (!q.prepare(query: INSERT_AUTHOR_SQL))
130 return q.lastError();
131 QVariant asimovId = addAuthor(q, name: QLatin1String("Isaac Asimov"), birthdate: QDate(1920, 2, 1));
132 QVariant greeneId = addAuthor(q, name: QLatin1String("Graham Greene"), birthdate: QDate(1904, 10, 2));
133 QVariant pratchettId = addAuthor(q, name: QLatin1String("Terry Pratchett"), birthdate: QDate(1948, 4, 28));
134
135 if (!q.prepare(query: INSERT_GENRE_SQL))
136 return q.lastError();
137 QVariant sfiction = addGenre(q, name: QLatin1String("Science Fiction"));
138 QVariant fiction = addGenre(q, name: QLatin1String("Fiction"));
139 QVariant fantasy = addGenre(q, name: QLatin1String("Fantasy"));
140
141 if (!q.prepare(query: INSERT_BOOK_SQL))
142 return q.lastError();
143 addBook(q, title: QLatin1String("Foundation"), year: 1951, authorId: asimovId, genreId: sfiction, rating: 3);
144 addBook(q, title: QLatin1String("Foundation and Empire"), year: 1952, authorId: asimovId, genreId: sfiction, rating: 4);
145 addBook(q, title: QLatin1String("Second Foundation"), year: 1953, authorId: asimovId, genreId: sfiction, rating: 3);
146 addBook(q, title: QLatin1String("Foundation's Edge"), year: 1982, authorId: asimovId, genreId: sfiction, rating: 3);
147 addBook(q, title: QLatin1String("Foundation and Earth"), year: 1986, authorId: asimovId, genreId: sfiction, rating: 4);
148 addBook(q, title: QLatin1String("Prelude to Foundation"), year: 1988, authorId: asimovId, genreId: sfiction, rating: 3);
149 addBook(q, title: QLatin1String("Forward the Foundation"), year: 1993, authorId: asimovId, genreId: sfiction, rating: 3);
150 addBook(q, title: QLatin1String("The Power and the Glory"), year: 1940, authorId: greeneId, genreId: fiction, rating: 4);
151 addBook(q, title: QLatin1String("The Third Man"), year: 1950, authorId: greeneId, genreId: fiction, rating: 5);
152 addBook(q, title: QLatin1String("Our Man in Havana"), year: 1958, authorId: greeneId, genreId: fiction, rating: 4);
153 addBook(q, title: QLatin1String("Guards! Guards!"), year: 1989, authorId: pratchettId, genreId: fantasy, rating: 3);
154 addBook(q, title: QLatin1String("Night Watch"), year: 2002, authorId: pratchettId, genreId: fantasy, rating: 3);
155 addBook(q, title: QLatin1String("Going Postal"), year: 2004, authorId: pratchettId, genreId: fantasy, rating: 3);
156
157 return QSqlError();
158}
159
160#endif
161

source code of qtbase/examples/sql/books/initdb.h