Experimenting with SQLite3

Hardkap » Pritlog - Blogging made simple

Experimenting with SQLite3

(6 posts) (2 voices)

Started 3 years ago by john
Latest reply from Prit

Tags:

john
Member

Hi Prit,

I did some research about sqlite3 that might interest you.

A. what I wanted to understand ?

- the first stable sqlite3 version (3.07) was released back in September 2004 !
- sqlite3 seems under active development (current version is 3.6.11 dated Feb 2009)

3 questions
- why aren't more php scripts using it ?
- why isn't more info available on the web
- what specific improvements does it offer when compared to sqlite2 ?

B. what I did

1. reexamined my php info, and discovered that both sqlite2 and sqlite3 were available

- PDO Drivers: sqlite2, sqlite, mysql

- SQLite support: enabled
- SQLite Library: 2.8.17
- SQLite Encoding: iso8859

- PDO Driver for SQLite 3.x: Enabled
- SQLite Library: SQLite Library 3.3.16

"PDO_SQLITE is a driver that implements the PHP Data Objects (PDO) interface to enable access to SQLite 3 databases. This extension provides an SQLite v3 driver for PDO. SQLite V3 is NOT compatible with the bundled SQLite 2 in PHP 5, but is a significant step forwards, featuring complete utf-8 support, native support for blobs, native support for prepared statements with bound parameters and improved concurrency"

2. my next step was to find a script, to test if it worked on my hosting and see how the code was implemented

- after several failures, I found a sqlite3 manager script at Hotscripts (it was posted Feb 13 2009) http://www.hotscripts.com/listing/sqlite3-database-manager

- I uploaded the script to my test blog so you can access it:
http://www.enbaires.com/pritlog_test/sqlite3/index.php
login user: a pass: a

Everything worked ! confirming that sqlite3 is available at my hosting

- I also uploaded a text copy of the code so you can give it a look:
http://www.enbaires.com/pritlog_test/sqlite3/index.txt

C. conclusions

from what I was able to read, its still unclear if sqlite3's performance is significantly better than v2 when executting most common operations.

I'm also unable to determine if the new features are worth to be considered for Pritlog future improvements in particular, and for me to use sqlite3 in any new projects in general.

If possible let me know your comments, I'm curious about your opinions.

Some technical info I readed about v3:
http://www.sqlite.org/version3.html
http://www.sqlite.org/lockingv3.html

Posted 3 years ago #
Prit
Pritlogger

Hi John,

I had looked at Sqlite3 and did not choose this for Pritlog due to the following reasons:
1 - I read in several places that Sqlite3 is not as widely available on PHP5 installations by default. But Sqlite2 is available on most PHP5 installations.
2 - As mentioned by you, it is unclear how it might improve the performance for an app like Pritlog. The additional features may not help Pritlog.

Answers to your q's.
- why aren't more php scripts using it ?
A. Must be the same reasons I wrote above.
- why isn't more info available on the web
A. Not sure
- what specific improvements does it offer when compared to sqlite2 ?
A. Again, I think the ones you mentioned.

Unless Sqlite3 gets widely adopted in PHP and is available by default in PHP installations, it may never get used in Pritlog.

But, if you want to learn, then you can try an experiment and convert Pritlog to use Sqlite3 instead. Just as a learning experiment, this might help. I am sure, the commands have to be converted and the database has to be converted.

Posted 3 years ago #
john
Member

Re. converting sqlite2 to 3, I already installed a demo version of this software >
http://www.sqlabs.net/sqlitemanager.php I think it also includes features to create new db's and build commands

I'm still scratching my head: sqlite's official site is almost dominated by v3 info, 5 years passed since it was first released, and PDO_SQLITE appears as the only way to implement it in php, and that driver seems to work well (I couldn't find any negative comments).

My hosting is not precisely on the cutting edge of technology :), so there must be some advantages and be less uncommon among the best hosting providers.

Posted 3 years ago #
Prit
Pritlogger

I usually test Pritlog in atleast 5-6 different server setups, most of them are live ones and different hostings. And Sqlite2 worked out of the box on these and Sqlite 3 seemed to have some issues if I remember correctly.

Do let us know if you come across any really good value addition that Sqlite3 can bring to Pritlog.

Posted 3 years ago #
john
Member

Prit,

I sent a mail to the author of the sqlite3 manager script posted at Hotscripts, his answer:

"For me biggest difference is that version 3 allows to use blob type (to store binary data eg. files or images into the database itself) and support for fulltext search. Speed differences I don't know, I have heard that version 3 is little bit faster but I think that differences are marginal."

Based on the above, and considering that v2 is available in most PHP5 builds, version 2 seems clearly the best choice Pritlog's purposes.

Somebody said that we only learn by our own experience..
..I'm still learning (and testing Prit's patience :)

Posted 3 years ago #
Prit
Pritlogger

Thanks for doing the research John.

I am learning too (and you are not testing my patience, but giving ideas and thoughts for me to learn more :)

Posted 3 years ago #

RSS feed for this topic

Reply

You must log in to post.