Tag 8
1 Dokumentationsaufträge
1.1 Übungsdokuemntation Sakila
-
Struktur und Daten importieren
sudo mysql < sakila-schema.sql
sudo mysql < sakila-data.sql
- Datenbank testen
USE sakilla;
SHOW FULL TABLES;
OUTPUT
----------------------------+------------+
| Tables_in_sakila | Table_type |
+----------------------------+------------+
| actor | BASE TABLE |
| actor_info | VIEW |
| address | BASE TABLE |
| category | BASE TABLE |
| city | BASE TABLE |
| country | BASE TABLE |
| customer | BASE TABLE |
| customer_list | VIEW |
| film | BASE TABLE |
| film_actor | BASE TABLE |
| film_category | BASE TABLE |
| film_list | VIEW |
| film_text | BASE TABLE |
| inventory | BASE TABLE |
| language | BASE TABLE |
| nicer_but_slower_film_list | VIEW |
| payment | BASE TABLE |
| rental | BASE TABLE |
| sales_by_film_category | VIEW |
| sales_by_store | VIEW |
| staff | BASE TABLE |
| staff_list | VIEW |
| store | BASE TABLE |
+----------------------------+------------+
23 rows in set (0.00 sec)
SELECT COUNT(*) FROM film;
OUTPUT
+----------+
| COUNT(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.06 sec)
SELECT COUNT(*) FROM film_text;
OUTPUT
+----------+
| COUNT(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)
1.1.1 SPs, Views, Triggers anzeigen und anwenden
- Zeigen Sie alle Views der Datenbank sakila an:
SHOW FULL TABLES WHERE table_type = 'VIEW';
OUTPUT
+----------------------------+------------+
| Tables_in_sakila | Table_type |
+----------------------------+------------+
| actor_info | VIEW |
| customer_list | VIEW |
| film_list | VIEW |
| nicer_but_slower_film_list | VIEW |
| sales_by_film_category | VIEW |
| sales_by_store | VIEW |
| staff_list | VIEW |
+----------------------------+------------+
7 rows in set (0.00 sec)
- Zeigen Sie alle Stored Procedures der Datenbank sakila an:
SHOW PROCEDURE STATUS WHERE db = 'sakila';
OUTPUT
+--------+-------------------+-----------+----------------+---------------------+---------------------+---------------+--------------------------------------------------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+--------+-------------------+-----------+----------------+---------------------+---------------------+---------------+--------------------------------------------------+----------------------+----------------------+--------------------+
| sakila | film_in_stock | PROCEDURE | root@localhost | 2023-04-19 11:10:08 | 2023-04-19 11:10:08 | DEFINER | | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
| sakila | film_not_in_stock | PROCEDURE | root@localhost | 2023-04-19 11:10:08 | 2023-04-19 11:10:08 | DEFINER | | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
| sakila | rewards_report | PROCEDURE | root@localhost | 2023-04-19 11:10:08 | 2023-04-19 11:10:08 | DEFINER | Provides a customizable report on best customers | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
+--------+-------------------+-----------+----------------+---------------------+---------------------+---------------+--------------------------------------------------+----------------------+----------------------+--------------------+
3 rows in set (0.01 sec)
- Zeigen Sie alle Triggers der Datenbank sakila an:
SHOW TRIGGERS IN sakila;
OUTPUT
+----------------------+--------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+----------------------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation |
+----------------------+--------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+----------------------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| customer_create_date | INSERT | customer | SET NEW.create_date = NOW() | BEFORE | 2023-04-19 11:10:12.03 | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
| ins_film | INSERT | film | BEGIN
INSERT INTO film_text (film_id, title, description)
VALUES (new.film_id, new.title, new.description);
END | AFTER | 2023-04-19 11:10:07.07 | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
| upd_film | UPDATE | film | BEGIN
IF (old.title != new.title) OR (old.description != new.description) OR (old.film_id != new.film_id)
THEN
UPDATE film_text
SET title=new.title,
description=new.description,
film_id=new.film_id
WHERE film_id=old.film_id;
END IF;
END | AFTER | 2023-04-19 11:10:07.10 | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
| del_film | DELETE | film | BEGIN
DELETE FROM film_text WHERE film_id = old.film_id;
END | AFTER | 2023-04-19 11:10:07.12 | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
| payment_date | INSERT | payment | SET NEW.payment_date = NOW() | BEFORE | 2023-04-19 11:10:13.93 | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
| rental_date | INSERT | rental | SET NEW.rental_date = NOW() | BEFORE | 2023-04-19 11:10:14.97 | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
+----------------------+--------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+----------------------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
6 rows in set (0.04 sec)
Alle Stored Procedures, alle Views und Triggers sind in der Datei
sakila-data.sqldefiniert
1.1.2 SPs, Views, Triggers Import(Create)-Befehle dokumentiert
- Dokumentieren Sie den Create-Befehl der Stored Procedures
film_in_stock
SHOW CREATE PROCEDURE film_in_stock;
OUTPUT
...
BEGIN
SELECT inventory_id
FROM inventory
WHERE film_id = p_film_id
AND store_id = p_store_id
AND inventory_in_stock(inventory_id);
SELECT COUNT(*)
FROM inventory
WHERE film_id = p_film_id
AND store_id = p_store_id
AND inventory_in_stock(inventory_id)
INTO p_film_count;
END
...
Diese SP sucht nach der inventory_id, wo die Film und Store ID mit der übergebenen übereinstimmt, indem sie die Funktion inventory_in_stock verwendet, um zu überprüfen, ob das Inventar in dem angegebenen Lagerbestand vorhanden ist. Wenn das Inventar gefunden wird, wird die Anzahl der verfügbaren Einheiten in der Variable p_film_count gespeichert.
- Dokumentieren Sie den Create-Befehl der View
actor_info.
SHOW CREATE VIEW actor_info;
OUTPUT
...
CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY INVOKER VIEW `actor_info` AS
select
`a`.`actor_id` AS `actor_id`,
`a`.`first_name` AS `first_name`,
`a`.`last_name` AS `last_name`,
group_concat(
distinct concat(
`c`.`name`,
': ',
(
select
group_concat(
`f`.`title`
order by
`f`.`title` ASC separator ', '
)
from
(
(
`film` `f`
join `film_category` `fc` on(
(`f`.`film_id` = `fc`.`film_id`)
)
)
join `film_actor` `fa` on(
(`f`.`film_id` = `fa`.`film_id`)
)
)
where
(
(
`fc`.`category_id` = `c`.`category_id`
)
and (`fa`.`actor_id` = `a`.`actor_id`)
)
)
)
order by
`c`.`name` ASC separator '; '
) AS `film_info`
from
(
(
(
`actor` `a`
left join `film_actor` `fa` on(
(`a`.`actor_id` = `fa`.`actor_id`)
)
)
left join `film_category` `fc` on(
(`fa`.`film_id` = `fc`.`film_id`)
)
)
left join `category` `c` on(
(
`fc`.`category_id` = `c`.`category_id`
)
)
)
group by
`a`.`actor_id`,
`a`.`first_name`,
`a`.`last_name`
...
Diese View kombiniert Informationen über Schauspieler, Filme und Kategorien, um eine Tabelle zu erstellen, die detaillierte Informationen über jeden Schauspieler und seine Filme enthält. Die Informationen werden in der Spalte film_info durch die Funktion group_concat kombiniert. Durch das group by nach actor_id, first_name und last_name wird sichergestellt, dass jeder Schauspieler in der Tabelle nur einmal aufgeführt wird. Die View kann wie folgt aufgerufen werden:
SELECT * FROM actor_info;
1.2 Übungsdokuemntation Chat-Applikation
-
Repo von GitHub herunterladen.
-
NPM-Dependencies installieren
cd realtime-chat-application-using-nodejs-expressjs-and-web-socket
npm install
- Datenbank erstellen
CREATE DATABASE chat;
-
Benutzerinformation in
database/db.jsanpassen -
Applikation per
node server.jsstarten
info
Ein User welcher über mysql_native_password identifiziert wird ist nötig!
Link
- Erstellen Sie zwei Logins in der Datenbank
INSERT INTO login VALUES ('1','user1','user1');
INSERT INTO login VALUES ('2','user2','user2');
http://localhost:3000in ZWEI Browsern öffnen und mituser1unduser2anmelden
1.2.1 SPs, Views anwenden (erstellen und aus der Applikation verwenden)
Aktueller SQL-Code in server.js
var sql = "SELECT * FROM message ";
...
var sql = "INSERT INTO message (message , user) VALUES ('" + data+ "' , '"+user+"')";
...
var sql = "SELECT * FROM login WHERE username='" + username+"'";
- Führen Sie für die Applikation eine Stored-Procedures und eine View ein.
Message View erstellen
CREATE VIEW view_messages AS
SELECT * FROM message;
Stored Procedure erstellen
DELIMITER //
CREATE PROCEDURE insertMessage (IN messageParam VARCHAR(2550), IN userParam VARCHAR(250))
BEGIN
INSERT INTO message (message , user) VALUES (messageParam,userParam);
END;
//
DELIMITER ;
Login View erstellen
CREATE VIEW view_logins AS
SELECT * FROM login;
Message View testen
SELECT * FROM view_messages;
OUTPUT
+----+----------------------+-------+
| id | message | user |
+----+----------------------+-------+
| 1 | Hallo, ich bin User1 | user1 |
| 2 | Hallo, ich bin User2 | user2 |
+----+----------------------+-------+
2 rows in set (0.00 sec)
Stored Procedure testen
CALL insertMessage("TEST","user1");
info
Die Message sollte nach einem Reload im Browser angezeigt werden.
Login View testen
SELECT * FROM view_logins;
OUTPUT
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | user1 | user1 |
| 2 | user2 | user2 |
+----+----------+----------+
2 rows in set (0.00 sec)
- Passen Sie die Applikation entsprechend an und testen Sie sie aus
var sql = "SELECT * FROM view_messages ";
...
var sql = "CALL insertMessage('" + data+ "','"+user+"')";
...
var sql = "SELECT * FROM view_logins WHERE username='" + username+"'";
Nachdem neustarten der Applikation, sollte immer noch alles funktionieren.
