PageRenderTime 54ms CodeModel.GetById 23ms RepoModel.GetById 0ms app.codeStats 0ms

/python/engine/PinYin/PYSQLiteDB.py

http://scim-python.googlecode.com/
Python | 543 lines | 515 code | 3 blank | 25 comment | 0 complexity | 4a373450ec2c75aed18ac5b82be67dc3 MD5 | raw file
  1. # -*- coding: utf-8 -*-
  2. # vim: set noet ts=4:
  3. #
  4. # scim-python
  5. #
  6. # Copyright (c) 2007-2008 Huang Peng <shawn.p.huang@gmail.com>
  7. #
  8. #
  9. # This library is free software; you can redistribute it and/or
  10. # modify it under the terms of the GNU Lesser General Public
  11. # License as published by the Free Software Foundation; either
  12. # version 2 of the License, or (at your option) any later version.
  13. #
  14. # This library is distributed in the hope that it will be useful,
  15. # but WITHOUT ANY WARRANTY; without even the implied warranty of
  16. # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  17. # GNU Lesser General Public License for more details.
  18. #
  19. # You should have received a copy of the GNU Lesser General Public
  20. # License along with this program; if not, write to the
  21. # Free Software Foundation, Inc., 59 Temple Place, Suite 330,
  22. # Boston, MA 02111-1307 USA
  23. #
  24. # $Id: $
  25. #
  26. import os
  27. import os.path as path
  28. import time
  29. import sys
  30. import sqlite3 as sqlite
  31. import re
  32. import uuid
  33. import traceback
  34. import PYUtil
  35. import PYDict
  36. import PYParser
  37. (YLEN, Y0, Y1, Y2, Y3, YX, S0, S1, S2, S3, PHRASE, FREQ, USER_FREQ) = range (0, 13)
  38. FLUSH_PERIOD = 60 * 5 # 5 minute
  39. class PYSQLiteDB:
  40. """phrase database that contains all phrases and phrases' pinyin"""
  41. def __init__ (self, name = "py.db", user_db = None, filename = None):
  42. # init last flush time
  43. self._last_flush_time = None
  44. if filename:
  45. self.db = sqlite.connect (filename)
  46. self.parser = PYParser.PinYinParser ()
  47. return
  48. name = os.path.join (os.path.dirname (__file__), name)
  49. # open system phrase database
  50. self.db = sqlite.connect (name)
  51. self.parser = PYParser.PinYinParser ()
  52. # self.db.execute ("PRAGMA locking_mode = EXCLUSIVE;")
  53. self.db.execute ("PRAGMA synchronous = NORMAL;")
  54. self.db.execute ("PRAGMA temp_store = MEMORY;")
  55. if user_db != None:
  56. home_path = os.getenv ("HOME")
  57. pinyin_path = path.join (home_path, ".scim", "scim-python", "pinyin")
  58. user_db = path.join (pinyin_path, user_db)
  59. if not path.isdir (pinyin_path):
  60. os.makedirs (pinyin_path)
  61. try:
  62. desc = get_database_desc (user_db)
  63. if desc == None or desc["id"] != "0.1":
  64. new_name = "%s.%d" %(user_db, os.getpid())
  65. print >> sys.stderr, "Can not support the user db. We will rename it to %s" % new_name
  66. os.rename (user_db, new_name)
  67. except:
  68. pass
  69. else:
  70. user_db = ":memory:"
  71. # open user phrase database
  72. try:
  73. self.db.execute ("ATTACH DATABASE \"%s\" AS user_db;" % user_db)
  74. except:
  75. print >> sys.stderr, "The user database was damaged. We will recreate it!"
  76. os.rename (user_db, "%s.%d" % (user_db, os.getpid ()))
  77. self.db.execute ("ATTACH DATABASE \"%s\" AS user_db;" % user_db)
  78. # try create all tables in user database
  79. self.create_tables ("user_db")
  80. self.create_indexes ("user_db")
  81. self.generate_userdb_desc ()
  82. self.select_cache = Cache ()
  83. def create_tables (self, database = "main"):
  84. """create all phrases tables that contain all phrases"""
  85. try:
  86. self.db.executescript ("PRAGMA default_cache_size = 5000;")
  87. self.flush (True)
  88. except:
  89. pass
  90. # create pinyin table
  91. sqlstring = "CREATE TABLE IF NOT EXISTS %s.py_pinyin (pinyin TEXT PREMARY KEY);" % database
  92. self.db.execute (sqlstring)
  93. # create pinyin table
  94. sqlstring = "CREATE TABLE IF NOT EXISTS %s.py_shengmu (shengmu TEXT PREMARY KEY);" % database
  95. self.db.execute (sqlstring)
  96. # create phrase table
  97. sqlstring = """CREATE TABLE IF NOT EXISTS %(database)s.py_phrase (
  98. ylen INTEGER,
  99. y0 INTEGER, y1 INTEGER, y2 INTEGER, y3 INTEGER, yx TEXT,
  100. s0 INTEGER, s1 INTEGER, s2 INTEGER, s3 INTEGER,
  101. phrase TEXT,
  102. freq INTEGER, user_freq INTEGER);"""
  103. self.db.executescript (sqlstring % { "database":database })
  104. self.flush (True)
  105. def generate_userdb_desc (self):
  106. try:
  107. sqlstring = "CREATE TABLE user_db.desc (name PRIMARY KEY, value);"
  108. self.db.executescript (sqlstring)
  109. sqlstring = "INSERT INTO user_db.desc VALUES (?, ?);"
  110. self.db.execute (sqlstring, ("version", "0.1"))
  111. self.db.execute (sqlstring, ("id", str (uuid.uuid4 ())))
  112. sqlstring = "INSERT INTO user_db.desc VALUES (?, DATETIME(\"now\", \"localtime\"));"
  113. self.db.execute (sqlstring, ("create-time", ))
  114. self.flush (True)
  115. except:
  116. print "desc table has been created."
  117. def create_indexes (self, database = "main"):
  118. # create indexes
  119. sqlstring = """
  120. CREATE INDEX IF NOT EXISTS %(database)s.py_phrase_index_1 ON
  121. py_phrase (y0, y1, y2, y3);
  122. CREATE INDEX IF NOT EXISTS %(database)s.py_phrase_index_2 ON
  123. py_phrase (ylen, y0, y1, y2, y3);
  124. CREATE INDEX IF NOT EXISTS %(database)s.py_phrase_index_3 ON
  125. py_phrase (ylen, s0, s1, s2, s3);
  126. CREATE INDEX IF NOT EXISTS %(database)s.py_phrase_index_4 ON
  127. py_phrase (s0, s1, s2, s2, s3);
  128. CREATE INDEX IF NOT EXISTS %(database)s.py_phrase_index_5 ON
  129. py_phrase (phrase);
  130. """
  131. self.db.executescript (sqlstring % { "database" : database })
  132. self.flush (True)
  133. def optimize_database (self):
  134. sqlstring = """
  135. CREATE TABLE tmp AS SELECT * FROM py_phrase;
  136. DELETE FROM py_phrase;
  137. INSERT INTO py_phrase SELECT * FROM tmp ORDER BY ylen, y0, y1, y2, y3, yx, phrase;
  138. DROP TABLE tmp;
  139. """
  140. self.db.executescript (sqlstring)
  141. self.db.executescript ("VACUUM;")
  142. def init_pinyin_table (self):
  143. """create table pinyin that contains all pinyin"""
  144. sqlstring = "INSERT INTO py_pinyin (pinyin) VALUES (?)"
  145. for py in PYDict.PINYIN_DICT.keys ():
  146. self.db.execute (sqlstring, (unicode (py),))
  147. self.flush (True)
  148. def init_shengmu_table (self):
  149. """create table shengmu that contains all shengmu of pinyin"""
  150. sqlstring = "INSERT INTO py_shengmu (shengmu) VALUES (?)"
  151. for shengmu in PYDict.SHENGMU_DICT.keys ():
  152. self.db.execute (sqlstring, (unicode (shengmu),))
  153. self.flush (True)
  154. def add_phrases (self, phrases, database = "main"):
  155. """ add phrases to database, phrases is a iterable object
  156. Like: [(phrase, pinyin, freq), (phrase, pinyin, freq), ...]
  157. """
  158. sqlstring = """INSERT INTO %s.py_phrase (ylen, y0, y1, y2, y3, yx, s0, s1, s2, s3, phrase, freq, user_freq)
  159. VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"""
  160. line = 1
  161. for phrase, pinyin, freq in phrases:
  162. try:
  163. py = self.parser.parse (pinyin)
  164. if len (py) != len (phrase):
  165. error_message = u"%s %s: Can not parse pinyin." % (phrase, pinyin)
  166. raise Exception (error_message.encode ("utf8"))
  167. record = [None, None, None, None, None, None, None, None, None, None, None, None, None]
  168. record [YLEN] = len (py)
  169. i = 0
  170. for p in py[:4]:
  171. record[Y0 + i] = p.get_pinyin_id ()
  172. record[S0 + i] = p.get_sheng_mu_id ()
  173. i += 1
  174. if len (py) > 4:
  175. record[YX] = "'".join (map (str, py[4:]))
  176. record[PHRASE] = phrase
  177. record[FREQ] = freq
  178. record[USER_FREQ] = None
  179. self.db.execute (sqlstring % database, record)
  180. except Exception, e:
  181. print line, ":", phrase.encode ("utf-8"), pinyin, freq
  182. import traceback
  183. traceback.print_exc ()
  184. # print e
  185. line += 1
  186. self.flush (True)
  187. def get_pinyin_table (self):
  188. """get pinyin table"""
  189. try:
  190. return self._pinyin_table
  191. except:
  192. pass
  193. sql = "SELECT phrase, y0, s0 FROM py_phrase WHERE ylen = 1"
  194. pinyin_table = {}
  195. result = self.db.execute (sql % i)
  196. for phrase, y0, s0 in result:
  197. if phrase not in pinyin_table:
  198. pinyin_table [phrase] = []
  199. pinyin_table [phrase].append ((y0, s0))
  200. self._pinyin_table = pinyin_table
  201. return pinyin_table
  202. def select_words_by_pinyin_list (self, pys, mohu = [False] * 8):
  203. """select words from database by list that contains PYUtil.PinYinWord objects"""
  204. pinyin_string = u"'".join (map (str, pys))
  205. result = self.select_cache [pinyin_string]
  206. if result != None:
  207. return result
  208. tables_union = """( SELECT * FROM main.py_phrase WHERE %(conditions)s UNION ALL
  209. SELECT * FROM user_db.py_phrase WHERE %(conditions)s )"""
  210. if mohu[0]:
  211. sql_conditions = ["+ylen = %d" % len (pys) ]
  212. else:
  213. sql_conditions = ["ylen = %d" % len (pys) ]
  214. i = 0
  215. if mohu[0] == False:
  216. for py in pys[:4]:
  217. if py.is_valid_pinyin ():
  218. sql_conditions.append ("y%d = %d" % (i, py.get_pinyin_id ()))
  219. else:
  220. sql_conditions.append ("s%d = %d" % (i, py.get_sheng_mu_id ()))
  221. i += 1
  222. elif mohu[0] == True:
  223. for py in pys[:4]:
  224. if py.is_valid_pinyin ():
  225. shengmu = py.get_shengmu ()
  226. yunmu = py.get_pinyin ()[len (shengmu):]
  227. shengmu_list = [shengmu]
  228. yunmu_list = [yunmu]
  229. if mohu[1] and (shengmu == "s" or shengmu == "sh"):
  230. shengmu_list = PYDict.MOHU_SHENGMU["s"]
  231. if mohu[2] and (shengmu == "c" or shengmu == "ch"):
  232. shengmu_list = PYDict.MOHU_SHENGMU["c"]
  233. if mohu[3] and (shengmu == "z" or shengmu == "zh"):
  234. shengmu_list = PYDict.MOHU_SHENGMU["z"]
  235. if mohu[4] and (shengmu == "l" or shengmu == "n"):
  236. shengmu_list = PYDict.MOHU_SHENGMU["l"]
  237. if mohu[5] and (yunmu == "in" or yunmu == "ing"):
  238. yunmu_list = PYDict.MOHU_YUNMU["in"]
  239. if mohu[6] and (yunmu == "en" or yunmu == "eng"):
  240. yunmu_list = PYDict.MOHU_YUNMU["en"]
  241. if mohu[7] and (yunmu == "an" or yunmu == "ang"):
  242. yunmu_list = PYDict.MOHU_YUNMU["an"]
  243. pinyin_ids = []
  244. for s in shengmu_list:
  245. for y in yunmu_list:
  246. pinyin = s + y
  247. if pinyin in PYDict.PINYIN_DICT:
  248. pinyin_ids.append (str (PYDict.PINYIN_DICT[pinyin]))
  249. if len (pinyin_ids) > 1:
  250. sql_conditions.append ("y%d in (%s)" % (i, ",".join (pinyin_ids)))
  251. else:
  252. sql_conditions.append ("y%d == %s" % (i, pinyin_ids[0]))
  253. else:
  254. shengmu = py.get_shengmu ()
  255. if shengmu in PYDict.MOHU_SHENGMU:
  256. shengmu_ids = []
  257. for s in PYDict.MOHU_SHENGMU[shengmu]:
  258. shengmu_ids.append (str (PYDict.SHENGMU_DICT[s]))
  259. sql_conditions.append ("s%d in (%s)" % (i, ",".join (shengmu_ids)))
  260. else:
  261. sql_conditions.append ("s%d = %d" % (i, py.get_sheng_mu_id ()))
  262. i += 1
  263. if pys[4:]:
  264. pp = lambda (x): x.get_pattern (mohu[0])
  265. pattern = "'".join (map (pp, pys[4:]))
  266. sql_conditions.append ("yx LIKE \"" + pattern + "\"")
  267. tables_union = tables_union % { "conditions" : " AND ".join (sql_conditions) }
  268. sql_prefix = "SELECT * FROM " + tables_union
  269. sql_string = sql_prefix + " GROUP BY phrase ORDER BY user_freq DESC, freq DESC;"
  270. result = list (self.db.execute (sql_string).fetchall ())
  271. self.select_cache [pinyin_string] = result
  272. return result
  273. def select_words_by_pinyin_string (self, string, mohu = [False] * 8):
  274. """select words from the database by pinyin string"""
  275. pys = self.parser.parse (string)
  276. result = self.select_words_by_pinyin_list (pys, mohu)
  277. return result
  278. def commit_phrases (self, records):
  279. """this function adjusts frequence of phrase in user database."""
  280. for record in records:
  281. if record [USER_FREQ] != None:
  282. sql = "UPDATE user_db.py_phrase SET user_freq = user_freq + 1 WHERE ylen = ? AND y0 = ? AND phrase = ?;"
  283. self.db.execute (sql, (record[YLEN], record[Y0], record[PHRASE]))
  284. else:
  285. sql = """INSERT INTO user_db.py_phrase (ylen, y0, y1, y2, y3, yx, s0, s1, s2, s3, phrase, freq, user_freq)
  286. VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1);"""
  287. self.db.execute (sql, record[:12])
  288. self.flush ()
  289. self.select_cache.clear ()
  290. def commit_phrase (self, record):
  291. self.commit_phrases ([record])
  292. def commit_char (self, char, pinyin_id, shengmu_id):
  293. sql = "SELECT * FROM main.py_phrase WHERE ylen = 1 AND y0 = ? AND phrase = ?"
  294. records = self.db.execute (sql, (pinyin_id, char)).fetchall ()
  295. self.commit_phrases (records)
  296. def new_phrase (self, phrases, freq = None, user_freq = 1):
  297. """this function create a new phrase from a phrase list and put it into user database."""
  298. pinyin_ids = []
  299. shengmu_ids = []
  300. phrase = u""
  301. phrase_length = 0
  302. for p in phrases:
  303. if phrase_length + p[YLEN] > 8:
  304. break
  305. phrase += p[PHRASE]
  306. phrase_length += p[YLEN]
  307. if p[YLEN] > 4:
  308. ys = p[YX].split ("'")
  309. for i in range (0, p[YLEN]):
  310. if i < 4:
  311. pinyin_ids.append (p[Y0 + i])
  312. shengmu_ids.append (p[S0 + i])
  313. else:
  314. w = PYUtil.PinYinWord (ys[i - 4])
  315. pinyin_ids.append (w.get_pinyin_id ())
  316. shengmu_ids.append (w.get_sheng_mu_id ())
  317. sql = """INSERT INTO user_db.py_phrase
  318. (ylen, y0, y1, y2, y3, yx, s0, s1, s2, s3, phrase, freq, user_freq)
  319. VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"""
  320. values = [phrase_length, None, None, None, None, None, None, None, None, None, phrase, freq, user_freq]
  321. if phrase_length <=4:
  322. values[1: 1 + phrase_length] = pinyin_ids [:phrase_length]
  323. values[6: 6 + phrase_length] = shengmu_ids [:phrase_length]
  324. else:
  325. values[1: 5] = pinyin_ids [:4]
  326. values[6: 10] = shengmu_ids [:4]
  327. get_pinyin = lambda id: PYDict.ID_PINYIN_DICT[id]
  328. values[5] = "'".join (map (get_pinyin, pinyin_ids[4:]))
  329. self.db.execute (sql, values)
  330. self.flush ()
  331. self.select_cache.clear ()
  332. def remove_phrase (self, record):
  333. "Remove phrase from user database"
  334. sql = "DELETE FROM user_db.py_phrase WHERE ylen = ? AND y0 = ? AND phrase = ?"
  335. self.db.execute (sql, (record[YLEN], record[Y0], record[PHRASE]))
  336. self.flush ()
  337. self.select_cache.clear ()
  338. def flush (self, force = False):
  339. if self._last_flush_time == None:
  340. self._last_flush_time = time.time ()
  341. if force or time.time() - self._last_flush_time >= FLUSH_PERIOD:
  342. self.db.commit ()
  343. self._last_flush_time = time.time ()
  344. class Cache:
  345. """cache object for cache history queries"""
  346. class Slot:
  347. """Slot item of cache object"""
  348. def __init__ (self):
  349. self.time = -1
  350. self.value = None
  351. self.index = None
  352. def __init__ (self, max_slot = 1024):
  353. self._max_slot = max_slot
  354. self.clear ()
  355. def clear (self):
  356. self._slots = []
  357. self._dict = {}
  358. self._time = 0
  359. def __delitem__ (self, index):
  360. if not self._dict.has_key (index):
  361. return None
  362. del self._dict [index]
  363. def __getitem__ (self, index):
  364. """return a vale associate with the giving index. It cache does not has this index,
  365. it will retrun None."""
  366. if not self._dict.has_key (index):
  367. return None
  368. slot = self._dict [index]
  369. self._time += 1
  370. slot.time = self._time
  371. return slot.value
  372. def __setitem__ (self, index, value):
  373. if self._dict.has_key (index):
  374. slot = self._dict[index]
  375. else:
  376. slot = self.get_slot ()
  377. self._time += 1
  378. slot.value = value
  379. slot.time = self._time
  380. slot.index = index
  381. self._dict[index] = slot
  382. def get_slot (self):
  383. """get_slot will return a empty slot. If there is not any empty slot,
  384. it will find the oldest slot and return it."""
  385. if len (self._slots) < self._max_slot:
  386. slot = Cache.Slot ()
  387. self._slots.append (slot)
  388. else:
  389. self._slots.sort (lambda x,y : x.time - y.time)
  390. slot = self._slots[0]
  391. del self._dict[slot.index]
  392. return slot
  393. def get_database_desc (db_file):
  394. if not path.exists (db_file):
  395. raise Exception ("%s does not exist!" % dbname)
  396. try:
  397. desc = {}
  398. db = sqlite.connect (db_file)
  399. for row in db.executescript ("SELECT * FROM desc;"):
  400. desc [row[0]] = row[1]
  401. return desc
  402. except:
  403. return None
  404. def test_select_words ():
  405. import time
  406. db = PYSQLiteDB ()
  407. while True:
  408. l = raw_input ().strip ()
  409. if not l:
  410. break
  411. t = time.time ()
  412. res = db.select_words_by_pinyin_string (l)
  413. t = time.time () - t
  414. i = 0
  415. for p in res:
  416. print "%s = %s %s " % (i, str (p), p[PHRASE].encode ("utf8"))
  417. i += 1
  418. print "OK t =", t, " count =", len (res)
  419. while True:
  420. try:
  421. commit = int (raw_input ("commit = ").strip ())
  422. db.commit_phrase (res[commit])
  423. except KeyboardInterrupt, e:
  424. print "Exit"
  425. sys.exit (0)
  426. except:
  427. print "Input is invalidate"
  428. continue
  429. break
  430. def test_case (string):
  431. db = PYSQLiteDB ()
  432. parser = PYParser.PinYinParser ()
  433. pys = parser.parse (string)
  434. result = u""
  435. while len (result) != len (pys):
  436. pps = pys[len (result):]
  437. for x in range (len (pps), 0, -1):
  438. candidates = db.select_words_by_pinyin_list (pps[:x])
  439. if candidates:
  440. result += candidates[0][PHRASE]
  441. break
  442. print "'".join (map (str, pys))
  443. print result
  444. def test ():
  445. test_case ("gaodangfangdichankaifashangdedongtianjiuyaolaile")
  446. test_case ("huanyingshiyongwokaifadezhinengpinyinshurufa")
  447. test_case ("beijingshirenminzhengfujuedingzaitongzhouqujianlizhengfuxingzhengjidi")
  448. test_case ("woguojuminshoumingqiwangtigaodaoqishisansui")
  449. test_case ("wgjmshmqwtgdqshss")
  450. test_case ("xjinyhuiyouyongme")
  451. if __name__ == "__main__":
  452. import timeit
  453. t = timeit.Timer ("PYSQLiteDB.test ()","import PYSQLiteDB")
  454. print t.repeat (3,1)