Re: [sqlite] [sqlite-dev] sqlite3 db is locked

[Alexander Syvak]

The code in function from the 1st e-mail is used before exiting, so the
sqlite3_close is called in fact.


2013/12/30 Dan Kennedy 

> On 12/29/2013 11:43 PM, Alexander Syvak wrote:
>
>> Hello,
>>
>> there's a LabView app. which is calling C++ function from a DLL.
>> In the DLL the function opens a connection, selects data using
>> sqlite3_exec and closing using the code
>>
>>int rc = 0;
>>while ( (rc = sqlite3_close(db)) == SQLITE_BUSY)
>>{
>>// TODO:
>>// shouldn't happen in a good written application but let's
>> handle it
>>sqlite3_stmt * stmt_raw_p = nullptr;
>>while ((stmt_raw_p = sqlite3_next_stmt(db, NULL)) != NULL)
>> sqlite3_finalize(stmt_raw_p);
>>   // TODO: if (rc != SQLITE_OK)
>>   // then the app. messes with other thread which should not
>> happen
>>   // more tests are required
>>}
>> The rc after the if statement holds SQLITE_OK.
>> However, after execution the function the LabView app. is finished but
>> the IDE is not closed. At deleting the db file using Delete key Windows
>> says it's locked and you can either Try again or Cancel. After the IDE is
>> closed, the file may be deleted.
>> It's needed to delete the file during the app. is running.
>>
>
> I would guess you need to call sqlite3_close() before the
> program exits in this case.
>
> Technically, this is the wrong mailing list for questions
> to do with developing SQLite apps. You will get more answers
> on the "sqlite-users" group.
>
> Dan.
>
>
>
>
>>
>> ___
>> sqlite-dev mailing list
>> sqlite-...@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev
>>
>
> ___
> sqlite-dev mailing list
> sqlite-...@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Web application with SQLite

[Kevin Benson]

On Tue, Dec 31, 2013 at 6:17 PM, Tim Johnson  wrote:

> * Eduardo  [131231 07:11]:
> >
> > You can check mongoose webserver. It uses LUA for scripting web pages
> > (similar to PHP but with LUA) and Sqlite for db access from LUA.
>
>   I found what I believe is the mongoose page at
>   https://code.google.com/p/mongoose/
>   Very interesting!
>
>   BTW: See the following text at that page :
>   "Lua server pages (PHP-like functionality using Lua) with SQLite3,
>   see page.lp"
>   Where "page.lp" is a link to
>   https://github.com/cesanta/mongoose/blob/master/test/page.lp
>   That URL generates a 404 error.


https://groups.google.com/forum/#!msg/mongoose-users/OLY1hQnyHh8/uB0HAyITuCcJ

--
   --
  --
 --Ô¿Ô--
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] Happy New Year

[Ryan Finnesey]

Happy New Year to you as well. 

Sent from my iPad

> On Dec 31, 2013, at 7:46 PM, "Igor Korot"  wrote:
> 
> Hi, ALL,
> I want to wish everybody who is reading and involved with the list
> Happy and oyful New Year!
> Let's have a great time in it and lets make a lot of good products and
> new releases with the software that everybody involve with.
> 
> Thank you.
> ___
> 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] unable to use function MATCH in requested context

[E. Timothy Uy]

In fact, it does not work in a nested query - the issue is actually
multiple inner joins. When I LEFT JOIN the rest of the tables, it works
fine. This must have been a change in the last year or so.


On Tue, Dec 31, 2013 at 3:15 PM, E. Timothy Uy  wrote:

> Hi, as a sanity check, did something change in the use of MATCH with JOINs?
>
> I used to do
>
> SELECT ...
> FROM Entries e
> INNER JOIN Combos c ON e.Entries MATCH c.combo
> INNER JOIN Entries_content ec ON ec.docid = e.docid
>
> but now I get "unable to use function MATCH in the requested context". I
> can pull it off in a nested query with the MATCH by itself, but I feel like
> this totally used to work. Here Entries is an FTS3 table and
> Entries_content is associated content.
>
> Thank you in advance.
>
> Respectfully
> Tim
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Web application with SQLite

[Tim Johnson]

* Eduardo  [131231 07:11]:
> 
> You can check mongoose webserver. It uses LUA for scripting web pages
> (similar to PHP but with LUA) and Sqlite for db access from LUA.

  I found what I believe is the mongoose page at
  https://code.google.com/p/mongoose/
  Very interesting! 

  BTW: See the following text at that page :
  "Lua server pages (PHP-like functionality using Lua) with SQLite3,
  see page.lp"
  Where "page.lp" is a link to
  https://github.com/cesanta/mongoose/blob/master/test/page.lp
  That URL generates a 404 error.

