sqlGit.py 18 KB
Newer Older
Inkhey's avatar
Inkhey committed
1 2 3 4 5 6 7 8 9 10 11 12 13
#!/usr/bin/python
# -*- coding: utf-8 -*-
#  sqlGit
#
#  Copyright 2015 Guénaël Muller <contact@inkey-art.net>
#
#  This program is free software; you can redistribute it and/or modify
#  it under the terms of the GNU General Public License as published by
#  the Free Software Foundation; either version 3 of the License, or
#  (at your option) any later version.

import sqlite3
import os
Inkhey's avatar
Inkhey committed
14
from threading import Thread
inkhey's avatar
inkhey committed
15
from subprocess import Popen, PIPE
inkhey's avatar
inkhey committed
16
from walkdir import filtered_walk
inkhey's avatar
inkhey committed
17
from timeit import default_timer as timer
Inkhey's avatar
Inkhey committed
18
# use walkir instead of os.walk to be able
Inkhey's avatar
Inkhey committed
19 20
# to select a depth

inkhey's avatar
inkhey committed
21

inkhey's avatar
inkhey committed
22 23 24 25 26 27 28 29 30 31 32 33 34
def calctime(method):

    def calctime(*args, **kw):
        ts = timer()
        result = method(*args, **kw)
        te = timer()

        print( "\t%r : %2.2f sec" % \
              (method.__name__, te-ts))
        return result

    return calctime

Inkhey's avatar
Inkhey committed
35

Inkhey's avatar
Inkhey committed
36
class sqlGit(object):
inkhey's avatar
inkhey committed
37

Inkhey's avatar
Inkhey committed
38 39 40
    ''' Transform Git data into sqlite database to
    analyse these datas into program like gephi'''

Inkhey's avatar
Inkhey committed
41
    # CREATE
inkhey's avatar
inkhey committed
42

Inkhey's avatar
Inkhey committed
43 44
    def __init__(self, db):
        ''' Initialise sqlite database file'''
Inkhey's avatar
Inkhey committed
45
        self.conn = sqlite3.connect(db, check_same_thread=False)
Inkhey's avatar
Inkhey committed
46

inkhey's avatar
inkhey committed
47
    @calctime
Inkhey's avatar
Inkhey committed
48 49 50
    def create_table(self):
        ''' create SQL tables : author, commits, file, link_author
        commit and link commit_file .'''
Inkhey's avatar
Inkhey committed
51
        cmds=[]
Inkhey's avatar
Inkhey committed
52 53

        c = self.conn.cursor()
Inkhey's avatar
Inkhey committed
54 55
        cmds=[]
        cmds.append('''CREATE TABLE author(
Inkhey's avatar
Inkhey committed
56 57 58 59
                    name text,
                    email text,
                    primary key(name,email));''')

Inkhey's avatar
Inkhey committed
60
        cmds.append('''CREATE TABLE commits(
Inkhey's avatar
Inkhey committed
61 62 63 64 65 66 67 68
                    subject text,
                    hash text primary key,
                    abbrev_hash text,
                    date integer);''')
        # value of dir :
        # root -> "."
        # folder -> "./dossier"
        # subfolders-> "./dossier1/dossier2"
Inkhey's avatar
Inkhey committed
69
        cmds.append('''CREATE TABLE file(
Inkhey's avatar
Inkhey committed
70 71 72
                    name text,
                    size integer,
                    dir text )''')
Inkhey's avatar
Inkhey committed
73 74 75
        threads = [Thread(target=self._execCommit, args=(cmd,)) for cmd in cmds]
        [t.start() for t in threads]
        [t.join() for t in threads]
Inkhey's avatar
Inkhey committed
76
        # Link
Inkhey's avatar
Inkhey committed
77 78
        cmds=[]
        cmds.append('''CREATE TABLE link_author_commits(
Inkhey's avatar
Inkhey committed
79 80 81 82 83 84 85 86 87
                    author_name text,
                    author_mail text,
                    commits text
                        references commits(hash),
                    foreign key (author_name,author_mail)
                        references author(name,mail) );''')

        # TODO : chercher à voir si l'on trouve des données
        # ajout/suppressions de lignes sur le commit pour un fichier défini.
