db.py 10 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276
  1. import logging
  2. import os.path
  3. import errno
  4. import prserv
  5. import time
  6. try:
  7. import sqlite3
  8. except ImportError:
  9. from pysqlite2 import dbapi2 as sqlite3
  10. logger = logging.getLogger("BitBake.PRserv")
  11. sqlversion = sqlite3.sqlite_version_info
  12. if sqlversion[0] < 3 or (sqlversion[0] == 3 and sqlversion[1] < 3):
  13. raise Exception("sqlite3 version 3.3.0 or later is required.")
  14. #
  15. # "No History" mode - for a given query tuple (version, pkgarch, checksum),
  16. # the returned value will be the largest among all the values of the same
  17. # (version, pkgarch). This means the PR value returned can NOT be decremented.
  18. #
  19. # "History" mode - Return a new higher value for previously unseen query
  20. # tuple (version, pkgarch, checksum), otherwise return historical value.
  21. # Value can decrement if returning to a previous build.
  22. #
  23. class PRTable(object):
  24. def __init__(self, conn, table, nohist):
  25. self.conn = conn
  26. self.nohist = nohist
  27. self.dirty = False
  28. if nohist:
  29. self.table = "%s_nohist" % table
  30. else:
  31. self.table = "%s_hist" % table
  32. self._execute("CREATE TABLE IF NOT EXISTS %s \
  33. (version TEXT NOT NULL, \
  34. pkgarch TEXT NOT NULL, \
  35. checksum TEXT NOT NULL, \
  36. value INTEGER, \
  37. PRIMARY KEY (version, pkgarch, checksum));" % self.table)
  38. def _execute(self, *query):
  39. """Execute a query, waiting to acquire a lock if necessary"""
  40. start = time.time()
  41. end = start + 20
  42. while True:
  43. try:
  44. return self.conn.execute(*query)
  45. except sqlite3.OperationalError as exc:
  46. if 'is locked' in str(exc) and end > time.time():
  47. continue
  48. raise exc
  49. def sync(self):
  50. self.conn.commit()
  51. self._execute("BEGIN EXCLUSIVE TRANSACTION")
  52. def sync_if_dirty(self):
  53. if self.dirty:
  54. self.sync()
  55. self.dirty = False
  56. def _getValueHist(self, version, pkgarch, checksum):
  57. data=self._execute("SELECT value FROM %s WHERE version=? AND pkgarch=? AND checksum=?;" % self.table,
  58. (version, pkgarch, checksum))
  59. row=data.fetchone()
  60. if row != None:
  61. return row[0]
  62. else:
  63. #no value found, try to insert
  64. try:
  65. self._execute("INSERT INTO %s VALUES (?, ?, ?, (select ifnull(max(value)+1,0) from %s where version=? AND pkgarch=?));"
  66. % (self.table,self.table),
  67. (version,pkgarch, checksum,version, pkgarch))
  68. except sqlite3.IntegrityError as exc:
  69. logger.error(str(exc))
  70. self.dirty = True
  71. data=self._execute("SELECT value FROM %s WHERE version=? AND pkgarch=? AND checksum=?;" % self.table,
  72. (version, pkgarch, checksum))
  73. row=data.fetchone()
  74. if row != None:
  75. return row[0]
  76. else:
  77. raise prserv.NotFoundError
  78. def _getValueNohist(self, version, pkgarch, checksum):
  79. data=self._execute("SELECT value FROM %s \
  80. WHERE version=? AND pkgarch=? AND checksum=? AND \
  81. value >= (select max(value) from %s where version=? AND pkgarch=?);"
  82. % (self.table, self.table),
  83. (version, pkgarch, checksum, version, pkgarch))
  84. row=data.fetchone()
  85. if row != None:
  86. return row[0]
  87. else:
  88. #no value found, try to insert
  89. try:
  90. self._execute("INSERT OR REPLACE INTO %s VALUES (?, ?, ?, (select ifnull(max(value)+1,0) from %s where version=? AND pkgarch=?));"
  91. % (self.table,self.table),
  92. (version, pkgarch, checksum, version, pkgarch))
  93. except sqlite3.IntegrityError as exc:
  94. logger.error(str(exc))
  95. self.conn.rollback()
  96. self.dirty = True
  97. data=self._execute("SELECT value FROM %s WHERE version=? AND pkgarch=? AND checksum=?;" % self.table,
  98. (version, pkgarch, checksum))
  99. row=data.fetchone()
  100. if row != None:
  101. return row[0]
  102. else:
  103. raise prserv.NotFoundError
  104. def getValue(self, version, pkgarch, checksum):
  105. if self.nohist:
  106. return self._getValueNohist(version, pkgarch, checksum)
  107. else:
  108. return self._getValueHist(version, pkgarch, checksum)
  109. def _importHist(self, version, pkgarch, checksum, value):
  110. val = None
  111. data = self._execute("SELECT value FROM %s WHERE version=? AND pkgarch=? AND checksum=?;" % self.table,
  112. (version, pkgarch, checksum))
  113. row = data.fetchone()
  114. if row != None:
  115. val=row[0]
  116. else:
  117. #no value found, try to insert
  118. try:
  119. self._execute("INSERT INTO %s VALUES (?, ?, ?, ?);" % (self.table),
  120. (version, pkgarch, checksum, value))
  121. except sqlite3.IntegrityError as exc:
  122. logger.error(str(exc))
  123. self.dirty = True
  124. data = self._execute("SELECT value FROM %s WHERE version=? AND pkgarch=? AND checksum=?;" % self.table,
  125. (version, pkgarch, checksum))
  126. row = data.fetchone()
  127. if row != None:
  128. val = row[0]
  129. return val
  130. def _importNohist(self, version, pkgarch, checksum, value):
  131. try:
  132. #try to insert
  133. self._execute("INSERT INTO %s VALUES (?, ?, ?, ?);" % (self.table),
  134. (version, pkgarch, checksum,value))
  135. except sqlite3.IntegrityError as exc:
  136. #already have the record, try to update
  137. try:
  138. self._execute("UPDATE %s SET value=? WHERE version=? AND pkgarch=? AND checksum=? AND value<?"
  139. % (self.table),
  140. (value,version,pkgarch,checksum,value))
  141. except sqlite3.IntegrityError as exc:
  142. logger.error(str(exc))
  143. self.dirty = True
  144. data = self._execute("SELECT value FROM %s WHERE version=? AND pkgarch=? AND checksum=? AND value>=?;" % self.table,
  145. (version,pkgarch,checksum,value))
  146. row=data.fetchone()
  147. if row != None:
  148. return row[0]
  149. else:
  150. return None
  151. def importone(self, version, pkgarch, checksum, value):
  152. if self.nohist:
  153. return self._importNohist(version, pkgarch, checksum, value)
  154. else:
  155. return self._importHist(version, pkgarch, checksum, value)
  156. def export(self, version, pkgarch, checksum, colinfo):
  157. metainfo = {}
  158. #column info
  159. if colinfo:
  160. metainfo['tbl_name'] = self.table
  161. metainfo['core_ver'] = prserv.__version__
  162. metainfo['col_info'] = []
  163. data = self._execute("PRAGMA table_info(%s);" % self.table)
  164. for row in data:
  165. col = {}
  166. col['name'] = row['name']
  167. col['type'] = row['type']
  168. col['notnull'] = row['notnull']
  169. col['dflt_value'] = row['dflt_value']
  170. col['pk'] = row['pk']
  171. metainfo['col_info'].append(col)
  172. #data info
  173. datainfo = []
  174. if self.nohist:
  175. sqlstmt = "SELECT T1.version, T1.pkgarch, T1.checksum, T1.value FROM %s as T1, \
  176. (SELECT version,pkgarch,max(value) as maxvalue FROM %s GROUP BY version,pkgarch) as T2 \
  177. WHERE T1.version=T2.version AND T1.pkgarch=T2.pkgarch AND T1.value=T2.maxvalue " % (self.table, self.table)
  178. else:
  179. sqlstmt = "SELECT * FROM %s as T1 WHERE 1=1 " % self.table
  180. sqlarg = []
  181. where = ""
  182. if version:
  183. where += "AND T1.version=? "
  184. sqlarg.append(str(version))
  185. if pkgarch:
  186. where += "AND T1.pkgarch=? "
  187. sqlarg.append(str(pkgarch))
  188. if checksum:
  189. where += "AND T1.checksum=? "
  190. sqlarg.append(str(checksum))
  191. sqlstmt += where + ";"
  192. if len(sqlarg):
  193. data = self._execute(sqlstmt, tuple(sqlarg))
  194. else:
  195. data = self._execute(sqlstmt)
  196. for row in data:
  197. if row['version']:
  198. col = {}
  199. col['version'] = row['version']
  200. col['pkgarch'] = row['pkgarch']
  201. col['checksum'] = row['checksum']
  202. col['value'] = row['value']
  203. datainfo.append(col)
  204. return (metainfo, datainfo)
  205. def dump_db(self, fd):
  206. writeCount = 0
  207. for line in self.conn.iterdump():
  208. writeCount = writeCount + len(line) + 1
  209. fd.write(line)
  210. fd.write('\n')
  211. return writeCount
  212. class PRData(object):
  213. """Object representing the PR database"""
  214. def __init__(self, filename, nohist=True):
  215. self.filename=os.path.abspath(filename)
  216. self.nohist=nohist
  217. #build directory hierarchy
  218. try:
  219. os.makedirs(os.path.dirname(self.filename))
  220. except OSError as e:
  221. if e.errno != errno.EEXIST:
  222. raise e
  223. self.connection=sqlite3.connect(self.filename, isolation_level="EXCLUSIVE", check_same_thread = False)
  224. self.connection.row_factory=sqlite3.Row
  225. self.connection.execute("pragma synchronous = off;")
  226. self.connection.execute("PRAGMA journal_mode = WAL;")
  227. self._tables={}
  228. def disconnect(self):
  229. self.connection.close()
  230. def __getitem__(self,tblname):
  231. if not isinstance(tblname, str):
  232. raise TypeError("tblname argument must be a string, not '%s'" %
  233. type(tblname))
  234. if tblname in self._tables:
  235. return self._tables[tblname]
  236. else:
  237. tableobj = self._tables[tblname] = PRTable(self.connection, tblname, self.nohist)
  238. return tableobj
  239. def __delitem__(self, tblname):
  240. if tblname in self._tables:
  241. del self._tables[tblname]
  242. logger.info("drop table %s" % (tblname))
  243. self.connection.execute("DROP TABLE IF EXISTS %s;" % tblname)