> You can use scgi lib from http://www.xamuel.com/scgilib/ and use Sqlite
> if you are developing a webservice.
  
   Thank you for turning me on to this.

-- 
Tim 
tim at tee jay forty nine dot com or akwebsoft dot com
http://www.akwebsoft.com, http://www.tj49.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] unable to use function MATCH in requested context

[E. Timothy Uy]

Hi, as a sanity check, did something change in the use of MATCH with JOINs?

I used to do

SELECT ...
FROM Entries e
INNER JOIN Combos c ON e.Entries MATCH c.combo
INNER JOIN Entries_content ec ON ec.docid = e.docid

but now I get "unable to use function MATCH in the requested context". I
can pull it off in a nested query with the MATCH by itself, but I feel like
this totally used to work. Here Entries is an FTS3 table and
Entries_content is associated content.

Thank you in advance.

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

Re: [sqlite] "Common Table Expression"

[Simon Slavin]

On 31 Dec 2013, at 10:05pm, James K. Lowden  wrote:

> Meanwhile, here's a much more important failing that cannot be worked
> around within SQL without a temporary table:
> 
>   sqlite> create table i ( i int primary key );
>   sqlite> insert into i values (1);
>   sqlite> insert into i values (2);
>   sqlite> update i set i = i + 1;
>   SQL error: column i is not unique

Is that not actually a serious bug in SQLite ?  My understanding of SQL is that 
checks for consistency should occur at the end of a transaction, not at each 
row of an UPDATE.  I hadn't realised this problem existed.

I wonder whether it would be possible to fix this in SQLite4, if not before.  
SQLite4 implements PRIMARY KEYs properly, of course, but I don't know whether 
that means there'd have to be one fix or two.

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

Re: [sqlite] "Common Table Expression"

[James K. Lowden]

On Tue, 31 Dec 2013 20:43:20 +0100
big stone  wrote:

> To get CTE in SQLite, I guess we must answer by the example the fears
> expressed by Simon and Rsmith.
> 
> I propose the following method :
>   - unproven-demand :
...
>   - code size + performance increase fear :

It seems to me you have more substantial hurdle, namely 

0.  undemonstrated utility

(See my reply to you on 31 Dec 2013 13:59:26 -0500 for more details.) 

Whether or not CTEs are wanted and easily implemented, they add exactly
zero to SQLite's capability.  All they do is afford another form or
equivalent expression for the same query.  That is: complexity without
power.  

Meanwhile, here's a much more important failing that cannot be worked
around within SQL without a temporary table:

sqlite> create table i ( i int primary key );
sqlite> insert into i values (1);
sqlite> insert into i values (2);
sqlite> update i set i = i + 1;
SQL error: column i is not unique

The workaround is:

0.  BEGIN TRANSACTION
1.  Create a temporary table T to hold the new values with 
CREATE TEMPORARY TABLE T AS SELECT ...
2.  Delete rows from main table M based on the old values
3.  INSERT INTO M SELECT * FROM T;
4.  COMMIT TRANSACTION;
5.  DROP TABLE T;

That only works in SQLite because a user-defined transaction locks the
database.  In most DBMSs the same SQL leaves the main table M open to
update by another process, making the results indeterminate.  

The problem crops up anytime unique constraints are in force, including
updating primary keys.  I know of no more important flaw in SQLite.  

--jkl


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

Re: [sqlite] Windows Embedded Handheld 6.5

[Ryan Finnesey]

I did see the System.Data.SQLite binaries for PocketPC I was not shore  they 
were the ones to use.  It has been a very long time since I have done any 
Windows Mobile development and within Visual Studio 2008  I see a development 
template and separate SDK for PocketPC and Windows Mobile 6.0 

Thank you for your help.

Cheers
Ryan


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Joe Mistachkin
Sent: Monday, December 30, 2013 4:29 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Windows Embedded Handheld 6.5


Ryan Finnesey wrote:
> 
> If I want to add a SQLite database to an application I am developing 
> to
run
> on Windows Embedded Handheld 6.5 do I need to compile the SQLite 
> Source
Code
> or do I just use System.Data.SQLite
> 

Unless you need custom compilation options, it will probably be easier just to 
use the System.Data.SQLite binaries for "PocketPC", seen here under 
"Precompiled Binaries for Windows CE (.NET Compact Framework)":

https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

--
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] SQLite 2013 retrospective

[Ryan Finnesey]

Wanted to thank the team for all there great work and wish everyone a Happy New 
Year! And all the best in 2014.

