(Python3) Creates an sqlite3 database from Xonotic's game server database.
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

main.py 6.0KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175
  1. import re, argparse
  2. import sqlite3 as sql
  3. import logging
  4. import logging.handlers
  5. from os import listdir, mkdir
  6. from os.path import isfile
  7. from urllib.parse import unquote
  8. from modules.readsvdb import readfile, cleanup
  9. from modules.log import filename
  10. def initl():
  11. fn = filename("_logs/dbimport-%s.log")
  12. fh = open(fn, mode='a', encoding='utf-8')
  13. logging.basicConfig(stream=fh, level=logging.DEBUG)
  14. #------------------------------------------------+
  15. # Functions: Clean up.
  16. #------------------------------------------------+
  17. # Unlike other rows,
  18. # the separator character, '/' is part of the value of the second column.
  19. # so an ordinary match for '/' or '\' can not be done like the other types of rows.
  20. # example from game server db:
  21. # \/uid2name/Mnumg2Yh/yxNFDTqGI+YyhlM7QDI0fpEmAaBJ8cI5dU=\Tuxxy
  22. # it should become:
  23. # ["uid2name", "Mnumg2Yh/yxNFDTqGI+YyhlM7QDI0fpEmAaBJ8cI5dU=", "Tuxxy"]
  24. def uid2namefix(row):
  25. # quick fix
  26. # replace first and last occurrence of backslash
  27. # this results in [,/uid2name/cryptoid_fp, name]
  28. e = re.sub(r'^([^\\]*)\\|\\(?=[^\\]*$)', ',', row)
  29. # replace first two occurence of forward slash
  30. # this results in [,,uid2name,cryptoid_fp, name]
  31. ee = e.replace('/', ',', 2)
  32. # split on comma
  33. # but start from index 2 because the first commas are left over
  34. # c is now a list of strings.
  35. # ["uid2name", <crypto_idfp value>, <player name value>]
  36. c = ee[2:].split(',')
  37. c[2] = unquote(c[2])
  38. c[2] = c[2].strip('\n')
  39. return c
  40. # O(n) and organize cts related data into list of rows.
  41. def filters(db):
  42. tt = []
  43. tr = []
  44. ti = []
  45. rank_index = 2
  46. for d in db:
  47. if d.find("uid2name") != -1:
  48. ti.append(uid2namefix(d))
  49. else:
  50. # regex:
  51. # find substrings that do not contain backslash, forwardslash, or newline.
  52. e = re.findall(r'[^\\/\n]+', d)
  53. if d.find("cts100record/time") != -1:
  54. e[rank_index] = int(e[rank_index].replace("time", ""))
  55. tt.append(e)
  56. if d.find("cts100record/crypto_idfp") != -1:
  57. e[3] = unquote(e[3])
  58. e[rank_index] = int(e[rank_index].replace("crypto_idfp", ""))
  59. tr.append(e)
  60. return tt, tr, ti
  61. #------------------------------------------------+
  62. # Functions: Database Creation
  63. #------------------------------------------------+
  64. def inserttodb(c, q, d):
  65. for x in d:
  66. # possible to do executemany
  67. # but want to be able to catch the problematic rows
  68. # as it is iterated through.
  69. # and proceed with adding OK rows.
  70. try:
  71. c.execute(q, x)
  72. except sql.ProgrammingError as e:
  73. print(e)
  74. print(x)
  75. #------------------------------------------------+
  76. # insert new data directly into new database file
  77. def i(d, s):
  78. con = sql.connect(d)
  79. with con:
  80. csr = con.cursor()
  81. try:
  82. times, ranks, ids = filters(cleanup(readfile(s)))
  83. if times:
  84. inserttodb(csr, "INSERT OR REPLACE INTO Cts_times VALUES(?, ?, ?, ?)", times)
  85. logging.info('\n'.join(y for y in [str(x) for x in times]))
  86. if ranks:
  87. inserttodb(csr, "INSERT OR REPLACE INTO Cts_ranks VALUES(?, ?, ?, ?)", ranks)
  88. logging.info('\n'.join(y for y in [str(x) for x in ranks]))
  89. if ids:
  90. inserttodb(csr, "INSERT OR REPLACE INTO Id2alias VALUES(?, ?, ?)", ids)
  91. logging.info('\n'.join(y for y in [str(x) for x in ids]))
  92. except sql.Error:
  93. logging.exception("sql error encountered in function 'i'")
  94. if con:
  95. con.rollback()
  96. # 'insert' new data into a file i.e sql query file
  97. def f(d, s):
  98. with open(d, 'w', encoding='utf-8') as h:
  99. times, ranks, ids = filters(cleanup(readfile(s)))
  100. for t in times:
  101. h.write("INSERT OR REPLACE INTO Cts_times VALUES(%s, %s, %s, %s)\n" % tuple(t))
  102. pass
  103. for r in ranks:
  104. h.write("INSERT OR REPLACE INTO Cts_ranks VALUES(%s, %s, %s, %s)\n" % tuple(r))
  105. pass
  106. for i in ids:
  107. h.write("INSERT OR REPLACE INTO Id2aslias VALUES(%s, %s, %s)\n" % tuple(i))
  108. pass
  109. pass
  110. pass
  111. # Test whether repeat rows are added.
  112. def duplicatestest(d, s):
  113. c = sql.connect(d)
  114. p = True
  115. with c:
  116. cs = c.cursor()
  117. try:
  118. logging.info("Inserting into database (1/2)")
  119. i(d, s)
  120. logging.info("Querying (1/2)")
  121. cs.execute("SELECT * FROM Cts_times")
  122. a = cs.fetchall()
  123. cs.execute("SELECT * FROM Cts_ranks")
  124. b = cs.fetchall()
  125. cs.execute("SELECT * FROM Id2alias")
  126. c = cs.fetchall()
  127. logging.info("Inserting into database (2/2)")
  128. i(d, s)
  129. logging.info("Querying (2/2)")
  130. cs.execute("SELECT * FROM Cts_times")
  131. x = cs.fetchall()
  132. cs.execute("SELECT * FROM Cts_ranks")
  133. y = cs.fetchall()
  134. cs.execute("SELECT * FROM Id2alias")
  135. z = cs.fetchall()
  136. if len(a) != len(x):
  137. logging.error("Issue with Cts_times")
  138. p = False
  139. if len(b) != len(y):
  140. logging.error("Issue with Cts_ranks")
  141. p = False
  142. if len(c) != len(z):
  143. logging.error("Issue with Id2alias")
  144. p = False
  145. if p:
  146. logging.info("Database ok - no repeat rows added.")
  147. except sql.Error:
  148. logging.exception("encountered sql error in function 'duplicate test'.")
  149. if __name__ == "__main__":
  150. ap = argparse.ArgumentParser()
  151. ap.add_argument('db')
  152. ap.add_argument('src')
  153. ap.add_argument('-t', '--test', action='store_true')
  154. ap.add_argument('-q', '--sql', action='store_true')
  155. args = ap.parse_args()
  156. initl()
  157. if args.test:
  158. duplicatestest(args.db, args.src)
  159. if args.sql:
  160. f(args.db, args.src)
  161. else:
  162. i(args.db, args.src)