Inkhey's avatar
Inkhey committed
88
        cmds.append('''CREATE TABLE link_file_commits(
Inkhey's avatar
Inkhey committed
89 90 91 92
                    file text,
                    dir text,
                    commits text,
                    foreign key (file,dir)
inkhey's avatar
inkhey committed
93
                        references file(name,dir),
Inkhey's avatar
Inkhey committed
94 95
                    foreign key (commits)
                        references commits(hash));''')
inkhey's avatar
inkhey committed
96 97

        # save last and first commit date for file and author
Inkhey's avatar
Inkhey committed
98
        cmds.append('''CREATE TABLE date_file(
inkhey's avatar
inkhey committed
99 100 101 102 103 104 105 106
                    file text,
                    dir text,
                    deb_date integer,
                    fin_date integer,
                    foreign key (file,dir)
                        references file(name,dir)
                    );''')

Inkhey's avatar
Inkhey committed
107
        cmds.append('''CREATE TABLE date_author(
inkhey's avatar
inkhey committed
108 109 110 111 112 113 114
                    name text,
                    email text,
                    deb_date integer,
                    fin_date integer,
                    foreign key (name,email)
                        references author(name,email)
                    );''')
Inkhey's avatar
Inkhey committed
115 116 117
        threads = [Thread(target=self._execCommit, args=(cmd,)) for cmd in cmds]
        [t.start() for t in threads]
        [t.join() for t in threads]
Inkhey's avatar
Inkhey committed
118 119
    # INSERT
    # DATA
Inkhey's avatar
Inkhey committed
120 121 122 123 124
    def _gInsert(self,l):
        for line in l:
            data = line.split('[;;|\/|;;]')
            yield (data)

Inkhey's avatar
Inkhey committed
125
    def _gInsertFile(self,depth):
Inkhey's avatar
Inkhey committed
126 127 128 129 130 131
        for root, dirs, files in filtered_walk(".", depth=depth):
            for fn in files:
                path = os.path.join(root, fn)
                size = os.path.getsize(path)
                data = (fn, size, root)
                yield(data)
Inkhey's avatar
Inkhey committed
132 133 134 135 136 137

            if( root.count("/") == depth):
                for d in dirs:
                    path = os.path.join(root, d)
                    size = self._get_size_dir(path)
                    data = (d, size, root)
inkhey's avatar
inkhey committed
138
                    yield(data)
Inkhey's avatar
Inkhey committed
139 140 141 142 143 144 145 146 147 148 149

    def _gInsertLFC(self,l):
        for row in l:
            path = os.path.join(row[0], row[1])
            cmd = 'git log --pretty=format:%H' + ' -- ' + path
            log = Popen(cmd.split(), stdout=PIPE)
            f = log.stdout.read().decode('utf-8', 'replace')
            dLinkF_C = f.splitlines()
            for commits in dLinkF_C:
                data = (row[1], row[0], commits)
                yield(data)
Inkhey's avatar
Inkhey committed
150

Inkhey's avatar
Inkhey committed
151 152
    def insert_all(self, path, depth):
        ''' insert all content and delete unused file'''
Inkhey's avatar
Inkhey committed
153
        os.chdir(path)
Inkhey's avatar
Inkhey committed
154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179
        t1 = Thread(target=self.insert_author())
        t2 = Thread(target=self.insert_commit())
        t3 = Thread(target=self.insert_file(depth))
        t4 = Thread(target=self.insert_link_A_C())
        t5 = Thread(target=self.insert_link_F_C())
        t6 = Thread(target=self.insert_dates_author())
        t7 = Thread(target=self.insert_dates_file())

        # insert data
        t1.start()
        t2.start()
        t3.start()
        t1.join()
        t2.join()
        # insert file is long, begin link A_C before
        t4.start()
        t3.join()
        #insert link and dates
        t5.start()
        # lac libéré,date auteurs possible
        t4.join()
        t6.start()
        t5.join()
        t7.start()
        t6.join()
        t7.join()
Inkhey's avatar
Inkhey committed
180 181
        self.delete_unused_file()

inkhey's avatar
inkhey committed
182
    @calctime
