keepr

Today I would like to introduce keepr, a php cms/framework I am working on in my free time.

It all started in May/June 2007. I was without a job and needed something to help me deliver projects faster without forcing me to change the way I like to code. It impliments the way I see Model-View-Controller for the web environment using PHP XTemplate

I launched a GNU GPL version 3 at the following url http://code.google.com/p/keepr/ . There is no downloads yet by the full source is available at Source/Browse under trunk.

SQLite and SOCI

Now that I know that I can do SQL queries agains the SQLite database, my first objective is to create a C++ dataobject that will allow my yet to be created framework to interact with the database. But first I need to be able to actually access SQLite through code.

My search led me to SOCI which is a C++ Database Access Library. I quote from their website:

The idea is to provide C++ programmers a way to access SQL databases in the most natural and intuitive way.

From what I can see SOCI looks to be the best thing for what I want to achieve.

http://soci.sourceforge.net

SQLite - Updating Tables

What if I need to make changes to the data in my table? As an example, I forgot to insert Armourette's birthday and Johan is not active yet. This mean I will have to run an update query on the table.

SQLite query:

SQLite version 3.5.4
Enter ".help" for instructions
sqlite> .schema person
CREATE TABLE person
(
'ID' INTEGER PRIMARY KEY AUTOINCREMENT,
'FIRSTNAMES' TEXT,
'SURNAME' TEXT,
'NAME' TEXT,
'BIRTHDATE' DEFAULT CURRENT_DATE,
'TITLE' TEXT,
'ACTIVE' TEXT(1) DEFAULT 'N'
);
sqlite> select * from person;
1|Johannes|Strydom|Johan|1977-02-22|Mr|N
2|Armourette|Strydom|Armourette|2008-01-17|Mrs|Y
sqlite> UPDATE person SET ACTIVE='Y' WHERE ID=1;
sqlite> UPDATE person SET BIRTHDATE='1983-07-05' WHERE ID=2;
sqlite> select * from person;
1|Johannes|Strydom|Johan|1977-02-22|Mr|Y
2|Armourette|Strydom|Armourette|1983-07-05|Mrs|Y
sqlite>

Using the primary key ID I am able to quickly update the correct data.

SQLite - Creating Tables

What is a database without tables?

My next chalenge in SQLite is to create tables. Using the SQLite create table documentation I am able to create tables without any hassle.

Say for example I want to store data about a person in the database and to do that I want to create a table with fields for firstnames, surname, preferred name, birthdate,title and some sort of primary reference that should be indexed and that should auto increment whenever a new row is added to the table. I also want to indicate in this table whether the person is active.

So I start up SQLite and attached my database;

SQLite version 3.5.4
Enter ".help" for instructions
sqlite> ATTACH DATABASE 'myDB.fl' AS myDB;
sqlite> .databases

Ok so I now have everything in place to start creating the table.

sqlite> CREATE TABLE myDB.person
...> (
...> 'ID' INTEGER PRIMARY KEY AUTOINCREMENT,
...> 'FIRSTNAMES' TEXT,
...> 'SURNAME' TEXT,
...> 'NAME' TEXT,
...> 'BIRTHDATE' DEFAULT CURRENT_DATE,
...> 'TITLE' TEXT,
...> 'ACTIVE' TEXT(1) DEFAULT 'N'
...> );

Fine so far. Now to insert data!

sqlite> INSERT INTO myDB.person('FIRSTNAMES','SURNAME','NAME','BIRTHDATE','TITLE
') VALUES ('Johannes','Strydom','Johan','1977-02-22','Mr');

No problemo. To see whether everything works I do a SELECT

sqlite> SELECT * FROM myDB.person;
1|Johannes|Strydom|Johan|1977-02-22|Mr|N

And another insert to test the default date.

sqlite> INSERT INTO myDB.person('FIRSTNAMES','SURNAME','NAME','TITLE'
,'ACTIVE') VALUES ('Armourette','Strydom','Armourette','Mrs','Y');
sqlite> SELECT * FROM myDB.person;
1|Johannes|Strydom|Johan|1977-02-22|Mr|N
2|Armourette|Strydom|Armourette|2008-01-17|Mrs|Y
sqlite>

Wasn't that easy? Now If I can only get SQLite to show me the table schema. For some reason it does not want to do that.

sqlite> .schema myDB.person
sqlite>

So what worked:

• Create Table (passed)
• Insert (passed)
• Auto Increment (passed)
• Default values (passed)
• .schema (failed)

There must be a configuration or command somewhere for schema which I must have missed.

I solved the problem by exiting SQLite and opening my database as a parameter to the SQLite command line executable.

C:\sqlite-3_5_4\sqlite3.exe myDB.fl
SQLite version 3.5.4
Enter ".help" for instructions
sqlite> .schema person
CREATE TABLE person
(
'ID' INTEGER PRIMARY KEY AUTOINCREMENT,
'FIRSTNAMES' TEXT,
'SURNAME' TEXT,
'NAME' TEXT,
'BIRTHDATE' DEFAULT CURRENT_DATE,
'TITLE' TEXT,
'ACTIVE' TEXT(1) DEFAULT 'N'
);
sqlite>

So the master database is now my SQLite database! Isn't that cool? :)

With my database as the master I am also able to get the schema by running the following SELECT statement

sqlite> SELECT * FROM sqlite_master WHERE type IN ('table') AND tbl_name NOT LIKE 'INFORMATION_SCHEMA_%' ;
table|person|person|2|CREATE TABLE person
(
'ID' INTEGER PRIMARY KEY AUTOINCREMENT,
'FIRSTNAMES' TEXT,
'SURNAME' TEXT,
'NAME' TEXT,
'BIRTHDATE' DEFAULT CURRENT_DATE,
'TITLE' TEXT,
'ACTIVE' TEXT(1) DEFAULT 'N'
)
table|sqlite_sequence|sqlite_sequence|3|CREATE TABLE sqlite_sequence(name,seq)
sqlite>

Conclusion?

• .schema (!passed!)

SQLite - my journey starts

I do C++ programming in my free time and one of the challenges I am facing at the moment is databases. I have a project that require me to use a database for storing data.

Most of my development is taking place on my gaming PC with Windows XP Professional (Although I also work a bit on Fedora Linux)

My search led met to Sqlite

Sqlite is :

• Free as in the public domain which means that everybody can use and modify it.
• Small, very small. The binary is about 427KB on my PC, unzipped
• Fast, Faster than other popular client/server database engines for most common operations.
• SQL support although some features are not supported.
• Cross-Platform: Linux (unix), MacOSX, OS/2, Win32 and WinCE are supported out of the box
• Accessible through C++
• Used in one way or another by well known companies

and has many more features...

I downloaded the Windows command-line version and started it up.

SQLite version 3.5.4
Enter ".help" for instructions
sqlite>

Figuring out how to create a database was very easy using the Sqlite language syntax as on the Sqlite website

sqlite> ATTACH DATABASE 'myDB.fl' AS myDB;

That command created a database for me and I was able to see where it is located by entering .databases at the Sqlite command prompt.

sqlite> .databases
seq name file
--- ---- ---------------------
0 main
2 myDB C:\sqlite-3_5_4\myDB.fl

So far I like Sqlite allot. It's easy to use and it just simply works.