<commit_before># -*- coding: utf-8 -*-
# vim:et sts=4 sw=4
#
# ibus-table - The Tables engine for IBus
#
# Copyright (c) 2008-2009 <NAME> <<EMAIL>>
#
# This library is free software; you can redistribute it and/or
# modify it under the terms of the GNU Lesser General Public
# License as published by the Free Software Foundation; either
# version 2.1 of the License, or (at your option) any later version.
#
# This library is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# Lesser General Public License for more details.
#
# You should have received a copy of the GNU Lesser General Public
# License along with this library; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
#
# $Id: $
#

import os
import os.path as path
from sys import stderr
import sqlite3
import tabdict
import uuid
import time
import re

patt_r = re.compile(r'c([ea])(\d):(.*)')
patt_p = re.compile(r'p(-{0,1}\d)(-{0,1}\d)')

# first make some number index we will used :)
#(MLEN, CLEN, M0, M1, M2, M3, M4, PHRASE, FREQ, USER_FREQ) = range (0,10)


class tabsqlitedb:
    '''Phrase database for tables'''
    def __init__(self, name = 'table.db', user_db = None, filename = None ):
        # use filename when you are creating db from source
        # use name when you are using db
        # first we use the Parse in tabdict, which transform the char(a,b,c,...) to int(1,2,3,...) to fasten the sql enquiry
        self.parse = tabdict.parse
        self.deparse = tabdict.deparse
        self._add_phrase_sqlstr = ''
        self.old_phrases=[]
        self.ime_property_cache = {}
        
        if filename:
            # now we are creating db
            self.db = sqlite3.connect( filename )
        else:
            try:
                os.system('cat %s > /dev/null' % name)
            except:
                pass
            # open system phrase db
            self.db = sqlite3.connect(  name )
        try:
            self.db.execute( 'PRAGMA page_size = 8192; ' )
            self.db.execute( 'PRAGMA cache_size = 20000; ' )
            # increase the cache size to speedup sqlite enquiry
            self.db.execute( 'PRAGMA temp_store = MEMORY; ' )
            self.db.execute( 'PRAGMA synchronous = OFF; ' )
        except:
            print 'encountering error when init db'
            pass
        # create IME property table
        self.db.executescript('CREATE TABLE IF NOT EXISTS main.ime (attr TEXT, val TEXT);')
        # make sure we have values in ime table.
        if not self.db.execute('SELECT val FROM main.ime \
            WHERE attr="name";').fetchall():
            ime_keys={'name':'',
                      'name.zh_cn':'',
                      'name.zh_hk':'',
                      'name.zh_tw':'',
                      'author':'somebody', 
                      'uuid':'%s' % uuid.uuid4(),
                      'serial_number':'%s' % time.strftime('%Y%m%d'),
                      'icon':'ibus-table.svg',
                      'license':'LGPL',
                      'languages':'',
                      'language_filter':'',
                      'valid_input_chars':'abcdefghijklmnopqrstuvwxyz',
                      'max_key_length':'4',
            #          'commit_keys':'space',
            #          'forward_keys':'Return',
                      'select_keys':'1,2,3,4,5,6,7,8,9,0',
            #          'page_up_keys':'Page_Up,minus',
            #          'page_down_keys':'Page_Down,equal',
                      'status_prompt':'',
                      'def_full_width_punct':'TRUE',
                      'def_full_width_letter':'FALSE',
                      'user_can_define_phrase':'FALSE',
                      'pinyin_mode':'FALSE',
                      'dynamic_adjust':'FALSE',
                      'auto_commit':'false',
                      #'no_check_chars':u'',
                      'description':'A IME under IBus Table',
                      'layout':'us',
                      'rules':'',
                      #'rules':'ce2:p11+p12+p21+p22;ce3:p11+p21+p22+p31;ca4:p11+p21+p31+p41'}
                      'least_commit_length':'0',
                      'start_chars':''
                      # we use this entry for those IME, which don't
                      # have rules to build up phrase, but still need
                      # auto commit to preedit
                      }
            # inital the attribute in ime table, which should be updated from mabiao
            for _name in ime_keys:
                sqlstr = 'INSERT INTO main.ime (attr,val) VALUES (?,?);'
                self.db.execute( sqlstr, (_name,ime_keys[_name]) )
        # share variables in this class:
        self._mlen = int ( self.get_ime_property ("max_key_length") )
        # for chinese
        self._is_chinese = self.is_chinese()
        # for fast add word
        self._set_add_phrase_sqlstr()
        #(ID, MLEN, CLEN, M0, M1, M2, M3, M4, CATEGORY, PHRASE, FREQ, USER_FREQ) = range (0,12)
        self._pt_index = ['id', 'mlen', 'clen']
        for i in range(self._mlen):
            self._pt_index.append ('m%d' %i)
        if self._is_chinese:
            self._pt_index += ['category']
        self._pt_index += ['phrase','freq','user_freq']
        self.user_can_define_phrase = self.get_ime_property('user_can_define_phrase')
        if self.user_can_define_phrase:
            if self.user_can_define_phrase.lower() == u'true' :
                self.user_can_define_phrase = True
            else:
                self.user_can_define_phrase = False
        else:
            print 'Could not find "user_can_define_phrase" entry from database, is it a outdated database?'
            self.user_can_define_phrase = False
        
        self.dynamic_adjust = self.get_ime_property('dynamic_adjust')
        if self.dynamic_adjust:
            if self.dynamic_adjust.lower() == u'true' :
                self.dynamic_adjust = True
            else:
                self.dynamic_adjust = False
        else:
            print 'Could not find "dynamic_adjust" entry from database, is it a outdated database?'
            self.dynamic_adjust = False
        
        self.rules = self.get_rules ()
        self.pkeylens = []
        self.pkeylens = self.phrase_keys_len ()
        self.startchars = self.get_start_chars ()
        
        #self._no_check_chars = self.get_no_check_chars()
        # for fast gouci
        self._goucima={}
        if filename:
            # since we just creating db, we do not need userdb and mudb
            return
        
        # user database:
        if user_db != None:
            home_path = os.getenv ("HOME")
            tables_path = path.join (home_path, ".ibus",  "tables")
            user_db = path.join (tables_path, user_db)
            if not path.isdir (tables_path):
                os.makedirs (tables_path)
            try:
                desc = self.get_database_desc (user_db)
                if desc == None :
                    self.init_user_db (user_db)
                elif desc["version"] != "0.5":
                    new_name = "%s.%d" %(user_db, os.getpid())
                    print >> stderr, "Can not support the user db. We will rename it to %s" % new_name
                    self.old_phrases = self.extra_user_phrases( user_db )
                    os.rename (user_db, new_name)
                    self.init_user_db (user_db)
                elif self.get_table_phrase_len(user_db) != len(self._pt_index):
                    print >> stderr, "user db format outdated."
                    # store old user phrases
                    self.old_phrases = self.extra_user_phrases( user_db )
                    new_name = "%s.%d" %(user_db, os.getpid())
                    os.rename (user_db, new_name)
                    self.init_user_db (user_db)
            except:
                import traceback
                traceback.print_exc()
        else:
            user_db = ":memory:"
        
        # open user phrase database
        try:
            self.db.execute ('ATTACH DATABASE "%s" AS user_db;' % user_db)
        except:
            print >> stderr, "The user database was damaged. We will recreate it!"
            os.rename (user_db, "%s.%d" % (user_db, os.getpid ()))
            self.init_user_db (user_db)
            self.db.execute ('ATTACH DATABASE "%s" AS user_db;' % user_db)
        self.create_tables ("user_db")
        if self.old_phrases:
            # (mlen, phrase, freq, user_freq)
            # the phrases will be deparse again, and then be added     
            # the characters will be discard :(
            #chars = filter (lambda x: x[0] == 1, self.old_phrases)
            # print chars
            phrases = filter (lambda x: x[0] > 1, self.old_phrases)
            phrases = map(lambda x: [self.parse_phrase_to_tabkeys(x[1])]\
                    + list(x[1:]) , phrases)
 
            map (self.u_add_phrase,phrases)
            self.db.commit ()


        # try create all tables in user database
        self.create_indexes ("user_db",commit=False)
        self.generate_userdb_desc ()
        
        # attach mudb for working process
        mudb = ":memory:"  
        self.db.execute ('ATTACH DATABASE "%s" AS mudb;' % mudb )
        self.create_tables ("mudb")
    
    def update_phrase (self, entry, database='user_db'):
        '''update phrase freqs'''
        #print entry
        _con = [ entry[-1] ] + list(entry[1:3+entry[1]]) + [entry[-3]]
        #print _con
        _condition = u''.join( map(lambda x: 'AND m%d = ? ' % x, range(entry[1]) )    )
        #print _condition
        sqlstr = 'UPDATE %s.phrases SET user_freq = ? WHERE mlen = ? AND clen = ? %s AND phrase = ?;' % (database, _condition)
        #print sqlstr
        self.db.execute ( sqlstr , _con )
        # because we may update different db, we'd better commit every time.
        self.db.commit()

    def sync_usrdb (self):
        # we need to update the user_db
        #print 'sync userdb'
        mudata = self.db.execute ('SELECT * FROM mudb.phrases;').fetchall()
        #print mudata
        data_u = filter ( lambda x: x[-2] in [1,-3], mudata)
        data_a = filter ( lambda x: x[-2]==2, mudata)
        data_n = filter ( lambda x: x[-2]==-2, mudata)
        #print data_a
        data_a = map (lambda x: (u''.join ( map(self.deparse, x[3:3+x[1]])),x[-3],0,x[-1] ), data_a)
        data_n = map (lambda x: (u''.join ( map(self.deparse, x[3:3+x[1]])),x[-3],-1,x[-1] ), data_n)
        #print data_u
        map (self.update_phrase, data_u)
        #print self.db.execute('select * from user_db.phrases;').fetchall()
        map (self.u_add_phrase,data_a)
        map (self.u_add_phrase,data_n)
        self.db.commit ()
    
    def is_chinese (self):
        __lang = self.get_ime_property ('languages')
        if __lang:
            __langs = __lang.split(',')
            for _l in __langs:
                if _l.lower().find('zh') != -1:
                    return True
        return False

    def get_chinese_mode (self):
        try:
            __dict = {'cm0':0,'cm1':1,'cm2':2,'cm3':3,'cm4':4}
            __filt = self.get_ime_property ('language_filter')
            return __dict[__filt]
        except:
            return -1

    def get_page_size (self):
        try:
            __select_keys = self.get_ime_property ('select_keys')
            return len (__select_keys.split (','))
        except:
            return 6

    def create_tables (self, database):
        '''Create tables that contain all phrase'''

        try:
            self.db.execute( 'PRAGMA cache_size = 20000; ' )
            # increase the cache size to speedup sqlite enquiry
        except:
            pass
        if database == 'main':
            # create  ikeys table
            sqlstr = 'CREATE TABLE IF NOT EXISTS %s.ikeys (ikey TEXT PRIMARY KEY, id INTEGER);' % database
            self.db.execute ( sqlstr )
        
            # create goucima table, this table is used in construct new phrases
            sqlstr = 'CREATE TABLE IF NOT EXISTS %s.goucima (zi TEXT PRIMARY KEY' % database
            for i in range(self._mlen):
                sqlstr += ', g%d INTEGER' % i 
            #sqlstr += ''.join(map (lambda x: ', g%d INTEGER' % x, range(self._mlen)) )
            sqlstr += ');'
            self.db.execute ( sqlstr )

            # create pinyin table, this table is used in search single character for user handly
            sqlstr = 'CREATE TABLE IF NOT EXISTS %s.pinyin ( plen INTEGER, ' % database
            #for i in range(6):
            #    sqlstr += 'p%d INTEGER, ' % i 
            sqlstr += ''.join( map (lambda x: 'p%d INTEGER, ' % x, range(7) ) )
            sqlstr += 'zi TEXT, freq INTEGER);'
            self.db.execute ( sqlstr )

        # create phrase table (mabiao)
        sqlstr = 'CREATE TABLE IF NOT EXISTS %s.phrases (id INTEGER PRIMARY KEY AUTOINCREMENT,\
                mlen INTEGER, clen INTEGER, ' % database
        #for i in range(self._mlen):
        #    sqlstr += 'm%d INTEGER, ' % i 
        sqlstr += ''.join ( map (lambda x: 'm%d INTEGER, ' % x, range(self._mlen)) )
        if self._is_chinese:
            sqlstr += 'category INTEGER, '
        sqlstr += 'phrase TEXT, freq INTEGER, user_freq INTEGER);'
        self.db.execute ( sqlstr )
        self.db.commit()
    
    def update_ime (self, attrs):
        '''Update attributes in ime table, attrs is a iterable object
        Like [(attr,val), (attr,val), ...]
        '''
        sqlstr = 'UPDATE main.ime SET val = ? WHERE attr = ?;' 
        for attr,val in attrs:
            _sqlstr = 'SELECT * from main.ime WHERE attr = ?' 
            res = self.db.execute( _sqlstr, (attr,) ).fetchall()
            if res:
                self.db.execute(sqlstr,(val,attr))
            else:
                #print '"',attr,'"'," didn't in ime property now!"
                pass
        # then flush previous cache
        self.ime_property_cache = {}
        # we need to update some self variables now.
        self._mlen = int (self.get_ime_property ('max_key_length' ))
        self._is_chinese = self.is_chinese()
        self._set_add_phrase_sqlstr()
        self._pt_index = ['id', 'mlen', 'clen']
        for i in range(self._mlen):
            self._pt_index.append ('m%d' %i)
        if self._is_chinese:
            self._pt_index += ['category']
        self._pt_index += ['phrase','freq','user_freq']
        self.user_can_define_phrase = self.get_ime_property('user_can_define_phrase')
        if self.user_can_define_phrase:
            if self.user_can_define_phrase.lower() == u'true' :
                self.user_can_define_phrase = True
            else:
                self.user_can_define_phrase = False
        else:
            print 'Could not find "user_can_define_phrase" entry from database, is it a outdated database?'
            self.user_can_define_phrase = False
        self.rules = self.get_rules ()

        self.db.commit()

    def get_rules (self):
        '''Get phrase construct rules'''
        rules={}
        if self.user_can_define_phrase:
            try:
                _rules = self.get_ime_property ('rules')
                if _rules:
                    _rules = _rules.strip().split(';')
                for rule in _rules:
                    res = patt_r.match (rule)
                    if res:
                        cms = []
                        if res.group(1) == 'a':
                            rules['above'] = int(res.group(2))
                        _cms = res.group(3).split('+')
                        if len(_cms) > self._mlen:
                            print 'rule: "%s" over max key length' % rule
                            break
                        for _cm in _cms:
                            cm_res = patt_p.match(_cm)
                            cms.append(( int(cm_res.group(1)),int(cm_res.group(2)) ))
                        rules[int(res.group(2))]=cms
                    else:
                        print 'not a legal rule: "%s"'  % rule 
            except Exception:
                import traceback
                traceback.print_exc ()
            return rules
        else:
            return ""

    def phrase_keys_len (self):
        '''Return the phrase possible key length'''
        if self.rules:
            max_len = self.rules["above"]
            try:
                return map ( lambda x: len(self.rules[x]), range(2, max_len+1) )[:]
            except:
                return []
        else:
            # we try to findout whether we have least commit len
            # in ime table
            try:
                least_commit_len = int(self.get_ime_property('least_commit_length'))
            except:
                least_commit_len = 0
            if least_commit_len > 0:
                return range (least_commit_len, self._mlen + 1)
            else:
                return []
    
    def get_start_chars (self):
        '''return possible start chars of IME'''
        try:
            return self.get_ime_property('start_chars')
        except:
            return ''

    def get_no_check_chars (self):
        '''Get the characters which engine should not change freq'''
        _chars= self.get_ime_property('no_check_chars')
        try:
            _chars = _chars.decode('utf-8')
        except:
            pass
        return _chars

    def add_phrases (self, phrases, database = 'main'):
        '''Add phrases to database, phrases is a iterable object
        Like: [(tabkeys, phrase, freq ,user_freq), (tabkeys, phrase, freq, user_freq), ...]
        '''
        map (self.add_phrase, phrases, [database]*len(phrases),[False]*len(phrases) )
        self.db.commit()
    
    def add_new_phrases (self, nphrases, database='main'):
        '''Add new phrases into db, new phrases is a object
        of [(phrase,freq), (phrase,freq),...]'''
        n_phrases=[]
        for _ph, _freq in nphrases:
            try:
                _tabkey = self.parse_phrase_to_tabkeys(_ph)
                if not self.check_phrase_internal (_ph, _tabkey, database):
                    # we don't have this phrase
                    n_phrases.append ( (_tabkey, _ph, _freq, 0) )
            except:
                print '\"%s\" would not been added' % _ph
        if n_phrases:
            self.add_phrases ( n_phrases, database )
    
    def u_add_phrase (self,nphrase):
        '''Add a phrase to userdb'''
        self.add_phrase (nphrase,database='user_db',commit=False)

    def _set_add_phrase_sqlstr(self):
        '''Create the sqlstr for add phrase according to self._mlen.'''
        sqlstr = 'INSERT INTO %s.phrases ( mlen, clen, '
        sql_suffix = 'VALUES ( ?, ?, '
        mmlen = range(self._mlen)
        sqlstr += ''.join ( map(lambda x: 'm%d, ' %x , mmlen) )
        sql_suffix += ''.join ( map (lambda x: '?, ' , mmlen) )
        if self._is_chinese:
            sqlstr += 'category, '
            sql_suffix += '?, '
        sqlstr += 'phrase, freq, user_freq) '
        sql_suffix += '?, ?, ? );'
        sqlstr += sql_suffix
        self._add_phrase_sqlstr = sqlstr

    def add_phrase (self, aphrase, database = 'main',commit=True):
        '''Add phrase to database, phrase is a object of
        (tabkeys, phrase, freq ,user_freq)
        '''
        sqlstr = self._add_phrase_sqlstr
        try:
            tabkeys,phrase,freq,user_freq = aphrase
        except:
            tabkeys,phrase,freq = aphrase
            user_freq = 0
        # now we will set the category bits if this is chinese
        if self._is_chinese:
            # this is the bitmask we will use,
            # from low to high, 1st bit is simplify Chinese,
            # 2nd bit is traditional Chinese,
            # 3rd bit means out of gbk
            category = 0
            # make sure that we got a unicode string
            if type(phrase) != type(u''):
                phrase = phrase.decode('utf8')
            tmp_phrase = ''.join(re.findall(u'['
                                            + u'\u4E00-\u9FCB'
                                            + u'\u3400-\u4DB5'
                                            + u'\uF900-\uFaFF'
                                            + u'\U00020000-\U0002A6D6'
                                            + u'\U0002A700-\U0002B734'
                                            + u'\U0002B740-\U0002B81D'
                                            + u'\U0002F800-\U0002FA1D'
                                            + u']+',
                                            phrase))
            # first whether in gb2312
            try:
                tmp_phrase.encode('gb2312')
                category |= 1
            except:
                if '〇'.decode('utf8') in tmp_phrase:
                    # we add '〇' into SC as well
                    category |= 1
            # second check big5-hkscs
            try:
                tmp_phrase.encode('big5hkscs')
                category |= 1 << 1
            except:
                # then check whether in gbk,
                if category & 1:
                    # already know in SC
                    pass
                else:
                    # need to check
                    try:
                        tmp_phrase.encode('gbk')
                        category |= 1
                    except:
                        # not in gbk
                        pass
            # then set for 3rd bit, if not in SC and TC
            if not ( category & (1 | 1 << 1) ):
                category |= (1 << 2)
        try:
            tbks = self.parse(tabkeys)
            if len(tbks) != len(tabkeys):
                print 'In %s %s: we parse tabkeys fail' % (phrase, tabkeys )
                return
            record = [None] * (5 + self._mlen)
            record [0] = len (tabkeys)
            record [1] = len (phrase)
            record [2: 2+len(tabkeys)] = map (lambda x: tbks[x].get_key_id(), range(0,len(tabkeys)))
            if self._is_chinese:
                record +=[None]
                record[-4] = category
            record[-3:] = phrase, freq, user_freq
            self.db.execute (sqlstr % database, record)
            if commit:
                self.db.commit()    
        except Exception:
            import traceback
            traceback.print_exc()
    
    def add_goucima (self, gcms):
        '''Add goucima into database, gcms is iterable object
        Like gcms = [(zi,goucima),(zi,goucima), ...]
        '''
        count = 1
        for zi,gcm in gcms:
            _con = ''
            _val = ''
            _len = min ( len(gcm),self._mlen)
            for i in range( _len ):
                _con += ', g%d' % i
                _val += ', ?' 
            sqlstr = '''INSERT INTO main.goucima ( zi %s )
            VALUES ( ? %s );''' % (_con, _val)
            try:
                gc = self.parse(gcm)
                if len(gc) != len(gcm):
                    error_m = u'%s %s: Can not parse goucima' % (zi, gcm )
                    raise Exception ( error_m.encode ('utf8') )
                record = [zi]
                for i in range(_len):
                    record.append( gc[i].get_key_id())
                self.db.execute (sqlstr , record)
            
            except Exception:
                import traceback
                traceback.print_exc()
            count += 1
        self.db.commit()
    
    def add_pinyin (self, pinyins, database = 'main'):
        '''Add pinyin to database, pinyins is a iterable object
        Like: [(zi,pinyin, freq), (zi, pinyin, freq), ...]
        '''
        sqlstr = 'INSERT INTO %s.pinyin ( plen, '
        sql_suffix = 'VALUES ( ?, '
        for i in range(7):
            sqlstr += 'p%d, ' % i
            sql_suffix += '?, '
        sqlstr += 'zi, freq ) '
        sql_suffix += '?, ? );'
        sqlstr += sql_suffix
        
        count = 1
        for pinyin,zi,freq in pinyins:
            try:
                pinyin_n = pinyin.replace('1','!').replace('2','@').replace('3','#').replace('4','$').replace('5','%')
                py = self.parse(pinyin_n)
                if len(py) != len(pinyin_n):
                    error_m = u'%s %s: Can not parse pinyin' % (zi, pinyin )
                    raise Exception ( error_m.encode ('utf8') )
                record = [None]*10
                record [0] = len (pinyin_n)
                for i in range(0,len(pinyin_n)):
                    record [ 1+i ] = py[i].get_key_id()
                record [-2] = zi
                record [-1] = freq
                self.db.execute (sqlstr % database, record)
            except Exception:
                print count, ': ', zi.encode('utf8'), ' ', pinyin
                import traceback
                traceback.print_exc()
            count += 1

        self.db.commit()    
    
    def optimize_database (self, database='main'):
        sqlstr = '''
            CREATE TABLE tmp AS SELECT * FROM %(database)s.phrases;
            DELETE FROM %(database)s.phrases;
            INSERT INTO %(database)s.phrases SELECT * FROM tmp ORDER BY
            %(tabkeystr)s mlen ASC, user_freq DESC, freq DESC, id ASC;
            DROP TABLE tmp;
            CREATE TABLE tmp AS SELECT * FROM %(database)s.goucima;
            DELETE FROM %(database)s.goucima;
            INSERT INTO %(database)s.goucima SELECT * FROM tmp ORDER BY zi,g0,g1;
            DROP TABLE tmp;
            CREATE TABLE tmp AS SELECT * FROM %(database)s.pinyin;
            DELETE FROM %(database)s.pinyin;
            INSERT INTO %(database)s.pinyin SELECT * FROM tmp ORDER BY p0,p1,p2,p3,p4,p5,plen ASC;
            DROP TABLE tmp;
            '''
        tabkeystr = ''
        for i in range(self._mlen):
            tabkeystr +='m%d, ' % i
        self.db.executescript (sqlstr % {'database':database,'tabkeystr':tabkeystr })
        self.db.executescript ("VACUUM;")
        self.db.commit()
    
    def drop_indexes(self, database):
        '''Drop the index in database to reduce it's size'''
        sqlstr = '''
            DROP INDEX IF EXISTS %(database)s.goucima_index_z;
            DROP INDEX IF EXISTS %(database)s.pinyin_index_i;
            DROP INDEX IF EXISTS %(database)s.phrases_index_p;
            DROP INDEX IF EXISTS %(database)s.phrases_index_i;
            VACUUM; 
            ''' % { 'database':database }
        
        self.db.executescript (sqlstr)
        self.db.commit()
    
    def create_indexes(self, database, commit=True):
        sqlstr = '''
            CREATE INDEX IF NOT EXISTS %(database)s.goucima_index_z ON goucima (zi);
            CREATE INDEX IF NOT EXISTS %(database)s.pinyin_index_i ON pinyin (p0,p1,p2,p3,p4,p5,plen ASC, freq DESC);
            ''' % { 'database':database }

        sqlstr_t = '''
            CREATE INDEX IF NOT EXISTS %(database)s.phrases_index_p ON phrases
            (%(tabkeystr)s mlen ASC, freq DESC, id ASC);
            CREATE INDEX IF NOT EXISTS %(database)s.phrases_index_i ON phrases (phrase, mlen ASC);
            ''' 
        tabkeystr = ''
        for i in range(self._mlen):
            tabkeystr +='m%d,' % i
        if database == 'main':
            sqlstr = sqlstr_t % {'database':database,'tabkeystr':tabkeystr } + sqlstr
        else:
            sqlstr = sqlstr_t % {'database':database,'tabkeystr':tabkeystr }
        self.db.executescript (sqlstr)
        if commit:
            self.db.commit()
    
    def compare (self,x,y):
        return cmp (x[1], y[1]) or -(cmp (x[-1], y[-1])) \
                or -(cmp (x[-2], y[-2])) or (cmp (x[0], y[0]))

    def select_words( self, tabkeys, onechar=False, bitmask=0 ):
        '''
        Get phrases from database by tab_key objects
        ( which should be equal or less than the max key length)
        This method is called in table.py by passing UserInput held data
        Return result[:] 
        '''
        # firstly, we make sure the len we used is equal or less than the max key length
        _len = min( len(tabkeys),self._mlen )
        _condition = ''
        _condition += ''.join ( map (lambda x: 'AND m%d = ? ' %x, range(_len) ) )
        if onechar:
            # for some users really like to select only single characters
            _condition += 'AND clen=1 '
        if bitmask:
            # now just the bits for chinese
            all_ints = xrange(1,5)
            need_ints = filter (lambda x: x & bitmask, all_ints)
            bit_condition = 'OR'.join( map(lambda x: ' category = %d ' %x,\
                    need_ints) )
            _condition += 'AND (%s) ' % bit_condition

        # you can increase the x in _len + x to include more result, but in the most case, we only need one more key result, so we don't need the extra overhead :)
        # we start search for 1 key more, if nothing, then 2 key more and so on
        # this is the max len we need to add into the select cause.
        w_len = self._mlen - _len +1
        # we start from 2, because it is < in the sqlite select, which need 1 more.
        x_len = 2
        while x_len <= w_len + 1:
            sqlstr = '''SELECT * FROM (SELECT * FROM main.phrases WHERE mlen < %(mk)d  %(condition)s 
            UNION ALL
            SELECT * FROM user_db.phrases WHERE mlen < %(mk)d %(condition)s 
            UNION ALL
            SELECT * FROM mudb.phrases WHERE mlen < %(mk)d %(condition)s )
            ORDER BY mlen ASC, user_freq DESC, freq DESC, id ASC;''' % { 'mk':_len+x_len, 'condition':_condition}
            # we have redefine the __int__(self) in class tabdict.tab_key to return the key id, so we can use map to got key id :)
            _tabkeys = map(int,tabkeys[:_len])
            _tabkeys += _tabkeys + _tabkeys
            result = self.db.execute(sqlstr, _tabkeys).fetchall()
            #self.db.commit()
            # if we find word, we stop this while, 
            if len(result) >0:
                break
            x_len += 1
        # here in order to get high speed, I use complicated map
        # to subtitute for
        sysdb={}
        usrdb={}
        mudb={}
        _cand = []
        #searchres = map ( lambda res: res[-2] and [ True, [(res[:-2],[res[:-1],res[-1:]])] ]\
        #        or [ False, [(res[:-2] , [res[:-1],res[-1:]])] ] \
        #        , result )
        searchres = map ( lambda res: [ int(res[-2]), int(res[-1]),
            [(res[1:-2],[res[:-1],res[-1:]])] ], result)
        # for sysdb
        reslist=filter( lambda x: not x[1], searchres )
        map (lambda x: sysdb.update(x[2]), reslist)
        # for usrdb
        reslist=filter( lambda x: ( x[0] in [0,-1] ) and x[1], searchres )
        map (lambda x: usrdb.update(x[2]), reslist)
        # for mudb
        reslist=filter( lambda x: ( x[0] not in [0,-1] ) and x[1], searchres )
        map (lambda x: mudb.update(x[2]), reslist)

        # first process mudb
        searchres = map ( lambda key: mudb[key][0] + mudb[key][1], mudb )
        #print searchres
        map (_cand.append, searchres)

        # now process usrdb and sysdb
        searchres = map ( lambda key:  (not mudb.has_key(key))  and usrdb[key][0] + usrdb[key][1]\
                or None , usrdb )
        searchres = filter(lambda x: bool(x), searchres )
        #print searchres
        map (_cand.append, searchres)
        searchres = map ( lambda key: ((not mudb.has_key(key)) and (not usrdb.has_key(key)) )and sysdb[key][0] + sysdb[key][1]\
                or None, sysdb )
        searchres = filter (lambda x: bool(x), searchres)
        map (_cand.append, searchres)
        #for key in usrdb:
        #    if not sysdb.has_key (key):
        #        _cand.append( usrdb[key][0] + usrdb[key][1] )
        #    else:
        #        _cand.append( sysdb[key][0] + usrdb[key][1] )
        #for key in sysdb:
        #    if not usrdb.has_key (key):
        #        _cand.append( sysdb[key][0] + sysdb[key][1] )
        _cand.sort(cmp=self.compare)
        return _cand[:]

    def select_zi( self, tabkeys ):
        '''
        Get zi from database by tab_key objects
        ( which should be equal or less than 6)
        This method is called in table.py by passing UserInput held data
        Return  result[:] 
        '''
        # firstly, we make sure the len we used is equal or less than
        # the max pinyin length 7 (include tune[1-5])
        _len = min( len(tabkeys), 7 )
        _condition = ''
        #for i in range(_len):
        #    _condition += 'AND p%d = ? ' % i
        _condition += ''.join ( map (lambda x: 'AND p%d = ? ' %x, range(_len)) )
        # you can increase the x in _len + x to include more result, but in the most case, we only need one more key result, so we don't need the extra overhead :)
        # here we need make sure that the 3 <= plen <=7,
        # so , if if _len < 3, than we start from 3; 
        #   if _len >= 3, we start from _len + 1;
        #   if _len = 7, we start from _len
        if _len < 7:
            if _len < 3:
                x_len = 3
            else:
                x_len = _len + 1
        else:<commit_msg>Remove unneeded sort in select_words(), speedup<commit_after># -*- coding: utf-8 -*-
