[6ea066]: script.games.xbmame / resources / lib / obj / DBHelper.py  Maximize  Restore  History

Download this file

141 lines (116 with data), 5.7 kB

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
# The contents of this file are subject to the Mozilla Public License
# Version 1.1 (the "License"); you may not use this file except in
# compliance with the License. You may obtain a copy of the License at
# http://www.mozilla.org/MPL/
#
# Software distributed under the License is distributed on an "AS IS"
# basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
# License for the specific language governing rights and limitations
# under the License.
#
# The Original Code is plugin.games.xbmame.
#
# The Initial Developer of the Original Code is Olivier LODY aka Akira76.
# Portions created by the XBMC team are Copyright (C) 2003-2010 XBMC.
# All Rights Reserved.
from pysqlite2 import dbapi2 as sqlite
class DBHelper(object):
_TABLES = [ "Games", "BiosSets", "Dipswitches", "DipswitchesValues" ]
def __init__(self, dbpath):
self._db = sqlite.connect(dbpath)
self._cursor = self._db.cursor()
if self.needsSchema():
self.execute("CREATE TABLE GameSettings (id INTEGER PRIMARY KEY, romset TEXT, view INTEGER, rotate INTEGER, backdrops BOOLEAN, overlays BOOLEAN, bezels BOOLEAN, zoom BOOLEAN, have BOOLEAN, thumb BOOLEAN)")
self.execute("CREATE TABLE XBMame (id INTEGER PRIMARY KEY, setting TEXT, value TEXT)")
self.execute("CREATE TABLE Favorites (id INTEGER PRIMARY KEY, gamename TEXT)")
self.execute("CREATE TABLE Games (id INTEGER PRIMARY KEY, romset TEXT, cloneof TEXT, romof TEXT, biosset TEXT, driver TEXT, gamename TEXT, gamecomment TEXT, manufacturer TEXT, year TEXT, isbios BOOLEAN, hasdisk BOOLEAN, isworking BOOLEAN, emul BOOLEAN, color BOOLEAN, graphic BOOLEAN, sound BOOLEAN, hasdips BOOLEAN, view INTEGER, rotate INTEGER, backdrops BOOLEAN, overlays BOOLEAN, bezels BOOLEAN, zoom BOOLEAN, have BOOLEAN, thumb BOOLEAN, history INTEGER, info INTEGER)")
self.execute("CREATE TABLE BiosSets (id INTEGER PRIMARY KEY, romset_id INTEGER, name TEXT, description TEXT)")
self.execute("CREATE TABLE Dipswitches (id INTEGER PRIMARY KEY, romset_id integer, name TEXT, tag TEXT, mask INTEGER, defvalue INTEGER, value INTEGER)")
self.execute("CREATE TABLE DipswitchesValues (id INTEGER PRIMARY KEY, dipswitch_id INTEGER, name TEXT, value TEXT)")
self.commit()
def commit(self):
self.db.commit()
def isEmpty(self):
rows = 0
for t in self._TABLES:
if len(self.runQuery("SELECT * FROM %s" % t, ())):rows+=1;
return (rows==0)
def needsSchema(self):
if self.getCount("main.sqlite_master")==0:
return True
else:
rows = 0
for t in self._TABLES:
if len(self.runQuery("SELECT * FROM main.sqlite_master WHERE name=?", (t,))):rows += 1
return (rows==0)
def dropTable(self, table):
if self.tableExists(table):
self.execute("DROP TABLE %s" % table)
self.commit()
def tableExists(self, table):
return bool(len(self.runQuery("SELECT * FROM sqlite_master WHERE name=?", (table,))))
def getSetting(self, setting):
try:
return self.Query(
"SELECT value FROM XBMame WHERE setting=?",
(setting,)
)[0][0]
except IndexError:
return ""
def setSetting(self, setting, value):
if self.getSetting(setting)=="":
self.execute("INSERT INTO XBMame VALUES(null, ?, ?)", (setting, value,))
else:
self.execute("UPDATE XBMame SET value=? WHERE setting=?", (value, setting,))
def execute(self, sql, values=""):
if values:
self._cursor.execute(sql, values)
else:
self._cursor.execute(sql)
return self._cursor.lastrowid
def getCount(self, table):
self._cursor.execute("SELECT count(*) as tblcount FROM %s" % table)
return self._cursor.fetchone()[0]
def commit(self):
self._db.commit()
def Query(self, sql, values):
try:
self._cursor.execute(sql, values)
results = self._cursor.fetchall()
return results
except sqlite.OperationalError:
return ""
def getList(self, table, fields, criteria={}):
filters=""
for key in criteria.keys():
filters+=" AND %s%s" % (key, criteria[key])
fieldlist = ""
for field in fields:
fieldlist += ", %s" % field
fieldlist = fieldlist[1:]
sql = "SELECT %s FROM %s WHERE id>0 %s GROUP BY %s ORDER BY %s ASC" % (fieldlist, table, filters, fields[0], fields[0])
return self.runQuery(sql)
def getResult(self, table, field, id):
return self.runQuery("SELECT %s FROM %s WHERE id=%s" % (field, table, id))[0]
def getResults(self, table, field, id):
return self.runQuery("SELECT %s FROM %s WHERE id=%s" % (field, table, id))
def runQuery(self, sql, values=()):
cursor = self._db.cursor()
cursor.execute(sql, values)
headers = [tuple[0] for tuple in cursor.description]
results = cursor.fetchall()
return self._getRows(headers, results)
def _getRow(self, headers, results):
for result in results:
fields = {}
for i in range(len(headers)):
fields[headers[i]]=result[i]
return fields
def _getRows(self, headers, results):
values = []
for result in results:
fields = {}
for i in range(len(headers)):
fields[headers[i]]=result[i]
values.append(fields)
return values

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks