commit 04f0866bea02ea8c4ace6d860ff8b092dae2d432
parent 47d6d147c08c11e1a64ad888841908c7bce075cd
Author: Paco Esteban <paco@e1e0.net>
Date: Sun, 8 Oct 2023 17:39:45 +0200
add basic edit part function
Diffstat:
3 files changed, 77 insertions(+), 3 deletions(-)
diff --git a/partsdb/database.py b/partsdb/database.py
@@ -51,6 +51,23 @@ INSERT_PART_QUERY = """
)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?)
"""
+UPDATE_PART_QUERY = """
+ UPDATE parts
+ SET
+ pn = ?,
+ mpn = ?,
+ manufacturer = ?,
+ description = ?,
+ specs = ?,
+ footprint = ?,
+ category_id = ?,
+ storage_id = ?,
+ quantity = ?,
+ part_type = ?,
+ update_date = datetime('now','localtime')
+ WHERE
+ id = ?
+"""
GET_PART_QUERY = """
SELECT p.*, c.name as cat, s.name as storage
FROM parts as p, categories as c, storages as s
@@ -103,11 +120,21 @@ class PartsDB:
c.execute(GET_CATEGORIES_QUERY)
return c.fetchall()
+ def get_category_by_name(self, cat_name):
+ c = self.conn.cursor()
+ c.execute(f"{GET_CATEGORIES_QUERY} WHERE name = ?", (cat_name,))
+ return c.fetchone()
+
def get_storages(self):
c = self.conn.cursor()
c.execute(GET_STORAGES_QUERY)
return c.fetchall()
+ def get_storage_by_name(self, sto_name):
+ c = self.conn.cursor()
+ c.execute(f"{GET_STORAGES_QUERY} WHERE name = ?", (sto_name,))
+ return c.fetchone()
+
def list_parts(self):
c = self.conn.cursor()
c.execute(LIST_PARTS_QUERY)
@@ -171,6 +198,26 @@ class PartsDB:
c.execute(DELETE_HISTORY_QUERY, (part_id,))
c.execute(DELETE_PART_QUERY, (part_id,))
+ def update_part(self, part):
+ with self.conn:
+ cat_id = self.get_category_by_name(part["cat"])["id"]
+ sto_id = self.get_storage_by_name(part["storage"])["id"]
+ c = self.conn.cursor()
+ update_tuple = (
+ part["pn"],
+ part["mpn"],
+ part["manufacturer"],
+ part["description"],
+ part["specs"],
+ part["footprint"],
+ cat_id,
+ sto_id,
+ part["quantity"],
+ part["part_type"],
+ part["id"],
+ )
+ c.execute(UPDATE_PART_QUERY, update_tuple)
+
def get_image(self, part_id):
c = self.conn.cursor()
c.execute(GET_IMAGE_QUERY, (part_id,))
diff --git a/partsdb/partsdb.py b/partsdb/partsdb.py
@@ -4,8 +4,10 @@
import argparse
import os
+import subprocess
import sys
import urllib.request
+import yaml
from partsdb import database as pdb
from partsdb import exports
@@ -171,6 +173,26 @@ def delete_part(part_id):
db.delete_part(part_id)
+def edit_part(part_id):
+ # load part into a tmp yaml
+ part = db.get_part(part_id)
+ part_tmp_file = f"/tmp/partsdb_edit_part{part_id}.yaml"
+ with open(part_tmp_file, mode="w+") as fp:
+ yaml.dump(dict(part), fp)
+
+ # call vim to edit, load the resulting yaml and update db
+ with open(part_tmp_file, mode="r") as fp:
+ subprocess.call(["nvim", fp.name])
+ try:
+ edited_part = yaml.safe_load(fp)
+ db.update_part(edited_part)
+ except yaml.YAMLError as exc:
+ print(exc)
+
+ # clean up
+ os.remove(part_tmp_file)
+
+
def adjust_stock(part_id, stock_mod, comment):
db.new_part_history_event(part_id, stock_mod, comment)
db.update_part_qty(part_id, stock_mod)
@@ -251,6 +273,9 @@ def main():
# delete
ap_delete = asp.add_parser("delete", help="Delete a part")
ap_delete.add_argument("part_id", help="Part Id", type=int)
+ # edit
+ ap_edit = asp.add_parser("edit", help="Edit a part")
+ ap_edit.add_argument("part_id", help="Part Id", type=int)
# stock
ap_stock = asp.add_parser("stock", help="Modifies a part stock")
ap_stock.add_argument("part_id", help="Part Id", type=int)
@@ -285,6 +310,8 @@ def main():
open_image(args.part_id)
elif args.command == "delete":
delete_part(args.part_id)
+ elif args.command == "edit":
+ edit_part(args.part_id)
elif args.command == "stock":
adjust_stock(args.part_id, args.stock_mod, args.comment)
elif args.command == "export":
diff --git a/schema.sql b/schema.sql
@@ -25,8 +25,8 @@ CREATE TABLE IF NOT EXISTS parts (
datasheet blob,
image blob,
part_type text,
- insert_date text NOT NULL DEFAULT CURRENT_TIMESTAMP,
- update_date text NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ insert_date text NOT NULL DEFAULT (datetime('now','localtime')),
+ update_date text NOT NULL DEFAULT (datetime('now','localtime')),
FOREIGN KEY (category_id) REFERENCES categories (id),
FOREIGN KEY (storage_id) REFERENCES storages (id)
);
@@ -35,7 +35,7 @@ CREATE TABLE IF NOT EXISTS parts (
CREATE TABLE IF NOT EXISTS parts_history (
id integer PRIMARY KEY,
part_id integer NOT NULL,
- insert_date text NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ insert_date text NOT NULL DEFAULT (datetime('now','localtime')),
movement integer NOT NULL,
mcomment text,
FOREIGN KEY (part_id) REFERENCES parts (id)