Inkhey's avatar
Inkhey committed
183
    def insert_author(self):
Inkhey's avatar
Inkhey committed
184
        ''' insert author data into author table'''
inkhey's avatar
inkhey committed
185

Inkhey's avatar
Inkhey committed
186
        log = Popen("git log --format=%aN[;;|\/|;;]%aE".split(), stdout=PIPE)
inkhey's avatar
inkhey committed
187 188
        sort = Popen("sort -u".split(), stdin=log.stdout, stdout=PIPE)
        log.stdout.close()
inkhey's avatar
inkhey committed
189
        f = sort.stdout.read().decode('utf-8', 'replace')
inkhey's avatar
inkhey committed
190
        dAuthor = f.splitlines()
Inkhey's avatar
Inkhey committed
191
        c = self.conn.cursor()
Inkhey's avatar
Inkhey committed
192 193 194 195
        g = self._gInsert(dAuthor)
        c.executemany(
            '''INSERT INTO author(name,email)
                    values (?,?);''',g)
Inkhey's avatar
Inkhey committed
196 197
        self.conn.commit()

inkhey's avatar
inkhey committed
198
    @calctime
Inkhey's avatar
Inkhey committed
199
    def insert_commit(self):
Inkhey's avatar
Inkhey committed
200
        ''' insert commit data into commits table'''
inkhey's avatar
inkhey committed
201
        log = Popen(
Inkhey's avatar
Inkhey committed
202
            "git log --format=%s[;;|\/|;;]%H[;;|\/|;;]%t[;;|\/|;;]%at".split(), stdout=PIPE)
inkhey's avatar
inkhey committed
203 204
        sort = Popen("sort -u".split(), stdin=log.stdout, stdout=PIPE)
        log.stdout.close()
inkhey's avatar
inkhey committed
205
        f = sort.stdout.read().decode('utf-8', 'replace')
inkhey's avatar
inkhey committed
206
        dCommit = f.splitlines()
Inkhey's avatar
Inkhey committed
207
        c = self.conn.cursor()
Inkhey's avatar
Inkhey committed
208 209 210 211
        g = self._gInsert(dCommit)
        c.executemany(
            '''INSERT INTO commits(subject,hash,abbrev_hash,date)
                    values (?,?,?,?);''', g)
Inkhey's avatar
Inkhey committed
212 213
        self.conn.commit()

inkhey's avatar
inkhey committed
214 215 216 217 218 219 220 221
    def _get_size_dir(self,path):
        total_size = 0
        for dirpath, dirnames, filenames in os.walk(path):
            for f in filenames:
                fp = os.path.join(dirpath, f)
                total_size += os.path.getsize(fp)
        return total_size

inkhey's avatar
inkhey committed
222
    @calctime
inkhey's avatar
inkhey committed
223
    def insert_file(self,depth):
Inkhey's avatar
Inkhey committed
224 225 226 227
        ''' insert file data into file table, depth define how precise
        file data will be.'''
        c = self.conn.cursor()
        # TODO : gérer profondeur et ignorer fichier dans .git ?
Inkhey's avatar
Inkhey committed
228
        g = self._gInsertFile(depth)
Inkhey's avatar
Inkhey committed
229 230 231
        c.executemany(
            '''INSERT INTO file(name,size,dir)
                            values (?,?,?);''', g)
Inkhey's avatar
Inkhey committed
232 233
        self.conn.commit()

inkhey's avatar
inkhey committed
234
        # LINKS
inkhey's avatar
inkhey committed
235
    @calctime
Inkhey's avatar
Inkhey committed
236
    def insert_link_A_C(self):
Inkhey's avatar
Inkhey committed
237 238 239
        ''' insert link data between author and commit into
        link_author_commits table'''

inkhey's avatar
inkhey committed
240
        log = Popen(
Inkhey's avatar
Inkhey committed
241
            'git log --pretty=format:%aN[;;|\/|;;]%aE[;;|\/|;;]%H'.split(), stdout=PIPE)
inkhey's avatar
inkhey committed
242
        f = log.stdout.read().decode('utf-8', 'replace')
inkhey's avatar
inkhey committed
243
        dLinkC_A = f.splitlines()
