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

[Richard Hipp]

On 10/3/16, Domingo Alvarez Duarte  wrote:
> Hello !
>
> Thinking about this and the problem I'm experiencing with big databases
> with sqlite3 "vacuum" probably could be a good idea to use a flag in the
> sqlite3 header to inform other processes to reopen the database.
>

That might work on unix.  But on Windows, the operating system
prohibits files from being renamed while the file is open.

-- 
D. Richard Hipp
d...@sqlite.org
___
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

[Domingo Alvarez Duarte]

Hello Richard !

Ok I missed this point, but still while fighting to use sqlite3 with big 
databases I was thinking on some custom changes to allow sqlite relax 
some restrictions:
1- Create a new sqlite reserved table for register attached databases, 
this way every time a program try to open an sqlite3 database it will 
look if this table exists and will automatically attach then, also will 
allow views and triggers between attached databases.
2- For operations like "vacuum" and to force processes to reopen the 
database another sqlite reserved table could be created like 
"sqlite_open_instead" if that table exists it indicates anyone trying to 
open that database to open the one indicated there instead, this would 
work on any operating system (maybe not too elegant but it should work, 
or anything else using a similar mechanism).


Cheers !

On 03/10/16 09:53, Richard Hipp wrote:

On 10/3/16, Domingo Alvarez Duarte  wrote:

Hello !

Thinking about this and the problem I'm experiencing with big databases
with sqlite3 "vacuum" probably could be a good idea to use a flag in the
sqlite3 header to inform other processes to reopen the database.


That might work on unix.  But on Windows, the operating system
prohibits files from being renamed while the file is open.



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

[sqlite] Default ordering of SELECT query

[Andrii Motsok]

Hi,


Sqlite man (https://www.sqlite.org/lang_select.html)

[https://sqlite.org/images/syntax/select-stmt.gif]

SQLite Query Language: SELECT
www.sqlite.org
The SELECT statement is used to query the database. The result of a SELECT is 
zero or more rows of data where each row has a fixed number of columns.

says: "If a SELECT statement that returns more than one row does not have an 
ORDER BY clause, the order in which the rows are returned is undefined"

There is a question:

We have scenario:
 * database is opened in WAL mode
 * 10 rows have been read to container1
 * database has been checkpointed
 * database has been closed
 * database has been opened on DELETE mode
 * 10 rows have been read to container2

Can we assume that container1 == container2 ? Or in another words can we assume 
that order of rows is same in this scenario?


Regards,
Andrii

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

Re: [sqlite] Default ordering of SELECT query

[Clemens Ladisch]

Andrii Motsok wrote:
> "If a SELECT statement that returns more than one row does not have an ORDER 
> BY clause, the order in which the rows are returned is undefined"
>
> We have scenario:
>  * database is opened in WAL mode
>  * 10 rows have been read to container1
>  * database has been checkpointed
>  * database has been closed
>  * database has been opened on DELETE mode
>  * 10 rows have been read to container2
>
> Can we assume that container1 == container2 ?

This assumption happens to be true in the current version of SQLite, if no
other changes are being made to the database before re-opening it.

But to assume this is extremely dangerous, because you don't know how your
application will be changed in the future.  Just use ORDER BY.


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

Re: [sqlite] Default ordering of SELECT query

[Simon Slavin]

On 3 Oct 2016, at 3:18pm, Andrii Motsok  wrote:

> Can we assume that container1 == container2 ? Or in another words can we 
> assume that order of rows is same in this scenario?

Even if this assumption holds true in the current version of SQLite, someone 
could always do

PRAGMA reverse_unordered_selects

and mess it up for you.  If you want a fixed order, specify it.

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

Re: [sqlite] Partial indexes on JSON properties?

[Richard Hipp]

On 10/1/16, Jens Alfke  wrote:
> the WHERE clause in a CREATE INDEX statement
> explicitly disallows function calls Is this limitation something that
> might be lifted soon

Deterministic SQL functions are now allowed in partial index WHERE
clauses, as of a few minutes ago. The current "Prerelease Snapshot"
(https://www.sqlite.org/download.html) supports this capability.
Please try it out and report any problems.  Thanks.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Partial indexes on JSON properties?

[Jens Alfke]

> On Oct 3, 2016, at 11:29 AM, Richard Hipp  wrote:
> 
> Deterministic SQL functions are now allowed in partial index WHERE
> clauses, as of a few minutes ago. The current "Prerelease Snapshot"
> (https://www.sqlite.org/download.html ) 
> supports this capability.
> Please try it out and report any problems.  Thanks.

Thanks! I was next going to ask whether this is feasible to implement, and 
where I should file a feature request, but this is even better :)

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

Re: [sqlite] New sqlite version update tool based on pragma

[Sergei G]

I can certainly add a transaction.  I did not know "Android's
SQLiteOpenHelper" exists.

On Sat, Oct 1, 2016 at 1:01 AM, Clemens Ladisch  wrote:

> Sergei G wrote:
> > I wrote a tool that I am using to maintain SQLite DB schema.  It is very
> > simple and it is based on user_version pragma, which makes it SQLite
> > specific.  I have no plans to make it more complicated than it is today.
>
> This makes it quite similar to Android's SQLiteOpenHelper.
>
> > https://github.com/Kulak/sqlitemaint
>
> | Known Issues
> |
> | There is no wrapping of script file in a transaction. If script fails
> | it may leave datbase in an inconsistent state.  Workaround: apply
> | transaction in the script file itself.
>
> The checking of the version and the actual update must be done
> atomically, so it is your tool that must create a transaction.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
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] smartest way to exchange a sqlite3 database with another empty

[Niall O'Reilly]

On 3 Oct 2016, at 10:18, Luca Ferrari wrote:

> 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)?

  I think that your application must be responsible for this, not
  sqlite3.

  I don't know about a "right" way to do it, but I can describe what
  I did in a similar situation.

  In my case it seemed natural to start a new database every day,
  at 00:00 UTC.  Hourly, weekly, monthly, or according to a size-related
  criterion may suit your situation better.

  Each time the application has something to write to the database, it
  must calculate the file name to use.  If this has changed since the
  last time, the application must close the current database file and
  create a new one.  For example, when the date changes from 2016-10-02
  to 2016-10-03, it might be time to do this.  Alternatively, the
  application could track the number of entries in the database and
  change over when the count reaches 100,000 or whatever.  You need to
  have some idea how long, or how many entries, it takes to reach your
  chosen file-size limit.

  When reading the database, your application will need to identify and
  open as many files as necessary so as to avoid ignoring relevant data.

  I hope this helps.

  Niall O'Reilly
___
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

[Richard Hipp]

On 10/3/16, Luca Ferrari  wrote:
> 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)?

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.

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.

You cannot rename a database file while another process has that
database open.  Windows simply will not allow that.  If you do it on
Unix, then the process that has the file open will not know that the
file has been renamed and will continue to write to the original file.

-- 
D. Richard Hipp
d...@sqlite.org
___
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

[Petite Abeille]

> On Oct 3, 2016, at 11:18 AM, Luca Ferrari  wrote:
> 
> 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)?

