SQLite

Giriş
Gömülü veri tabanları şu çeşitlere ayrılırlar. Ayrıca bir de Oracle Berkeley DB var.

- SQL databases such as SQLite
- Time-series databases like Influx
- Data-historian systems

Açıklaması şöyle

SQLite is the brainchild of D. Richard Hipp, who was also involved with the Tcl programming language, and his own version control system, among others.

Kurulum
Linux'a şöyle kurarız.

sudo apt-get install sqlite3

sqlite3 komutlar
satır silmek
Şöyle yaparız.

sqlite3 ~/cookies.sqlite \
'delete from moz_cookies where baseDomain="example.com";'

veritabanı açmak
sqlite3 komutu ile veritabanı şöyle açılır.

sqlite3 places.sqlite

tabloyu csv olarak ihraç etmek
bash kullanarak şöyle yaparız.

sqlite3 'Login Data' << EOF
.mode csv 
.headers on 
.separator "," 
.output UserPW.csv 
select * from logins; 
.exit
EOF

shell komutları

Tüm komutlar nokta karakter ile başlar.
Şöyledir
.help                            yardım
.quit                             sqlite3 shell'den çıkış
.databases                    mevcut veritabanlarını listeler
.tables                           mevcut tabloları listeler
.indices tablo                tablo için mevcut indeksleri listeler
.schema [tablo]             tablo yapısını gösterir. tablo belirtilmezse bütün tabloları gösterir
.output FILE                çıktıları FILE adlı dosyaya yazar
.output stdout               çıktıları ekrana yazar
.show                            bazı ayarların değerini gösterir
.headers on|off               çıktılarda başlık olacak mı
.mode column|list|line  çıktı formatı
.separator "str"              çıktılarda alanları "str" ifadesi ile ayırır

.tables - tabloları listelemek
Veritabanındaki dosyalar şöyle listelenir.

sqlite> .tables
moz_anno_attributes  moz_favicons         moz_items_annos    
moz_annos            moz_historyvisits    moz_keywords       
moz_bookmarks        moz_hosts            moz_places         
moz_bookmarks_roots  moz_inputhistory

.quit
Şöyle yaparız.

sqlite> ....
...
sqlite> .quit

sqlite3_analyzer
Şöyle yaparız. Sonuçları anlamak biraz zor.

$ sqlite3_analyzer duplicated.db
...

GUI Aracı
sqlitebrowser veya sqliteman kullanılabilir.

SQL Kullanımı
Açıklaması şöyle

It is interesting to note that there are implementations of SQL that are not strongly-typed. SQLite is probably the most popular example of such a relational database engine. Then again, it is designed for single-threaded use on a single system, so the performance concerns may not be as pronounced as in e.g. an enterprise Oracle database servicing millions of requests per minute.

Sütun Tipleri
Sqlite Sütun Tipleri yazısına taşıdım.

RowId
Açıklaması şöyle

If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID. You can then access the ROWID using any of four different names, the original three names described above or the name given to the INTEGER PRIMARY KEY column. All these names are aliases for one another and work equally well in any context.

Eğer "Integer Primary Key" sütunu yoksa şöyledir.

CREATE TABLE A ( _id INT PRIMARY KEY, name TEXT ); -- not "INTEGER"

| rowid | _id | name |
+-------+-----+------+
|     1 |   1 | this |
|     2 |   2 | that |
|     3 |  30 | NULL |  -- does not need to be the same

Eğer varsa şöyledir.

CREATE TABLE B ( _id INTEGER PRIMARY KEY, name TEXT );

| rowid | name |
| = _id |      | 
+-------+------+
|     1 | this |
|     2 | that |
|     3 | NULL |

Kısıtlar
aralık kısıtı
Int tipi için sayı aralığı koyabiliyoruz. Aşağıdaki örnekte duration_90k alanı için check kelimesinden sonra >=0 ve < 90k kısıtı konuluyor.

create table recording (
...
  -- The duration of the recording, in 90 kHz units.
  duration_90k integer not null
      check (duration_90k >= 0 and duration_90k < 5*60*90000),
...
) WITHOUT ROWID;

not null kısıtı
Sütunun null olmaması kısıtını koyabiliyoruz. Aşağıdaki örnekte sütun tipi int olsa bile null olmaması sağlanıyor.

create table recording (
...

  sample_file_bytes integer not null check (sample_file_bytes > 0),
...

) WITHOUT ROWID;

Abs
Size isimli sütün değerleri arasında 800'e en yakın değeri bulmak için şöyle yaparız.

