1PRAGMA user_version=21; --keep in sync with pool.cpp
2
3DROP TABLE IF EXISTS "units";
4CREATE TABLE "units" (
5	'uuid'	TEXT NOT NULL UNIQUE,
6	'name'	TEXT NOT NULL,
7	'manufacturer'	TEXT NOT NULL,
8	'filename'	TEXT NOT NULL,
9	'pool_uuid'	TEXT NOT NULL,
10	'last_pool_uuid' TEXT NOT NULL,
11	PRIMARY KEY('uuid')
12);
13
14DROP TABLE IF EXISTS "entities";
15CREATE TABLE "entities" (
16	'uuid'	TEXT NOT NULL UNIQUE,
17	'name'	TEXT NOT NULL,
18	'manufacturer'	TEXT NOT NULL,
19	'n_gates'	INTEGER NOT NULL,
20	'prefix'	TEXT NOT NULL,
21	'filename'	TEXT NOT NULL,
22	'pool_uuid'	TEXT NOT NULL,
23	'last_pool_uuid' TEXT NOT NULL,
24	PRIMARY KEY('uuid')
25);
26
27DROP TABLE IF EXISTS "symbols";
28CREATE TABLE "symbols" (
29	'uuid'	TEXT NOT NULL UNIQUE,
30	'unit'	TEXT NOT NULL,
31	'name'	TEXT NOT NULL,
32	'filename'	TEXT NOT NULL,
33	'pool_uuid'	TEXT NOT NULL,
34	'last_pool_uuid' TEXT NOT NULL,
35	PRIMARY KEY('uuid')
36);
37
38DROP TABLE IF EXISTS "packages";
39CREATE TABLE "packages" (
40	'uuid'	TEXT NOT NULL UNIQUE,
41	'name'	TEXT NOT NULL,
42	'manufacturer'	TEXT NOT NULL,
43	'n_pads'	INTEGER NOT NULL,
44	'alternate_for'	TEXT NOT NULL,
45	'filename'	TEXT NOT NULL,
46	'pool_uuid'	TEXT NOT NULL,
47	'last_pool_uuid' TEXT NOT NULL,
48	PRIMARY KEY('uuid')
49);
50
51DROP TABLE IF EXISTS "models";
52CREATE TABLE "models" (
53	'package_uuid'		TEXT NOT NULL,
54	'model_uuid'		TEXT NOT NULL,
55	'model_filename'	TEXT NOT NULL
56);
57
58DROP TABLE IF EXISTS "parts";
59CREATE TABLE "parts" (
60	'uuid'	TEXT NOT NULL UNIQUE,
61	'MPN'	TEXT,
62	'manufacturer'	TEXT,
63	'entity'	TEXT NOT NULL,
64	'package'	TEXT NOT NULL,
65	'description'	TEXT NOT NULL,
66	'datasheet'	TEXT NOT NULL,
67	'parametric_table'	TEXT NOT NULL,
68	'base'	TEXT NOT NULL,
69	'filename'	TEXT,
70	'flag_base_part' BOOLEAN NOT NULL,
71	'pool_uuid'	TEXT NOT NULL,
72	'last_pool_uuid' TEXT NOT NULL,
73	PRIMARY KEY('uuid')
74);
75
76DROP INDEX IF EXISTS part_mpn;
77CREATE INDEX part_mpn ON parts (MPN COLLATE naturalCompare ASC);
78
79DROP INDEX IF EXISTS part_manufacturer;
80CREATE INDEX part_manufacturer ON parts (manufacturer COLLATE naturalCompare ASC);
81
82DROP TABLE IF EXISTS "orderable_MPNs";
83CREATE TABLE "orderable_MPNs" (
84	'part'	TEXT NOT NULL,
85	'uuid'	TEXT NOT NULL,
86	'MPN'	TEXT,
87	PRIMARY KEY('part', 'uuid')
88);
89
90DROP TABLE IF EXISTS "tags";
91CREATE TABLE 'tags' (
92	'tag'	TEXT NOT NULL,
93	'uuid'	TEXT NOT NULL,
94	'type'	TEXT NOT NULL,
95	PRIMARY KEY('tag','uuid','type')
96);
97
98DROP INDEX IF EXISTS tag_tag;
99CREATE INDEX tag_tag ON tags (tag ASC);
100
101DROP INDEX IF EXISTS tag_uuid;
102CREATE INDEX tag_uuid ON tags (uuid ASC);
103
104DROP TABLE IF EXISTS "padstacks";
105CREATE TABLE "padstacks" (
106	'uuid'	TEXT NOT NULL UNIQUE,
107	'name'	TEXT NOT NULL,
108	'well_known_name'	TEXT NOT NULL,
109	'package'	TEXT NOT NULL,
110	'filename'	TEXT NOT NULL,
111	'type'	TEXT NOT NULL,
112	'pool_uuid'	TEXT NOT NULL,
113	'last_pool_uuid' TEXT NOT NULL,
114	PRIMARY KEY('uuid')
115);
116
117DROP TABLE IF EXISTS "frames";
118CREATE TABLE "frames" (
119	'uuid'	TEXT NOT NULL UNIQUE,
120	'name'	TEXT NOT NULL,
121	'filename'	TEXT NOT NULL,
122	'pool_uuid'	TEXT NOT NULL,
123	'last_pool_uuid' TEXT NOT NULL,
124	PRIMARY KEY('uuid')
125);
126
127DROP TABLE IF EXISTS "dependencies";
128CREATE TABLE "dependencies" (
129	'type'	TEXT NOT NULL,
130	'uuid'	TEXT NOT NULL,
131	'dep_type'	TEXT NOT NULL,
132	'dep_uuid'	TEXT NOT NULL,
133	UNIQUE('type', 'uuid', 'dep_type', 'dep_uuid') ON CONFLICT IGNORE
134);
135
136DROP INDEX IF EXISTS dependencies_dep;
137CREATE INDEX dependencies_dep ON dependencies (dep_uuid, dep_uuid);
138
139DROP TABLE IF EXISTS "decals";
140CREATE TABLE "decals" (
141	'uuid'	TEXT NOT NULL UNIQUE,
142	'name'	TEXT NOT NULL,
143	'filename'	TEXT NOT NULL,
144	'pool_uuid'	TEXT NOT NULL,
145	'last_pool_uuid' TEXT NOT NULL,
146	PRIMARY KEY('uuid')
147);
148
149DROP TABLE IF EXISTS "pools_included";
150CREATE TABLE "pools_included" (
151	'uuid'	TEXT NOT NULL UNIQUE,
152	'level'	INTEGER NOT NULL UNIQUE,
153	PRIMARY KEY('uuid')
154);
155
156DROP VIEW IF EXISTS "all_items_view";
157CREATE VIEW "all_items_view" AS
158	SELECT 'unit' AS 'type',     uuid AS uuid, filename AS filename, name AS name, pool_uuid AS pool_uuid, last_pool_uuid AS last_pool_uuid FROM units UNION ALL
159	SELECT 'symbol' AS 'type',   uuid AS uuid, filename AS filename, name AS name, pool_uuid AS pool_uuid, last_pool_uuid AS last_pool_uuid FROM symbols UNION ALL
160	SELECT 'entity' AS 'type',   uuid AS uuid, filename AS filename, name AS name, pool_uuid AS pool_uuid, last_pool_uuid AS last_pool_uuid FROM entities UNION ALL
161	SELECT 'padstack' AS 'type', uuid AS uuid, filename AS filename, name AS name, pool_uuid AS pool_uuid, last_pool_uuid AS last_pool_uuid FROM padstacks UNION ALL
162	SELECT 'package' AS 'type',  uuid AS uuid, filename AS filename, name AS name, pool_uuid AS pool_uuid, last_pool_uuid AS last_pool_uuid FROM packages UNION ALL
163	SELECT 'frame' AS 'type',    uuid AS uuid, filename AS filename, name AS name, pool_uuid AS pool_uuid, last_pool_uuid AS last_pool_uuid FROM frames UNION ALL
164	SELECT 'decal' AS 'type',    uuid AS uuid, filename AS filename, name AS name, pool_uuid AS pool_uuid, last_pool_uuid AS last_pool_uuid FROM decals UNION ALL
165	SELECT 'part' AS 'type',      uuid AS uuid, filename AS filename, MPN AS name, pool_uuid AS pool_uuid, last_pool_uuid AS last_pool_uuid FROM parts;
166
167DROP VIEW IF EXISTS "tags_view";
168CREATE VIEW "tags_view" AS
169	SELECT type as type, uuid as uuid, GROUP_CONCAT(tag, ' ') as tags from tags group by tags.uuid, tags.type;
170
171DROP TABLE IF EXISTS "last_updated";
172CREATE TABLE "last_updated" (
173	'time'	DATETIME NOT NULL UNIQUE
174);
175INSERT INTO last_updated VALUES ('2000-01-01');
176