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