In cases where you need something just a bit more smart and easy to use than flat file storage or JSON/XML files you can parse and a full Oracle database is overkill you might want to look at SQLite. SQLite is an open source software library that implements a self-contained (single file), zero-configuration, transactional SQL database engine. SQLite supports multi-user access, but only a single user can update the database at a time. It is largely an "untyped" system and all data is stored as strings.
SQLite is by default shipped with Oracle Linux 7 and is widely used in scripting whenever a semi-smart storage of data is needed. Understanding SQLite and investing some time into it well worth it if you regularly develop code and scripting for your Oracle Linux systems or for other purposes.
Interacting with SQLite
The easiest way to explore SQLite is using the SQLite command line. When on your Linux shell you can use the sqlite3 command to open the SQLite command line. The below example shows how we open a new database, create a table, write some data to the table, query it and after that exit. As soon as you open a new database that does not exist and write something to this database the file will be created on the filesystem.
[root@testbox08 tmp]#
[root@testbox08 tmp]# ls showcase.db
ls: cannot access showcase.db: No such file or directory
[root@testbox08 tmp]#
[root@testbox08 tmp]# sqlite3 showcase.db
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table objecttracker (object, version, object_id INTEGER);
sqlite> insert into objecttracker values ('api/getNewProduct','1.3',10);
sqlite> insert into objecttracker values ('api/getProductPrice','1.3',20);
sqlite> select * from objecttracker;
api/getNewProduct|1.3|10
api/getProductPrice|1.3|20
sqlite> .exit
[root@testbox08 tmp]
[root@testbox08 tmp]# ls showcase.db
showcase.db
[root@testbox08 tmp]#
As you can see from the above example we do not explicitly create the file showcase.db it is simply created the moment we start writing something to the database. In our case the first write is the creation of the table objecttracker.
Even though knowing your way around the SQLite command line is something you have to understand the more interesting part is using it in a programmatic manner.
Coding against SQLite
There are many ways you can interact and code against SQLite, a large number of languages provide a standard way of interacting with SQLite. However, if you simply want to interact with it using a bash script at your Oracle Linux instance you can very well do so.
Working from bash with SQLite is failry simple if you understand the SQLite command line. You can simply wrap all commands together with the command used to call the SQLite database. As an example, if we want to query the table we just created and have the output we can use the below:
[root@testbox08 tmp]# [root@testbox08 tmp]# sqlite3 showcase.db "select * from objecttracker;" api/getNewProduct|1.3|10 api/getProductPrice|1.3|20 [root@testbox08 tmp]# [root@testbox08 tmp]#
As you can see we now have the exact same output as that we got when executing the select statement in the SQLite command line.
This means you can use the above way of executing a SQLite command in a bash script and parse the results in the bash code for future use. In general SQLite provides you a great way to store data in a database without the need to install a full fletched database. In a lot of (small) cases a full database such as the Oracle database is an overkill as you only want to store some small sets of data and retrieve it using SQL statements.
No comments:
Post a Comment