Home » Internet » Web Programming » Other Web Programming

SQLite Database Design

Ranked #988 in Internet, #59,443 overall

Design Databases Using SQLite

Use this lens to learn how to design the data and database of your application using SQLite. You will learn some basic data design, and then use the SQLite command line tool to describe your application data and then to verify the ability to add, change, view and otherwise manipulate the data in your database.

This lens is currently under construction. Please go to SQLite The Hammer for more information on SQLite.

For more information on SQLite, go to SQLite The Hammer. For more information on programming with SQLite, go to SQLite Programming. For an introductory tutorial SQL or SQLite go to SQLite Tutorial.

New Book: Using SQLite

The folks at O'Reilly have published a good book on using SQLite. It has a good section on common database design principles.
Loading

Good Book on Database Design

You could spend your time scouring the internet for free tutorials, free code, etc. But you would spend a lot of time doing it. You could save a fair bit of time by coming back to this page to read and explore material. This is much more effective. But to really jump-start your learning of data design, the book shown below will ground you in the fundamentals of data design. If your time is worth money, the books will more than pay for themselves.

A wise old computer scientist once said, "Make your data structures smart and your code dumb". That is so true! When you have smart data structures, you just don't have to write as much code to build your application. SQLite makes it really easy to design smart data structures, but If you haven't done a lot of data design, it's hard. Get "Beginning Database Design: From Novice to Professional" if you need to jump-start your data design.
Loading

How To Design a Simple Phone Book with SQLite

...a good example to show how to get going with SQLite

The best way to get into database design is to try a mundane example, which is a phone book that holds one phone number per name. This example shows how to code a simple database, insert entries, modify entries, show entries with different filters, and delete entries. All of these SQL commands can be driven by programming interfaces in various languages. (Please see SQLite Programming and SQLite Hammer for more details if you want to drive this database with a computer program).

Start by firing up SQLite.

C:\Users\Jay> sqlite3 phonebook.db
SQLite version 3.5.6
Enter ".help" for instructions


Now create the table. (The .schema command above shows you which tables are in this database.)

sqlite> create table phonebook (name string, phone string);
sqlite> .schema
CREATE TABLE phonebook (name string, phone string);

sqlite> insert into phonebook values ("Jay", "555-1234");
sqlite> insert into phonebook values ("Jay", "222-1234");
sqlite> insert into phonebook values ("Kay", "555-3333");
sqlite> insert into phonebook values ("Ray", "222-1111");
sqlite> insert into phonebook values ("May", "222-1111");
sqlite> insert into phonebook values ("Fay", "333-1111");
sqlite> insert into phonebook values ("Balasubrahmaniam", "333-1111");
sqlite>



Now show the table. (Note that "|" separates the columns of the rows).

sqlite> select * from phonebook;
Jay|555-1234
Jay|222-1234
Kay|555-3333
Ray|222-1111
May|222-1111
Fay|333-1111
Balasubrahmaniam|333-1111
sqlite>



Now change a phone number (with update) and see the effect on the phone book compared to what was above.

sqlite> update phonebook set phone="666-1234" where name="Ray";
sqlite> select * from phonebook;
Jay|555-1234
Jay|222-1234
Kay|555-3333
Ray|666-1234
May|222-1111
Fay|333-1111
Balasubrahmaniam|333-1111


Now change a name for a given phone number.

sqlite> update phonebook set name="Fay Jones" where phone="333-1111";
sqlite> select * from phonebook;
Jay|555-1234
Jay|222-1234
Kay|555-3333
Ray|666-1234
May|222-1111
Fay Jones|333-1111
Fay Jones|333-1111


Notice that there were two names changed because they both had the same phone number.

Now delete a user from the phone book and check for updates.

sqlite> delete from phonebook where name="Kay"
...> ;
sqlite> select * from phonebook;
Jay|555-1234
Jay|222-1234
Ray|666-1234
May|222-1111
Fay Jones|333-1111
Fay Jones|333-1111


Now find phone numbers with "333" in it.

sqlite> select * from phonebook where phone like "333";
sqlite> select * from phonebook where phone like "%333%";
Fay Jones|333-1111
Fay Jones|333-1111


Now find phone numbers with "222" in it.

sqlite> select * from phonebook where phone like "%222%";
Jay|222-1234
May|222-1111


Now put another number into the book, and look for the numbers with "222" in it.

sqlite> insert into phonebook values ("Balasubrahmaniam", "333-2221");
sqlite> select * from phonebook where phone like "%222%";
Jay|222-1234
May|222-1111
Balasubrahmaniam|333-2221


Now delete all entries from the phone book and check that it worked.

sqlite> delete from phonebook;
sqlite> select * from phonebook;
sqlite>

Design a Phonebook With SQLite
by Jaynonymous1 | video info

12 ratings | 3,817 views
curated content from YouTube

Simple Phone Book: Unique Attributes

...one way to write less code and avoid errors...

One of the problems with the simple database above is that it allows you to enter multiple phone numbers for the same person. This can be challenging because if you work in a system where each person only has one phone, the database above allows you to add more phone numbers without complaining about an existing one. Also, later on somebody could search for a name, expecting one phone, but getting another one that may be out of date.

