Re: [sqlite] mode insert dumps

[Luca Ferrari]

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.

Thanks,
Luca
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] mode insert dumps

[Luca Ferrari]

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 a more recent version
and use such version for the dump.

Thanks,
Luca
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] mode insert dumps

[Luca Ferrari]

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 the insert operating against "table" instead of the real
table I was selecting from (catdaemo)?
2) is there a way to force the INSER to have also the columns I
selected listed? I would like something like:
INSERT INTO catadaemo( mude_anno, mude_numero ) VALUES(2019,1161);

The reason is that I want to exclude automatic keys from my inserts
because I'm going to pump those inserts into another database with
automatic keys.

Thanks,
Luca
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why this query plan?

[Luca Ferrari]

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 simply
>> states that the user wants all the rows, and while it is true that the
>> order is something the engine can choose, why bother traversing an
>> index instead of a direct scan of the table?
>
> Because the index is smaller than the main table.  Less disk I/O.

Shame on me, it was selecting the columns of the index...
Thanks for the explaination.

Luca
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why this query plan?

[Luca Ferrari]

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.  :-)
>

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 simply
states that the user wants all the rows, and while it is true that the
order is something the engine can choose, why bother traversing an
index instead of a direct scan of the table?

Luca
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Luca Ferrari]

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 the only ugly solution I thought is to use a temp file to write
down the query:

echo "SELECT COUNT(*) FROM foo WHERE baz=$BAZ" > $$.sql
COUNT=`sqlite3 $db < $$.sql`

but I'm sure there's a better and much more elegant approach.

Thanks,
Luca
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Luca Ferrari]

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 cooperation on the part of the application.
> But it does involving a lot of I/O.

This sounds good to built a maintanance program to keep just in case,
I will go for this solution at the moment.


>
> Alternative:  Modify the application so that it automatically detects
> when the database is getting to large (perhaps using PRAGMA page_count
> - https://www.sqlite.org/pragma.html#pragma_page_count) and then (1)
> closes the database connection, (2) renames the database file to a
> backup, and (3) reopens the main DB and reinitializes the schema.

When working with PostgreSQL (not meant to flame) I would solve with
partitioning: create a table which refers to a time period and move
all records of that period to such table. In the cas eof sqlite I have
to move the period table to another database, but that be simpler than
dumping the whole database and archive it (I mean it could keep the
original database online).

By the way, my database is growing around 1 MB per day, so nothing
scaring (at least now). In other words, I've a chance to study the
problem and get a suitable solution.

Thanks all,
Luca
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Luca Ferrari]

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 therefore without knowing when a new I/O operation will be
issued)?
Does sqlite3 provide some facility that could come into help (e.g.,
connected databases)?

Thanks,
Luca
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Luca Ferrari]

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 chance the optimizer thinks the table is too small to use the index?
Is the data in the table the same before and after the update (meaning
as tuple count size)?
Could you provide the explain?

Luca
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] alter table and .schema output

[Luca Ferrari]

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 too complex; and in any case the DB cannot know whether the comment
> belongs to the column or the table.

Thanks, I was suspecting it.
Is there a "correct" way to annotate SQL schema? Other databases
provides special commands (e.g., PostgreSQL ADD COMMENT), but I don't
see nothing in SQLIte3 syntax except the SQL '--' one.

Thanks,
Luca
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] alter table and .schema output

[Luca Ferrari]

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 pratica_protocollo ADD COLUMN  cage_attribuzione_anno
   integer;
ALTER TABLE pratica_protocollo ADD COLUMN  cage_attribuzione_numero
   integer;

and the .schema on the table provides now:

CREATE TABLE pratica_protocollo(
...

note varchar( 2048 ), cage_attribuzione_anno integer,
cage_attribuzione_numero   integer, -- note per l'integrazione
)

As you can see, the columns have been added before the SQL comment on
the last column of the table.
While this does not have any implicit or functional problem, I suspect
it could avoid some tools to work on "annotated" schemas.
Am I missing something on the ALTER TABLE side or this behavior is normal?
I'm running 3.8.11.1.

Thanks,
Luca
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] dump only data, change schema, reload

[Luca Ferrari]

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 added to the file ...


I already considered the .mode insert, the problem is that while all
databases have the same table columns not all have the same column
order (some columns have been added to existing databases and some
databases where created with new schemas), so having INSERTs without
the column list is not suitable.

Another problem I'm facing is foreign keys from other tables, and
therefore I'm looking at a script that, via table_info, extracts the
column list and builds SQL to export and reimport data.
But it is a lot of work to just drop a constraint, that's why I'm
searching for smarter ways.

Luca

[sqlite] dump only data, change schema, reload

[Luca Ferrari]

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 into the same table schema, but
I'm looking for a way to do it massively (and remotely).
So far I was thinking to wrap a shell script to dump the content of a
single database, create the new schema and reload the dump in it.
The problem is that .dump provides data and schema, while I'd like to
have data only.
Other commands like .clone and .backup works pretty much the same, as
far as I understand.

This leads me to either use awk/sed to manipulate the dump or to
hard-code single select statements into the script to extract data.
Is there any smarter way to dump only data in a loadable form?

Thanks
Luca

[sqlite] problem with CURRENT_TIMESTAMP

[Luca Ferrari]

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:

UPDATE tt SET ts=( SELECT datetime( 'now','localtime') ) ...

that produced the time glitch.

Sorry for the noise.
Luca

[sqlite] problem with CURRENT_TIMESTAMP

[Luca Ferrari]

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', CURRENT_TIMESTAMP ),
CURRENT_TIMESTAMP  FROM ...
1457956766|2016-03-14 11:59:26|1457953182|2016-03-14 10:59:42

Any idea?

[sqlite] problem with CURRENT_TIMESTAMP

[Luca Ferrari]

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, so with the
default) with the current timestamp I got 'now' is before the row
timestamp:

SELECT strftime('%s', ts ), ts, strftime( '%s', CURRENT_TIMESTAMP ),
CURRENT_TIMESTAMP FROM 
1457949984|2016-03-14 10:06:24|1457948222|2016-03-14 09:37:02

So the row appear to be one hour in the future.
I kindly ask how to fix this and how to safely check all the already
inserted tuples (e.g., update their ts values).

Thanks,
Luca

[sqlite] How to read data from WAL?

[Luca Ferrari]

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 implement logical decoding, that is to
extract an SQL form from the binary WAL format.
I don't believe SQLite provides such a feature, that is something
other databases use for bidirectional replication.

> I want to make note of those changes and replicate someplace else. And I
> found that WAL is the correct place where changes to the database are
> present. So my main aim is to get those changes and replicate after further
> processing.
> Is there any other better way other than WAL?

Wal shipping is a good way if you don't need to get bidirectional.

Luca

[sqlite] CREATE TABLE parser

[Luca Ferrari]

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

[Luca Ferrari]

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;
> CREATE TABLE ;
> CREATE INDEX ;
> pragma user_version=1;
> COMMIT;
> }


Thank you guys for opening my mind on the usage of pragmas.
The next question is: do I have a way to conditionally place the
pragma test into an sql file to make sqlite apply it? The above syntax
does not look familiar to me, and in the meantime I've wrapped the
user_version pragma get/set into a shell script.

Thanks,
Luca

[sqlite] applyng schema changes to several databases

[Luca Ferrari]

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 job I'm
wondering if there's a better approach or a more automated one.

Any suggestion is welcome.

Thanks,
Luca