Cheers
Ryan


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Tuesday, December 31, 2013 11:37 AM
To: General Discussion of SQLite Database
Subject: [sqlite] SQLite 2013 retrospective

Here is a quick summary of the changes and enhancements to SQLite during 2013.  
The comparison is between trunk versions,
http://www.sqlite.org/src/info/a611c75 versus
http://www.sqlite.org/src/info/cc72c5aec7

The amalgamation source file grew in side from 137619 lines to 145010 lines, or 
4868834 bytes to 5125216 bytes.  Excluding comments the size grew from 82156 
lines to 87537 lines.  That's between 5% to 7% growth in source code, depending 
on how you measure.  Approximately one source code line out of every five 
changed during 2013 for a 20% code churn.

The compiled binary (gcc 4.8.1 with -Os on x64) grew from 412365 to 433963 
bytes, or about 5%.

Running a typical mix of SQL statements (as implemented by the 
http://www.sqlite.org/src/artifact/7130d2cb?ln test program), SQLite uses 15.5% 
fewer CPU operations to do the same task as it did one year ago, as measured by 
valgrind.  Real-time performance is about 12% faster according to that same 
benchmark running on Ubuntu 13.10, x64.

There were 737 trunk check-ins during 2013, or about two check-ins per day.  
There were an additional 924 check-ins on branches, for a total of
1661 check-ins.  416 files were changed in some way or another.  24216 lines 
were inserted and 12744 lines deleted, according to diffstat.  The above is for 
the main source tree only.  There are many, many other enhancements to the 
documentation and test suites.

The following releases occurred during 2013:

2013-01-09:   3.7.15.2
2013-03-18:   3.7.16
2013-03-29:   3.7.16.1
2013-04-12:   3.7.16.2
2013-05-20:   3.7.17
2013-08-26:   3.8.0
2013-08-29:   3.8.0.1
2013-09-03:   3.8.0.2
2013-10-17:   3.8.1
2013-12-06:   3.8.2

Major new features added in 2013 include:

   1.  Memory-mapped I/O:  http://www.sqlite.org/mmap.html
   2.  The next-generation query planner:
http://www.sqlite.org/queryplanner-ng.html
   3.  Partial indices: http://www.sqlite.org/partialindex.html
   4.  WITHOUT ROWID tables: http://www.sqlite.org/withoutrowid.html

New PRAGMAs:

   1.  PRAGMA foreign_key_check;
   2.  PRAGMA application_id;
   3.  PRAGMA defer_foreign_keys;
   4.  PRAGMA cache_spill;
   5.  PRAGMA query_only;
   6.  PRAGMA soft_heap_limit;

New SQL functions:

   1.  printf()  -- to appear in version 3.8.3
   2.  unlikely()
   3.  likelihood()
   4.  unicode()
   5.  char()

New C-language APIs:

   1.  sqlite3_cancel_auto_extension()
   2.  sqlite3_strglob()

New tested and supported extensions added to the source tree:

   1.  The approximate_match virtual table:
www.sqlite.org/src/artifact/678056a
   2.  The transitive_closure virtual table:
www.sqlite.org/src/artifact/6360243
   3.  The ieee754() SQL function:
http://www.sqlite.org/src/artifact/b03621672
   4.  The next_char() SQL function: www.sqlite.org/src/artifact/35c8b8ba
   5.  The percentile() SQL function: www.sqlite.org/src/artifact/bcbee3c
   6.  The regexp() SQL function:
http://www.sqlite.org/src/artifact/af92cdaa5
   7.  The rot13() SQL function: http://www.sqlite.org/src/artifact/1ac6f95f
   8.  The spellfix1 virtual table: http://www.sqlite.org/spellfix1.html
   9.  The tointeger() and toreal() SQL functions:
www.sqlite.org/src/artifact/4a167594
   10. The wholenumber virtual table: www.sqlite.org/src/artifact/784b1254

In addition to the above, there are countless new test cases and minor feature 
and performance enhancements.

Our goal is to maintain this aggressive pace of innovation and enhancement in 
SQLite throughout 2014 and beyond.

Happy New Year to all.
--
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] SQLite 2013 retrospective

[Simon Slavin]

On 31 Dec 2013, at 7:36pm, jose isaias cabrera  wrote:

> It would be nice to know also if you have any major idea(s) for enhancements 
> for sqlite3 in 2014.

You might consider SQLite4 to be major ideas for SQLite3



though as the page says, the two will coexist since there is no backward 
compatibility and the billion or so installations of SQLite3 aren't going to go 
away any time soon.

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