The way to solve this is to use unique columns. When you define a table, define a column as UNIQUE, and the database itself will disallow entering duplicates.

From the example above do the following in SQLite.

sqlite> drop table phonebook;
sqlite> create table phonebook (name string unique, phone string);



The last command tells you that the names in the table must be unique. SQLite will reject attempts to insert or modify data that causes non-unique names.
Then insert some data.

sqlite> insert into phonebook values ("Jay", "555-1234");



So far, so good.

sqlite> insert into phonebook values ("Jay", "222-1234");
SQL error: column name is not unique
sqlite> select * from phonebook;
Jay|555-1234


Notice that SQLite rejected the same insertion attempt for "Jay", and the select statement proves it.

sqlite> insert into phonebook values ("Ray", "222-1111");
sqlite> select * from phonebook;
Jay|555-1234
Ray|222-1111
sqlite>

SQLite Unique Attributes
by Jaynonymous1 | video info

5 ratings | 3,195 views
curated content from YouTube

How to Use SQL Join Tables to Extract Data From Many Tables

Many times when designing normalized data structures, you are asked to split information across multiple tables. It seems wasteful, but you can sort of understand why. For example, consider this example where you have many users and phones. Each phone is associated with a user with its "user_id" field.




C:\Users\Jay>sqlite3 joinexample.db
SQLite version 3.5.6
Enter ".help" for instructions
sqlite> create table users (id integer, name string);
sqlite> insert into users values (1, "Jay");
sqlite> insert into users values (2, "Ray");
sqlite> insert into users values (3, "Kay");
sqlite> insert into users values (4, "May");
sqlite> create table phones (user_id integer, number string);
sqlite> insert into phones values (1, "221-7182");
sqlite> insert into phones values (1, "492-9999");
sqlite> insert into phones values (2, "836-4252");
sqlite> insert into phones values (2, "271-4002");
sqlite> insert into phones values (3, "737-1111");
sqlite> insert into phones values (4, "969-4322");
sqlite> select * from users;
1|Jay
2|Ray
3|Kay
4|May
sqlite> select * from phones;
1|221-7182
1|492-9999
2|836-4252
2|271-4002
3|737-1111
4|969-4322



So far, so good. Now if somebody asked you to figure out which phone numbers belong to Ray, you would do the following:

1) You would look into the table and realize that Ray has an "id" of "2".
2) Then you go the phones table and realize that there are 2 phone numbers belonging to "user_id" of "2", namely "836-4252", and "271-4002".

Although it seems easy to do this by hand, doing it in software is harder unless you understand the concept of joining tables in SQL. Consider the following statement.

sqlite> select * from users, phones;
1|Jay|1|221-7182
1|Jay|1|492-9999
1|Jay|2|836-4252
1|Jay|2|271-4002
1|Jay|3|737-1111
1|Jay|4|969-4322
2|Ray|1|221-7182
2|Ray|1|492-9999
2|Ray|2|836-4252
2|Ray|2|271-4002
2|Ray|3|737-1111
2|Ray|4|969-4322
3|Kay|1|221-7182
3|Kay|1|492-9999
3|Kay|2|836-4252
3|Kay|2|271-4002
3|Kay|3|737-1111
3|Kay|4|969-4322
4|May|1|221-7182
4|May|1|492-9999
4|May|2|836-4252
4|May|2|271-4002
4|May|3|737-1111
4|May|4|969-4322
sqlite>



You'll notice that each row of the result is from combining each row of the "users" table with each row of the "phones" table. Thus you would expect 4 users X 6 phones for a total of 24 rows. This is just a set join operation in relational algebra. (See Wikipedia on Relational Algebra).

How is this useful?

In SQL, you can also filter the results. For example suppose you want to join the tables, but only pick the tables where the "id" of the "users" table matches the "user_id" of the "phones" table. Then you write:

sqlite> select * from users, phones where users.id = phones.user_id;
1|Jay|1|221-7182
1|Jay|1|492-9999
2|Ray|2|836-4252
2|Ray|2|271-4002
3|Kay|3|737-1111
4|May|4|969-4322



Conceptually, this causes the SQL engine to build the 24-row result above, and then filters out the rows where the "users.id" and "phones.user_id" fields don't match.

The following example is a bit more efficient in most database engines.

sqlite> select * from users, phones on users.id = phones.user_id;
1|Jay|1|221-7182
1|Jay|1|492-9999
2|Ray|2|836-4252
2|Ray|2|271-4002
3|Kay|3|737-1111
4|May|4|969-4322
sqlite>



Conceptually it causes only the rows where "users.id" match "phones.user_id" to be put into the result row. (There is no difference in performance with SQLite, but there may be with other SQL database engines).

If you want to use this to present in a nice user interface, you probably don't want the "id" interface in the output. In that case you just do:

sqlite> select users.name, phones.number from users, phones on users.id=phones.user_id;
Jay|221-7182
Jay|492-9999
Ray|836-4252
Ray|271-4002
Kay|737-1111
May|969-4322
sqlite>



In this case, you had to select which field you wanted, and from which table. In the example above, we took the "name" from the "users" table and the "number" from the "phones" table. Under the covers, we are still only picking results where "users.id=phones.user_id".

