Re: [sqlite] custom property db/editor

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/02/12 22:06, Bill McCormick wrote:
> ... building and editing recipes ...

For real world messy data I'm a huge fan of documented oriented databases
(aka NoSQL).  There is no schema so you don't have to make every item
conform to the same rules, and JSON or something substantially similar is
the native format for data.

My favourite is MongoDB - you can get a Perl flavour here:

  http://search.cpan.org/dist/MongoDB/lib/MongoDB/Tutorial.pod

[There is a contingent that argue stringent schema rules, the relational
model and decades of operational history is always the right answer and
developers doing things this new fangled way will remake the old mistakes
and learn their lesson eventually.]

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk82DcsACgkQmOOfHg372QQNfACgvmUwe4h/OZk2bluxzB1WY6Jh
iloAnRm97wwW8eAI8QLpe8bDWB6I+9d+
=55vA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] custom property db/editor

[Bill McCormick]

I need to develop a light-weight, custom property editor using SQLite, 
Perl, thttpd and HTML/CSS for building and editing recipes. I'm thinking 
of something like the classic "folder/tree/property sheet" interface and 
it will (obviously) run in a web browser


I suppose I could use XML as well, but I'm leaning toward SQLite since 
I'm already using it in another part of the project. Perhaps SQLite has 
some features that could make this easier? Or maybe somebody out there 
has run across some open source tools (CPAN?) to help with this? Or if 
you live in S. TX (RGV), I could arrange to pay you to do this.


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

Re: [sqlite] SQLite Random number generator

[Igor Tandetnik]

On 2/10/2012 10:52 PM, Rick Guizawa wrote:

Hi All, how do you generate a random number between two numbers in
your query using the random() function? Thank's.


select random() % (:high  - :low) + :low;

--
Igor Tandetnik

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

[sqlite] SQLite Random number generator

[Rick Guizawa]

Hi All, how do you generate a random number between two numbers in
your query using the random() function? Thank's.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Elegant printout of table (with header) via TCL

[rod]

2012/2/9 rod :
> My apologies if this question should be directed to some other list.
>
> I'm looking for a better way to printout the:
> Column Headers,
> followed by the table contents -
> in comma separated value (csv) format
> from a SELECT statement:
>

On Fri, Feb 10, 2012 at 3:35 PM, Alexey Pechnikov wrote:

> See pragma table_info
>
>

First  a few typo  corrections, I had written comma separated values(csv) I
 had meant  "tab sep. val. (TSV)" hence the TCL code:
join  $list  "\t"

Alexey suggest  I research table_info.

I think I already have all the info about the table, as produced by the
select statement,  that I need.  This info is contained within row(*): the
column names for the select columns being executed by 'select'.   I don't
see any added benefit of
pragma table_info.


I should have presented a more generic case  of a select statement, one
that contains a subset of the available columns not * (ie all the columns)
Eventually I would like a to be able to add a procedure to the select
script that prints out a tab separated table of   column headers and table
values for a generic select statement.

Something like:

sql eval {Select col_1, col_2, col_8, col_4  from generic_table } rows {
procedure_that_prints_tsv_and_the_column_names $rows
}

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

Re: [sqlite] VS2008 design support error

[Trevor Burns]

Joe

After getting the update on the changes I downloaded the installer from the
website (the publish dates match today).  When I used the installer, the
same error message occurred. So I downloaded the VS2008 SDK 1.1, installed,
and the "add connection" screen is working.

Thanks for your help.

Trevor

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin
Sent: Friday, February 10, 2012 2:47 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] VS2008 design support error


Trevor Burns wrote:
> 
> Package Load Failure
> Package 'System.Data.SQlite Designer Package' has failed to load 
> properly
(
> GUID = {DCBE6C8D-0E57-4099-A183-98FF74C64D9C}). Please contact package 
> vendor for assistance.  Application restart is recommended, due to
possible
> environment corruption.  Would you like to disable loading this 
> package in the future?  You may use 'devenv /resetskippkgs' to 
> re-enable package loading.
> 

The underlying issue (package load key mismatch) should now be fixed, see:

http://system.data.sqlite.org/index.html/ci/67caeebd79?sbs=0

Please note that this issue does *NOT* exist for Visual Studio 2010 (and
later)
as it no longer requires a proper package load key.

--
Joe Mistachkin

___
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

Re: [sqlite] Sql Unions

[David Hubbard]

I will check and get back to you. I have not really studied it.

On Fri, Feb 10, 2012 at 3:52 PM, Richard Hipp  wrote:

> On Fri, Feb 10, 2012 at 5:28 PM, David Hubbard 
> wrote:
>
> > I suspected the odbc layer, but is there any type of logging for SqlLite
> to
> > verify the sql it gets?
> >
>
> No.  We've always assumed that the application developer knows what he is
> sending into SQLite, or else can write his own wrapper to trace what is
> going into SQLite.  We don't have any provisions to debug intervening ODBC
> layers.  Does your ODBC driver has a feature to trace the output it sends
> to SQLite?
>
>
>
> > On Fri, Feb 10, 2012 at 11:37 AM, Richard Hipp  wrote:
> >
> > > On Fri, Feb 10, 2012 at 1:17 PM, David Hubbard 
> > > wrote:
> > >
> > > > We are running this from an access front end and the
> > > > simplest example of a query that generates this error is:
> > > >
> > > > SELECT MDR.MDR_No
> > > > FROM MDR
> > > > UNION
> > > > SELECT MDR_Archive.MDR_No
> > > > FROM MDR_Archive;
> > > >
> > >
> > > The above is perfectly valid syntax.
> > >
> > >
> > > >
> > > > The error is:
> > > > ODBC--call failed.
> > > > near "(": syntax error (1) (#1)
> > > >
> > >
> > > There is no "(" character in your input.  This makes me suspicious that
> > > there is a bug in your application or in your ODBC driver that is
> somehow
> > > sending SQL over to SQLite that is different from what you intend.
> > >
> > >
> > >
> > > >
> > > > and MDR_No is a string field.
> > > >
> > > >
> > > >
> > > >
> > > > On Fri, Feb 10, 2012 at 9:07 AM, nobre <
> > rafael.ro...@novaprolink.com.br
> > > > >wrote:
> > > >
> > > > >
> > > > > What is the query , and what error do you encounter ? SQLite does
> > > support
> > > > > UNION and UNION ALL
> > > > >
> > > > > Regards
> > > > > nobre
> > > > >
> > > > > David Hubbard-4 wrote:
> > > > > >
> > > > > > We are looking at using SqlLite from an access application, but
> we
> > > have
> > > > > > run
> > > > > > into troubles executing a query that has a UNION.
> > > > > > Does SqlLite support Unions? Any help you can provide would be
> > > > > > appreciated.
> > > > > > ___
> > > > > > sqlite-users mailing list
> > > > > > sqlite-users@sqlite.org
> > > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > > > >
> > > > > >
> > > > >
> > > > > --
> > > > > View this message in context:
> > > > > http://old.nabble.com/Sql-Unions-tp33301365p33301398.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
> > > > >
> > > > ___
> > > > sqlite-users mailing list
> > > > sqlite-users@sqlite.org
> > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > >
> > >
> > >
> > >
> > > --
> > > D. Richard Hipp
> > > d...@sqlite.org
> > > ___
> > > 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
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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

Re: [sqlite] Sql Unions

[Richard Hipp]

On Fri, Feb 10, 2012 at 5:28 PM, David Hubbard  wrote:

> I suspected the odbc layer, but is there any type of logging for SqlLite to
> verify the sql it gets?
>

No.  We've always assumed that the application developer knows what he is
sending into SQLite, or else can write his own wrapper to trace what is
going into SQLite.  We don't have any provisions to debug intervening ODBC
layers.  Does your ODBC driver has a feature to trace the output it sends
to SQLite?



> On Fri, Feb 10, 2012 at 11:37 AM, Richard Hipp  wrote:
>
> > On Fri, Feb 10, 2012 at 1:17 PM, David Hubbard 
> > wrote:
> >
> > > We are running this from an access front end and the
> > > simplest example of a query that generates this error is:
> > >
> > > SELECT MDR.MDR_No
> > > FROM MDR
> > > UNION
> > > SELECT MDR_Archive.MDR_No
> > > FROM MDR_Archive;
> > >
> >
> > The above is perfectly valid syntax.
> >
> >
> > >
> > > The error is:
> > > ODBC--call failed.
> > > near "(": syntax error (1) (#1)
> > >
> >
> > There is no "(" character in your input.  This makes me suspicious that
> > there is a bug in your application or in your ODBC driver that is somehow
> > sending SQL over to SQLite that is different from what you intend.
> >
> >
> >
> > >
> > > and MDR_No is a string field.
> > >
> > >
> > >
> > >
> > > On Fri, Feb 10, 2012 at 9:07 AM, nobre <
> rafael.ro...@novaprolink.com.br
> > > >wrote:
> > >
> > > >
> > > > What is the query , and what error do you encounter ? SQLite does
> > support
> > > > UNION and UNION ALL
> > > >
> > > > Regards
> > > > nobre
> > > >
> > > > David Hubbard-4 wrote:
> > > > >
> > > > > We are looking at using SqlLite from an access application, but we
> > have
> > > > > run
> > > > > into troubles executing a query that has a UNION.
> > > > > Does SqlLite support Unions? Any help you can provide would be
> > > > > appreciated.
> > > > > ___
> > > > > sqlite-users mailing list
> > > > > sqlite-users@sqlite.org
> > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > > >
> > > > >
> > > >
> > > > --
> > > > View this message in context:
> > > > http://old.nabble.com/Sql-Unions-tp33301365p33301398.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
> > > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > 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
>



-- 
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] Sql Unions

[David Hubbard]

I suspected the odbc layer, but is there any type of logging for SqlLite to
verify the sql it gets?
On Fri, Feb 10, 2012 at 11:37 AM, Richard Hipp  wrote:

> On Fri, Feb 10, 2012 at 1:17 PM, David Hubbard 
> wrote:
>
> > We are running this from an access front end and the
> > simplest example of a query that generates this error is:
> >
> > SELECT MDR.MDR_No
> > FROM MDR
> > UNION
> > SELECT MDR_Archive.MDR_No
> > FROM MDR_Archive;
> >
>
> The above is perfectly valid syntax.
>
>
> >
> > The error is:
> > ODBC--call failed.
> > near "(": syntax error (1) (#1)
> >
>
> There is no "(" character in your input.  This makes me suspicious that
> there is a bug in your application or in your ODBC driver that is somehow
> sending SQL over to SQLite that is different from what you intend.
>
>
>
> >
> > and MDR_No is a string field.
> >
> >
> >
> >
> > On Fri, Feb 10, 2012 at 9:07 AM, nobre  > >wrote:
> >
> > >
> > > What is the query , and what error do you encounter ? SQLite does
> support
> > > UNION and UNION ALL
> > >
> > > Regards
> > > nobre
> > >
> > > David Hubbard-4 wrote:
> > > >
> > > > We are looking at using SqlLite from an access application, but we
> have
> > > > run
> > > > into troubles executing a query that has a UNION.
> > > > Does SqlLite support Unions? Any help you can provide would be
> > > > appreciated.
> > > > ___
> > > > sqlite-users mailing list
> > > > sqlite-users@sqlite.org
> > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > >
> > > >
> > >
> > > --
> > > View this message in context:
> > > http://old.nabble.com/Sql-Unions-tp33301365p33301398.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
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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

Re: [sqlite] Elegant printout of table (with header) via TCL

[Alexey Pechnikov]

See pragma table_info

2012/2/9 rod :
> My apologies if this question should be directed to some other list.
>
> I'm looking for a better way to printout the:
> Column Headers,
> followed by the table contents -
> in comma separated value (csv) format
> from a SELECT statement:
>
>
> So far I have two solutions, neither seems elegant enough.
>
> First solution I add the rowid tag after the *  then check to see if
> rowid==1 in the output and if so print the headers first
>
> 
>
> # the following code should print the headers and the
> # results from the SELECT command in CSV format
> sql eval {
> SELECT *, rowid \
> FROM Fxyz_max_min limit 10} row {
> # if at first row print headers first
> if $row(rowid)==1 {puts  [join $row(*) "\t"] }
> # define an EMPTY list
> set b_list {}
> foreach col $row(*) {lappend b_list $row($col)}
> set b_list [join $b_list "\t"]
> #write list
> puts   $b_list
> }
>
> 
>
>
> The second solution makes use of a test of count
> (not really a counter just gets set to 1 instead of 0)
>
> 
> set count 0
> sql eval {
> SELECT * \
> FROM Fxyz_max_min limit 10} row {
> # define an EMPTY list
> if $count==0 {puts  [join $row(*) "\t"] }
> set count 1
> set b_list {}
> foreach col $row(*) {lappend b_list $row($col)}
> set b_list [join $b_list "\t"]
> #write list
> puts   $b_list
> }
>
> 
>
>
>
>
> Each of these needs something added; either the rowid to the output or
> a counter test.
> is there a better way??
>
>
> Thanks
>
> P.S.   can the TCL sqlite3 statement open the database using command switchs
> (ie    --cvs  -headers)
> --
> -Rod
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow inserts in indexed table

[Alexey Pechnikov]

Modification of big index-tree is disk-expensive operation.
You can try to insert pre-sorted data. Did yoy search only
by equals conditions? Did you think about packing a set
of values in single string or blob like to
Insert into t1 values ('1 2 3 4 5 6 7 8 9');
You can search by string content using FTS3/4 index.


2012/2/9 Valentin Davydov :
> Hi, All!
>
> I've got a database containing single table with a dozen of columns and some
> indices like that:
>
> CREATE TABLE T(X, Y, Z, ..., UNIQUE (X, Y, Z, ...) ON CONFLICT IGNORE);
> CREATE INDEX IX ON T(X);
> CREATE INDEX IY ON T(Y);
> CREATE INDEX IZ ON T(Z);
> .
>
> Data in the different columns are small positive integers, 32-bit integers
> or few-bytes blobs. Neither of the X, Y, Z, ... are unique by themselves,
> only their combination is unique (that's why I opt using relational database
> to process them). My application treats this table as append-only, that is,
> doing either INSERT or SELECT on it, without any UPDATEs. Few millions of
> inserts are wrapped in a single transaction in order to reduce journal usage.
> Total number of records in the table is more than 10^10, so it doesn't fit
> in any RAM. PRAGMA CACHE_SIZE is adjusted to use most of the available memory.
> Journal file (of enough size to hold all the pages dirtied by a biggest
> transaction) is created in advance and PRAGMA JOURNAL_MODE is set to PERSIST
> in order not to bother operating system with creating/deleting files. Page
> size is matched to the underlying filesystem block size and to the stripe
> size of RAID containing that filesystem. Sqlite version is now 3.7.3, but
> it seems that exact version doesn't matter.
>
> When trying to insert data already present in the table, performance is
> fairly well, most of the CPU time is spent only on parsing SQL statements
> and converting data to internal format, while database operation itself
> (that is checking data against the constraint) is almost instantaneous,
> which is quite impressive given the table size. But when the application
> inserts new data, things change drastically: total throughput drops by a
> 2-3 orders of magnitude. CPU is staying almost idle, and all time is spent
> waiting for disk _reading_ (rarely interspersed with fast and happy write
> bursts on each COMMIT). What is sqlite reading there? Does it try to
> perfectly balance each index on each insert (million times per
> transaction) or something else?
>
> Sincerely,
> Valentin Davydov.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VS2008 design support error

[Joe Mistachkin]

Trevor Burns wrote:
> 
> Package Load Failure
> Package 'System.Data.SQlite Designer Package' has failed to load properly
(
> GUID = {DCBE6C8D-0E57-4099-A183-98FF74C64D9C}). Please contact package
> vendor for assistance.  Application restart is recommended, due to
possible
> environment corruption.  Would you like to disable loading this package in
> the future?  You may use 'devenv /resetskippkgs' to re-enable package
> loading.
> 

The underlying issue (package load key mismatch) should now be fixed, see:

http://system.data.sqlite.org/index.html/ci/67caeebd79?sbs=0

Please note that this issue does *NOT* exist for Visual Studio 2010 (and
later)
as it no longer requires a proper package load key.

--
Joe Mistachkin

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

Re: [sqlite] VS2008 design support error

[Joe Mistachkin]

Trevor Burns wrote:
> 
> Package Load Failure
> Package 'System.Data.SQlite Designer Package' has failed to load properly
(
> GUID = {DCBE6C8D-0E57-4099-A183-98FF74C64D9C}). Please contact package
> vendor for assistance.  Application restart is recommended, due to
possible
> environment corruption.  Would you like to disable loading this package in
> the future?  You may use 'devenv /resetskippkgs' to re-enable package
> loading.
> 

If this is indeed a package load key failure, one potential workaround would
be to install the Visual Studio 2008 SDK, which would then allow all
packages
to load.  Meanwhile, I'll look into getting a new package load key assigned
for
the package.

Please note that this issue does *NOT* exist for Visual Studio 2010 as it no
longer requires a proper package load key.

--
Joe Mistachkin

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

Re: [sqlite] Database locked in multi process scenario

[Igor Tandetnik]

On 2/10/2012 2:57 PM, Marc L. Allen wrote:

MSSQL in its default serialization mode does not guarantee repeatable
reads within a transaction.  But, it provides locking hints to help
enforce it when required.  I'm guessing that sqlite does guarantee
repeatable reads?


SQLite implements only one transaction isolation level - serializable 
(except in shared cache mode). In regular journal mode, this is achieved 
by holding locks. In WAL mode, this is effectively achieved via snapshot 
isolation (http://en.wikipedia.org/wiki/Snapshot_isolation)

--
Igor Tandetnik

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

Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5

[Tim Leland]

I still wasn't able to get this to work. It doesn't do anything.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Thursday, February 09, 2012 3:04 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5

Let's change them a touch...one too many @'s in loop.bat

loop.bat

@for %%i in (%1) do @call dump %%i

dump.bat

@echo .separator "," >grocery.sql
@echo .output %~n1.csv >>grocery.sql
@echo select * from grocery; >>grocery.sql
@echo .quit >>grocery.sql
@sqlite3 %1 grocery.sql
echo .output %~n1.csv >>grocery.sql
echo select * from grocery; >>grocery.sql echo .quit >>grocery.sql
sqlite3 %1 mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Thursday, February 09, 2012 1:39 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5


On 9 Feb 2012, at 6:28pm, Tim Leland wrote:

> That will work but we have to import the sqlite file into our
AS400/Iseries
> IBM (DB2)system. Easiest way is convert it to a .csv file for the import.
My
> last resort is just run a script on the PC side that will run sqlite3.exe
> shell and convert the sqlite file to a .csv but I want to avoid that step.
> If you know of a way for an as400 to import sqlite files that would be
> great!

Oh, you're that guy.  Okay, I'd forgotten your hosting side was on an AS400.

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

Re: [sqlite] Database locked in multi process scenario

[Marc L. Allen]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Friday, February 10, 2012 2:36 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Database locked in multi process scenario
> 
> On 2/10/2012 12:29 PM, Sreekumar TP wrote:
> > Can this situation be handled in sqlite -  by upgrading the lock to a
> > writer lock  ? Since both applications use the same WAL file for read
> > and writes, it shouldnt be a problem , because all changes will be in
> > linear sequence ?
> 
> Consider again:
> 
> [1] BEGIN;
> [1] SELECT balance from Accounts;  (1)
> 
> [2]   BEGIN;
> [2]   SELECT balance from Accounts;  (2)
> [2]   UPDATE Accounts SET balance = balance + 100;
> [2]   SELECT balance from Accounts;  (3)
> [2]   COMMIT;
> 
> [1] UPDATE Accounts SET balance = balance + 100; [1] SELECT balance
> from Accounts;  (4) [1] COMMIT;
> 
> [3] SELECT balance from Accounts;  (5)
> 
> Statements are shown in the order they are submitted to SQLite. Numbers
> in square brackets indicate individual connections.
> 
> Let's suppose SQLite does everything the way you want (it's not quite
> clear what it is you want exactly, so that's what I'm trying to
> establish). I assume you expect this sequence of statements to succeed.
> In this ideal world, what value of Accounts.balance should be observed
> at points (1), (2), (3), (4) and (5), in your opinion?

Well.. in MSSQL that would work the way you think he expects.  That is, if the 
initial balance is $100

(1) 100
(2) 100
(3) 200
(4) 300
(5) 300

MSSQL in its default serialization mode does not guarantee repeatable reads 
within a transaction.  But, it provides locking hints to help enforce it when 
required.  I'm guessing that sqlite does guarantee repeatable reads?

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

Re: [sqlite] Database locked in multi process scenario

[Igor Tandetnik]

On 2/10/2012 12:29 PM, Sreekumar TP wrote:

Can this situation be handled in sqlite -  by upgrading the lock to a
writer lock  ? Since both applications use the same WAL file for read and
writes, it shouldnt be a problem , because all changes will be in linear
sequence ?


Consider again:

[1] BEGIN;
[1] SELECT balance from Accounts;  (1)

[2]   BEGIN;
[2]   SELECT balance from Accounts;  (2)
[2]   UPDATE Accounts SET balance = balance + 100;
[2]   SELECT balance from Accounts;  (3)
[2]   COMMIT;

[1] UPDATE Accounts SET balance = balance + 100;
[1] SELECT balance from Accounts;  (4)
[1] COMMIT;

[3] SELECT balance from Accounts;  (5)

Statements are shown in the order they are submitted to SQLite. Numbers 
in square brackets indicate individual connections.


Let's suppose SQLite does everything the way you want (it's not quite 
clear what it is you want exactly, so that's what I'm trying to 
establish). I assume you expect this sequence of statements to succeed. 
In this ideal world, what value of Accounts.balance should be observed 
at points (1), (2), (3), (4) and (5), in your opinion?

--
Igor Tandetnik

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

Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5

[Tim Leland]

Would this be able to run on windows mobile or windows ce?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Andrew Barnes
Sent: Friday, February 10, 2012 1:46 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5


Hi,

Not sure if anyone has suggested this already but rather than messing with
the shell and bat files, cant you write a little program which reads the
database, outputs a CSV file and puts it wherever it needs putting

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

Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5

[Andrew Barnes]

Hi,

Not sure if anyone has suggested this already but rather than messing with the 
shell and bat files, cant you write a little program which reads the database, 
outputs a CSV file and puts it wherever it needs putting

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

Re: [sqlite] Sql Unions

[Richard Hipp]

On Fri, Feb 10, 2012 at 1:17 PM, David Hubbard  wrote:

> We are running this from an access front end and the
> simplest example of a query that generates this error is:
>
> SELECT MDR.MDR_No
> FROM MDR
> UNION
> SELECT MDR_Archive.MDR_No
> FROM MDR_Archive;
>

The above is perfectly valid syntax.


>
> The error is:
> ODBC--call failed.
> near "(": syntax error (1) (#1)
>

There is no "(" character in your input.  This makes me suspicious that
there is a bug in your application or in your ODBC driver that is somehow
sending SQL over to SQLite that is different from what you intend.



>
> and MDR_No is a string field.
>
>
>
>
> On Fri, Feb 10, 2012 at 9:07 AM, nobre  >wrote:
>
> >
> > What is the query , and what error do you encounter ? SQLite does support
> > UNION and UNION ALL
> >
> > Regards
> > nobre
> >
> > David Hubbard-4 wrote:
> > >
> > > We are looking at using SqlLite from an access application, but we have
> > > run
> > > into troubles executing a query that has a UNION.
> > > Does SqlLite support Unions? Any help you can provide would be
> > > appreciated.
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > >
> >
> > --
> > View this message in context:
> > http://old.nabble.com/Sql-Unions-tp33301365p33301398.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
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] Sql Unions

[David Hubbard]

We are running this from an access front end and the
simplest example of a query that generates this error is:

SELECT MDR.MDR_No
FROM MDR
UNION
SELECT MDR_Archive.MDR_No
FROM MDR_Archive;

The error is:
ODBC--call failed.
near "(": syntax error (1) (#1)

and MDR_No is a string field.




On Fri, Feb 10, 2012 at 9:07 AM, nobre wrote:

>
> What is the query , and what error do you encounter ? SQLite does support
> UNION and UNION ALL
>
> Regards
> nobre
>
> David Hubbard-4 wrote:
> >
> > We are looking at using SqlLite from an access application, but we have
> > run
> > into troubles executing a query that has a UNION.
> > Does SqlLite support Unions? Any help you can provide would be
> > appreciated.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
>
> --
> View this message in context:
> http://old.nabble.com/Sql-Unions-tp33301365p33301398.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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Simon Slavin]

On 10 Feb 2012, at 5:55pm, Kit wrote:

> 2012/2/10 Simon Slavin :
>> On 10 Feb 2012, at 5:32pm, Kit wrote:
>>> A situation in which I read from the database first and then changes
>>> the data tells me that they are wrong questions. It is such a problem
>>> to insert SELECT into UPDATE or INSERT?
>> 
>> Why do you need to do a SELECT at all ?  Can you present us with a simple 
>> example of your situation ?
>> Simon.
> 
> Add value from Alpha to Bravo:
> 
> Wrong:
> SELECT val AS val1 FROM t1 WHERE name='Alpha';
> UPDATE t1 SET val=val+val1 WHERE name='Bravo';
> 
> Good:
> UPDATE t1 SET val=val+(SELECT val FROM t1 WHERE name='Alpha') WHERE
> name='Bravo';
> 
> No explicit transaction, no problem.

Ah, you were suggesting the solution, not asking the question.  I 
misunderstood.  Sorry.

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

Re: [sqlite] Database locked in multi process scenario

[Kit]

2012/2/10 Simon Slavin :
> On 10 Feb 2012, at 5:32pm, Kit wrote:
>> A situation in which I read from the database first and then changes
>> the data tells me that they are wrong questions. It is such a problem
>> to insert SELECT into UPDATE or INSERT?
>
> Why do you need to do a SELECT at all ?  Can you present us with a simple 
> example of your situation ?
> Simon.

Add value from Alpha to Bravo:

Wrong:
SELECT val AS val1 FROM t1 WHERE name='Alpha';
UPDATE t1 SET val=val+val1 WHERE name='Bravo';

Good:
UPDATE t1 SET val=val+(SELECT val FROM t1 WHERE name='Alpha') WHERE
name='Bravo';

No explicit transaction, no problem.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Interpolation

[Steinar Midtskogen]

[Simon Slavin]

> On 10 Feb 2012, at 3:24pm, Steinar Midtskogen wrote:
>
>> I feared that.  As it is, it takes 6 seconds to do a SELECT * FROM
>> Combined LIMIT 1 ("Combined" is a view representing the merged table).
>> If I add an ORDER BY, it takes 35 seconds.
>> 
>> Any way to speed up the ordering?
>
> Are you putting the ORDER BY in the VIEW definition or the SELECT definition 
> ?  Whichever you're doing, try the other one.  Also, is there an index which 
> provides a sorted list in an order which suits your ORDER BY clause ?

I created the view this way:

CREATE VIEW Combined AS
 SELECT strftime("%Y-%m-%d %H:%M:%S", unix_time, "unixepoch") AS time, * FROM
 (SELECT unix_time FROM Voksenlia1 UNION
  SELECT unix_time FROM Voksenlia2 UNION
  SELECT unix_time FROM Voksenlia3 UNION
  SELECT unix_time FROM Voksenlia4 UNION
  SELECT unix_time FROM Voksenlia5 UNION
  SELECT unix_time FROM Voksenlia6 UNION
  SELECT unix_time FROM Voksenlia8
)
LEFT NATURAL JOIN Voksenlia1
LEFT NATURAL JOIN Voksenlia2
LEFT NATURAL JOIN Voksenlia3
LEFT NATURAL JOIN Voksenlia4
LEFT NATURAL JOIN Voksenlia5
LEFT NATURAL JOIN Voksenlia6
LEFT NATURAL JOIN Voksenlia8 ORDER BY unix_time;

All 7 tables have a PRIMARY KEY (unix_time)

I tried then this:

CREATE VIEW Combined AS
 SELECT strftime("%Y-%m-%d %H:%M:%S", unix_time, "unixepoch") AS time, * FROM
 (SELECT unix_time FROM Voksenlia1 UNION
  SELECT unix_time FROM Voksenlia2 UNION
  SELECT unix_time FROM Voksenlia3 UNION
  SELECT unix_time FROM Voksenlia4 UNION
  SELECT unix_time FROM Voksenlia5 UNION
  SELECT unix_time FROM Voksenlia6 UNION
  SELECT unix_time FROM Voksenlia8 ORDER BY unix_time
)
LEFT NATURAL JOIN Voksenlia1
LEFT NATURAL JOIN Voksenlia2
LEFT NATURAL JOIN Voksenlia3
LEFT NATURAL JOIN Voksenlia4
LEFT NATURAL JOIN Voksenlia5
LEFT NATURAL JOIN Voksenlia6
LEFT NATURAL JOIN Voksenlia8;

And I got a big speedup.  Is this what you meant?

The combined view currently has 84 columns and 2,548,717 rows, so
doing things the wrong way makes a huge impact.
-- 
Steinar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Simon Slavin]

On 10 Feb 2012, at 5:32pm, Kit wrote:

> 2012/2/10 Sreekumar TP :
>>  Though the example of $ is very intuitive, I am not suggesting that we
>> drop one of the transaction and block the database forever (as it is
>> happening now). Instead, it could be serialized such that two $100
>> transactions are committed to the db.
> 
> A situation in which I read from the database first and then changes
> the data tells me that they are wrong questions. It is such a problem
> to insert SELECT into UPDATE or INSERT?

Why do you need to do a SELECT at all ?  Can you present us with a simple 
example of your situation ?

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

Re: [sqlite] Database locked in multi process scenario

[Richard Hipp]

On Fri, Feb 10, 2012 at 11:45 AM, Sreekumar TP wrote:

> There is no recovery from this situation-
>

The recovery from your situation is to reset or finalize the initial query
that is holding the transaction option.


>
> If you try to rollback, you get the following error -"cannot rollback
> savepoint, SQL statments in progress"  or if you dont use SAVEPOINT -
> "cannot rollback, no transaction is active "
> If you start the transaction with BEGIN IMMEDIATE in App1, the writer in
> App2 gets the following error " database is locked"
>
> Sreekumar
>
> On Fri, Feb 10, 2012 at 8:13 PM, Igor Tandetnik 
> wrote:
>
> > Marc L. Allen  wrote:
> > > I see.  So, the implied commit doesn't occur until you finalize?
> >
> > Or reset.
> >
> > > As a result, the subsequent update in step 5 was added to his
> > > non-finalized select?
> >
> > The update was attempted within the same transaction.
> >
> > > Still.. what is the correct way to handle the explicit scenario?  I
> > mean, having one process do a BEGIN SELECT UPDATE and another
> > > do BEGIN UPDATE is perfectly reasonable, isn't it?  How do you protect
> > from a problem?  Detect the error, rollback, and try
> > > again?
> >
> > That's one way. The other is for the first connection to start its
> > transaction with BEGIN IMMEDIATE, thus marking itself as a writer from
> the
> > start.
> > --
> > Igor Tandetnik
> >
> > ___
> > 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
>



-- 
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] Database locked in multi process scenario

