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
|
PRAGMA page_size = 4096;
PRAGMA foreign_keys = ON;
CREATE TABLE featuretype (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
UNIQUE (name ASC));
CREATE TABLE sourcefile (
id INTEGER PRIMARY KEY,
filename TEXT NOT NULL,
UNIQUE (filename ASC));
CREATE TABLE machine (
id INTEGER PRIMARY KEY,
shortname TEXT NOT NULL,
description TEXT NOT NULL,
sourcefile INTEGER NOT NULL,
isdevice INTEGER NOT NULL,
runnable INTEGER NOT NULL,
UNIQUE (shortname ASC),
UNIQUE (description ASC),
FOREIGN KEY (sourcefile) REFERENCES sourcefile (id));
CREATE TABLE system (
id INTEGER PRIMARY KEY,
year TEXT NOT NULL,
manufacturer TEXT NOT NULL,
FOREIGN KEY (id) REFERENCES machine (id));
CREATE TABLE cloneof (
id INTEGER PRIMARY KEY,
parent TEXT NOT NULL,
FOREIGN KEY (id) REFERENCES machine (id));
CREATE TABLE romof (
id INTEGER PRIMARY KEY,
parent TEXT NOT NULL,
FOREIGN KEY (id) REFERENCES machine (id));
CREATE TABLE biosset (
id INTEGER PRIMARY KEY,
machine INTEGER NOT NULL,
name TEXT NOT NULL,
description TEXT NOT NULL,
UNIQUE (machine ASC, name ASC),
FOREIGN KEY (machine) REFERENCES machine (id));
CREATE TABLE biossetdefault (
id INTEGER PRIMARY KEY,
FOREIGN KEY (id) REFERENCES biosset (id));
CREATE TABLE devicereference (
id INTEGER PRIMARY KEY,
machine INTEGER NOT NULL,
device INTEGER NOT NULL,
UNIQUE (machine ASC, device ASC),
FOREIGN KEY (machine) REFERENCES machine (id),
FOREIGN KEY (device) REFERENCES machine (id));
CREATE TABLE dipswitch (
id INTEGER PRIMARY KEY,
machine INTEGER NOT NULL,
isconfig INTEGER NOT NULL,
name TEXT NOT NULL,
tag TEXT NOT NULL,
mask INTEGER NOT NULL,
--UNIQUE (machine ASC, tag ASC, mask ASC), not necessarily true, need to expose port conditions
FOREIGN KEY (machine) REFERENCES machine (id));
CREATE TABLE diplocation (
id INTEGER PRIMARY KEY,
dipswitch INTEGER NOT NULL,
bit INTEGER NOT NULL,
name TEXT NOT NULL,
num INTEGER NOT NULL,
inverted INTEGER NOT NULL,
UNIQUE (dipswitch ASC, bit ASC),
FOREIGN KEY (dipswitch) REFERENCES dipswitch (id));
CREATE TABLE dipvalue (
id INTEGER PRIMARY KEY,
dipswitch INTEGER NOT NULL,
name TEXT NOT NULL,
value INTEGER NOT NULL,
isdefault INTEGER NOT NULL,
FOREIGN KEY (dipswitch) REFERENCES dipswitch (id));
CREATE TABLE feature (
id INTEGER PRIMARY KEY,
machine INTEGER NOT NULL,
featuretype INTEGER NOT NULL,
status INTEGER NOT NULL,
overall INTEGER NOT NULL,
UNIQUE (machine ASC, featuretype ASC),
FOREIGN KEY (machine) REFERENCES machine (id),
FOREIGN KEY (featuretype) REFERENCES featuretype (id));
CREATE TABLE slot (
id INTEGER PRIMARY KEY,
machine INTEGER NOT NULL,
name TEXT NOT NULL,
UNIQUE (machine ASC, name ASC),
FOREIGN KEY (machine) REFERENCES machine (id));
CREATE TABLE slotoption (
id INTEGER PRIMARY KEY,
slot INTEGER NOT NULL,
device INTEGER NOT NULL,
name TEXT NOT NULL,
UNIQUE (slot ASC, name ASC),
FOREIGN KEY (slot) REFERENCES slot (id),
FOREIGN KEY (device) REFERENCES machine (id));
CREATE TABLE slotdefault (
id INTEGER PRIMARY KEY,
slotoption INTEGER NOT NULL,
FOREIGN KEY (id) REFERENCES slot (id),
FOREIGN KEY (slotoption) REFERENCES slotoption (id));
|