Re: [sqlite] UPDATE statement without FROM clause

[Simon Slavin]

On 4 Jun 2016, at 10:15pm, Jean-Christophe Deschamps  wrote:

> Can't the same update be done more efficiently with a CTE?

The command inside the WITH has to be a SELECT command.

I wonder if there's a good reason for that.  If the command inside WITH could 
make changes to the database the result might be ambiguous, and very sensitive 
to how the SQL engine works.

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

Re: [sqlite] [BugReport]Data changes cannot actually flush to disk

[Simon Slavin]

On 7 Jun 2016, at 8:13am, 刘翔  wrote:

> Problem:
> When update database and power off immediately,

How soon do you turn the power off ?  Two seconds ?  Ten seconds ?  One minute ?

What type of hard disk do you have ?  Is it a rotating disk or a solid state 
drive ?

Is the drive you have the database on the boot drive of the computer ?

If you try to make a database on an external drive (Flash drive ?  External 
rotating drive ?) instead of the drive you were using, do you see the same 
problem ?

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

Re: [sqlite] Latin-1 characters cannot be supported for Unicode

[Simon Slavin]

On 7 Jun 2016, at 8:43am, Wang, Wei  wrote:

> Then I opened the database with SQLite Developer.

SQLite Developer is not supported by the team which wrote SQLite.  It's just a 
program which uses SQLite.  If it allows you to pick character encoding then it 
is not correctly showing you the contents of your database so you should not 
necessarily trust what you see.

If you want to see what's really in your database please use the SQLite shell 
tool, which was written by the team which wrote SQLite and is understood to be 
100% correct.

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

[sqlite] libsqlfs - a file system in a SQLite database

[Simon Slavin]

"The libsqlfs library implements a POSIX style file system on top of an
SQLite database.  It allows applications to have access to a full read/write
file system in a single file, complete with its own file hierarchy and name
space.  This is useful for applications which needs structured storage, such
as embedding documents within documents, or management of configuration
data or preferences."

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

Re: [sqlite] Long lived prepared statements

[Simon Slavin]

On 4 Oct 2014, at 10:08pm, Jim Dodgen  wrote:

> What I do is to prepare a relatively large and complex query. Then I run
> the same query forever  never doing a finalize. My assumption is I will
> have no memory leakage.

You can do _step() and _reset() and _bind_() as many times as you want in any 
order.  You should not get memory (or any other resource) leaks unless you've 
messed up in your own programming or supplied the wrong value for the fifth 
parameter to a _bind_().

When you are finished with the query you should either end on a _reset() or do 
a _finalize().  Or (harmlessly) do both.  If you do not do one of those, you 
may find that when you _close() some memory is not released for the statement 
and/or the database (I'm not sure which, but either way it's bad).

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

Re: [sqlite] Long lived prepared statements

[Simon Slavin]

On 4 Oct 2014, at 11:16pm, Jim Dodgen  wrote:

> It might be I need more of a Perl DBI question

Whoops.  Yes, my answer was geared to users of the C API and thin shims to it.  
I have no idea how Perl DBI works.  Sorry about that, and I hope you can get a 
response from a Perl user.

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

Re: [sqlite] Detecting multiple CHECK failures

[Simon Slavin]

On 7 Oct 2014, at 10:00pm, Peter Haworth  wrote:

> I'm a great believer in using CHECK constraints to do as much validation as
> possible within the database rather than code it in my application.
> 
> However, I think I'm right in saying that as soon as a CHECK constraint
> fails, an error is returned to my application so no other CHECK constraints
> are executed  In a data entry type of application, this isn't ideal as
> users would prefer to see all the errors they need to correct in one
> message.

For most ways in which SQLite can refuse to do something, you have no way to 
know why it refused.  The results don't include the name of a constraint which 
failed, or anything else of any use.  You simply get a result code which tells 
you that the operation failed because of the data in your command (rather than 
because the command had bad syntax or referred to a table/index/column which 
didn't exist).

> I can't think of a way round this but wondering if anyone has found a
> technique to return all CHECK constraint errors at once.

It would appear that in SQLite the CHECK constraints are useful only in 
ensuring your database doesn't reflect things that are impossible.  It is of no 
use at all in knowing why a command is rejected.

Ideally, if a result code indicates a constraint failure, there would be a way 
to retrieve a list of the names of the constraints which would have been 
violated.  However this is not possible in SQLite3 at all without a major 
rewrite.  SQLite3 just gets a binary indication of whether any constraints were 
violated.

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

Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

[Simon Slavin]

On 8 Oct 2014, at 5:14am, Stephen Chrzanowski  wrote:

> The one downside I just realized is that ON CONFLICT can be used outside of
> the table declarations as well, so perhaps a different word or signal might
> be needed for it to make linguistic sense, or, this version of ON CONFLICT
> USE DEFAULT can only be used in the tables field def'n.
> 
> Thoughts?

You could probably use a TRIGGER that detects the type of row you don't want 
and replaces it with your preferred form.  This doesn't do exactly what you 
want, but it is something like it.

I did have one system which created reports on certain operations and put them 
in a TABLE called 'log'.  I could use TRIGGERs in that program to have it 
notice data I didn't like, report on it, then delete the offending row.  All 
the program had to do was look in TABLE log for anything new tagged 'conflict' 
and report it back to the user.  It was a pretty neat toy but it turned out 
more complicated than I really needed.

SQL doesn't detail any method of communicating with the user (or even the 
programmer).  From its origins as a simple demonstration of a concept it has 
grown so powerful that people try to do programming in it without using a 
proper programming language.  It may be that a new procedural data manipulation 
language will come along one day but I think it will allow the programmer to 
define data conflict in a more elegant manner.

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

Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

[Simon Slavin]

On 8 Oct 2014, at 1:22pm, Stephen Chrzanowski  wrote:

> Can one not also put a constraint up on the field to say that the field can
> only be of a certain value, kind of to emulate ENUM?

Sure you can.  You can do anything expressible in SQL which turns into a 
BOOLEAN value.  Use a CHECK constraint:



It can even compare two fields in the same row and test one against the other 
so you can, for example, make sure you don't have any people who are both male 
and pregnant.

You can also use FOREIGN KEYS to check that a value entered in a field really 
does point to a row in another table.

Unfortunately neither of these things fulfill your original request.  If the 
data fails these checks and is rejected, you just get a standard error code.  
It doesn't name the constraint which was failed.

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

Re: [sqlite] In python, determine database status

[Simon Slavin]

On 8 Oct 2014, at 10:45pm, Mark Halegua  wrote:

> I think my problem is I've opened the database in different modules for 
> different views of the 
> data (there are six tables, one of which relates to two/three others, another 
> which relates to 
> one other) and therefore the database is locked for anything like adding data.

If you are using Shared Cache Mode, then you are using shared access to the 
database.  So whatever _open() command you use first is governing the access 
all modules have.

It may be that whatever Python library you're using automatically uses shared 
cache mode without you intentionally turn it on.

Are you using any special directives or PRAGMAs ?

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

Re: [sqlite] Single large table vs. several smaller tables

[Simon Slavin]

On 9 Oct 2014, at 3:25pm, Drago, William @ MWG - NARDAEAST 
 wrote:

> The question I have is, should I lump everything together in one table just 
> like the .csv file or should I create several smaller tables that group 
> similar parameters? I'm not sure what would normally be done. I think the 
> database is normalized properly in either case.

For SQLite, except in exceptional cases such as huge (multi terabyte) databases 
or slow media, it is more efficient to have one big table rather than several 
smaller tables.

At a first glance, when I see two tables with identical column definitions, I 
tend to feel that they should be merged into one table with one additional 
column.  Occasionally there are situations in which it's not appropriate, such 
as when the ability to DROP an entire table efficiently is useful.  But it's a 
good guideline.

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

Re: [sqlite] Will someone be able to explain this weird outcome...

[Simon Slavin]

On 10 Oct 2014, at 9:27pm, to...@acm.org wrote:

> sqlite> select "7,915" - "5,021";
> 2
> 
> But, would someone explain the result of 2?  Sorry for this child-like
> question, but I can't find the how the result of 2 came to be displayed.

7 - 5 = 2

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

Re: [sqlite] following a trail of references

[Simon Slavin]

On 12 Oct 2014, at 9:03pm, Paul Sanderson  wrote:

> Is this possible just using SQL select type statements?

I don't know how to do it but I bet you can do it with a recursive WITH:



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

Re: [sqlite] (no subject)

[Simon Slavin]

On 13 Oct 2014, at 12:06pm, Rohit Kaushal  wrote:

> please unregister me

Only you can stop forest fires.  See the link at the bottom of every post to 
this list.

Simon.

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

Re: [sqlite] Performance issue when copying data from one sqlite database to another

[Simon Slavin]

On 14 Oct 2014, at 10:24pm, Pontus Bergsten  wrote:

> INSERT INTO Dest.TheTable (field1, ..., fieldN) FROM Main.TheTable WHERE time 
> BETWEEN t1 AND t2
> And here is the performance problem: When the application is executed on 
> Windows on a desktop computer, the copying works fine and the performance is 
> fairly ok, even when saving to USB. However, when the same code is executed 
> on the embedded system, the copying of data is extremely slow, even though 
> the CPU load is very moderate. Profiling the thread that executes the 
> sql-statements above, reveals that the thread is active in many very small 
> steps, while waiting for the USB driver for very long time (compared to the 
> active time), in between. During profiling the copy-thread only did useful 
> work for about 5% of of the total time, the rest was waiting.
> 
> Is there any technique that can be used for tuning the performance of sqlite3 
> in this scenario? For example, writing larger chunks of data to the "Dest" 
> database?