Table joins can also be extended to multiple tables, and with multiple filtering conditions. When you know you can use joins to get the information you need, you'll feel much safer normalizing your data and storing a piece of information only once.

Video: How to Use SQL Join Tables to Extract Data From Many Tables

...see joins live.

This video shows how to use SQL joins using SQLite.
SQLite Join Tables
by Jaynonymous1 | video info

19 ratings | 4,944 views
curated content from YouTube

How To Generate Data-driven Strings Using SQLite

Powerful data-driven string construction in SQLite

Say you have to create strings in your application based on values in a database. SQLite has a simple but powerful string concatenation utility which can be used to generate data-driven strings. You can generate such strings simply by using the concatenation operator "||". For example in the sequence below with the sqlite3 command utility,

sqlite> select "a" || 2 || "b" ;
a2b
sqlite>



you'll notice that you can concatenate multiple elements of varying types. SQLite handles it for you. This is fairly easy to do in Ruby or Perl, but in C you have to worry about allocating and de-allocating memory buffers to hold strings, and to convert from char to int (atoi) or vice versa (itoa). SQLite provides a simple way to build the strings and all of the memory allocation and type conversions are handled by the SQLite library.

The example below shows how to build a list of function names based on a list of things and a list of common operations. Concatenating integers (rowid) and literal strings are thrown into the mix for completeness. Run the commands and study the results as it relates to the query.

sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE things (thingName string);
INSERT INTO "things" VALUES('Button');
INSERT INTO "things" VALUES('Window');
INSERT INTO "things" VALUES('Pane');
INSERT INTO "things" VALUES('Label');
CREATE TABLE operations(opName string);
INSERT INTO "operations" VALUES('Create');
INSERT INTO "operations" VALUES('Read');
INSERT INTO "operations" VALUES('Update');
INSERT INTO "operations" VALUES('Delete');
INSERT INTO "operations" VALUES('Ioctl');
COMMIT;
sqlite> select thingName || "_" || opName || "___" || things.rowid || "__" || operations.rowid from things, operations;
Button_Create___1__1
Button_Read___1__2
Button_Update___1__3
Button_Delete___1__4
Button_Ioctl___1__5
Window_Create___2__1
Window_Read___2__2
Window_Update___2__3
Window_Delete___2__4
Window_Ioctl___2__5
Pane_Create___3__1
Pane_Read___3__2
Pane_Update___3__3
Pane_Delete___3__4
Pane_Ioctl___3__5
Label_Create___4__1
Label_Read___4__2
Label_Update___4__3
Label_Delete___4__4
Label_Ioctl___4__5
sqlite>


If you need data-driven string construction, SQLite provides a convenient native utility, and it is especially useful for C programs that use SQLite.

SQL Books

This lens is as much about manipulating data using SQL as it is about using SQLite. If you want to get a sense of the kinds of problems you can solve with SQL, both of these books will give you practical examples of how to solve typical problems.
Loading

How To Use SQLite For a SNMP Agent Implementation

Use SQLite for SNMP data storage.

Many networked devices must be monitored on a network. The protocol of choice is the Simple Network Management Protocol (SNMP). SNMP mandates the use of standard data structures to monitor any type of networked device. SQLite can be used to store the data for a SNMP Agent implementation. Why? First of all, initialization is very easy. Secondly, querying the data is very easy.

Here is a simple example:

We havea 4-port DSL home router, with 4 10-100 ethernet ports, and one DSL port. There are no removable modules. Provide the data for a SNMP agent to "walk" the Entity MIB.

The entity MIB tells what is actually there. This is a pretty trivial example because this router has exactly one module.

According to RFC 2737 ,the Entity MIB data structure looks like the structure below. (My comments are to the right of each variable).

EntPhysicalEntry ::= SEQUENCE {
entPhysicalIndex Index of table, starting at 1
entPhysicalDescr Manufacturer's name, product code
entPhysicalVendorType Use NULL here
entPhysicalContainedIn index of entity contained in. Use 0 for the
entPhysicalClass ports=10, chassis=3
entPhysicalParentRelPos port number (1..4) for ethernet, 1 for DSL, 0 for chassis
entPhysicalName "home router" for the chassis, "DSL uplink" for the DSL uplink, and "port XX" for the ethernet port.
entPhysicalHardwareRev "" for the ports, "hrx213" for the chassis.
entPhysicalFirmwareRev "" for ports, "hrx213fw" for the chassis.
entPhysicalSoftwareRev "" for the ports, "hrx213sw-build101" for the chassis
entPhysicalSerialNum "" for the ports, "MFGXX12748398" for the chassis
entPhysicalMfgName "XX Networks" for all.
entPhysicalModelName "" for ports "DSL Home Router 3x17" for chassis
entPhysicalAlias "" for all
entPhysicalAssetID ""
entPhysicalIsFRU true=1 for chassis, false=2 for ports.
}

There will be one row for the router, one entry for the DSL port, and 4 entries for the ethernet ports. Read the RFC 2737 for details.

Here is what the table will look like on its side.

