create table customer (
	custid serial primary key not null,
	custname varchar(20)
);

create table part (
	partnum serial primary key,
	price decimal(10,2),
	quan_in_stock integer
);

create table partupdate (
	invnum  integer,
	partnum integer,
	quan_sold integer,
	constraint partupdate_invnum  foreign key (invnum) references invoice(invnum),
	constraint partupdate_partnum foreign key (partnum) references part(partnum)
);

-- this transaction updates all the part.quan_in_stock from the various partupdate.quan_sold.

begin;

with sums as 
(select p.partnum, sum(p.quan_sold) as numsold from partupdate p group by p.partnum)
update part p set quan_in_stock = quan_in_stock - (select s.numsold from sums s where s.partnum = p.partnum) from sums where p.partnum = sums.partnum;

delete from partupdate;
commit;

create table invoice (
	invnum serial primary key,
	ordertime timestamp,
	custid   integer not null,
	totalamt decimal(10,2),
        constraint invoice_custid foreign key (custid) references customer(custid)
);

-- MySQL version
create table invoice (
	invnum    serial primary key,
	ordertime timestamp,
	custid    bigint unsigned not null,
	totalamt  decimal(10,2),
        constraint invoice_custid foreign key (custid) references customer(custid)
);

create table invitem (
	invoicenum integer not null,
        partnum integer not null,
        quantity integer not null check (quantity >= 0),
        primary key (invoicenum, partnum),
        constraint invitem_invoice foreign key (invoicenum) references invoice (invnum),
        constraint invitem_partnum foreign key (partnum) references part(partnum)
);

-- MySQL version
create table invitem (
	invoicenum bigint unsigned not null,
        partnum    bigint unsigned not null,
        quantity   integer not null check (quantity >= 0),
        primary key (invoicenum, partnum),
        constraint invitem_invoice foreign key (invoicenum) references invoice (invnum),
        constraint invitem_partnum foreign key (partnum) references part(partnum)
);


-- postgres
Create or replace function random_string(length integer) returns text as
$$
declare
  chars text[] := '{a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
  result text := '';
  i integer := 0;
begin
  if length < 0 then
    raise exception 'Given length cannot be less than 0';
  end if;
  for i in 1..length loop
    result := result || chars[1+random()*(array_length(chars, 1)-1)];
  end loop;
  return result;
end;
$$ language plpgsql;

-- mysql
delimiter $$
Create function random_string(length integer) returns varchar(30) 
begin
declare chars varchar(30);
declare result varchar(30);
declare i integer;
declare pos integer;
  set chars = 'abcdefghijklmnopqrstuvwxyz';
  set result = '';
  set i = 1;
  while i <= length do
    set pos = 1+round(rand()*26);
    set result = concat(result, substring(chars, pos, 1));
    set i = i+1;
  end while;
  return result;
end;
$$
delimiter ;


insert into customer (custname) select random_string(6)                                              
FROM generate_series (1,2000) s(i);                                                   

mysql: 
load data local infile 'customer.csv' into table customer fields terminated by ',';
load data local infile 'part.csv' into table part fields terminated by ',';

insert into part (price, quan_in_stock)
select 10*random(), 1000
from generate_series(1,1000) s(i);


Given a list of (part,quan)  (p1,q1)...(pN,qN), and custid:

begin;
insert into invoice(ordertime,custid) values (now(), custid) returning invnum;
-- for each (p,q)
	insert into invitem values (invnum, p, q);
	update part set quan_in_stock = quan_in_stock - q where partnum = p;
if all good: commit;

-- really SLOOOW query:
select distinct pu.invnum from partupdate pu where (select count(*) from partupdate p where p.invnum = pu.invnum) <> 10 order by pu.invnum limit 200;

-- faster:
select pu.invnum from partupdate pu group by pu.invnum having count(*) <> 10 order by pu.invnum limit 200;

