iap_sample/bookshelf/code/resourceserver/create_scheme.sql

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

-- -----------------------------------------------------
-- 
-- DROP SCHEMA IF EXISTS bookstore ;
-- CREATE SCHEMA IF NOT EXISTS bookstore ;
-- USE bookstore ;
-- -----------------------------------------------------
USE izinin ; /* TODO: adjust to your environment */

DROP TABLE IF EXISTS product;
DROP TABLE IF EXISTS customer;
DROP TABLE IF EXISTS customer_product;
DROP PROCEDURE IF EXISTS addTicket;

-- -----------------------------------------------------
-- Table product
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS product (
  id INT NOT NULL AUTO_INCREMENT ,
  productid VARCHAR(10) NOT NULL ,
  name VARCHAR(16) NULL ,
  shortdescr VARCHAR(30) NULL ,
  longdescr VARCHAR(500) NULL ,
  url VARCHAR(500) not null ,
  PRIMARY KEY (id) ,
  UNIQUE INDEX productid_UNIQUE (productid ASC) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table customer
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS customer (
  id INT NOT NULL AUTO_INCREMENT ,
  accountdigest VARCHAR(300) NOT NULL ,
  PRIMARY KEY (id) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table customer_product
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS customer_product (
  customer_id INT NOT NULL ,
  product_id INT NOT NULL ,
  ticket VARCHAR(1500) NOT NULL ,
  PRIMARY KEY (customer_id, product_id) ,
  CONSTRAINT fk_customer_product_customer
    FOREIGN KEY (customer_id )
    REFERENCES customer (id )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT fk_customer_product_product
    FOREIGN KEY (product_id )
    REFERENCES product (id )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX customer_key ON customer_product (customer_id ASC) ;

CREATE INDEX product_key ON customer_product (product_id ASC) ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Data for table product
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO product (id, productid, name, shortdescr, longdescr, url) 
        VALUES (NULL, '588547', 'book#1', 'shortDescr#1', 'longDescr#1', '../products/book_0.txt');
INSERT INTO product (id, productid, name, shortdescr, longdescr, url)
        VALUES (NULL, '588549', 'book#2', 'shortDescr#2', 'longDescr#2', '../products/book_1.txt');
INSERT INTO product (id, productid, name, shortdescr, longdescr, url) 
        VALUES (NULL, '588550', 'book#3', 'shortDescr#3', 'longDescr#3', '../products/book_2.txt');
INSERT INTO product (id, productid, name, shortdescr, longdescr, url)
        VALUES (NULL, '588551', 'book#4', 'shortDescr#4', 'longDescr#4', '../products/book_3.txt');
COMMIT;

DELIMITER $$

CREATE PROCEDURE addTicket (in var_account VARCHAR(300), in var_ticket VARCHAR(1500), var_prodid VARCHAR(10))
BEGIN
  DECLARE var_customer INT DEFAULT -1;
  DECLARE var_product INT DEFAULT -1;
  SELECT id into var_customer from customer c where c.accountdigest = var_account LIMIT 1;
  SELECT id into var_product from product p where p.productid = var_prodid LIMIT 1;

  -- do nothing product if product id is incorrect (-1)
  if var_product != -1  and var_account != '' then
    if var_customer = -1 then
        insert into customer (accountdigest) values (var_account);
        SELECT LAST_INSERT_ID() into var_customer;
    end if;
      
    -- for duplicate handling there is use of 'replace' instead of 'insert ignore'
    -- if no record to replase mysql will insert new one
    REPLACE INTO customer_product (customer_id, product_id, ticket) values (var_customer, var_product, var_ticket);
  end if;

END$$

DELIMITER ;