entPhysicalIndex 1 2 3 4 5 6
entPhysicalDescr "XX Networks DSL Home Router 3x17" "" "" "" "" ""
entPhysicalVendorType "" "" "" "" ""
entPhysicalContainedIn 0 1 1 1 1 1
entPhysicalClass 3 10 10 10 10 10
entPhysicalParentRelPos 0 1 1 2 3 4
entPhysicalName "home router" "DSL uplink" "ethernet port 1" "ethernet port 2" "ethernet port 3" "ethernet port 4"

entPhysicalHardwareRev "hrx213" "" "" "" "" ""
entPhysicalFirmwareRev "hrx213fw" "" "" "" "" ""
entPhysicalSoftwareRev "hrx213sw-build101" "" "" "" "" ""
entPhysicalSerialNum "MFGXX12748398" "" "" "" "" ""
entPhysicalMfgName "XX Networks" "" "" "" "" ""
entPhysicalModelName "DSL Home Router 3x17" "" "" "" "" ""
entPhysicalAlias "" "" "" "" "" ""
entPhysicalAssetID "" "" "" "" "" ""
entPhysicalIsFRU 1 2 2 2 2 2

To implement the table in SQLite, first create the table:
CREATE TABLE entPhysicalTable (entPhysicalIndex integer, entPhysicalDescr string, entPhysicalVendorType string, entPhysicalContainedIn integer, entPhysicalClass integer, entPhysicalParentRelPos integer, entPhysicalName string, entPhysicalHardwareRev string, entPhysicalFirmwareRev string, entPhysicalSoftwareRev string, entPhysicalSerialNum string, entPhysicalMfgName string, entPhysicalModelName string, entPhysicalAlias string, entPhysicalAssetID string, entPhysicalIsFRU integer);

The statements to initialize the tables is as follows:
insert into entPhysicalTable (entPhysicalIndex, entPhysicalParentRelPos, entPhysicalName) values (1,0,"home router");
insert into entPhysicalTable (entPhysicalIndex, entPhysicalParentRelPos, entPhysicalName) values (2,1,"DSL uplink");
insert into entPhysicalTable (entPhysicalIndex, entPhysicalParentRelPos, entPhysicalName) values (3,1,"ethernet port 1");
insert into entPhysicalTable (entPhysicalIndex, entPhysicalParentRelPos, entPhysicalName) values (4,2,"ethernet port 2");
insert into entPhysicalTable (entPhysicalIndex, entPhysicalParentRelPos, entPhysicalName) values (5,3,"ethernet port 3");
insert into entPhysicalTable (entPhysicalIndex, entPhysicalParentRelPos, entPhysicalName) values (6,4,"ethernet port 4");

update entPhysicalTable set entPhysicalDescr='', entPhysicalVendorType="", entPhysicalContainedIn=1, entPhysicalClass=10, entPhysicalHardwareRev="", entPhysicalFirmWareRev="", entPhysicalSoftwareRev="", entPhysicalSerialNum="", entPhysicalMfgName="", entPhysicalModelName="", entPhysicalAlias="", entPhysicalAssetID="" ,entPhysicalIsFRU=2;
update entPhysicalTable set entPhysicalDescr="XX Networks DSL Home Router 3x17", entPhysicalModelName="DSL Home Router 3x17", entPhysicalIsFRU=1 where entPhysicalIndex=1;

These can be done once when the load is built, or at initialization time.

There will be a software routine that reads the hardware, firmware, and software revisions at initialization time from well-known APIs.
Once done, do the following at system initialization time right after reading the values:
update entPhysicalTable set entPhysicalHardwareRev='hrx213', entPhysicalFirmwareRev='hrx213fw', entPhysicalSoftwareRev='hrx213sw-build101' where entPhysicalIndex=1;

The actual values will be built into the update query string in the language of your choice.

If hard disk storage is not available, an in-memory SQLite database can be used instead.

The SNMP manager will issue SNMP-GET requests to the SNMP agent. The following query satisfies the request for the index @X:

select * from entPhysicalTable where entPhysicalIndex = @X;

If the query yields no results, then the MIB objects do not exist.

The SNMP manager will issue SNMP-GETNEXT requests to the SNMP agent. The following query satisfies the request for the index @X:

select * from entPhyscialTable where entPhysicalIndex >= @X order by entPhysicalIndex limit 1;

If the query yields no results, then the table is empty or the table has been walked completely.

This example shows that you can use a very simple SQLite table and simple SQLite queries to implement the data storage for the SNMP Entity MIB.

P.S.
There is a great wiki at http://net-snmp.sourceforge.net/wiki/index.php/Tutorials which shows how to hook up NetSNMP to a c++ extension.

SQLite For A SNMP Agent For A Flexible Modular Device

The previous example showed that it is possible to use SQLite for the SNMP data storage of a simple single-module device. It gets a little more complicated for a modular device. Why? The entPhysicalTable assumes that chassis, modules, ports, etc have a uniform data structure and naming scheme. That is simply not the case. In any system, the quantity that identifies one kind of entity (e.g. port) is probably different than the quantity that identifies a different kind of entity. Also, different kinds of entities have different attributes. One particular tool from SNMP Research comes with the entPhysicalTable data structures already coded and tested. The problem with this example is that because the schema of the system is different from the schema of the storage, you will probably end up with the same concepts (e.g cards & ports) stored in two places (Entity MIB tables and provisioning tables). Duplicating data like this is rarely ever a good thing because over time, it leads to bugs. This Squidoo Lens module shows a way out.