Re: [sqlite] "Common Table Expression"

[big stone]

Hello "CTE in SQLite" fans,

To get CTE in SQLite, I guess we must answer by the example the fears
expressed by Simon and Rsmith.

I propose the following method :
  - unproven-demand :
==> publish on this mailing list external "CTE for SQLite"
implementations,
==> if there is demand :
. they should become popular and improve quickly,
. the number of implementations should grow.

  - code size + performance increase fear :
==>  from the external implementations :
. develop knowledge of the algorithm to implement in SQLite,
. implement it in a true SQLite 'fossil' branch, with those two
goals in mind.

Here is my first implementation in Python (very rough and unclean, please
apologize) :

http://nbviewer.ipython.org/github/stonebig/baresql/blob/master/examples/baresql_with_cte_code_included.ipynb

I encourage other "CTE in SQlite" fans to :
 - create and publish theirs, from this first example,
 - provide links to other existing ones,
 - then to start 'CTE' SQlite patches in Fossil.


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

Re: [sqlite] "Common Table Expression"

[James K. Lowden]

On Thu, 26 Dec 2013 20:23:33 +0100
big stone  wrote:

> Indeed, '1' CTE can be replaced by the creation of 'N' temporary
> views (or tables), and their deletion after the CTE request.
> 
> CTE is :
> - a syntaxic simplification :
>. the SQL creator don't have to care about those intermediate
> views, . these intermediate views don't grow and multiply on your
> database schema.

Let's enumerate the uses of a CTE:

1.  Used once, instead of a subquery
2.  Used multiple times, instead of a view or repeated subquery
3.  Used in a recursive query 

Of these, only #3 gains any benefit from a CTE.  

Unless the (CTE) query is used more than once, it has no advantage over
a subquery.  

Case #2 is rare, and a view serves as well.  Only a small fraction of
queries use a subquery more than once.  The small number of such views
will not materially affect the schema namespace.  

Recursive queries are a unique feature of CTEs.  They are not supported
in SQLite.  If and when they are, CTEs will be required.  

I for one would like to see recursive updates supported, but I
recognize it's somewhat specialized behavior and non-trivial to
implement.  Support for atomic updates is more important.  

--jkl


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

Re: [sqlite] Using SQLite by Jay Kreibich (2010 paperback edition)

[Jay Kreibich]

Yes, of course.  Sorry.  It would seem I shouldn't answer email that early in 
the morning on a day off.

 -j

On Dec 31, 2013, at 7:48 AM, Stephan Beal  wrote:

> On Tue, Dec 31, 2013 at 2:33 PM, Jay Kreibich  wrote:
> 
>> Using SQLite covers up to the very last 2.6 version of SQLite.   The book
>> went to press about the same time that SQLite 2.7.0 came out.
> 
> 
> Certainly you intended 3.x instead of 2.x?
> 
> To quote the preface:
> 
> "The first edition of this book coves SQLite version 3.6.23.1. As this goes
> to press, work
> on SQLite version 3.7 is being finalized."
> 
> -- 
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
> those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

--  
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson




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

Re: [sqlite] SQLite 2013 retrospective

[Jan Nijtmans]

2013/12/31 Richard Hipp :
> Here is a quick summary of the changes and enhancements to SQLite during
> 2013.  The comparison is between trunk versions,
> http://www.sqlite.org/src/info/a611c75 versus
> http://www.sqlite.org/src/info/cc72c5aec7
...
> Major new features added in 2013 include:
>
>1.  Memory-mapped I/O:  http://www.sqlite.org/mmap.html
>2.  The next-generation query planner:
> http://www.sqlite.org/queryplanner-ng.html
>3.  Partial indices: http://www.sqlite.org/partialindex.html
>4.  WITHOUT ROWID tables: http://www.sqlite.org/withoutrowid.html

Also worth to mention:

New VFS's:
   1.  win32-longpath
   2.  unix-cygwin

(Ok. Ok, the latter is not in the officlal SQLite sources
yet, but it's available as official Cygwin package and
I hope to be able to contribute this in 2014.)

> Happy New Year to all.

Seconded:   Happy New Year to all.

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

[sqlite] SQLite 2013 retrospective

[Richard Hipp]

Here is a quick summary of the changes and enhancements to SQLite during
2013.  The comparison is between trunk versions,
http://www.sqlite.org/src/info/a611c75 versus
http://www.sqlite.org/src/info/cc72c5aec7