SELECT * FROM my_table ORDER BY ABS(800-Size) LIMIT 1

Alias
Sqlite ile string birleştirmek için || karakteri kullanılır. Şöyle yaparız.

SELECT EmployeeID, FirstName || ', ' || LastName AS Name FROM Employees;

Create Table ve Primary Key
Primary key eğer integer ise "auto increment" olarak tanımlanır.

"CREATE TABLE MyTable (id INTEGER PRIMARY KEY AUTOINCREMENT , ..."

Drop Table
Eğer tablo varsa silinir.

"DROP TABLE IF EXISTS MyTable"

Alter Table
Sütun şöyle eklenir
alter table mytable add column mycomments text;

Index
Şöyle yaparız.

create index recording_camera_start on recording (camera_id, start_time_90k);

Like
Basit like sorgusu escape edilmeye ihtiyaç duymaz.

Select Col1 from Table1 where Col1 like 'A%';

Ancak bazen like içindeki cümlede % karakterini kullanmak isteyebiliriz. Bu durumda "like y Escape z" şeklinde kullanılıyor. Yani aramak istediğimiz string içinde % işareti varsa escape yapmak gerekir.

sqlite> create table t(a);
sqlite> insert into t values('a%b');
sqlite> insert into t values('ab');
sqlite> insert into t values('aa%bb');
sqlite> select * from t where a like '%a\%b%' escape '\';
a%b
aa%bb

Limit
Şöyle yaparız.

Select T.team as tea, T.score as sco 
         from T where sco < T.score order by random()) limit 1

printf
C'deki printf gibi çalışır.

order by
En büyük iki id değerine sahip satırları almak için şöyle yaparız

select * from (select * from tbl order by id ASC limit 2) order by id DESC;

order by - case
Şu kalıbı kullanır.

ORDER BY CASE ... WHEN ... THEN ... ELSE ... END

 Eğer sütunün değeri belli bir şey ise bir başka sütuna göre sıralama yapmak için when cümlesini kullanabiliriz. Şöyle yaparız.

... ORDER BY CASE sent WHEN 0 THEN created ELSE sent END DESC;

When koşulu içinde "or" kullanılabilir. Şöyle yaparız.

SELECT ...
ORDER BY CASE WHEN "Order" IS NULL OR "Order" = 99 THEN 0 ELSE Description END,
 Brand,
 Product;

sqlite_master tablosu
Bir tablonun olup olmadığını anlamak için şöyle yaparız.

"select DISTINCT tbl_name from sqlite_master where tbl_name = '...'

strftime - format + timestring
Sqlite Date ve Time Metodları yazısına taşıdım.

transaction

Şöyle yapılır.

BEGIN TRANSACTION;
...
UPDATE accounts SET balance = balance - 5000 WHERE ...;
...
END TRANSACTION;

trigger
Şöyle yaparız. Bu trigger ile tabloya hiç bir şey girilemez.

CREATE TRIGGER block_MyTable_update
BEFORE UPDATE ON MyTable
BEGIN
    SELECT RAISE(FAIL, "updates not allowed");
END;

update
Güncellenecek satır sayısı limit ile sınırlanabilir. Ancak sqlite kütüphanesini bu seçenek ile derlenmiş olması gerekiyor.

update label set is_used = 1 where name !='temp' order by cnt desc limit 3

view
Tabloya view şöyle yaratılır.

DROP VIEW IF EXISTS view_accounts;
CREATE VIEW view_accounts AS
    SELECT rowid,
           name,
           printf("$%.2f", balance / 100.0) AS balance
    FROM accounts;

Sqlite ve Concurrency
Açıklaması şöyle.

SQLite uses reader/writer locks on the entire database file. That means if any process is reading from any part of the database, all other processes are prevented from writing any other part of the database. Similarly, if any one process is writing to the database, all other processes are prevented from reading any other part of the database. For many situations, this is not a problem. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.

Bir başka açıklama şöyle.

If most of those concurrent accesses are reads (e.g. SELECT), SQLite can handle them very well. But if you start writing concurrently, lock contention could become an issue. A lot would then depend on how fast your filesystem is, since the SQLite engine itself is extremely fast and has many clever optimizations to minimize contention. Especially SQLite 3.

Sqlite Mimarisi
7 tane mimarisel katmandan oluşuyor. Katmanlar şöyle.
1. Tokenizer
2. Parser
3. Code Generator
4. Virtual Machine
5. Btree
6. Pager
7. VFS (Virtual File System)

Sqlite API
Sqlite C++ Api yazısına taşıdım