User avatar
mhmediaonline
Posts: 27
Joined: Mon Feb 04, 2013 1:57 pm
Location: Yorkshire. By 'eck
Contact: Website

Can anyone recommend a small database?

Thu Aug 08, 2013 8:56 am

Hi folks!

I'm experimenting with a Raspberry Pi (B) and a cheapo laser barcode scanner sourced from eBay. I'm looking to read my scanned data into a small database using a Python program. This data will be interrogated via Nginx and I'm running Nginx Light with PHP-fpm using around 200Mb of memory, so the Pi is not stressed.

So, I need some opinions on which database I could use. The idea is that there would not be many writes (maybe 100 a day), but there would be quite a few reads as people interrogate the database via Nginx. I'd only need to index one field - product number - so nothing fancy would be needed.

I'm currently running MySQL on another Pi and this would be serious overkill, so.. what are your suggestions? :)

User avatar
mhmediaonline
Posts: 27
Joined: Mon Feb 04, 2013 1:57 pm
Location: Yorkshire. By 'eck
Contact: Website

Re: Can anyone recommend a small database?

Thu Aug 08, 2013 11:16 am

Further investigation shows that the version of PHP installed on the 'Pi includes an in-built database driver for the DB4 database: this jives with my policy of not installing extra PHP stuff unless I really have to. Have a look at this very useful HOWTO page at http://architects.dzone.com/articles/be ... abases-php

Right now I'm trying to do some development work on the Pi using a 7inch tablet ssh'd in and it's a pain so I'll have another go tonight on a full size screen and report back. Feel free to come up with other suggestions though!
---
Raspberry Pi - One of my Five-a-Day

User avatar
mhmediaonline
Posts: 27
Joined: Mon Feb 04, 2013 1:57 pm
Location: Yorkshire. By 'eck
Contact: Website

Re: Can anyone recommend a small database?

Thu Aug 08, 2013 2:55 pm

Here's an update: I finally got some dirty working code together: I can create a database using Nginx/PHP which can be read by a Python script. The challenge was to (ideally) find a database already compiled into PHP, and after much faffing around I was able to achieve this. In fact you can run the PHP script using the command line: php scriptname.php

First thing to do was to run a PHP script ripped from the PHP manual to find out was compiled in:

Code: Select all

<?php
foreach (dba_handlers(true) as $handler_name => $handler_version) {
  $handler_version = str_replace('$', '', $handler_version);
  echo "Supported handlers $handler_name: $handler_version\n";
}
?>
and it duly returned this short list:
Supported handlers cdb cdb_make db4 inifile flatfile qdbm
Bearing in mind that I wanted to use Python 2.7 as supplied with the Pi, I went with DB4 which according to the PHP manual is
"for Oracle Berkeley DB 4 or 5. This option is available from PHP 4.3.2. It can be used with BDB 5 from PHP 5.3.3"
So, here's a quick PHP script to create a load up some data:

Code: Select all

<?php
$id	= dba_open('test.db', 'c', 'db4');
$data	= array('key 1' => 'some value', 'key 2' => 'another value', 'key 3' => 'final value');
foreach ($data as $key=>$value)
   {
   dba_insert($key, $value, $id); 
   }
dba_optimize($id);
dba_close($id);
?>
and here's the Python code to read and display it. Note that I had to install the python-bsddb3 package using apt-get.

Code: Select all

#!/usr/bin/env python
from bsddb3 import db

fruitDB = db.DB()
fruitDB.open('test.db',None,db.DB_BTREE,db.DB_DIRTY_READ)
cursor = fruitDB.cursor()
rec = cursor.first()

while rec:
	# print out our (list) record
	print rec
	# then, just the key value
	somekey = rec[0]
	print somekey
	rec = cursor.next()

fruitDB.close()
And that's it for now - quick, dirty, but it's a start. I'll post more when I get it working the other way (I.e. created using Python then read/queried using PHP.
---
Raspberry Pi - One of my Five-a-Day

guruevi
Posts: 7
Joined: Fri Aug 09, 2013 2:53 pm

Re: Can anyone recommend a small database?

Fri Aug 09, 2013 8:48 pm

I would recommend SQLite, BDB is Oracle-based so you may or may not be able to use it in the near future. SQLite is supported on all platforms currently in existence even JavaScript.

User avatar
peepo
Posts: 305
Joined: Sun Oct 21, 2012 9:36 am

Re: Can anyone recommend a small database?

Sat Aug 10, 2013 10:01 am

had you looked at nodejs & redis? a good fit with nginx....

not for php

User avatar
mhmediaonline
Posts: 27
Joined: Mon Feb 04, 2013 1:57 pm
Location: Yorkshire. By 'eck
Contact: Website

Re: Can anyone recommend a small database?

Mon Aug 12, 2013 10:26 am