Take the following example:

1) Ethernet switch chassis, with a built-in main controller, and three plug-in slots, and a 1000-base T ethernet interface...northbound
2) Optional cards can be plugged into any slot in any combination. One card type is a 2-port PoE card. The is a 4-port Ethernet card.

A number of issues come up:

1) Slots and modules are uniquely identified by slot number (1, 2, or 3).
2) Ports could be identified by a combination of slot number and port number, or just by the fact that it is a northbound port.
3) Depending on which kind of cards are plugged in, the configuration could have anywhere from 3 to 13 ports. Allocating entPhysicalIndex could be troublesome, because there will either be an inconsistent numbering scheme for a given slot/port combination, or the allocation of entPhysicalIndex could be sparse.

The solution to this problem is to realize that entPhysicalTable is only a view of data. You can and should store data in tables that are keyed by the appropriate quantities.
Also, to make this work, we need to ensure that entPhysicalIndex is consistently allocated. A convenient way to do this is to pre-allocate them up front. For example:
1) entPhysicalIndex=1 goes for the chassis.
2) 2 goes for the 1000-base-T port
3) 3, 4, and 5 go for the slots.
4) 6, 7, and 8 go for the modules.
5) Since we know that a module can have up to 4 ports, indices 9-12 go to the possible ports in slot 1. 13-16 go to the ports in slot 2. 17-20 go to the ports in slot 3.
6) We know that modules 6,7,and 8 are contained in slots 3.4,and 5 respectively (entPhysicalContainedIn). We know that the slots 3,4,and 5 as well as the port at 2 are contained in the chassis (1), that the chassis is contained in 0.
7) We know that the slots 3,4,and 5 are positioned relatively in the module as 1,2,and 3 respectively (entPhysicalParentRelPos). We also know that the port number of a port within a module is the entPhysicalParentRelPos as well. Any other contained entity (including the modules) have 1 as the entPhysicalParentRelPos.
8) We know that only modules and the chassis are field-replaceable units (entPhysicalIsFRU=1), and ports and slots/containers are not field-replaceable.

We will only focus on the following fields of the entPhysicalTable, as they are the only ones relevant to using entPhysicalTable as a view on physical entity data.

EntPhysicalEntry ::= SEQUENCE {
entPhysicalIndex Index of table, starting at 1
entPhysicalContainedIn index of entity contained in. Use 0 for the chassis
entPhysicalClass ports=10, chassis=3, container=5, module=9
entPhysicalParentRelPos port number for ethernet, 1 for 1000-Base-T, 0 for chassis, slot number for slots, and 1 for modules
entPhysicalName "FlexiRouter" for the chassis,
entPhysicalHardwareRev
entPhysicalIsFRU true=1 for chassis, false=2 for ports.
}

In this example, we need separate tables for ports, slots and modules, and input default data for the maximum configuration. For modules it is:

sqlite>CREATE TABLE Modules (slotNumber integer, hardwareRev string, moduleType string, isPresent integer, entPhysicalIndex integer);
sqlite>INSERT INTO "Modules" VALUES(1,'','',0,6);
sqlite>INSERT INTO "Modules" VALUES(2,'','',0,7);
sqlite>INSERT INTO "Modules" VALUES(3,'','',0,8);



Notice how the values for entPhysicalIndex are 6, 7, 8. Also note that isPresent=0 because nothing is present yet.

For the ports, it is a bit more tedious. Again, the port indices start at 9, and go up as specified above, and isPresent is 0 for all.

sqlite>CREATE TABLE AccessPorts (slotNumber integer, portNumber integer, portType string, isPresent integer, entPhysicalIndex integer, entPhysicalContainedIn );
sqlite>INSERT INTO "AccessPorts" VALUES(1,1,'',0,9,6);
sqlite>INSERT INTO "AccessPorts" VALUES(1,2,'',0,10,6);
sqlite>INSERT INTO "AccessPorts" VALUES(1,3,'',0,11,6);
sqlite>INSERT INTO "AccessPorts" VALUES(1,4,'',0,12,6);
sqlite>INSERT INTO "AccessPorts" VALUES(2,1,'',0,13,7);
sqlite>INSERT INTO "AccessPorts" VALUES(2,2,'',0,14,7);
sqlite>INSERT INTO "AccessPorts" VALUES(2,3,'',0,15,7);
sqlite>INSERT INTO "AccessPorts" VALUES(2,4,'',0,16,7);
sqlite>INSERT INTO "AccessPorts" VALUES(3,1,'',0,17,8);
sqlite>INSERT INTO "AccessPorts" VALUES(3,2,'',0,18,8);
sqlite>INSERT INTO "AccessPorts" VALUES(3,3,'',0,19,8);
sqlite>INSERT INTO "AccessPorts" VALUES(3,4,'',0,20,8);