Your description makes perfect sense, bearing in mind that cheap USB drives are 
slow.  A relatively cheap piece of research might be to see if you can find a 
(more expensive) fast USB drive and see whether that makes your operation 
faster.  Other than that, two possibilities occur to me:

(A) Create your new database file on main storage, and create the Dest table 
there.  Once it is complete, close the database, then copy the database file to 
the USB drive using file copy commands rather than SQLite commands.  That 
should give you the fastest possible way of getting that data onto the drive.

(B) Write your Dest table to memory, then use the SQLite backup API to copy 
that to a file on the USB drive.  Copying the entire database page by page 
should be faster than copying the data row by row.



I do not know that either of these will definitely help you.  It depends too 
much on the relative speed of various components of your embedded system and on 
the width of your data bottleneck.  But they might be worth exploring.

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

Re: [sqlite] UPDATE Help

[Simon Slavin]

On 15 Oct 2014, at 1:10am, Shantanu Namjoshi 
 wrote:

> ALTER TABLE dailydelete ADD COLUMN SMB varchar(11);

If you find yourself doing things like this your schema is messed up.  SMB is 
obviously data, not a column.  Redesign your table so that HML, SMB, UMD and Rf 
are values in a column, not the names of columns.

By the way, SQLite doesn't have a varchar type.  Your data will be interpreted 
as TEXT, and will not be truncated to 11 characters.  It would be better just 
to declare the column as TEXT.

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

Re: [sqlite] sqlite Query Optimizer

[Simon Slavin]

On 15 Oct 2014, at 12:54pm, Prakash Premkumar  wrote:

> I'm trying to understand the sqlite select query optimizer. It works by
> assigning costs to each relation in FROM clause.

That is only a little bit of how it works.  Have you read these ?




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

Re: [sqlite] sqlite Query Optimizer

[Simon Slavin]

On 16 Oct 2014, at 7:50am, Prakash Premkumar  wrote:

> Does sqlite implement the pointers in the System R Algorithm ?

SQLite does not implement the System R Algorithm, so no, it doesn't implement 
the pointers from System R.  It implements the algorithms described in the two 
references just cited.  Those algorithms have some things in common with System 
R but are not the same as it.

> Like
> assigning selectivity factors for predicates in where clause?

SQLite does something equivalent to this.  Read about the ANALYZE command which 
is documented on the SQLite web site, and examine the type of data it puts in 
the tables it creates.  The ANALYZE command is discussed in the two references 
just cited.

You've asked some detailed questions on this list which could be answered by 
reading the documentation available to on the SQLite web site.  It might be 
worth trying to get your answers from the web site first, and if you still want 
to post, citing the pages you've already read so we don't point them out to you 
in our own answers.

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

Re: [sqlite] sqlite Query Optimizer

[Simon Slavin]

On 16 Oct 2014, at 11:07am, Prakash Premkumar  wrote:

> Are there any projects where , sqlite optimizer has been extended to add
> System R

The optimizer currently built into SQLite does the same job as what you're 
thinking of as "System R".  It is not possible to use both together.

You should be aware that the System R paper is now 40 years old.  We have got 
considerably better and learned considerably more about databases since System 
R was devised.  My guess is that if you replaced the current SQLite 
optimization system with System R SQLite would get worse, not better.

> or other algorithms ?

I will let the developers answer that.

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

Re: [sqlite] Inmemory database in sqlite

[Simon Slavin]

On 17 Oct 2014, at 10:39am, Prakash Premkumar  wrote:

> So the approach where we open the db with ":memory:" keyword does not
> provide durability .
> Only by increasing the cache size can we make db act as an inmemory db with
> durablity. That's the conclusion right ?

No.  The cache size has nothing to do with it.  If you explicitly tell SQLite 
to open a database in memory then it will open a database in memory and not on 
disk.  That is what the documentation says.

Can you explain to us why you are asking all these questions comparing SQLite 
with other things ?  You don't seem to know the subjects will enough to ask 
useful questions and the answers don't seem to get you any advantage.

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

Re: [sqlite] 'INTEGER PRIMARY KEY' start value

[Simon Slavin]

On 19 Oct 2014, at 2:27pm, Baruch Burstein  wrote:

> Is the rowid/'INTEGER PRIMARY KEY' field that is not entered manually
> guaranteed to start from 1? Or at least from a positive number?

See the section 'Background' in



for the 'usual' algorithm used for ROWID.  Whatever 'usually' means.  I think 
it means anything that says INTEGER PRIMARY KEY but not INTEGER PRIMARY KEY 
AUTOINCREMENT.

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

Re: [sqlite] Data error

[Simon Slavin]

On 18 Oct 2014, at 6:25am, aaquib Khan  wrote:

> i have encountered a strange issue lately, i am using sqllite in my windows
> application. The logic in application is, at the start of the application
> db file is created, and it is deleted when the application is closed. Now
> when i again start my application the db file is created but it contains
> old data which was deleted successfully

Do you know it was deleted successfully ?  Are you checking the results 
returned from the DELETE, the _finalize(), and the _close() ?

> i even tried to manually
> shift+delete the db file but in vain, the old data comes back somehow, any
> solution for this to not happen.

Windows does some broken caching of some files and ignores what has actually 
happened on disk:



Pick an unusual file extension nobody else has ever thought of, and try that.  
See if that fixes the problem.

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

Re: [sqlite] Question on locks

[Simon Slavin]

On 18 Oct 2014, at 1:32pm, Ali Jawad  wrote:

> I do have one sqlite DB, with multiple dbs, and multiple scripts writing to
> those tables, at one time only one script writes to one table, I.e. there
> is no simultaneous read/write access to one table at one time. However, I
> am getting DB locked errors, did I misunderstand Sqlite3 ? Is the lock per
> DB or per table ?

The lock is per database.

You're bound to get locks occasionally if you don't set a timeout.  Set 
yourself a timeout using



or the PRAGMA.  Set it to something like 1 minute.  Does the problem go away ?

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

Re: [sqlite] Question on locks

[Simon Slavin]

On 20 Oct 2014, at 1:15am, Ali Jawad  wrote:

> Thanks, I hope it works, I hate to use MySQL for this project. Pardon my
> ignorance but the example at hand below is for C, any ideas on how to set
> for Bash and PHP ? Or better yet globally for sqlite3

There's a PRAGMA which sets the same thing:



The time is in milliseconds and for testing purposes you want to set it to at 
least 1 milliseconds.

Execute the pragma after you open the connection /on all the computers which 
are opening the database/.

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

Re: [sqlite] Regression with sqlite 3.8.7

[Simon Slavin]

On 23 Oct 2014, at 4:55pm, Luigi Iemma  wrote:

> When I run this query on 3.8.5 it takes 0.126 seconds,
> when I run this query on 3.8.7 it takes 17.37 seconds

Can you do an ANALYZE then try it again ?

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

Re: [sqlite] quasi-bug related to locking, and attached databases....

[Simon Slavin]

On 24 Oct 2014, at 9:54pm, dave  wrote:

>  Can locking be made more clever to know about aux being an alias
> for main, and effectively translate the query shown to it's functional
> equivalent of:
>insert or replace into main.dest ( name, value ) values ('allow',(select
> value from main.source where name = 'allow'));
> which does work (or both to 'aux' as well)?

To help us consider this, please give us the following information:

Which version of SQLite are you using ?
What operating system are you using (including which version) ?
What formats are the volumes those files are stored on ?

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

Re: [sqlite] quasi-bug related to locking, and attached databases....

[Simon Slavin]

On 24 Oct 2014, at 10:40pm, dave  wrote:

> Later I can try on linux, but I don't have it at my fingertips just now.  I
> don't mind trying with other versions of sqlite if you think it's helpful,
> but I suspect it's been there forever.

Not gonna ask for this since you've now given enough info for someone to 
reproduce and test the issue.  They might come back asking for further 
information but I'll leave that up to the experts.

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

Re: [sqlite] Question on locks

[Simon Slavin]

On 25 Oct 2014, at 3:31pm, Ali Jawad  wrote:

> bash script
> 
> sqlite3 websites.db "PRAGMA busy_timeout=1500;CREATE TABLE [$SITE] (DATE
> INT ,EU INT , US INT);"

Creating and destroying tables always involves a long lock.