[Simon Slavin]

On 10 Feb 2012, at 5:29pm, Sreekumar TP wrote:

> Can this situation be handled in sqlite -  by upgrading the lock to a
> writer lock  ? Since both applications use the same WAL file for read and
> writes, it shouldnt be a problem , because all changes will be in linear
> sequence ?

SQLite handles all of this perfectly.  We don't understand why you have a 
problem.  Why are you starting a SELECT command, then waiting to finalize it 
for a long time ?

Just do your SELECT and complete it.  When you're ready to make the changes in 
your your transaction, do that.  There is no need for the SELECT and the 
changes to be part of the same transaction.

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

Re: [sqlite] Database locked in multi process scenario

[Kit]

2012/2/10 Sreekumar TP :
>  Though the example of $ is very intuitive, I am not suggesting that we
> drop one of the transaction and block the database forever (as it is
> happening now). Instead, it could be serialized such that two $100
> transactions are committed to the db.

A situation in which I read from the database first and then changes
the data tells me that they are wrong questions. It is such a problem
to insert SELECT into UPDATE or INSERT?
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Sreekumar TP]

Can this situation be handled in sqlite -  by upgrading the lock to a
writer lock  ? Since both applications use the same WAL file for read and
writes, it shouldnt be a problem , because all changes will be in linear
sequence ?

Sreekumar


On Fri, Feb 10, 2012 at 10:49 PM, Sreekumar TP wrote:

>  Though the example of $ is very intuitive, I am not suggesting that we
> drop one of the transaction and block the database forever (as it is
> happening now). Instead, it could be serialized such that two $100
> transactions are committed to the db.
>
>
>
> On Fri, Feb 10, 2012 at 10:33 PM, Igor Tandetnik wrote:
>
>> On 2/10/2012 9:57 AM, Sreekumar TP wrote:
>>
>>> The last transaction should always be the final one. In a a
>>> multiprocess/threaded application how can one make assumptions on the
>>> order
>>> of updates?
>>>
>>
>> There are two updates in my example:
>>
>>
>> update t set count = count + 1;
>> update t set count = count + 10;
>>
>> Do you feel it unreasonable to assume that, after these two statements
>> are executed successfully, in any order, the value of count should increase
>> by 11?
>>
>> If two $100 deposits to your bank account are made by different parties
>> at approximately the same time, I think you'd be pretty upset if the
>> account balance didn't increase by precisely $200.
>>
>> --
>> Igor Tandetnik
>>
>> __**_
>> 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

Re: [sqlite] Database locked in multi process scenario

[Simon Slavin]

> On 2/10/2012 9:57 AM, Sreekumar TP wrote:
> 
>> The last transaction should always be the final one. In a a
>> multiprocess/threaded application how can one make assumptions on the
>> order
>> of updates?

SQL does not have any concept of 'last transaction' or 'final transaction' or 
'order of transactions'.  Either a transaction is done or it isn't.  There is 
no order for changes to a database done within a transaction.  Either they're 
all done, or none are done.  There is no idea that one change is made first, 
then another, then another.

Similarly, if you specify an order in your SELECT command, that's the order 
you'll get the rows in.  If you don't, the rows may appear in a random order.  
If you don't specify an ORDER BY clause or perhaps even if you do and your 
ORDER BY is ambiguous, they could easily appear in a different order every time 
you do a SELECT.

On 10 Feb 2012, at 5:19pm, Sreekumar TP wrote:

> Though the example of $ is very intuitive, I am not suggesting that we
> drop one of the transaction and block the database forever (as it is
> happening now). Instead, it could be serialized such that two $100
> transactions are committed to the db.

You BEGIN one transaction.  In that transaction you make the two changes which 
balance each other out.  You COMMIT the transaction and both changes are made.  
Or you ROLLBACK the transaction and nether change is made.  That's how 
transactions work.

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

Re: [sqlite] Database locked in multi process scenario

[Sreekumar TP]

 Though the example of $ is very intuitive, I am not suggesting that we
drop one of the transaction and block the database forever (as it is
happening now). Instead, it could be serialized such that two $100
transactions are committed to the db.


On Fri, Feb 10, 2012 at 10:33 PM, Igor Tandetnik wrote:

> On 2/10/2012 9:57 AM, Sreekumar TP wrote:
>
>> The last transaction should always be the final one. In a a
>> multiprocess/threaded application how can one make assumptions on the
>> order
>> of updates?
>>
>
> There are two updates in my example:
>
>
> update t set count = count + 1;
> update t set count = count + 10;
>
> Do you feel it unreasonable to assume that, after these two statements are
> executed successfully, in any order, the value of count should increase by
> 11?
>
> If two $100 deposits to your bank account are made by different parties at
> approximately the same time, I think you'd be pretty upset if the account
> balance didn't increase by precisely $200.
>
> --
> Igor Tandetnik
>
> __**_
> 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

Re: [sqlite] Database locked in multi process scenario

[Igor Tandetnik]

On 2/10/2012 11:45 AM, Sreekumar TP wrote:

There is no recovery from this situation-

If you try to rollback, you get the following error -"cannot rollback
savepoint, SQL statments in progress"  or if you dont use SAVEPOINT -
"cannot rollback, no transaction is active"
If you start the transaction with BEGIN IMMEDIATE in App1, the writer in
App2 gets the following error " database is locked"


Just reset the statement that keeps the transaction open.

If you have more than one write and you want to be able to roll back 
reliably if any of them fails, you should start an explicit transaction.

--
Igor Tandetnik

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

Re: [sqlite] Database locked in multi process scenario

[Igor Tandetnik]

On 2/10/2012 9:57 AM, Sreekumar TP wrote:

The last transaction should always be the final one. In a a
multiprocess/threaded application how can one make assumptions on the order
of updates?


There are two updates in my example:

update t set count = count + 1;
update t set count = count + 10;

Do you feel it unreasonable to assume that, after these two statements 
are executed successfully, in any order, the value of count should 
increase by 11?


If two $100 deposits to your bank account are made by different parties 
at approximately the same time, I think you'd be pretty upset if the 
account balance didn't increase by precisely $200.

--
Igor Tandetnik

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

Re: [sqlite] Database locked in multi process scenario

[Simon Slavin]

On 10 Feb 2012, at 4:45pm, Sreekumar TP wrote:

> There is no recovery from this situation-
> 
> If you try to rollback, you get the following error -"cannot rollback
> savepoint, SQL statments in progress"  or if you dont use SAVEPOINT -
> "cannot rollback, no transaction is active "
> If you start the transaction with BEGIN IMMEDIATE in App1, the writer in
> App2 gets the following error " database is locked"

Correct.  The database really is locked, since the structure of your 
application tells SQLite that it is going to make a change soon.  If you're not 
ready to make the change yet, don't lock the database yet.

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

Re: [sqlite] Database locked in multi process scenario

[Sreekumar TP]

There is no recovery from this situation-

If you try to rollback, you get the following error -"cannot rollback
savepoint, SQL statments in progress"  or if you dont use SAVEPOINT -
"cannot rollback, no transaction is active "
If you start the transaction with BEGIN IMMEDIATE in App1, the writer in
App2 gets the following error " database is locked"

Sreekumar

On Fri, Feb 10, 2012 at 8:13 PM, Igor Tandetnik  wrote:

> Marc L. Allen  wrote:
> > I see.  So, the implied commit doesn't occur until you finalize?
>
> Or reset.
>
> > As a result, the subsequent update in step 5 was added to his
> > non-finalized select?
>
> The update was attempted within the same transaction.
>
> > Still.. what is the correct way to handle the explicit scenario?  I
> mean, having one process do a BEGIN SELECT UPDATE and another
> > do BEGIN UPDATE is perfectly reasonable, isn't it?  How do you protect
> from a problem?  Detect the error, rollback, and try
> > again?
>
> That's one way. The other is for the first connection to start its
> transaction with BEGIN IMMEDIATE, thus marking itself as a writer from the
> start.
> --
> Igor Tandetnik
>
> ___
> 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

Re: [sqlite] Interpolation

[Simon Slavin]

On 10 Feb 2012, at 3:24pm, Steinar Midtskogen wrote:

> I feared that.  As it is, it takes 6 seconds to do a SELECT * FROM
> Combined LIMIT 1 ("Combined" is a view representing the merged table).
> If I add an ORDER BY, it takes 35 seconds.
> 
> Any way to speed up the ordering?

Are you putting the ORDER BY in the VIEW definition or the SELECT definition ?  
Whichever you're doing, try the other one.  Also, is there an index which 
provides a sorted list in an order which suits your ORDER BY clause ?

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

Re: [sqlite] Database locked in multi process scenario

[Simon Slavin]

On 10 Feb 2012, at 3:01pm, Marc L. Allen wrote:

> From my background, I'm used to SQL statements blocking until appropriate 
> locks are acquired.  From what I've seen, it looks like sqlite doesn't block, 
> but returns BUSY, is that correct?

You can set a timeout.  SQLite tries and retries until the timeout expires, 
then it returns _BUSY.



You can set the timeout to a few milliseconds or a few hours.  Both will work.

> If two processes start a BEGIN IMMEDIATE, will one return a BUSY or will it 
> block and wait?

Dependent on whether the database becomes free before the amount of time you 
set in the timeout.

> If it returns BUSY, how does sqlite3_exec() handle it?

It is the sqlite3_exec() routine which will itself return _BUSY.  It's up to 
your application to handle that well.

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

Re: [sqlite] Sql Unions

[nobre]

What is the query , and what error do you encounter ? SQLite does support
UNION and UNION ALL

Regards
nobre

David Hubbard-4 wrote:
> 
> We are looking at using SqlLite from an access application, but we have
> run
> into troubles executing a query that has a UNION.
> Does SqlLite support Unions? Any help you can provide would be
> appreciated.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Sql-Unions-tp33301365p33301398.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] multiple rows of VALUES in an INSERT incompatible with SQLITE_OMIT_COMPOUND_SELECT

[Ralf Junker]

On 07.02.2012 12:28, Ralf Junker wrote:

> The new feature to insert multiple rows of VALUES in a single INSERT
> 
>   http://www.sqlite.org/src/info/eb3b6a0ceb
> 
> gives wrong results if SQLite is compiled with SQLITE_OMIT_COMPOUND_SELECT.

Has the team seen this or has it been overlooked? Shall I file a ticket?

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

[sqlite] Sql Unions

[David Hubbard]

We are looking at using SqlLite from an access application, but we have run
into troubles executing a query that has a UNION.
Does SqlLite support Unions? Any help you can provide would be appreciated.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] VS2008 design support error

[Trevor Burns]

I have been trying to get the system.data.sqlite version 1.0.79 working with
Visual Studio 2008 Pro (with SP).  Each time I try to use Add Connection to
create the database connection string, the system.sqlite.data, I VS2008 is
popping up an error dialog:

Package Load Failure
Package 'System.Data.SQlite Designer Package' has failed to load properly (
GUID = {DCBE6C8D-0E57-4099-A183-98FF74C64D9C}). Please contact package
vendor for assistance.  Application restart is recommended, due to possible
environment corruption.  Would you like to disable loading this package in
the future?  You may use 'devenv /resetskippkgs' to re-enable package
loading.

I did find the exact same error in the mailing list archive
[http://www.mail-archive.com/sqlite-users@sqlite.org/msg67358.html] , but
there was no resolution attached just a request for logging.  To add to it,
I am getting this error message on my system referenced below and a Virtual
Machine with a fresh VS2008SP install on Windows 2008.

System Details:
Windows 7 x64 SP1
Visual Studio 2008 Professional (Version: 9.0.30729.1 SP)
Installer: sqlite-netFx35-setup-x86-2008-1.0.79.0.exe
Options used from installer: Full installation with Generate native images
and Install the designer components for Visual Studio 2008 checked

VS2008 Log
# Type Description GUID Hr Source Time
121 Warning CheckPackageSignature failed; invalid Package Load Key
{DCBE6C8D-0E57-4099-A183-98FF74C64D9C}  Microsoft Visual Studio Appid Stub
2012/02/10 15:20:24.424 
122 Warning CheckPackageSignature failed; invalid Package Load Key
{DCBE6C8D-0E57-4099-A183-98FF74C64D9C}  Microsoft Visual Studio Appid Stub
2012/02/10 15:20:24.463 
123 Warning CheckPackageSignature failed; invalid Package Load Key
{DCBE6C8D-0E57-4099-A183-98FF74C64D9C}  Microsoft Visual Studio Appid Stub
2012/02/10 15:20:24.465 
124 Warning CheckPackageSignature failed; invalid Package Load Key
{DCBE6C8D-0E57-4099-A183-98FF74C64D9C}  Microsoft Visual Studio Appid Stub
2012/02/10 15:20:24.470 
125 Warning CheckPackageSignature failed; invalid Package Load Key
{DCBE6C8D-0E57-4099-A183-98FF74C64D9C}  Microsoft Visual Studio Appid Stub
2012/02/10 15:20:24.478 
126 Warning Appid denied the loading of package
{DCBE6C8D-0E57-4099-A183-98FF74C64D9C}  Microsoft Visual Studio 2012/02/10
15:20:24.478 
127  Entering function CVsPackageInfo::HrInstantiatePackage
{DCBE6C8D-0E57-4099-A183-98FF74C64D9C}  Microsoft Visual Studio 2012/02/10
15:20:26.519 
128 Warning CheckPackageSignature failed; invalid Package Load Key
{DCBE6C8D-0E57-4099-A183-98FF74C64D9C}  Microsoft Visual Studio Appid Stub
2012/02/10 15:20:26.521 
129 Warning CheckPackageSignature failed; invalid Package Load Key
{DCBE6C8D-0E57-4099-A183-98FF74C64D9C}  Microsoft Visual Studio Appid Stub
2012/02/10 15:20:26.523 
130 Warning CheckPackageSignature failed; invalid Package Load Key
{DCBE6C8D-0E57-4099-A183-98FF74C64D9C}  Microsoft Visual Studio Appid Stub
2012/02/10 15:20:26.524 
131 Warning CheckPackageSignature failed; invalid Package Load Key
{DCBE6C8D-0E57-4099-A183-98FF74C64D9C}  Microsoft Visual Studio Appid Stub
2012/02/10 15:20:26.538 
132 Warning CheckPackageSignature failed; invalid Package Load Key
{DCBE6C8D-0E57-4099-A183-98FF74C64D9C}  Microsoft Visual Studio Appid Stub
2012/02/10 15:20:26.539 
133 Warning Appid denied the loading of package
{DCBE6C8D-0E57-4099-A183-98FF74C64D9C}  Microsoft Visual Studio 2012/02/10
15:20:26.540

Trevor Burns


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

Re: [sqlite] Interpolation

[Steinar Midtskogen]

[Igor Tandetnik]

> If you need a particular order, it's best to add an explicit ORDER BY. 
> Otherwise, you are at the mercy of an implementation. Your current version of 
> SQLite chooses an execution plan that happens, by accident, to produce rows 
> in the desired order. Tomorrow you upgrade to a new version, and it chooses a 
> different execution plan that results in a different order.

I feared that.  As it is, it takes 6 seconds to do a SELECT * FROM
Combined LIMIT 1 ("Combined" is a view representing the merged table).
If I add an ORDER BY, it takes 35 seconds.

Any way to speed up the ordering?

I think I'll need the ordering to do interpolation.

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

Re: [sqlite] Database locked in multi process scenario

[Sreekumar TP]

I took traces of the lock/unlock pattern -


After App1 SELECT
--
fcntl -1212610880 7 SETLK WRLCK 124 1 0 0
WAL806F9D8: acquire EXCLUSIVE-READ-LOCK[1] cnt=1 ok
fcntl -1212610880 7 SETLK UNLCK 124 1 0 0
WAL806F9D8: release EXCLUSIVE-READ-LOCK[1] cnt=1
fcntl -1212610880 7 SETLK RDLCK 124 1 0 0
WAL806F9D8: acquire SHARED-READ-LOCK[1] ok

After App2 UPDATE
-
fcntl -1212344640 7 SETLK RDLCK 124 1 0 0
WAL806F570: acquire SHARED-READ-LOCK[1] ok
fcntl -1212344640 7 SETLK WRLCK 120 1 0 0
WAL806F570: acquire EXCLUSIVE-WRITE-LOCK cnt=1 ok
WAL806F570: frame write ok
fcntl -1212344640 7 SETLK UNLCK 120 1 0 0
WAL806F570: release EXCLUSIVE-WRITE-LOCK cnt=1
fcntl -1212344640 7 SETLK UNLCK 124 1 0 0
WAL806F570: release SHARED-READ-LOCK[1]

After App1 SELECT
--
fcntl -1212610880 7 SETLK WRLCK 120 1 0 0
WAL806F9D8: acquire EXCLUSIVE-WRITE-LOCK cnt=1 ok
fcntl -1212610880 7 SETLK UNLCK 120 1 0 0
WAL806F9D8: release EXCLUSIVE-WRITE-LOCK cnt=1
error @ 225, 1,5,database is locked

After App2 UPDATE ( a second UPDATE )
-
fcntl -1212344640 7 SETLK WRLCK 124 1 0 -1
fcntl-failure-reason: RDLCK 124 1 15295
WAL806F570: acquire EXCLUSIVE-READ-LOCK[1] cnt=1 failed
fcntl -1212344640 7 SETLK WRLCK 125 1 0 0
WAL806F570: acquire EXCLUSIVE-READ-LOCK[2] cnt=1 ok
fcntl -1212344640 7 SETLK UNLCK 125 1 0 0
WAL806F570: release EXCLUSIVE-READ-LOCK[2] cnt=1
fcntl -1212344640 7 SETLK RDLCK 125 1 0 0
WAL806F570: acquire SHARED-READ-LOCK[2] ok
fcntl -1212344640 7 SETLK WRLCK 120 1 0 0
WAL806F570: acquire EXCLUSIVE-WRITE-LOCK cnt=1 ok
fcntl -1212344640 7 SETLK UNLCK 120 1 0 0
WAL806F570: release EXCLUSIVE-WRITE-LOCK cnt=1
fcntl -1212344640 7 SETLK UNLCK 125 1 0 0
WAL806F570: release SHARED-READ-LOCK[2]



Additional Info -

You can also see that acquiring an EXCLUSIVE-READ-LOCK FAILED during the a
second update by App2.
The fcntl error translated means "resource temporarily unavailable"

-
Sreekumar




On Fri, Feb 10, 2012 at 8:31 PM, Marc L. Allen
wrote:

> One last question or series (I hope)...
>
> From my background, I'm used to SQL statements blocking until appropriate
> locks are acquired.  From what I've seen, it looks like sqlite doesn't
> block, but returns BUSY, is that correct?
>
> If two processes start a BEGIN IMMEDIATE, will one return a BUSY or will
> it block and wait?
>
> If it returns BUSY, how does sqlite3_exec() handle it?
>
> (Btw.. I know there are books on sqlite, so if you want to point me to one
> that answers all these questions, or if I'm missing it from the online
> docs, just let me know.  I just haven't found them.)
>
> Thanks
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Marc L. Allen
> > Sent: Friday, February 10, 2012 9:45 AM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Database locked in multi process scenario
> >
> > Thanks so much for clarifying that.  I was unaware of the BEGIN
> > IMMEDIATE.  Sorry.. new to sqlite, used to MySQL and MSSQL.
> >
> > > -Original Message-
> > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > > boun...@sqlite.org] On Behalf Of Igor Tandetnik
> > > Sent: Friday, February 10, 2012 9:43 AM
> > > To: sqlite-users@sqlite.org
> > > Subject: Re: [sqlite] Database locked in multi process scenario
> > >
> > > Marc L. Allen  wrote:
> > > > I see.  So, the implied commit doesn't occur until you finalize?
> > >
> > > Or reset.
> > >
> > > > As a result, the subsequent update in step 5 was added to his
> > > > non-finalized select?
> > >
> > > The update was attempted within the same transaction.
> > >
> > > > Still.. what is the correct way to handle the explicit scenario?  I
> > > > mean, having one process do a BEGIN SELECT UPDATE and another do
> > > BEGIN
> > > > UPDATE is perfectly reasonable, isn't it?  How do you protect from
> > a
> > > problem?  Detect the error, rollback, and try again?
> > >
> > > That's one way. The other is for the first connection to start its
> > > transaction with BEGIN IMMEDIATE, thus marking itself as a writer
> > from
> > > the start.
> > > --
> > > Igor Tandetnik
> > >
> > > ___
> > > 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

Re: [sqlite] Database locked in multi process scenario

[Marc L. Allen]

One last question or series (I hope)...

>From my background, I'm used to SQL statements blocking until appropriate 
>locks are acquired.  From what I've seen, it looks like sqlite doesn't block, 
>but returns BUSY, is that correct?

If two processes start a BEGIN IMMEDIATE, will one return a BUSY or will it 
block and wait?

If it returns BUSY, how does sqlite3_exec() handle it?

(Btw.. I know there are books on sqlite, so if you want to point me to one that 
answers all these questions, or if I'm missing it from the online docs, just 
let me know.  I just haven't found them.)

Thanks

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Marc L. Allen
> Sent: Friday, February 10, 2012 9:45 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Database locked in multi process scenario
> 
> Thanks so much for clarifying that.  I was unaware of the BEGIN
> IMMEDIATE.  Sorry.. new to sqlite, used to MySQL and MSSQL.
> 
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Igor Tandetnik
> > Sent: Friday, February 10, 2012 9:43 AM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] Database locked in multi process scenario
> >
> > Marc L. Allen  wrote:
> > > I see.  So, the implied commit doesn't occur until you finalize?
> >
> > Or reset.
> >
> > > As a result, the subsequent update in step 5 was added to his
> > > non-finalized select?
> >
> > The update was attempted within the same transaction.
> >
> > > Still.. what is the correct way to handle the explicit scenario?  I
> > > mean, having one process do a BEGIN SELECT UPDATE and another do
> > BEGIN
> > > UPDATE is perfectly reasonable, isn't it?  How do you protect from
> a
> > problem?  Detect the error, rollback, and try again?
> >
> > That's one way. The other is for the first connection to start its
> > transaction with BEGIN IMMEDIATE, thus marking itself as a writer
> from
> > the start.
> > --
> > Igor Tandetnik
> >
> > ___
> > 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

Re: [sqlite] Database locked in multi process scenario

[Sreekumar TP]

The last transaction should always be the final one. In a a
multiprocess/threaded application how can one make assumptions on the order
of updates?


Sreekumar


On Fri, Feb 10, 2012 at 8:16 PM, Igor Tandetnik  wrote:

> Sreekumar TP  wrote:
> > How is this different from two threads each with a db connection in a
> > single process?
>
> If each thread uses its own separate connection, it should be no different
> - you would observe the same issue.
>
> > Moreover the journal mode is WAL. Hence the writer should be able to
> append
> > changes to the WAL file as there are no other write transaction.
>
> Your problem is with a transaction stat starts as a reader, and later
> tries to become a writer. This is only possible if the reader is observing
> the most recent state of the database, that is, if there were no writes
> since it started.
>
> Consider:
>
> // initial setup
> create table t(count integer);
> insert into t values (0);
>
> /* 1 */ select count from t;
> /* 2 */ update t set count = count + 10;
> /* 1 */ update t set count = count + 1;  // (!)
> /* 1 */ select count from t;  // (!!)
>
> /* 1 */ and  /* 2 */ mark operations performed by two separate
> transactions. Imagine that such a sequence were possible, and the update at
> (!) succeeded. What value should count have after this update? If it's 11,
> then a select at (!!) would effectively observe a change written by a
> different transaction, violating transaction isolation. If it's 1, then an
> observer in yet third connection could see the count go up, then down -
> which is surprising as the update statements only ever increment it.
>
> Neither outcome is particularly appealing, so the sequence is prohibited
> altogether.
>
> There are several ways in which transactions that start as readers and
> later promote themselves to writers may cause problems. It's best to avoid
> such situations: if you know that you may need to write eventually, start
> your transaction with BEGIN IMMEDIATE, then it would be marked as a writer
> from the outset.
> --
> Igor Tandetnik
>
> ___
> 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

Re: [sqlite] Interpolation

[Igor Tandetnik]

Steinar Midtskogen  wrote:
> [Igor Tandetnik]
>> Try something like this:
>> 
>> select timestamp, value1, ..., value6 from
>> (select timestamp from tab1
>>  union
>>  select timestamp from tab2
>>  union
>>  select timestamp from tab3)
>> left join tab1 using (timespamp)
>> left join tab2 using (timespamp)
>> left join tab3 using (timespamp);
> 
> Wonderful!  It also eliminates the need to list all the nulls and
> values, and as a bonus it gets ordered by timestamp whereas the
> previous solution required an "ORDER BY timestamp"

If you need a particular order, it's best to add an explicit ORDER BY. 
Otherwise, you are at the mercy of an implementation. Your current version of 
SQLite chooses an execution plan that happens, by accident, to produce rows in 
the desired order. Tomorrow you upgrade to a new version, and it chooses a 
different execution plan that results in a different order.
-- 
Igor Tandetnik

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

Re: [sqlite] Database locked in multi process scenario

[Igor Tandetnik]

Sreekumar TP  wrote:
> How is this different from two threads each with a db connection in a
> single process?

If each thread uses its own separate connection, it should be no different - 
you would observe the same issue.

> Moreover the journal mode is WAL. Hence the writer should be able to append
> changes to the WAL file as there are no other write transaction.

Your problem is with a transaction stat starts as a reader, and later tries to 
become a writer. This is only possible if the reader is observing the most 
recent state of the database, that is, if there were no writes since it started.

Consider:

// initial setup
create table t(count integer);
insert into t values (0);

/* 1 */ select count from t;
/* 2 */ update t set count = count + 10;
/* 1 */ update t set count = count + 1;  // (!)
/* 1 */ select count from t;  // (!!)

/* 1 */ and  /* 2 */ mark operations performed by two separate transactions. 
Imagine that such a sequence were possible, and the update at (!) succeeded. 
What value should count have after this update? If it's 11, then a select at 
(!!) would effectively observe a change written by a different transaction, 
violating transaction isolation. If it's 1, then an observer in yet third 
connection could see the count go up, then down - which is surprising as the 
update statements only ever increment it.

Neither outcome is particularly appealing, so the sequence is prohibited 
altogether.

There are several ways in which transactions that start as readers and later 
promote themselves to writers may cause problems. It's best to avoid such 
situations: if you know that you may need to write eventually, start your 
transaction with BEGIN IMMEDIATE, then it would be marked as a writer from the 
outset.
-- 
Igor Tandetnik

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

Re: [sqlite] Interpolation

[Steinar Midtskogen]

[Igor Tandetnik]

> Steinar Midtskogen  wrote:
>> 
>> Thanks, I didn't think in that simple terms. :) I think about listing
>> all the values, so I got lost.

I lost a word there: "I didn't think about listing"...

>> 
>> But what if the tables share a timestamp, then I would get, say:
>> 
>> 1328873300|1|2| | | |
>> 1328873300| | |3| | |
>> 1328873300| | | |4|5|6
>> 
>> How can that get collapsed into:
>> 
>> 1328873300|1|2|3|4|5|6
>
> Try something like this:
>
> select timestamp, value1, ..., value6 from
> (select timestamp from tab1
>  union
>  select timestamp from tab2
>  union
>  select timestamp from tab3)
> left join tab1 using (timespamp)
> left join tab2 using (timespamp)
> left join tab3 using (timespamp);

Wonderful!  It also eliminates the need to list all the nulls and
values, and as a bonus it gets ordered by timestamp whereas the
previous solution required an "ORDER BY timestamp" which made
everything slower than this solution (for some reason).

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

Re: [sqlite] Database locked in multi process scenario

[Marc L. Allen]

Thanks so much for clarifying that.  I was unaware of the BEGIN IMMEDIATE.  
Sorry.. new to sqlite, used to MySQL and MSSQL.

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Friday, February 10, 2012 9:43 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Database locked in multi process scenario
> 
> Marc L. Allen  wrote:
> > I see.  So, the implied commit doesn't occur until you finalize?
> 
> Or reset.
> 
> > As a result, the subsequent update in step 5 was added to his
> > non-finalized select?
> 
> The update was attempted within the same transaction.
> 
> > Still.. what is the correct way to handle the explicit scenario?  I
> > mean, having one process do a BEGIN SELECT UPDATE and another do
> BEGIN
> > UPDATE is perfectly reasonable, isn't it?  How do you protect from a
> problem?  Detect the error, rollback, and try again?
> 
> That's one way. The other is for the first connection to start its
> transaction with BEGIN IMMEDIATE, thus marking itself as a writer from
> the start.
> --
> Igor Tandetnik
> 
> ___
> 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

Re: [sqlite] Database locked in multi process scenario

[Richard Hipp]

On Fri, Feb 10, 2012 at 9:32 AM, Marc L. Allen
wrote:

> I see.  So, the implied commit doesn't occur until you finalize?  As a
> result, the subsequent update in step 5 was added to his non-finalized
> select?
>
> Still.. what is the correct way to handle the explicit scenario?  I mean,
> having one process do a BEGIN SELECT UPDATE and another do BEGIN UPDATE is
> perfectly reasonable, isn't it?  How do you protect from a problem?  Detect
> the error, rollback, and try again?
>

One approach is to be prepared to rollback and try again.  Or, if you know
that your transaction is going to be reading first and later writing, you
can start with "BEGIN IMMEDIATE" which goes ahead and starts as a write
transaction, guaranteeing that no other process will write ahead of you so
that when you get around to writing yourself, the write won't hit a BUSY.



>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Richard Hipp
> > Sent: Friday, February 10, 2012 9:28 AM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Database locked in multi process scenario
> >
> > On Fri, Feb 10, 2012 at 9:19 AM, Marc L. Allen
> > wrote:
> >
> > >
> > > So, you're assuming the OP actually started a transaction?  Because,
> > > otherwise, isn't the SELECT in step 2 and the UPDATE in step 5
> > > separate transactions?
> > >
> >
> > The OP said "Step 3:  The statement is not reset or finalized".  That
> > doesn't guarantee that the automatic read transaction that was started
> > by the statement is still open, but it is pretty good hint.
> >
> > Remember, every statement runs within a transaction.  Otherwise, the
> > information coming out of the SELECT at the beginning might be
> > incompatible with information that comes out at the end, if another
> > connection modified the database while the select was running.  It is
> > not necessary to explicitly start a transaction with BEGIN in order to
> > be in a transaction.
> > If you don't manually do BEGIN, then BEGIN ... COMMIT is automatically
> > inserted around each SQL statement you run.
> >
> >
> > >
> > > If there is a BEGIN in there somewhere, we're talking about:
> > >
> > > App1:
> > >BEGIN
> > >SELECT
> > >UPDATE
> > >..
> > >
> > > App2BEGIN
> > >UPDATE
> > >...
> > >
> > > Right?  And you're saying that this causes a problem if App2 gets in
> > > between App1's SELECT and UPDATE?
> > >
> > > > -Original Message-
> > > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > > > boun...@sqlite.org] On Behalf Of Richard Hipp
> > > > Sent: Friday, February 10, 2012 9:13 AM
> > > > To: General Discussion of SQLite Database
> > > > Subject: Re: [sqlite] Database locked in multi process scenario
> > > >
> > > > On Fri, Feb 10, 2012 at 9:05 AM, Rob Richardson  > > > con.com>wrote:
> > > >
> > > > > Isn't it almost a requirement of a transaction that only one be
> > > > > open at a time in a database?  If there could be more than one
> > > > transaction,
> > > > > then transaction 1 might start, transaction 2 starts, transaction
> > > > > 1 fails, transaction 1 is rolled back, and what happens to
> > > > > transaction 2?  One could imagine one transaction working an
> > table
> > > > > 1 and a second working on table 2 which has no connection, but
> > > > > then someone comes along and adds a trigger to table 1 that
> > > > > updates table 2.  Now we
> > > > have
> > > > > two simultaneous independent transactions working on table 2.
> > > > >
> > > >
> > > > SQLite supports only SERIALIZABLE transaction semantics.  That
> > means
> > > > the end result of the database is as if the various transactions
> > had
> > > > occurred in a strictly linear sequence.
> > > >
> > > > But SQLite does allow multiple simultaneous transactions to be in
> > > > play, as long as no more than one of them is a write transaction.
> > > > When a read transaction begins, it sees a snapshot of the database
> > > > from the moment in time when the transaction started.  In change
> > > > that occur to the database file from other database connections are
> > > > invisible to that transaction.
> > > >
> > > > The OPs problem is that he has a old read transaction open which is
> > > > looking at an historical snapshot of the database, that does not
> > > > contain the latest changes to the database.  Then he tries to
> > > > promote that read transaction to a write transaction.  But that is
> > > > not allowed, because doing so would "fork" the history of the
> > > > database file.  The result might not be serializable.  Before  you
> > > > can write, you have to first be looking at the most up-to-date copy
> > of the database.
> > > >
> > > >
> > > >
> > > > >
> > > > > RobR, who has been struggling for months with a program that
> > might
> > > > > open the same 

