I am new in SQLite database. So I am searching for lots of things. So, I thought to crate a post with important SQLite notes.
Get Tables Information and more:
SELECT * from sqlite_master;
Crating Table:
Example for crating a table with primary key, and with unique field:
CREATE TABLE tbl1(id INTEGER NOT NULL PRIMARY KEY, name TEXT, counter INTEGER, url TEXT UNIQUE, daterecord TEXT, rate REAL);
Creating table, with unique value depend on 2 columns:
CREATE TABLE tbl2(id INTEGER, field1 TEXT NOT NULL, field2 TEXT NOT NULL, counter INTEGER, daterecord TEXT, UNIQUE (field1, field2) );
Create a table, with default values for columns:
CREATE TABLE tbl3 (daterecord TEXT DEFAULT CURRENT_TIMESTAMP, counter INTEGER DEFAULT 0, message TEXT DEFAULT 'none');
Create a table from query result:
CREATE TABLE tblnew AS SELECT title, message FROM tbl1 WHERE id<30;
Insert Into Table:
INSERT INTO tbl1(id, name, counter, url, daterecord, rate )
VALUES (5, "a name", 0, "an url", datetime("now"), 0.22);
We can use multiple values at once like:
INSERT INTO tbl2 (firstname, lastname, age)
VALUES ("firstname1", "lastname1", 21), ("firstname2", "lastname2", 22), ("firstname3", "lastname3", 33);
Insert Data From Other Tables
INSERT INTO tbl1 SELECT * FROM table2;
Insert Into Another Database Table:
ATTACH '/media/data/test/db2.sqlite' AS db2;
INSERT OR IGNORE INTO db2.tblTest SELECT * FROM tblTest LIMIT 500;
DETACH db2;
or create a new table by selecting from another database;
ATTACH '/media/data/test/db2.sqlite' AS db2;
CREATE TABLE tblNew AS SELECT * FROM db2.tblTest;
DETACH db2;
This query tested using SqliteCommand.ExecuteNonQuery and worked great!
So, we can copy data from currently connected database to another without need to use sqlite command line utility.
Update From Another Table
Update table from dynamically created table. Multiple fields can be updated from the same sub query.
;WITH tblfly AS(
SELECT ID, firstname lastname FROM tbl1 WHERE ID<300
)
UPDATE tbl2 SET (field1, field2) = (SELECT firstname, lastname FROM tblfly WHERE tblfly.ID=tbl2.ID);
Add New Column to Existing Table
ALTER TABLE tblTest ADD COLUMN newcolumn TEXT;
ALTER TABLE tblword ADD COLUMN counterror INTEGER DEFAULT 0;
Add Primary key or rename column for existing table:
ALTER TABLE commands is limited support in SQLite. So, we can achieve this indirectly.
Let's say the original table was created as follow:
CREATE TABLE table1 (id INTEGER, field1 TEXT, field2 TEXT );
First, rename the original table.
ALTER TABLE table1 RENAME TO table1_temp;
Then create a new table as wanted. Let say we want to rename field2 column to fld2.
CREATE TABLE table1( id INTEGER PRIMARY KEY, field1 TEXT, fld2 TEXT, field3 INTEGER DEFAULT 0);
Now, copy the data from the original table to the new one.
INSERT OR IGNORE INTO table1 (id, field1, fld2) SELECT id, field1, field2 FROM table1_temp;
INSERT OR IGNORE helps if there is duplicate for unique fields. Else, error throwing.
INSERT OR REPLACE also can be used.
And finally, delete the old table:
DROP TABLE table1_temp;
With this approach, the primary keys values stays unchanged, if no conflict. This is very useful.
Note: If you have any indexes or triggers related to the original table, you should be edit them according to new table. For example:
CREATE UNIQUE INDEX idx_table1_1 ON table1 (field1, fld2);
CREATE TRIGGER trg_table1_1 AFTER INSERT ON table1 BEGIN UPDATE table2 SET daterecord = DATE('NOW'), title = new.fld2 WHERE rowid = new.rowid; END;
Get Random Records:
SELECT * FROM tbltest ORDER BY RANDOM() LIMIT 5
Convert Date Formatted Text to SQLite Date Text Format
I convert an Access database to SQLite. The database had a date column. I know SQLite doesn't has date data type. So I stored the date column as text in SQLite.
After conversation I noticed the dates in dd/MM/yyyy HH:mm:ss format.
SQLite date function unsupported this pattern. So I wanted to change the data to yyyy-MM-dd HH:mm:ss format.
After some tries, I succeed to replace the text dates to the new pattern using following query:
UPDATE tblComment
SET DateRecord = substr(DateRecord,7,4) || '-' || substr(DateRecord,4,2) || '-' || substr(DateRecord,1,2) || substr(DateRecord,11,9)
WHERE DateRecord LIKE "__/__/201_ __:__:__"
Date Operation Examples:
Add 6 day to current date:
SELECT date('now','+6 day');
5 hours before now:
SELECT datetime('now','-5 hour');
Get values as local time:
SELECT datetime('now', 'localtime', '-15 minutes');