Inkhey's avatar
Inkhey committed
244
        c = self.conn.cursor()
Inkhey's avatar
Inkhey committed
245 246 247 248 249
        g = self._gInsert(dLinkC_A)
        c.executemany(
            '''INSERT INTO link_author_commits(author_name,
                                           author_mail,commits)
                    values (?,?,?);''', g)
Inkhey's avatar
Inkhey committed
250 251
        self.conn.commit()

inkhey's avatar
inkhey committed
252
    @calctime
Inkhey's avatar
Inkhey committed
253
    def insert_link_F_C(self):
Inkhey's avatar
Inkhey committed
254 255 256 257 258
        ''' insert link data between file and commit into
        link_author_commits table'''

        # TODO : utiliser git show ?
        c = self.conn.cursor()
Inkhey's avatar
Inkhey committed
259
        c.execute("SELECT dir,name FROM file;")
Inkhey's avatar
Inkhey committed
260 261 262 263
        g = self._gInsertLFC(c.fetchall())
        c.executemany(
            '''INSERT INTO link_file_commits(file,dir,commits)
                            values (?,?,?);''', g)
Inkhey's avatar
Inkhey committed
264 265
        self.conn.commit()

inkhey's avatar
inkhey committed
266
    @calctime
Inkhey's avatar
Inkhey committed
267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283
    def insert_dates_file(self):
        cmd='''INSERT INTO date_file(deb_date,fin_date,file,dir)
            Select min(c.date),max(c.date),lfc.file,lfc.dir
            from link_file_commits lfc join commits c
            on c.hash=lfc.commits
            group by lfc.file,lfc.dir;'''
        self._execCommit(cmd)

    @calctime
    def insert_dates_author(self):
        cmd='''INSERT INTO date_author(deb_date,fin_date,name,email)
            Select min(c.date),max(c.date),lac.author_name,lac.author_mail
            from link_author_commits lac join commits c
            on c.hash=lac.commits
            group by lac.author_name,lac.author_mail;'''
        self._execCommit(cmd)

inkhey's avatar
inkhey committed
284

Inkhey's avatar
Inkhey committed
285
    def _execCommit(self,cmd):
inkhey's avatar
inkhey committed
286
        c = self.conn.cursor()
Inkhey's avatar
Inkhey committed
287
        c.execute(cmd)
inkhey's avatar
inkhey committed
288 289
        self.conn.commit()

Inkhey's avatar
Inkhey committed
290

inkhey's avatar
inkhey committed
291
    @calctime
Inkhey's avatar
Inkhey committed
292
    def view_3nodestype(self):
Inkhey's avatar
Inkhey committed
293
        '''View with 3 types of nodes : author,commit,file'''
Inkhey's avatar
Inkhey committed
294 295
        cmds=[]
        cmds.append(
296
            '''create table nodes as
inkhey's avatar
inkhey committed
297 298 299
        select f.dir || f.name as id ,
               f.size as taille,
               f.dir as dir,
Inkhey's avatar
Inkhey committed
300
               "" as name,
Inkhey's avatar
Inkhey committed
301
               "" as email,
Inkhey's avatar
Inkhey committed
302 303
               "" as subject,
               "" as abbrev_hash,
inkhey's avatar
inkhey committed
304 305 306 307 308 309 310
               datetime(df.deb_date, 'unixepoch') as deb_date,
               datetime(df.fin_date,'unixepoch') as fin_date,
               "fichier" as type,
               f.dir || f.name as label
        from file f join date_file df
        where f.dir=df.dir
        and f.name=df.file
Inkhey's avatar
Inkhey committed
311
        union
inkhey's avatar
inkhey committed
312
        select a.name || a.email as id,
Inkhey's avatar
Inkhey committed
313 314
               "" as taille,
               "" as dir,
inkhey's avatar
inkhey committed
315 316
               a.name as name,
               a.email as email,
Inkhey's avatar
Inkhey committed
317 318
               "" as subject,
               "" as abbrev_hash,
inkhey's avatar
inkhey committed
319 320
               datetime(da.deb_date, 'unixepoch') as deb_date,
               datetime(da.fin_date,'unixepoch') as fin_date,
Inkhey's avatar
Inkhey committed
321
                "author" as type,
inkhey's avatar
inkhey committed
322 323 324 325
                a.name as label
        from author a join date_author da
        where a.email=da.email
        and a.name=da.name
Inkhey's avatar
Inkhey committed
326 327 328 329 330
        union
        select hash as id,
               "" as taille,
               "" as dir,
               "" as name,
Inkhey's avatar
Inkhey committed
331
               "" as email,
Inkhey's avatar
Inkhey committed
332 333
               subject as subject,
               abbrev_hash as abbrev_hash,
inkhey's avatar
inkhey committed
334 335
               datetime(date, 'unixepoch') as date_deb,
               datetime(date, 'unixepoch') as date_fin,
Inkhey's avatar
Inkhey committed
336 337
               "commit" as type,
               abbrev_hash as label
Inkhey's avatar
Inkhey committed
338 339
        from commits;
        ''')