guruevi wrote:I would recommend SQLite, BDB is Oracle-based so you may or may not be able to use it in the near future. SQLite is supported on all platforms currently in existence even JavaScript.
Hi Gurievi,

Yes, I considered SQLite (which i use on my big PC) but I considered this too "disk heavy". Mind you, I could be wrong - I need to run a comparative test against BDB. Right now though, I'm happy that BDB is compiled into PHP.

As regards it being an Oracle product I was most disappointed to find that the Sleepycat Software link went to the Oracle site and although this isn't an issue right now, you're correct and I need to factor this possible withdrawal of support at a later date.
---
Raspberry Pi - One of my Five-a-Day

User avatar
mhmediaonline
Posts: 27
Joined: Mon Feb 04, 2013 1:57 pm
Location: Yorkshire. By 'eck
Contact: Website

Re: Can anyone recommend a small database?

Mon Aug 12, 2013 10:27 am

peepo wrote:had you looked at nodejs & redis? a good fit with nginx....

not for php
I haven't actually, but thanks for the heads-up. PHP is a bit of deal-breaker right now but it may not be so in the near future.
---
Raspberry Pi - One of my Five-a-Day

Thaddy
Posts: 173
Joined: Wed Mar 06, 2013 11:28 am

Re: Can anyone recommend a small database?

Mon Aug 12, 2013 11:05 am

mhmediaonline wrote:
Hi Gurievi,

Yes, I considered SQLite (which i use on my big PC) but I considered this too "disk heavy". Mind you, I could be wrong - I need to run a comparative test against BDB. Right now though, I'm happy that BDB is compiled into PHP.

As regards it being an Oracle product I was most disappointed to find that the Sleepycat Software link went to the Oracle site and although this isn't an issue right now, you're correct and I need to factor this possible withdrawal of support at a later date.
If there's anything that isn't disk heavy, it is sqlite. That's why almost the entire world is using it for any serious light database applications.
I am a little bemused why you are made to think otherwise?

In that context: the others mentioned? I never heard about them. (imnsho: Probably not a good sign, although they probably work.)


From a technical point of view:
Sqlite doesn't take up more space than necessary, for example, as opposed to most if not all other serious databases. That makes it at least one of the lightest on disk use since it only uses what an individual field entry needs, not what - at best, in case of the competition - an individual field needs for all entries: its maximum size! in the case of fixed length fields.
As far as most of us know that is pretty unique amongst its rivals and almost like flat file but with full blown SQL as a bonus. If you don't need SQL, there are alternatives that are indeed even less disk usage prone at the cost of performance and usability.

User avatar
mhmediaonline
Posts: 27
Joined: Mon Feb 04, 2013 1:57 pm
Location: Yorkshire. By 'eck
Contact: Website

Re: Can anyone recommend a small database?

Mon Aug 12, 2013 2:39 pm

If there's anything that isn't disk heavy, it is sqlite. That's why almost the entire world is using it for any serious light database applications. I am a little bemused why you are made to think otherwise?
Probably down to my misunderstanding of how SQLite operates ;) I was under the impression that there was a lot of disk I/O involved.

As regards my own setup though, i will stick with the Berkeley setup for now as it's compiled into the PHP I'm using with Nginx Light. If it proves to be a pain further down the line I can always switch over.

Code: Select all

DBA support 	enabled
Supported handlers 	cdb cdb_make db4 inifile flatfile qdbm 
---
Raspberry Pi - One of my Five-a-Day

Thaddy
Posts: 173
Joined: Wed Mar 06, 2013 11:28 am

Re: Can anyone recommend a small database?

Mon Aug 12, 2013 2:45 pm

mhmediaonline wrote:
Probably down to my misunderstanding of how SQLite operates ;) I was under the impression that there was a lot of disk I/O involved.
This depends on the settings - including OS settings - and to a certain extend how sqlite is compiled.
In general: do you want to cache in memory and are you comfortable with that? (Think ACID)
Btw I would still recommend sqlite over Berkeley. Run some tests.

User avatar
mhmediaonline
Posts: 27
Joined: Mon Feb 04, 2013 1:57 pm
Location: Yorkshire. By 'eck
Contact: Website

Re: Can anyone recommend a small database?

Mon Aug 12, 2013 3:42 pm

Thaddy wrote:
mhmediaonline wrote:
Probably down to my misunderstanding of how SQLite operates ;) I was under the impression that there was a lot of disk I/O involved.
This depends on the settings - including OS settings - and to a certain extend how sqlite is compiled.
In general: do you want to cache in memory and are you comfortable with that? (Think ACID)
Btw I would still recommend sqlite over Berkeley. Run some tests.
I'd be okay to cache in memory but would appreciate it if there was a bit more memory on the Pi. At this stage though, there's practically no database reads going on - it's 99% straight inserts, acting as a logger.. Might be fun to try and tune SQLite though!
---
Raspberry Pi - One of my Five-a-Day

Return to “Advanced users”