Is there an SQLite equivalent to MySQL’s DESCRIBE [table]?
The SQLite command line utility has a .schema TABLENAME command that shows you the create statements.
sqlite
How to get a list of column names on Sqlite3 database?
PRAGMA table_info(table_name); will get you a list of all the column names.
What ‘additional configuration’ is necessary to reference a .NET 2.0 mixed mode assembly in a .NET 4.0 project?
In order to use a CLR 2.0 mixed mode assembly, you need to modify your App.Config file to include: <?xml version=”1.0″?><configuration> <startup useLegacyV2RuntimeActivationPolicy=”true”> <supportedRuntime version=”v4.0″ sku=”.NETFramework,Version=v4.0″/> </startup></configuration> The key is the useLegacyV2RuntimeActivationPolicy flag. This causes the CLR to use the latest version (4.0) to load your mixed mode assembly. Without this, it will not work. … Read more
SQLite – UPSERT *not* INSERT or REPLACE
Assuming three columns in the table: ID, NAME, ROLE BAD: This will insert or replace all columns with new values for ID=1: INSERT OR REPLACE INTO Employee (id, name, role) VALUES (1, ‘John Foo’, ‘CEO’); BAD: This will insert or replace 2 of the columns… the NAME column will be set to NULL or the … Read more
Is it possible to insert multiple rows at a time in an SQLite database?
update As BrianCampbell points out here, SQLite 3.7.11 and above now supports the simpler syntax of the original post. However, the approach shown is still appropriate if you want maximum compatibility across legacy databases. original answer If I had privileges, I would bump river’s reply: You can insert multiple rows in SQLite, you just need … Read more
Sqlite primary key on multiple columns
According to the documentation, it’s CREATE TABLE something ( column1, column2, column3, PRIMARY KEY (column1, column2) );
How can I avoid concurrency problems when using SQLite on Android?
Inserts, updates, deletes and reads are generally OK from multiple threads, but Brad’s answer is not correct. You have to be careful with how you create your connections and use them. There are situations where your update calls will fail, even if your database doesn’t get corrupted. The basic answer. The SqliteOpenHelper object holds on … Read more
How do I check in SQLite whether a table exists?
I missed that FAQ entry. Anyway, for future reference, the complete query is: SELECT name FROM sqlite_master WHERE type=”table” AND name=”{table_name}”; Where {table_name} is the name of the table to check. Documentation section for reference: Database File Format. 2.6. Storage Of The SQL Database Schema This will return a list of tables with the name … Read more
How can I list the tables in a SQLite database file that was opened with ATTACH?
There are a few steps to see the tables in an SQLite database: List the tables in your database: .tables List how the table looks: .schema tablename Print the entire table: SELECT * FROM tablename; List all of the available SQLite prompt commands: .help
Select first row in each GROUP BY group?
DISTINCT ON is typically simplest and fastest for this in PostgreSQL. (For performance optimization for certain workloads see below.) SELECT DISTINCT ON (customer) id, customer, total FROM purchases ORDER BY customer, total DESC, id; Or shorter (if not as clear) with ordinal numbers of output columns: SELECT DISTINCT ON (2) id, customer, total FROM purchases … Read more