484 lines
16 KiB
Ruby
Executable File
484 lines
16 KiB
Ruby
Executable File
#!/usr/bin/env ruby
|
|
# encoding: utf-8
|
|
# ruby: 3.0.2
|
|
=begin
|
|
backend to query part database
|
|
|
|
Copyright (C) 2023 King Kévin <kingkevin@cuvoodoo.info>
|
|
SPDX-License-Identifier: GPL-3.0-or-later
|
|
|
|
to install sinatra
|
|
gem install sinatra puma
|
|
=end
|
|
require 'set'
|
|
require 'sqlite3'
|
|
require 'json'
|
|
require 'sinatra'
|
|
require 'uri'
|
|
require 'net/http'
|
|
require 'cgi'
|
|
|
|
DEBUG = false
|
|
# maximum number of parts returned
|
|
PARTS_LIMIT = 100
|
|
# database file
|
|
DB_PATH = "partdb.db"
|
|
raise "DB file #{DB_PATH} does not exist" unless File.file? DB_PATH
|
|
# folder name for served pages
|
|
PUBLIC = "public"
|
|
# folder name for part attachments (in PUBLIC)
|
|
ATTACHMENTS = "attachments"
|
|
# port for this service
|
|
PORT = 4245
|
|
|
|
# open server
|
|
configure do
|
|
if DEBUG then
|
|
set :show_exceptions, true
|
|
set :logging, true
|
|
else
|
|
set :show_exceptions, false
|
|
set :environment, :production
|
|
set :logging, false
|
|
end
|
|
set :protection, :except => :json_csrf
|
|
|
|
set :bind, 'localhost'
|
|
set :port, PORT
|
|
set :public_folder, "public"
|
|
set :static, true
|
|
end
|
|
|
|
before do
|
|
response.headers["Access-Control-Allow-Origin"] = "*"
|
|
response.headers["Access-Control-Allow-Headers"] = "Content-Type"
|
|
if request.request_method == 'OPTIONS'
|
|
response.headers["Access-Control-Allow-Methods"] = "GET,POST"
|
|
halt 200
|
|
end
|
|
|
|
# all replies are only JSON
|
|
content_type 'application/json'
|
|
# open database
|
|
@db = SQLite3::Database.new(DB_PATH)
|
|
@db.results_as_hash = true
|
|
end
|
|
|
|
after do
|
|
response.headers["Access-Control-Allow-Origin"] = "*"
|
|
response.headers["Access-Control-Allow-Headers"] = "Content-Type"
|
|
end
|
|
|
|
get '/' do
|
|
content_type 'text/html'
|
|
send_file File.join(settings.public_folder, 'index.html')
|
|
end
|
|
|
|
def get_part_by_id(id)
|
|
return nil unless id
|
|
statement = @db.prepare("SELECT part.id, part.name, part.description, part.details, part.datasheet, manufacturer.name AS manufacturer, part.mpn AS mpn, part.package, part.page AS page, part.family AS parent, p2.name AS family FROM part LEFT JOIN manufacturer ON manufacturer.id = part.manufacturer LEFT JOIN part AS p2 ON p2.id = part.family WHERE part.id = ?")
|
|
part = statement.execute(id).to_a[0]
|
|
return nil unless part
|
|
parent = get_part_by_id(part["parent"])
|
|
# merge parent info
|
|
if parent then
|
|
part.each do |k,v|
|
|
part[k] ||= parent[k]
|
|
end
|
|
end
|
|
# add distributors
|
|
part["distributors"] = []
|
|
statement = @db.prepare("SELECT distribution.sku AS sku, distributor.name AS name, distributor.product_page AS url FROM distribution LEFT JOIN distributor ON distributor.id = distribution.distributor WHERE distribution.part = ?")
|
|
statement.execute(id).each do |distribution|
|
|
distribution["url"].gsub!("%s", distribution["sku"]) if distribution["url"]
|
|
part["distributors"] << distribution
|
|
end
|
|
# add inventory
|
|
statement = @db.prepare("SELECT location.name AS location, inventory.quantity AS stock FROM inventory LEFT JOIN location ON location.id = inventory.location WHERE inventory.part = ? ORDER BY inventory.quantity DESC LIMIT 1")
|
|
inventory = statement.execute(id).to_a[0]
|
|
if inventory then
|
|
part["location"] = inventory["location"]
|
|
part["stock"] = inventory["stock"]
|
|
end
|
|
# add properties
|
|
part["properties"] = []
|
|
statement = @db.prepare("SELECT property.name AS name, properties.value AS value FROM properties JOIN property ON property.id = properties.property WHERE properties.part = ?")
|
|
statement.execute(id).each do |row|
|
|
part["properties"] << row
|
|
end
|
|
part["properties"] += parent["properties"] if parent
|
|
# add attachments
|
|
part["attachments"] = []
|
|
dir = PUBLIC + "/" + ATTACHMENTS + "/" + part["name"].gsub("/", "_")
|
|
if File.directory?(dir) then
|
|
Dir.entries(dir).each do |file|
|
|
path = dir + "/" + file
|
|
next unless File.file? path
|
|
part["attachments"] << ATTACHMENTS + "/" + part["name"].gsub("/", "_") + "/" + file
|
|
end
|
|
end
|
|
part["attachments"].sort!
|
|
part["attachments"] += parent["attachments"] if parent
|
|
# add components for assembly
|
|
part["components"] = []
|
|
statement = @db.prepare("SELECT part.name AS name, part.description AS description, assembly.quantity AS quantity FROM assembly JOIN part ON part.id = assembly.component WHERE assembly.assembled = ?")
|
|
statement.execute(id).each do |row|
|
|
part["components"] << row
|
|
end
|
|
part["components"] += parent["components"] if parent
|
|
# clean up
|
|
delete = ["parent"]
|
|
delete.each do |k|
|
|
part.delete k
|
|
end
|
|
return part
|
|
end
|
|
|
|
def get_part_by_name(name)
|
|
statement = @db.prepare("SELECT id FROM part WHERE part.name = ?")
|
|
id = statement.execute(name).to_a[0]
|
|
if id then
|
|
return get_part_by_id(id["id"])
|
|
else
|
|
return nil
|
|
end
|
|
end
|
|
|
|
get '/part/:name' do
|
|
part = get_part_by_name(params['name'])
|
|
halt 404 unless part
|
|
part.to_json
|
|
end
|
|
|
|
get '/part?' do
|
|
halt 404, "name required" unless params['name']
|
|
part = get_part_by_name(params['name'])
|
|
halt 404 unless part
|
|
part.to_json
|
|
end
|
|
|
|
# search in names, description, and category
|
|
def search(terms)
|
|
statements = []
|
|
statements << @db.prepare("SELECT id FROM part WHERE name LIKE ?")
|
|
statements << @db.prepare("SELECT id FROM part WHERE mpn LIKE ?")
|
|
statements << @db.prepare("SELECT id FROM part WHERE description LIKE ?")
|
|
statements << @db.prepare("SELECT properties.part AS id FROM properties JOIN property ON property.id = properties.property WHERE property.name = 'category' AND properties.value LIKE ?")
|
|
children = @db.prepare("SELECT id FROM part WHERE family = ?")
|
|
term_ids = []
|
|
terms.each do |term|
|
|
ids = Set.new
|
|
# OR term location
|
|
statements.each do |statement|
|
|
statement.execute("%#{term}%").each do |row|
|
|
ids << row["id"]
|
|
children.execute(row["id"]).each do |child|
|
|
ids << child["id"]
|
|
end
|
|
end
|
|
end
|
|
term_ids << ids
|
|
end
|
|
# AND terms
|
|
ids = term_ids.shift
|
|
term_ids.each do |term_id|
|
|
ids &= term_id
|
|
end
|
|
parts = ids.collect {|id| get_part_by_id(id)}
|
|
parts.compact!
|
|
parts = parts[0, PARTS_LIMIT]
|
|
parts.sort! {|x,y| x["name"] <=> y["name"]}
|
|
end
|
|
|
|
get '/search/:terms' do
|
|
halt 429 if $searching
|
|
terms = params['terms'].split(" ")
|
|
terms.keep_if {|term| term.length >= 3}
|
|
halt 400 if terms.empty?
|
|
$searching = true
|
|
parts = search(terms)
|
|
$searching = false
|
|
parts.to_json
|
|
end
|
|
|
|
get '/search?' do
|
|
halt 429 if $searching
|
|
halt 400, "terms needed" unless params['terms']
|
|
terms = params['terms'].split(" ")
|
|
terms.keep_if {|term| term.length >= 3}
|
|
halt 400 if terms.empty?
|
|
$searching = true
|
|
parts = search(terms)
|
|
$searching = false
|
|
parts.to_json
|
|
end
|
|
|
|
def delete_part(id)
|
|
# first delete all children
|
|
statement = @db.prepare("SELECT id FROM part WHERE family = ?")
|
|
statement.execute(id).each do |row|
|
|
puts "child: #{row['id']}"
|
|
delete_part(row['id'])
|
|
puts "deleted"
|
|
end
|
|
# delete all fields
|
|
statements = []
|
|
statements << @db.prepare("DELETE FROM properties WHERE part = ?")
|
|
statements << @db.prepare("DELETE FROM assembly WHERE assembled = ?")
|
|
statements << @db.prepare("DELETE FROM assembly WHERE component = ?")
|
|
statements << @db.prepare("DELETE FROM distribution WHERE part = ?")
|
|
statements << @db.prepare("DELETE FROM properties WHERE part = ?")
|
|
statements << @db.prepare("DELETE FROM inventory WHERE part = ?")
|
|
statements << @db.prepare("DELETE FROM part WHERE id = ?")
|
|
statements.each do |statement|
|
|
statement.execute(id)
|
|
end
|
|
end
|
|
|
|
get '/delete/:id' do
|
|
statement = @db.prepare("SELECT id FROM part WHERE id = ?")
|
|
result = statement.execute(params['id'])
|
|
halt 400 if result.to_a.empty?
|
|
delete_part(params['id'])
|
|
return 200
|
|
end
|
|
|
|
def add_part(part)
|
|
if part["id"] then
|
|
# ensure part to update exists
|
|
statement = @db.prepare("SELECT id FROM part WHERE id = ?")
|
|
raise ScriptError.new("id not valid") if statement.execute(part["id"]).to_a.empty?
|
|
else
|
|
# add new part
|
|
raise ScriptError.new("name required") unless part["name"] and part["name"].length > 0
|
|
statement = @db.prepare("SELECT id FROM part WHERE name = ?")
|
|
raise ScriptError.new("name already existing") unless statement.execute(part["name"]).to_a.empty?
|
|
insert = @db.prepare("INSERT INTO part (name) VALUES (?)");
|
|
insert.execute(part["name"])
|
|
part["id"] = statement.execute(part["name"]).to_a[0]["id"]
|
|
end
|
|
old_part = get_part_by_id(part["id"])
|
|
# update family
|
|
family = nil
|
|
field = "family"
|
|
if part[field] then
|
|
if part[field].empty? then
|
|
update = @db.prepare("UPDATE part SET #{field} = NULL WHERE id = ?")
|
|
update.execute(part["id"])
|
|
else
|
|
statement = @db.prepare("SELECT id FROM part WHERE name = ?")
|
|
family = statement.execute(part[field]).to_a
|
|
raise ScriptError.new("family not existing") if family.empty?
|
|
update = @db.prepare("UPDATE part SET #{field} = ? WHERE id = ?")
|
|
update.execute(family[0]["id"], part["id"])
|
|
family = get_part_by_id(family[0]["id"])
|
|
end
|
|
end
|
|
# update fields
|
|
fields_txt = ["name", "description", "details", "mpn", "package", "datasheet", "page"];
|
|
fields_txt.each do |field|
|
|
next unless part[field]
|
|
if family and family[field] == part[field] then
|
|
update = @db.prepare("UPDATE part SET #{field} = NULL WHERE id = ?")
|
|
update.execute(part["id"])
|
|
else
|
|
update = @db.prepare("UPDATE part SET #{field} = ? WHERE id = ?")
|
|
update.execute(part[field], part["id"])
|
|
end
|
|
end
|
|
# update manufacturer and package
|
|
field_ref = ["manufacturer"]
|
|
field_ref.each do |field|
|
|
next if family and family[field] == part[field]
|
|
if part[field] then
|
|
statement = @db.prepare("SELECT id FROM #{field} WHERE LOWER(name) = ?")
|
|
ref = statement.execute(part[field].downcase).to_a[0]
|
|
unless ref then
|
|
insert = @db.prepare("INSERT INTO #{field} (name) VALUES (?)");
|
|
insert.execute(part[field])
|
|
end
|
|
ref = statement.execute(part[field].downcase).to_a[0]
|
|
update = @db.prepare("UPDATE part SET #{field} = ? WHERE id = ?")
|
|
update.execute(ref["id"], part["id"])
|
|
else
|
|
update = @db.prepare("UPDATE part SET #{field} = NULL WHERE id = ?")
|
|
update.execute(part["id"])
|
|
end
|
|
end
|
|
# update inventory
|
|
field = "location"
|
|
part[field] = nil if part[field] and 0 == part[field].length
|
|
if part[field] then
|
|
delete = @db.prepare("DELETE FROM inventory WHERE part = ?")
|
|
delete.execute(part["id"])
|
|
statement = @db.prepare("SELECT id FROM #{field} WHERE LOWER(name) = ?")
|
|
ref = statement.execute(part[field].downcase).to_a[0]
|
|
unless ref then
|
|
insert = @db.prepare("INSERT INTO #{field} (name) VALUES (?)");
|
|
insert.execute(part[field])
|
|
end
|
|
ref = statement.execute(part[field].downcase).to_a[0]
|
|
statement = @db.prepare("SELECT id FROM inventory WHERE part = ? AND location = ?")
|
|
ref_inv = statement.execute(part["id"], ref["id"]).to_a[0]
|
|
unless ref_inv then
|
|
insert = @db.prepare("INSERT INTO inventory (part, location, quantity) VALUES (?,?,?)")
|
|
insert.execute(part["id"], ref["id"], part["stock"].to_i)
|
|
end
|
|
ref_inv = statement.execute(part["id"], ref["id"]).to_a[0]
|
|
update = @db.prepare("UPDATE inventory SET quantity = ? WHERE id = ?")
|
|
update.execute(part["stock"].to_i, ref_inv["id"])
|
|
else
|
|
delete = @db.prepare("DELETE FROM inventory WHERE part = ?")
|
|
delete.execute(part["id"])
|
|
end
|
|
# update distributors
|
|
field = "distributors"
|
|
part[field] = nil if part[field] and 0 == part[field].length
|
|
delete = @db.prepare("DELETE FROM distribution WHERE part = ?")
|
|
delete.execute(part["id"])
|
|
if part[field] then
|
|
part[field].each do |row|
|
|
next unless row["name"] and !row["name"].empty?
|
|
next unless row["sku"] and !row["sku"].empty?
|
|
statement = @db.prepare("SELECT id FROM distributor WHERE LOWER(name) = ?")
|
|
ref = statement.execute(row["name"].downcase).to_a[0]
|
|
unless ref then
|
|
insert = @db.prepare("INSERT INTO distributor (name) VALUES (?)");
|
|
insert.execute(row["name"])
|
|
ref = statement.execute(row["name"].downcase).to_a[0]
|
|
end
|
|
insert = @db.prepare("INSERT INTO distribution (distributor,part,sku) VALUES (?,?,?)")
|
|
insert.execute(ref["id"], part["id"], row["sku"])
|
|
end
|
|
end
|
|
# update properties
|
|
field = "properties"
|
|
if part[field] then
|
|
delete = @db.prepare("DELETE FROM properties WHERE part = ?")
|
|
delete.execute(part["id"])
|
|
part[field].each do |row|
|
|
next unless row["name"] and !row["name"].empty?
|
|
next unless row["value"] and !row["value"].empty?
|
|
next if family and family["properties"] and family["properties"].include?(row)
|
|
statement = @db.prepare("SELECT id FROM property WHERE LOWER(name) = ?")
|
|
ref = statement.execute(row["name"].downcase).to_a[0]
|
|
unless ref then
|
|
insert = @db.prepare("INSERT INTO property (name) VALUES (?)");
|
|
insert.execute(row["name"])
|
|
ref = statement.execute(row["name"].downcase).to_a[0]
|
|
end
|
|
insert = @db.prepare("INSERT INTO properties (property,part,value) VALUES (?,?,?)")
|
|
insert.execute(ref["id"], part["id"], row["value"])
|
|
end
|
|
end
|
|
# update components
|
|
field = "components"
|
|
if part[field] then
|
|
delete = @db.prepare("DELETE FROM assembly WHERE assembled = ?")
|
|
delete.execute(part["id"])
|
|
part[field].each do |row|
|
|
next unless row["name"] and !row["name"].empty?
|
|
next unless row["quantity"]
|
|
statement = @db.prepare("SELECT id FROM part WHERE LOWER(name) = ?")
|
|
ref = statement.execute(row["name"].downcase).to_a[0]
|
|
#raise ScriptError.new("component #{name} does not exist") unless ref
|
|
next unless ref
|
|
row["quantity"] ||= 0
|
|
insert = @db.prepare("INSERT INTO assembly (assembled,component,quantity) VALUES (?,?,?)");
|
|
insert.execute(part["id"], ref["id"], row["quantity"])
|
|
end
|
|
end
|
|
# update attachments
|
|
dir_old = PUBLIC + "/" + ATTACHMENTS + "/" + old_part["name"].gsub("/", "_")
|
|
dir_new = PUBLIC + "/" + ATTACHMENTS + "/" + part["name"].gsub("/", "_")
|
|
unless dir_old == dir_new then
|
|
File.rename(dir_old, dir_new)
|
|
end
|
|
end
|
|
|
|
post '/part' do
|
|
request.body.rewind
|
|
begin
|
|
part = JSON.parse(request.body.read)
|
|
rescue
|
|
halt 401, "not json"
|
|
end
|
|
puts part if DEBUG
|
|
begin
|
|
add_part(part)
|
|
rescue ScriptError => e
|
|
halt 401, e.message
|
|
end
|
|
return 200
|
|
end
|
|
|
|
get '/import/lcsc/:lcsc' do
|
|
halt 401 unless params['lcsc'] and params['lcsc'] =~ /^C\d+$/i
|
|
uri = URI("https://wmsc.lcsc.com/wmsc/product/detail?productCode=#{params['lcsc']}")
|
|
res = Net::HTTP.get_response(uri)
|
|
halt 401, "could not get part" unless res.is_a?(Net::HTTPSuccess)
|
|
json = JSON.parse(res.body)
|
|
#puts json
|
|
halt 401, "part not found" unless 200 == json["code"] and json["result"]
|
|
result = json["result"]
|
|
part = {}
|
|
part["name"] = result["productModel"]
|
|
part["mpn"] = result["productModel"]
|
|
part["description"] = result["productDescEn"]
|
|
part["details"] = result["productIntroEn"]
|
|
part["manufacturer"] = result["brandNameEn"]
|
|
part["package"] = result["encapStandard"]
|
|
part["distributors"] = [{"name" => "LCSC", "sku" => result["productCode"]}]
|
|
part["attachments"] = result["productImages"]
|
|
part["datasheet"] = result["pdfUrl"]
|
|
existing = get_part_by_name(part["name"])
|
|
halt 401, "part #{part['name']} already exists" if existing
|
|
begin
|
|
add_part(part)
|
|
rescue ScriptError => e
|
|
halt 401, e.message
|
|
end
|
|
i = 0
|
|
(part["attachments"] + [part["datasheet"]]).each do |attachment|
|
|
next unless attachment
|
|
file = attachment.split("/")[-1]
|
|
dir = PUBLIC + "/" + ATTACHMENTS + "/" + part["name"].gsub("/", "_")
|
|
path = "#{dir}/#{i}_#{file}"
|
|
i += 1
|
|
unless File.file?(path) then
|
|
uri = URI(attachment)
|
|
res = Net::HTTP.get_response(uri)
|
|
if (res.is_a?(Net::HTTPSuccess)) then
|
|
Dir.mkdir(dir) unless File.directory?(dir)
|
|
File.open(path, "wb") do |f|
|
|
f.write res.body
|
|
end
|
|
end
|
|
end
|
|
end
|
|
return 200, "#{part['name']} added"
|
|
end
|
|
|
|
get '/attach?' do
|
|
halt 400, "part name or id required" unless params['id'] or params['name']
|
|
halt 400, "attachement URL required" unless params['url']
|
|
statement = @db.prepare("SELECT id, name FROM part WHERE id = ? OR name = ?")
|
|
part = statement.execute(params['id'], params['name']).to_a[0]
|
|
halt 400, "unknown part" unless part
|
|
file = CGI.unescape(params['url']).split("/")[-1]
|
|
dir = PUBLIC + "/" + ATTACHMENTS + "/" + part["name"].gsub("/", "_")
|
|
path = "#{dir}/#{file}"
|
|
unless File.file?(path) then
|
|
uri = URI(params['url'])
|
|
res = Net::HTTP.get_response(uri)
|
|
if (res.is_a?(Net::HTTPSuccess)) then
|
|
Dir.mkdir(dir) unless File.directory?(dir)
|
|
File.open(path, "wb") do |f|
|
|
f.write res.body
|
|
end
|
|
else
|
|
halt 404, "download failed"
|
|
end
|
|
end
|
|
end
|