Re: [sqlite] Database locked in multi process scenario

[Igor Tandetnik]

Marc L. Allen  wrote:
> I see.  So, the implied commit doesn't occur until you finalize?

Or reset.

> As a result, the subsequent update in step 5 was added to his
> non-finalized select? 

The update was attempted within the same transaction.

> Still.. what is the correct way to handle the explicit scenario?  I mean, 
> having one process do a BEGIN SELECT UPDATE and another
> do BEGIN UPDATE is perfectly reasonable, isn't it?  How do you protect from a 
> problem?  Detect the error, rollback, and try
> again?  

That's one way. The other is for the first connection to start its transaction 
with BEGIN IMMEDIATE, thus marking itself as a writer from the start.
-- 
Igor Tandetnik

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

Re: [sqlite] Database locked in multi process scenario

[Marc L. Allen]

I see.  So, the implied commit doesn't occur until you finalize?  As a result, 
the subsequent update in step 5 was added to his non-finalized select?

Still.. what is the correct way to handle the explicit scenario?  I mean, 
having one process do a BEGIN SELECT UPDATE and another do BEGIN UPDATE is 
perfectly reasonable, isn't it?  How do you protect from a problem?  Detect the 
error, rollback, and try again?

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: Friday, February 10, 2012 9:28 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Database locked in multi process scenario
> 
> On Fri, Feb 10, 2012 at 9:19 AM, Marc L. Allen
> wrote:
> 
> >
> > So, you're assuming the OP actually started a transaction?  Because,
> > otherwise, isn't the SELECT in step 2 and the UPDATE in step 5
> > separate transactions?
> >
> 
> The OP said "Step 3:  The statement is not reset or finalized".  That
> doesn't guarantee that the automatic read transaction that was started
> by the statement is still open, but it is pretty good hint.
> 
> Remember, every statement runs within a transaction.  Otherwise, the
> information coming out of the SELECT at the beginning might be
> incompatible with information that comes out at the end, if another
> connection modified the database while the select was running.  It is
> not necessary to explicitly start a transaction with BEGIN in order to
> be in a transaction.
> If you don't manually do BEGIN, then BEGIN ... COMMIT is automatically
> inserted around each SQL statement you run.
> 
> 
> >
> > If there is a BEGIN in there somewhere, we're talking about:
> >
> > App1:
> >BEGIN
> >SELECT
> >UPDATE
> >..
> >
> > App2BEGIN
> >UPDATE
> >...
> >
> > Right?  And you're saying that this causes a problem if App2 gets in
> > between App1's SELECT and UPDATE?
> >
> > > -Original Message-
> > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > > boun...@sqlite.org] On Behalf Of Richard Hipp
> > > Sent: Friday, February 10, 2012 9:13 AM
> > > To: General Discussion of SQLite Database
> > > Subject: Re: [sqlite] Database locked in multi process scenario
> > >
> > > On Fri, Feb 10, 2012 at 9:05 AM, Rob Richardson  > > con.com>wrote:
> > >
> > > > Isn't it almost a requirement of a transaction that only one be
> > > > open at a time in a database?  If there could be more than one
> > > transaction,
> > > > then transaction 1 might start, transaction 2 starts, transaction
> > > > 1 fails, transaction 1 is rolled back, and what happens to
> > > > transaction 2?  One could imagine one transaction working an
> table
> > > > 1 and a second working on table 2 which has no connection, but
> > > > then someone comes along and adds a trigger to table 1 that
> > > > updates table 2.  Now we
> > > have
> > > > two simultaneous independent transactions working on table 2.
> > > >
> > >
> > > SQLite supports only SERIALIZABLE transaction semantics.  That
> means
> > > the end result of the database is as if the various transactions
> had
> > > occurred in a strictly linear sequence.
> > >
> > > But SQLite does allow multiple simultaneous transactions to be in
> > > play, as long as no more than one of them is a write transaction.
> > > When a read transaction begins, it sees a snapshot of the database
> > > from the moment in time when the transaction started.  In change
> > > that occur to the database file from other database connections are
> > > invisible to that transaction.
> > >
> > > The OPs problem is that he has a old read transaction open which is
> > > looking at an historical snapshot of the database, that does not
> > > contain the latest changes to the database.  Then he tries to
> > > promote that read transaction to a write transaction.  But that is
> > > not allowed, because doing so would "fork" the history of the
> > > database file.  The result might not be serializable.  Before  you
> > > can write, you have to first be looking at the most up-to-date copy
> of the database.
> > >
> > >
> > >
> > > >
> > > > RobR, who has been struggling for months with a program that
> might
> > > > open the same SQLite file at the same time from two points in the
> > > > program, and who has realized that the program is not well
> designed.
> > > >
> > > > -Original Message-
> > > > From: sqlite-users-boun...@sqlite.org [mailto:
> > > > sqlite-users-boun...@sqlite.org] On Behalf Of Sreekumar TP
> > > > Sent: Friday, February 10, 2012 8:52 AM
> > > > To: General Discussion of SQLite Database
> > > > Subject: Re: [sqlite] Database locked in multi process scenario
> > > >
> > > > In the real code, there is no sleep/wait or pause. It so happens
> > > > that the write of the app2 is scheduled in between.
> > > >
> > > > What you are suggesting is that at any 