The amalgamation source file grew in side from 137619 lines to 145010
lines, or 4868834 bytes to 5125216 bytes.  Excluding comments the size grew
from 82156 lines to 87537 lines.  That's between 5% to 7% growth in source
code, depending on how you measure.  Approximately one source code line out
of every five changed during 2013 for a 20% code churn.

The compiled binary (gcc 4.8.1 with -Os on x64) grew from 412365 to 433963
bytes, or about 5%.

Running a typical mix of SQL statements (as implemented by the
http://www.sqlite.org/src/artifact/7130d2cb?ln test program), SQLite uses
15.5% fewer CPU operations to do the same task as it did one year ago, as
measured by valgrind.  Real-time performance is about 12% faster according
to that same benchmark running on Ubuntu 13.10, x64.

There were 737 trunk check-ins during 2013, or about two check-ins per
day.  There were an additional 924 check-ins on branches, for a total of
1661 check-ins.  416 files were changed in some way or another.  24216
lines were inserted and 12744 lines deleted, according to diffstat.  The
above is for the main source tree only.  There are many, many other
enhancements to the documentation and test suites.

The following releases occurred during 2013:

2013-01-09:   3.7.15.2
2013-03-18:   3.7.16
2013-03-29:   3.7.16.1
2013-04-12:   3.7.16.2
2013-05-20:   3.7.17
2013-08-26:   3.8.0
2013-08-29:   3.8.0.1
2013-09-03:   3.8.0.2
2013-10-17:   3.8.1
2013-12-06:   3.8.2

Major new features added in 2013 include:

   1.  Memory-mapped I/O:  http://www.sqlite.org/mmap.html
   2.  The next-generation query planner:
http://www.sqlite.org/queryplanner-ng.html
   3.  Partial indices: http://www.sqlite.org/partialindex.html
   4.  WITHOUT ROWID tables: http://www.sqlite.org/withoutrowid.html

New PRAGMAs:

   1.  PRAGMA foreign_key_check;
   2.  PRAGMA application_id;
   3.  PRAGMA defer_foreign_keys;
   4.  PRAGMA cache_spill;
   5.  PRAGMA query_only;
   6.  PRAGMA soft_heap_limit;

New SQL functions:

   1.  printf()  -- to appear in version 3.8.3
   2.  unlikely()
   3.  likelihood()
   4.  unicode()
   5.  char()

New C-language APIs:

   1.  sqlite3_cancel_auto_extension()
   2.  sqlite3_strglob()

New tested and supported extensions added to the source tree:

   1.  The approximate_match virtual table:
www.sqlite.org/src/artifact/678056a
   2.  The transitive_closure virtual table:
www.sqlite.org/src/artifact/6360243
   3.  The ieee754() SQL function:
http://www.sqlite.org/src/artifact/b03621672
   4.  The next_char() SQL function: www.sqlite.org/src/artifact/35c8b8ba
   5.  The percentile() SQL function: www.sqlite.org/src/artifact/bcbee3c
   6.  The regexp() SQL function:
http://www.sqlite.org/src/artifact/af92cdaa5
   7.  The rot13() SQL function: http://www.sqlite.org/src/artifact/1ac6f95f
   8.  The spellfix1 virtual table: http://www.sqlite.org/spellfix1.html
   9.  The tointeger() and toreal() SQL functions:
www.sqlite.org/src/artifact/4a167594
   10. The wholenumber virtual table: www.sqlite.org/src/artifact/784b1254

In addition to the above, there are countless new test cases and minor
feature and performance enhancements.

Our goal is to maintain this aggressive pace of innovation and enhancement
in SQLite throughout 2014 and beyond.

Happy New Year to all.
-- 
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] SQLite3 temporary filename collision in worker processes

[Török Edwin]

Hi,

I am using SQLite 3.8.1 compiled from the amalgamation on Debian GNU/Linux 
"wheezy", amd64.
I have set a logging callback with sqlite3_config(SQLITE_CONFIG_LOG, qlog, 
NULL), and I see the following error sometimes:

Query "INSERT INTO topush (block, size, node, expires_at) VALUES (:b, :s, :n, 
:e)" failed: (code 0xf: locking protocol) locking protocol
[...]
SQLite error e: cannot open file at line 28873 of [c78be6d786]
SQLite error e: os_unix.c:28873: (17) open(/var/tmp/etilqs_RxmAVCiy0tddswN) - 
File exists
SQLite error e: statement aborts at 25: [UPDATE hashfs SET value = value + 1 
WHERE key = 'next_blockno'] 
Query "UPDATE hashfs SET value = value + 1 WHERE key = 'next_blockno'" failed: 
(code 0xe: unable to open database file) unable to open database file