Now let's suppose that we insert a "2-PoE-Port" module in slot 1, and a "4-Port-Ethernet" module in slot 3, and the plug&play software detects hardware revision "A001" for the 2 port PoE card and "B002" for the 4 port card. We'll need to update the module table as follows:

sqlite>update Modules set isPresent=1, hardwareRev="A001", moduleType="2-PoE-Port" where slotNumber=1;
sqlite>update Modules set isPresent=1, hardwareRev="B002", moduleType="4-Port-Ethernet" where slotNumber=3;



We'll need to update the port table as follows:

sqlite>update AccessPorts set isPresent=1, portType="PoE" where slotNumber=1 and portNumber>0 and portNumber <3;
sqlite>update AccessPorts set isPresent=1, portType="Ethernet" where slotNumber=3;



For simplicity we'll have a table for the slots:

sqlite> create table Slots (slotNumber integer, entPhysicalIndex integer);
sqlite> insert into Slots values (1,3);
sqlite> insert into Slots values (2,4);
sqlite> insert into Slots values (3,5);


We don't need tables for the chassis, or the 1000-base T port on the chassis, because they are always there, but we will need to represent them in a view for entPhysicalTable.

The view of entPhysicalTable is just a union of the views for the chassis, uplink, slots, modules and ports, and ordered by entPhysicalIndex. The SQL view below encodes this idea.

create view entPhysicalTable as
-- This part represents the whole chassis
select 1 as entPhysicalIndex, 0 as entPhysicalContainedIn, 3 as
entPhysicalClass, 0 as entPhysicalParentRelPos, "FlexiRouter" as
entPhysicalName, "AAXX01" as entPhysicalHardwareRev, 1 as
entPhysicalIsFRU
UNION
-- This part represents the 1000 Base T uplink
select 2 as entPhysicalIndex, 1 as entPhysicalContainedIn, 10 as
entPhysicalClass, 1 as entPhysicalParentRelPos, "1000-Base-T Uplink" as
entPhysicalName, "" as entPhysicalHardwareRev, 2 as entPhysicalIsFRU
UNION
-- This part represents the slots
select entPhysicalIndex, 1 as entPhysicalContainedIn, 5 as
entPhysicalClass, slotNumber as entPhysicalParentRelPos, "Slot " ||
slotNumber as entPhysicalName, "" as entPhysicalHardwareRev, 2 as
entPhysicalIsFRU from Slots
UNION
-- This part represents the modules
select entPhysicalIndex, entPhysicalIndex-3 as entPhysicalContainedIn,
9 as entPhysicalClass, slotNumber as entPhysicalParentRelPos,
moduleType || ' module/slot ' || slotNumber as entPhysicalName,
hardwareRev as entPhysicalHardwareRev, 1 as entPhysicalFRU from Modules
where isPresent=1
UNION
-- This part represents the ports
select entPhysicalIndex, entPhysicalContainedIn, 10 as
entPhysicalClass, portType || "/slot=" || slotNumber || "/port=" ||
portNumber as entPhysicalName, "" as entPhysicalHardwareRev, 2 as
entPhysicalIsFRU from AccessPorts where isPresent=1
ORDER BY entPhysicalIndex;



Because the structure of the entPhysicalTable view is the same as with the previous example, the queries for SNMP requests are the same as with the previous example.
The SNMP manager will issue SNMP-GET requests to the SNMP agent. The following query satisfies the request for the index @X:

sqlite>select * from entPhysicalTable where entPhysicalIndex = @X;



If the query yields no results, then the MIB objects do not exist.

The SNMP manager will issue SNMP-GETNEXT requests to the SNMP agent. The following query satisfies the request for the index @X:

sqlite>select * from entPhyscialTable where entPhysicalIndex >= @X;

Reader Feedback

Please leave useful criticism, praise or other feedback here.

submit
Show All

Logic Data Modeling

...videos on data design....

These videos show how to design your data and how to normalize it. Very useful.
Logic Data Modeling 1 - Introduction
by gvimontslice | video info

92 ratings | 47,971 views
automatically generated by YouTube

Video: SQLite Check Constraints

Design robust data using SQL CHECK constraints in SQLite

Good data design implies knowing exactly what is permitted in the fields of your data tables. Although most people specify data using types such as "integer", "float", or "string", these specifications are usually not enough. For example, if you have a data field to store ages of living people, you may specify the "age" field to be an integer, but in reality, you usually want to constrain ages to a number between 0 and 120 inclusively.

This video shows you how to use SQL CHECK constraints in SQLite to constrain your data.
powered by Youtube

SQLite Autoincrement

This module and video show you how to use the SQLite Autoincrement feature.

> sqlite3

Consider the following two tables:

> create table girls (id integer unique not null, name string);

In this table, the "id" field uniquely identifies the user, and the "name" field is the other value.

You must supply the "id" field for all tables.

> insert into girls (id, name) values (1,"May");
> select * from girls ;
> insert into girls (name) values ("Kay");

The second insert fails because you didn't supply an "id".

> insert into girls (id, name) values (1,"Shay");

This statement fails, of course, because the id is not unique.

The problem with this table is that you must know which values of "id" are free to use.

Fortunately there is a feature in SQLite that lets you do that. Consider this table:

> create table boys (id integer primary key autoincrement, name string);

