How to get the number of rows of the selected result from sqlite3?
try this way select (select count() from XXX) as count, * from XXX;
sqlite
Sqlite over a network share [closed]
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
Is there a boolean literal in SQLite?
From section 1.1 Boolean Datatype of the docs: SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true). So it looks like you are stuck with 0 and 1.
What is the difference between related SQLite data-types like INT, INTEGER, SMALLINT and TINYINT?
SQLite, technically, has no data types, there are storage classes in a manifest typing system, and yeah, it’s confusing if you’re used to traditional RDBMSes. Everything, internally, is stored as text. Data types are coerced/converted into various storage locations based on affinities (ala data types assigned to columns). The best thing that I’d recommend you … Read more
Are there any reserved words in SQLite?
Yes, see http://www.sqlite.org/lang_keywords.html for the list. You can bracket them, but it isn’t standard SQL. Also, see http://www.sqlite.org/lang_keywords.html
DbFunctions.TruncateTime LINQ equivalent in EF CORE
In EF6 DbFunctions.TruncateTime is used instead of DateTime.Date property because for some reason the later is not supported. In EF Core the former is not needed simply because DateTime.Date now is recognized and translated correctly. group events by events.DateTimeFrom.Date into dateGroup Unfortunately there is no documentation (yet) of what is supported, so as a general … Read more
How do I DROP a constraint from a sqlite (3.6.21) table?
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
SQLite “INSERT OR REPLACE INTO” vs. “UPDATE … WHERE”
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.
sqlite – How to get INSERT OR IGNORE to work
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
Does it matter what extension is used for SQLite database files?
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