The first error seems to be a known issue: 
http://www.mail-archive.com/sqlite-users@sqlite.org/msg57092.html

The second error can be triggered in a sequence of events that can be roughly 
described as:
 * create an sqlite DB in WAL mode, with synchronous=NORMAL
 * open an sqlite DB as soon the application starts, prepare some statements, 
run some selects on the DB, then close it
 * fork worker processes
 * open the same DB in each sub-process
 * run queries on the DB in parallel

A closer look at sqlite3.c reveals that the temporary filename generator uses 
sqlite3_randomness which is seeded once, globally, by the main process in this 
case.
The forked children will share the PRNG's state, possibly generating the same 
sequence in each child. 
This alone is not enough to trigger the bug though, because sqlite3 will 
generate a new filename if osAccess() says the file exists already.
However there is a race condition between osAccess() and open(): two processes 
might generate same name, check that the file doesn't exist, and both of them 
will try
to create the file. One of them will fail due to O_EXCL.

For now I am using a workaround, trying to reset the PRNG state in each child:
  sqlite3_test_control(SQLITE_TESTCTRL_PRNG_RESET);

What is the next step? Should I open a ticket on 
https://www.sqlite.org/src/tktnew, and do I need to attach a testcase, or the 
above description enough?

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

Re: [sqlite] Using SQLite by Jay Kreibich (2010 paperback edition)

[Bob Cochran]

Thank you, Jay!

Bob

On 12/31/13, 8:33 AM, Jay Kreibich wrote:

Using SQLite covers up to the very last 2.6 version of SQLite.   The book went 
to press about the same time that SQLite 2.7.0 came out.  As such, the book 
does not cover WAL or any of the more resent features.  The book only covers 
the C/C++ APIs, since everything else is based off those.

The primary audience for Using SQLite is experienced developers that have never 
had to use a relational database before.  Much of the book does cover 
introductory information.  As others have said, if you already have database 
experience and all you need to understand is the basic SQLite-specific APIs, 
I'm not sure the full book is going to offer much.  On the other hand, if you 
have a more advanced project and need to understand SQLite's locking model in 
more detail, or understand how virtual tables (or one of the other more 
advanced features) work, then the book still has something to offer.


Amazon.com offers their "Look Inside!" preview of Using SQLite.  That should 
give you a pretty good idea of what information is covered.  You can also buy the PDF 
from oreilly.com for much less then the cost of a printed copy.


Let me know if there are any other questions.

  -j




On Dec 30, 2013, at 8:48 PM, Bob Cochran  wrote:


I'm working on a tough project that requires me to use Node.js and SQLite. I'm 
building a database with SQLite version 3.8.2.

My question is, would purchasing the book "Using Sqlite" by Jay Kreibich still 
be mostly pertinent to SQLite 3.8.x features? Would it be helpful in the context how 
Node.js is used? I realize that Mr. Kreibich was writing the book and bringing it to 
publication just as Node.js was being invented in 2009.

Does anyone know if this book will be updated?

Or is there a different book title I should look at?

Right now, my major difficulties seem to be in my own grasp of asynchronous programming. 
It is my own deficient grasp of the logic problems that are biting me; I need to 
"get it" and understand Node style programming. An example is: I need to insert 
two rows to an SQLite table, but because I'm using non-blocking interface software to do 
the actual inserts, and I'm probably using that software incorrectly, only one insert is 
being done.

Thanks

Bob Cochran
Greenbelt, Maryland, USA

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

--
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing it to 
the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson




___
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] Using SQLite by Jay Kreibich (2010 paperback edition)

[Stephan Beal]

On Tue, Dec 31, 2013 at 2:33 PM, Jay Kreibich  wrote:

> Using SQLite covers up to the very last 2.6 version of SQLite.   The book
> went to press about the same time that SQLite 2.7.0 came out.


Certainly you intended 3.x instead of 2.x?

To quote the preface:

"The first edition of this book coves SQLite version 3.6.23.1. As this goes
to press, work
on SQLite version 3.7 is being finalized."

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite by Jay Kreibich (2010 paperback edition)

[Jay Kreibich]

Using SQLite covers up to the very last 2.6 version of SQLite.   The book went 
to press about the same time that SQLite 2.7.0 came out.  As such, the book 
does not cover WAL or any of the more resent features.  The book only covers 
the C/C++ APIs, since everything else is based off those.

The primary audience for Using SQLite is experienced developers that have never 
had to use a relational database before.  Much of the book does cover 
introductory information.  As others have said, if you already have database 
experience and all you need to understand is the basic SQLite-specific APIs, 
I'm not sure the full book is going to offer much.  On the other hand, if you 
have a more advanced project and need to understand SQLite's locking model in 
more detail, or understand how virtual tables (or one of the other more 
advanced features) work, then the book still has something to offer.