You could use the attach/detach [1] functionality to transparently roll the 
logs over.

Something like main -> attach current -> insert current.log

Every now and then, change what ‘current’ points to.

[1] https://www.sqlite.org/lang_attach.html

___
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

[Florian Weimer]

* Richard Hipp:

> You cannot rename a database file while another process has that
> database open.  Windows simply will not allow that.  If you do it on
> Unix, then the process that has the file open will not know that the
> file has been renamed and will continue to write to the original file.

The journal, SHM and WAL files will also not be renamed at the same
time on POSIX systems, which could have nasty consequences.
___
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

[Stephen Chrzanowski]

Are you looking to keep the logs in the same file, or, are you looking to
put your log entries in a new file?

If you're interested in just keeping a single file, and if you've got
access to change your code that is writing to the database, then, what I
would do is "create table if not exists Logs (LogDate, LogMessage)", then
do the insert.  Then you can purge previous logs with a simple DROP TABLE
whenever you want to clean things up.  No worries about OS file handling,
unless you have a burst of log entries, file sizes are going to pretty much
stay the same size, etc, since you'll be just freeing pages up internally
in the database.

As others have mentioned, if you plan on a log per day, then, you'll need
to close the file handle, reopen with a new file and database.

On Mon, Oct 3, 2016 at 5:18 AM, Luca Ferrari  wrote:

> 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
>
___
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

[Paul Sanderson]

It seems that you just want to keep access to all of your historic
logging so rather than copy/backup the entire database you could just
create a new archive DB (or open an old one), attach it, copy x
records to the archive and then delete the same x records from the
master.

How big is your log database and how many records are added daily?


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 3 October 2016 at 12:57, Stephen Chrzanowski  wrote:
> Are you looking to keep the logs in the same file, or, are you looking to
> put your log entries in a new file?
>
> If you're interested in just keeping a single file, and if you've got
> access to change your code that is writing to the database, then, what I
> would do is "create table if not exists Logs (LogDate, LogMessage)", then
> do the insert.  Then you can purge previous logs with a simple DROP TABLE
> whenever you want to clean things up.  No worries about OS file handling,
> unless you have a burst of log entries, file sizes are going to pretty much
> stay the same size, etc, since you'll be just freeing pages up internally
> in the database.
>
> As others have mentioned, if you plan on a log per day, then, you'll need
> to close the file handle, reopen with a new file and database.
>
> On Mon, Oct 3, 2016 at 5:18 AM, Luca Ferrari  wrote:
>
>> 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
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
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

[Domingo Alvarez Duarte]

Hello !

Thinking about this and the problem I'm experiencing with big databases 
with sqlite3 "vacuum" probably could be a good idea to use a flag in the 
sqlite3 header to inform other processes to reopen the database.


Right now every time sqlite3 would perform an operation on a database it 
aquires a lock on the file and check to see if the schema has changed, 
at the same time it can check if it needs to reopen the database if a 
flag is set, this way it can work in any operating system.


Cheers !


On 03/10/16 06:51, Richard Hipp wrote:

On 10/3/16, Luca Ferrari  wrote:

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)?

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.

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.

You cannot rename a database file while another process has that
database open.  Windows simply will not allow that.  If you do it on
Unix, then the process that has the file open will not know that the
file has been renamed and will continue to write to the original file.



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