MythTV  master
mythplugins/mythweather/mythweather/dbcheck.cpp
Go to the documentation of this file.
1 #include <QString>
2 #include <QDir>
3 #include <QStringList>
4 #include <QSqlError>
5 
6 #include <mythcontext.h>
7 #include <mythdb.h>
8 
9 #include "dbcheck.h"
10 
11 const QString currentDatabaseVersion = "1006";
12 
13 static bool UpdateDBVersionNumber(const QString &newnumber)
14 {
15  if (!gCoreContext->SaveSettingOnHost("WeatherDBSchemaVer",newnumber,nullptr))
16  {
17  LOG(VB_GENERAL, LOG_ERR,
18  QString("DB Error (Setting new DB version number): %1\n")
19  .arg(newnumber));
20 
21  return false;
22  }
23 
24  return true;
25 }
26 
27 static bool performActualUpdate(const QStringList& updates, const QString& version,
28  QString &dbver)
29 {
30  LOG(VB_GENERAL, LOG_NOTICE,
31  "Upgrading to MythWeather schema version " + version);
32 
34 
35  QStringList::const_iterator it = updates.begin();
36 
37  while (it != updates.end())
38  {
39  QString thequery = *it;
40  if (!query.exec(thequery))
41  {
42  QString msg =
43  QString("DB Error (Performing database upgrade): \n"
44  "Query was: %1 \nError was: %2 \nnew version: %3")
45  .arg(thequery)
46  .arg(MythDB::DBErrorMessage(query.lastError()))
47  .arg(version);
48  LOG(VB_GENERAL, LOG_ERR, msg);
49  return false;
50  }
51  ++it;
52  }
53 
55  return false;
56 
57  dbver = version;
58  return true;
59 }
60 
61 /*
62  * TODO Probably the biggest change to simplify things would be to get rid of
63  * the surrogate key screen_id in weatherscreens, draworder should be unique,
64  * that way, with cascading, updating screens won't need to blow out everything
65  * in the db everytime.
66  */
68 {
69  QString dbver = gCoreContext->GetSetting("WeatherDBSchemaVer");
70 
71  if (dbver == currentDatabaseVersion)
72  return true;
73 
74  if (dbver == "")
75  {
76  LOG(VB_GENERAL, LOG_NOTICE,
77  "Inserting MythWeather initial database information.");
78  QStringList updates;
79  updates << "CREATE TABLE IF NOT EXISTS weathersourcesettings ("
80  "sourceid INT UNSIGNED NOT NULL AUTO_INCREMENT,"
81  "source_name VARCHAR(64) NOT NULL,"
82  "update_timeout INT UNSIGNED NOT NULL DEFAULT '600',"
83  "retrieve_timeout INT UNSIGNED NOT NULL DEFAULT '60',"
84  "hostname VARCHAR(255) NULL,"
85  "path VARCHAR(255) NULL,"
86  "author VARCHAR(128) NULL,"
87  "version VARCHAR(32) NULL,"
88  "email VARCHAR(255) NULL,"
89  "types MEDIUMTEXT NULL,"
90  "PRIMARY KEY(sourceid)) ENGINE=InnoDB;"
91  << "CREATE TABLE IF NOT EXISTS weatherscreens ("
92  "screen_id INT UNSIGNED NOT NULL AUTO_INCREMENT,"
93  "draworder INT UNSIGNED NOT NULL,"
94  "container VARCHAR(64) NOT NULL,"
95  "hostname VARCHAR(255) NULL,"
96  "units TINYINT UNSIGNED NOT NULL,"
97  "PRIMARY KEY(screen_id)) ENGINE=InnoDB;"
98  << "CREATE TABLE IF NOT EXISTS weatherdatalayout ("
99  "location VARCHAR(64) NOT NULL,"
100  "dataitem VARCHAR(64) NOT NULL,"
101  "weatherscreens_screen_id INT UNSIGNED NOT NULL,"
102  "weathersourcesettings_sourceid INT UNSIGNED NOT NULL,"
103  "PRIMARY KEY(location, dataitem, weatherscreens_screen_id,"
104  "weathersourcesettings_sourceid),"
105  "INDEX weatherdatalayout_FKIndex1(weatherscreens_screen_id),"
106  "INDEX weatherdatalayout_FKIndex2(weathersourcesettings_sourceid),"
107  "FOREIGN KEY(weatherscreens_screen_id) "
108  "REFERENCES weatherscreens(screen_id) "
109  "ON DELETE CASCADE "
110  "ON UPDATE CASCADE,"
111  "FOREIGN KEY(weathersourcesettings_sourceid) "
112  "REFERENCES weathersourcesettings(sourceid) "
113  "ON DELETE RESTRICT "
114  "ON UPDATE CASCADE) ENGINE=InnoDB;";
115  /*
116  * TODO Possible want to delete old stuff (i.e. agressiveness, locale..)
117  * that we don't use any more
118  */
119 
120  if (!performActualUpdate(updates, "1000", dbver))
121  return false;
122  }
123 
124  if (dbver == "1000")
125  {
126  QStringList updates;
127  updates << "ALTER TABLE weathersourcesettings ADD COLUMN updated "
128  "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP "
129  " ON UPDATE CURRENT_TIMESTAMP;";
130 
131  if (!performActualUpdate(updates, "1001", dbver))
132  return false;
133  }
134 
135 
136 
137  if (dbver == "1001")
138  {
139  QStringList updates;
140  updates << QString("ALTER DATABASE %1 DEFAULT CHARACTER SET latin1;")
142  "ALTER TABLE weatherdatalayout"
143  " MODIFY location varbinary(64) NOT NULL,"
144  " MODIFY dataitem varbinary(64) NOT NULL;" <<
145  "ALTER TABLE weatherscreens"
146  " MODIFY container varbinary(64) NOT NULL,"
147  " MODIFY hostname varbinary(64) default NULL;" <<
148  "ALTER TABLE weathersourcesettings"
149  " MODIFY source_name varbinary(64) NOT NULL,"
150  " MODIFY hostname varbinary(64) default NULL,"
151  " MODIFY path varbinary(255) default NULL,"
152  " MODIFY author varbinary(128) default NULL,"
153  " MODIFY version varbinary(32) default NULL,"
154  " MODIFY email varbinary(255) default NULL,"
155  " MODIFY types mediumblob;";
156 
157  if (!performActualUpdate(updates, "1002", dbver))
158  return false;
159  }
160 
161 
162  if (dbver == "1002")
163  {
164  QStringList updates;
165  updates << QString("ALTER DATABASE %1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;")
167  "ALTER TABLE weatherdatalayout"
168  " DEFAULT CHARACTER SET default,"
169  " MODIFY location varchar(64) CHARACTER SET utf8 NOT NULL,"
170  " MODIFY dataitem varchar(64) CHARACTER SET utf8 NOT NULL;" <<
171  "ALTER TABLE weatherscreens"
172  " DEFAULT CHARACTER SET default,"
173  " MODIFY container varchar(64) CHARACTER SET utf8 NOT NULL,"
174  " MODIFY hostname varchar(64) CHARACTER SET utf8 default NULL;" <<
175  "ALTER TABLE weathersourcesettings"
176  " DEFAULT CHARACTER SET default,"
177  " MODIFY source_name varchar(64) CHARACTER SET utf8 NOT NULL,"
178  " MODIFY hostname varchar(64) CHARACTER SET utf8 default NULL,"
179  " MODIFY path varchar(255) CHARACTER SET utf8 default NULL,"
180  " MODIFY author varchar(128) CHARACTER SET utf8 default NULL,"
181  " MODIFY version varchar(32) CHARACTER SET utf8 default NULL,"
182  " MODIFY email varchar(255) CHARACTER SET utf8 default NULL,"
183  " MODIFY types mediumtext CHARACTER SET utf8;";
184 
185  if (!performActualUpdate(updates, "1003", dbver))
186  return false;
187  }
188 
189  if (dbver == "1003")
190  {
191  QStringList updates;
192  updates << "DELETE FROM keybindings "
193  " WHERE action = 'DELETE' AND context = 'Weather';";
194 
195  if (!performActualUpdate(updates, "1004", dbver))
196  return false;
197  }
198 
199  if (dbver == "1004")
200  {
201  QStringList updates;
202  updates << "ALTER TABLE weatherdatalayout"
203  " MODIFY location varchar(128) CHARACTER SET utf8 NOT NULL;";
204 
205  if (!performActualUpdate(updates, "1005", dbver))
206  return false;
207  }
208 
209  if (dbver == "1005")
210  {
211  QStringList updates;
212  updates << "ALTER TABLE weathersourcesettings MODIFY COLUMN updated "
213  " TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP "
214  " ON UPDATE CURRENT_TIMESTAMP;";
215 
216  if (!performActualUpdate(updates, "1006", dbver))
217  return false;
218  }
219 
220  return true;
221 }
QSqlQuery wrapper that fetches a DB connection from the connection pool.
Definition: mythdbcon.h:125
MythCoreContext * gCoreContext
This global variable contains the MythCoreContext instance for the app.
DatabaseParams GetDatabaseParams(void)
MythContext * gContext
This global variable contains the MythContext instance for the application.
Definition: mythcontext.cpp:62
static bool UpdateDBVersionNumber(const QString &newnumber)
static bool performActualUpdate(const QStringList &updates, const QString &version, QString &dbver)
const QString currentDatabaseVersion
QSqlError lastError(void) const
Definition: mythdbcon.h:202
QString GetSetting(const QString &key, const QString &defaultval="")
QString m_dbName
database name
Definition: mythdbparams.h:26
static MSqlQueryInfo InitCon(ConnectionReuse=kNormalConnection)
Only use this in combination with MSqlQuery constructor.
Definition: mythdbcon.cpp:535
#define LOG(_MASK_, _LEVEL_, _STRING_)
Definition: mythlogging.h:41
static QString DBErrorMessage(const QSqlError &err)
Definition: mythdb.cpp:184
bool exec(void)
Wrap QSqlQuery::exec() so we can display SQL.
Definition: mythdbcon.cpp:603
bool SaveSettingOnHost(const QString &key, const QString &newValue, const QString &host)