summaryrefslogtreecommitdiffstats
path: root/upgrade/r4132_reglog2db.py
blob: b5e47e8fb7a72d96f9dc57dcf313fe0b3da87dd3 (plain) (blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
#!/usr/bin/env python
#
# parse register.log and create reglog.db
#
# please first test and fix your register.log, then 
# try writing to database.
# 
# Create: piaip

# python 2.5:
#import sqlite3
# python 2.4 or prior:
from pysqlite2 import dbapi2 as sqlite3

import sys
import time

def initdb(dbfn) :
    db = sqlite3.connect(dbfn)
    c = db.cursor()
    # separate the tables to 'approved' and 'rejected',
    # since the 'rejected' is rarely used...
    c.execute('create table if not exists approved ' +
            '(uid text, name text, career text, ' +
            ' addr text, phone text, date integer, approved text)');
    c.execute('create table if not exists rejected ' +
            '(uid text, name text, career text, ' +
            ' addr text, phone text, date integer, rejected text)');
    return (db, c)

def processblk(m) :
    #print "process blk!"
    if not m.has_key('uid') :
        print "no uid record at line: ", lineno
        sys.exit(-1)
    if (not m.has_key('approved')) and (not m.has_key('rejected')) :
        print "invalid record (action?) at line: ", lineno
        sys.exit(-1)
    if c != None :
        type = 'rejected';
        if m.has_key('approved') : type = 'approved';
        # write into database
        sql = 'insert into ' + type + ' values (?,?,?,?,?,?,?)';
        # follow the orders
        c.execute(sql, (m['uid'], m['name'], m['career'], m['addr'], m['phone'], m['date'], m[type]));

# default input
fn = "register.log" 
dbfn= ""
db = None
c = None
if len (sys.argv) < 1 :
    print "syntax:", sys.argv[0], "register.log [reglog.db]"
    sys.exit(0)
if len (sys.argv) > 1 :
    fn = sys.argv[1]
if len (sys.argv) > 2 :
    dbfn = sys.argv[2]

print "Opening ", fn, "..."
f = open(fn, "rt")

if dbfn != '' :
    (db, c) = initdb(dbfn)

m={}
lineno = 0
tickets = 0
for s in f :
    lineno = lineno +1
    # for each line...
    if s.startswith("num:")   : continue
    if s.startswith("email:") : continue
    if s.startswith("ident:") : continue
    if s.startswith("]") : continue
    if s.startswith("----")   :
            # ready to build new one
            processblk(m)
            m = {}
            tickets = tickets +1
            if tickets % 10000 == 0 :
                if db : db.commit()
                print tickets, "forms processed."
            continue
    v = s.split(': ', 1)
    if len(v) != 2 :
        print "error at line ", lineno
        sys.exit(-1)
    k = v[0].lower()
    if (m.has_key(k)) :
        print "key duplicated at line ", lineno
        sys.exit(-1)
    # convert timestamp
    v = v[1].strip()
    if (k == 'date') :
        # strip final weekday
        v = v.split(' ') # mdy HMS abrv
        if len(v) < 2 or len(v) > 3 :
            print "invalid date entry at line ", lineno
            sys.exit(-1)
        v = v[0] + ' ' + v[1]
        # build timestamp
        v = int(time.mktime(time.strptime(v, "%m/%d/%Y %H:%M:%S")))
    m[k] = v

f.close()

# vim:sw=4:expandtab