Inkhey's avatar
Inkhey committed
340
        cmds.append(
341
            '''create table edges as
Inkhey's avatar
Inkhey committed
342
        select author_name || author_mail as source,
Inkhey's avatar
Inkhey committed
343 344 345 346 347
               commits as target,
               author_name,
               "" as dir
        from link_author_commits
        union
Inkhey's avatar
Inkhey committed
348
        select dir || file as source,
Inkhey's avatar
Inkhey committed
349 350 351 352 353
               commits as target,
               "" as author_name,
               dir
        from link_file_commits;
        ''')
Inkhey's avatar
Inkhey committed
354 355 356
        threads = [Thread(target=self._execCommit, args=(cmd,)) for cmd in cmds]
        [t.start() for t in threads]
        [t.join() for t in threads]
Inkhey's avatar
Inkhey committed
357

inkhey's avatar
inkhey committed
358
    @calctime
Inkhey's avatar
Inkhey committed
359
    def view_2nodestype(self):
Inkhey's avatar
Inkhey committed
360
        '''View with 2 types of nodes : author,file'''
Inkhey's avatar
Inkhey committed
361 362
        cmds=[]
        cmds.append('''create table nodes as
inkhey's avatar
inkhey committed
363 364 365
        select f.dir || f.name as id ,
               f.size as taille,
               f.dir as dir,
Inkhey's avatar
Inkhey committed
366 367
               "" as name,
               "" as email,
inkhey's avatar
inkhey committed
368 369 370 371 372 373 374
               datetime(df.deb_date, 'unixepoch') as deb_date,
               datetime(df.fin_date,'unixepoch') as fin_date,
               "fichier" as type,
               f.dir || f.name as label
        from file f join date_file df
        where f.dir=df.dir
        and f.name=df.file
Inkhey's avatar
Inkhey committed
375
        union
inkhey's avatar
inkhey committed
376
        select a.name || a.email as id,
Inkhey's avatar
Inkhey committed
377 378
               "" as taille,
               "" as dir,
inkhey's avatar
inkhey committed
379 380 381 382
               a.name as name,
               a.email as email,
               datetime(da.deb_date, 'unixepoch') as deb_date,
               datetime(da.fin_date,'unixepoch') as fin_date,
Inkhey's avatar
Inkhey committed
383
                "author" as type,
inkhey's avatar
inkhey committed
384 385 386 387
                a.name as label
        from author a join date_author da
        where a.email=da.email
        and a.name=da.name''')
Inkhey's avatar
Inkhey committed
388

Inkhey's avatar
Inkhey committed
389
        cmds.append(''' create table edges as
Inkhey's avatar
Inkhey committed
390 391 392 393
        select
            lac.author_name || lac.author_mail as source,
            lfc.dir || lfc.file as target,
            count(distinct(c.hash)) as nb_commit,
Inkhey's avatar
weight  
Inkhey committed
394
            count(distinct(c.hash)) as weight,
inkhey's avatar
inkhey committed
395 396
            datetime(min(c.date),'unixepoch') as date_first_commit,
            datetime(max(c.date),'unixepoch') as date_last_commit
Inkhey's avatar
Inkhey committed
397 398 399 400 401 402 403 404 405 406
        from
        link_author_commits lac
        join
        commits c
        on lac.commits = c.hash
        join
        link_file_commits lfc
        on c.hash = lfc.commits
        group by lac.author_name || lac.author_mail,lfc.dir || lfc.file;
        ''')