Re: [sqlite] Database locked in multi process scenario

[Richard Hipp]

On Fri, Feb 10, 2012 at 9:19 AM, Marc L. Allen
wrote:

>
> So, you're assuming the OP actually started a transaction?  Because,
> otherwise, isn't the SELECT in step 2 and the UPDATE in step 5 separate
> transactions?
>

The OP said "Step 3:  The statement is not reset or finalized".  That
doesn't guarantee that the automatic read transaction that was started by
the statement is still open, but it is pretty good hint.

Remember, every statement runs within a transaction.  Otherwise, the
information coming out of the SELECT at the beginning might be incompatible
with information that comes out at the end, if another connection modified
the database while the select was running.  It is not necessary to
explicitly start a transaction with BEGIN in order to be in a transaction.
If you don't manually do BEGIN, then BEGIN ... COMMIT is automatically
inserted around each SQL statement you run.


>
> If there is a BEGIN in there somewhere, we're talking about:
>
> App1:
>BEGIN
>SELECT
>UPDATE
>..
>
> App2BEGIN
>UPDATE
>...
>
> Right?  And you're saying that this causes a problem if App2 gets in
> between App1's SELECT and UPDATE?
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Richard Hipp
> > Sent: Friday, February 10, 2012 9:13 AM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Database locked in multi process scenario
> >
> > On Fri, Feb 10, 2012 at 9:05 AM, Rob Richardson  > con.com>wrote:
> >
> > > Isn't it almost a requirement of a transaction that only one be open
> > > at a time in a database?  If there could be more than one
> > transaction,
> > > then transaction 1 might start, transaction 2 starts, transaction 1
> > > fails, transaction 1 is rolled back, and what happens to transaction
> > > 2?  One could imagine one transaction working an table 1 and a second
> > > working on table 2 which has no connection, but then someone comes
> > > along and adds a trigger to table 1 that updates table 2.  Now we
> > have
> > > two simultaneous independent transactions working on table 2.
> > >
> >
> > SQLite supports only SERIALIZABLE transaction semantics.  That means
> > the end result of the database is as if the various transactions had
> > occurred in a strictly linear sequence.
> >
> > But SQLite does allow multiple simultaneous transactions to be in play,
> > as long as no more than one of them is a write transaction.  When a
> > read transaction begins, it sees a snapshot of the database from the
> > moment in time when the transaction started.  In change that occur to
> > the database file from other database connections are invisible to that
> > transaction.
> >
> > The OPs problem is that he has a old read transaction open which is
> > looking at an historical snapshot of the database, that does not
> > contain the latest changes to the database.  Then he tries to promote
> > that read transaction to a write transaction.  But that is not allowed,
> > because doing so would "fork" the history of the database file.  The
> > result might not be serializable.  Before  you can write, you have to
> > first be looking at the most up-to-date copy of the database.
> >
> >
> >
> > >
> > > RobR, who has been struggling for months with a program that might
> > > open the same SQLite file at the same time from two points in the
> > > program, and who has realized that the program is not well designed.
> > >
> > > -Original Message-
> > > From: sqlite-users-boun...@sqlite.org [mailto:
> > > sqlite-users-boun...@sqlite.org] On Behalf Of Sreekumar TP
> > > Sent: Friday, February 10, 2012 8:52 AM
> > > To: General Discussion of SQLite Database
> > > Subject: Re: [sqlite] Database locked in multi process scenario
> > >
> > > In the real code, there is no sleep/wait or pause. It so happens that
> > > the write of the app2 is scheduled in between.
> > >
> > > What you are suggesting is that at any point of time only one process
> > > can have a transaction open in a database?
> > >
> > >
> > > Sreekumar
> > > On Feb 10, 2012 7:12 PM, "Simon Slavin"  wrote:
> > >
> > > >
> > > > On 10 Feb 2012, at 1:32pm, Sreekumar TP wrote:
> > > >
> > > > > well, the 'wait' is a simulation of what happens in the real
> > code.
> > > > >
> > > > > The error is fatal to the application as it never ever recovers
> > > > > from it even though the writer has finalized and terminated.
> > > >
> > > > In a multi-process environment I recommend that you do not pause
> > for
> > > > such a long time between the first _step() and the _reset() or
> > > > _finalize().  You can _bind() a statement then wait a long time to
> > > > execute it, but once you have done your first _step() you want to
> > > > get through the data and release the database for other processes.
> > > >
> > > > If you still have the 

Re: [sqlite] sqlite3_close() blocked data base file

[NOCaut2012]

i think fixed:: http://synopse.info/forum/viewtopic.php?id=20
-- 
View this message in context: 
http://old.nabble.com/sqlite3_close%28%29-blocked-data-base-file-tp33292831p33300699.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] Database locked in multi process scenario

[Marc L. Allen]

So, you're assuming the OP actually started a transaction?  Because, otherwise, 
isn't the SELECT in step 2 and the UPDATE in step 5 separate transactions?

If there is a BEGIN in there somewhere, we're talking about:

App1: 
BEGIN
SELECT
UPDATE
..

App2BEGIN
UPDATE
...

Right?  And you're saying that this causes a problem if App2 gets in between 
App1's SELECT and UPDATE?

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: Friday, February 10, 2012 9:13 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Database locked in multi process scenario
> 
> On Fri, Feb 10, 2012 at 9:05 AM, Rob Richardson  con.com>wrote:
> 
> > Isn't it almost a requirement of a transaction that only one be open
> > at a time in a database?  If there could be more than one
> transaction,
> > then transaction 1 might start, transaction 2 starts, transaction 1
> > fails, transaction 1 is rolled back, and what happens to transaction
> > 2?  One could imagine one transaction working an table 1 and a second
> > working on table 2 which has no connection, but then someone comes
> > along and adds a trigger to table 1 that updates table 2.  Now we
> have
> > two simultaneous independent transactions working on table 2.
> >
> 
> SQLite supports only SERIALIZABLE transaction semantics.  That means
> the end result of the database is as if the various transactions had
> occurred in a strictly linear sequence.
> 
> But SQLite does allow multiple simultaneous transactions to be in play,
> as long as no more than one of them is a write transaction.  When a
> read transaction begins, it sees a snapshot of the database from the
> moment in time when the transaction started.  In change that occur to
> the database file from other database connections are invisible to that
> transaction.
> 
> The OPs problem is that he has a old read transaction open which is
> looking at an historical snapshot of the database, that does not
> contain the latest changes to the database.  Then he tries to promote
> that read transaction to a write transaction.  But that is not allowed,
> because doing so would "fork" the history of the database file.  The
> result might not be serializable.  Before  you can write, you have to
> first be looking at the most up-to-date copy of the database.
> 
> 
> 
> >
> > RobR, who has been struggling for months with a program that might
> > open the same SQLite file at the same time from two points in the
> > program, and who has realized that the program is not well designed.
> >
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:
> > sqlite-users-boun...@sqlite.org] On Behalf Of Sreekumar TP
> > Sent: Friday, February 10, 2012 8:52 AM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Database locked in multi process scenario
> >
> > In the real code, there is no sleep/wait or pause. It so happens that
> > the write of the app2 is scheduled in between.
> >
> > What you are suggesting is that at any point of time only one process
> > can have a transaction open in a database?
> >
> >
> > Sreekumar
> > On Feb 10, 2012 7:12 PM, "Simon Slavin"  wrote:
> >
> > >
> > > On 10 Feb 2012, at 1:32pm, Sreekumar TP wrote:
> > >
> > > > well, the 'wait' is a simulation of what happens in the real
> code.
> > > >
> > > > The error is fatal to the application as it never ever recovers
> > > > from it even though the writer has finalized and terminated.
> > >
> > > In a multi-process environment I recommend that you do not pause
> for
> > > such a long time between the first _step() and the _reset() or
> > > _finalize().  You can _bind() a statement then wait a long time to
> > > execute it, but once you have done your first _step() you want to
> > > get through the data and release the database for other processes.
> > >
> > > If you still have the database locked and another process tries to
> > > modify it, one process or the other will have to deal with a BUSY,
> > > or a LOCKED, or something like that.  In your own setup, it turns
> > > out to be process 1.  But a slightly different setup would make
> > > process 2 see a
> > BUSY instead.
> > >
> > > Simon.
> > > ___
> > > 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
> >
> 
> 
> 
> --
> D. Richard Hipp
> d...@sqlite.org
> 

Re: [sqlite] Phrase in the docs

[Max Vlasov]

On Fri, Feb 10, 2012 at 5:45 PM, Igor Tandetnik  wrote:

> Value has TEXT affinity, 5 has none. So 5 is converted to '5', and then
> lexicographic comparisons are performed. It so happens that all strings in
> the Value column lexicographically precede '5'.
>
> If you wanted Value to be treated as an integer and compared numerically,
> why would you declare it as VARCHAR?
> --
>


Hmm, I never saw mentioning literal had NONE affinity (select typeof(5)
returns integer).
As for the source of the problem, I have a table where cell can contain
values (12) or ranges (12-15) so the type should be non-integer, at least
to be mysql-compatible. One of my joins should compare a subset that
guaranteed to contain non-range value, so WHERE clause safely compared
values as integers. After querying in MySql and sqlite I got different
results that led to this narrowing.

Affinity section mentioned the goal of more compatibility, but without
numerical literal being treated exactly like numerical fields the
compatibility looks a little bit limited. I can live with that , but I
think the datatype doc page can also mention this difference.

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

Re: [sqlite] Database locked in multi process scenario

[Richard Hipp]

On Fri, Feb 10, 2012 at 9:05 AM, Rob Richardson wrote:

> Isn't it almost a requirement of a transaction that only one be open at a
> time in a database?  If there could be more than one transaction, then
> transaction 1 might start, transaction 2 starts, transaction 1 fails,
> transaction 1 is rolled back, and what happens to transaction 2?  One could
> imagine one transaction working an table 1 and a second working on table 2
> which has no connection, but then someone comes along and adds a trigger to
> table 1 that updates table 2.  Now we have two simultaneous independent
> transactions working on table 2.
>

SQLite supports only SERIALIZABLE transaction semantics.  That means the
end result of the database is as if the various transactions had occurred
in a strictly linear sequence.

But SQLite does allow multiple simultaneous transactions to be in play, as
long as no more than one of them is a write transaction.  When a read
transaction begins, it sees a snapshot of the database from the moment in
time when the transaction started.  In change that occur to the database
file from other database connections are invisible to that transaction.

The OPs problem is that he has a old read transaction open which is looking
at an historical snapshot of the database, that does not contain the latest
changes to the database.  Then he tries to promote that read transaction to
a write transaction.  But that is not allowed, because doing so would
"fork" the history of the database file.  The result might not be
serializable.  Before  you can write, you have to first be looking at the
most up-to-date copy of the database.



>
> RobR, who has been struggling for months with a program that might open
> the same SQLite file at the same time from two points in the program, and
> who has realized that the program is not well designed.
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Sreekumar TP
> Sent: Friday, February 10, 2012 8:52 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Database locked in multi process scenario
>
> In the real code, there is no sleep/wait or pause. It so happens that the
> write of the app2 is scheduled in between.
>
> What you are suggesting is that at any point of time only one process can
> have a transaction open in a database?
>
>
> Sreekumar
> On Feb 10, 2012 7:12 PM, "Simon Slavin"  wrote:
>
> >
> > On 10 Feb 2012, at 1:32pm, Sreekumar TP wrote:
> >
> > > well, the 'wait' is a simulation of what happens in the real code.
> > >
> > > The error is fatal to the application as it never ever recovers from
> > > it even though the writer has finalized and terminated.
> >
> > In a multi-process environment I recommend that you do not pause for
> > such a long time between the first _step() and the _reset() or
> > _finalize().  You can _bind() a statement then wait a long time to
> > execute it, but once you have done your first _step() you want to get
> > through the data and release the database for other processes.
> >
> > If you still have the database locked and another process tries to
> > modify it, one process or the other will have to deal with a BUSY, or
> > a LOCKED, or something like that.  In your own setup, it turns out to
> > be process 1.  But a slightly different setup would make process 2 see a
> BUSY instead.
> >
> > Simon.
> > ___
> > 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
>



-- 
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] Database locked in multi process scenario

[Richard Hipp]

On Fri, Feb 10, 2012 at 9:01 AM, Marc L. Allen
wrote:

> I'm not sure I'm even following how this scenario can happen.  Doesn't
> App1 have a Shared lock on the DB?  Doesn't App2 require an Exclusive lock
> before it can update something?
>

The OP is running in WAL mode.  Different rules apply.  In WAL mode, you
can have multiple simultaneous readers concurrently with a single writer.
And the readers all see (possibly different) snapshots of the database from
the point in time where their read transaction was first started.



>
> When given the initial scenario, I thought that Step 5 would block waiting
> for App1 to finalize.
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Simon Slavin
> > Sent: Friday, February 10, 2012 8:55 AM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Database locked in multi process scenario
> >
> >
> > On 10 Feb 2012, at 1:52pm, Sreekumar TP wrote:
> >
> > > In the real code, there is no sleep/wait or pause. It so happens that
> > > the write of the app2 is scheduled in between.
> > >
> > > What you are suggesting is that at any point of time only one process
> > > can have a transaction open in a database?
> >
> > I understand your question but I don't know enough of the internals of
> > SQLite to answer it.  Also, I'm not sure how the time between the first
> > _step() and the step which returns 'no more data' resembles a
> > transaction.  I hope someone who knows more than I do will contribute.
> >
> > Simon.
> > ___
> > 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
>



