Re: [sqlite] Transaction involving multiple attached databases

2013-11-26 Thread Hick Gunter 
Not without reading the source code and patching it at a very deep level.

In our setup we use virtual tables almost exclusively and these do not support 
transactions. There are also dedicated writers for each set of tables, so table 
locking outside of SQLite is sufficient. The only changes that require SQLite 
to lock the database file(s) are metadata changes (CREATE VIRTUAL TABLE and 
DROP TABLE). I have added a second bitmap to the code generator that allows me 
to suppress the Cookie opcode unless a VCreate or VDestroy opcode is generated 
on a specific database file.

There are some caveats and this is not quite ACID but sufficient for our 
application, which uses SQL as a universal query interface for application 
status, report generation and configuration updates.

-Ursprüngliche Nachricht-
Von: Joshua Grauman [mailto:jnf...@grauman.com]
Gesendet: Dienstag, 26. November 2013 20:25
An: sqlite-users@sqlite.org
Betreff: [sqlite] Transaction involving multiple attached databases

Hello all,

If I have multiple databases attached and then do a:
BEGIN EXCLUSIVE

I assume all the sqlite3 tables involved get locked?
Is there a way to lock only one of the attached tables?

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


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] count (*) performance

2013-11-26 Thread veeresh kumar 
Hi ,

I see that in release history for 3.8.1 below item.
"Estimate the sizes of table and index rows and use the smallest applicable 
B-Tree for full scans and "count(*)" operations."


Does it mean that performance of count(*) has been improved in 3.8.1 and if yes 
by how much? We were using count (*) to get row count in our application and it 
was terribly slow if we have 2 Million records.Just wanted to know if we 
upgrade from 3.8.0 to 3.8.1 or 3.8.2 will there be any improvement in the query 
performance?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

2013-11-26 Thread James K. Lowden 
On Mon, 25 Nov 2013 19:44:15 +0100
Luuk  wrote:

> On 25-11-2013 13:41, Simon Slavin wrote:
> > I'm wondering whether there's an argument that it should be
> > evaluated just once for a transaction.
> 
> I'm still thinking about this question,
> 
> i can think of some benefits if its evaluated just once per
> transaction.
> 
> but i hope anyone has some examples why its better to evalutate it
> once per statement.

The easy answer is: that's how SQL is defined.  

A more complicated answer is that, inside a user-defined transaction,
you have control over the meaning of "now".  You can insert "now" into
a table and re-use it as often as you like, without fear of it being
changed by another process.  If, on the other hand, you'd like to
execute several statements in a transaction, you might also like to
know when "now" is for each one.  The per-statement definition of "now"
supports that use.  