> php script
> 
> $ret = $db->query("PRAGMA busy_timeout=1500;SELECT eu,us,date FROM [$site]
> ORDER BY date(DATE) DESC  LIMIT 10");

This doesn't work.  The query will process only the query command.  You want 
something more like

// do this just once, soon after creating the $db connection
$ret = $db-exec("PRAGMA busy_timeout=1500");

// do this when you need the result
$ret = $db->query("SELECT eu,us,date FROM [$site] ORDER BY date(DATE) DESC 
LIMIT 10");

By the way ... I notice you are creating a table with a variable name.  This is 
usually a bad sign.  It might make more sense to put your data into one table, 
and add a column which contains the $site .  Then you don't need to create a 
new table when you have data for a new site.

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

Re: [sqlite] Question on locks

[Simon Slavin]

On 25 Oct 2014, at 7:16pm, Ali Jawad  wrote:

> Thanks Simon, the create process is a one off. As for the table name I did
> use this approach as to not accumulate too much data in one table and
> instead split the data in multiple tables. From a design POV  in sqlite is
> this a mistake. And will the pragma for php eliminate locks ?

You should definitely execute the PRAGMA as a separate command, not as part of 
your SELECT command.

I do not know for sure that, done as above, it will fix your lock.  I'm not 
sure why you are getting the locks.  But it is the next step for you to try, 
and if it doesn't fix them it will provide good diagnostic information.

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

Re: [sqlite] Performing a SELECT on an enormous database...

[Simon Slavin]

On 25 Oct 2014, at 9:07pm, Ross Altman  wrote:

> Thanks for all the responses. The small integer column H11 comes before the
> large string column NVERTS, so doesn't that mean SQLite is only loading the
> minimum required while filtering? If that's the case then I don't
> understand why it's taking up to 15 minutes to load.

The searching is taking the time.  Because, as a number of other people have 
pointed out, there is no index on the H11 column, so SQLite has to look at 
every row in the table to see whether it qualifies for your SELECT.  Create an 
index by doing something like

CREATE INDEX ToricCY_H11 ON ToricCY (H11)

then do as many SELECTs as you want.

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

Re: [sqlite] unique with icu

[Simon Slavin]

On 26 Oct 2014, at 6:00am, dd  wrote:

> Application using sqlite database without icu extension. I am planning to
> add icu extension. for schema, add new column and index with lower.
> 
> Is it safe to add icu for existing db's?

Yes.  But once you've added it and used it there will be a problem if you ever 
try to use the database without it.

> Will it lead to any corruptions?

No.

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

Re: [sqlite] Question on locks

[Simon Slavin]

On 26 Oct 2014, at 9:27am, Ali Jawad  wrote:

> right now this is all about the write process to
> the database. 4 scripts run simultaneously, writing 500 entries each
> through a while loop to 500 tables each every 10 minutes.
> 
> The relevant part is here
> 
> sqlite3 websites.db  "PRAGMA busy_timeout=1500;insert into [$SITE]
> (date,eu,us) values ($DATE,$DIFF,$DIFF2);"

First, a database with 500 tables in is probably badly organised and will lead 
to slow operations (and therefore locks !).  Any time you find yourself using a 
500-value data variable as a table name (in your case, $SITE) you're probably 
doing something wrong.  It would be better to organise your table so that the 
$SITE name is a column in a table:

CREATE TABLE samples (sitename TEXT,date TEXT,eu ,us )

insert into samples (sitename,date,eu,us) values ($SITE,$DATE,$DIFF,$DIFF2);

It also means that you never have to worry about $SITE containing a character 
that is not legal in a table name.  However, in the rest of this reply I will 
assume that you have good reasons for not wanting to reorganise your data in 
this way.

Second, you are opening and closing the database 500 times, and opening and 
closing the database requires a huge amount of unique access, and is therefore 
keeping it busy, and therefore locked for a long time.

So instead of running the SQLite shell tool 500 times, run it just once.  In 
your script which runs the shell tool, instead of putting the data directly 
into the database, have it write the data to a text file.  This text file 
should read

PRAGMA busy_timeout=1500;
BEGIN;
insert into [$SITE] (date,eu,us) values ($DATE,$DIFF,$DIFF2);
insert into [$SITE] (date,eu,us) values ($DATE,$DIFF,$DIFF2);
insert into [$SITE] (date,eu,us) values ($DATE,$DIFF,$DIFF2);
 ... all your other INSERTs here ...
END;

Once you've written the whole text file you can tell the shell tool to execute 
it using the following command

sqlite3 websites.db ".read commands.txt"

All the INSERTs will happen while the file is open once, and the BEGIN/END 
means that they'll all happen in the same transaction, which will also make 
everything far faster.  In SQLite it's transactions that take time, not 
individual commands.

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

Re: [sqlite] [RFE bug] Improve error reporting for foreign keys

[Simon Slavin]

On 27 Oct 2014, at 8:17am, Clemens Ladisch  wrote:

> Tristan Van Berkom wrote:
>> locateFKeyIndex() function issuing the not-so-informative
>> message "foreign key mismatch" [...]
>> 
>>  o When foreign keys are enabled at CREATE TABLE time, it would
>>be very helpful at this point to issue an error if a foreign
>>key is declared which refers to a non-unique column (or compound
>>FK referring to a non-unique set of keys in the parent table).
> 
> The referenced table might not yet exist.

This is the problem, and it occurs with both FOREIGN KEYs and TRIGGERs.

I could do my standard thing and say that a schema with circular references 
between tables is badly designed but I don't actually know whether this is true 
or not.  Nevertheless, SQL allows circular references so SQLite should deal 
with them.

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

Re: [sqlite] quasi-bug related to locking, and attached databases....

[Simon Slavin]

On 27 Oct 2014, at 8:43am, Stephan Beal  wrote:

> - a couple months back Simon suggested ATTACHing the db to itself so that
> we can effectively alias "main" to the well-known name we have specified
> for that db instance. It worked like a charm until Dave discovered this
> weird locking behaviour.

The suggestion I made originally was that sqlite3_open() should open a dummy 
database -- which doesn't need to contain any tables -- as 'main'.  That way 
any attached database would never be 'main' too.  However this solution was 
rejected in your case because introducing a new file into your project 'costs' 
a lot of work.

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

Re: [sqlite] Circular References [Was: Re: [RFE bug] Improve error reporting for foreign keys]

[Simon Slavin]

On 27 Oct 2014, at 9:11am, Tristan Van Berkom  wrote:

> This seems to me to be an elegant solution, but I'd be curious to
> know if it would be considered badly designed for some reason, and
> would be interested to know what kind of alternatives people would
> propose.

A foreign key which points to the same table had not occurred to me, outside of 
abstract mathematical exercises.  I think you've come up with an acceptable 
example of why I felt reluctant to condemn the practise.

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

Re: [sqlite] quasi-bug related to locking, and attached databases....

[Simon Slavin]

On 27 Oct 2014, at 9:49pm, Nico Williams  wrote:

> On Mon, Oct 27, 2014 at 3:17 PM, Stephan Beal  wrote:
>> That's conceptually the same problem we're trying to solve here: keep the
>> public db names stable, regardless of where/how they're actually attached.
> 
> Yes, I think that's desirable.
> 
> If it's not too much to ask for then SQLite3 ought to: a) check for
> duplicates by canonicalized path (but keep in mind that this can be
> difficult to do portably, or without obnoxious length limitations on
> Windows), then b) check for duplicates by st_dev/st_ino where
> available.

Checking for cannonical path would seem to be important to improving this 
functionality.  There's a function to do this under OS X (resolve links, then 
do "stringByStandardizingPath") and Windows ("PathCchCanonicalize").  BSD has 
"realpath(3)".  It should be possible for most VFSes.

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

Re: [sqlite] Ordering of group_concat values using subselect in 3.8.7

[Simon Slavin]

On 28 Oct 2014, at 7:33pm, James Earl  wrote:

> After upgrading from SQLite 3.8.6 to 3.8.7, I am no longer able to
> order group_concat values by using a subselect.

Sorry, but as the documentation says



"The order of the concatenated elements is arbitrary."

I think the change you're seeing is the result of improved optimisation in 
3.8.7.  It might be possible to get the order you want by changing the phrasing 
of your query from a sub-select to JOIN, but it's already complicated and I 
can't figure out the right phrasing right now.

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

Re: [sqlite] problem trigger

[Simon Slavin]

On 28 Oct 2014, at 8:06pm, pablo Van  wrote:

> WHEN new.DiaHs_Inicio not between (old.DiaHs_Inicio and old.DiaHs_Fin)

I do not think SQLite supports NOT BETWEEN.  I would change it to something like

WHEN (new.DiaHs_Inicio < old.DiaHs_Inicio) OR (new.DiaHs_Inicio > old.DiaHs_Fin)

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

Re: [sqlite] Database is locked exceptions

[Simon Slavin]

On 29 Oct 2014, at 4:13pm, Mike McWhinney  wrote:

> "URI=file:mydb.db; default timeout=10; Pooling=True; Max Pool Size=100;";

Can you please change your timeout to 1 (really, 1ms == 10 seconds) and 
see if this makes the problems go away ?

It may not be necessary to leave the setting like that.  But the information 
about whether this does or doesn't solve the problem may tell us what the 
problem is.

> One thing is that I have tried a method where I open the database and leave 
> it open through out the life
> of the program (open it when I start the program and close on exit). Should I 
> perhaps change this so that 
> I open the database, perform the query, then close it right away?

One usually keeps the database open for the life of the program, the way you 
have it working already.  So lets try other solutions first.

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

Re: [sqlite] New kids on block the SQLite Master Suite.

[Simon Slavin]

On 29 Oct 2014, at 7:46pm, Billy Huynh  wrote:

> Please check it out by visiting http://www.aimtelligentsw.com? 

A few quick tips:

* Say right up front which operating systems your application runs on.
* What's Windows 2003 ?
* On the download page, explain what can be done with the 'Trial version' 
without obtaining a license.  Is it hampered in any way ?
* Screenshots, screenshots, screenshots.  People want to judge what the 
software can do from screenshots.  They will read your feature list only after 
that.  The ones you have included are fine, but they need to be seen first, up 
top, not after an extra click.

Also, I would advise figuring out whether your site is about your company or 
the one piece of software.  The different menus give a mixed message: one 
explicitly mentions the software title, others don't mention it.  It doesn't 
matter which you choose, but you should be consistent.

Good luck with your product.

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

Re: [sqlite] Fw: Database is locked exceptions

[Simon Slavin]

On 30 Oct 2014, at 4:13pm, Mike McWhinney  wrote:

> I have continued to receive the locking erros. The latest connection string I 
> have as follows:
> 
> public static string OMconnectionString = "URI=file:oslermedicine.db; busy 
> timeout=1; Pooling=True; Max Pool Size=100;";
> 
> 
> 
> Are there any other parameters that will increase the timeout when the 
> database gets locked? 

I'm sorry, but the library you're using uses parameters which don't exist in 
SQLite, and I don't know enough about how it works.  Perhaps somebody else has 
a suggestion.  Or perhaps you can contact the authors of that web page and ask 
them.

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

Re: [sqlite] sqlite_column_table_name() and table alias name

[Simon Slavin]

> On 31 Oct 2014, at 12:30pm, Jose F. Gimenez  wrote:
> 
> SELECT a, b, table2.c, alias.c
> FROM table1
> LEFT JOIN table2 ON ...
> LEFT JOIN table2 AS alias ON ...
> 
> the API sqlite_column_table_name() applied to both columns 3 and 4 returns 
> . I know that  belongs to  in both cases, but is there any 
> way to get  for column 3 and  for column 4?

Specify which names you want SQLite to use:

SELECT a, b, table2.c AS table2, alias.c AS alias
FROM table1
LEFT JOIN table2 ON ...
LEFT JOIN table2 AS alias ON ...

However, I strongly advise against having one string which is both a table name 
and a column name ("table2" in your example).  I cannot right now think of a 
problem this will trigger but I suspect you're just setting yourself up for 
later confusion.  Perhaps use something like .

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

Re: [sqlite] Stuck and drowning trying to work out some SQL.

[Simon Slavin]

On 1 Nov 2014, at 10:19am, Rob Willett  wrote:

> I struggle when I try to pull it all together so that I have one piece of SQL 
> that does all the work. 

You want to identify the first Active and the first Closed for each 
Disruption_id.  Your biggest problem is that, as you describe clearly and 
usefully, your data is not clean and orderly.

You have too many combinations and possibilities to be able to get SQlite to do 
all the work.  You might be able to get SQLite to do some of the work by using 
some complicated commands but I suspect that you'll be better off by using your 
programming language to do some of the work.  Do

SELECT * FROM Table_1 ORDER BY Disruption_id,Time_Event

Then spot a change in Disruption_id, and with each Disruption_id spot the first 
Active and the first Closed.  You can come up with your own ideas about what to 
use when either status is missing.  This combination of SQLite and your 
programming language will lead to a fast result from code which is easy to 
understand and debug.

To make the SELECT run faster you'll want to create an index on 
(Disruption_id,Time_Event).

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

Re: [sqlite] sqlite_column_table_name() and table alias name

[Simon Slavin]

On 1 Nov 2014, at 12:29pm, Jose F. Gimenez  wrote:

> But if I need the column's fullname, I get:
> 
> messages.subject, people.name, people.name
> 
> And yes, I know that I can specify an alias for those columns which could be 
> ambiguous. That is "sender.name AS sender_name" and "receipt.name as 
> receipt_name", but what about a query like "SELECT messages.*, sender.*, 
> receipt.* ..." which will be processed later by a reporting system that knows 
> nothing about original columns? In this case, it's absolutely needed to 
> distingish between  and ,  and 
> , and so on.

This is from the documentation of SQLite:



"The name of a result column is the value of the "AS" clause for that column, 
if there is an AS clause. If there is no AS clause then the name of the column 
is unspecified and may change from one release of SQLite to the next."

If you do not use AS, then you have no idea what the names of your columns are. 
 You cannot even rely on each column having a different name.  You might 
upgrade to a slightly higher version of SQLite because of a bug, and get 
completely different names for the columns in your result.

If you want to have any idea about your column names at all, assign each one 
using an "AS" clause.

If you want to pass the results of "SELECT * FROM ..." to something else and 
don't know the names of your columns, don't do a "SELECT * FROM ..." in the 
first place.  Use



to find the names of the columns which exist, and create your SELECT from that.

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

Re: [sqlite] sqlite_column_table_name() and table alias name

[Simon Slavin]

On 2 Nov 2014, at 6:28pm, Jose F. Gimenez  wrote:

> If you develop bussiness software, where you know exactly the querys because 
> you are writing them, then there is no problem at all. But other kind of 
> software, like a database manager, need all available metadata. And more 
> metadata info is better, for sure. And this is my case now.

Yep.  That's what



is for.

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

Re: [sqlite] Index without backing table

[Simon Slavin]

On 3 Nov 2014, at 9:50am, Paul  wrote:

> So, to be clear, WITHOUT ROWID table will have it's PRIMARY KEY 
> as a replacement for ROWID and table itself is an index?

It would appear that the answer is "yes".  I'm not going to go beyond the 
official documentation at



but if you have a specific question, please post it.

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

Re: [sqlite] Random locking errors using Sqlite.NET

[Simon Slavin]

On 4 Nov 2014, at 5:45pm, Mike McWhinney  wrote:

> I am getting random locking errors during the execution of my program. The 
> program resides on a network and each client has a mapped share
> with full read/write access to the folder as well as the .db file used by 
> SQLite.

I don't know if this is what's causing your specific problem, but ...



"SQLite will work over a network filesystem, but because of the latency 
associated with most network filesystems, performance will not be great. Also, 
the file locking logic of many network filesystems implementation contains bugs 
(on both Unix and Windows). If file locking does not work like it should, it 
might be possible for two or more client programs to modify the same part of 
the same database at the same time, resulting in database corruption. Because 
this problem results from bugs in the underlying filesystem implementation, 
there is nothing SQLite can do to prevent it.

A good rule of thumb is that you should avoid using SQLite in situations where 
the same database will be accessed simultaneously from many computers over a 
network filesystem."

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

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

[Simon Slavin]

On 5 Nov 2014, at 12:13pm, vita...@yourcmc.ru wrote:

> Which is of course very slow.

Can you please run ANALYZE then try the plans again ?

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

Re: [sqlite] SQLite as a meta database

[Simon Slavin]

On 5 Nov 2014, at 9:28pm, Edward Lau  wrote:

> Maybe some time in the future a version 4 be started that incorporates many 
> new advancement in the industry.  Version 3 can still be continued for 
> backwards compatibility and version 4 will break some but set the stage for 
> the future.

Like this, you mean ?



You might be particularly interested in the fact that there will be two 
different types of storage engine, both pluggable:



I don't understand the details on that page, but one of those two looks like it 
may allow more flexibility for external data supply than the current virtual 
table implementation.  But I may have misunderstood that.

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

Re: [sqlite] Is sqlite thread-safety sufficient for use with "Go" language ?

[Simon Slavin]

On 5 Nov 2014, at 10:05pm, nicolas riesch  wrote:

> Even if the user writes a Go program with only one logical thread, he has
> no control about which OS thread will process a function call.
> 
> This means that EACH SUCCESSIVE function in the sequence above can be
> processed on a DIFFERENT OS THREAD.
> 
> It means that to run safely, sqlite source code should not depend in any
> way on the identity of the threads, which must be fully interchangeable.
> So, the following conditions should be true. Are these sentences correct ?
> 
> 1) no local-thread-storage is used in sqlite code.
> 2) thread id (gettid()) are not used.
> 3) when a function of the API enters a mutex, it leaves it before the
> function returns.
>   Between two API function calls, no mutex should be locked (else, it
> would be impossible to ensure that the mutex is unlocked by the same thread
> that locked it).
> 4) all file locking information is attached to connections, and not to
> threads.

Since you don't already refer to it, can I ask that you read this page



and then ask any questions which remain, plus any new ones ?  You should 
probably tell us which threading mode you intend to use based on the needs you 
outline above.

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

Re: [sqlite] Is it a bug ?

[Simon Slavin]

On 6 Nov 2014, at 3:13am, Andrei Yakimov  wrote:

> Problem is incorrect journal file, which is created on system reboot.
> Reproduce this condition relativity simple:
> 
> step 1:  we open db
> step 2:  write/update something to db.
> step 3:  switch journal to memory
> step 4:  write/update something to db.
> Do not close you SW keep it running and DB open
> step 5:  reboot your system or kill you SW not gracefully.

Please see



"The MEMORY journaling mode stores the rollback journal in volatile RAM. This 
saves disk I/O but at the expense of database safety and integrity. If the 
application using SQLite crashes in the middle of a transaction when the MEMORY 
journaling mode is set, then the database file will very likely go corrupt."

In other words, SQLite is operating as designed.  The ability to keep the 
journal in memory is supplied for databases where, if the system crashes, you 
would have to begin the task again anyway.  If you need your database to be 
recoverable after a crash, sorry, but you can't use that mode.

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

Re: [sqlite] Bug report: typo in sqlite3.h

[Simon Slavin]

On 7 Nov 2014, at 9:18am, Philip Newton  wrote:

> The amalgamation-3080701 sqlite3.h file has a comment that reads, in part:
> 
> ** These no-op macros are used in front of interfaces to mark those
> ** interfaces as either deprecated or experimental.  New applications
> ** should not use deprecated interfaces - they are support for backwards
> ** compatibility only.  Application writers should be aware that
> ** experimental interfaces are subject to change in point releases.
> 
> In that comment, "they are support" should read "they are supported".

Or possibly a clearer phrasing might be "they support".  I'm not sure which the 
current phrasing actually means.

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

Re: [sqlite] Is it safe to use backup at shell level when an application is running

[Simon Slavin]

On 7 Nov 2014, at 10:33am, Yves Crespin  wrote:

> We use sqlite3_open_v2() with
> SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX flags and all the
> transactions are exclusive.
> 
> 
> So, if we add a SQLITE_BUSY handle, can we use the sqlite3 .backup
> when the application is running?
> 
> Is it safe or is there a risk to corrumpt the database or do we need
> to change some settings ?

The Backup API can not corrupt the data it is backing up.  The Backup API can 
not produce corrupt backups.  These are true no matter what your flags are, 
unless you are intentionally using bad flags for your setup.

However, the backup API works this way:



"If another thread or process writes to the source database while this function 
is sleeping, then SQLite detects this and usually restarts the backup process "

In other words, if you have a process which occasionally writes to the database 
faster (using a different connection) than an entire backup can be taken, the 
backup is restarted after each write.  Under those circumstances your backup 
will probably complete after normal database writing does down, perhaps after 
your users go home for the evening.

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

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

[Simon Slavin]

On 9 Nov 2014, at 1:49pm, Tristan Van Berkom  wrote:

> This year in particular I've been faced with my first queries of modest
> complexity, see for example this (temporary) paste:
> 
>http://www.fpaste.org/148918/41545194/
> 
> This is the beginnings of a query which will try to fetch an available
> employee for a given 'task', ordered eventually by their relevance,
> first set 'is it their job ?' second set 'do they have the licenses and
> formations to actually perform the task, even if it's not their job' ?
> and so on and so forth.
> 
> I've found that when trying to bend my brain around these problems of
> modest complexity, they are only understandable if I visualize them in
> terms of 'sets of rows' and how they join to each other.

I haven't looked into your specific example in depth, but you should also be 
aware of another solution to this problem: Make a good but simple SQL query 
that gets you some way to your data, then parse the resulting rows using your 
programming language.  This can lead to a far simpler program which is faster 
to write, easier to debug, and more flexible than what can be done entirely 
inside SQL.

Just because one can write complicated iterating queries with sub-SELECTs in 
SQL doesn't means one should.  There comes a point where it's batter to do the 
work inside whatever programming language you're using.

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

Re: [sqlite] SQLite where clause tree

[Simon Slavin]

On 10 Nov 2014, at 9:40am, Prakash Premkumar  wrote:

> If I am constructing this tree for where clause by myself, should i take
> the operator precedence in to account while constructing it or will sqlite
> take care of precedence,given any tree (i.e constructing it with out taking
> precedence into account) ?

SQLite takes operator precedence into account, using the precedence described 
in the section "Operators" on this page:



I found that by Googling 'SQLite precedence', selecting the top hit, then 
searching for 'precedence' on the page.

If you want any other precedence than the one this page describes, you should 
to use brackets.

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

Re: [sqlite] Virtual memory management of Sqlite core on Windows Mobile 6.5 with .NET CF 3.5

[Simon Slavin]

On 10 Nov 2014, at 3:45pm, Pavlo  wrote:

> Total Virtual memory allocations size made by Sqlite core seems to grow in
> time in application process even though Sqlite connection get closed on a
> regular basis.
> For example if we do search touching several tables in sqlite database it
> seems like sqlite core uses memory-mapped IO to do its job done before
> returning results of a query.
> There are of course other quires that touch almost all tables in the
> database. It results in growing virtual memory footprint and fragmentation.
> However it looks like Sqlite never releases that allocated virtual memory
> space in application process even after we close the sqlite connection and
> delete sqlite DB file from storage.
> So you imagine if for several days database changes and sqlite engine uses
> more and more memory-mapped pages we will end-up with "Out-of-memory"
> situations.

The last point may not be true (unless, of course, you have demonstrated it).  
SQLite is designed to intelligently use whatever memory is available.  It can 
expand to fill a lot of memory, but then stop.

Some things to check:

Check the values returned from /all/ sqlite3_ calls, including ones which 
finalize statements and close connections.  Anything that doesn't return 
SQLITE_OK can sabotage the way future calls use memory. Tracking down a missing 
_finalize() can completely change the memory footprint.

Secondly, from your above description I assume that your application doesn't 
use SQLite all the time it's running.  I think you're describing something that 
keeps a connection in use only part of the time.  If that's the case, as a 
debugging aid you might try manually calling



sqlite3_initialize() and sqlite3_shutdown() in that code, then look at memory 
usage after those calls.  This might magically fix your problem.  But it might 
also allow you to figure out what your problem actually is.  If SQLite is still 
using any significant resources after _shutdown(), something is wrong.

> Is there any logic behind when sqlite engine releases VM memory?

It shouldn't do anything very unusual.  But SQLite will not close a connection 
if a resource (e.g. an unfinalized statement) still exists for the connection.  
And that's the sort of thing that might trigger what you are reporting.

Simon.

PS: Thanks for your detailed description of your setup and concerns, which made 
it unnecessary to ask lots of questions before answering.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual memory management of Sqlite core on Windows Mobile 6.5 with .NET CF 3.5

[Simon Slavin]

On 10 Nov 2014, at 4:45pm, Pavlo  wrote:

> That main problem is that we are working with managed code and .net wrapper
> packaged in System.Data.Sqlite :) 

I apologise that I missed this.  Fortunately we have several readers to the 
list who understand that wrapper very well and may be able to help.

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

Re: [sqlite] Locking errors on network

[Simon Slavin]

On 10 Nov 2014, at 6:22pm, Mike McWhinney  wrote:

> So SQLite shouldn't be used at all on a network?

SQLite is not designed for hosting a database on a server for access by lots of 
different computers at the same time.  To do that efficiently you need a 
client/server design and SQLite doesn't have it.

> Aren't there any other provisions to handled the locking errors if/when
> they occur?

The problem is at the operating system and Network File System level.  The 
required support is often just too buggy to be usable.  See "Client/Server 
Applications" on this page:



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

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

[Simon Slavin]

On 10 Nov 2014, at 10:55pm, RP McMurphy  wrote:

>> 
> 
> Okay, for my simplified example analyze does improve the times. But for our 
> application this does not help, and it also harmed a couple of other queries.

If you can provide any examples where ANALYZE makes a query slower, I suspect 
the developer team would like to see them.

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

Re: [sqlite] Triggers and CTE's

[Simon Slavin]

On 11 Nov 2014, at 11:15pm, Ben Newberg  wrote:

> WITH RECURSIVE Weeks(wk) as (select 1 union all select wk + 1 from Weeks
> limit 10)
> INSERT INTO zWeeks (Week) select wk from Weeks;

Just use 10 INSERT commands.

I don't know what's causing your error message, but your code will be simpler 
if you just don't use RECURSIVE unnecessarily.

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

Re: [sqlite] Triggers and CTE's

[Simon Slavin]

On 11 Nov 2014, at 11:59pm, Ben Newberg  wrote:

> The 10 is just an arbitrary value I chose for this example. The user
> actually determines the value at run-time, so this value could be any
> integer. I have a way to settle that, if only I could figure out how I can
> get this trigger working.

Sorry, I see no way to do it.  Two alternatives:

1) Do it in your programming language.

2) Don't create the rows with default values.  Have your software assume that 
if the row doesn't exist, it has default values.

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

Re: [sqlite] [SQLite] Support for 23,10 Precision number format

[Simon Slavin]

On 13 Nov 2014, at 12:23pm, Dinesh Navsupe  wrote:

> Does any of SQLite data Type support 23,10 precision format for Number?
> 
> If yes, could you pleas help with right data type or approach to achieve
> this.

SQL stores REAL numbers in a REAL field which conforms to 64-bit IEEE 754 (as 
much as SQL permits).  This allows 16 decimal digits of precision.

You can store numbers of greater precision than that -- by storing them as 
strings or BLOBs.  You just can't have SQLite do its own maths on them.

> If No, then is there something that can be added to SQLite and how quickly?

It is unlikely that the developer team would be interested in doing this.  
Given that the source code for SQLite is open, you might want to implement them 
yourself.  However, the test library for having the developer team do this this 
would probably have to be huge.

SQLite4, which is not released yet, uses an 18-digit decimal number with a 
3-digit base-10 exponent.  It is possible that this might change if you are 
able to produce a good argument for doing so

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

Re: [sqlite] [SQLite] Support for 23,10 Precision number format

[Simon Slavin]

On 13 Nov 2014, at 1:01pm, Dinesh Navsupe  wrote:

> My need is 23 decimal digits of precision. We work on complex payout
> calculation engine where in formula outputs are quite large numbers and
> clients do not want to round off.

If you're working with floating-point numbers, you will get roundoff [1].  
Integer arithmetic really is how big banks work.  That way you don't have to 
test for rounding problems and never get accused of Salami Slicing.  If you 
really need money precision you'll be working in integers representing paise, 
pennies, halalas, whatever.  You'd have asked for 23 digit integers, not 23,10.

Since the abandonment of the Lira, no decimal country currency has needed 
anything more than three places of decimals for manipulation.  So for 23 digits 
of accuracy you seem to have a requirement to manipulate

100,000,000,000,000,000,000

units of currency with perfect accuracy.  Even the World Bank Group doesn't 
need that.  And I don't think the total wealth of any country in its own 
currency requires that many digits. 

I've worked with international financial organisations and we never did 
anything that needed 23 digits of precision, and that includes complicated 
cumulative interest calculations and those unbelievable asset value predictions 
that require integration and antilogs.

In summary, if you need ultimate precision, use integers.  If not, use 64-bit 
IEEE-571 like everyone else does without being sued.  If you somehow really 
need 23,10 maths, then you're going to have to write your own mathematical 
library anyway, because I'm not aware of any usable libraries which actually 
support 23,10 outside the world of physics.

