cmY64c9R

1#!/usr/bin/env python2
2import glob
3import os
4import tempfile
5import re
6import argparse
7
8import MySQLdb
9
10R_CODEPOINT = re.compile(r'\\[uU]([0-9a-fA-F]{4})')
11INDICES_TABLE = (
12    (('id',),                       # col_count=1
13     ('id', 'arg1'),
14     ('relation',),
15     ('arg1',),
16     ('arg2',),
17    ),
18    (('id',),                       # col_count = 2
19     ('id', 'arg1'),
20     ('relation', 'arg1'),
21     ('arg1', 'relation'),
22     ('arg2', 'relation'),
23     ('arg1', 'arg2'),
24     ('relation', 'arg2'),
25     ('arg2', 'arg1'),
26    ),
27    (('id',),                       # col_count = 3
28     ('id', 'arg1'),
29     ('relation', 'arg1', 'arg2'),
30     ('arg1', 'relation', 'arg2'),
31     ('arg2', 'relation', 'arg1'),
32     ('arg1', 'arg2', 'relation'),
33     ('relation', 'arg2', 'arg1'),
34     ('arg2', 'arg1', 'relation'),
35    ),
36)
37DEFAULT_INDICES = (
38    ('id',),
39    ('id', 'arg1'),
40)
41
42class YagoLoader(object):
43    verbose = False
44    facts_table = 'facts'
45    max_fact_len = 255
46    log_file = None
47    translate = False
48
49    def __init__(self, db):
50        self.db = db
51        self.tmpfn = tempfile.mktemp()
52        print self.tmpfn
53
54    def _print(self, *args):
55        if self.verbose:
56            message = ' '.join(args)
57            print message
58
59    def prepare_schema(self):
60        self._print('Preparing schema', self.facts_table)
61        query = 'CREATE TABLE IF NOT EXISTS `%s` (id VARCHAR(255), relation VARCHAR(255), arg1 VARCHAR(255), arg2 VARCHAR(255))' % self.facts_table
62        cur = self.db.cursor()
63        cur.execute(query)
64
65    def build_indices(self, col_count=2):
66        if col_count >= 1 and col_count <= 3:
67            indices = INDICES_TABLE[col_count-1]
68        else:
69            indices = DEFAULT_INDICES
70        for attr in indices:
71            self._print('Creating index on', ', '.join(attr))
72            self._create_index(attr)
73
74    def _create_index(self, attr):
75        name_suffix = '_'.join((a[:4] for a in attr)) + '_index'
76        if len(name_suffix) + len(self.facts_table) > 64:
77            table_name = self.facts_table[:64-len(name_suffix)]
78        else:
79            table_name = self.facts_table
80        name = table_name + '_' + name_suffix
81        query = 'CREATE INDEX %s ON `%s` (%s)' % (name, self.facts_table, ','.join(attr))
82        cur = self.db.cursor()
83        cur.execute(query)
84
85    def _load_data_infile(self):
86        self._print('Loading table')
87        query = 'LOAD DATA INFILE %s INTO TABLE `%s`' % (repr(self.tmpfn), self.facts_table)
88        cur = self.db.cursor()
89        cur.execute(query)
90        self.db.commit()
91
92    def _log(self, filename):
93        if self.log_file:
94            f = open(self.log_file, 'a')
95            f.write(filename+'\n')
96            f.close()
97
98    def _translate(self, line):
99        res = []
100        start = 0
101        while(True):
102            m = R_CODEPOINT.search(line, start)
103            if not m:
104                break
105            res.append(line[start:m.start()])
106            res.append(unichr(int(m.group(1), 16)))
107            start = m.end()
108        res.append(line[start:])
109        return u''.join(res)
110
111    def load(self, filename, exclude=[]):
112        files = glob.glob(filename) # Windows doesnt expand wildcards?
113        for infn in files:
114            if infn in exclude:
115                self._print('excluded', infn)
116                continue
117            self._print('Translating', infn)
118            infile = open(infn)
119            tmpfile = open(self.tmpfn, 'w')
120            relation = os.path.splitext(os.path.basename(infn))[0]
121            for line in infile:
122                line = line.decode('utf8')
123                if self.translate:
124                    line = self._translate(line)
125                line = line.replace(u'\\', u'\\\\')
126                id, arg1, arg2 = (split.strip() for split in line.split(u'\t'))
127
128                # Ignore long arguments
129                arg1_blen = len(arg1.encode('utf8'))
130                arg2_blen = len(arg2.encode('utf8'))
131                max_len = self.max_fact_len
132                if relation == 'hasGloss' and arg2_blen > max_len:
133                    arg2 = arg2.encode('utf8')[:max_len-4].decode('utf8', 'ignore') + u'..."'
134                elif arg1_blen > max_len or arg2_blen > max_len:
135                    continue
136
137                line = u'\t'.join((id, relation, arg1, arg2)) + u'\n'
138                tmpfile.write(line.encode('utf8'))
139            infile.close()
140            tmpfile.close()
141            self._load_data_infile()
142            self._log(infn)
143
144def make_argparser():
145    parser = argparse.ArgumentParser(description='YagoLoader: Yago to MySQL database loader')
146    parser.add_argument(
147        'host',
148        help='MySQL database host'
149    )
150    parser.add_argument(
151        'username',
152        help='database username'
153    )
154    parser.add_argument(
155        'password',
156        help='database password'
157    )
158    parser.add_argument(
159        'database',
160        help='database name'
161    )
162    parser.add_argument(
163        'files',
164        nargs='+',
165        help='TSV files to be loaded'
166    )
167    parser.add_argument(
168        '-t', '--table',
169        default='facts',
170        required=False,
171        help='name of the facts table (default:"facts")',
172    )
173    parser.add_argument(
174        '-i', '--index_count',
175        default=0,
176        required=False,
177        type=int,
178        help='number of columns for generating database indices'
179    )
180    parser.add_argument(
181        '-u', '--translate',
182        action='store_true',
183        default=False,
184        required=False,
185        help='translate backslashed code points in arguments (default:False)',
186    )
187    parser.add_argument(
188        '-e', '--exclude',
189        required=False,
190        help='exlude list for TSV files'
191    )
192    parser.add_argument(
193        '-l', '--log',
194        required=False,
195        help='log of commited TSV files'
196    )
197    parser.add_argument(
198        '-v', '--verbose',
199        action='store_true',
200        default=True,
201        required=False,
202        help='verbose operation (default:True)',
203    )
204    return parser
205
206def main():
207    parser = make_argparser()
208    args = parser.parse_args()
209
210    exclude_list = []
211    if args.exclude:
212        try:
213            f = open(args.exclude)
214            exclude_list = [filename.strip() for filename in f]
215            f.close()
216        except IOError:
217            pass
218
219    db = MySQLdb.connect(args.host, args.username, args.password, args.database)
220    loader = YagoLoader(db)
221    loader.verbose = args.verbose
222    loader.facts_table = args.table
223    loader.log_file = args.log
224    loader.translate = args.translate
225
226    loader.prepare_schema()
227    for f in args.files:
228        loader.load(f, exclude_list)
229    loader.build_indices(args.index_count)
230
231    db.close()
232
233if __name__ == '__main__':
234    main()