Showing entries 1 to 10 of 24
10 Older Entries »
Displaying posts with tag: SQLite (reset)
When simple SQL can be complex

I think SQL is a very simple language, but ofcourse I'm biased.

But even a simple statement might have more complexity to it than you might think.

Do you know what the result is of this statement?


scroll down for the answer.

The answer is: it depends.

You might expect it to return false because the 3 items in the comparison are not equal. But that's not the case.

In PostgreSQL this is the result:

(1 row)

So it compares FALSE against FALSE, which results in TRUE …

[Read more]
SQLite vs MySQL

Today I got asked from a friend about the differences between SQLite and MySQL and in which cases it is better to use the first or the latter one. You can’t really compare these two, I will try to explain why this is the case and when it is better to use the first or the latter.

SQLite is a single-file based database which is useful for testing and for embedding in applications. This means that all the information is stored in a single file on a file system and you use a SQLite library to open this file to read and write your data.

MySQL is a database server which got different forms to store its data (engines) and will server the data to request comming from applications who connect to it.


  • Fast setup. You only need to include a library into your app.
  • Embedded. No need to think about …
[Read more]
Simple MySQL: Converting ANSI SQL to SQLite3

I was digging through some old project code and found this script. Sometimes one finds oneself in an odd situation and needs to convert regular SQL, say from a MySQL database dump, into SQLite3 format. There’s not too much else to say, but here is a script that helps with the process. It can likely be improved but this handles the items that came up during conversion on initial runs.

# AUTHOR: Matt Reid
# DATE: 2011-03-22
if [ "x$1" == "x" ]; then
   echo "Usage: $0 "
cat $1 |
grep -v ' KEY "' |   
grep -v ' UNIQUE KEY "' |
grep -v ' PRIMARY KEY ' |
sed '/^SET/d' |          
sed 's/ unsigned / /g' | 
sed 's/ auto_increment/ primary key autoincrement/g' |
sed 's/ smallint([0-9]*) / integer /g' | 
sed 's/ tinyint([0-9]*) / integer /g' |  
sed 's/ int([0-9]*) / integer /g' |      
sed 's/ character set [^ ]* / /g' |      
sed 's/ enum([^)]*) / varchar(255) /g' | 
sed 's/ on …
[Read more]
NIST::NVD::Store::SQLite3 1.00.00

It’s been released. Use this with NIST::NVD 1.00.00 and you will be able to perform immediate look-ups of CVE and CWE data given a CPE URN. For instance:

cjac@foxtrot:/usr/src/git/f5/NIST-NVD-Store-SQLite3$ perl Makefile.PL ; make ; make test ; cjac@foxtrot:/usr/src/git/f5/NIST-NVD-Store-SQLite3$ perl -MNIST::NVD::Query -MData::Dumper -e '
$q = NIST::NVD::Query->new(store    => q{SQLite3},database => q{t/data/nvdcve-2.0.db});
$cve_list = $q->cve_for_cpe( cpe => q{cpe:/a:microsoft:ie:7.0.5730.11} );
print Data::Dumper::Dumper { cve_list => $cve_list, first_cvss => $q->cve( cve_id => $cve_list->[0] )->{q{vuln:cvss}} }
$VAR1 = {
          'cve_list' => [
          'first_cvss' => {
                            'cvss:base_metrics' => { …
[Read more]
NIST::NVD CWE development – follow along

I’m in the process of getting the tests passing for the 0.03 release of NIST::NVD::Store::SQLite3 wherein our hero imports the CWE data and cross-indexes it with CVEs and CPEs.

Follow along and suggest some patches. I’m developing on Debian Wheezy, but I would very much like input from devs on other platforms.;a=summary

cjac@foxtrot:/tmp$ time git clone
Cloning into 'NIST-NVD-Store-SQLite3'...

real    0m32.757s
user    0m0.200s
sys     0m0.088s
cjac@foxtrot:/tmp$ ls NIST-NVD-Store-SQLite3/t/data/
cwec_v2.1.xml  nvdcve-2.0-test.xml

Publish your patches and I’ll fetch them, or you can submit them in udiff format and I’ll review/apply. Thanks for playing along!

[edit 20120216T1456 -0800] …

[Read more]

I published an SQLite3 storage back-end to NIST::NVD on the CPAN. It’s pretty quick. About as fast as the DB_File one, but without the down side of being tied to DB_File. It shouldn’t be too difficult to re-factor this code to any DBI-based database. MariaDB anyone?

I know it works on Debian. The nightly CPAN test results should come back shortly and I’ll find out how well it works on other platforms.

Same query, 3 databases, 3 different results

The SQL standard leaves a lot of room for different implementations. This is a little demonstration of one of such differences.

SQLite  3.7.4
sqlite> create table t1 (id serial, t time);
sqlite> insert into t1(t) values ('00:05:10');
sqlite> select t,t*1.5 from t1;
MySQL 5.6.4-m5
mysql> create table t1 (id serial, t time);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1(t) values ('00:05:10');
Query OK, 1 row affected (0.00 sec)

mysql> select t,t*1.5 from t1;
| t        | t*1.5 |
| 00:05:10 |   765 |
1 row in set (0.00 sec)

PostgreSQL 9.0.3
test=# create table t1 (id serial, t time);
NOTICE:  CREATE TABLE will create implicit …

[Read more]
451 CAOS Links 2011.07.29

Open Cloud Initiative launches. HP joins OpenStack. Oracle releases Java 7. And more.

# The Open Cloud Initiative launched to drive open standards in cloud computing.

# HP announced its support for OpenStack.

# Oracle announced the availability of Java SE 7. The Apache Software Foundation warned of index corruption and crashes in Apache Lucene and Solr.

# Nebula …

[Read more]
Open Database Camp 2011 opens today!
The Open Database Camp 2011 opens today with the Welcome Party, starting today at 7pm CEST. The party (with good Italian food and drinks) is open to all the ones who have registered in the Attendees list.
By car you have to reach Pula, take Via Nora (Nora Street), than Via Sant'Efisio (Sant'Efisio Street), until the end, directly to the party location.
Organisers will also make a bus available on Friday 6 May, leaving from Pula Hotels (Nora Club Hotel - Villa Madau - Baia Di Nora - Is Molas - Marin Hotel - Is Morus Hotel) around 18:30 and reaching Nora. From Nora back to Pula Hotels …
[Read more]
Open Database camp 2011 - Travel logistics, and don't forget the party
The Open Database Camp 2011 is near. In 9 days, the welcome party starts, and then the conference itself gets going.
If you are coming earlier than Friday, May 6th, you can either use public transportation or book a private seat with a volunteer in the car pooling page. Please help the organizers: post your arrival and departure dates and times, so we may be able to help you even outside the official conference days.
About the conference itself, as everyone should know, it's a …
[Read more]
Showing entries 1 to 10 of 24
10 Older Entries »