-- 
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] Database locked in multi process scenario

[Rob Richardson]

Isn't it almost a requirement of a transaction that only one be open at a time 
in a database?  If there could be more than one transaction, then transaction 1 
might start, transaction 2 starts, transaction 1 fails, transaction 1 is rolled 
back, and what happens to transaction 2?  One could imagine one transaction 
working an table 1 and a second working on table 2 which has no connection, but 
then someone comes along and adds a trigger to table 1 that updates table 2.  
Now we have two simultaneous independent transactions working on table 2.  

RobR, who has been struggling for months with a program that might open the 
same SQLite file at the same time from two points in the program, and who has 
realized that the program is not well designed.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Sreekumar TP
Sent: Friday, February 10, 2012 8:52 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Database locked in multi process scenario

In the real code, there is no sleep/wait or pause. It so happens that the write 
of the app2 is scheduled in between.

What you are suggesting is that at any point of time only one process can have 
a transaction open in a database?


Sreekumar
On Feb 10, 2012 7:12 PM, "Simon Slavin"  wrote:

>
> On 10 Feb 2012, at 1:32pm, Sreekumar TP wrote:
>
> > well, the 'wait' is a simulation of what happens in the real code.
> >
> > The error is fatal to the application as it never ever recovers from 
> > it even though the writer has finalized and terminated.
>
> In a multi-process environment I recommend that you do not pause for 
> such a long time between the first _step() and the _reset() or 
> _finalize().  You can _bind() a statement then wait a long time to 
> execute it, but once you have done your first _step() you want to get 
> through the data and release the database for other processes.
>
> If you still have the database locked and another process tries to 
> modify it, one process or the other will have to deal with a BUSY, or 
> a LOCKED, or something like that.  In your own setup, it turns out to 
> be process 1.  But a slightly different setup would make process 2 see a BUSY 
> instead.
>
> Simon.
> ___
> 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

Re: [sqlite] Database locked in multi process scenario

[Marc L. Allen]

I'm not sure I'm even following how this scenario can happen.  Doesn't App1 
have a Shared lock on the DB?  Doesn't App2 require an Exclusive lock before it 
can update something?

When given the initial scenario, I thought that Step 5 would block waiting for 
App1 to finalize.

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: Friday, February 10, 2012 8:55 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Database locked in multi process scenario
> 
> 
> On 10 Feb 2012, at 1:52pm, Sreekumar TP wrote:
> 
> > In the real code, there is no sleep/wait or pause. It so happens that
> > the write of the app2 is scheduled in between.
> >
> > What you are suggesting is that at any point of time only one process
> > can have a transaction open in a database?
> 
> I understand your question but I don't know enough of the internals of
> SQLite to answer it.  Also, I'm not sure how the time between the first
> _step() and the step which returns 'no more data' resembles a
> transaction.  I hope someone who knows more than I do will contribute.
> 
> Simon.
> ___
> 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

Re: [sqlite] Database locked in multi process scenario

[Simon Slavin]

On 10 Feb 2012, at 1:52pm, Sreekumar TP wrote:

> In the real code, there is no sleep/wait or pause. It so happens that the
> write of the app2 is scheduled in between.
> 
> What you are suggesting is that at any point of time only one process can
> have a transaction open in a database?

I understand your question but I don't know enough of the internals of SQLite 
to answer it.  Also, I'm not sure how the time between the first _step() and 
the step which returns 'no more data' resembles a transaction.  I hope someone 
who knows more than I do will contribute.

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

Re: [sqlite] Interpolation

[Igor Tandetnik]

Steinar Midtskogen  wrote:
> [Igor Tandetnik]
> 
>>> timestamp|value1|value2|value3|value4|value5|value6
>>> 1328873000|1|2| |  |  |
>>> 1328873050| | |7|  |  |
>>> 1328873075| | | |10|13|16
>>> 1328873100|3|4| |  |  |
>>> 1328873150| | |8|  |  |
>>> 1328873175| | | |11|14|17
>>> 1328873200|5|6| |  |  |
>>> 1328873250| | |9|  |  |
>>> 1328873275| | | |12|15|18
>>> 
>>> But, first things first, how can I merge my tables to get the combined
>>> table with NULLs?
>> 
>> select value1, value2, null, null, null, null from tab1
>> union all
>> select null, null, value3, null, null, null from tab2
>> union all
>> select null, null, null, value4, value5, value6 from tab3;
> 
> Thanks, I didn't think in that simple terms. :) I think about listing
> all the values, so I got lost.
> 
> But what if the tables share a timestamp, then I would get, say:
> 
> 1328873300|1|2| | | |
> 1328873300| | |3| | |
> 1328873300| | | |4|5|6
> 
> How can that get collapsed into:
> 
> 1328873300|1|2|3|4|5|6

Try something like this:

select timestamp, value1, ..., value6 from
(select timestamp from tab1
 union
 select timestamp from tab2
 union
 select timestamp from tab3)
left join tab1 using (timespamp)
left join tab2 using (timespamp)
left join tab3 using (timespamp);

-- 
Igor Tandetnik

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

Re: [sqlite] Database locked in multi process scenario

[Sreekumar TP]

In the real code, there is no sleep/wait or pause. It so happens that the
write of the app2 is scheduled in between.

What you are suggesting is that at any point of time only one process can
have a transaction open in a database?


Sreekumar
On Feb 10, 2012 7:12 PM, "Simon Slavin"  wrote:

>
> On 10 Feb 2012, at 1:32pm, Sreekumar TP wrote:
>
> > well, the 'wait' is a simulation of what happens in the real code.
> >
> > The error is fatal to the application as it never ever recovers from it
> > even though the writer has finalized and terminated.
>
> In a multi-process environment I recommend that you do not pause for such
> a long time between the first _step() and the _reset() or _finalize().  You
> can _bind() a statement then wait a long time to execute it, but once you
> have done your first _step() you want to get through the data and release
> the database for other processes.
>
> If you still have the database locked and another process tries to modify
> it, one process or the other will have to deal with a BUSY, or a LOCKED, or
> something like that.  In your own setup, it turns out to be process 1.  But
> a slightly different setup would make process 2 see a BUSY instead.
>
> Simon.
> ___
> 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

Re: [sqlite] Phrase in the docs

[Igor Tandetnik]

Max Vlasov  wrote:
> On Fri, Feb 10, 2012 at 4:53 PM, Richard Hipp  wrote:
>> In the statement:
>> 
>>SELECT '25' < 25;
>> 
>> There are no columns, only literals.  And hence no affinity is applied.
> 
> So if a string looks like a numeral it should be treated as numeral by
> sqlite?

Not unless something else in the expression forces numeric affinity.

> The following db and query returns results bigger than 5, is this ok?
> 
> CREATE TABLE [testtable] (
> [Id] INTEGER PRIMARY KEY AUTOINCREMENT,
> [value] VARCHAR(200)
> );
> INSERT INTO "testtable" VALUES(4,'-3');
> INSERT INTO "testtable" VALUES(5,'-2');
> INSERT INTO "testtable" VALUES(11,'33');
> INSERT INTO "testtable" VALUES(12,'44');
>
> SELECT value from testtable WHERE Value  <= 5

Value has TEXT affinity, 5 has none. So 5 is converted to '5', and then 
lexicographic comparisons are performed. It so happens that all strings in the 
Value column lexicographically precede '5'.

If you wanted Value to be treated as an integer and compared numerically, why 
would you declare it as VARCHAR?
-- 
Igor Tandetnik

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

Re: [sqlite] Database locked in multi process scenario

[Simon Slavin]

On 10 Feb 2012, at 1:32pm, Sreekumar TP wrote:

> well, the 'wait' is a simulation of what happens in the real code.
> 
> The error is fatal to the application as it never ever recovers from it
> even though the writer has finalized and terminated.

In a multi-process environment I recommend that you do not pause for such a 
long time between the first _step() and the _reset() or _finalize().  You can 
_bind() a statement then wait a long time to execute it, but once you have done 
your first _step() you want to get through the data and release the database 
for other processes.

If you still have the database locked and another process tries to modify it, 
one process or the other will have to deal with a BUSY, or a LOCKED, or 
something like that.  In your own setup, it turns out to be process 1.  But a 
slightly different setup would make process 2 see a BUSY instead.

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

Re: [sqlite] Interpolation

[Steinar Midtskogen]

[Igor Tandetnik]

>> timestamp|value1|value2|value3|value4|value5|value6
>> 1328873000|1|2| |  |  |
>> 1328873050| | |7|  |  |
>> 1328873075| | | |10|13|16
>> 1328873100|3|4| |  |  |
>> 1328873150| | |8|  |  |
>> 1328873175| | | |11|14|17
>> 1328873200|5|6| |  |  |
>> 1328873250| | |9|  |  |
>> 1328873275| | | |12|15|18
>> 
>> But, first things first, how can I merge my tables to get the combined
>> table with NULLs?
>
> select value1, value2, null, null, null, null from tab1
> union all
> select null, null, value3, null, null, null from tab2
> union all
> select null, null, null, value4, value5, value6 from tab3;

Thanks, I didn't think in that simple terms. :) I think about listing
all the values, so I got lost.

But what if the tables share a timestamp, then I would get, say:

1328873300|1|2| | | |
1328873300| | |3| | |
1328873300| | | |4|5|6

How can that get collapsed into:

1328873300|1|2|3|4|5|6

?

One way could be to fill out the missing values using interpolation
(as I would like anyway), then remove duplicate lines, but if there is
a simple way before I attempt to interpolate, I should probably
collapse first.

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

Re: [sqlite] Database locked in multi process scenario

[Sreekumar TP]

Hi Simon,

well, the 'wait' is a simulation of what happens in the real code.

The error is fatal to the application as it never ever recovers from it
even though the writer has finalized and terminated.

Sreekumar
On Feb 10, 2012 6:57 PM, "Simon Slavin"  wrote:

>
> On 10 Feb 2012, at 11:47am, Sreekumar TP wrote:
>
> > I have a 'database is locked' issued which can be reproduced as follows.
> >
> > I have two applications opening the database in WAL mode. The threading
> > mode is SERIALIZED.  Environment is  PC/Linux.
> >
> >
> >
> > Step1: Launch App1 followed by App 2 ( same executables)
> > Step 2: App1 Prepares a SELECT statement and executes the statement.
> > Step 3: The statement is not reset and finalized.
> > Step 4: App1 wait on keyboard input
> > Step 5 : App2 prepares a statement to update a few records. The statement
> > is  executed, reset and finalized
> > Step 6: Go back to App1, prepare an UPDATE/INSERT statement and execute
> it.
>
> Sreekumar, imagine what would happen if two users both got to step 4 at
> the same point at the same time.  There are no circumstances under which
> either of them could proceed.  Whichever of them tried to proceed would
> invalidate the SELECT that the other one was still in the middle of.
>
> When writing your software, imagine that the database is locked from the
> 'execute' until you have done the 'finalize'.  You want to do the finalize
> as soon as possible, to unlock the database for other users.  You can't
> wait for a user to type something: their phone might ring or something.
>
> Simon.
> ___
> 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

Re: [sqlite] Phrase in the docs

[Max Vlasov]

On Fri, Feb 10, 2012 at 4:53 PM, Richard Hipp  wrote:

> On Fri, Feb 10, 2012 at 6:11 AM, Max Vlasov  wrote:
>
> > Hi,
> > working with sqlite and mysql, noticed that they're different in regard
> of
> > mixed types.
> >  Select '24' < 25
> >  Select 24 < 25
> > have the same results in MySql and different sqlite.
> >
>
> In the statement:
>
>SELECT '25' < 25;
>
> There are no columns, only literals.  And hence no affinity is applied.
>
>
>

So if a string looks like a numeral it should be treated as numeral by
sqlite?

The following db and query returns results bigger than 5, is this ok?

CREATE TABLE [testtable] (
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[value] VARCHAR(200)
);
INSERT INTO "testtable" VALUES(4,'-3');
INSERT INTO "testtable" VALUES(5,'-2');
INSERT INTO "testtable" VALUES(11,'33');
INSERT INTO "testtable" VALUES(12,'44');
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('testtable',20);
SELECT value from testtable WHERE Value  <= 5
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Simon Slavin]

On 10 Feb 2012, at 11:47am, Sreekumar TP wrote:

> I have a 'database is locked' issued which can be reproduced as follows.
> 
> I have two applications opening the database in WAL mode. The threading
> mode is SERIALIZED.  Environment is  PC/Linux.
> 
> 
> 
> Step1: Launch App1 followed by App 2 ( same executables)
> Step 2: App1 Prepares a SELECT statement and executes the statement.
> Step 3: The statement is not reset and finalized.
> Step 4: App1 wait on keyboard input
> Step 5 : App2 prepares a statement to update a few records. The statement
> is  executed, reset and finalized
> Step 6: Go back to App1, prepare an UPDATE/INSERT statement and execute it.

Sreekumar, imagine what would happen if two users both got to step 4 at the 
same point at the same time.  There are no circumstances under which either of 
them could proceed.  Whichever of them tried to proceed would invalidate the 
SELECT that the other one was still in the middle of.

When writing your software, imagine that the database is locked from the 
'execute' until you have done the 'finalize'.  You want to do the finalize as 
soon as possible, to unlock the database for other users.  You can't wait for a 
user to type something: their phone might ring or something.

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

Re: [sqlite] Interpolation

[Igor Tandetnik]

