-- On 3308
drop table animals;
create or replace table animals (n int not null, genre char(32)) engine=myisam;
insert into animals values(0,'chat'),(0,'chien'),(0,'hamster'),(0,'bird'),(0,'fish');
select * from animals;
check table animals;

drop table lastname;
create or replace table lastname (name char(32)) engine=myisam;
delete from lastname;
insert into lastname values('None');
select * from lastname;
check table lastname;

-- On 3306
DROP TABLE anum;

delete from anum;
insert into anum values(0,'chat'),(0,'chien'),(0,'hamster'),(0,'bird'),(0,'fish');
select * from anum;
check table anum;

DROP TABLE lanm;

delete from lanm;
insert into lanm values('None');
select * from lanm;
check table lanm;

create or replace table pets (
id int not null,
name char(32),
sex int(1),
race char(32))
engine=connect table_type=CSV file_name='C:/Data/FMT/pets.csv' header=1;
select * from pets;
delete from pets;
check table pets;
repair table pets;

drop procedure do_update;
create or replace procedure do_update(in r char(32), in x int) language sql
  begin
    declare gr char(34);
    set gr = concat('''',r,'''');
    update anum set n = n + x where genre = gr;
  end;

drop procedure do_it;
create or replace procedure do_it(in m char(32)) language sql
  begin
    declare nm char(34);
    set nm = concat('''',m,'''');
    update lanm set name = nm;
  end;

set connect_xtrace=64;

drop trigger ibp;
create or replace trigger ibp before insert on pets
  for each row
    begin
      call do_update(new.race,1);
      call do_it(new.name);
    end;

drop trigger dbp;
create or replace trigger dbp before delete on pets
  for each row
    begin
      call do_update(old.race,-1);
    end;

drop trigger ubp;
create or replace trigger ubp before update on pets
  for each row
    begin
      if new.race != old.race
        then
          begin
            call do_update(old.race,-1);
            call do_update(new.race,1);
          end;
      end if;
    end;

set connect_xtrace='QUERY,STMT';
set connect_xtrace=99;

insert into pets values(1,'Choupy',1,'chat');
insert into pets values(2,'Biscotte',2,'chat');
insert into pets values(3,'Ficelle',2,'chien');
insert into pets values(4,'Buggy',1,'hamster');

insert into pets values(1,'Choupy',1,'chat'),(3,'Ficelle',2,'chien');
insert into pets values(2,'Biscotte',2,'chat'),(4,'Buggy',1,'hamster');
insert into pets values(5,'Medor',2,'chien'),(6,'Tarum',1,'chat');
insert into pets values(1,'Choupy',1,'chat'),(2,'Biscotte',2,'chat'),(3,'Ficelle',2,'chien'),(4,'Buggy',1,'hamster');
insert into pets values(7,'Coco',1,'bird');
insert into pets values(8,'Wanda',1,'fish');

delete from pets;
delete from pets where name = 'Buggy';
delete from pets where name = 'Coco';
delete from pets where name = 'Medor';
delete from pets where name = 'Tarum';
update pets set race = 'chien' where name = 'Biscotte';
update pets set race = 'chat' where name = 'Biscotte';
update pets set race = 'chien' where id = 1;

select * from anum;
select * from pets;
select * from lanm; 

-- Testing INSERT

-- On 3308
drop table surnames;
create table surnames (
n int not null,
name char(32));
select * from surnames;


delete from names;
select * from names;

create or replace procedure do_insert(in sx int) language sql
  begin
    declare nm char(34);
    FOR rec IN (SELECT id, name FROM pets WHERE sex = sx) DO
      set nm = concat('''',rec.name,'''');
      insert into names values(rec.id,nm);
      end for;
  end;

call do_insert(1);
call do_insert(2);