Inkhey's avatar
Inkhey committed
407 408 409
        threads = [Thread(target=self._execCommit, args=(cmd,)) for cmd in cmds]
        [t.start() for t in threads]
        [t.join() for t in threads]
Inkhey's avatar
Inkhey committed
410

inkhey's avatar
inkhey committed
411
    @calctime
Inkhey's avatar
Inkhey committed
412
    def view_files(self):
Inkhey's avatar
Inkhey committed
413
        '''View of relation between files'''
Inkhey's avatar
Inkhey committed
414
        sNodes='''create table nodes as
inkhey's avatar
inkhey committed
415 416 417 418 419 420 421 422
        select f.dir || f.name as id ,
               f.size as taille,
               f.dir as dir,
               datetime(df.deb_date, 'unixepoch') as deb_date,
               datetime(df.fin_date,'unixepoch') as fin_date,
               f.dir || f.name as label
        from file f join date_file df
        where f.dir=df.dir
Inkhey's avatar
Inkhey committed
423
        and f.name=df.file;'''
Inkhey's avatar
Inkhey committed
424
        #tmp table to gain time
Inkhey's avatar
Inkhey committed
425
        sTmp='''
Inkhey's avatar
Inkhey committed
426 427 428 429 430 431
        create table tmp as
        select
        a.author_name || a.author_mail as author,
        f.dir || f.file as file
        from link_file_commits f, link_author_commits a
        where ( f.commits=a.commits);
Inkhey's avatar
Inkhey committed
432 433 434
        '''

        sEdge='''
435
        create table edges as
Inkhey's avatar
Inkhey committed
436 437 438
        select f1.file AS source , f2.file  AS target,
        count(distinct(f1.author)) as nbAuthors,
        count(distinct(f1.author)) as weight
Inkhey's avatar
Inkhey committed
439 440 441 442 443 444
        from(
            tmp as f1
            join
            tmp as f2
            on f1.author = f2.author
            and f1.file != f2.file
Inkhey's avatar
Inkhey committed
445
        )
Inkhey's avatar
Inkhey committed
446 447 448 449 450 451 452 453 454 455
        group by  f1.file , f2.file;'''
        t1 = Thread(target=self._execCommit, args=(sNodes,))
        t2 = Thread(target=self._execCommit, args=(sTmp,))
        t3 = Thread(target=self._execCommit, args=(sEdges,))
        t1.start()
        t2.start()
        t2.join()
        t3.start()
        t3.join()
        t1.join()
Inkhey's avatar
Inkhey committed
456

inkhey's avatar
inkhey committed
457
    @calctime
Inkhey's avatar
Inkhey committed
458
    def view_authors(self):
Inkhey's avatar
Inkhey committed
459
        '''View of relation between authors'''
Inkhey's avatar
Inkhey committed
460
        sNodes='''
461
        create table nodes as
inkhey's avatar
inkhey committed
462 463 464 465 466 467 468 469
               select distinct( a.name || a.email) as id,
               a.name as name,
               a.email as email,
               a.name as label,
               datetime(da.deb_date, 'unixepoch') as deb_date,
               datetime(da.fin_date,'unixepoch') as fin_date
        from author a join date_author da
        where a.email=da.email
Inkhey's avatar
Inkhey committed
470
        and a.name=da.name;'''
Inkhey's avatar
Inkhey committed
471

