partdb/server.rb

#!/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