Author.

• add gender : 'man', 'woman', 'n/a'

Tested with Mysql 8.0.18

database is 'bookshelf' (wip)

Reference Manual

manual

Mysql commands

1. mysql> show triggers;
2. mysql> SHOW CREATE TRIGGER before_book_update\G

Export and Import

mysqldump

Insert-statements

1. insert into author values(null,'Zygmunt',null,'Bauman',null,'1925-11-19','2017-01-09','Polish');

Select-statements ( database is 'bookshelf')

from book

SELECT * FROM bookshelf.book; or SELECT * FROM book;

1. fetch all books that are onloan
2. select book.title from book where onloan=true;
3. result = Atomstationen

from bookshelf (every owner can have x number of bookshelfs)

call owner_and_bookshelf_filter_byMobile('0706582414');

'Ingimar', 'Erlingsson', 'Skönlitteratur'
'Ingimar', 'Erlingsson', 'Självstudier ryska'
'Ingimar', 'Erlingsson', 'Sociologi'

View

[] DONE

CREATE VIEW Book_and_Author AS
SELECT *  
FROM book b , author a , bookshelf bshelf 
WHERE  b.fk_authorid=a.id
AND b.fk_bookshelfid=bshelf.id;

bookshelf and books [x]DONE

CREATE VIEW [dbo].[bookshelf_books]
	AS SELECT bookshelf.bookshelfName, book.title 
	FROM bookshelf,book where book.fkBookshelfId=bookshelf.bookshelfId;

Stored Procedures

SQL Server SP

CREATE PROCEDURE [dbo].[spCountBooks]
	@TotalCount int OUTPUT
AS
BEGIN
	SELECT @TotalCount=COUNT(*)FROM book;
END

call

declare @countBooks int
execute [spCountBooks] @countBooks OUTPUT
print @countBooks

authors_and_books_filter_byAuthorsName`

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `authors_and_books_filter_byAuthorsName`(IN name CHAR(50))
BEGIN
  SELECT a.firstname, a.lastname, a.alias, b.title, b.language,b.onloan 
  FROM book b,author a
  WHERE b.fk_authorid=a.id and a.firstname=name;
END$$
DELIMITER ;

book_and_author_filtertag

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `book_and_author_filtertag`(IN tag json)
BEGIN
  SELECT a.firstname, a.lastname, b.title, b.publisher,b.tags FROM book b, author a
  WHERE JSON_CONTAINS(tags, tag)
  AND b.fk_authorid=a.id;
END$$
DELIMITER ;

owner_and_bookshelf_filter_byMobile

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `owner_and_bookshelf_filter_byMobile`(IN phone CHAR(50))
BEGIN
  SELECT o.firstname,o.lastname, s.name 
  FROM owner o, bookshelf s
  WHERE  o.id=s.fk_ownerid and o.phone='0706582414';
END$$
DELIMITER ;

owner_and_bookshelf_withBooks_filter_byMobile

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `owner_and_bookshelf_withBooks_filter_byMobile`(IN phone CHAR(50))
BEGIN
  SELECT o.firstname ,s.name,b.title 
  FROM bookshelf s, book b,owner o
  WHERE s.id = b.fk_bookshelfid
  AND o.id=s.fk_ownerid
  AND o.phone=phone;
END$$
DELIMITER ;

Call Stored Procedures

book_and_author_filtertag('["tag"]')

1. tag='Epikuros'
2. The book 'Döden och odödligheten i det moderna samhället' has the tag 'Epikuros'
3. mysql > call bookshelf.book_and_author_filtertag('["Epikuros"]');

result, returns the book with tags (and the author) # firstname, lastname, title, publisher, tags 'Zygmunt', 'Bauman', 'Döden och odödligheten i det moderna samhället', 'Daidalos', '[\"Judith Adler\", \"Jean Briggs\", \"Robert Paine\", \"Anthony Giddens\", \"Epikuros\", \"Schopenhauer\"]'

bookshelf.authors_and_books_filter_byAuthorsName(author)

1. search for author = 'Halldór' (as in Halldor Laxness)
2. call bookshelf.authors_and_books_filter_byAuthorsName('Halldór');

result

firstname, lastname, alias, title, language, onloan
'Halldór', 'Gudjonsson', 'Halldór Kiljan Laxness', 'Världens ljus och himlens skönhet', 'swedish', '0'
'Halldór', 'Gudjonsson', 'Halldór Kiljan Laxness', 'Fria män', 'swedish', '0'
'Halldór', 'Gudjonsson', 'Halldór Kiljan Laxness', 'Íslandsklukkan', 'icelandic', '0'
'Halldór', 'Gudjonsson', 'Halldór Kiljan Laxness', 'Sjálfsagdir hlutir', 'icelandic', '0'

owner_and_bookshelf_filter_byMobile(mobilenumber)

1. list all bookshelfs for owner with mobilenumber =0706582414
2. call owner_and_bookshelf_filter_byMobile('0706582414');

result

'Ingimar', 'Erlingsson', 'Skönlitteratur'
'Ingimar', 'Erlingsson', 'Självstudier ryska'
'Ingimar', 'Erlingsson', 'Sociologi'

This user has 3 bookshelfs

1. Skönlitteratur
2. Självstudier ryska
3. Sociologi

call owner_and_bookshelf_withBooks_filter_byMobile(mobilenumber);

1. list all bookshelfs-with-bookes for owner with mobilenumber =0706582414
2. call owner_and_bookshelf_withBooks_filter_byMobile('0706582414');

'Ingimar', 'Skönlitteratur', 'Atomstationen'
'Ingimar', 'Skönlitteratur', 'Världens ljus och himlens skönhet'
'Ingimar', 'Skönlitteratur', 'Fria män'
'Ingimar', 'Skönlitteratur', 'Íslandsklukkan'
'Ingimar', 'Skönlitteratur', 'Sjálfsagdir hlutir'
'Ingimar', 'Skönlitteratur', 'livstidsmänniskan'
'Ingimar', 'Självstudier ryska', 'Peter den Store'
'Ingimar', 'Sociologi', 'Döden och odödligheten i det moderna samhället'

Triggers

Trigger: before_book_update

Trigger: before_book_update
              sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `before_book_update` BEFORE UPDATE ON `book` FOR EACH ROW BEGIN
    INSERT INTO book_audit
    SET action = 'update',
     book_id = OLD.id,
	 title = OLD.title,
        changedat = NOW(); 
END

---

The triggers works like this :