There's actually nothing special about "now".  Other functions, e.g.
changes(), may vary between statements in a transaction.  (It happens
SQLite doesn't have many such.)  You really just want to preserve the
function's defintion as "returns correct output each time it's
called".  

HTH.  

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

[sqlite] SQLite version 3.8.2 release schedule

2013-11-26 Thread Richard Hipp 
SQLite version 3.8.2 is tentatively scheduled for release on 2013-12-10.
That date can move in either direction.

Snapshots of the latest beta code can be obtained from the
http://www.sqlite.org/download.html download page.  A summary of changes in
the 3.8.2 release can be seen at
http://www.sqlite.org/draft/releaselog/current.html

The version 3.8.2 status board (
http://www.sqlite.org/checklists/3080200/index) is now online.  Sometime
next week, if we stay on schedule, the developers will begin checking off
items the status board.  Individual status items turn green upon successful
validation.  (Various other colors show other states, of course.)  The
release will occur after the status board goes all-green.

If you have any issues or concerns with the latest SQLite code, now would
be a very good time to raise them.

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

Re: [sqlite] Transaction involving multiple attached databases

2013-11-26 Thread Peter Aronson 
And you can't execute it at all if any of the attached databases are read-only.
 
Peter


>On 11/26/2013 2:24 PM, Joshua Grauman wrote:
>> If I have multiple databases attached and then do a:
>> BEGIN EXCLUSIVE
>> 
>> I assume all the sqlite3 tables involved get locked?
>
>All the database files are locked.
>
>> Is there a way to lock only one of the attached tables?
>
>I'm pretty sure there isn't, short of creating a connection directly to a 
>single database and not attaching any others to it.
>-- Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction involving multiple attached databases

2013-11-26 Thread Igor Tandetnik 

On 11/26/2013 2:24 PM, Joshua Grauman wrote:

If I have multiple databases attached and then do a:
BEGIN EXCLUSIVE

I assume all the sqlite3 tables involved get locked?


All the database files are locked.


Is there a way to lock only one of the attached tables?


I'm pretty sure there isn't, short of creating a connection directly to 
a single database and not attaching any others to it.

--
Igor Tandetnik

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

[sqlite] Transaction involving multiple attached databases

2013-11-26 Thread Joshua Grauman 

Hello all,

If I have multiple databases attached and then do a:
BEGIN EXCLUSIVE

I assume all the sqlite3 tables involved get locked?
Is there a way to lock only one of the attached tables?

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

Re: [sqlite] Failed test on aarch64

2013-11-26 Thread Richard Hipp 
Checkin http://www.sqlite.org/src/info/6f53fc7106 is an important behavior
change for SQLite.

The check-in above changes the behavior of REAL-to-INTEGER casts where the
REAL value is larger than the largest possible integer.  For example:

SELECT CAST(9223372036854775808.0 to INTEGER);

All historic versions of SQLite running on x86/x64 hardware generate an
answer of -9223372036854775808 for the above.  This is what casting an
over-size double to a long long int on intel hardware does, and since
SQLite was developed primarily on x86 workstations that is the behavior it
has previously exhibited. The change causes the query to yield
9223372036854775807, which seems a more sensible answer, and is in fact
what sparc hardware does.  SQLite now always gives the second answer (the
sparc answer) regardless of the platform it is running on.

One might argue that this is an incompatible change, but I'd like to argue
that it is a bug fix.

Cross-posted to sqlite-...@sqlite.org because this is an important change
that needs to be reviewed carefully before the next release.





On Tue, Nov 26, 2013 at 8:26 AM, Richard Hipp  wrote:

>
>
>
> On Tue, Nov 26, 2013 at 6:39 AM, Jan Staněk  wrote:
>
>> Hello,
>> I'm trying to build sqlite for aarch64 (ARMv8) and one of the expression
>> tests is failing (specifically e_expr-31.2.4) with:
>> > Expected: [integer -9223372036854775808]
>> >  Got: [integer 9223372036854775807]
>> From the comment, I gather that this should test correct CASTing from
>> REAL to INT, and that somehow it returns the wrong extreme (largest
>> positive integer instead of largest negative).
>>
>> Can someone point me where in the source is that implemented? I would
>> like to try to fix it, but I'm unable to find the actual execution of
>> the CAST.
>>
>
> The test in question is this:
>
> SELECT cast(9223372036854775809.0 AS INT);
>
> The expected answer is (surprisingly) -9223372036854775808.  This goes
> back to a change in 2008.  We added a comment in 2009 saying that this is
> the intended behavior.  See
> http://localhost:591/sqlite/info/7f3be3608542bbc6
>
> The idea was apparently to emulate the behavior seen in this test program:
>
> #include 
> int main(int argc, char **argv){
>   long long int x;
>   double r;
>   r = 9223372036854775809.0;
>   x = (long long int)r;
>   printf("%lld\n", x);
>   return 0;
> }
>
> The test program above, on linux, returns -9223372036854775808.  Now, 5
> years later, I'm having my doubts.  Maybe I should change the behavior of
> SQLite to make sense, rather than to emulate the behavior of floating point
> hardward on x86 linux...
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>



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

Re: [sqlite] Failed test on aarch64

2013-11-26 Thread Richard Hipp 
On Tue, Nov 26, 2013 at 6:39 AM, Jan Staněk  wrote:

> Hello,
> I'm trying to build sqlite for aarch64 (ARMv8) and one of the expression
> tests is failing (specifically e_expr-31.2.4) with:
> > Expected: [integer -9223372036854775808]
> >  Got: [integer 9223372036854775807]
> From the comment, I gather that this should test correct CASTing from
> REAL to INT, and that somehow it returns the wrong extreme (largest
> positive integer instead of largest negative).
>
> Can someone point me where in the source is that implemented? I would
> like to try to fix it, but I'm unable to find the actual execution of
> the CAST.
>

The test in question is this:

SELECT cast(9223372036854775809.0 AS INT);

The expected answer is (surprisingly) -9223372036854775808.  This goes back
to a change in 2008.  We added a comment in 2009 saying that this is the
intended behavior.  See http://localhost:591/sqlite/info/7f3be3608542bbc6

The idea was apparently to emulate the behavior seen in this test program:

#include 
int main(int argc, char **argv){
  long long int x;
  double r;
  r = 9223372036854775809.0;
  x = (long long int)r;
  printf("%lld\n", x);
  return 0;
}

The test program above, on linux, returns -9223372036854775808.  Now, 5
years later, I'm having my doubts.  Maybe I should change the behavior of
SQLite to make sense, rather than to emulate the behavior of floating point
hardward on x86 linux...



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

Re: [sqlite] Failed test on aarch64

2013-11-26 Thread Clemens Ladisch 
Jan Staněk wrote:
> I'm trying to build sqlite for aarch64 (ARMv8) and one of the expression
> tests is failing (specifically e_expr-31.2.4) with:
>> Expected: [integer -9223372036854775808]
>>  Got: [integer 9223372036854775807]
> From the comment, I gather that this should test correct CASTing from
> REAL to INT, and that somehow it returns the wrong extreme (largest
> positive integer instead of largest negative).
>
> Can someone point me where in the source is that implemented?

sqlite> explain select cast(123.45 as integer);
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
...
2 Real   0 1 0 123.45 00
3 ToInt  1 0 000
...

In sqlite3.c, the OP_ToInt implementation calls sqlite3VdbeMemIntegerify,
which calls sqlite3VdbeIntValue, which calls doubleToInt64:

static i64 doubleToInt64(double r){
#ifdef SQLITE_OMIT_FLOATING_POINT
  /* When floating-point is omitted, double and int64 are the same thing */
  return r;
#else
  /*
  ** Many compilers we encounter do not define constants for the
  ** minimum and maximum 64-bit integers, or they define them
  ** inconsistently.  And many do not understand the "LL" notation.
  ** So we define our own static constants here using nothing
  ** larger than a 32-bit integer constant.
  */
  static const i64 maxInt = LARGEST_INT64;
  static const i64 minInt = SMALLEST_INT64;

  if( r<(double)minInt ){
return minInt;
  }else if( r>(double)maxInt ){
/* minInt is correct here - not maxInt.  It turns out that assigning
** a very large positive number to an integer results in a very large
** negative integer.  This makes no sense, but it is what x86 hardware
** does so for compatibility we will do the same in software. */
return minInt;
  }else{
return (i64)r;
  }
#endif
}

... so I guess the value of SMALLEST_INT64 might not be computed correctly:

/*
** Constants for the largest and smallest possible 64-bit signed integers.
** These macros are designed to work correctly on both 32-bit and 64-bit
** compilers.
*/
#define LARGEST_INT64  (0x|(((i64)0x7fff)<<32))
#define SMALLEST_INT64 (((i64)-1) - LARGEST_INT64)


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

[sqlite] Failed test on aarch64

2013-11-26 Thread Jan Staněk 
Hello,
I'm trying to build sqlite for aarch64 (ARMv8) and one of the expression
tests is failing (specifically e_expr-31.2.4) with:
> Expected: [integer -9223372036854775808]
>  Got: [integer 9223372036854775807]
>From the comment, I gather that this should test correct CASTing from
REAL to INT, and that somehow it returns the wrong extreme (largest
positive integer instead of largest negative).

Can someone point me where in the source is that implemented? I would
like to try to fix it, but I'm unable to find the actual execution of
the CAST.

Thanks in advance.
-- 
Jan Stanek - Red Hat Associate Developer Engineer - Databases Team
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite version 1.0.89.0 released

2013-11-26 Thread Jan Slodicka 
>> 2) SqliteDataReader.GetOrdinal() 

> This does not appear to be entirely compatible, most notable due to not
> taking the keyInfo into account. 

Sorry, I forgot that we eliminated all keyInfo-related code.

Anyway, it still would make sense to extend caching to all column names that
were visited during the search.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/System-Data-SQLite-version-1-0-89-0-released-tp71904p72605.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does sqlite has db file-size restriction on Solaris 10?

2013-11-26 Thread Pepijn Van Eeckhoudt 
Ah ok, I thought the Xerial driver no longer supported the nestedvm 
stuff. What you'll need to do is compile the Xerial binary for Solaris. 
See https://bitbucket.org/xerial/sqlite-jdbc/wiki/Home#BuildfromSource 
for details.


Pepijn

On 11/26/2013 06:48 AM, Liang Kunming wrote:
Hi, Pepijn, After confirm and compare the content, the jdbc driver 
which we are using to access sqlite is from Xerial fork 
(https://bitbucket.org/xerial/sqlite-jdbc): 
sqlite-jdbc-3.7.2.jar. 
Do you have any other suggestion for this issue? Thanks. 
sqlite-jdbc-3.7.2.jar 
3.1 MB 2013-01-23 48150 Regards, Liang Kunming. -Original 
Message- From: sqlite-users-boun...@sqlite.org 
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Liang Kunming 
Sent: 2013年11月25日 17:29 To: General Discussion of SQLite Database 
Subject: Re: [sqlite] Does sqlite has db file-size restriction on 
Solaris 10? Hi, Pepijn, Thanks. Please help me to do some testing and 
check whether the 2GB restrict issue be solved if use new driver. 
Regards, Liang Kunming. -Original Message- From: 
sqlite-users-boun...@sqlite.org 
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pepijn Van 
Eeckhoudt Sent: 2013年11月25日 16:09 To: sqlite-users@sqlite.org 
Subject: Re: [sqlite] Does sqlite has db file-size restriction on 
Solaris 10? On 2013-11-23 02:03, Liang Kunming wrote:
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database 
(no such table: tasks) 
at org.sqlite.DB.newSQLException(DB.java:383) 
at org.sqlite.DB.newSQLException(DB.java:387) 
at org.sqlite.DB.throwex(DB.java:374) 
at org.sqlite.NestedDB.prepare(NestedDB.java:134) 
at org.sqlite.DB.prepare(DB.java:123) 
NestedDB in the stack trace indicates you're using the old Zentus JDBC 
driver in NestedVM (http://nestedvm.ibex.org/) mode. I would first try 
to take that out of the equation and see if it solves the problem. To 
do that you'll need to compile the native library part of the Zentus 
driver for Solaris, but since the source code is no longer available 
that might be hard to do (unless you have a copy somewhere). Using the 
Xerial fork (https://bitbucket.org/xerial/sqlite-jdbc) is probably 
easier. It doesn't ship with a Solaris binary though, so you'll still 
need to compile that first. Pepijn 
___ sqlite-users mailing 
list sqlite-users@sqlite.org 
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
___ sqlite-users mailing 
list sqlite-users@sqlite.org 
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
___ sqlite-users mailing 
list sqlite-users@sqlite.org 
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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