You have two options. First, you could simply add a new column with the following: ALTER TABLE {tableName} ADD COLUMN COLNew {type}; Second, and more complicatedly, but would actually put the column where you want it, would be to create the new table with the missing column and a temporary new name: CREATE TABLE {tempNewTableName} … Read more
My experience of file based databases (i.e. those without a database server process), which goes back over twenty years, is that if you try to share them, they will inevitably eventually get corrupted. I’d strongly suggest you look at MySQL again. And please note, I am not picking on SQLite – I use it myself, … Read more
SQLite does not (as of this answer) support the alter table drop constraint command. The allowed syntax can be seen here. You will need to create a new table without a constraint, transfer the data, then delete the old table. I think something like the following should work: CREATE TABLE child2 ( id INTEGER PRIMARY … Read more
Replace CREATE TABLE t ( id INTEGER PRIMARY KEY, name VARCHAR, other INT ); with CREATE TABLE t ( id INTEGER PRIMARY KEY, name VARCHAR UNIQUE, other INT ); Then you will get sqlite> CREATE TABLE t ( …> id INTEGER PRIMARY KEY, …> name VARCHAR UNIQUE, …> other INT …> ); sqlite> INSERT OR … Read more
UPDATE will not do anything if the row does not exist. Where as the INSERT OR REPLACE would insert if the row does not exist, or replace the values if it does.
Pretty much down to personal choice. It may make sense to use an extension based on the database scheme you are storing; treat your database schema as a file format, with SQLite simply being an encoding used for that file format. So, you might use .bookmarks if it’s storing bookmarks, or .index if it’s being … Read more
Sqlite allows only one writer to the whole database at a time and, unless you selected “WAL” journal mode, no reader while writing. Moreover unless you explicitly ask it to wait, it simply returns the SQLITE_BUSY status for any attempt to access the database while conflicting operation is running. You can tell sqlite to wait … Read more
This is not possible without a recursive common table expression, which was introduced in SQLite 3.8.3: WITH RECURSIVE dates(date) AS ( VALUES(‘2015-10-03’) UNION ALL SELECT date(date, ‘+1 day’) FROM dates WHERE date < ‘2015-11-01’ ) SELECT date FROM dates;
This is fairly easy to deduce from the implementation limits page: An SQLite database file is organized as pages. The size of each page is a power of 2 between 512 and SQLITE_MAX_PAGE_SIZE. The default value for SQLITE_MAX_PAGE_SIZE is 32768. … The SQLITE_MAX_PAGE_COUNT parameter, which is normally set to 1073741823, is the maximum number of … Read more
SELECT sql FROM sqlite_master WHERE tbl_name=”table_name” AND type=”table” Then parse this value with Reg Exp (it’s easy) which could looks similar to this: [(.*?)] Alternatively you can use: PRAGMA table_info(table_name)