SQLite is a relational database management system contained in a C programming library. In contrast to other database management systems, SQLite is not implemented as a separate process that a client program running in another process accesses. Rather, it is part of the using program.
SQLite is ACID-compliant and implements most of the SQL standard, using a dynamically and weakly typed SQL syntax that does not guarantee the domain integrity.
SQLite is a popular choice as embedded database for local/client storage in application software such as web browsers. SQLite has bindings to many programming languages.
Create new database,inventory.sqlt, of datacentre inventory with two sample tables hosts and datacentre.
-bash-3.2$ sqlite3 inventory.sqlt
SQLite version 3.3.6
Enter “.help” for instructions
sqlite> CREATE TABLE hosts (IP CHAR(18), hostname CHAR(255), datacentre CHAR(32), vendormodel CHAR(255));
sqlite> CREATE TABLE datacentre (datacentre CHAR(255), location CHAR(255));
sqlite>
See database
sqlite> .databases
seq name file
— ————— ———————————————————-
0 main /home/marya/sqlite/inventory.sqlt
sqlite>
See tables
sqlite> .table
datacentre hosts
sqlite>
Check table structure (schema)
sqlite> .schema hosts
CREATE TABLE hosts (IP CHAR(18), hostname CHAR(255), datacentre CHAR(32), vendormodel CHAR(255));
sqlite>
sqlite> .schema datacentre
CREATE TABLE datacentre (datacentre CHAR(255), location CHAR(255));
sqlite>
Insert data in tables
sqlite>
sqlite> insert into “datacentre” values(‘DC1′,’CITY1’);
sqlite> insert into “datacentre” values(‘DC2′,’CITY1’);
sqlite> insert into “datacentre” values(‘DC3′,’CITY2’);
sqlite>
You can also create a batch file and pull data from there
create a file with insert statements
-bash-3.2$ cat hosts.txt
insert into “hosts” values(‘192.168.1.1′,’router1.dc1.city1′,’DC1′,’Cisco’);
insert into “hosts” values(‘192.168.1.2′,’server1.dc1.city1′,’DC1′,’HP’);
insert into “hosts” values(‘192.168.2.3′,’server2.dc2.city1′,’DC2′,’Sun’);
insert into “hosts” values(‘192.168.2.2′,’firewall.dc2.city1′,’DC2′,’F5’);
insert into “hosts” values(‘192.168.2.4′,’backup.dc2.city1′,’DC2′,’netbackup’);
-bash-3.2$
-bash-3.2$ sqlite3 inventory.sqlt < datacentre.txt
-bash-3.2$
Select queries
Show all data in hosts table
sqlite> select * from hosts;
192.168.1.1|router1.dc1.city1|DC1|Cisco
192.168.1.2|server1.dc1.city1|DC1|HP
192.168.2.3|server2.dc2.city1|DC2|Sun
192.168.2.2|firewall.dc2.city1|DC2|F5
192.168.2.4|backup.dc2.city1|DC2|netbackup
Show hosts in DC2
sqlite> select * from hosts where datacentre like ‘DC2’;
192.168.2.3|server2.dc2.city1|DC2|Sun
192.168.2.2|firewall.dc2.city1|DC2|F5
192.168.2.4|backup.dc2.city1|DC2|netbackup
Show location of 192.168.2.2 IP/Host
sqlite>
sqlite> select location from datacentre where datacentre like (select datacentre from hosts where IP like ‘%192.168.2.2%’);
CITY1
Update city information of DC2 from city1 to city2
sqlite>
sqlite> update datacentre set location=’city2′ where datacentre=’DC2′;
sqlite>
Now again check city information for IP/Host 192.168.2.2
sqlite> select location from datacentre where datacentre like (select datacentre from hosts where IP like ‘%192.168.2.2%’);
city2
sqlite>
You can dump output to a file as well
sqlite>
sqlite> .output test.txt
sqlite> select location from datacentre where datacentre like (select datacentre from hosts where IP like ‘%192.168.2.2%’);
sqlite> .exit
-bash-3.2$ cat test.txt
city2
Delete operation
sqlite> delete from hosts where ip=’192.168.2.2′;
Create index
sqlite>
sqlite> CREATE INDEX ip_name ON hosts (ip);
sqlite>
Show indexes on a table
sqlite> .indices hosts
ip_name
sqlite>
Show index details
sqlite> SELECT * FROM sqlite_master WHERE type = ‘index’;
index|ip_name|hosts|4|CREATE INDEX ip_name ON hosts (ip)
Remove indexing
sqlite> drop index ip_name;
sqlite>
Import data from CSV file
.separator “delimiter”
.import csvfile
Take a break, come out of sqlite !
sqlite>
sqlite> .exit
-bash-3.2$