Simon.

[1] This is a little hand-waving but only a little.  I'm only mentioning that 
because I don't want someone in the industry to dig this up and use it against 
me.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [SQLite] Support for 23,10 Precision number format

[Simon Slavin]

On 13 Nov 2014, at 3:44pm, Dominique Devienne <ddevie...@gmail.com> wrote:

> On Thu, Nov 13, 2014 at 3:38 PM, Simon Slavin <slav...@bigfraud.org> wrote:
> 
>> 100,000,000,000,000,000,000
> 
> Assuming he means Oracle's NUMBER(23, 10), and given [1], that's more
> 
> 9,999,999,999,999.99
> 
> i.e. "just" under 10 trillion max, with 10 decimal digits accuracy, and not
> 100 million trillion.

But he's using the field to store an amount of money in.  So why ask for 
anything with ten places after the decimal point ?  No genuine currency 
requires more than three places.

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

Re: [sqlite] [SQLite] Support for 23,10 Precision number format

[Simon Slavin]

On 14 Nov 2014, at 3:42am, James K. Lowden <jklow...@schemamania.org> wrote:

> Simon Slavin <slav...@bigfraud.org> wrote:
> 
>> I'm not aware of
>> any usable libraries which actually support 23,10 outside the world
>> of physics.
> 
> http://www.mpfr.org/#free-sw
> 
> I'm sure you're aware of such things.

Hey, you're right.  I was thinking about 128-bit stuff.  I forgot about 
arbitrary-precision libraries.  Thanks for the correction.

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

Re: [sqlite] sqlite Issue

[Simon Slavin]

On 14 Nov 2014, at 10:40am, ARVIND KUMAR  wrote:

> I am new for SQLite. I am trying to create database. But its not creating.
> I have attached the screenshot. Please find and do needful.

You cannot post screenshots to this mailing list.

Most problems with creating a new database are because you failed to specify an 
appropriate folder/directory for the database file.  Please make sure you have 
write access to the folder you specified.

If you are writing your own program which calls the SQLite API, and it is not 
creating the database, the function will return a value which is not SQLITE_OK. 
 Please tell us what value it is returning.

If you are using the SQLite Shell Tool to create your database, and it is not 
working, please post the error message it generates.

If you are using some other program besides the SQLite Shell Tool, then that 
program is written by a third party, not the SQLite team and the SQLite team 
can't do anything about your problem.  However, if you tell us what program 
you're using and what error message it shows we /might/ be able to advise you 
on what to try next.

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

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

[Simon Slavin]

On 14 Nov 2014, at 2:32pm, RP McMurphy  wrote:

> After we run analyze and then
> let the process run for a while the DB
> contents change - and it can change quite
> considerably depending
> upon what is
> happening
> 
> I suspect that the analyze data gets stale, but
> I don't know how to track such things in
> sqlite. Anyhow we can't keep running
> analyze every few minutes because it takes a
> long time to run with our DB
> and it appears
> to block all other actions until it is done.

Okay.  ANALYZE isn't used for specific information about every piece of data.  
It's used to evaluate the 'chunkiness' of each column and index.  For instance 
one column may only ever have two values in it -- "yes" or "no" -- whereas 
another column may have a different value in it for each row.

So unless the character of your data changes, if you run ANALYZE once when you 
have realistic data, you shouldn't need to run it again even if your tables 
grow to 100 times their original size.  You don't need to run ANALYZE under 
normal operation.  Perhaps once a year in a yearly maintenance run or 
something.  Or if you create a new index so SQLite can see how useful that 
index will be for certain operations.

As others have shown you using EXPLAIN QUERY PLAN, SQLite is itself capable of 
doing the expansion you describe to get an efficient query.  I would definitely 
not recommend you try beating the SQLite internals which have been developed 
and improved for years.  It's going to be more effective to see if you can spot 
some point at which your query starts executing slowly.

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

Re: [sqlite] Student's t-test table

[Simon Slavin]

On 16 Nov 2014, at 7:39am, Giuseppe Costanzi  wrote:

> do you know if a database exists in sqlite with the values, degrees of
> fredom vs  probability (alfa) of the t Student?

If you can find one in text or .csv format, we can tell you how to import it.

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

Re: [sqlite] Column name as a variable

[Simon Slavin]

On 17 Nov 2014, at 10:55am, Paul Sanderson  wrote:

> Is it possible to get a row count for each of the tables in a database
> using a SQL query.
> 
> i.e.
> 
> is there a way I could use each row in sqlite_master and use
> table_name to somehow do a select count(*) from
> sqlite.master.table_name

No, but you can use UNION to get something like it:

SELECT 'table1',count(*) FROM table1
UNION
SELECT 'table2',count(*) FROM table2

should do something like what you want.  As far as I know, there's no way 
within SQL to use a table name as a variable.  I suspect that this was done 
deliberately to enforce correct schema.

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

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

[Simon Slavin]

On 17 Nov 2014, at 12:48pm, RP McMurphy  wrote:

> Upon further analysis it appears that the data "shape" is different in 
> different periods within the table. That is, some sections have the inverse 
> shape to other sections. So it looked like query times would change over time 
> but actually they are changing because different portions of the table are 
> being accessed.

It would be difficult to spot that.  Nice analysis.

> Is it possible to tell sqlite to analyze different sections and keep separate 
> a record for each?

No, but the amount by which such a wrong choice should increase your execution 
time should be extremely slow.  Are you actually getting annoyingly long 
execution times or are you just trying to wring every last millisecond out of 
your application ?  If the latter, then I feel you should stop worrying about 
this and move onto something else.

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

Re: [sqlite] Column name as a variable

[Simon Slavin]

On 18 Nov 2014, at 8:46am, Paul Sanderson  wrote:

> my requirement, which is using a
> table name as a variable

This is deliberately made very difficult in SQL.  I think it's for security 
reasons.

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

Re: [sqlite] automatic index on sqlite_sq_#######

[Simon Slavin]

On 20 Nov 2014, at 7:45am, Yongil Jang  wrote:

> I've found that following log string when using sub-query.
> 
> "automatic index on sqlite_sq_9F222470(STAT_DATA_ID)"

This is not output by SQLite.  It is output by a program which uses SQLite.  It 
may indicate normal working and not a bug.

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

Re: [sqlite] Why is a b-tree sort required for this query?

[Simon Slavin]

On 20 Nov 2014, at 9:48pm, Oliver Smith  wrote:

> The t2c table has an index on id, name; I expected it would use that index so 
> that the data would be naturally in order.

As you've found, you cannot rely on this.  If you need an answer to a query to 
be in a specific order, specify it using ORDER BY.  It's the only way to be 
sure.

Don't try to second-guess the optimizer.  It occasionally does things that seem 
weird just because the numbers suggest they're optimal.

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

[sqlite] Testing the 'I' in ACID

[Simon Slavin]

The 'I' in ACID stands for 'isolation'.  In SQLite terms it means that a change 
made in one transaction doesn't affect another until the transaction is 
committed, at which point it does affect the database and transactions made 
afterwards.

SQLite doesn't support massive concurrency because it locks the entire database 
during changes.  So it may not have much of a problem enforcing 'I'.  This 
short article



and this code which goes with it



don't mention SQLite at all.  But I'd be interested in a review of how the 
reasoning and code would affect SQLite if they were applied to it.

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

Re: [sqlite] Testing the 'I' in ACID

[Simon Slavin]

On 25 Nov 2014, at 10:39pm, Igor Tandetnik <i...@tandetnik.org> wrote:

> On 11/25/2014 5:32 PM, Simon Slavin wrote:
>> SQLite doesn't support massive concurrency because it locks the entire 
>> database during changes.
> 
> Not entirely true. WAL mode allows one writer working concurrently with 
> multiple readers.

Fair point.  I should have written 'writing concurrency'.  I would be 
interested in anything you have to say about the article I referred to.

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

Re: [sqlite] Network and concurrency

[Simon Slavin]

On 26 Nov 2014, at 4:20pm, Mike McWhinney  wrote:

> Are there any other PRAGMA or connection string
> statements that can help with this concurrency issue?

If you haven't set a busy timeout then SQlite won't retry when there is a 
network clash, it will immediately return an error code.  If you have the 
ability to use PRAGMAs then I suggest you set your timeout



to 6 milliseconds for testing.  If this makes your problem go away that 
should identify your problem.

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

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Simon Slavin]

On 3 Dec 2014, at 2:20pm, Stephen Chrzanowski  wrote:

> Although I think there is already an error result, one situation might be
> when the DB is in a read only state.

I just thought of the database /file/ being marked 'read-only'.  But it turns 
out that there's a different SQLite result code for that situation.

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

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Simon Slavin]

On 3 Dec 2014, at 3:10pm, Hick Gunter  wrote:

> SQLITE_BUSY means that some connection is BUSY with a write transaction and 
> has locked the database file; presumably, it will be possible to write to the 
> database when the current writer has finished, just not now or within the 
> specified busy timeout.
> 
> SQLITE_LOCKED otoh means that the calling application is in error and has 
> specified two or more transactions whose table access modes are incompatible 
> and whose table access orders differ. This situation is resolvable only if at 
> least one involved transaction is rolled back.

This is very illuminating and far better information than I managed to find in 
the official SQLite documentation.  It would be really helpful if something 
like this could be incorporated in an appropriate place.

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

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Simon Slavin]

On 3 Dec 2014, at 3:20pm, Richard Hipp  wrote:

