[sqlite] Sqlite + Dropbox

Hi I have just started using Dropbox to share documents. If I place a Sqlite database in my Dropbox folder I access it from a local application as a local file. This is then automatically synced with the copy held in my Dropbox cloud storage. This strikes me as a possible way to share a

[sqlite] SQL Optimisation

Hi I have a database which describes the various phases of a product lifecycle - one table per phase, one row per product. This is accessed by an application (developed in Gambas3) which displays the entire lifecycle of each product in a grid - left to right. Each phase also has its own

[sqlite] Sqlite Query Builder

Hi Does anybody know of a visual query builder which can be used on Linux to create syntactically-correct views/queries for a Sqlite database? I'm thinking of something along the lines of the query builder in MS Access or LibreOffice Base. Thanks Nige

[sqlite] SQL Syntax To Copy A Table

Hi I know this must seem a fairly dumb question, but I can't find an easy way to create a copy of table using just SQL. My requirement is to take periodic snapshots of a names and addresses table, to be stored in the same database as the master. The master table has one index - an

Re: [sqlite] Best Practice: Storing Dates

Hi Interesting discussion on the best way to store date/time fields. Unix time certainly has advantages for portability but, I agree, it can be a pain to convert, depending on which applications and tools you are interfacing with. ISO8601 is fine if you just need a local time stamp, but even

[sqlite] Export Of BLOB Data

Hi I have a table with a record definition broadly as follows: ID (Integer Primary Key) Field1 (Text) Field2 (Text) Field3 (BLOB) The BLOB field contains document scans in JPG and PDF format, and some source documents in ODT and DOC formats. I am on Linux, but need to export the data to

Re: [sqlite] populating a table as quickly as possible

Hi I had a similar issue a few months ago, only it was several hundred thousand records rather than millions. I found that dividing the job into smaller transactions did definitely speed things up considerably. In my case I think I limited each transaction to 20,000 records. I would

[sqlite] Duplicated Values - Occurrence Count

Hi I'd be grateful for a little advice on SQL syntax, as supported by sqlite. I have a table containing, typically, around 50,000 records. Within the table one particular field will contain many occurrences of the same string value. I can produce a de-duplicated list of the values from that

Re: [sqlite] Data Input Techniques

Dear All Thanks for all the help regarding ways of improving the speed of loading large volumes of data from a text file into a Sqlite table from within a bespoke application. The solution was indeed to place all the INSERT instructions between a BEGIN and a COMMIT. I can now load approx

[sqlite] Data Import Techniques

Hi I am writing an application which requires approximately 50,000 items to be imported from a text file into a table. Each item is a single string of 8 characters, and the target table has an auto-incrementing PK and one other field, to hold the 8 character string. Using the "Import Table

[sqlite] Blob Data Assignments

Hi I'm new to SQLite, coming from an Access/VB background. Having moved over to Linux I'm now developing an application using Gambas. One of the requirements is to store a scanned document (PDF) in a record as a blob, along with its metadata. I've no problem selecting the document, but I'm