This table is exactly the same except that the id field has been augmented with "autoincrement". This means that if you don't supply an "id" in an insert statement, it will pick one for you that is at least one higher than the highest value of "id" in the table.

> insert into boys (id,name) values (1,"Jay");
> select * from boys;
> insert into boys (name) values ("Ray");
> select * from boys;

The second insert worked because the "id" was auto-incremented.

FYI, in PostgreSQL, it is called "serial", in MySQL it is called "auto_increment", in MS SQL-Server it is called "identity", in db2 it is "generated always as identity", and for Oracle, you must create a separate sequence table and a trigger to make it happen.

Whenever you declare a field to be "autoincrementing", an entry is put into the sqlite_sequence table.

>.schema sqlite_sequence

>select * from sqlite_sequence;


You see here that the biggest value is xxx


You can change that value by updating sqlite_sequence.

> update sqlite_sequence set seq=242 where name="boys";
> select * from sqlite_sequence;

Now, if you insert something into boys, you would expect it to have an index of 243. Let's see

> insert into boys (name) values ("Nahasapeemapetilan");
> select * from boys;

You can now see that the last insert was assigned "243".
powered by Youtube

How To Use SQLite Database Triggers

SQLite supports database triggers. A trigger is basically an action that executes when another action is triggered on a database.

First create a table and add in some data.


C:\> sqlite3

sqlite> CREATE TABLE ceos (name string unique, salary integer, politics string);
sqlite> INSERT INTO "ceos" VALUES('Jack Welch',1000000,'Republican');
sqlite> INSERT INTO "ceos" VALUES('Carly Fiorina',5000000,'Republican');
sqlite> INSERT INTO "ceos" VALUES('Erik Schmidt',400000,'Democrat');
sqlite> INSERT INTO "ceos" VALUES('Lee Iacocca',1,'Democrat');
sqlite> INSERT INTO "ceos" VALUES('Kim Polese',600000,'Republican');



Then add in a table to record salary changes. This table will initially have nothing in it. It records content, time stamp, and the person who changed it. In this example, the value is set to "Flunkie", but if you use this idea in a computer program, you could make it the user name of the person who is making this change through your program (while logged in as himself).


sqlite> CREATE TABLE recorded_salary_changes (id integer primary key autoincrement, content text, time_of datetime, change_by string);



Populating this table manually is a pain. However, we can use a trigger to do this:


sqlite> CREATE TRIGGER record_salary_changes UPDATE OF salary on ceos
... begin
... insert into recorded_salary_changes (content, time_of, change_by) values ("name: " || old.name || ", old salary:" || old.salary || ", new salary: " || new.salary , datetime('now'), "Flunkie");
... end;
sqlite>



The trigger basically tells SQLite to add a row to the recorded_salary_changes table every time somebody updates the salary in the ceos table. It records the old and new salaries, the time stamp, and the name of the user who changed it.


sqlite> select * from recorded_salary_changes;


Nothing in the table.


sqlite> update ceos set salary=2 where name="Lee Iacocca";

sqlite> select * from ceos;
sqlite> select * from recorded_salary_changes;


Notice that the salary is updated for Iacocca, and there is an entry in the recorded_salary_changes table.

Now try this:


sqlite> update ceos set salary=1200000 where politics="Democrat";

sqlite> select * from ceos;
sqlite> select * from recorded_salary_changes;


Notice that two of the salaries are changed (Schmidt & Iacocca), and that there are 2 new entries in the recorded_salary_changes table with the same time stamp. That is because the triggered fired twice for this query because two salaries were affected by the update.
powered by Youtube

SQLite UNION, INTERSECT, and EXCEPT

SQLite UNION and INTERSECTION and EXCEPT

SQL can be used to quickly find out unions and intersections of sets.

Consider the following three tables


sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE shortcolours (name string unique);
INSERT INTO "shortcolours" VALUES('violet');
INSERT INTO "shortcolours" VALUES('purple');
INSERT INTO "shortcolours" VALUES('ultraviolet');
INSERT INTO "shortcolours" VALUES('indigo');
INSERT INTO "shortcolours" VALUES('blue');
INSERT INTO "shortcolours" VALUES('navyblue');
INSERT INTO "shortcolours" VALUES('green');
INSERT INTO "shortcolours" VALUES('yellow');
CREATE TABLE longcolours (name string unique);
INSERT INTO "longcolours" VALUES('red');
INSERT INTO "longcolours" VALUES('orange');
INSERT INTO "longcolours" VALUES('infrared');
INSERT INTO "longcolours" VALUES('yellow');
INSERT INTO "longcolours" VALUES('green');
CREATE TABLE colours (name string unique);
INSERT INTO "colours" VALUES('red');
INSERT INTO "colours" VALUES('orange');
INSERT INTO "colours" VALUES('infrared');
INSERT INTO "colours" VALUES('yellow');
INSERT INTO "colours" VALUES('green');
INSERT INTO "colours" VALUES('violet');
INSERT INTO "colours" VALUES('purple');
INSERT INTO "colours" VALUES('ultraviolet');
INSERT INTO "colours" VALUES('indigo');
INSERT INTO "colours" VALUES('blue');
INSERT INTO "colours" VALUES('navyblue');
INSERT INTO "colours" VALUES('chartreuse');
INSERT INTO "colours" VALUES('mauve');
INSERT INTO "colours" VALUES('olive');
INSERT INTO "colours" VALUES('white');
INSERT INTO "colours" VALUES('black');
INSERT INTO "colours" VALUES('brown');
COMMIT;
sqlite>

