Yes SQLite can support multiple users at once. It does however lock the whole database when writing, so if you have lots of concurrent writes it is not the database you want (usually the time the database is locked is a few milliseconds – so for most uses this does not matter). But it is … Read more
You’ve basically named the solution in your question. To get a list of tables (and views), query sqlite_master as in SELECT name, sql FROM sqlite_master WHERE type=”table” ORDER BY name; (see the SQLite FAQ) To get information about the columns in a specific table, use PRAGMA table_info(table-name); as explained in the SQLite PRAGMA documentation. I … Read more
There is no native boolean data type for SQLite. Per the Datatypes doc: SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).
According to the documentation, it’s CREATE TABLE something ( column1, column2, column3, PRIMARY KEY (column1, column2) );
You can use limit with select and you can combine select and delete like: DELETE FROM Foo WHERE someColumn in ( SELECT someColumn FROM FOO WHERE SomeCondition LIMIT 200 )
Please note that you always have to use an ORDER BY clause; otherwise, the order is arbitrary. To do efficient paging, save the first/last displayed values of the ordered field(s), and continue just after them when displaying the next page: SELECT * FROM MyTable WHERE SomeColumn > LastValue ORDER BY SomeColumn LIMIT 100; (This is … Read more
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
The problem, as it turns out, is that the PDO SQLite driver requires that if you are going to do a write operation (INSERT,UPDATE,DELETE,DROP, etc), then the folder the database resides in must have write permissions, as well as the actual database file. I found this information in a comment at the very bottom of … Read more
There’s no ALTER COLUMN in sqlite. I believe your only option is to: Rename the table to a temporary name Create a new table without the NOT NULL constraint Copy the content of the old table to the new one Remove the old table This other Stackoverflow answer explains the process in details
Here’s how you can do it in C#: class Program { static void Main(string[] args) { if (File.Exists(“test.db3”)) { File.Delete(“test.db3”); } using (var connection = new SQLiteConnection(“Data Source=test.db3;Version=3”)) using (var command = new SQLiteCommand(“CREATE TABLE PHOTOS(ID INTEGER PRIMARY KEY AUTOINCREMENT, PHOTO BLOB)”, connection)) { connection.Open(); command.ExecuteNonQuery(); byte[] photo = new byte[] { 1, 2, 3, … Read more