DVD Store is based on the The Dell DVD Store 2 ( https://linux.dell.com/files/dvdstore/ ), which is an open source simulation of an online e-commerce site with different SQL implementations.
Aim: This repo recreates the small size database from the original for learning purposes.
There are two ways to install. The file paths are written assuming you are in the mysql directory.
- Using
mysql
mysql -u <username> -p <password> < ds2_schema.sql
mysql -u <username> -p <password> ds2 < ds2_data.sql
Sometimes, if mysql information schema doesn't register the new tables properly. Please run the following command to update information_schema with the new tables information.
analyze table <each table name>;
- Using
mysqlsh
mysqlsh -u <username> -p <password>
Once inside the mysqlsh shell:
util.loadDump("./ds2", {threads: 4})
This should take much less time to install than the mysql commands.
To a folder using mysqlsh
util.dumpSchemas(["ds2"],"ds2", {threads: 1, bytesPerChunk: "200M"})Schema SQL using mysqldump
mysqldump -u <username> --single-transaction --default-character-set=utf8mb4 --no-data --routines --databases ds2 --result-file ds2_schema.sql
Data SQL using mysqldump
mysqldump --no-create-db --no-create-info --single-transaction --complete-insert --extended-insert=FALSE --flush-logs -u <username> ds2 > ds2_data.sql
Install the Database import from SQL file but first change the name in the SQL file from dbowner to an existing user or to `postgres' which is the default user.
psql -h localhost -U <username> -d postgres -f ds2dump.sql
Database export to SQL
pg_dump -h localhost -U remo -d ds2 -c -C -S ds2u -f ds2dump.sql
Database export to folder
pg_dump -h localhost -U remo -d ds2 -c -C -Fd -f dss
Database Comparison between mysql & postgresql
mysql> SELECT CONCAT(
'SELECT "',
table_name,
'" AS table_name, COUNT(*) AS exact_row_count FROM `',
table_schema,
'`.`',
table_name,
'` UNION '
)
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'ds2';
mysql> SELECT "categories" AS table_name, COUNT(*) AS exact_row_count FROM `ds2`.`categories` UNION
SELECT "cust_hist" AS table_name, COUNT(*) AS exact_row_count FROM `ds2`.`cust_hist` UNION
SELECT "customers" AS table_name, COUNT(*) AS exact_row_count FROM `ds2`.`customers` UNION
SELECT "inventory" AS table_name, COUNT(*) AS exact_row_count FROM `ds2`.`inventory` UNION
SELECT "orderlines" AS table_name, COUNT(*) AS exact_row_count FROM `ds2`.`orderlines` UNION
SELECT "orders" AS table_name, COUNT(*) AS exact_row_count FROM `ds2`.`orders` UNION
SELECT "products" AS table_name, COUNT(*) AS exact_row_count FROM `ds2`.`products` UNION
SELECT "reorder" AS table_name, COUNT(*) AS exact_row_count FROM `ds2`.`reorder`;
------------ -----------------
| table_name | exact_row_count |
------------ -----------------
| categories | 16 |
| cust_hist | 60350 |
| customers | 20000 |
| inventory | 10000 |
| orderlines | 60350 |
| orders | 12000 |
| products | 10000 |
| reorder | 0 |
------------ -----------------
8 rows in set (1.67 sec)
ds2-# SELECT nspname, proname FROM pg_catalog.pg_namespace JOIN pg_catalog.pg_proc ON pronamespace = pg_namespace.oid WHERE nspname = 'public';
schemaname | relname | n_live_tup
------------ ------------ ------------
public | categories | 16
public | cust_hist | 60350
public | customers | 20000
public | inventory | 10000
public | orderlines | 60350
public | orders | 12000
public | products | 10000
public | reorder | 0
(8 rows)