# vim:et sts=4 sw=4
#
# ibus-table - The Tables engine for IBus
#
# Copyright (c) 2008-2009 <NAME> <<EMAIL>>
#
# This library is free software; you can redistribute it and/or
# modify it under the terms of the GNU Lesser General Public
# License as published by the Free Software Foundation; either
# version 2.1 of the License, or (at your option) any later version.
#
# This library is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# Lesser General Public License for more details.
#
# You should have received a copy of the GNU Lesser General Public
# License along with this library; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
#
# $Id: $
#

import os
import os.path as path
from sys import stderr
import sqlite3
import tabdict
import uuid
import time
import re

patt_r = re.compile(r'c([ea])(\d):(.*)')
patt_p = re.compile(r'p(-{0,1}\d)(-{0,1}\d)')

# first make some number index we will used :)
#(MLEN, CLEN, M0, M1, M2, M3, M4, PHRASE, FREQ, USER_FREQ) = range (0,10)


class tabsqlitedb:
    '''Phrase database for tables'''
    def __init__(self, name = 'table.db', user_db = None, filename = None ):
        # use filename when you are creating db from source
        # use name when you are using db
        # first we use the Parse in tabdict, which transform the char(a,b,c,...) to int(1,2,3,...) to fasten the sql enquiry
        self.parse = tabdict.parse
        self.deparse = tabdict.deparse
        self._add_phrase_sqlstr = ''
        self.old_phrases=[]
        self.ime_property_cache = {}
        
        if filename:
            # now we are creating db
            self.db = sqlite3.connect( filename )
        else:
            try:
                os.system('cat %s > /dev/null' % name)
            except:
                pass
            # open system phrase db
            self.db = sqlite3.connect(  name )
        try:
            self.db.execute( 'PRAGMA page_size = 8192; ' )
            self.db.execute( 'PRAGMA cache_size = 20000; ' )
            # increase the cache size to speedup sqlite enquiry
            self.db.execute( 'PRAGMA temp_store = MEMORY; ' )
            self.db.execute( 'PRAGMA synchronous = OFF; ' )
        except:
            print 'encountering error when init db'
            pass
        # create IME property table
        self.db.executescript('CREATE TABLE IF NOT EXISTS main.ime (attr TEXT, val TEXT);')
        # make sure we have values in ime table.
        if not self.db.execute('SELECT val FROM main.ime \
            WHERE attr="name";').fetchall():
            ime_keys={'name':'',
                      'name.zh_cn':'',
                      'name.zh_hk':'',
                      'name.zh_tw':'',
                      'author':'somebody', 
                      'uuid':'%s' % uuid.uuid4(),
                      'serial_number':'%s' % time.strftime('%Y%m%d'),
                      'icon':'ibus-table.svg',
                      'license':'LGPL',
                      'languages':'',
                      'language_filter':'',
                      'valid_input_chars':'abcdefghijklmnopqrstuvwxyz',
                      'max_key_length':'4',
            #          'commit_keys':'space',
            #          'forward_keys':'Return',
                      'select_keys':'1,2,3,4,5,6,7,8,9,0',
            #          'page_up_keys':'Page_Up,minus',
            #          'page_down_keys':'Page_Down,equal',
                      'status_prompt':'',
                      'def_full_width_punct':'TRUE',
                      'def_full_width_letter':'FALSE',
                      'user_can_define_phrase':'FALSE',
                      'pinyin_mode':'FALSE',
                      'dynamic_adjust':'FALSE',
                      'auto_commit':'false',
                      #'no_check_chars':u'',
                      'description':'A IME under IBus Table',
                      'layout':'us',
                      'rules':'',
                      #'rules':'ce2:p11+p12+p21+p22;ce3:p11+p21+p22+p31;ca4:p11+p21+p31+p41'}
                      'least_commit_length':'0',
                      'start_chars':''
                      # we use this entry for those IME, which don't
                      # have rules to build up phrase, but still need
                      # auto commit to preedit
                      }
            # inital the attribute in ime table, which should be updated from mabiao
            for _name in ime_keys:
                sqlstr = 'INSERT INTO main.ime (attr,val) VALUES (?,?);'
                self.db.execute( sqlstr, (_name,ime_keys[_name]) )
        # share variables in this class:
        self._mlen = int ( self.get_ime_property ("max_key_length") )
        # for chinese
        self._is_chinese = self.is_chinese()
        # for fast add word
        self._set_add_phrase_sqlstr()
        #(ID, MLEN, CLEN, M0, M1, M2, M3, M4, CATEGORY, PHRASE, FREQ, USER_FREQ) = range (0,12)
        self._pt_index = ['id', 'mlen', 'clen']
        for i in range(self._mlen):
            self._pt_index.append ('m%d' %i)
        if self._is_chinese:
            self._pt_index += ['category']
        self._pt_index += ['phrase','freq','user_freq']
        self.user_can_define_phrase = self.get_ime_property('user_can_define_phrase')
        if self.user_can_define_phrase:
            if self.user_can_define_phrase.lower() == u'true' :
                self.user_can_define_phrase = True
            else:
                self.user_can_define_phrase = False
        else:
            print 'Could not find "user_can_define_phrase" entry from database, is it a outdated database?'
            self.user_can_define_phrase = False
        
        self.dynamic_adjust = self.get_ime_property('dynamic_adjust')
        if self.dynamic_adjust:
            if self.dynamic_adjust.lower() == u'true' :
                self.dynamic_adjust = True
            else:
                self.dynamic_adjust = False
        else:
            print 'Could not find "dynamic_adjust" entry from database, is it a outdated database?'
            self.dynamic_adjust = False
        
        self.rules = self.get_rules ()
        self.pkeylens = []
        self.pkeylens = self.phrase_keys_len ()
        self.startchars = self.get_start_chars ()
        
        #self._no_check_chars = self.get_no_check_chars()
        # for fast gouci
        self._goucima={}
        if filename:
            # since we just creating db, we do not need userdb and mudb
            return
        
        # user database:
        if user_db != None:
            home_path = os.getenv ("HOME")
            tables_path = path.join (home_path, ".ibus",  "tables")
            user_db = path.join (tables_path, user_db)
            if not path.isdir (tables_path):
                os.makedirs (tables_path)
            try:
                desc = self.get_database_desc (user_db)
                if desc == None :
                    self.init_user_db (user_db)
                elif desc["version"] != "0.5":
                    new_name = "%s.%d" %(user_db, os.getpid())
                    print >> stderr, "Can not support the user db. We will rename it to %s" % new_name
                    self.old_phrases = self.extra_user_phrases( user_db )
                    os.rename (user_db, new_name)
                    self.init_user_db (user_db)
                elif self.get_table_phrase_len(user_db) != len(self._pt_index):
                    print >> stderr, "user db format outdated."
                    # store old user phrases
                    self.old_phrases = self.extra_user_phrases( user_db )
                    new_name = "%s.%d" %(user_db, os.getpid())
                    os.rename (user_db, new_name)
                    self.init_user_db (user_db)
            except:
                import traceback
                traceback.print_exc()
        else:
            user_db = ":memory:"
        
        # open user phrase database
        try:
            self.db.execute ('ATTACH DATABASE "%s" AS user_db;' % user_db)
        except:
            print >> stderr, "The user database was damaged. We will recreate it!"
            os.rename (user_db, "%s.%d" % (user_db, os.getpid ()))
            self.init_user_db (user_db)
            self.db.execute ('ATTACH DATABASE "%s" AS user_db;' % user_db)
        self.create_tables ("user_db")
        if self.old_phrases:
            # (mlen, phrase, freq, user_freq)
            # the phrases will be deparse again, and then be added     
            # the characters will be discard :(
            #chars = filter (lambda x: x[0] == 1, self.old_phrases)
            # print chars
            phrases = filter (lambda x: x[0] > 1, self.old_phrases)
            phrases = map(lambda x: [self.parse_phrase_to_tabkeys(x[1])]\
                    + list(x[1:]) , phrases)
 
            map (self.u_add_phrase,phrases)
            self.db.commit ()


        # try create all tables in user database
        self.create_indexes ("user_db",commit=False)
        self.generate_userdb_desc ()
        
        # attach mudb for working process
        mudb = ":memory:"  
        self.db.execute ('ATTACH DATABASE "%s" AS mudb;' % mudb )
        self.create_tables ("mudb")
    
    def update_phrase (self, entry, database='user_db'):
        '''update phrase freqs'''
        #print entry
        _con = [ entry[-1] ] + list(entry[1:3+entry[1]]) + [entry[-3]]
        #print _con
        _condition = u''.join( map(lambda x: 'AND m%d = ? ' % x, range(entry[1]) )    )
        #print _condition
        sqlstr = 'UPDATE %s.phrases SET user_freq = ? WHERE mlen = ? AND clen = ? %s AND phrase = ?;' % (database, _condition)
        #print sqlstr
        self.db.execute ( sqlstr , _con )
        # because we may update different db, we'd better commit every time.
        self.db.commit()

    def sync_usrdb (self):
        # we need to update the user_db
        #print 'sync userdb'
        mudata = self.db.execute ('SELECT * FROM mudb.phrases;').fetchall()
        #print mudata
        data_u = filter ( lambda x: x[-2] in [1,-3], mudata)
        data_a = filter ( lambda x: x[-2]==2, mudata)
        data_n = filter ( lambda x: x[-2]==-2, mudata)
        #print data_a
        data_a = map (lambda x: (u''.join ( map(self.deparse, x[3:3+x[1]])),x[-3],0,x[-1] ), data_a)
        data_n = map (lambda x: (u''.join ( map(self.deparse, x[3:3+x[1]])),x[-3],-1,x[-1] ), data_n)
        #print data_u
        map (self.update_phrase, data_u)
        #print self.db.execute('select * from user_db.phrases;').fetchall()
        map (self.u_add_phrase,data_a)
        map (self.u_add_phrase,data_n)
        self.db.commit ()
    
    def is_chinese (self):
        __lang = self.get_ime_property ('languages')
        if __lang:
            __langs = __lang.split(',')
            for _l in __langs:
                if _l.lower().find('zh') != -1:
                    return True
        return False

    def get_chinese_mode (self):
        try:
            __dict = {'cm0':0,'cm1':1,'cm2':2,'cm3':3,'cm4':4}
            __filt = self.get_ime_property ('language_filter')
            return __dict[__filt]
        except:
            return -1

    def get_page_size (self):
        try:
            __select_keys = self.get_ime_property ('select_keys')
            return len (__select_keys.split (','))
        except:
            return 6

    def create_tables (self, database):
        '''Create tables that contain all phrase'''

        try:
            self.db.execute( 'PRAGMA cache_size = 20000; ' )
            # increase the cache size to speedup sqlite enquiry
        except:
            pass
        if database == 'main':
            # create  ikeys table
            sqlstr = 'CREATE TABLE IF NOT EXISTS %s.ikeys (ikey TEXT PRIMARY KEY, id INTEGER);' % database
            self.db.execute ( sqlstr )
        
            # create goucima table, this table is used in construct new phrases
            sqlstr = 'CREATE TABLE IF NOT EXISTS %s.goucima (zi TEXT PRIMARY KEY' % database
            for i in range(self._mlen):
                sqlstr += ', g%d INTEGER' % i 
            #sqlstr += ''.join(map (lambda x: ', g%d INTEGER' % x, range(self._mlen)) )
            sqlstr += ');'
            self.db.execute ( sqlstr )

            # create pinyin table, this table is used in search single character for user handly
            sqlstr = 'CREATE TABLE IF NOT EXISTS %s.pinyin ( plen INTEGER, ' % database
            #for i in range(6):
            #    sqlstr += 'p%d INTEGER, ' % i 
            sqlstr += ''.join( map (lambda x: 'p%d INTEGER, ' % x, range(7) ) )
            sqlstr += 'zi TEXT, freq INTEGER);'
            self.db.execute ( sqlstr )

        # create phrase table (mabiao)
        sqlstr = 'CREATE TABLE IF NOT EXISTS %s.phrases (id INTEGER PRIMARY KEY AUTOINCREMENT,\
                mlen INTEGER, clen INTEGER, ' % database
        #for i in range(self._mlen):
        #    sqlstr += 'm%d INTEGER, ' % i 
        sqlstr += ''.join ( map (lambda x: 'm%d INTEGER, ' % x, range(self._mlen)) )
        if self._is_chinese:
            sqlstr += 'category INTEGER, '
        sqlstr += 'phrase TEXT, freq INTEGER, user_freq INTEGER);'
        self.db.execute ( sqlstr )
        self.db.commit()
    
    def update_ime (self, attrs):
        '''Update attributes in ime table, attrs is a iterable object
        Like [(attr,val), (attr,val), ...]
        '''
        sqlstr = 'UPDATE main.ime SET val = ? WHERE attr = ?;' 
        for attr,val in attrs:
            _sqlstr = 'SELECT * from main.ime WHERE attr = ?' 
            res = self.db.execute( _sqlstr, (attr,) ).fetchall()
            if res:
                self.db.execute(sqlstr,(val,attr))
            else:
                #print '"',attr,'"'," didn't in ime property now!"
                pass
        # then flush previous cache
        self.ime_property_cache = {}
        # we need to update some self variables now.
        self._mlen = int (self.get_ime_property ('max_key_length' ))
        self._is_chinese = self.is_chinese()
        self._set_add_phrase_sqlstr()
        self._pt_index = ['id', 'mlen', 'clen']
        for i in range(self._mlen):
            self._pt_index.append ('m%d' %i)
        if self._is_chinese:
            self._pt_index += ['category']
        self._pt_index += ['phrase','freq','user_freq']
        self.user_can_define_phrase = self.get_ime_property('user_can_define_phrase')
        if self.user_can_define_phrase:
            if self.user_can_define_phrase.lower() == u'true' :
                self.user_can_define_phrase = True
            else:
                self.user_can_define_phrase = False
        else:
            print 'Could not find "user_can_define_phrase" entry from database, is it a outdated database?'
            self.user_can_define_phrase = False
        self.rules = self.get_rules ()

        self.db.commit()

    def get_rules (self):
        '''Get phrase construct rules'''
        rules={}
        if self.user_can_define_phrase:
            try:
                _rules = self.get_ime_property ('rules')
                if _rules:
                    _rules = _rules.strip().split(';')
                for rule in _rules:
                    res = patt_r.match (rule)
                    if res:
                        cms = []
                        if res.group(1) == 'a':
                            rules['above'] = int(res.group(2))
                        _cms = res.group(3).split('+')
                        if len(_cms) > self._mlen:
                            print 'rule: "%s" over max key length' % rule
                            break
                        for _cm in _cms:
                            cm_res = patt_p.match(_cm)
                            cms.append(( int(cm_res.group(1)),int(cm_res.group(2)) ))
                        rules[int(res.group(2))]=cms
                    else:
                        print 'not a legal rule: "%s"'  % rule 
            except Exception:
                import traceback
                traceback.print_exc ()
            return rules
        else:
            return ""

    def phrase_keys_len (self):
        '''Return the phrase possible key length'''
        if self.rules:
            max_len = self.rules["above"]
            try:
                return map ( lambda x: len(self.rules[x]), range(2, max_len+1) )[:]
            except:
                return []
        else:
            # we try to findout whether we have least commit len
            # in ime table
            try:
                least_commit_len = int(self.get_ime_property('least_commit_length'))
            except:
                least_commit_len = 0
            if least_commit_len > 0:
                return range (least_commit_len, self._mlen + 1)
            else:
                return []
    
    def get_start_chars (self):
        '''return possible start chars of IME'''
        try:
            return self.get_ime_property('start_chars')
        except:
            return ''

    def get_no_check_chars (self):
        '''Get the characters which engine should not change freq'''
        _chars= self.get_ime_property('no_check_chars')
        try:
            _chars = _chars.decode('utf-8')
        except:
            pass
        return _chars

    def add_phrases (self, phrases, database = 'main'):
        '''Add phrases to database, phrases is a iterable object
        Like: [(tabkeys, phrase, freq ,user_freq), (tabkeys, phrase, freq, user_freq), ...]
        '''
        map (self.add_phrase, phrases, [database]*len(phrases),[False]*len(phrases) )
        self.db.commit()
    
    def add_new_phrases (self, nphrases, database='main'):
        '''Add new phrases into db, new phrases is a object
        of [(phrase,freq), (phrase,freq),...]'''
        n_phrases=[]
        for _ph, _freq in nphrases:
            try:
                _tabkey = self.parse_phrase_to_tabkeys(_ph)
                if not self.check_phrase_internal (_ph, _tabkey, database):
                    # we don't have this phrase
                    n_phrases.append ( (_tabkey, _ph, _freq, 0) )
            except:
                print '\"%s\" would not been added' % _ph
        if n_phrases:
            self.add_phrases ( n_phrases, database )
    
    def u_add_phrase (self,nphrase):
        '''Add a phrase to userdb'''
        self.add_phrase (nphrase,database='user_db',commit=False)

    def _set_add_phrase_sqlstr(self):
        '''Create the sqlstr for add phrase according to self._mlen.'''
        sqlstr = 'INSERT INTO %s.phrases ( mlen, clen, '
        sql_suffix = 'VALUES ( ?, ?, '
        mmlen = range(self._mlen)
        sqlstr += ''.join ( map(lambda x: 'm%d, ' %x , mmlen) )
        sql_suffix += ''.join ( map (lambda x: '?, ' , mmlen) )
        if self._is_chinese:
            sqlstr += 'category, '
            sql_suffix += '?, '
        sqlstr += 'phrase, freq, user_freq) '
        sql_suffix += '?, ?, ? );'
        sqlstr += sql_suffix
        self._add_phrase_sqlstr = sqlstr

    def add_phrase (self, aphrase, database = 'main',commit=True):
        '''Add phrase to database, phrase is a object of
        (tabkeys, phrase, freq ,user_freq)
        '''
        sqlstr = self._add_phrase_sqlstr
        try:
            tabkeys,phrase,freq,user_freq = aphrase
        except:
            tabkeys,phrase,freq = aphrase
            user_freq = 0
        # now we will set the category bits if this is chinese
        if self._is_chinese:
            # this is the bitmask we will use,
            # from low to high, 1st bit is simplify Chinese,
            # 2nd bit is traditional Chinese,
            # 3rd bit means out of gbk
            category = 0
            # make sure that we got a unicode string
            if type(phrase) != type(u''):
                phrase = phrase.decode('utf8')
            tmp_phrase = ''.join(re.findall(u'['
                                            + u'\u4E00-\u9FCB'
                                            + u'\u3400-\u4DB5'
                                            + u'\uF900-\uFaFF'
                                            + u'\U00020000-\U0002A6D6'
                                            + u'\U0002A700-\U0002B734'
                                            + u'\U0002B740-\U0002B81D'
                                            + u'\U0002F800-\U0002FA1D'
                                            + u']+',
                                            phrase))
            # first whether in gb2312
            try:
                tmp_phrase.encode('gb2312')
                category |= 1
            except:
                if '〇'.decode('utf8') in tmp_phrase:
                    # we add '〇' into SC as well
                    category |= 1
            # second check big5-hkscs
            try:
                tmp_phrase.encode('big5hkscs')
                category |= 1 << 1
            except:
                # then check whether in gbk,
                if category & 1:
                    # already know in SC
                    pass
                else:
                    # need to check
                    try:
                        tmp_phrase.encode('gbk')
                        category |= 1
                    except:
                        # not in gbk
                        pass
            # then set for 3rd bit, if not in SC and TC
            if not ( category & (1 | 1 << 1) ):
                category |= (1 << 2)
        try:
            tbks = self.parse(tabkeys)
            if len(tbks) != len(tabkeys):
                print 'In %s %s: we parse tabkeys fail' % (phrase, tabkeys )
                return
            record = [None] * (5 + self._mlen)
            record [0] = len (tabkeys)
            record [1] = len (phrase)
            record [2: 2+len(tabkeys)] = map (lambda x: tbks[x].get_key_id(), range(0,len(tabkeys)))
            if self._is_chinese:
                record +=[None]
                record[-4] = category
            record[-3:] = phrase, freq, user_freq
            self.db.execute (sqlstr % database, record)
            if commit:
                self.db.commit()    
        except Exception:
            import traceback
            traceback.print_exc()
    
    def add_goucima (self, gcms):
        '''Add goucima into database, gcms is iterable object
        Like gcms = [(zi,goucima),(zi,goucima), ...]
        '''
        count = 1
        for zi,gcm in gcms:
            _con = ''
            _val = ''
            _len = min ( len(gcm),self._mlen)
            for i in range( _len ):
                _con += ', g%d' % i
                _val += ', ?' 
            sqlstr = '''INSERT INTO main.goucima ( zi %s )
            VALUES ( ? %s );''' % (_con, _val)
            try:
                gc = self.parse(gcm)
                if len(gc) != len(gcm):
                    error_m = u'%s %s: Can not parse goucima' % (zi, gcm )
                    raise Exception ( error_m.encode ('utf8') )
                record = [zi]
                for i in range(_len):
                    record.append( gc[i].get_key_id())
                self.db.execute (sqlstr , record)
            
            except Exception:
                import traceback
                traceback.print_exc()
            count += 1
        self.db.commit()
    
    def add_pinyin (self, pinyins, database = 'main'):
        '''Add pinyin to database, pinyins is a iterable object
        Like: [(zi,pinyin, freq), (zi, pinyin, freq), ...]
        '''
        sqlstr = 'INSERT INTO %s.pinyin ( plen, '
        sql_suffix = 'VALUES ( ?, '
        for i in range(7):
            sqlstr += 'p%d, ' % i
            sql_suffix += '?, '
        sqlstr += 'zi, freq ) '
        sql_suffix += '?, ? );'
        sqlstr += sql_suffix
        
        count = 1
        for pinyin,zi,freq in pinyins:
            try:
                pinyin_n = pinyin.replace('1','!').replace('2','@').replace('3','#').replace('4','$').replace('5','%')
                py = self.parse(pinyin_n)
                if len(py) != len(pinyin_n):
                    error_m = u'%s %s: Can not parse pinyin' % (zi, pinyin )
                    raise Exception ( error_m.encode ('utf8') )
                record = [None]*10
                record [0] = len (pinyin_n)
                for i in range(0,len(pinyin_n)):
                    record [ 1+i ] = py[i].get_key_id()
                record [-2] = zi
                record [-1] = freq
                self.db.execute (sqlstr % database, record)
            except Exception:
                print count, ': ', zi.encode('utf8'), ' ', pinyin
                import traceback
                traceback.print_exc()
            count += 1

        self.db.commit()    
    
    def optimize_database (self, database='main'):
        sqlstr = '''
            CREATE TABLE tmp AS SELECT * FROM %(database)s.phrases;
            DELETE FROM %(database)s.phrases;
            INSERT INTO %(database)s.phrases SELECT * FROM tmp ORDER BY
            %(tabkeystr)s mlen ASC, user_freq DESC, freq DESC, id ASC;
            DROP TABLE tmp;
            CREATE TABLE tmp AS SELECT * FROM %(database)s.goucima;
            DELETE FROM %(database)s.goucima;
            INSERT INTO %(database)s.goucima SELECT * FROM tmp ORDER BY zi,g0,g1;
            DROP TABLE tmp;
            CREATE TABLE tmp AS SELECT * FROM %(database)s.pinyin;
            DELETE FROM %(database)s.pinyin;
            INSERT INTO %(database)s.pinyin SELECT * FROM tmp ORDER BY p0,p1,p2,p3,p4,p5,plen ASC;
            DROP TABLE tmp;
            '''
        tabkeystr = ''
        for i in range(self._mlen):
            tabkeystr +='m%d, ' % i
        self.db.executescript (sqlstr % {'database':database,'tabkeystr':tabkeystr })
        self.db.executescript ("VACUUM;")
        self.db.commit()
    
    def drop_indexes(self, database):
        '''Drop the index in database to reduce it's size'''
        sqlstr = '''
            DROP INDEX IF EXISTS %(database)s.goucima_index_z;
            DROP INDEX IF EXISTS %(database)s.pinyin_index_i;
            DROP INDEX IF EXISTS %(database)s.phrases_index_p;
            DROP INDEX IF EXISTS %(database)s.phrases_index_i;
            VACUUM; 
            ''' % { 'database':database }
        
        self.db.executescript (sqlstr)
        self.db.commit()
    
    def create_indexes(self, database, commit=True):
        sqlstr = '''
            CREATE INDEX IF NOT EXISTS %(database)s.goucima_index_z ON goucima (zi);
            CREATE INDEX IF NOT EXISTS %(database)s.pinyin_index_i ON pinyin (p0,p1,p2,p3,p4,p5,plen ASC, freq DESC);
            ''' % { 'database':database }

        sqlstr_t = '''
            CREATE INDEX IF NOT EXISTS %(database)s.phrases_index_p ON phrases
            (%(tabkeystr)s mlen ASC, freq DESC, id ASC);
            CREATE INDEX IF NOT EXISTS %(database)s.phrases_index_i ON phrases (phrase, mlen ASC);
            ''' 
        tabkeystr = ''
        for i in range(self._mlen):
            tabkeystr +='m%d,' % i
        if database == 'main':
            sqlstr = sqlstr_t % {'database':database,'tabkeystr':tabkeystr } + sqlstr
        else:
            sqlstr = sqlstr_t % {'database':database,'tabkeystr':tabkeystr }
        self.db.executescript (sqlstr)
        if commit:
            self.db.commit()
    
    def compare (self,x,y):
        return cmp (x[1], y[1]) or -(cmp (x[-1], y[-1])) \
                or -(cmp (x[-2], y[-2])) or (cmp (x[0], y[0]))

    def select_words( self, tabkeys, onechar=False, bitmask=0 ):
        '''
        Get phrases from database by tab_key objects
        ( which should be equal or less than the max key length)
        This method is called in table.py by passing UserInput held data
        Return result[:] 
        '''
        # firstly, we make sure the len we used is equal or less than the max key length
        _len = min( len(tabkeys),self._mlen )
        _condition = ''
        _condition += ''.join ( map (lambda x: 'AND m%d = ? ' %x, range(_len) ) )
        if onechar:
            # for some users really like to select only single characters
            _condition += 'AND clen=1 '
        if bitmask:
            # now just the bits for chinese
            all_ints = xrange(1,5)
            need_ints = filter (lambda x: x & bitmask, all_ints)
            bit_condition = 'OR'.join( map(lambda x: ' category = %d ' %x,\
                    need_ints) )
            _condition += 'AND (%s) ' % bit_condition

        # you can increase the x in _len + x to include more result, but in the most case, we only need one more key result, so we don't need the extra overhead :)
        # we start search for 1 key more, if nothing, then 2 key more and so on
        # this is the max len we need to add into the select cause.
        w_len = self._mlen - _len +1
        # we start from 2, because it is < in the sqlite select, which need 1 more.
        x_len = 2
        while x_len <= w_len + 1:
            sqlstr = '''SELECT * FROM (SELECT * FROM main.phrases WHERE mlen < %(mk)d  %(condition)s 
            UNION ALL
            SELECT * FROM user_db.phrases WHERE mlen < %(mk)d %(condition)s 
            UNION ALL
            SELECT * FROM mudb.phrases WHERE mlen < %(mk)d %(condition)s )
            ORDER BY mlen ASC, user_freq DESC, freq DESC, id ASC;''' % { 'mk':_len+x_len, 'condition':_condition}
            # we have redefine the __int__(self) in class tabdict.tab_key to return the key id, so we can use map to got key id :)
            _tabkeys = map(int,tabkeys[:_len])
            _tabkeys += _tabkeys + _tabkeys
            result = self.db.execute(sqlstr, _tabkeys).fetchall()
            #self.db.commit()
            # if we find word, we stop this while, 
            if len(result) >0:
                break
            x_len += 1

        # Remove duplicated entries other than different id,freq,user_freq
        box_uniq = set()
        def add2box(x):
            box_uniq.add(x[1:-2])
            return x
        result = [add2box(x) for x in result if x[1:-2] not in box_uniq]
        return result[:]

    def select_zi( self, tabkeys ):
        '''
        Get zi from database by tab_key objects
        ( which should be equal or less than 6)
        This method is called in table.py by passing UserInput held data
        Return  result[:] 
        '''
        # firstly, we make sure the len we used is equal or less than
        # the max pinyin length 7 (include tune[1-5])
        _len = min( len(tabkeys), 7 )
        _condition = ''
        #for i in range(_len):
        #    _condition += 'AND p%d = ? ' % i
        _condition += ''.join ( map (lambda x: 'AND p%d = ? ' %x, range(_len)) )
        # you can increase the x in _len + x to include more result, but in the most case, we only need one more key result, so we don't need the extra overhead :)
        # here we need make sure that the 3 <= plen <=7,
        # so , if if _len < 3, than we start from 3; 
        #   if _len >= 3, we start from _len + 1;
        #   if _len = 7, we start from _len
        if _len < 7:
            if _len < 3:
                x_len = 3
            else:
                x_len = _len + 1
        else:
{'fraction_non_alphanumeric': 0.11379719708299287, 'fraction_numerical': 0.011116078999707065, 'mean_word_length': 1.6034358192181104, 'pattern_counts': {'":': 6, '<': 41, '<?xml version=': 0, '>': 33, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 40, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4586654', 'n_tokens_mistral': 20274, 'n_tokens_neox': 19156, 'n_words': 5441}