Dealing with system jobs is one thing a shell like bash is good at. But what else can you do with it?
I'm glad you asked. I have several databases locally that hold data on my books, CDs, and so on. The database I use is SQLite, a popular and free database system.
SQLite is typically administered through the command line. Lucky for you, because that means we can use bash scripting to do a lot of work for us.
Most databases will have an option to access it this way. MySQL and MariaDB are 2 that I have experience with, and both are accessible from the command line. Making them also accessible in a shell script.
Bear in mind that we're talking about a DB system installed on your home computer. If this is something you already have then we can continue. If you are interested in learning more about SQLite, a visit to my SQLite site might be in order.
If you need to access a DB in a web environment, these pages will give you some direction, but no specific details. You may wish to visit here for a start.
If you have a different DB installed, you will need to find out how to access it from a shell. Apologies, but I'm not able to provide that information for you.
A question you might be asking is 'Why do I need a script? I have to type in all the information needed into the database. So what's the advantage of typing it into a script?'
Yes, it may seem a bit redundant. All the information you will be adding for a book needs to be individually typed in whether it's in a script or the database directly. But we can do things with a script that may take you several individual steps to do, involving other programs and applications. And the script can do that much faster.
A few reasons to consider bashing into SQLite:
So, assuming SQLite is the DB you have installed and active, let's get going.
Note we didn't say 'Connecting', because we don't begin an ongoing connection to SQLite. We are merely running the sqlite3 application to perform operations.
A typical operation you do is add a record (or many) to your database. For simplicity let's create a database of physical books as an example. So how do we do that using bash?
We'll be using some of the techniques and tools discussed on this site, so buckle up for the ride.
First we need a database. In SQLite that is really simple:
sqlite books.db
Yes. That just created an empty DB called books.
Next, let's set up a variable:
myDB=~/MyDB/books.db If there is a space anywhere in the path, it MUST be quoted: myDB="~/This is where my DB is/my database.db"
You will need to fill in your own path and filename.
NB: I've renamed the default executable file from sqlite3 to sqlite.
If you've done scripting in other languages, you may have heard of here documents. These are very useful and considered Best Practice for this exercise.
It means putting one or more commands into a block delimited by some text, then using the redirect into operator (<<) to pull that block into sqlite:
Now that have a DB we need to create the tables that will hold our data.
Designing tables is beyond what we're talking about here. If this is unfamiliar territory for you, a place to start might be this.
sqlite $myDB << sql CREATE TABLE IF NOT EXISTS "Authors" ( id INTEGER, authorid INTEGER NOT NULL, author TEXT NOT NULL, PRIMARY KEY(id) ); sql sqlite $myDB << sql CREATE TABLE IF NOT EXISTS "Titles" ( id INTEGER, authorid INTEGER NOT NULL, titleid INTEGER NOT NULL, title TEXT NOT NULL, pubyear INTEGER, isbn TEXT, PRIMARY KEY(id) ); sql sqlite $myDB << sql CREATE TABLE IF NOT EXISTS "Keywords" ( id INTEGER, authorid INTEGER NOT NULL, titleid INTEGER NOT NULL, keyword TEXT NOT NULL, PRIMARY KEY(id) ); sql
From now on we will be using a here document for all database commands.
Of course, one of the things you want to do with a DB is add new records.
Since we've just created a brand new DB, let's enter a new book.
This sounds fairly straight-forward - put the data into variables, and insert them into the DB.
But wait - we need to have an 'authorid' and 'titleid'. What are they? And why do we need them?
Good DB design says we should have unique IDs for each author and title. This is where we can use bash to do that for us.
An author can have many books, but a book can only have 1 title.
Two important things we need are a unique ID for the author, and a unique ID for the book title, as indicated in our table creation statements.
But since our DB is completely empty (brand new), we can start with any value you like.
In which case we can start with any value you like for both. Let's start authors at 10 and titles at 10.
Title: A Nother Book TitleID: 10 Author: Anne Author AuthorID: 10 Subjects: Programming, bash, Database, SQLite
We can set up variables for those terms. That way we can use the script again, just changing values for the variables.
Title="Nother Book (A)"
TitleID=10
Author="Author,Anne"
AuthorID=10
Keywords=("Programming" "bash" "Database" "SQLite")
a, an, the are stop words in library science, so typically not useful for indexing.
We've altered the author's name to standard 'lastname,firstname' order.
And we've put the keywords into an array for easier access.
You might have noticed the keyword 'bash' in lower case. We will correct that later into 'BASH'.
And we already have the DB path and name as a variable ($myDB), so we're good to go.
sqlite $myDB << sql
insert into authors(authorid,author) values($AuthorID,'$Author');
insert into books (titleid, authorid, title)
values ($TitleID, $AuthorID,'$Title');
for i in ${Keywords[@]}
do
sqlite $myDB << sql
insert into Keywords(authorid, titleid, keyword)
values ($AuthorID, $TitleID, '$i');
sql
done
As you can see, a 'here document' is used to bundle several commands together. C00l.
When adding the keywords, we didn't put them all into one record, but added each keyword into its own record, along with the authorid and titleid. This is normalization. If you don't know what that is, you have some research to do.
Just to make sure ...
sqlite $myDB << sql select * from authors; select * from titles; select * from keywords order by keyword; sql id authorid author -- -------- ----------- 1 10 Author,Anne id authorid titleid title pubyear isbn -- -------- ------- --------------- ------- ---- 1 10 10 Nother Book (A) id authorid titleid keyword -- -------- ------- ----------- id authorid titleid keyword -- -------- ------- ----------- 4 10 10 Database 1 10 10 Programming 3 10 10 SQLite 2 10 10 bash
Note we did not have to insert a value for ID in any of the tables. That is handled by sqlite, because we defined it as a PRIMARY KEY.
The observant viewer will also note we did not enter a publishing date, an ISBN, or any notes in the 'titles' table. We can forgo them here because we did not define those fields as NOT NULL.
We can add them later (or not) when we have that information. So let's update that table next.
Yes update NOT insert because those fields already exist, they just happen to be empty.
You might also wonder about the order of the keywords. Upper case letters have higher priority than lower case letters.