MythTV  master
browserdbutil.cpp
Go to the documentation of this file.
1 // Qt
2 #include <QSqlError>
3 
4 // myth
5 #include <mythcontext.h>
6 #include <mythdb.h>
7 #include <mythsorthelper.h>
8 
9 // mythbrowser
10 #include "browserdbutil.h"
11 #include "bookmarkmanager.h"
12 
13 const QString currentDatabaseVersion = "1003";
14 
15 static bool UpdateDBVersionNumber(const QString &newnumber)
16 {
17 
18  if (!gCoreContext->SaveSettingOnHost("BrowserDBSchemaVer", newnumber, nullptr))
19  {
20  LOG(VB_GENERAL, LOG_ERR,
21  QString("DB Error (Setting new DB version number): %1\n")
22  .arg(newnumber));
23 
24  return false;
25  }
26 
27  return true;
28 }
29 
30 static bool performActualUpdate(const QString updates[], const QString& version,
31  QString &dbver)
32 {
34 
35  LOG(VB_GENERAL, LOG_NOTICE,
36  "Upgrading to MythBrowser schema version " + version);
37 
38  int counter = 0;
39  QString thequery = updates[counter];
40 
41  while (thequery != "")
42  {
43  if (!query.exec(thequery))
44  {
45  QString msg =
46  QString("DB Error (Performing database upgrade): \n"
47  "Query was: %1 \nError was: %2 \nnew version: %3")
48  .arg(thequery)
49  .arg(MythDB::DBErrorMessage(query.lastError()))
50  .arg(version);
51  LOG(VB_GENERAL, LOG_ERR, msg);
52  return false;
53  }
54 
55  counter++;
56  thequery = updates[counter];
57  }
58 
60  return false;
61 
62  dbver = version;
63  return true;
64 }
65 
67 {
68  QString dbver = gCoreContext->GetSetting("BrowserDBSchemaVer");
69 
70  if (dbver == currentDatabaseVersion)
71  return true;
72 
73  if (dbver == "")
74  {
75  LOG(VB_GENERAL, LOG_NOTICE,
76  "Inserting MythBrowser initial database information.");
77 
78  const QString updates[] =
79  {
80  "DROP TABLE IF EXISTS websites;",
81  "CREATE TABLE websites ("
82  "id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, "
83  "category VARCHAR(100) NOT NULL, "
84  "name VARCHAR(100) NOT NULL, "
85  "url VARCHAR(255) NOT NULL);",
86  ""
87  };
88  if (!performActualUpdate(updates, "1000", dbver))
89  return false;
90  }
91 
92  if (dbver == "1000")
93  {
94  const QString updates[] =
95  {
96  "UPDATE settings SET data = 'Internal' WHERE data LIKE '%mythbrowser' AND value = 'WebBrowserCommand';",
97  ""
98  };
99  if (!performActualUpdate(updates, "1001", dbver))
100  return false;
101  }
102 
103  if (dbver == "1001")
104  {
105  const QString updates[] =
106  {
107  "DELETE FROM keybindings "
108  " WHERE action = 'DELETETAB' AND context = 'Browser';",
109  ""
110  };
111  if (!performActualUpdate(updates, "1002", dbver))
112  return false;
113  }
114 
115  if (dbver == "1002")
116  {
117  const QString updates[] =
118  {
119  "ALTER TABLE `websites` ADD `homepage` BOOL NOT NULL;",
120  ""
121  };
122  if (!performActualUpdate(updates, "1003", dbver))
123  return false;
124  }
125 
126  return true;
127 }
128 
129 bool FindInDB(const QString &category, const QString& name)
130 {
131  MSqlQuery query(MSqlQuery::InitCon());
132  query.prepare("SELECT name FROM websites "
133  "WHERE category = :CATEGORY AND name = :NAME ;");
134  query.bindValue(":CATEGORY", category);
135  query.bindValue(":NAME", name);
136  if (!query.exec())
137  {
138  MythDB::DBError("mythbrowser: find in db", query);
139  return false;
140  }
141 
142  return (query.size() > 0);
143 }
144 
146 {
147  MSqlQuery query(MSqlQuery::InitCon());
148  query.prepare("UPDATE `websites` SET `homepage` = '0' WHERE `homepage` = '1';");
149 
150  return query.exec();
151 }
152 
154 {
155  MSqlQuery query(MSqlQuery::InitCon());
156  query.prepare("UPDATE `websites` SET `homepage` = '1' "
157  "WHERE `category` = :CATEGORY "
158  "AND `name` = :NAME;");
159  query.bindValue(":CATEGORY", site->m_category);
160  query.bindValue(":NAME", site->m_name);
161 
162  return query.exec();
163 }
164 
165 bool InsertInDB(Bookmark* site)
166 {
167  if (!site)
168  return false;
169 
170  return InsertInDB(site->m_category, site->m_name, site->m_url, site->m_isHomepage);
171 }
172 
173 bool InsertInDB(const QString &category,
174  const QString &name,
175  const QString &url,
176  const bool &isHomepage)
177 {
178  if (category.isEmpty() || name.isEmpty() || url.isEmpty())
179  return false;
180 
181  if (FindInDB(category, name))
182  return false;
183 
184  QString _url = url.trimmed();
185  if (!_url.startsWith("http://") && !_url.startsWith("https://") &&
186  !_url.startsWith("file:/"))
187  _url.prepend("http://");
188 
189  _url.replace("&amp;","&");
190 
191  MSqlQuery query(MSqlQuery::InitCon());
192  query.prepare("INSERT INTO websites (category, name, url, homepage) "
193  "VALUES(:CATEGORY, :NAME, :URL, :HOMEPAGE);");
194  query.bindValue(":CATEGORY", category);
195  query.bindValue(":NAME", name);
196  query.bindValue(":URL", _url);
197  query.bindValue(":HOMEPAGE", isHomepage);
198  if (!query.exec())
199  {
200  MythDB::DBError("mythbrowser: inserting in DB", query);
201  return false;
202  }
203 
204  return (query.numRowsAffected() > 0);
205 }
206 
208 {
209  if (!site)
210  return false;
211 
212  return RemoveFromDB(site->m_category, site->m_name);
213 }
214 
215 bool RemoveFromDB(const QString &category, const QString &name)
216 {
217  MSqlQuery query(MSqlQuery::InitCon());
218  query.prepare("DELETE FROM websites "
219  "WHERE category = :CATEGORY AND name = :NAME;");
220  query.bindValue(":CATEGORY", category);
221  query.bindValue(":NAME", name);
222  if (!query.exec())
223  {
224  MythDB::DBError("mythbrowser: delete from db", query);
225  return false;
226  }
227 
228  return (query.numRowsAffected() > 0);
229 }
230 
231 int GetCategoryList(QStringList &list)
232 {
233  MSqlQuery query(MSqlQuery::InitCon());
234  query.prepare("SELECT DISTINCT category FROM websites "
235  "ORDER BY category;");
236 
237  if (!query.exec())
238  {
239  MythDB::DBError("mythbrowser: get category list", query);
240  return 0;
241  }
242 
243  while (query.next())
244  {
245  list << query.value(0).toString();
246  }
247 
248  return list.size();
249 }
250 
251 int GetSiteList(QList<Bookmark*> &siteList)
252 {
253  while (!siteList.isEmpty())
254  delete siteList.takeFirst();
255 
256  MSqlQuery query(MSqlQuery::InitCon());
257 
258  if (!query.exec("SELECT category, name, url, homepage FROM websites "
259  "ORDER BY category, name"))
260  {
261  LOG(VB_GENERAL, LOG_ERR, "BookmarkManager: Error in loading from DB");
262  }
263  else
264  {
265  std::shared_ptr<MythSortHelper>sh = getMythSortHelper();
266  while (query.next())
267  {
268  Bookmark *site = new Bookmark();
269  site->m_category = query.value(0).toString();
270  site->m_name = query.value(1).toString();
271  site->m_sortName = sh->doTitle(site->m_name);
272  site->m_url = query.value(2).toString();
273  site->m_isHomepage = query.value(3).toBool();
274  site->m_selected = false;
275  siteList.append(site);
276  }
277  std::sort(siteList.begin(), siteList.end(), Bookmark::sortByName);
278  }
279 
280  return siteList.size();
281 }
bool next(void)
Wrap QSqlQuery::next() so we can display the query results.
Definition: mythdbcon.cpp:782
void bindValue(const QString &placeholder, const QVariant &val)
Add a single binding.
Definition: mythdbcon.cpp:863
bool RemoveFromDB(Bookmark *site)
bool UpgradeBrowserDatabaseSchema(void)
bool m_selected
QString m_name
QSqlQuery wrapper that fetches a DB connection from the connection pool.
Definition: mythdbcon.h:125
int size(void) const
Definition: mythdbcon.h:203
bool FindInDB(const QString &category, const QString &name)
static bool sortByName(Bookmark *a, Bookmark *b)
MythCoreContext * gCoreContext
This global variable contains the MythCoreContext instance for the app.
QVariant value(int i) const
Definition: mythdbcon.h:198
const QString currentDatabaseVersion
static bool performActualUpdate(const QString updates[], const QString &version, QString &dbver)
QSqlError lastError(void) const
Definition: mythdbcon.h:202
QString GetSetting(const QString &key, const QString &defaultval="")
bool ResetHomepageFromDB()
QString m_sortName
static bool UpdateDBVersionNumber(const QString &newnumber)
static MSqlQueryInfo InitCon(ConnectionReuse=kNormalConnection)
Only use this in combination with MSqlQuery constructor.
Definition: mythdbcon.cpp:535
const char * name
Definition: ParseText.cpp:328
bool prepare(const QString &query)
QSqlQuery::prepare() is not thread safe in Qt <= 3.3.2.
Definition: mythdbcon.cpp:807
QString m_url
#define LOG(_MASK_, _LEVEL_, _STRING_)
Definition: mythlogging.h:41
int GetCategoryList(QStringList &list)
bool InsertInDB(Bookmark *site)
int numRowsAffected() const
Definition: mythdbcon.h:206
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 UpdateHomepageInDB(Bookmark *site)
bool SaveSettingOnHost(const QString &key, const QString &newValue, const QString &host)
static void DBError(const QString &where, const MSqlQuery &query)
Definition: mythdb.cpp:179
bool m_isHomepage
QString m_category
int GetSiteList(QList< Bookmark * > &siteList)
std::shared_ptr< MythSortHelper > getMythSortHelper(void)
Get a pointer to the MythSortHelper singleton.