Amazon.com offers their "Look Inside!" preview of Using SQLite.  That should 
give you a pretty good idea of what information is covered.  You can also buy 
the PDF from oreilly.com for much less then the cost of a printed copy.


Let me know if there are any other questions.

 -j




On Dec 30, 2013, at 8:48 PM, Bob Cochran  wrote:

> I'm working on a tough project that requires me to use Node.js and SQLite. 
> I'm building a database with SQLite version 3.8.2.
> 
> My question is, would purchasing the book "Using Sqlite" by Jay Kreibich 
> still be mostly pertinent to SQLite 3.8.x features? Would it be helpful in 
> the context how Node.js is used? I realize that Mr. Kreibich was writing the 
> book and bringing it to publication just as Node.js was being invented in 
> 2009.
> 
> Does anyone know if this book will be updated?
> 
> Or is there a different book title I should look at?
> 
> Right now, my major difficulties seem to be in my own grasp of asynchronous 
> programming. It is my own deficient grasp of the logic problems that are 
> biting me; I need to "get it" and understand Node style programming. An 
> example is: I need to insert two rows to an SQLite table, but because I'm 
> using non-blocking interface software to do the actual inserts, and I'm 
> probably using that software incorrectly, only one insert is being done.
> 
> Thanks
> 
> Bob Cochran
> Greenbelt, Maryland, USA
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

--  
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson




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

Re: [sqlite] Using SQLite by Jay Kreibich (2010 paperback edition)

[Bob Cochran]

Thank you, I will look more closely at the node-sqlite3 documentation.

Bob

On 12/31/13, 12:25 AM, Navaneeth K N wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA512

Hello,

On 12/31/13 8:18 AM, Bob Cochran wrote:

I'm working on a tough project that requires me to use Node.js and
SQLite. I'm building a database with SQLite version 3.8.2.

My question is, would purchasing the book "Using Sqlite" by Jay Kreibich
still be mostly pertinent to SQLite 3.8.x features? Would it be helpful
in the context how Node.js is used? I realize that Mr. Kreibich was
writing the book and bringing it to publication just as Node.js was
being invented in 2009.

Does anyone know if this book will be updated?

Or is there a different book title I should look at?

- From my personal experience, you don't need a book to learn/use SQLite.
It is very well documented and pretty easy to get started. If you are in
doubt at anytime, get on the interactive SQLIte console and try out
commands directly on the database.


Right now, my major difficulties seem to be in my own grasp of
asynchronous programming. It is my own deficient grasp of the logic
problems that are biting me; I need to "get it" and understand Node
style programming. An example is: I need to insert two rows to an SQLite
table, but because I'm using non-blocking interface software to do the
actual inserts, and I'm probably using that software incorrectly, only
one insert is being done.

You need to do one insert, then in the success callback of the insert,
do the second insert.

If you use `node-sqlite3`[1], then it allows you to serialize the query
execution in which case it is simpler.

[1]: https://github.com/mapbox/node-sqlite3


Thanks

Bob Cochran
Greenbelt, Maryland, USA

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

Navaneeth
-BEGIN PGP SIGNATURE-
Version: GnuPG/MacGPG2 v2.0.22 (Darwin)
Comment: GPGTools - https://gpgtools.org

iQEcBAEBCgAGBQJSwlUyAAoJEHFACYSL7h6kUCwH/0tMAKjgGldSPeXWKyMT3j8t
X8br4nLyyxsEUa0xASm6S7jiPrwQ8D1IKEjSu1cbX4AAimZSzeMXnIlwyuKVxF7Q
1NLNknzei/LKM1m+jwRYtC6Pencm4o2Jnrc5kcKBrXAh/M4w6r+7Z1dpBN7LK/wP
RZs42203DoV2V4/0FxoBP9OxgdP+AKSYRf4UrOqJM5XZiPVaoi9J43MqRyldwUaU
0n9cTlGzFqgKMxyQgq4cqoQKM9BztvAxkq5jU72FJHXg/3N6tJennL/ACrf2qkff
G5rSdb0SDOUAvajy8cybRj90F2e3E+3C6uvrph7UdD/86A4PitzKnezFw2y72L4=
=gKGL
-END PGP SIGNATURE-
___
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] Web application with SQLite

[Eduardo]

>   Understood. Thanks

Sorry if I'm late.

There's no difference between developing a formal application and a Web
application from the Sqlite point of view. 

