Home > Code Snippets, Tips > sqlite3; I am falling in love..

sqlite3; I am falling in love..

After about a decade of php and mysql, with just deviating only in the scripting or language area, today morining, I felt like digging into the sqlite3, which a lot of people are referring to, and some good RIA players are paying attention to also. The big names that sqlite website brags about are Adobe, Symbian and Firefox.

Jumping straight into the hands on, though for a prologe or introduction I will brief the respective situation. Kyle Newton of Galaxy WD, a client of Saturn SPL wanted a website to have the famous USAPostalCodes db, but he wanted the system to use pre imported flatfiles, and with about a couple of hours, we could pre import the USAPostalCodes db to the recommended format, though the whole data size was now 170MB. The pages where being generated on the webserver under 1/100 th of a second. Along with php ob_gzhandler was giving a good output. The client was almost satisfied, though he had some other views and that was not about the technology.

Seeing that the database was not having any modifications while the system is running, I though about giving it a trial, and it struck me that I would try sqlite. But I had never used this before. How will I stick to my decision and make it a success? Well like all experienced code integrators know, googling would be the first option. And the second variant of keyword struck gold. Which took me to the sqlite3 wiki at sourceforge. Then it was just a matter of firing up my favorite editor, which happens to be gPHPEdit. I will go through the steps and explain the code which I have used.

$link sqlite3_open(dirname(__FILE__).'/db.sql3');

Well, atleast some of you would be wondering why I went to the complicated way of using dirname when I could use a preset path. Actually it is just a practice, that mostly I may develop things on my local machine, demonstrate to the clients or their clients on a test server, and finally host it with tom, dick or harry or even go to a dedicated box. So it is better to have the variable in place, that the path does not change when the porting is done. By the way, I deviated from the main track. Okay the straights are that this would create the file or db, as you can call it if it does not exist, opens the db, and returns a resource for later use.

The next part was tricky, that you should remember I have only mysql exposure, and that too learned real time. To make the story short, I ended up installing SQLite Database Browser for ubuntu-desktop. Thanks to the ubuntu maintainers, that I could do this by searching in the Synaptic Package Manager, and not mess out with any build files and make files. Coming back to our scenario. I used the GUI tool to create the database, then copied the queries from the logs. Combined this to the second stage of my script.



if ( filesize(dirname(__FILE__).'/db.sql3') == ){
    
$init = array(
        
'CREATE TABLE states (fullname char(35), id INTEGER PRIMARY KEY, state char(2))',
        
'CREATE TABLE cities (fullname char(35), lat decimal(12,6), long decimal(12,6), states_id NUMERIC)',
        
'CREATE UNIQUE INDEX getCitybyName ON cities(fullname ASC)',
        
'CREATE INDEX getCitiesByState ON cities(states_id ASC)',
        
'CREATE UNIQUE INDEX getStateByName ON states(fullname ASC)'
    
);
    foreach(
$init as $query){
        
sqlite3_exec($link,$query);
    }
}

I dont think that needs any explanation. In fact what it does is check the file size, I used this method because the other standard method confused me. Since I am just going to use this sqlite db just for the USAPostalCodes to find cities near by a city, I could ignore a lot of the data from the available USA-zip-codes-database-DELUXE-csv. Should comment that the csv is pretty exhaustive. From all the mumbo in the csv db, I needed only the above set. Okay now it is time to feed on the csv, and it was peanuts since CSV was being used heavily a lot. Still here we needed some sort of filtering before getting the csv to be parsed, and there it was popen, which was exactly what would help me, when used like,

$fp popen("grep ',".'"P"'.",' database.csv",'r');

and lo the csv would be filtered that any record with one of the field having only the letter P would be filtered into the piped shell, and that would then be processed.

while($data fgetcsv($fp)){
    if(empty(
$data[20])) continue;
    
$state = array('state' => $data[19], 'fullname' => $data[20]);
    
$rs sqlite3_query($link,sprintf(“SELECT id FROM states where fullname='%s',$state['fullname']));
    
$rd sqlite3_fetch($rs);
    if(!empty(
$rd)){
        
$state_id $rd[0];
    }else{
        
$query mkSqliteSQL('states'$state);
        
sqlite3_exec($link,$query);
        
$state_id sqlite3_last_insert_rowid($link);
    }
    
$city = array('states_id' => $state_id,'fullname' => $data[24], 'lat' => $data[16], 'long' => $data[17]);
    
$query mkSqliteSQL('cities'$city);
    
sqlite3_exec($link,$query);
}

I have a habit or practice of creating any record like the way we get if we give a fetch_array on the resource result, and that is what is done in lines 3 and 11, where $state and $city is defined as an associative array. This is converted to a properly escaped query using the mkSqliteSQL custom function listed below.


function mkSqliteSQL($table$arr){
    
// just consider inserts only
    
$rv 'INSERT INTO '$table ' (' join(','array_keys($arr) ). ') VALUES (';
    foreach(
array_values($arr) as $k => $val){
        if(
$k 0$rv .= ',';
        
$rv .= sprintf("'%s'"$val);
    }
    
$rv .= ')';
    return 
$rv;
}


Running the above code from command line with the USA-zip-codes-database-DELUXE-csv, kept as database.csv, in the same folder will show the city ids flashing on the screen and when it is finished, the csv is converted to sqlite3 db.

While trying to use this, I had to calculate the distance from one city to another such that I could filter out a pool of nearby cities, and the accuracy need was not too tight, so I tried the latitude = longitude equation, ROUND( SQRT( POW((69.1 * ( – lat)), 2) + POW((53 * ( – long)), 2)), 1) AS distance, and this bombed. Well on search through the Internet, to my dismay I found that the SQRT, and POW were not there in the sqlite. It was then only I noticed the last function documented on the API documentation, which is sqlite3_create_function.

sqlite3_create_function ($link'POW'2'pow');
sqlite3_create_function ($link'SQRT'1'sqrt');

Simple, that solved my problem, what it does is to attach a php function (it need not be a native php function) to a sqlite function as a callback. This rocked me!I am in love with sqlite.

I got through the ordeal in one day. The system is integrated with the clients site, and deployed for testing. I achieved by getting to know sqlite, the site was having a database which was lesser by 168MB. But the page generation times was really long, as compared to the flat file system. With the file based system page generation was less than 1/100th of a second, where as with the sqlite3 db, it was of the order of 45 to 50 micro seconds. But it sure offers the flexiblity. I am not permitted to disclose the website url, at this point of time. Once it is live, may be I will add it to this post, provided the client gives the permission.

Categories: Code Snippets, Tips Tags:
  1. No comments yet.
  1. No trackbacks yet.

− two = one