-
Notifications
You must be signed in to change notification settings - Fork 0
/
db_manage.py
129 lines (95 loc) · 2.96 KB
/
db_manage.py
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
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
import sqlite3
from contextlib import closing
DB_NAME = 'polyhedrons.db'
sqlite3.register_adapter(tuple, lambda x: ','.join(str(i) for i in x))
sqlite3.register_converter('INTTUPLE', lambda x: tuple(int(i) for i in x.split(b',')))
sqlite3.register_converter('FLOATTUPLE', lambda x: tuple(float(i) for i in x.split(b',')))
def _get_data(sql, name):
with closing(sqlite3.connect(DB_NAME, detect_types=sqlite3.PARSE_DECLTYPES)) as conn:
for r in conn.execute(sql, (name,)):
yield r[0]
def get_vertices(name):
vertices = [v for v in _get_data(SELECT_VERTEX, name)]
return vertices
def get_faces(name):
faces = [f for f in _get_data(SELECT_FACE, name)]
return faces
def get_items():
with closing(sqlite3.connect(DB_NAME)) as conn:
items = {r[0]: r[1] for r in conn.execute(SELECT_ITEMS)}
return items
def get_sub_items(prefix):
with closing(sqlite3.connect(DB_NAME)) as conn:
names = [r[0] for r in conn.execute(SELECT_SUB_ITEMS, (prefix + '%',))]
return names
def insert_data(sql, data):
with closing(sqlite3.connect(DB_NAME)) as conn:
conn.execute(PRAGMA_FOREIGN_KEY)
conn.executemany(sql, data)
conn.commit()
def create_tables():
with closing(sqlite3.connect(DB_NAME)) as conn:
conn.execute(PRAGMA_FOREIGN_KEY)
for sql in (CREATE_POLYHEDRONS_TABLE, CREATE_VERTICES_TABLE,
CREATE_FACES_TABLE, CREATE_ITEMS_TABLE):
conn.execute(sql)
conn.commit()
SELECT_FACE = '''
SELECT face
FROM faces as f
INNER JOIN polyhedrons AS p ON f.id = p.id
WHERE p.name = ?
ORDER BY f.row_num;
'''
SELECT_VERTEX = '''
SELECT vertex
FROM vertices as v
INNER JOIN polyhedrons AS p ON v.id = p.id
WHERE p.name = ?
ORDER BY v.row_num;
'''
SELECT_ITEMS = '''
SELECT name, id FROM items;
'''
SELECT_SUB_ITEMS = '''
SELECT name FROM polyhedrons
WHERE id like ?
'''
PRAGMA_FOREIGN_KEY = '''
PRAGMA foreign_keys = 1
'''
CREATE_ITEMS_TABLE = '''
CREATE TABLE IF NOT EXISTS items (
id TEXT PRIMARY KEY,
name TEXT UNIQUE
);
'''
CREATE_POLYHEDRONS_TABLE = '''
CREATE TABLE IF NOT EXISTS polyhedrons (
id TEXT PRIMARY KEY,
name TEXT
);
'''
CREATE_VERTICES_TABLE = '''
CREATE TABLE IF NOT EXISTS vertices (
id TEXT,
row_num INTEGER,
vertex FLOATTUPLE,
PRIMARY KEY(id, row_num),
FOREIGN KEY(id) REFERENCES polyhedrons(id)
);
'''
CREATE_FACES_TABLE = '''
CREATE TABLE IF NOT EXISTS faces (
id TEXT,
row_num INTEGER,
face INTTUPLE,
PRIMARY KEY(id, row_num),
FOREIGN KEY(id) REFERENCES polyhedrons(id)
);
'''
INSERT_POLYHEDRONS = 'INSERT INTO polyhedrons (id, name) VALUES (?, ?)'
INSERT_VERTICES = 'INSERT INTO vertices (id, row_num, vertex) VALUES (?, ?, ?)'
INSERT_FACES = 'INSERT INTO faces (id, row_num, face) VALUES (?, ?, ?)'
if __name__ == '__main__':
create_tables()