Some SQL Experiments
I have always been a big fan of literature and books! And also a bit obsessed with order! For such reason I've created at some point (around the year 2000) a Microsoft Access DB with a complete catalogue of my personal library, following the Dewey decimal classification system. The years passed and at then it became harder and harder to keep the DB up to date. Some years more go and I learned SQL! Now I moved everything from a .mdb file to a mySQL DB!
Here a small summary how I made it (All commands where performed on my Linux machine, using Ubuntu 20.04).
Here a small summary how I made it (All commands where performed on my Linux machine, using Ubuntu 20.04).
1. I use mdb-schema to extract the schema of the original (old) file, called "BibliotecaPersonal.mdb"
$ mdb-schema -1 BibliotecaPersonal.mdb > Biblioteca_schema.sql
The schema looks like:
CREATE TABLE Libros
(
Id Text(100),
Titulo Text(510),
Autor Text(100),
Editorial Text(100),
Pags INT,
ISBN Text(100),
Ejemplares INT,
Leido Boolean NOT Null,
Observaciones Text(510)
);
CREATE TABLE Prestamos
(
Id Text(100),
Titulo Text(510),
PrestadoA Text(100),
FechaPrestamo DateTime,
FechaEntrega DateTime,
Observaciones Text(510)
);
Among others, there are two tables:
- "Libros" ("Books"), which contains all the books in my personal library
- "Prestamos" ("Loans"), which basically contains the books that I loan to somebody else
2. Inside mysql I created a new database, called "BibliotecaPersonal" (Personal Library)
mysql> create database BibliotecaPersonal;
3. On the command line, I loaded the schema to mySQL
$ mysql -u carlos -p BibliotecaPersonal < Biblioteca_schema.sql
4. Now, sometimes there are some troubles importing individual tables to mySQL (it can be from some special characters, that were not well encoded, for instance). So I write down a small bash script which import individual tables to the DB on mySQL. The script looks like:
#!/bin/bash
IFS=$'\n'
TABLES=$(mdb-tables -1 $1)
FILE_DB='export.sql'
for t in $TABLES
do
echo "Exporting Table $t"
mdb-export -I mysql $1 $t > $FILE_DB
mysql -u carlos -p BibliotecaPersonal < $FILE_DB
echo
done
For simplicity I did not write down all the script, but in the loop I added some lines to change the special characters to a more traditional form, SQL-friendly, using some "sed" magic! Some ideas were adapted from this link.
Then I ran the script:
$./mdb_to_mysql.sh BibliotecaPersonal.mdb
5. Now, I can enjoy my DB on mySQL! All tables are loaded, and I can update
mysql> SELECT COUNT(Id) FROM Libros;
+-----------+
| COUNT(Id) |
+-----------+
| 321 |
+-----------+
1 row in set (0.05 sec)
Only 321 books! Hope that I can read them all soon!
6. As a final example, one can check the books that I have for a given author
SELECT * FROM Libros WHERE Autor LIKE '%Saramago%';
+----------------+---------------------------------+-----------------+------+------------------------------+
| Id | Titulo | Autor | Pags | Observaciones |
+----------------+---------------------------------+-----------------+------+------------------------------+
| 869.3 S17m | Memorial del Convento | Saramago, José | 480 | NULL |
| 869.3 S17l | Levantado del Suelo | Saramago, José | 512 | NULL |
| 869.3 S17c | Caverna, La | Saramago, José | 448 | NULL |
| 869.3 S17t | Todos los Nombres | Saramago, José | 352 | NULL |
| 869.3 S17e - X | Evangelio Según Jesucristo, El | Saramago, José | 478 | PERDIDO - PRESTADO... |
+----------------+---------------------------------+-----------------+------+------------------------------+
5 rows in set (0.00 sec)
Hooray! 5 books of one of my favorites authors: José Saramago!