sqlite

How can I access the last inserted row ID within a SQL script?

I'm using SQLite, and I have a table for properties, and a table for sub-properties. Each sub-property points to its parent using the fkPropertyId column. Right now, to create the initial database, I've got a script that looks something like this: INSERT INTO property VALUES(1,.....); INSERT INTO property VALUES(2,.....); INSERT INTO ...

SQLITE group by

I use the sql below in sqlite to group by time intervals. The first is group by day and the second group by hour: select strftime('%Y-%m-%dT%00:00:00.000', date_time),line, count() from entry group by strftime('%Y-%m-%dT%00:00:00.000', date_time) select strftime('%Y-%m-%dT%H:00:00.000', date_time),line, count() from entry group by str...

Can anyone please help me with this SQL Query (works in SQLite DB Browser, but not in Rails)

Rails has been spitting this out to me: SQLite3::SQLException: near "SELECT": syntax error: SELECT questions.id, questions.text, questions.question_type_id, questions.meta, questions.max_answer_length, COUNT(form_questions.id) AS expr1, (5) AS expr2, CAST(COUNT(form_questions.id) AS REAL) / CAST((...

SQLite PRAGMA foreign_keys question

I've modified System.Data.SQLite to use a recent version of SQLite engine that automatically enforces foreign keys without using custom triggers. I'm also using SubSonic 2.x but this would apply to any ORM frameworks using SQLite that are 'open late close early'. How would you ensure that the statement 'PRAGMA foreign_keys=true' is ca...

Thread synchronization in C#?

I have lots of few small functions, each executes a query. I want only one function to be running at a time, what is the best possible way of thread sync to avoid database locked issue in SQLite (C#). The functions are in multiple classes, how will you lock all the functions in all the DB classes, so that only one function from any of t...

How do you enable LIMIT for DELETE in SQLite?

Using PHP, I have a simple database that may store multiple items with the same content. I want to delete the first occurrence of an instance when I use DELETE. How do you enable LIMIT for DELETE in SQLite using PHP? ...

SubSonic 3 Repository - SQLite In-Memory

Before I invest the time in modifying the SubSonic 3 source, I figured I ask to see if I'm missing something simple. Is it possible to use the SubSonic 3 Repository with migrations on a SQLite In-Memory database? I couldn't find a way to force the DbDataProvider to keep the connection open so the In-Memory SQLite database doesn't vanish...

Quickest way to insert into SQLite using C API?

I want to do a lot of inserts (let's say a couple of million) into a database as quick as possible. Since I'm calling from C I thought that there might be a shortcut for doing this in the API. I can't help but feel that creating strings from data and having SQLite parse the strings back, all in one call, is less efficient than it could b...

rapid application developement tools for very basic GUI apps

I know there are many RAD platforms out there. Infact there are so many that I'm having a hard time finding out which one fits me best. What I want is a RAD tool that would allow me to define a database data model (make DB tables) and then create (view and edit) forms for the various tables. Data input, updating and various queries shoul...

Python 3: Best string compression method to minimize the size of a sqlite3 db

I recently created a script that parses several web proxy logs into a tidy sqlite3 db file that is working great for me... with one snag. the file size. I have been pressed to use this format (a sqlite3 db) and python handles it natively like a champ, so my question is this... what is the best form of string compression that I can use...

Read datetime back from sqlite as a datetime in Python

I'm using the sqlite3 module in Python 2.6.4 to store a datetime in a SQLite database. Inserting it is very easy, because sqlite automatically converts the date to a string. The problem is, when reading it it comes back as a string, but I need to reconstruct the original datetime object. How do I do this? ...

creating trigger across different databases

Hi, Is there any way to create triggers on different databases? my requirement is like:- database: a1.db consist table: t1 database:a2.db consist table: t2 now i have to use trigger on t1 (whenever any delete and update operation) happens on t1 a value has to be inserted into t2. waiting for your feedback... ...

Pivoting SQLite table, setwise like SQL should be

I have some data. 224,000 rows of it, in a SQLite database. I want to extract time series information from it to feed a data visualisation tool. Essentially, each row in the db is an event that has (among other things not strictly relevant) a time-date group in seconds since the epoch and a name responsible for it. I want to extract how ...

where do I get sqllite.dll to import into vs.net?

I downloaded a version of sqllite, but when trying to add as reference I got an error. sqllite3.dll cannot be added, please make sure the file is accessible, and that it is a valid assembly or com component. Is there a special dll for vs.net that i need? ...

Fixed Timetable DB Design

I want to let my users specify which hours / days they want to be contacted, I though of creating a fixed timetable with the 7 days of the week and let the user specify which hours he has free. I'm having a little trouble figuring out how I would store that info in the database, can anyone help me with a good table design for this situa...

What happens to Triggers/Indexes when i rename/create/insert/drop a table in SQLite?

What happens to Triggers/Indexes when i rename/drop a table SQLite? I'm running and upgrade script where i add a column to a table, so i d the following: ALTER TABLE [Entry] RENAME TO [Entry_temp]; CREATE TABLE [Entry] ( [EntryID] integer PRIMARY KEY AUTOINCREMENT NOT NULL, ... ) INSERT INTO [Entry] (...) SELECT ... FROM ...

sqlite: mirrorred ID-only table, worth it or not

I have a project where I need to load all ids of a table and have random access for different records afterward. This can be effectively done with loading results from SELECT rowid FROM... into an array and accessing actual data with SELECT * ... WHERE rowid = . This approach gives decent results for example for a 90M/70,000 records tab...

SELECT * in SQLite doesn't return everything in the table

I'm using PHP to fetch data from a database. Here is the code: <?php $db = new SQLiteDatabase("testDB.db"); $query = $db->query("SELECT * FROM blog ORDER BY blogdate DESC"); while($entry = $query->fetch(SQLITE_ASSOC)) { //only one blog entry per day echo "<a href=\"display.php?date=".$entry['blogdate']."\">".$ent...

sqlite_bind_?(character) how to bind characters in sqlite?

Hi all, May be its a silly question but I am a bit confused in it, I want to bind a character into sqlite database (neither as varchar, nor as text but as character only) Hence we can not use sqlite3_bind_text(insertStmt, 1, @"A", -1, SQLITE_TRANSIENT); So should I use sqlite_bind_int(insertStmt,1,65); or there is a better way t...

SQLite ADO .NET - how to correctly use query builder?

Hello. The following code works perfectly: var oDb = new SQLiteConnection(); oDb.ConnectionString = String.Format( "Data Source={0};" + "Synchronous=Full;", "test.db" ); oDb.Open(); SQLiteCommand oCmd = new SQLiteCommand( oDb ); oCmd.CommandText = "create table tests ( "; oCmd.CommandText += " id guid primar...