Steinar Midtskogen  wrote:
> Let's say I have N tables, each with a
> timestamp as primary key.  For instance:
> 
> tab1:
> timestamp|value1|value2
> 1328873000|1|2
> 1328873100|3|4
> 1328873200|5|6
> 
> tab2:
> timestamp|value3
> 1328873050|7
> 1328873150|8
> 1328873250|9
> 
> tab3:
> timestamp|value4|value5|value6
> 1328873075|10|13|16
> 1328873175|11|14|17
> 1328873275|12|15|18
> 
> So the resulting table should be:
> 
> timestamp|value1|value2|value3|value4|value5|value6
> 1328873000|1|2| |  |  |
> 1328873050| | |7|  |  |
> 1328873075| | | |10|13|16
> 1328873100|3|4| |  |  |
> 1328873150| | |8|  |  |
> 1328873175| | | |11|14|17
> 1328873200|5|6| |  |  |
> 1328873250| | |9|  |  |
> 1328873275| | | |12|15|18
> 
> But, first things first, how can I merge my tables to get the combined
> table with NULLs?

select value1, value2, null, null, null, null from tab1
union all
select null, null, value3, null, null, null from tab2
union all
select null, null, null, value4, value5, value6 from tab3;

-- 
Igor Tandetnik

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

Re: [sqlite] Database locked in multi process scenario

[Sreekumar TP]

How is this different from two threads each with a db connection in a
single process?

Moreover the journal mode is WAL. Hence the writer should be able to append
changes to the WAL file as there are no other write transaction.

Sreekumar
On Feb 10, 2012 6:22 PM, "Richard Hipp"  wrote:

> On Fri, Feb 10, 2012 at 6:47 AM, Sreekumar TP  >wrote:
>
> > I have a 'database is locked' issued which can be reproduced as follows.
> >
> > I have two applications opening the database in WAL mode. The threading
> > mode is SERIALIZED.  Environment is  PC/Linux.
> >
> >
> >
> > Step1: Launch App1 followed by App 2 ( same executables)
> > Step 2: App1 Prepares a SELECT statement and executes the statement.
> > Step 3: The statement is not reset and finalized.
> > Step 4: App1 wait on keyboard input
> > Step 5 : App2 prepares a statement to update a few records. The statement
> > is  executed, reset and finalized
> > Step 6: Go back to App1, prepare an UPDATE/INSERT statement and execute
> it.
> >
>
> At this point, App1 is still holding a read transaction open that points to
> an image of the database from an earlier point in time.  App1 is not
> allowed to write to the database because that would cause the database
> content to fork.  Any database connection must be in a transaction that
> shows the very latest content of the database in order for it to write
> without forking.
>
>
> >
> > Result: 'database is locked' error is thrown by App 1
> >
> > App2 should have released the exclusive lock after it has reset and
> > finalized the statement.Hence App1 should not have encountered
> SQLITE_BUSY
> > when writing.
> >
> >
> > What could be the cause ?
> >
> > Kind Regards,
> > Sreekumar
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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

Re: [sqlite] Phrase in the docs

[Richard Hipp]

On Fri, Feb 10, 2012 at 6:11 AM, Max Vlasov  wrote:

> Hi,
> working with sqlite and mysql, noticed that they're different in regard of
> mixed types.
>  Select '24' < 25
>  Select 24 < 25
> have the same results in MySql and different sqlite.
>
> Actually it's no news (my sqlite queries contained CAST(.. as INT) ), but I
> decided to look at the docs and noticed that first sentence
>
> http://www.sqlite.org/datatype3.html
>
>  ...If one operand has INTEGER, REAL or NUMERIC affinity and the other
> operand as TEXT or NONE affinity then NUMERIC affinity is applied to other
> operand.
> but below
>  ...Because column "a" has text affinity, numeric values on the
> right-hand side of the comparisons are converted to text before  the
> comparison occurs.
>
> Shouldn't the first be fixed to end with "...then TEXT affinity is applied
> to other operand"?
>

In the statement:

SELECT '25' < 25;

There are no columns, only literals.  And hence no affinity is applied.


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



-- 
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] Database locked in multi process scenario

[Richard Hipp]

On Fri, Feb 10, 2012 at 6:47 AM, Sreekumar TP wrote:

> I have a 'database is locked' issued which can be reproduced as follows.
>
> I have two applications opening the database in WAL mode. The threading
> mode is SERIALIZED.  Environment is  PC/Linux.
>
>
>
> Step1: Launch App1 followed by App 2 ( same executables)
> Step 2: App1 Prepares a SELECT statement and executes the statement.
> Step 3: The statement is not reset and finalized.
> Step 4: App1 wait on keyboard input
> Step 5 : App2 prepares a statement to update a few records. The statement
> is  executed, reset and finalized
> Step 6: Go back to App1, prepare an UPDATE/INSERT statement and execute it.
>

At this point, App1 is still holding a read transaction open that points to
an image of the database from an earlier point in time.  App1 is not
allowed to write to the database because that would cause the database
content to fork.  Any database connection must be in a transaction that
shows the very latest content of the database in order for it to write
without forking.


>
> Result: 'database is locked' error is thrown by App 1
>
> App2 should have released the exclusive lock after it has reset and
> finalized the statement.Hence App1 should not have encountered SQLITE_BUSY
> when writing.
>
>
> What could be the cause ?
>
> Kind Regards,
> Sreekumar
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] Interpolation

[Steinar Midtskogen]

I've rethought the interpolation strategy.  It's not important to be
able to look up any timestamp, just the timestamps that actually have
values in at least one table.  Let's say I have N tables, each with a
timestamp as primary key.  For instance:

tab1:
timestamp|value1|value2
1328873000|1|2  
1328873100|3|4  
1328873200|5|6  

tab2:
timestamp|value3
1328873050|7
1328873150|8 
1328873250|9

tab3:
timestamp|value4|value5|value6
1328873075|10|13|16
1328873175|11|14|17
1328873275|12|15|18

First, I'd like to merge all tables to create one single table with
every timestamp and every value.  That is, it will have the timestamps
that I get by:

 SELECT timestamp FROM tab1 UNION
 SELECT timestamp FROM tab2 UNION
 SELECT timestamp FROM tab3;

So the resulting table should be:

timestamp|value1|value2|value3|value4|value5|value6
1328873000|1|2| |  |  |
1328873050| | |7|  |  |
1328873075| | | |10|13|16
1328873100|3|4| |  |  |
1328873150| | |8|  |  |
1328873175| | | |11|14|17
1328873200|5|6| |  |  |
1328873250| | |9|  |  |
1328873275| | | |12|15|18

The resulting table will have a lot of NULLs.  Next, I'd like to fill
out all NULLs by using linear interpolation.

But, first things first, how can I merge my tables to get the combined
table with NULLs?  I've been playing with JOIN and UNION, but I'm
afraid my SQL experience is very limited, so I got stuck at this one.

In reality I have 7 tables to be merged with a lot of columns (perhaps
100 in all) and up to a million rows, so if an SQL statement to do
this sounds unrealistic, that would be a good answer as well.

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

[sqlite] Database locked in multi process scenario

[Sreekumar TP]

I have a 'database is locked' issued which can be reproduced as follows.

I have two applications opening the database in WAL mode. The threading
mode is SERIALIZED.  Environment is  PC/Linux.



Step1: Launch App1 followed by App 2 ( same executables)
Step 2: App1 Prepares a SELECT statement and executes the statement.
Step 3: The statement is not reset and finalized.
Step 4: App1 wait on keyboard input
Step 5 : App2 prepares a statement to update a few records. The statement
is  executed, reset and finalized
Step 6: Go back to App1, prepare an UPDATE/INSERT statement and execute it.

Result: 'database is locked' error is thrown by App 1

App2 should have released the exclusive lock after it has reset and
finalized the statement.Hence App1 should not have encountered SQLITE_BUSY
when writing.


What could be the cause ?

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

Re: [sqlite] Database corruption with fts table after closing/reopening the database

[Simon Slavin]

On 10 Feb 2012, at 11:01am, Julien LF wrote:

>> Did you try surrounding them strings with single quotes, instead ?
> 
> I got the same problem using single quotes.
> However the machine I was performing those tests on is a vmware virtual 
> machine.
> Performing the same steps on physical hardware (identical os & sqlite
> version) worked fine, so I guess there is some inconsistency somewhere
> between NetBSD and vmware at the disk writing level.
> 
> Sorry for the noise.

Actually I'm a little concerned at that.  SQLite may be marvellous but it's not 
mystical.  Many other programs use similar patterns of disk reading and 
writing.  You seem to have found a serious fault in the virtual machine system.

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

[sqlite] Phrase in the docs

[Max Vlasov]

Hi,
working with sqlite and mysql, noticed that they're different in regard of
mixed types.
  Select '24' < 25
  Select 24 < 25
have the same results in MySql and different sqlite.

Actually it's no news (my sqlite queries contained CAST(.. as INT) ), but I
decided to look at the docs and noticed that first sentence

http://www.sqlite.org/datatype3.html

  ...If one operand has INTEGER, REAL or NUMERIC affinity and the other
operand as TEXT or NONE affinity then NUMERIC affinity is applied to other
operand.
but below
  ...Because column "a" has text affinity, numeric values on the
right-hand side of the comparisons are converted to text before  the
comparison occurs.

Shouldn't the first be fixed to end with "...then TEXT affinity is applied
to other operand"?

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

Re: [sqlite] Database corruption with fts table after closing/reopening the database

[Julien LF]

> Did you try surrounding them strings with single quotes, instead ?

I got the same problem using single quotes.
However the machine I was performing those tests on is a vmware virtual machine.
Performing the same steps on physical hardware (identical os & sqlite
version) worked fine, so I guess there is some inconsistency somewhere
between NetBSD and vmware at the disk writing level.

Sorry for the noise.

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

Re: [sqlite] Database corruption with fts table after closing/reopening the database

[Kevin Benson]

On Fri, Feb 10, 2012 at 5:20 AM, Julien LF  wrote:

> Hello,
>
> I'm testing the fts extensions and experiencing a 'database disk image
> is malformed' problem that I can reproduce the following way:
> - Create a database
> - Open the database to insert/update rows. 'match' queries work at this
> point
> - Close the database
> - Open the database. 'match' queries still work at this point
> - Update a row
> - 'match' queries generate a 'database disk image is malformed'
> message, but normal selects work fine.
>
> This is sqlite 3.7.7.1 on NetBSD 5.1. I tried both fts3 and fts4.
>
> You'll find the schema below, followed by the complete detailed steps
> to produce the error. Is there something I'm missing?
>
> Regards,
> Julien
>
> CREATE VIRTUAL TABLE page USING fts4 (
>url,
>title,
>content
> );
>
> vnetbsd$ sqlite3 wiki.db < ./schema.sql
> vnetbsd$ sqlite3 ./wiki.db
> SQLite version 3.7.7.1 2011-06-28 17:39:05
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> insert into page(url,title,content)
> values("intro","intro","intro");
> sqlite> select * from page where url match 'intro';
> intro|intro|intro
> sqlite> update page set url="intro", title="intro", content="introm"
> where rowid=1;
> sqlite> select * from page where url match 'intro';
> intro|intro|introm
> sqlite> .q
> vnetbsd$ sqlite3 ./wiki.db
> SQLite version 3.7.7.1 2011-06-28 17:39:05
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> select * from page where url match 'intro';
> intro|intro|introm
> sqlite> update page set url="intro", title="intro", content="intro"
> where rowid=1;
> sqlite> select * from page where url match 'intro';
> Error: database disk image is malformed
> sqlite> select * from page;
> intro|intro|intro
>
Did you try surrounding them strings with single quotes, instead ?
--
   --
  --
 --ô¿ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step getting core dumbed.

[Tim Streater]

On 10 Feb 2012 at 07:55, bhaskarReddy  wrote: 

> PRAGMA table_info(yourtablename); will display  colNumber, colName, colType,
> 
> ex: 0|slotId|INTEGER|0||0
> 1|ponChannelId|INTEGER|0||0
> 2|onuType|INTEGER|0||0
> 3|onuSerialNumber|TEXT|0||0
> 4|onuId|INTEGER|0||0
> 5|plannedSwVersion|TEXT|0||0
> 6|adminStatus|INTEGER|0||0
>
> In that how can we extract INTEGER.

I expect that in the result set, third column (index 2) says INTEGER.

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

[sqlite] Database corruption with fts table after closing/reopening the database

[Julien LF]

Hello,

I'm testing the fts extensions and experiencing a 'database disk image
is malformed' problem that I can reproduce the following way:
- Create a database
- Open the database to insert/update rows. 'match' queries work at this point
- Close the database
- Open the database. 'match' queries still work at this point
- Update a row
- 'match' queries generate a 'database disk image is malformed'
message, but normal selects work fine.

This is sqlite 3.7.7.1 on NetBSD 5.1. I tried both fts3 and fts4.

You'll find the schema below, followed by the complete detailed steps
to produce the error. Is there something I'm missing?

Regards,
Julien

CREATE VIRTUAL TABLE page USING fts4 (
url,
title,
content
);

vnetbsd$ sqlite3 wiki.db < ./schema.sql
vnetbsd$ sqlite3 ./wiki.db
SQLite version 3.7.7.1 2011-06-28 17:39:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> insert into page(url,title,content) values("intro","intro","intro");
sqlite> select * from page where url match 'intro';
intro|intro|intro
sqlite> update page set url="intro", title="intro", content="introm"
where rowid=1;
sqlite> select * from page where url match 'intro';
intro|intro|introm
sqlite> .q
vnetbsd$ sqlite3 ./wiki.db
SQLite version 3.7.7.1 2011-06-28 17:39:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from page where url match 'intro';
intro|intro|introm
sqlite> update page set url="intro", title="intro", content="intro"
where rowid=1;
sqlite> select * from page where url match 'intro';
Error: database disk image is malformed
sqlite> select * from page;
intro|intro|intro
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users