Inkhey's avatar
Inkhey committed
472
        sTmp='''
Inkhey's avatar
Inkhey committed
473 474 475 476 477 478
        create table tmp as
        select
            a.author_name || a.author_mail as author,
            f.dir || f.file as file
            from link_file_commits f, link_author_commits a
            where ( f.commits=a.commits);
Inkhey's avatar
Inkhey committed
479 480 481
        '''

        sEdges='''
482
        create table edges as
Inkhey's avatar
Inkhey committed
483 484 485 486 487 488
        select a1.author AS source , a2.author AS target,
        count(a1.file) as nbfile,
        count(a1.file) as weight,
        a1.file as label
        from
        (
Inkhey's avatar
Inkhey committed
489
            tmp as a1
Inkhey's avatar
Inkhey committed
490
            join
Inkhey's avatar
Inkhey committed
491
            tmp as a2
Inkhey's avatar
Inkhey committed
492 493 494 495
            on a1.file = a2.file
            and a1.author != a2.author
        )
        group by a1.author,a2.author
Inkhey's avatar
Inkhey committed
496 497 498 499 500 501 502 503 504 505
        ;'''
        t1 = Thread(target=self._execCommit, args=(sNodes,))
        t2 = Thread(target=self._execCommit, args=(sTmp,))
        t3 = Thread(target=self._execCommit, args=(sEdges,))
        t1.start()
        t2.start()
        t2.join()
        t3.start()
        t3.join()
        t1.join()
inkhey's avatar
inkhey committed
506 507
    @calctime
    def view_file_commits(self):
Inkhey's avatar
Inkhey committed
508 509
        pass

inkhey's avatar
inkhey committed
510
    @calctime
Inkhey's avatar
Inkhey committed
511
    def view_authors_commits():
Inkhey's avatar
Inkhey committed
512
        pass
Inkhey's avatar
Inkhey committed
513
    # CLEAN
Inkhey's avatar
Inkhey committed
514

inkhey's avatar
inkhey committed
515
    @calctime
Inkhey's avatar
Inkhey committed
516
    def delete_unused_file(self):
Inkhey's avatar
Inkhey committed
517 518 519 520 521 522 523 524 525 526 527 528 529 530 531
        ''' delete unused file (who are not linked to any commit.)'''

        c = self.conn.cursor()
        c.execute(
            '''select f.name,f.dir
                from file  f left join link_file_commits l
                on f.name=l.file and f.dir=l.dir
                where l.commits is null;''')
        for row in c.fetchall():
            c.execute(
                '''DELETE from file
                where name="''' + row[0] +
                '" and dir="' + row[1] + '";')
        self.conn.commit()

inkhey's avatar
inkhey committed
532
    @calctime
Inkhey's avatar
Inkhey committed
533
    def clean_DB(self):
Inkhey's avatar
Inkhey committed
534 535 536 537
        ''' drop alls tables'''

        c = self.conn.cursor()
        listTable = ('author', 'commits', 'file', 'link_author_commits',
inkhey's avatar
inkhey committed
538
                     'link_file_commits','date_author','date_file')
Inkhey's avatar
Inkhey committed
539
        for table in listTable:
inkhey's avatar
inkhey committed
540
            c.execute('DROP TABLE IF EXISTS ' + table + ' ;')
Inkhey's avatar
Inkhey committed
541 542
        self.conn.commit()

inkhey's avatar
inkhey committed
543
    @calctime
Inkhey's avatar
Inkhey committed
544 545 546 547
    def dropView(self):
        ''' drop current view'''

        c = self.conn.cursor()
Inkhey's avatar
Inkhey committed
548
        listTable = ('nodes', 'edges','tmp')
Inkhey's avatar
Inkhey committed
549
        for table in listTable:
550
            c.execute('DROP TABLE IF EXISTS ' + table + ' ;')
Inkhey's avatar
Inkhey committed
551 552
        self.conn.commit()

Inkhey's avatar
Inkhey committed
553 554
# autotest
if __name__ == '__main__':
inkhey's avatar
inkhey committed
555
    ts = timer()
inkhey's avatar
inkhey committed
556
    print("Creating database…")
Inkhey's avatar
Inkhey committed
557
    db = sqlGit('self.db')
Inkhey's avatar
Inkhey committed
558 559
    db.clean_DB()
    db.dropView()
Inkhey's avatar
Inkhey committed
560
    db.create_table()
inkhey's avatar
inkhey committed
561 562
    print("inserting data…")
    db.insert_all('.', 0)
inkhey's avatar
inkhey committed
563
    print("Creating view…")
inkhey's avatar
inkhey committed
564 565 566
    db.view_authors()
    te = timer()
    print("database ready : %2.2f sec" % (te-ts))