> https://www.sqlite.org/rescode.html#busy

Thanks, Richard.  I have somehow never seen that.

I had no idea that the difference between _BUSY and _LOCKED was purely about 
whether the conflicting access was from the same connection.

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

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Simon Slavin]

On 4 Dec 2014, at 5:36pm, Jonathan Moules  wrote:

> Depending on the application, an end user likely won't see the error code, 
> but instead just the error message 

SQlite is not a program.  It's an API, intended for use by a programmer.  Those 
error codes should not be reported to the end user.  They are intended for the 
user of the API: the programmer.  An end user is not expected to know what 
something like

SQLITE_BUSY_SNAPSHOT 

means.  What the programmer has their program do about them is the heart of the 
matter.

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

Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

[Simon Slavin]

On 8 Dec 2014, at 12:43am, David Barrett  wrote:

> Other alternatives we're considering are to fork and nice the process, or
> to call ioprio_set() directly, but I'm curious if there's a simpler way to
> do it.  Thanks!

VACUUM does the same job (in a very different way) as copying all the TABLEs, 
then creating the VIEWS, INDEXes and TRIGGERs on the new tables.  These can all 
be done using SQL statements.  Had you considered creating a VACUUMed copy 
yourself ?  You could do one table/view/index/trigger at a time.  And you could 
engineer a pause of a few seconds after every ten thousand rows are put in a 
table.

But I'm wondering why you need to VACUUM often enough that anything it does is 
a problem.  It can save filespace after deletion (before new data is put in to 
take up the released filespace), and it can increase speed, but the speed 
increase is small.  It's not needed in normal use.  It should be kept for a 
maintenance routine, perhaps once a month at most.  If your users are putting 
in more data than they are deleting, VACUUM has no noticable effect and I know 
of SQLite databases which have been amended daily for years without ever once 
having been VACUUMed.

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

Re: [sqlite] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on?

[Simon Slavin]

On 8 Dec 2014, at 2:24am, Dwight Harvey  
wrote:

> I know very little and Databases are complex and intimidating.
> 
> I figured out how to run queries but I don't know if they are
> correct/accurate, as in what I requested from the 'RUN' results?
> 
> How do you 'VERIFY' your query results?

I bet if you read over your notes or your textbook you'll find that your 
instructor explained how to do this sometime during your instruction.  But 
without knowing what you instructor intended you to do we might be able to take 
some guesses.

If you could list every row of the tables in your query you could look down 
them yourself and see what you think the result of the query should be.  So you 
can do that, just use

SELECT * FROM dbo.employees

Obviously in real life tables get huge and it's not practical to do this.  
That's why when you test your code you make up small dummy tables to check that 
things are working correctly.  And you make up appropriate sample data to test 
two kinds of errors:

false positive: returning a row you didn't mean to return
false negative: not returning a row you should have returned

Good luck with your course.

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

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

[Simon Slavin]

On 8 Dec 2014, at 1:31pm, Gwendal Roué  wrote:

> We share the same conclusion. I even tried to decorate the update query with 
> "ORDER" clauses, in a foolish attempt to reverse the ordering of row updates, 
> and circumvent the issue.

A way to solve this is to use REAL for page numbers instead of INTEGER.  To 
insert a page between two existing ones, give it a number which is the mean of 
the two pages you're inserting it between.  Every so often you can run a 
maintenance routine which renumbers all pages to integers.

Alternatively, store your pages as a linked list.

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

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

[Simon Slavin]

On 8 Dec 2014, at 3:05pm, Gwendal Roué  wrote:

> Why not an opt-in way to ask for deferred constraint checking. The key here 
> is only to allow perfectly legit requests to run. With all the due respect to 
> sqlite implementors and the wonderful design of sqlite.

SQL-99 includes a syntax for deferred checking.  We don't need to invent our 
own syntax with a PRAGMA.  However, it is done when the constraint is defined 
rather than being something one can turn on or off.  So you would need to think 
out whether you wanted row- or transaction-based checking when you define each 
constraint in the first place.

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

Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

[Simon Slavin]

On 9 Dec 2014, at 1:36am, David Barrett  wrote:

> *Re: Why VACUUM.*  We vacuum weekly.  This particular database is a
> "rolling journal" -- we are constantly adding new rows to the end of the
> table, and every week we truncate off the head of the journal to only keep
> 3M rows at the "tail".  Given that we're truncating the "head", without
> vacuuming we'd be inserting the new rows at the "front" of the database
> with the old rows at the "end" -- and then each truncation would leave the
> database more and more fragmented.  Granted, this is on SSDs so the
> fragmentation doesn't matter a *ton*, but it just adds up and gets worse
> over time.  Anyway, agreed it's not the most important thing to do, but all
> things being equal I'd like to do it if I can to keep things clean and
> snappy.

Okay.  I have some great news for you.  You can completely ignore VACUUMing 
without any time or space drawbacks.  You're wasting your time and using up the 
life of your SSD for no advantage.

Fragmentation ceases to become a problem when you move from rotating disks to 
SSD.  SSD is a truly random access medium.  It's no faster to access block b 
then block b+1 than it is block b then block b+1000.  Two contiguous blocks 
used to be faster in rotating disks only because there is a physical read/write 
head and it will already be in the right place.  SSDs have no read/write head.  
It's all solid state and accessing one block is no faster than another.

Delete old rows and you'll release space.  Insert new rows and they'll take up 
the space released.  Don't worry about the internal 'neatness' of the file.  
Over a long series of operations you might see an extra block used from time to 
time.  But it will be either zero or one extra block per table/index.  No more 
than that.  A messy internal file structure might niggle the OCD side of your 
nature but that's the only disadvantage.

Also, SSD drives wear out fast.  We don't have good figures yet for 
mass-produced drives (manufacturers introduce new models faster than the old 
ones wear out, so it's hard to gather stats) but typical figures show a drive 
failing in from 2,000 to 3,000 write cycles of each single block.  Your drive 
does something called 'wear levelling' and it has a certain number of blocks 
spare and will automatically swap them in when the first blocks fail, but after 
that your drive is smoke.  And VACUUM /thrashes/ a drive, doing huge amounts of 
reading and writing as it rebuilds tables and indexes.  You don't want to do 
something like that on an SSD without a good reason.

So maybe once every few years, or perhaps if you have another more complicated 
maintenance routine which already takes up lots of time, do a VACUUM then.  But 
it doesn't really matter if you never VACUUM.  (Which is entirely unlike my 
home, dammit.)

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

Re: [sqlite] seeking advice

[Simon Slavin]

On 9 Dec 2014, at 8:41pm, Rene Zaumseil  wrote:

> Version 3: One table with time stamp, parameter id and parameter value
>  - Is it working when all values change?
>  - Is retrieving values for one parameter fast?

That one.  Versions 1 & 2 will both, technically, work, but they're abuse of 
how SQL should be used and will result in horrible code.

The speed for retrieving all parameters will be bound by your programming 
language.  SQLite will do its side of the job very quickly.  And since columns 
have just affinity and not type, having some values INTEGER and other REAL will 
work fine.

> I will write and read the data on the same time. But writing should have 
> priority.

Multithread ?  Multiprocess ?  Neither are needed, but those are the things you 
need to decide on next.

Also, do not forget to pick a journal mode and to set a busy_timeout.  Ah, I 
see you already mentioned journal_mode.  Good.

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

Re: [sqlite] Online/Hot backup of WAL journalling mode database

[Simon Slavin]

On 9 Dec 2014, at 8:57pm, Nick  wrote:

> Environment is Linux with multiple (c. 4-6) processes accessing a single 
> sqlite database named "test.db".
> 
> Backup:
> - New process started using cronjob to initiate application checkpoint until 
> completion.
> - rsync diff the file "test.db" to another drive/location (specifically 
> ignoring the "-shm" and "-wal" file).
> - exit process
> 
> Restore:
> - rsync the file "test.db" from another drive/location.

Will not be trustworthy if the database is being written to during the rsync 
operations.  Recommend either of the following:

A) Ensure all processes besides the backup process have the database closed 
while it is being copied. Establish some kind of semaphore so they can tell 
when it's safe to open the database again.

B) Use the SQLite Backup API which was invented to do what you want.

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

Re: [sqlite] Online/Hot backup of WAL journalling mode database

[Simon Slavin]

On 10 Dec 2014, at 12:30am, Nick  wrote:

> That's interesting Simon I didn't expect the database not to be trustworthy.

The database will be trustworthy at any instant.  Your copy of it will be 
corrupt because the file will be changing while you are copying it.

> In WAL mode I thought the database file is only written to when 
> checkpointing. Have I misunderstood this journaling mode?

How do you intend to prevent your other processes from checkpointing while you 
take the backup ?  You can disable checkpointing for your own connection to the 
database but not for the connections other processes have.

> Again I may have misunderstood the docs around the Backup API, does it not 
> start again from the beginning copying pages if another process writes to the 
> database during the process? In practice could it successfully backup a 2GB 
> database that is being written to once a second?

Not if the writing never stopped.  But there's no way to take a copy of a file 
which is constantly being rewritten.  rsync can't do it either.  How can 
anything copy a file which is constantly being modified ?

You can BEGIN EXCLUSIVE and then END once your backup is finished.  That should 
prevent other processes writing to the file.  You will have to deal with what 
happens if your BEGIN EXCLUSIVE times out, and you will have to put long 
timeouts in your other processes so they can handle the file being locked long 
enough for the entire copy to be taken.  That's the only way I can think of to 
do it.  And yes, it will prevent writing to the database while it's being 
copied.

