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 <mythdbcheck.h>
8 #include <mythsorthelper.h>
9 
10 // mythbrowser
11 #include "browserdbutil.h"
12 #include "bookmarkmanager.h"
13 
14 const QString currentDatabaseVersion = "1003";
15 const QString MythBrowserVersionName = "BrowserDBSchemaVer";
16 
18 {
19  QString dbver = gCoreContext->GetSetting("BrowserDBSchemaVer");
20 
21  if (dbver == currentDatabaseVersion)
22  return true;
23 
24  if (dbver == "")
25  {
26  LOG(VB_GENERAL, LOG_NOTICE,
27  "Inserting MythBrowser initial database information.");
28 
29  DBUpdates updates
30  {
31  "DROP TABLE IF EXISTS websites;",
32  "CREATE TABLE websites ("
33  "id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, "
34  "category VARCHAR(100) NOT NULL, "
35  "name VARCHAR(100) NOT NULL, "
36  "url VARCHAR(255) NOT NULL);"
37  };
38  if (!performActualUpdate("MythBrowser", MythBrowserVersionName,
39  updates, "1000", dbver))
40  return false;
41  }
42 
43  if (dbver == "1000")
44  {
45  DBUpdates updates
46  {
47  "UPDATE settings SET data = 'Internal' WHERE data LIKE '%mythbrowser' AND value = 'WebBrowserCommand';"
48  };
49  if (!performActualUpdate("MythBrowser", MythBrowserVersionName,
50  updates, "1001", dbver))
51  return false;
52  }
53 
54  if (dbver == "1001")
55  {
56  DBUpdates updates
57  {
58  "DELETE FROM keybindings "
59  " WHERE action = 'DELETETAB' AND context = 'Browser';"
60  };
61  if (!performActualUpdate("MythBrowser", MythBrowserVersionName,
62  updates, "1002", dbver))
63  return false;
64  }
65 
66  if (dbver == "1002")
67  {
68  DBUpdates updates
69  {
70  "ALTER TABLE `websites` ADD `homepage` BOOL NOT NULL;"
71  };
72  if (!performActualUpdate("MythBrowser", MythBrowserVersionName,
73  updates, "1003", dbver))
74  return false;
75  }
76 
77  return true;
78 }
79 
80 bool FindInDB(const QString &category, const QString& name)
81 {
83  query.prepare("SELECT name FROM websites "
84  "WHERE category = :CATEGORY AND name = :NAME ;");
85  query.bindValue(":CATEGORY", category);
86  query.bindValue(":NAME", name);
87  if (!query.exec())
88  {
89  MythDB::DBError("mythbrowser: find in db", query);
90  return false;
91  }
92 
93  return (query.size() > 0);
94 }
95 
97 {
99  query.prepare("UPDATE `websites` SET `homepage` = '0' WHERE `homepage` = '1';");
100 
101  return query.exec();
102 }
103 
105 {
107  query.prepare("UPDATE `websites` SET `homepage` = '1' "
108  "WHERE `category` = :CATEGORY "
109  "AND `name` = :NAME;");
110  query.bindValue(":CATEGORY", site->m_category);
111  query.bindValue(":NAME", site->m_name);
112 
113  return query.exec();
114 }
115 
116 bool InsertInDB(Bookmark* site)
117 {
118  if (!site)
119  return false;
120 
121  return InsertInDB(site->m_category, site->m_name, site->m_url, site->m_isHomepage);
122 }
123 
124 bool InsertInDB(const QString &category,
125  const QString &name,
126  const QString &url,
127  const bool &isHomepage)
128 {
129  if (category.isEmpty() || name.isEmpty() || url.isEmpty())
130  return false;
131 
132  if (FindInDB(category, name))
133  return false;
134 
135  QString _url = url.trimmed();
136  if (!_url.startsWith("http://") && !_url.startsWith("https://") &&
137  !_url.startsWith("file:/"))
138  _url.prepend("http://");
139 
140  _url.replace("&amp;","&");
141 
143  query.prepare("INSERT INTO websites (category, name, url, homepage) "
144  "VALUES(:CATEGORY, :NAME, :URL, :HOMEPAGE);");
145  query.bindValue(":CATEGORY", category);
146  query.bindValue(":NAME", name);
147  query.bindValue(":URL", _url);
148  query.bindValue(":HOMEPAGE", isHomepage);
149  if (!query.exec())
150  {
151  MythDB::DBError("mythbrowser: inserting in DB", query);
152  return false;
153  }
154 
155  return (query.numRowsAffected() > 0);
156 }
157 
159 {
160  if (!site)
161  return false;
162 
163  return RemoveFromDB(site->m_category, site->m_name);
164 }
165 
166 bool RemoveFromDB(const QString &category, const QString &name)
167 {
169  query.prepare("DELETE FROM websites "
170  "WHERE category = :CATEGORY AND name = :NAME;");
171  query.bindValue(":CATEGORY", category);
172  query.bindValue(":NAME", name);
173  if (!query.exec())
174  {
175  MythDB::DBError("mythbrowser: delete from db", query);
176  return false;
177  }
178 
179  return (query.numRowsAffected() > 0);
180 }
181 
182 int GetCategoryList(QStringList &list)
183 {
185  query.prepare("SELECT DISTINCT category FROM websites "
186  "ORDER BY category;");
187 
188  if (!query.exec())
189  {
190  MythDB::DBError("mythbrowser: get category list", query);
191  return 0;
192  }
193 
194  while (query.next())
195  {
196  list << query.value(0).toString();
197  }
198 
199  return list.size();
200 }
201 
202 int GetSiteList(QList<Bookmark*> &siteList)
203 {
204  while (!siteList.isEmpty())
205  delete siteList.takeFirst();
206 
208 
209  if (!query.exec("SELECT category, name, url, homepage FROM websites "
210  "ORDER BY category, name"))
211  {
212  LOG(VB_GENERAL, LOG_ERR, "BookmarkManager: Error in loading from DB");
213  }
214  else
215  {
216  std::shared_ptr<MythSortHelper>sh = getMythSortHelper();
217  while (query.next())
218  {
219  auto *site = new Bookmark();
220  site->m_category = query.value(0).toString();
221  site->m_name = query.value(1).toString();
222  site->m_sortName = sh->doTitle(site->m_name);
223  site->m_url = query.value(2).toString();
224  site->m_isHomepage = query.value(3).toBool();
225  site->m_selected = false;
226  siteList.append(site);
227  }
228  std::sort(siteList.begin(), siteList.end(), Bookmark::sortByName);
229  }
230 
231  return siteList.size();
232 }
MSqlQuery::next
bool next(void)
Wrap QSqlQuery::next() so we can display the query results.
Definition: mythdbcon.cpp:783
MSqlQuery
QSqlQuery wrapper that fetches a DB connection from the connection pool.
Definition: mythdbcon.h:126
MSqlQuery::size
int size(void) const
Definition: mythdbcon.h:203
UpgradeBrowserDatabaseSchema
bool UpgradeBrowserDatabaseSchema(void)
Definition: browserdbutil.cpp:17
InsertInDB
bool InsertInDB(Bookmark *site)
Definition: browserdbutil.cpp:116
getMythSortHelper
std::shared_ptr< MythSortHelper > getMythSortHelper(void)
Get a pointer to the MythSortHelper singleton.
Definition: mythsorthelper.cpp:126
mythdb.h
bookmarkmanager.h
UpdateHomepageInDB
bool UpdateHomepageInDB(Bookmark *site)
Definition: browserdbutil.cpp:104
MSqlQuery::value
QVariant value(int i) const
Definition: mythdbcon.h:198
RemoveFromDB
bool RemoveFromDB(Bookmark *site)
Definition: browserdbutil.cpp:158
MSqlQuery::exec
bool exec(void)
Wrap QSqlQuery::exec() so we can display SQL.
Definition: mythdbcon.cpp:603
category
QString category
Definition: mythplugins/mytharchive/mytharchivehelper/main.cpp:1419
Bookmark::m_url
QString m_url
Definition: bookmarkmanager.h:21
LOG
#define LOG(_MASK_, _LEVEL_, _QSTRING_)
Definition: mythlogging.h:23
Bookmark::m_isHomepage
bool m_isHomepage
Definition: bookmarkmanager.h:22
mythsorthelper.h
MSqlQuery::InitCon
static MSqlQueryInfo InitCon(ConnectionReuse _reuse=kNormalConnection)
Only use this in combination with MSqlQuery constructor.
Definition: mythdbcon.cpp:535
MythDB::DBError
static void DBError(const QString &where, const MSqlQuery &query)
Definition: mythdb.cpp:179
Bookmark::m_category
QString m_category
Definition: bookmarkmanager.h:18
browserdbutil.h
mythdbcheck.h
currentDatabaseVersion
const QString currentDatabaseVersion
Definition: browserdbutil.cpp:14
ResetHomepageFromDB
bool ResetHomepageFromDB()
Definition: browserdbutil.cpp:96
FindInDB
bool FindInDB(const QString &category, const QString &name)
Definition: browserdbutil.cpp:80
Bookmark
Definition: bookmarkmanager.h:14
gCoreContext
MythCoreContext * gCoreContext
This global variable contains the MythCoreContext instance for the app.
Definition: mythcorecontext.cpp:57
performActualUpdate
bool performActualUpdate(const QString &component, const QString &versionkey, const DBUpdates &updates, const QString &version, QString &dbver)
Definition: dbcheckcommon.cpp:114
MSqlQuery::bindValue
void bindValue(const QString &placeholder, const QVariant &val)
Add a single binding.
Definition: mythdbcon.cpp:864
mythcontext.h
MSqlQuery::numRowsAffected
int numRowsAffected() const
Definition: mythdbcon.h:206
GetCategoryList
int GetCategoryList(QStringList &list)
Definition: browserdbutil.cpp:182
DBUpdates
std::vector< std::string > DBUpdates
Definition: mythdbcheck.h:9
Bookmark::m_name
QString m_name
Definition: bookmarkmanager.h:19
GetSiteList
int GetSiteList(QList< Bookmark * > &siteList)
Definition: browserdbutil.cpp:202
Bookmark::sortByName
static bool sortByName(Bookmark *a, Bookmark *b)
Definition: bookmarkmanager.h:29
query
MSqlQuery query(MSqlQuery::InitCon())
MythCoreContext::GetSetting
QString GetSetting(const QString &key, const QString &defaultval="")
Definition: mythcorecontext.cpp:916
MSqlQuery::prepare
bool prepare(const QString &query)
QSqlQuery::prepare() is not thread safe in Qt <= 3.3.2.
Definition: mythdbcon.cpp:808
MythBrowserVersionName
const QString MythBrowserVersionName
Definition: browserdbutil.cpp:15