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)

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

For a complete description see https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

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

 Come evitare iniezione SQL: lato SQL Server (SP_EXECUTESQL)

In generale va evitato nel modo più assoluto la scrittura di query sql diamiche.

Va evitato cioè  l’uso lato SQL Server di sp_executesql e EXEC

Di seguito mostriamo come trasformare una query “dinamica” in una “statica”

Read More

 

 Dapper is a single file you can drop in to your project that will extend your IDbConnection interface. [...]

A key feature of Dapper is performance. The following metrics show how long it takes to execute 500 SELECT statements against a DB and map the data returned to objects.

Read More

L’integrazione tra .NET e SQL Server è data per scontata: in questo articolo esploreremo invece  come accedere a SQL Server da Python. Uno dei vantaggi di python è che è un linguaggio molto facile da imparare, con un notevole numero di librerie e un’ottima integrazione multi piattaforma. Oltre a ciò python offre un sistema di installazione rapido delle librerie (come  Ruby e Perl).

Read More

In questo articolo della serie SQL Server, vedremo come creare  in modo dinamico delle istruzione per impostare delle policy di sicurezza. Creeremo da zero una stored procedure chiamata sp_FixOverallGrant che in modo dinamico imposterà i profili di sicurezza per due utenze, una in lettura e scrittura (EBRB0_APP) ed una in sola lettura (EBRB0_USR).

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