sql_scripts.txt

# CREATE TABLE FOR BOOKS
CREATE TABLE IF NOT EXISTS BOOK_STORE (ID INTEGER not null PRIMARY KEY desc, TITLE VARCHAR, COVER VARCHAR, IMAGE VARCHAR, ISBN VARCHAR, OLD_PRICE VARCHAR, PAGES VARCHAR, PRICE VARCHAR, PUBLISH_DATE VARCHAR, PUBLISHER VARCHAR, SUMMARY VARCHAR, TRANSLATOR VARCHAR, URL VARCHAR);

# CREATE BOOK SEQUENCE
CREATE SEQUENCE BOOK_STORE.BOOK_SEQUENCE;

#CREATE TABLE FOR AUTHORS
CREATE TABLE IF NOT EXISTS AUTHORS (ID INTEGER NOT NULL PRIMARY KEY DESC, NAME VARCHAR, SURNAME VARCHAR);

# CREATE AUTHORS SEQUENCE
CREATE SEQUENCE AUTHORS.AUTHORS_SEQUENCE;

#CREATE TABLE FOR CATEGORIES
CREATE TABLE IF NOT EXISTS CATEGORIES (ID INTEGER NOT NULL PRIMARY KEY DESC, CATEGORY VARCHAR);

# CREATE CATEGORIES SEQUENCE
CREATE SEQUENCE CATEGORIES.CATEGORIES_SEQUENCE;

#CREATE BOOK-AUTHOR TABLE
CREATE TABLE IF NOT EXISTS BOOK_AUTHOR (ID INTEGER NOT NULL PRIMARY KEY DESC, BOOK_ID INTEGER, AUTHOR_ID INTEGER);

# CREATE BOOK_AUTHOR SEQUENCE
CREATE SEQUENCE BOOK_AUTHOR.BOOK_AUTHOR_SEQUENCE;

#CREATE BOOK-CATEGORY TABLE
CREATE TABLE IF NOT EXISTS BOOK_CATEGORY (ID INTEGER NOT NULL PRIMARY KEY DESC, BOOK_ID INTEGER, CATEGORY_ID INTEGER);

# CREATE BOOK_CATEGORY SEQUENCE
CREATE SEQUENCE BOOK_CATEGORY.BOOK_CATEGORY_SEQUENCE;

#CREATE VISITOR TABLE
CREATE TABLE IF NOT EXISTS VISITOR (ID INTEGER NOT NULL PRIMARY KEY DESC, USER_AGENT VARCHAR, TIMEZONE INTEGER, HEADERS VARCHAR, SCREEN VARCHAR, ENABLED_COOKIES VARCHAR, PLUGINS VARCHAR, FONTS VARCHAR, MD5_ID VARCHAR, MD5_DATA VARCHAR);

# CREATE VISITOR SEQUENCE
CREATE SEQUENCE VISITOR.VISITOR_SEQUENCE;

#CREATE VISITOR LOGGER
CREATE TABLE IF NOT EXISTS VISITOR_LOG (ID INTEGER NOT NULL PRIMARY KEY DESC, IP_ADDRESS VARCHAR, LINK VARCHAR, TIMESTAMP BIGINT, MD5_ID VARCHAR, CART INTEGER);

# CREATE VISITOR_LOG SEQUENCE
CREATE SEQUENCE VISITOR_LOG.VISITOR_LOG_SEQUENCE;


#TEST INSERT SOMETHING
UPSERT INTO BOOK_STORE (ID, TITLE, COVER, IMAGE, ISBN, OLD_PRICE, PAGES, PRICE, PUBLISH_DATE, PUBLISHER, SUMMARY, TRANSLATOR, URL) VALUES(NEXT VALUE FOR BOOK_STORE.BOOK_SEQUENCE, 'Amerikos snaiperis', 'kietas', 'https://www.manoknyga.lt/knyga/amerikos-snaiperis/virselis-230.jpg', '978-6098-11-414-0', '', '416', '11,99 €', '2015 m., Vilnius', 'manoknyga.lt', 'summary text..', 'Algirdas Acus', 'https://www.manoknyga.lt/knyga/amerikos-snaiperis.html');

#EXAMPLE:
#UPSERT INTO BOOK_STORE (TITLE) VALUES('NEW BOOK!');
#Error: ERROR 218 (22018): Constraint violation. BOOK_STORE.ID may not be null

#JOINS
#GETTING ID OF VISITOR AND BOOK USED WITH VISITOR_LOG;
SELECT B.ID, V.ID FROM BOOK_STORE AS B, VISITOR_LOG AS VL INNER JOIN VISITOR AS V ON VL.MD5_ID = V.MD5_ID WHERE B.ID IN (SELECT CART FROM VISITOR_LOG);