Questo articolo, di tal Jordan Tigani, getta una luce oscura sul futuro dei big data. E’ scritto dal CEO di un’azienda che sviluppa un nuovo database OLAP (Online analytical processing) chiamato Duckdb, che e’ open source ed e’ “embedded”, nel senso che si ispira molto al modo di funzionare di SQLite. L’articolo sottolinea come uno degli argomenti piu’ forti dietro la commercializzazione di sistemi BigData (come BigQuery, MongoDB ecc) e cioe’ l’enorme flusso di dati che avrebbe investito alcune aziende, rappresentando sia una opportunita’ che una sfida, non si e’ verificato nonostante queste profezie siano vecchie di dieci anni.

Read More

SQLite is slowing becoming a new standard. It is fast, and has a growing number of features.

Useful settings

1. PRAGMA journal_mode = wal;
2. PRAGMA foreign_keys = true; Query, set, or clear the enforcement of foreign key constraints.
3. PRAGMA busy_timeout=....; In milliseconds, the busy_timeout is associated with each connection to the database and as such you need to set the timeout for each connection.
4. pragma synchronous = OFF; I call this "scissor mode" referring to running with a scissor in hands. This directive disables the call to fsync and it is very dangerous if a power outrange happens on the host. It delegates to filesystem the "fsync". Some database reach full speed disabling fsync call, but your data are not guaranteed to be stored in safe place until operating system flush data (which will eventually occur...).
5. NUMERIC type is special.... https://news.ycombinator.com/item?id=28069694 From the documentation: "A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if the text is a well-formed integer or real literal . . . If the TEXT value is not a well-formed integer or real literal, then the value is stored as TEXT." So NUMERIC is kind of like TEXT except when the text is a pure number.
6. SQLite ".expert" can help you to find out missed indexes:

   For most non-trivial SQL databases, the key to performance is creating the right SQL indexes. In this context "the right SQL indexes" means those that cause the queries that an application needs to optimize run fast. The ".expert" command can assist with this by proposing indexes that might assist with specific queries, were they present in the database.

Relevant reading

• Hosting SQLite databases on static github pages
• Distinctive Features Of SQLite
• SQLite Autoincrement
• SQLite FAQ
• Why SQLite succeeded as a database (Hacker News)
• SQLite small blob storage: 35% Faster Than the Filesystem (Hacker News)
• SQLite: Small, Fast, Reliable – Choose any three (Hacker News)
• Replicated SQLITE (give a try)

In my personal top ten on open source software initiative with some original sin inside them. Note: this is not a negative review, is only a snapshot of the world I see, I could be wrong, but please explain to me my mistakes, if any.

Read More

On 2002, I was forced to build a small java class to “sync” two databases (sorry for Italian):

SmartSync used the Metadata offered by JDBC API to be able to copy data between two tables. In the last years I evolved it to be able to support a special use case called “dump to SQLite”.

Read More

I admit it. I suffered from an “algebra narcoleptic syndrome” during my relational database lessons at University (1996 circa).

Ok it is a fake. But it seems so real on these days.

Boring male sleeping student

I was unable to avoid sleeping. I learned to love SQL after some years and now I think SQL is the most powerful, direct and useful stuff you can learn.

Read More

The Sqlite Oracle Compatibility Functions is an experimental compatibility layer for SQLite vs Oracle, written in Python 3.

 

It aims to provide a minimal compatibility with Oracle. The need was having some regular expression functions, and I do not like to work with risky C code, like this university project did

Read More

SQLite is a small, powerful embedded database. A friend of mine started using it about six years ago. Some years ago it comes also on top of  Python 2.5. It is used inside

• FileMaker Bento: its ultra customized model is based on a big sqllite db
• DropBox client, to store its internal state
• iPhone: stores your SMS and also other stuff. It is widely used by apps.
• Apple Safari uses it for HTML5 storage support
• Google Gears uses it
• ...and in a lot of embedded product.

I was annoyed because until version 3.1.3 SQLite did not provide an alter table syntax but… it is quite easy to emulate it with something like this, even if it required a bit of work:

Read More