commit 69f8ba0cd6b0f4bfd610a2cc9e42538d1288df3f
parent df0c1ce81abc948b05d2d1c8f16ad321fa4501e9
Author: Paco Esteban <paco@e1e0.net>
Date: Sun, 14 Mar 2021 20:34:17 +0100
reorganize base data and migration script
Diffstat:
| M | base_data.sql | | | 57 | ++++++++++++++++++++++++++++++++------------------------- |
| A | migration.py | | | 52 | ++++++++++++++++++++++++++++++++++++++++++++++++++++ |
2 files changed, 84 insertions(+), 25 deletions(-)
diff --git a/base_data.sql b/base_data.sql
@@ -1,38 +1,45 @@
INSERT INTO categories (id, name) VALUES (1, 'Resistors');
INSERT INTO categories (id, name) VALUES (2, 'Capacitors');
-INSERT INTO categories (id, name) VALUES (3, 'Inductors');
+INSERT INTO categories (id, name) VALUES (3, 'Inductors/Coils/Trsf');
INSERT INTO categories (id, name) VALUES (4, 'Crystalls');
INSERT INTO categories (id, name) VALUES (5, 'Diodes');
INSERT INTO categories (id, name) VALUES (6, 'LEDs');
-INSERT INTO categories (id, name) VALUES (7, 'Displays');
+INSERT INTO categories (id, name) VALUES (7, 'Optoelectronics');
INSERT INTO categories (id, name) VALUES (8, 'Connectors');
INSERT INTO categories (id, name) VALUES (9, 'Potentiometers');
INSERT INTO categories (id, name) VALUES (10, 'Encoders');
INSERT INTO categories (id, name) VALUES (11, 'Buttons');
INSERT INTO categories (id, name) VALUES (12, 'Switches');
-INSERT INTO categories (id, name) VALUES (13, 'MicroControllers');
-INSERT INTO categories (id, name) VALUES (14, 'Op-Amps');
-INSERT INTO categories (id, name) VALUES (15, 'Voltage Regulators');
-INSERT INTO categories (id, name) VALUES (16, 'Other ICs');
-INSERT INTO categories (id, name) VALUES (17, 'Motors');
+INSERT INTO categories (id, name) VALUES (13, 'Transistors');
+INSERT INTO categories (id, name) VALUES (14, 'MicroControllers');
+INSERT INTO categories (id, name) VALUES (15, 'Op-Amps');
+INSERT INTO categories (id, name) VALUES (16, 'Voltage Regulators');
+INSERT INTO categories (id, name) VALUES (17, 'Other ICs');
+INSERT INTO categories (id, name) VALUES (18, 'Motors and Fans');
+INSERT INTO categories (id, name) VALUES (19, 'Voltage References');
+INSERT INTO categories (id, name) VALUES (20, 'Relays');
+INSERT INTO categories (id, name) VALUES (21, 'Fuses');
+INSERT INTO categories (id, name) VALUES (22, 'Cables');
-- INSERT INTO categories (id, name) VALUES ();
-INSERT INTO storages (id, name) VALUES (1, 'Shelf-C1');
-INSERT INTO storages (id, name) VALUES (2, 'Shelf-C2');
-INSERT INTO storages (id, name) VALUES (3, 'Shelf-C3');
-INSERT INTO storages (id, name) VALUES (4, 'Shelf-C4');
-INSERT INTO storages (id, name) VALUES (5, 'Shelf-C5');
-INSERT INTO storages (id, name) VALUES (6, 'Shelf-C6');
-INSERT INTO storages (id, name) VALUES (7, 'Kallax-C1');
-INSERT INTO storages (id, name) VALUES (8, 'Kallax-C2');
-INSERT INTO storages (id, name) VALUES (9, 'Kallax-C3');
-INSERT INTO storages (id, name) VALUES (10, 'Kallax-C4');
-INSERT INTO storages (id, name) VALUES (11, 'Kallax-C5');
-INSERT INTO storages (id, name) VALUES (12, 'Kallax-C6');
-INSERT INTO storages (id, name) VALUES (13, 'Kallax-C7');
-INSERT INTO storages (id, name) VALUES (14, 'Kallax-C8');
-INSERT INTO storages (id, name) VALUES (15, 'Kallax-D1');
-INSERT INTO storages (id, name) VALUES (16, 'Kallax-D2');
-INSERT INTO storages (id, name) VALUES (17, 'Kallax-D3');
-INSERT INTO storages (id, name) VALUES (18, 'BigDrawer');
+INSERT INTO storages (id, name) VALUES (1, 'Shelf-CTop');
+INSERT INTO storages (id, name) VALUES (2, 'Shelf-C1');
+INSERT INTO storages (id, name) VALUES (3, 'Shelf-C2');
+INSERT INTO storages (id, name) VALUES (4, 'Shelf-C3');
+INSERT INTO storages (id, name) VALUES (5, 'Shelf-C4');
+INSERT INTO storages (id, name) VALUES (6, 'Shelf-C5');
+INSERT INTO storages (id, name) VALUES (7, 'Shelf-C6');
+INSERT INTO storages (id, name) VALUES (9, 'Kallax-Top');
+INSERT INTO storages (id, name) VALUES (10, 'Kallax-D1');
+INSERT INTO storages (id, name) VALUES (11, 'Kallax-D2');
+INSERT INTO storages (id, name) VALUES (12, 'Kallax-D3');
+INSERT INTO storages (id, name) VALUES (13, 'Kallax-C1');
+INSERT INTO storages (id, name) VALUES (14, 'Kallax-C2');
+INSERT INTO storages (id, name) VALUES (15, 'Kallax-C3');
+INSERT INTO storages (id, name) VALUES (16, 'Kallax-C4');
+INSERT INTO storages (id, name) VALUES (17, 'Kallax-C5');
+INSERT INTO storages (id, name) VALUES (18, 'Kallax-C6');
+INSERT INTO storages (id, name) VALUES (19, 'Kallax-C7');
+INSERT INTO storages (id, name) VALUES (20, 'Kallax-C8');
+INSERT INTO storages (id, name) VALUES (29, 'BigDrawer');
-- INSERT INTO storages (id, name) VALUES ();
diff --git a/migration.py b/migration.py
@@ -0,0 +1,52 @@
+#!/usr/bin/env python3
+# -*- coding: utf-8 -*-
+# vim:fenc=utf-8
+
+import pymysql
+import pymysql.cursors
+
+
+OUT_QUERY = """
+ select p.id,p.name,p.description,p.comment,p.stockLevel,p.status,f.name as footprint
+ from (Part as p, Footprint as f)
+ inner join Footprint
+ on p.footprint_id = f.id
+ group by p.id limit 10;
+"""
+DOCS_QUERY = "select filename, mimetype, extension from PartAttachment where part_id = %s"
+CAT_MATCH = {
+ 13: [10]+list(range(14, 19+1)),
+ 1: list(range(20,25+1))+[55],
+ 2: [26]+list(range(29,35+1)),
+ 4: [27],
+ 9: [28],
+ 3: [36],
+ 8: list(range(37,40+1)),
+ 15: [41],
+ 16: [42],
+ 17: [44],
+ 5: list(range(45,48+1)),
+ 14: [54],
+ 18: [53],
+ 19: [43],
+ 12: [50],
+ 20: [49],
+ 21: [51],
+ 7: [8],
+ 22: [58]
+}
+
+db = pymysql.connect(host="localhost", user="pk", password="pk_pwd", database="pk",
+ cursorclass=pymysql.cursors.DictCursor)
+cur = db.cursor()
+
+cur.execute(OUT_QUERY)
+
+for row in cur.fetchall():
+ cur.execute(DOCS_QUERY, (row['id'],))
+ # foo = [x for (x) in CAT_MATCH.items() if 14 in v]
+ docs = cur.fetchone()
+ if docs:
+ pass
+
+db.close()