You can check mongoose webserver. It uses LUA for scripting web pages
(similar to PHP but with LUA) and Sqlite for db access from LUA.

You can use scgi lib from http://www.xamuel.com/scgilib/ and use Sqlite
if you are developing a webservice.

HTH


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

Re: [sqlite] Error 11 after doing a lot of simple insert/update operations!

[Woody Wu]

Hi, Simon

I upload the source code onto my dropbox:
https://www.dropbox.com/s/9shhshi0wn3e717/downloadfile.c  Please have a
look at it.

The same test program run without a problem on my pc Linux after complied
natively.  But I think I should not dout my cross-compiler, which is
CodeBench ARM eabi compiler. With the same complier and the toolchain, I
have been buit a whole target ARM system including kernel, 1000 open source
applications, even including a tiny X window.


On Tuesday, 31 December 2013, Simon Slavin wrote:

>
> On 31 Dec 2013, at 8:41am, Woody Wu >
> wrote:
>
> > Attached is the test program writting in C.
>
> Sorry, but attachments don't work here.  If your program is short, please
> post it as text.  If not, please put it on a web site somewhere.
>
> > Anyway, all above errors looks so strange. And, these operations I
> talking
> > about are so basic and my real application (another bigger one) really
> > depends on these.
>
> You should not be able to make SQLite corrupt its database that easily.
>
> > Pleaes be kindly to check my test program.
>
> Can you run your program on the computer you used to send that email
> message and tell us whether it had the same problem ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Life is the only flaw in an otherwise perfect nonexistence
-- Schopenhauer

narke
public key at http://subkeys.pgp.net:11371 (narkewo...@gmail.com)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error 11 after doing a lot of simple insert/update operations!

[Simon Slavin]

On 31 Dec 2013, at 8:41am, Woody Wu  wrote:

> Attached is the test program writting in C.

Sorry, but attachments don't work here.  If your program is short, please post 
it as text.  If not, please put it on a web site somewhere.

> Anyway, all above errors looks so strange. And, these operations I talking
> about are so basic and my real application (another bigger one) really
> depends on these.

You should not be able to make SQLite corrupt its database that easily.

> Pleaes be kindly to check my test program.

Can you run your program on the computer you used to send that email message 
and tell us whether it had the same problem ?

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

[sqlite] Error 11 after doing a lot of simple insert/update operations!

[Woody Wu]

Hi,

I found sqlite (3.8.2 as well as 3.7.17) cannot stand reliably with some
simple insert/update operations on a single table with primary
key.  I found this problem on ARM platform with varities of filesystems
including UBIFS, Yafss on NAND, and Ext4 on SDCARD.

Attached is the test program writting in C.

What I did is to create a simple table with threee integer columns, the
first two columns are defined as primary key. The I continously update
the table with three different strategies:

strategy 0:  always insert and detect SQL_CONSTRAINT error, if
detected, do a update instead;

strategy 1: do a "select count (*) where ..." firstly, if the records
with the same key does not exists (count = 0) then do insertion,
otherwise do update.

strategy 2: using sqlite specific SQL "insert or replace".

In all above strategies, I orgnized sql operations in an exclusive
transation. I committed a transation after 500 insert/update operations
had been executed before start another transaction. If any abnormal error
detected in insert/update operations, I rollback the running
transation, print an error and continue.

If I run my test enough long, e.g., 1+ transactions, it always almost
show different problems, but all of these problems seem is
showing that the index structure of the database got corrupted:

a) The test program run successfully, but after it ended and I do a
integrity_check from sqlite3 shell, there are a lot of "row #nnn missed
from ***_autoindex_***" errors.

b) Program received sqlite error code 11 from invocation of sqlite3_step().
Most these kind of error happened after executed an "update" statement, but
it also observed that the same error can be received even after executed
a "select" statement.

c) In strategy 0, after executed a "select" statement, it returns a count
value of zero, means the records does not exist.  But immediatly after the
next "insert" was issued, it reported an sqlite err 19 (CONSTRAINT).

I have not yet speed too much time in testing strategy 2 (insert or
replace),
it has not yet shown an error.

Anyway, all above errors looks so strange. And, these operations I talking
about are so basic and my real application (another bigger one) really
depends on these.

Pleaes be kindly to check my test program.  I hope these issues were caused
by bugs in my ode, then I can fixed.  But if my code is okay, it's really
a big probleme. If, otherwise, someone can confirm that this is a sqlite
bug,
can I have any work-around?

Thanks in advance.
-woody


-- 
Life is the only flaw in an otherwise perfect nonexistence
-- Schopenhauer
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users