Re: [sqlite] mode insert dumps

On Fri, Aug 2, 2019 at 12:03 PM Olivier Mascia wrote: > Besides the other answers focused on using .mode insert more precisely, the > .dump command might prove useful too. Yes, but dump will dump all the columns, including autoincrement, that is something I want to avoid in the final output.

Re: [sqlite] mode insert dumps

On Fri, Aug 2, 2019 at 10:17 AM Dan Kennedy wrote: >sqlite3> .headers on Yes, but I forgot to mention that I'm using sqlite 3.7.17 (back from 2013), so this option is not working. However, since I've tested it works on recent versions, I think I would copy all the databases on a machine with

[sqlite] mode insert dumps

Hi, I've got a lot of database files that I would like to dump using INSERT statements. unluckily, .mode insert does not work for me, or I'm not able to understand how it works. sqlite> select mude_anno, mude_numero from catdaemo; INSERT INTO table VALUES(2019,1161); My questions are: 1) why is

Re: [sqlite] Why this query plan?

On Thu, Jan 12, 2017 at 5:33 PM, Richard Hipp <d...@sqlite.org> wrote: > On 1/12/17, Luca Ferrari <fluca1...@infinito.it> wrote: > >> One thing I was not expecting was SQLite to use the index at all: >> since the query does not apply any filter (where clause), it

Re: [sqlite] Why this query plan?

On Thu, Jan 12, 2017 at 1:45 AM, Richard Hipp wrote: > If you omit the ORDER BY clause, then the SQL database engine (*any* > engine, not just SQLite) is free to return the rows in whatever random > order it chooses. And it does not need to explain itself when it > does. :-) >

[sqlite] [OT] suggestion for shell script and variable interpolation

Hi all, this could be trivial, but assuming I need some shell script to query SQLite3 databases with variable-interpolated queries, what can I do? Of course the following does not work because ticks prevent variable interpolation: COUNT=`sqlite3 $db 'SELECT COUNT(*) FROM foo WHERE baz=$BAZ'` and

Re: [sqlite] smartest way to exchange a sqlite3 database with another empty

On Mon, Oct 3, 2016 at 11:51 AM, Richard Hipp wrote: > Safe way: In a separate process, use the backup API > (https://www.sqlite.org/backup.html) to copy the content of the main > DB over to a separate DB, then "DELETE FROM log;" on the main DB. > This will work without any

[sqlite] smartest way to exchange a sqlite3 database with another empty

Hi all, in one of my application I use a sqlite3 database as a log of activity. As you can imagine the file grows as time goes by, so I'm figuring I've to substitute it with an empty one once a good size is reached. What is the right way to do it without having to stop the application (and

Re: [sqlite] sqlite 3.13.0 does not use indexes as 3.8.11.1 does

On Tue, Jul 26, 2016 at 10:37 AM, Alessandro Fardin wrote: > As temporary work around we have have added to the query the INDEXED BY > energy_d_dateTimeIdx statement. > > In sqlite 3.8.11.1 the select was issued by using the > energy_d_dateTimeIdx index Any

Re: [sqlite] alter table and .schema output

On Mon, May 30, 2016 at 12:24 PM, Clemens Ladisch wrote: > This is normal. The ALTER TABLE adds the new column(s) immediately > behind the actual column definition. Inserting a comma before the > comment and the rest of the new column definition in the next line would > be

[sqlite] alter table and .schema output

Hi all, I've a doubt about the SQL that .schema provides regarding a single table. I've a table that has been created (and reported back by .schema) as follows: CREATE TABLE pratica_protocollo( ... note varchar( 2048 ) -- note per l'integrazione ); The I ran the following: ALTER TABLE

[sqlite] dump only data, change schema, reload

On Wed, Mar 23, 2016 at 1:02 PM, Keith Medcalf wrote: > > Do you mean something like this, perchance? This sets .mode insert > then does a select ..., which outputs the selected data in the > form of INSERT statements into a table called . The .mode list > just allows other commands to be

[sqlite] dump only data, change schema, reload

Hi all, I've a few hundreds sqlite3 database files, all almost equals except for some constraint that has changed during time. As I know, there is no way to alter constraint (e.g., unique indexes), and therefore I have to migrate data to a new schema version. That is possible because data will fit

[sqlite] problem with CURRENT_TIMESTAMP

On Mon, Mar 14, 2016 at 12:02 PM, Clemens Ladisch wrote: > Then the obvious explanation is that there is a bug in your insertion > code. (Which you have not shown.) Shame on me, the insert was fine (null value against timestamp column) but later on an update of such column was issued as:

[sqlite] problem with CURRENT_TIMESTAMP

On Mon, Mar 14, 2016 at 10:52 AM, Richard Hipp wrote: > I think your system clock was set incorrectly when you did the initial insert. I'm running on localhost and did not touch the clock. The following is the result of a just inserted row: SELECT strftime('%s', ts ), ts, strftime( '%s',

[sqlite] problem with CURRENT_TIMESTAMP

Hi all, I suspect I've a wrong timezone, even if I cannot understand how and why. I've got atable with a column defined as follows: ts timestamp DEFAULT CURRENT_TIMESTAMP this should get default values in GMT. Now if I try to compare a row inserted a few minutes before (without the ts value,

[sqlite] How to read data from WAL?

On Mon, Mar 7, 2016 at 7:55 AM, Sairam Gaddam wrote: > And the decryption here I mean is to get information from those pages(like > on which table, which column a change is made etc.,) which involves reading > the page to get the changes which are presently made. Sounds to me you want to

[sqlite] CREATE TABLE parser

On Wed, Mar 2, 2016 at 1:10 PM, Marco Bambini wrote: > I developed the parser myself. Great job but...what is the aim? Why one should use this instead of, let's say, Perl SQL::Parser ? Luca

[sqlite] applyng schema changes to several databases

On Tue, Feb 16, 2016 at 9:51 PM, Roger Binns wrote: > The way I (and many others) do it is to use the user_version. It is > an integer that starts out as zero, and can be read or written with > pragma user_version. Startup code then looks like this: > > if user_version()==0 { > BEGIN; >

[sqlite] applyng schema changes to several databases

Hi all, this could sound trivial but I've got a few hundreds SQLite 3 database files, all with the same schema, that I need to alter adding a few columns here and there. While I'm pretty sure a simple sheel script that will execute, file per file, the alter table (within a transaction) will do the