On the other hand, there's a different way to clone a database: log the changes.

When something issues an INSERT/DELETE/UPDATE command, execute the command but 
also append a copy of that command to a text file somewhere.  When you want to 
bring your backup copy up-to-date, take a copy of the log file, then execute 
all the commands in it to your out-of-date copy.

You need a method of zeroing out the log file, or knowing where you got to on 
your last backup.

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

[sqlite] replace many rows with one

[Simon Slavin]

Dear folks,

A little SQL question for you.  The database file concerned is purely for data 
manipulation at the moment.  I can do anything I like to it, even at the schema 
level, without inconveniencing anyone.

I have a TABLE with about 300 million (sic.) entries in it, as follows:

CREATE TABLE s2 (a TEXT, b TEXT, theCount INTEGER)

There are numerous cases where two or more rows (up to a few thousand in some 
cases) have the same values for a and b.  I would like to merge those rows into 
one row with a 'theCount' which is the total of all the merged rows.  
Presumably I do something like

CREATE TABLE s2merged (a TEXT, b TEXT, theCount INTEGER)

INSERT INTO s2merged SELECT DISTINCT ... FROM s2

and there'll be a TOTAL() in there somewhere.  Or is it GROUP BY ?  I can't 
seem to get the right phrasing.

Also, given that this is the last operation I'll be doing on table s2, will it 
speed things up to create an index on s2 (a,b), or will the SELECT just spend 
the same time making its own temporary index ?

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

Re: [sqlite] Online/Hot backup of WAL journalling mode database

[Simon Slavin]

On 10 Dec 2014, at 10:40pm, Nick  wrote:

> All the processes would have automatic checkpointing disabled. Just the 
> backup process would perform the checkpoint.

I don't know enough about the internals of SQLite to be sure, but various parts 
of me are concerned that this is a bad idea.  I don't know what WAL mode would 
be like without checkpointing but there has to be a reason for checkpointing 
and disabling it between backups sounds bad.

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

Re: [sqlite] How to speed up database open

[Simon Slavin]

On 11 Dec 2014, at 11:51am, Paul  wrote:

> I understand, that having them is a must for a decent performance. 
> In my specific case I have millions of individual database files. 
> This is one, among other reasons that I can't keep them open all the time. 
> Just too many of them. These databases are being opened frequently. 
> Let's say 500 times per second. In most cases, just to query a single row. 
> Ironically, querying takes only a handful of microseconds, and most 
> CPU time is spent reading same database structure over and over again.
> 
> Can you please make some advice, what can be done to reduce this overhead?

The problem with this is that it cannot be solved by SQLite's programmers 
because most of the time is taken by operating system calls.  Merely opening a 
file (which you no doubt know is not done by sqlite_open() but delayed until 
the first access) is a time-consuming procedure.  Once SQLite has access to the 
data it is, as you have shown, very fast.

You explain that you have millions of individual database files.  Is that the 
only reason you can't open the database and keep it open, or are there others ? 
 Also, do all these separate databases have the same tables with the same 
columns in ?

My normal advice would be that before you start querying you merge your 
millions of separate database files into one big one.  Judging by the degree of 
technical information in your question you don't need me to suggest ways of 
programming or scripting this, or of keeping a merged central copy up-to-date.  
The only question is whether it is appropriate to your circumstances.

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

Re: [sqlite] replace many rows with one

[Simon Slavin]

On 10 Dec 2014, at 3:40pm, RSmith  wrote:

> INSERT INTO s2merged SELECT a, b, sum(theCount) FROM s2 GROUP BY a,b;

Thanks to Martin, Hick and R for this solution.  It was just what I was looking 
for.

> Not sure if your theCount field already contains totals or if it just has 
> 1's...  how did duplication happen? 

The existing rows contain totals.  Or maybe I should call them subtotals.  The 
data is being massaged from one format to another.  I did a bunch of stuff when 
it was text files, then imported it into SQLite and did a bunch more on it as 
rows and columns.  Eventually it'll end up in SQLite.

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

[sqlite] Counting rows

[Simon Slavin]

In my table which had about 300 million (sic.) rows I did this

SELECT count(*) FROM myTable;

to count the number of rows.  After half an hour it was still processing and I 
had to kill it.

I know that the internal structure of a table means that this number isn't 
simple to produce.  But is there really no faster way ?  This table is going to 
have about six times that amount soon.  I really can't count the rows in less 
than a few hours ?

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

Re: [sqlite] Counting rows

[Simon Slavin]

On 11 Dec 2014, at 3:58pm, Paul Sanderson  wrote:

> would count _rowid_ from mytable be quicker

Hmm.  Given that these tables have the normal use of rowid, and that rows in 
this table are only inserted, never deleted, I wonder whether

SELECT max(rowid) FROM myTable

would have given the right result, almost instantly.  Can't check it now, but 
thanks for the idea, Paul.

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

Re: [sqlite] Counting rows

[Simon Slavin]

On 11 Dec 2014, at 4:39pm, Dominique Devienne  wrote:

> I have a little utility that connects to Oracle, and does a big UNION ALL
> query to get the counts of all my tables (82 currently):

Yeah, it's easy in Oracle.  The problem is that SQLite3 uses a tree to store 
lists, and it does not store the total number of entries separately.  So to 
count the number of rows in a table SQLite has to walk the entire tree: go up 
and down all the branches to find which rows exist, whether any have been 
deleted, etc..

SQLite4 uses a different file format and I understand it does not have this 
problem.  Which doesn't help me at all right now.

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

Re: [sqlite] Online/Hot backup of WAL journalling mode database

[Simon Slavin]

On 12 Dec 2014, at 10:27am, Clemens Ladisch  wrote:


> If you write your own backup tool that simply calls
> "sqlite3_backup_step(b, -1)", the entire database is copied in
> a single atomic transaction.

OP's problem is that he runs several processes which are constantly (every few 
seconds) writing to the database he needs to copy.  So any operation which 
locks the database for a long period would mean that some data was not captured.

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

Re: [sqlite] Feature Request - RowCount

[Simon Slavin]

On 13 Dec 2014, at 12:38pm, Richard Hipp  wrote:

> Also, if there are indices available, SQLite attempts to count the smallest
> index (it has to guess at which is the smallest by looking at the number
> and declared datatypes of the columns) and counting the smallest index
> instead, under the theory that a smaller index will involve less I/O.

Would it not be faster to just count the number of pages each index takes up ?  
Uh ... no.
Wow.  You really don't like storing counts or sizes, do you ?

> To do better than this requires, as far as I know, an incompatible file
> format change and/or a performance hit for applications that do not use the
> feature.

Can you tell us whether the problem exists in SQLite4 ?  I know it uses a 
different format for indexes.  I tried checking the documentation but didn't 
see an answer that didn't involve more work than I felt like doing.

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

Re: [sqlite] Feature Request - RowCount

[Simon Slavin]

On 13 Dec 2014, at 7:46pm, James K. Lowden  wrote:

> Every DB Admin tool I've ever used proved to be more hinderance than
> help.  They seem to be written by the moderately competent to help the
> novice, and run out of gas or fall over when faced with anything
> complex.  [snip]
> 
> My first question, then, is whether or not the rowcount is so
> interesting that it must be known before a table can be operated on.
> I suggest the answer is No.  The relative & approximate sizes of the
> tables is known to the admin in most cases and, when it is not, the
> information is readily discovered on a case-by-case basis. [snip]

All true.  Yet when I wrote my own DB Admin tool (suitable only for my own use, 
of no interest to anyone else) I included the same feature in it.  When you 
click on a TABLE to select it the count(*) pops up along with information about 
the table's structure.  I had no real idea why I put that in, it just seemed a 
natural thing to do.

> That said, I'm puzzled why rowcount isn't maintained and exposed in
> SQLite as part of a table's metadata, particularly when indexes/keys are
> present.  The cost of maintaining a rowcount is small, in terms of
> computation and complexity.  ISTM it is valuable information to the
> system itself in evaluating query-plan costs.

It does seem that knowing count(*) would be a very good thing to know for 
evaluating query-plan costs.  I hope SQLite4 stores it.

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

Re: [sqlite] Feature Request - RowCount

[Simon Slavin]

On 14 Dec 2014, at 11:08am, Jean-Christophe Deschamps  wrote:

> Without using slow triggers or changing the v3 file format there is still 
> another possibility which could be implemented relatively easily. All it 
> would need is a new pragma (or internal function) like "pragma row_count=0/1" 
> and some code.
> 
> On invokation, the engine would create a hidden "system" table like 
> sqlite_rowcount --similar to sqlite_sequence-- which would initially hold row 
> counts for every table in the DB.

Two obvious places:

A) In that sqlite_sequence table you mentioned, as an additional column.  
Always up-to-date.

B) In the tables prepared by SQLite ANALYZE.  If you want the rowcount updated, 
do another ANALYZE.

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

[sqlite] '.timer on' in the shell tool

[Simon Slavin]

Okay.  I used '.timer on' in the shell tool.  SQLite 3.7.13, if it matters.  
Here are two sample lines I got in response to different INSERT ... SELECT 
commands:

CPU Time: user 880.710398 sys 353.260288

CPU Time: user 5073.001124 sys 11609.266484

The two commands were issued one after another on a computer which was 
otherwise idle.

Question 1: What are the units ?

Question 2: I would have expected consistency in that user time was always 
greater than system time.  Or perhaps the other way around.  Why is a different 
one greater for the two examples ?

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