select * from colours;
select * from shortcolours;
select * from longcolours;



To see colours in common on the lists;


select * from colours intersect select * from shortcolours;

select * from colours intersect select * from longcolours;

select * from shortcolours intersect select * from longcolours;


To see all colours in lists,


select * from shortcolours union select * from longcolours;

select * from shortcolours union select * from colours;



Now consider this:


select * from longcolours;

select * from longcolours intersect select * from shortcolours;



To get the colours that are in longcolours but NOT in short colours, do this:


select * from longcolours except select * from shortcolours;


The except statements removes the intersecting elements

Finally, if you want to see the which colours are in the colours table, but not in the long and short tables, you do this


select * from colours EXCEPT select * from (select * from shortcolours union select * from longcolours) as longshort;

powered by Youtube

SQLite Foreign Keys

This video shows how to use foreign keys with SQLite to achieve better data integrity.
powered by Youtube

When to use SQL vs NoSQL

Adam Wiggins of heroku.com has posted a great article on various database and persistence technologies. He gives some very clear and sensible guidance that shows when to use SQL and when to use something else.

Database Constraints are good

Xavier Shay has just published an article which states that database constraints are good because they help developers understand the problem domain in which they are working.

Good food for thought.

How Database Engines Work

Dr. Hipp, the author of SQLite, gives a good presentation on how database engines work.
powered by Youtube

SQLite Functionally Independent Attributes

...less code, less bugs, all good.

When designing data tables, it is important to keep the attributes functionally independent of each other.

Why? Because when attributes are functionally independent, you are less likely to have bugs, and you usually don't have to write as much code.

The challenge of data schema design is to keep table attributes independent of each other. I'll show a bad example and then a good example.

For example, suppose you have a table

create table xmen (name string, approx_age integer, is_curly_hair boolean, is_straight_hair boolean);


Let's insert some data for a straight haired Jay and a curly haired Kay.

insert into xmen values ("Jay", 33, 0, 1);
insert into xmen values ("Kay", 22, 1, 0);

In SQLite, false is 0 and true is 1.

Now suppose we look at the data a year later without knowing when the data was entered. Immediately, the age is wrong because the age is implicitly dependent on when the data was entered. We say that the age is "functionally dependent" on the date of data entry. In this situation, you would have bad data that couldn't be fixed accurately. To kind of fix it, you would have to guess when Kay and Jay were added, add a field to the table, and hope for the best. Very bad!

Now suppose that Kay decides to straighten her hair. It would be tempting to then just update the data with

update xmen set is_straight_hair=1 where name="Kay";

However, that would be wrong because if you look at the data, Kay's hair is curly and straight.

select * from xmen;

The is_straight_hair attribute being true functionally depends on the is_curly_hair attribute being false. The correct SQL would be

update xmen set is_straight_hair=true, is_curly_hair=false where name="Kay";

In this trivial example that I have given you, this is not a problem. However, there are lots of systems where one table may have a large number of attributes that are not all well understood by the all of the software developers. It would be only natural for them to update one attribute without the other.

How to fix the problem? Pick data fields that are invariant, and independent of each other. For example:
The date of birth doesn't change for people the way age does every year.
Hair texture is probably a better attribute that captures the value of attributes is_straight_hair and is_curly_hair.


Let us try the new table.

create table zmen (name string, date_of_birth datetime, hair_texture string);

insert into zmen values ("Jay", '1977-01-01', "straight");
insert into zmen values ("Kay", '1988-05-09' , "curly");

To ask the database their name, age, and if their hair is straight,

select name, date('now') - date(date_of_birth) as approx_age from zmen;

Because the age is only dependent on the current date and the year of birth (which never changes or gets out of date), you never have to worry about the "age" field getting stale.

To change hair texture for example

update zmen set hair_texture="straight" where name="Kay";

You know that your attributes are functionally independent because there are no other attributes that relate to hair colour, and when you change one attribute, you don't have to change any others to keep the data consistent.

Having functionally independent attributes lets you avoid writing code to manage data that could become inconsistent because a designer didn't know about the functional dependency. It does take some extra work upfront because:
You have to put some thought in checking attributes against each other to ensure that they are functionally independent.
you often need to figure out a formula to calculate data that is derived from the attributes of the table (usually quantities such as age, averages, and the lot).



Less code. Less errors. All good!
powered by Youtube

by

JayGodse

I am a software developer.

Feeling creative? Create a Lens!

Explore related pages

Good Book on Database Design 

Enterprise Rails

Enterprise Rails

by: Dan Chak

Amazon Price: $15.55 (as of 02/14/2012)Buy Now

This book is a great tutorial on designing an industrial-strength database. Although this book focuses on the Ruby/Rails platform, Dan Chak devotes a lot of time on the merits of designing a robust database schema.

Related Tags