


-- 


CREATE TABLE `country` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);
CREATE INDEX country_id_IDX USING BTREE ON country (id);

CREATE TABLE `town` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);
CREATE INDEX town_id_IDX USING BTREE ON town (id);



CREATE TABLE `ticket` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `town_id` int(11) DEFAULT NULL,
  `country_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_ticket_town1` (`town_id`),
  KEY `fk_ticket_country1_idx` (`country_id`),
  CONSTRAINT `fk_ticket_country1_idx` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`),
  CONSTRAINT `fk_ticket_town1` FOREIGN KEY (`town_id`) REFERENCES `town` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX ticket_id_IDX USING BTREE ON ticket (id);


CREATE TABLE `opened_ticket` (
  `id` int(11) NOT NULL,
  `town_id` int(11) DEFAULT NULL,
  `country_id` int(11) DEFAULT NULL,
  `nb1` int(11) DEFAULT NULL,
  `nb2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_opened_ticket_town1` (`town_id`),
  KEY `fk_opened_ticket_country1_idx` (`country_id`),
  CONSTRAINT `fk_opened_ticket_ticket1_idx` FOREIGN KEY (`id`) REFERENCES `ticket` (`id`),
  CONSTRAINT `fk_opened_ticket_country1_idx` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`),
  CONSTRAINT `fk_opened_ticket_town1` FOREIGN KEY (`town_id`) REFERENCES `town` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX opened_ticket_town_id_IDX USING BTREE ON opened_ticket (town_id,country_id);


-- generate test data
-- -----------------
insert into country(id)
SELECT seq FROM seq_1_to_300;

insert into town(id)
SELECT seq FROM seq_1_to_20000;

insert into ticket(id,country_id,town_id)
SELECT seq,RAND()*258+1,RAND()*19999+1 FROM seq_1_to_3000000;

insert into ticket(country_id,town_id)
SELECT 186,RAND()*19999+1 FROM seq_1_to_857690;

insert into opened_ticket()
select id, town_id,country_id ,RAND()*300,RAND()*300 from ticket where id % 5 = 0 and country_id != 186;


insert into opened_ticket()
select id, town_id,country_id ,RAND()*300,RAND()*300 from ticket where id % 5 = 0 and country_id = 186;



commit;


