[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Darren Duncan]

Stephen,

What you are arguing for (no shared libraries) is bad old days where one had to 
recompile their programming language to add support for a DBMS, rather than the 
DBMS support being a separately installable library that one could choose to 
install or not or upgrade semi-independently or not, or choose to use an 
alternative or not.

Sure, SQLite is public domain, but why should every language bundle it into 
their core just because?  There are lots of other useful libraries one could 
make the same argument for.  Bundling it can make sense if the language core 
itself depends on SQLite or practically all of its users would use it, but 
that's not usually the case.

I should also point out that the standard Perl interface for SQLite, the 
DBD::SQLite module, bundles the SQLite source with it, so installing that Perl 
library gives you SQLite itself, there are no DLLs or dependence on some system 
SQLite library, but Perl itself doesn't have this built-in nor should it.

In the Perl 4 days you had to recompile Perl to make a version that can talk to 
a DBMS, eg "Oraperl", but thankfully with Perl 5 (1994 or so) we did away with 
that.

-- Darren Duncan

On 2016-01-07 5:47 PM, Stephen Chrzanowski wrote:
> I personally wish the reverse.  I wish that these interpreted language
> engines would incorporate the SQLite code directly into their own existence
> to avoid having to write wrappers to begin with, except for those wrappers
> where their method name is "DatabaseOpen" and I prefer "OpenDatabase".
>
> SQLite has been around for years, and "R", PHP, Java, Perl, and all these
> other interpreted new and old style languages have never bothered to
> incorporate this public domain database engine within itself.  It isn't
> like the maintainers of these languages don't know it doesn't exist, and if
> they didn't, then my god they gotta get out from under that rock.  Most web
> browsers use SQLite for crying out loud.
>
> For a few years, I've considered taking the entire amalgamation and porting
> it to Pascal (Delphi/FPC) so I have exactly zero reliance on DLLs.  No
> worries about OBJ files, no worries about dependencies, I just include a
> unit and my app is now database aware.  I know 386 assembly, and I can
> always read up on other specifications if I needed to.  My problem is that
> gaming gets in the way.
>
> My 2016 wish list for SQLite is that all developers who write for, or use
> directly or indirectly, any database engine out on the market has a safe
> and happy 2016 and beyond.

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Darren Duncan]

Perhaps we misunderstand each other here.

I interpreted your request as if current systems' error outputs at execute time 
were printing out the problematic SQL statement with placeholder names as 
originally prepared, and you wanted the error outputs to have the placeholders 
substituted with literals for the values passed to them at execute time 
interpolated into them.

And so I was saying that continuing to print out the SQL with placeholders was 
correct, and that one can just list the bound values separately / afterwards 
rather than having to rewrite the SQL to interpolate those values.

If your problem is that the output simply says an error occurred and doesn't 
print out the SQL, then fair enough, I misunderstood you.

If my assessment of what you said matches what you intended, then your analogy 
with C is flawed / apples and oranges with what I was talking about.

-- Darren Duncan

On 2016-01-07 5:55 PM, Warren Young wrote:
> On Jan 7, 2016, at 6:04 PM, Darren Duncan  wrote:
>>
>> On 2016-01-07 4:55 PM, Warren Young wrote:
>>> 2. There is no ?preview? mechanism.
>>
>> The current method of binding is correct.  All we really need is that the 
>> debug logging layer include both the SQL of the prepared statement AND a 
>> list of the bound values when the execute failed
>
> By that logic, it would be okay to design a C compiler that emitted only line 
> numbers as error numbers, and gave those line numbers as cpp(1) output line 
> numbers, not source input line numbers.
>
> That is, if stdio.h is 5kSLOC and you make an error on line 5 of your hello 
> world program, it should complain, ?hello.c:5005: error?.
>
> After all, the programmer has all the information necessary to subtract out 
> the #included files? offsets, and then go look at line 5 in the program to 
> determine what went wrong.
>
> SQLite error handling should improve the same way our C and C++ compilers 
> have.
>
> Given: include (missing ?#"!)
>
> Ancient Unix V7 cc says: hello.c:1: Expression syntax.  Yes, very helpful.  
> (Not!)
>
> pcc on the same box spits out about half a dozen errors for that line, none 
> of which tell you what is wrong.
>
> gcc 4 says:
>
> hello.c:1: error: expected ?=?, ?,?, ?;?, ?asm? or ?__attribute__? before 
> ?
> It?s not as pointlessly noisy as pcc, but it?s still not terribly useful.
>
> clang says:
>
>hello.c:1:1: error: unknown type name 'include'
>include 
>^
>
> The arrow points you right at the error.
>
> Wouldn?t it be nice if SQLite were more like clang in this regard?

[sqlite] How to make sqlite3_release_memory produce a result?

[Bart Smissaert]

Thanks for that. Will look into this. Looks you might be onto something
there.

RBS

On Thu, Jan 7, 2016 at 11:04 PM, Scott Hess  wrote:

> Hmm.  I see that sqlite3PcacheReleaseMemory() is a no-op
> if sqlite3GlobalConfig.nPage is non-zero, and that happens
> when SQLITE_DEFAULT_PCACHE_INITSZ is not zero.  If I compile with
> -DSQLITE_DEFAULT_PCACHE_INITSZ=0 , then I see the expected pages freed.
> AFAICT, it doesn't matter if you make use of more than
> SQLITE_DEFAULT_PCACHE_INITSZ pages, it still doesn't free the excess.
>
> Looks like that was added in 3.8.11 back in July.
>
> -scott
>
>
> On Thu, Jan 7, 2016 at 5:44 AM, Bart Smissaert 
> wrote:
>
> > These are the compile flags of my sqlite3.dll:
> >
> > compile_option
> > 
> > DEBUG
> > ENABLE_COLUMN_METADATA
> > ENABLE_MEMORY_MANAGEMENT
> > MEMDEBUG
> > OMIT_LOOKASIDE
> > THREADSAFE=0
> >
> > And these are the set pragma's:
> >
> > PRAGMA cache_size 32768
> > PRAGMA default_cache_size 32768
> > PRAGMA page_count 712711
> > PRAGMA max_page_count 1073741823
> > PRAGMA page_size 1024
> > PRAGMA journal_size_limit -1
> > PRAGMA locking_mode normal
> > PRAGMA automatic_index 1
> > PRAGMA encoding UTF-8
> > PRAGMA ignore_check_constraints 0
> > PRAGMA read_uncommitted 0
> > PRAGMA recursive_triggers 0
> > PRAGMA reverse_unordered_selects 0
> > PRAGMA secure_delete 0
> > PRAGMA wal_autocheckpoint 1000
> > PRAGMA writable_schema 0
> > PRAGMA journal_mode off
> > PRAGMA auto_vacuum NONE
> > PRAGMA synchronous OFF
> > PRAGMA temp_store DEFAULT
> >
> >
> > RBS
> >
> > On Thu, Jan 7, 2016 at 1:39 PM, Bart Smissaert  >
> > wrote:
> >
> > > > So you should
> > > see results if you start a transaction, do a few update statements,
> > commit
> > > the transaction, the call sqlite3_release_memory(db).
> > >
> > > I tried this with a large table, first with no transaction then with a
> > > transaction
> > > and tried the sqlite3_release_memory directly after the
> sqlite3_finalize,
> > > but in both cases result still zero:
> > >
> > > sqlite3_memory_used: 37190712
> > > sqlite3_release_memory:0
> > > sqlite3_memory_used: 37190712
> > >
> > > This was a single update, where I don't think a transaction is helpful.
> > > Still no idea how I can make sqlite3_release_memory produce non-zero.
> > >
> > > RBS
> > >
> > >
> > >
> > > On Thu, Jan 7, 2016 at 6:05 AM, Scott Hess  wrote:
> > >
> > >> On Wed, Jan 6, 2016 at 3:03 PM, Bart Smissaert <
> > bart.smissaert at gmail.com>
> > >> wrote:
> > >>
> > >> > Have compiled sqlite3.dll (latest) compiled with
> > >> ENABLE_MEMORY_MANAGEMENT,
> > >> > but sofar
> > >> > not been able yet to make sqlite3_release_memory produce anything
> else
> > >> than
> > >> > 0.
> > >> > What would be the simplest way to make this happen?
> > >> > I don't want to do this with C coding, so it should be some SQL
> > >> scenario or
> > >> > to do with simple
> > >> > SQLite functions such as sqlite3_step, _prepare, -finalize etc.
> > >>
> > >>
> > >> Last time I was paying attention to this, I believe that I found that
> > the
> > >> biggest effect was to free unpinned pages from the page cache.  So it
> > >> might
> > >> not free pages if you're in a transaction, for instance.  I would
> guess
> > >> that if you had memory-mapped mode on and are doing only reads, there
> > >> would
> > >> be no pages to free (mmap pages aren't in the page cache).  So you
> > should
> > >> see results if you start a transaction, do a few update statements,
> > commit
> > >> the transaction, the call sqlite3_release_memory(db).
> > >>
> > >> -scott
> > >> ___
> > >> sqlite-users mailing list
> > >> sqlite-users at mailinglists.sqlite.org
> > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >>
> > >
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Some FTS5 guidance

[Stadin, Benjamin]

One such algorithm would be a (generalized) Ukkonnen suffix tree 
(https://en.m.wikipedia.org/wiki/Ukkonen%27s_algorithm). 
It allows you to search efficiently for substrings. 
It would be possible to do some match weigthing based on match distance within 
words. But a general solution for a database is probably not trivial to 
implement.

Ben

Von meinem iPad gesendet

> Am 07.01.2016 um 21:46 schrieb Matthias-Christian Ott :
> 
>> On 2016-01-07 19:31, Mario M. Westphal wrote:
>> I hence wonder if this problem has been tackled already and if there is a
>> "standard" solution.
> 
> If I understand you correctly, it seems that you are looking for a
> compound splitting or decompounding algorithm. Unfortunately there is
> not a "standard solution" for this. There are many languages in the
> world and for some usable compound splitting algorithms exist. There are
> also attempts to create statistical universal algorithms.
> 
> As you said, for English a simple sub-string search might suffice but
> for other languages it more complex. I assume that you speak German. If
> you have a document that contains the term "Verkehrsleitsystem" and your
> search query is "Verkehr leiten", it's reasonable to assume that the
> document is relevant to the search query. Unfortunately a sub-string
> search could not find the document. Other languages are even more
> difficult (a textbook on linguistics will explain this better than I can).
> 
> Even if you have such algorithm, it's not trivial to score the results
> and there are more aspects to consider to create a simple search
> algorithm. For example, in English you will also have to do some
> analysis of the phrase structure to identify open compounds.
> 
> Perhaps it helps to mention the languages you are interested in and the
> application you have in mind to evaluate whether the SQLite FTS5 could
> meet your requirements.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Some FTS5 guidance

[Matthias-Christian Ott]

On 2016-01-07 19:31, Mario M. Westphal wrote:
> I hence wonder if this problem has been tackled already and if there is a
> "standard" solution. 

If I understand you correctly, it seems that you are looking for a
compound splitting or decompounding algorithm. Unfortunately there is
not a "standard solution" for this. There are many languages in the
world and for some usable compound splitting algorithms exist. There are
also attempts to create statistical universal algorithms.

As you said, for English a simple sub-string search might suffice but
for other languages it more complex. I assume that you speak German. If
you have a document that contains the term "Verkehrsleitsystem" and your
search query is "Verkehr leiten", it's reasonable to assume that the
document is relevant to the search query. Unfortunately a sub-string
search could not find the document. Other languages are even more
difficult (a textbook on linguistics will explain this better than I can).

Even if you have such algorithm, it's not trivial to score the results
and there are more aspects to consider to create a simple search
algorithm. For example, in English you will also have to do some
analysis of the phrase structure to identify open compounds.

Perhaps it helps to mention the languages you are interested in and the
application you have in mind to evaluate whether the SQLite FTS5 could
meet your requirements.

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Stephen Chrzanowski]

I personally wish the reverse.  I wish that these interpreted language
engines would incorporate the SQLite code directly into their own existence
to avoid having to write wrappers to begin with, except for those wrappers
where their method name is "DatabaseOpen" and I prefer "OpenDatabase".

SQLite has been around for years, and "R", PHP, Java, Perl, and all these
other interpreted new and old style languages have never bothered to
incorporate this public domain database engine within itself.  It isn't
like the maintainers of these languages don't know it doesn't exist, and if
they didn't, then my god they gotta get out from under that rock.  Most web
browsers use SQLite for crying out loud.

For a few years, I've considered taking the entire amalgamation and porting
it to Pascal (Delphi/FPC) so I have exactly zero reliance on DLLs.  No
worries about OBJ files, no worries about dependencies, I just include a
unit and my app is now database aware.  I know 386 assembly, and I can
always read up on other specifications if I needed to.  My problem is that
gaming gets in the way.

My 2016 wish list for SQLite is that all developers who write for, or use
directly or indirectly, any database engine out on the market has a safe
and happy 2016 and beyond.

[sqlite] Some FTS5 guidance

[Mario M. Westphal]

Hello,



I recently looked into FTS 5. 

The documentation is clear and I was able to get it running with a small
test database quickly. And the response times are awesome :-)



My question: 



At least as I understand it at this point, FTS can only do prefix queries.



If my database contains the words



moon

moonlight

moonshine

shine

sunshine



A FTS query like "moon*" will find all three terms starting with "moon" -
very fast.



But there is no way to find "moonshine" or "sunshine" by running a query for
"shine" or "shine*" ?



Currently I search using LIKE and there such 'contains' queries are easy. My
users of course don't understand all this and want to find all words
containing shine, wherever the term appears in the word.



The only idea I had so far was to write my own tokenizer and to store each
word with every possible 'sub-word':



When "moonshine" is added to FTS, it is split into multiple words:



moonshine
oonshine
onshine
nshine
shine
. 



(maybe I limit this to a minimum of 2 or 3 characters).



This of course produces a log of extra entries in FTS and may impact
performance and database size. 

I hence wonder if this problem has been tackled already and if there is a
"standard" solution. 

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Jim Callahan]

At the command line interface (CLI) in SQLite
(and most SQL implementations) is an interpreted
set at a time language with implicit loops.

Efficient low level languages (such as C) process data
a record at a time and the existing API is appropriate
for them.

Object Oriented Interactive Languages (OOIL ?) can receive a Table, a View
or a Query all at once as a data set.
I would count among the OOIL languages: R, Python, Julia Scala,
MatLab/Octave and APL. In a slightly different category would be Java and
C# which are object oriented and arguably interpreted, but are not intended
to be used interactively at a command line with a Read-Evaluate-Print-Loop
(REPL).

The intent of the higher level API is to improve the reliability of the
interfaces. The existing SQLite APIs are correct, but hard to use in the
sense that creating an interface from an OOIL language is more involved
than just "wrapping" one by one a set of functions. What I am proposing is
a second set of APIs that when trivially wrapped for use in an OOIL
language would result in a function that makes sense to an OOIL programmer
and interprets the SQL statements in a manner consistent with the SQLite
CLI (perhaps it could even borrow code from the CLI).

I believe R has remarkably good interface packages for SQLite, but that is
not necessarily the norm across the other OOIL languages.

I am assuming that the higher level API would be hard to use in C because
its up to the programmer to write the low level code while maintaining a
complex abstraction in their head (because C is better suited for creating
abstractions than using them). Header files (.h) would help some but they
would inflate the size of the code and still be hard for the C programmer
to keep track of. So, that's why I see the need for a second higher API
that might be written in C, but would certainly not be used in C!

I am undecided as to whether the higher level API would be useful in Java
or C#.  Java and C# programmers might not be used to implicit loops and
find them not worth the trouble;
whereas R, Python or Julia programer would expect to get an entire table,
view or query all at once.

The higher level API would have to be optional, since it would not be
desirable for a programmer or organization that needs SQLite to run with
the smallest possible footprint on a phone, tablet or Internet of things
(IOT) device.

Just a wishlist idea. No rush for me because I am happy in R and will
probably be moving from SQLite to client server SQL database before I move
from R to Python, Julia or Java.

Jim Callahan
Orlando, FL



This
email has been sent from a virus-free computer protected by Avast.
www.avast.com

<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

[sqlite] Some FTS5 guidance

[Graham Holden]

I've never used FTS, just throwing an off-the-wall idea out: instead of 
tokenising partial words, could you tokenise/store the reverse of each word 
(possibly in a separate place if that can be done):

enihsnoom
enihs
enihsnus

Then search for "enihs" as well as "shine". If you can't separate the forward 
and reversed versions, you'd have to filter-out when "dog" matches "god".

Graham

Sent from Samsung Mobile

 Original message 
From: "Mario M. Westphal"  
Date: 07/01/2016  18:31  (GMT+00:00) 
To: sqlite-users at mailinglists.sqlite.org 
Subject: [sqlite] Some FTS5 guidance 

Hello,



I recently looked into FTS 5. 

The documentation is clear and I was able to get it running with a small
test database quickly. And the response times are awesome :-)



My question: 



At least as I understand it at this point, FTS can only do prefix queries.



If my database contains the words



moon

moonlight

moonshine

shine

sunshine



A FTS query like "moon*" will find all three terms starting with "moon" -
very fast.



But there is no way to find "moonshine" or "sunshine" by running a query for
"shine" or "shine*" ?



Currently I search using LIKE and there such 'contains' queries are easy. My
users of course don't understand all this and want to find all words
containing shine, wherever the term appears in the word.



The only idea I had so far was to write my own tokenizer and to store each
word with every possible 'sub-word':



When "moonshine" is added to FTS, it is split into multiple words:



moonshine
oonshine
onshine
nshine
shine
. 



(maybe I limit this to a minimum of 2 or 3 characters).



This of course produces a log of extra entries in FTS and may impact
performance and database size. 

I hence wonder if this problem has been tackled already and if there is a
"standard" solution. 

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

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Warren Young]

On Jan 7, 2016, at 6:04 PM, Darren Duncan  wrote:
> 
> On 2016-01-07 4:55 PM, Warren Young wrote:
>> 2. There is no ?preview? mechanism.
> 
> The current method of binding is correct.  All we really need is that the 
> debug logging layer include both the SQL of the prepared statement AND a list 
> of the bound values when the execute failed

By that logic, it would be okay to design a C compiler that emitted only line 
numbers as error numbers, and gave those line numbers as cpp(1) output line 
numbers, not source input line numbers.

That is, if stdio.h is 5kSLOC and you make an error on line 5 of your hello 
world program, it should complain, ?hello.c:5005: error?.

After all, the programmer has all the information necessary to subtract out the 
#included files? offsets, and then go look at line 5 in the program to 
determine what went wrong.

SQLite error handling should improve the same way our C and C++ compilers have.

Given: include (missing ?#"!)

Ancient Unix V7 cc says: hello.c:1: Expression syntax.  Yes, very helpful.  
(Not!)

pcc on the same box spits out about half a dozen errors for that line, none of 
which tell you what is wrong.

gcc 4 says:

   hello.c:1: error: expected ?=?, ?,?, ?;?, ?asm? or ?__attribute__? before 
?
  ^

The arrow points you right at the error.

Wouldn?t it be nice if SQLite were more like clang in this regard?

[sqlite] Question regarding use of REPLACE

[audio muze]

I'm trying to get some consistency in the contents of a field in a
table. To do so involves multiple updates using REPLACE.

Is it acceptable to make multiple calls to replace involving the same
field in a single update operation, like so:

UPDATE audio SET
composer = REPLACE( composer, " / ", "\\" ),
composer = REPLACE( composer, " , ", "\\" ),
composer = REPLACE( composer, ", ", "\\" ),
composer = REPLACE( composer, ",", "\\" ),
composer = REPLACE( composer, "\\Jr.\\", ", Jr.\\" ),
composer = REPLACE( composer, "\\Jr\\", ", Jr.\\" )
;

[sqlite] whish list for 2016

[James K. Lowden]

On Sun, 20 Dec 2015 19:12:39 +0100
Big Stone  wrote:

> Best whishes for 2016!

If it's not too late to join the party...

1.  create table T (t primary key);
update T set t = t+1;

for consecutive values of t.  

2.  Specific constraint references in error messages.  


3.  Correct math.  E.g.: 


$ sqlite3 db 'select typeof(1/0)'

typeof(1/0)
---
null   

should produce an error.  There are other examples, but none comes to
mind just now.  

I would like to see a strict mode, too.  I would also like to be able
to make "strictness" a property of the database, not the connection.
One way to do that would be to honor a special user-created table, say
"PRAGMAS", with name-value pairs that are automatically applied when
the database is opened.  

IMHO update isolation (#1) and mathematical correctness (#3) are
prerequisites for analytical functions.  SQLite can't be dependably
used for quantitative work if domain errors are silently ignored.  

--jkl

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Warren Young]

On Jan 7, 2016, at 5:22 PM, Jim Callahan  
wrote:
> 
> I believe R has remarkably good interface packages for SQLite

That?s the appropriate level: the high-level language's DB access layer should 
map the low-level C record-at-a-time API to an appropriate language-level 
abstraction.

R almost forces you to do this because of things like data.frame.  But, that?s 
no argument for other HLL DBMS API writers not to provide similar affordances.

I?ve been involved with two different C++ DBMS wrapper libraries, and both of 
them provide a way to get a std::vector<> as a result set instead of iterate 
over individual rows.  As with R?s SQLite wrapper, I felt it was my C++ code?s 
responsibility to do this repackaging, not the underlying C DBMS access API.

That?s not to say that the SQLite C API has no warts:

1. sqlite3_column_*() uses 0-based indices but sqlite3_bind_*() uses 1-based 
indices.  I can cope with either base, but please pick one!  (And make it the 
correct base for programming, 0.  (Yes, I know I just praised R above.  R?s use 
of 1-based arrays is WRONG.))

2. There is no ?preview? mechanism.  That is, you can?t bind some parameters to 
a prepared query string and then get the resulting SQL because SQLite 
substitutes the values into the query at a layer below the SQL parser.  This 
means that if you have an error in your SQL syntax or your parameters cause a 
constraint violation, your debug logging layer can only log the prepared query 
string, not the parameters that went into it, which makes it unnecessarily 
difficult to determine which code path caused you to get the error when looking 
at logs of a running system.

3. The query finalization code could be less picky.  If I prepare a new query 
without finalizing the previous one, I?d rather that SQLite didn?t punish me by 
throwing errors unless I put it into a ?lint? mode.  Just toss the 
half-finished prior query and move on, please.

4. There are several signs of backwards compatible extensions which make the 
API more complex than if it were designed with the features from the start.  
(e.g. _v2() APIs, the various ways to get error codes, etc.)  Hopefully those 
doing the SQLite4 effort will feel free to break the API, jettisoning this 
historical baggage.

[sqlite] The database disk image is malformed

[Andrew Stewart]

Has anybody had a large file (78gb) on a compressed folder on Windows?  I 
believe that this is where the problem is coming from and want to find out if 
anybody else has any experience with this.  I am looking at this file being 
1.6TB and was hoping to be able to compress to save some space.

Thanks,
Andrew

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
Sent: Thursday, January 07, 2016 8:38 AM
To: SQLite mailing list 
Subject: Re: [sqlite] The database disk image is malformed


On 7 Jan 2016, at 3:09pm, Andrew Stewart  wrote:

> I have run the Quick Check.  The results are below.  This database is written 
> to once a minute.  One minute it was working and the next it was not.  What 
> would cause it to suddenly have errors?

Hardware fault.  Loss of power.  Another program overwriting parts of the file. 
 Corruption of memory which only SQLite should be accessing.  Other stuff.

Are you checking the values returned by your SQLite API calls ?  Were they all 
SQLITE_OK ?

Simon.
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Notice: This electronic transmission contains confidential information, 
intended only for the person(s) named above. If you are not the intended 
recipient, you are hereby notified that any disclosure, copying, distribution, 
or any other use of this email is strictly prohibited. If you have received 
this transmission by error, please notify us immediately by return email and 
destroy the original transmission immediately and all copies thereof.

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Darren Duncan]

On 2016-01-07 4:55 PM, Warren Young wrote:
> 2. There is no ?preview? mechanism.  That is, you can?t bind some parameters 
> to a prepared query string and then get the resulting SQL because SQLite 
> substitutes the values into the query at a layer below the SQL parser.  This 
> means that if you have an error in your SQL syntax or your parameters cause a 
> constraint violation, your debug logging layer can only log the prepared 
> query string, not the parameters that went into it, which makes it 
> unnecessarily difficult to determine which code path caused you to get the 
> error when looking at logs of a running system.

The current method of binding is correct.  All we really need is that the debug 
logging layer include both the SQL of the prepared statement AND a list of the 
bound values when the execute failed, and optionally a stack trace.  As for 
problems due to syntax errors, those presumably would be caught at prepare time 
or have nothing to do with the execute time values anyway as syntax errors are 
by definition a SQL syntax problem. -- Darren Duncan

[sqlite] Question regarding use of REPLACE

[Simon Slavin]

On 7 Jan 2016, at 4:49pm, audio muze  wrote:

> Is it acceptable to make multiple calls to replace involving the same
> field in a single update operation, like so:
> 
> UPDATE audio SET
>composer = REPLACE( composer, " / ", "\\" ),
>composer = REPLACE( composer, " , ", "\\" ),
>composer = REPLACE( composer, ", ", "\\" ),
>composer = REPLACE( composer, ",", "\\" ),
>composer = REPLACE( composer, "\\Jr.\\", ", Jr.\\" ),
>composer = REPLACE( composer, "\\Jr\\", ", Jr.\\" )
> ;

There is nothing that bans it, but there is no way to know in which order the 
operations are done.  One often sees something like this:

UPDATE audio SET
   composer = REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( composer
, " / ", "\\" ),
, " , ", "\\" ),
, ", ", "\\" ),
, ",", "\\" ),
, "\\Jr.\\", ", Jr.\\" ),
, "\\Jr\\", ", Jr.\\" )
;

Simon.

[sqlite] The database disk image is malformed

[Simon Slavin]

On 7 Jan 2016, at 3:09pm, Andrew Stewart  wrote:

> I have run the Quick Check.  The results are below.  This database is written 
> to once a minute.  One minute it was working and the next it was not.  What 
> would cause it to suddenly have errors?

Hardware fault.  Loss of power.  Another program overwriting parts of the file. 
 Corruption of memory which only SQLite should be accessing.  Other stuff.

Are you checking the values returned by your SQLite API calls ?  Were they all 
SQLITE_OK ?

Simon.

[sqlite] The database disk image is malformed

[Andrew Stewart]

I have run the Quick Check.  The results are below.  This database is written 
to once a minute.  One minute it was working and the next it was not.  What 
would cause it to suddenly have errors?  This is what I don't understand.

check_callback argc 1 argv <*** in database main ***

On tree page 78032851 cell 0: invalid page number 78032953
On tree page 78032851 cell 80: invalid page number 78032952
On tree page 78032851 cell 79: invalid page number 78032943
Page 78032902: btreeInitPage() returns error code 11
Page 78032899: btreeInitPage() returns error code 11
On tree page 78032851 cell 76: 2nd reference to page 78032896
Page 78032893: btreeInitPage() returns error code 11
On tree page 78032851 cell 74: 2nd reference to page 78032891
On tree page 78032851 cell 73: 2nd reference to page 78032890
On tree page 78032851 cell 72: 2nd reference to page 78032892
Page 78032894: btreeInitPage() returns error code 11
On tree page 78032851 cell 70: 2nd reference to page 78032897
Page 78032898: btreeInitPage() returns error code 11
Page 78032900: btreeInitPage() returns error code 11
Page 78032903: btreeInitPage() returns error code 11
Page 78032905: btreeInitPage() returns error code 11
Page 78032906: btreeInitPage() returns error code 11
Page 78032908: btreeInitPage() returns error code 11
Page 78032909: btreeInitPage() returns error code 11
On tree page 78032785 cell 50: Rowid 1920558098 out of order
On tree page 78032620 cell 51: Rowid 1920553873 out of order
On tree page 12554114 cell 1: 2nd reference to page 73763093
On tree page 12554114 cell 45: 2nd reference to page 74027333
On tree page 12554114 cell 44: 2nd reference to page 74269821
On tree page 12554114 cell 43: 2nd reference to page 74282113
On tree page 12554114 cell 42: Child page depth differs
On tree page 11504958 cell 0: 2nd reference to page 17057664
On tree page 11504958 cell 41: Child page depth differs
On tree page 49348817 cell 3: 2nd reference to page 77795949
On tree page 49348817 cell 44: 2nd reference to page 77795603
On tree page 49348817 cell 43: 2nd reference to page 77795269
On tree page 49348817 cell 42: 2nd reference to page 77794959
On tree page 49348817 cell 41: 2nd reference to page 77794647
On tree page 49348817 cell 40: 2nd reference to page 77794339
On tree page 49348817 cell 39: 2nd reference to page 77793992
On tree page 49348817 cell 38: 2nd reference to page 77793667
On tree page 49348817 cell 37: 2nd reference to page 77793387
On tree page 74474051 cell 18: 2nd reference to page 77794984
On tree page 74474051 cell 44: 2nd reference to page 77794646
On tree page 74474051 cell 43: 2nd reference to page 77794312
On tree page 74474051 cell 42: 2nd reference to page 77793944
On tree page 74474051 cell 41: 2nd reference to page 77793595
On tree page 23617480 cell 6: 2nd reference to page 77348882
On tree page 23617480 cell 45: 2nd reference to page 77348544
On tree page 23617480 cell 44: 2nd reference to page 77797542
On tree page 23617480 cell 43: 2nd reference to page 77797224
On tree page 23617480 cell 42: 2nd reference to page 77796839
On tree page 23617480 cell 41: 2nd reference to page 77796444
On tree page 23617480 cell 40: 2nd reference to page 77796068
On tree page 23617480 cell 39: 2nd reference to page 77795675
On tree page 23617480 cell 38: 2nd reference to page 77795283
On tree page 23617480 cell 37: 2nd reference to page 77794931
On tree page 23617480 cell 36: 2nd reference to page 77794576
On tree page 23617480 cell 35: 2nd reference to page 77794219
On tree page 44645394 cell 13: 2nd reference to page 77794197
On tree page 44645394 cell 45: 2nd reference to page 77793853
On tree page 44645394 cell 44: 2nd reference to page 77793543
On tree page 44645394 cell 43: 2nd reference to page 77793249
On tree page 44645394 cell 42: 2nd reference to page 77792913
On tree page 44645394 cell 41: 2nd reference to page 77792600
On tree page 44645394 cell 40: 2nd reference to page 77792259
On tree page 44645394 cell 39: 2nd reference to page 77791944
On tree page 44645394 cell 38: 2nd reference to page 77573982
On tree page 44645394 cell 37: 2nd reference to page 77573636
On tree page 44645394 cell 36: 2nd reference to page 77573305
On tree page 44645394 cell 35: 2nd reference to page 77572949
On tree page 44645394 cell 34: 2nd reference to page 77572603
On tree page 9022708 cell 22: 2nd reference to page 77795655
On tree page 9022708 cell 45: 2nd reference to page 77795318
On tree page 9022708 cell 44: 2nd reference to page 77795008
On tree page 9022708 cell 43: 2nd reference to page 77794698
On tree page 9022708 cell 42: 2nd reference to page 77794387
On tree page 9022708 cell 41: 2nd reference to page 77794048
On tree page 9022708 cell 40: 2nd reference to page 77793710
On tree page 9022708 cell 39: 2nd reference to page 77793431
On tree page 9022708 cell 38: 2nd reference to page 77793104
On tree page 9022708 cell 37: 2nd reference to page 77792777
On tree page 9022708 cell 36: 2nd reference to page 77792457
On tree page 

[sqlite] How to make sqlite3_release_memory produce a result?

[Scott Hess]

Hmm.  I see that sqlite3PcacheReleaseMemory() is a no-op
if sqlite3GlobalConfig.nPage is non-zero, and that happens
when SQLITE_DEFAULT_PCACHE_INITSZ is not zero.  If I compile with
-DSQLITE_DEFAULT_PCACHE_INITSZ=0 , then I see the expected pages freed.
AFAICT, it doesn't matter if you make use of more than
SQLITE_DEFAULT_PCACHE_INITSZ pages, it still doesn't free the excess.

Looks like that was added in 3.8.11 back in July.

-scott


On Thu, Jan 7, 2016 at 5:44 AM, Bart Smissaert 
wrote:

> These are the compile flags of my sqlite3.dll:
>
> compile_option
> 
> DEBUG
> ENABLE_COLUMN_METADATA
> ENABLE_MEMORY_MANAGEMENT
> MEMDEBUG
> OMIT_LOOKASIDE
> THREADSAFE=0
>
> And these are the set pragma's:
>
> PRAGMA cache_size 32768
> PRAGMA default_cache_size 32768
> PRAGMA page_count 712711
> PRAGMA max_page_count 1073741823
> PRAGMA page_size 1024
> PRAGMA journal_size_limit -1
> PRAGMA locking_mode normal
> PRAGMA automatic_index 1
> PRAGMA encoding UTF-8
> PRAGMA ignore_check_constraints 0
> PRAGMA read_uncommitted 0
> PRAGMA recursive_triggers 0
> PRAGMA reverse_unordered_selects 0
> PRAGMA secure_delete 0
> PRAGMA wal_autocheckpoint 1000
> PRAGMA writable_schema 0
> PRAGMA journal_mode off
> PRAGMA auto_vacuum NONE
> PRAGMA synchronous OFF
> PRAGMA temp_store DEFAULT
>
>
> RBS
>
> On Thu, Jan 7, 2016 at 1:39 PM, Bart Smissaert 
> wrote:
>
> > > So you should
> > see results if you start a transaction, do a few update statements,
> commit
> > the transaction, the call sqlite3_release_memory(db).
> >
> > I tried this with a large table, first with no transaction then with a
> > transaction
> > and tried the sqlite3_release_memory directly after the sqlite3_finalize,
> > but in both cases result still zero:
> >
> > sqlite3_memory_used: 37190712
> > sqlite3_release_memory:0
> > sqlite3_memory_used: 37190712
> >
> > This was a single update, where I don't think a transaction is helpful.
> > Still no idea how I can make sqlite3_release_memory produce non-zero.
> >
> > RBS
> >
> >
> >
> > On Thu, Jan 7, 2016 at 6:05 AM, Scott Hess  wrote:
> >
> >> On Wed, Jan 6, 2016 at 3:03 PM, Bart Smissaert <
> bart.smissaert at gmail.com>
> >> wrote:
> >>
> >> > Have compiled sqlite3.dll (latest) compiled with
> >> ENABLE_MEMORY_MANAGEMENT,
> >> > but sofar
> >> > not been able yet to make sqlite3_release_memory produce anything else
> >> than
> >> > 0.
> >> > What would be the simplest way to make this happen?
> >> > I don't want to do this with C coding, so it should be some SQL
> >> scenario or
> >> > to do with simple
> >> > SQLite functions such as sqlite3_step, _prepare, -finalize etc.
> >>
> >>
> >> Last time I was paying attention to this, I believe that I found that
> the
> >> biggest effect was to free unpinned pages from the page cache.  So it
> >> might
> >> not free pages if you're in a transaction, for instance.  I would guess
> >> that if you had memory-mapped mode on and are doing only reads, there
> >> would
> >> be no pages to free (mmap pages aren't in the page cache).  So you
> should
> >> see results if you start a transaction, do a few update statements,
> commit
> >> the transaction, the call sqlite3_release_memory(db).
> >>
> >> -scott
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users at mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] How to make sqlite3_release_memory produce a result?

[Bart Smissaert]

These are the compile flags of my sqlite3.dll:

compile_option

DEBUG
ENABLE_COLUMN_METADATA
ENABLE_MEMORY_MANAGEMENT
MEMDEBUG
OMIT_LOOKASIDE
THREADSAFE=0

And these are the set pragma's:

PRAGMA cache_size 32768
PRAGMA default_cache_size 32768
PRAGMA page_count 712711
PRAGMA max_page_count 1073741823
PRAGMA page_size 1024
PRAGMA journal_size_limit -1
PRAGMA locking_mode normal
PRAGMA automatic_index 1
PRAGMA encoding UTF-8
PRAGMA ignore_check_constraints 0
PRAGMA read_uncommitted 0
PRAGMA recursive_triggers 0
PRAGMA reverse_unordered_selects 0
PRAGMA secure_delete 0
PRAGMA wal_autocheckpoint 1000
PRAGMA writable_schema 0
PRAGMA journal_mode off
PRAGMA auto_vacuum NONE
PRAGMA synchronous OFF
PRAGMA temp_store DEFAULT


RBS

On Thu, Jan 7, 2016 at 1:39 PM, Bart Smissaert 
wrote:

> > So you should
> see results if you start a transaction, do a few update statements, commit
> the transaction, the call sqlite3_release_memory(db).
>
> I tried this with a large table, first with no transaction then with a
> transaction
> and tried the sqlite3_release_memory directly after the sqlite3_finalize,
> but in both cases result still zero:
>
> sqlite3_memory_used: 37190712
> sqlite3_release_memory:0
> sqlite3_memory_used: 37190712
>
> This was a single update, where I don't think a transaction is helpful.
> Still no idea how I can make sqlite3_release_memory produce non-zero.
>
> RBS
>
>
>
> On Thu, Jan 7, 2016 at 6:05 AM, Scott Hess  wrote:
>
>> On Wed, Jan 6, 2016 at 3:03 PM, Bart Smissaert 
>> wrote:
>>
>> > Have compiled sqlite3.dll (latest) compiled with
>> ENABLE_MEMORY_MANAGEMENT,
>> > but sofar
>> > not been able yet to make sqlite3_release_memory produce anything else
>> than
>> > 0.
>> > What would be the simplest way to make this happen?
>> > I don't want to do this with C coding, so it should be some SQL
>> scenario or
>> > to do with simple
>> > SQLite functions such as sqlite3_step, _prepare, -finalize etc.
>>
>>
>> Last time I was paying attention to this, I believe that I found that the
>> biggest effect was to free unpinned pages from the page cache.  So it
>> might
>> not free pages if you're in a transaction, for instance.  I would guess
>> that if you had memory-mapped mode on and are doing only reads, there
>> would
>> be no pages to free (mmap pages aren't in the page cache).  So you should
>> see results if you start a transaction, do a few update statements, commit
>> the transaction, the call sqlite3_release_memory(db).
>>
>> -scott
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>

[sqlite] How to make sqlite3_release_memory produce a result?

[Bart Smissaert]

> So you should
see results if you start a transaction, do a few update statements, commit
the transaction, the call sqlite3_release_memory(db).

I tried this with a large table, first with no transaction then with a
transaction
and tried the sqlite3_release_memory directly after the sqlite3_finalize,
but in both cases result still zero:

sqlite3_memory_used: 37190712
sqlite3_release_memory:0
sqlite3_memory_used: 37190712

This was a single update, where I don't think a transaction is helpful.
Still no idea how I can make sqlite3_release_memory produce non-zero.

RBS



On Thu, Jan 7, 2016 at 6:05 AM, Scott Hess  wrote:

> On Wed, Jan 6, 2016 at 3:03 PM, Bart Smissaert 
> wrote:
>
> > Have compiled sqlite3.dll (latest) compiled with
> ENABLE_MEMORY_MANAGEMENT,
> > but sofar
> > not been able yet to make sqlite3_release_memory produce anything else
> than
> > 0.
> > What would be the simplest way to make this happen?
> > I don't want to do this with C coding, so it should be some SQL scenario
> or
> > to do with simple
> > SQLite functions such as sqlite3_step, _prepare, -finalize etc.
>
>
> Last time I was paying attention to this, I believe that I found that the
> biggest effect was to free unpinned pages from the page cache.  So it might
> not free pages if you're in a transaction, for instance.  I would guess
> that if you had memory-mapped mode on and are doing only reads, there would
> be no pages to free (mmap pages aren't in the page cache).  So you should
> see results if you start a transaction, do a few update statements, commit
> the transaction, the call sqlite3_release_memory(db).
>
> -scott
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] SQLite remote access over WAN

[a...@zator.com]

>  Mensaje original 
> De: Eduardo Morras 
> Para:  sqlite-users at mailinglists.sqlite.org
> Fecha:  Mon, 4 Jan 2016 23:27:35 +0100
> Asunto:  Re: [sqlite] SQLite remote access over WAN
>
>On Mon, 04 Jan 2016 22:52:56 +0100
>ajm at zator.com wrote:
>
>> Hi list:
>> 
>> I've built a Windows desktop app using MS Visual C++ and SQLite. It
>> works fair well in local mode, but now I need made it work over a
>> TCP/IP network.
>> 
>> My question is if some in this list can suggest a library to that
>> purpose, on the basis that I have not concerns on questions like
>> concurrency, access permissions or encriptation. Preferable a light
>> weigth open source C/C++ library. Not .NET, MFC or other external
>> dependencies, although Boost Asio may be Ok. (already I'm using it)
>
>I used libevent (http://libevent.org/), ZMQ (http://zeromq.org/), nanomsg 
>(http://nanomsg.org/) and plain standard libc in different projects. Depending 
>on what you need, where your app will work, the workload you'll have, "the 
>marketing wise boys" opinion, etc...  you should choose one or another.
>

Eduardo:

Thanks for your input.

The ZeroMQ framework seem promising, but still I need some dig in it, and see 
if its asynchronous mode can be adequate to my purposes.

As as far as I can see, the matter has no easy solution (in Windows without 
.NET), so as a last resource, perhaps I use a plain skeletal RPC mechanism 
(MSRPC).

Cheers.

--
Adolfo J. Millan.

[sqlite] Use of DELETE LIMIT OFFSET with ENABLE_UPDATE_DELETE_LIMIT

[Jeremy Boy]

Hi list,
sorry for the late reply, Christmas holidays :-)

> Am 21.12.2015 um 09:15 schrieb Jan Nijtmans :
> 
> 2015-12-19 21:57 GMT+01:00 Jeremy Boy :
>> I would like to use the optional LIMIT and ORDER BY clause for DELETE 
>> statements in SQLite. I can verify that SQLite was built with 
>> SQLITE_ENABLE_UPDATE_DELETE_LIMIT:
> 
> This doesn't work when sqlite is buillt from the amalamation, which is
> clearly stated
> in the docs. You need to build it from the sources, then it will work.
Thanks for the pointer. I read about this in the docs, but thought that I built 
from sources. I used the FreeBSD ports tree (and didn't quite get the point of 
the amalgamation, yet). How can I verify where SQLite was built from?
> The problem is
> that the produced amalgamation is different depending on this flag.
> 
> My solution would be the patch below (to the SQLite sources). With this
> patch in place, the resulting amalgamation can be build either with or without
> SQLITE_ENABLE_UPDATE_DELETE_LIMIT, both work as expected. The
> produced amalgamation doesn't depend on the SQLITE_ENABLE_UPDATE_DELETE_LIMIT
> flag any more.
> 
> I'm donating this patch to the public domain, in case anyone is interested
This should be either implemented or the config flag should be removed from the 
amalgamation, IMHO. Thanks for the patch. 
> 
> Needless to say: All unit-test (in the SQLite sources) run fine when
> this patch is applied.
> 
> Could I add this to the 2016 wish-list?;-)
> 
> Regards,
>Jan Nijtmans
> 
> ===
> --- src/parse.y
> +++ src/parse.y
> @@ -740,54 +740,56 @@
> limit_opt(A) ::= LIMIT expr(X) COMMA expr(Y).
>  {A.pOffset = X.pExpr; A.pLimit
> = Y.pExpr;}
> 
> /// The DELETE statement /
> //
> -%ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
> cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W)
>orderby_opt(O) limit_opt(L). {
>  sqlite3WithPush(pParse, C, 1);
>  sqlite3SrcListIndexedBy(pParse, X, );
> +#if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) &&
> !defined(SQLITE_OMIT_SUBQUERY)
>  W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "DELETE");
> -  sqlite3DeleteFrom(pParse,X,W);
> -}
> -%endif
> -%ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
> -cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
> -  sqlite3WithPush(pParse, C, 1);
> -  sqlite3SrcListIndexedBy(pParse, X, );
> +#else
> +  if( O || L.pLimit ){
> +sqlite3ErrorMsg(pParse, "%s on DELETE not supported", O?"ORDER
> BY":"LIMIT");
> +sqlite3ExprDelete(pParse->db, W);
> +sqlite3ExprListDelete(pParse->db, O);
> +sqlite3ExprDelete(pParse->db, L.pLimit);
> +sqlite3ExprDelete(pParse->db, L.pOffset);
> +W = 0;
> +  }
> +#endif
>  sqlite3DeleteFrom(pParse,X,W);
> }
> -%endif
> 
> %type where_opt {Expr*}
> %destructor where_opt {sqlite3ExprDelete(pParse->db, $$);}
> 
> where_opt(A) ::= .{A = 0;}
> where_opt(A) ::= WHERE expr(X).   {A = X.pExpr;}
> 
> // The UPDATE command 
> //
> -%ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
> cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
>where_opt(W) orderby_opt(O) limit_opt(L).  {
>  sqlite3WithPush(pParse, C, 1);
>  sqlite3SrcListIndexedBy(pParse, X, );
>  sqlite3ExprListCheckLength(pParse,Y,"set list");
> +#if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) &&
> !defined(SQLITE_OMIT_SUBQUERY)
>  W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "UPDATE");
> -  sqlite3Update(pParse,X,Y,W,R);
> -}
> -%endif
> -%ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
> -cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
> -where_opt(W).  {
> -  sqlite3WithPush(pParse, C, 1);
> -  sqlite3SrcListIndexedBy(pParse, X, );
> -  sqlite3ExprListCheckLength(pParse,Y,"set list");
> +#else
> +  if( O || L.pLimit ){
> +sqlite3ErrorMsg(pParse, "%s on UPDATE not supported", O?"ORDER
> BY":"LIMIT");
> +sqlite3ExprDelete(pParse->db, W);
> +sqlite3ExprListDelete(pParse->db, O);
> +sqlite3ExprDelete(pParse->db, L.pLimit);
> +sqlite3ExprDelete(pParse->db, L.pOffset);
> +W = 0;
> +  }
> +#endif
>  sqlite3Update(pParse,X,Y,W,R);
> }
> -%endif
> 
> %type setlist {ExprList*}
> %destructor setlist {sqlite3ExprListDelete(pParse->db, $$);}
> 
> setlist(A) ::= setlist(Z) COMMA nm(X) EQ expr(Y). {

[sqlite] Use of DELETE LIMIT OFFSET with ENABLE_UPDATE_DELETE_LIMIT

[Richard Hipp]

On 1/7/16, Jeremy Boy  wrote:
>  How can I verify where SQLite was built
> from?

(1) Get the canonical sources from
https://www.sqlite.org/download.html or https://www.sqlite.org/src

(2) CFLAGS=-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT ./configure

(3) make sqlite3.c

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] The database disk image is malformed

[Richard Hipp]

On 1/7/16, Andrew Stewart  wrote:
> I have run the Quick Check.  The results are below.  This database is
> written to once a minute.  One minute it was working and the next it was
> not.  What would cause it to suddenly have errors?  This is what I don't
> understand.
>
> check_callback argc 1 argv <*** in database main ***
>
> On tree page 78032851 cell 0: invalid page number 78032953
> On tree page 78032851 cell 80: invalid page number 78032952
> On tree page 78032851 cell 79: invalid page number 78032943
> Page 78032902: btreeInitPage() returns error code 11

Knowing the content of page 78032902 (and any other page for which
btreeInitPage() failed) as a hex dump might give a clue.  On a
workstation, with the SQLite sources checked out and configured:

make showdb
./showdb FILENAME 78032902b


Enabling the error log might also give a clue.

Probably the errors did not appear suddenly.  The errors have been
accumulating and then SQLite suddenly tripped over one of them.


> Page 78032899: btreeInitPage() returns error code 11
> On tree page 78032851 cell 76: 2nd reference to page 78032896
> Page 78032893: btreeInitPage() returns error code 11
> On tree page 78032851 cell 74: 2nd reference to page 78032891
> On tree page 78032851 cell 73: 2nd reference to page 78032890
> On tree page 78032851 cell 72: 2nd reference to page 78032892
> Page 78032894: btreeInitPage() returns error code 11
> On tree page 78032851 cell 70: 2nd reference to page 78032897
> Page 78032898: btreeInitPage() returns error code 11
> Page 78032900: btreeInitPage() returns error code 11
> Page 78032903: btreeInitPage() returns error code 11
> Page 78032905: btreeInitPage() returns error code 11
> Page 78032906: btreeInitPage() returns error code 11
> Page 78032908: btreeInitPage() returns error code 11
> Page 78032909: btreeInitPage() returns error code 11
> On tree page 78032785 cell 50: Rowid 1920558098 out of order
> On tree page 78032620 cell 51: Rowid 1920553873 out of order
> On tree page 12554114 cell 1: 2nd reference to page 73763093
> On tree page 12554114 cell 45: 2nd reference to page 74027333
> On tree page 12554114 cell 44: 2nd reference to page 74269821
> On tree page 12554114 cell 43: 2nd reference to page 74282113
> On tree page 12554114 cell 42: Child page depth differs
> On tree page 11504958 cell 0: 2nd reference to page 17057664
> On tree page 11504958 cell 41: Child page depth differs
> On tree page 49348817 cell 3: 2nd reference to page 77795949
> On tree page 49348817 cell 44: 2nd reference to page 77795603
> On tree page 49348817 cell 43: 2nd reference to page 77795269
> On tree page 49348817 cell 42: 2nd reference to page 77794959
> On tree page 49348817 cell 41: 2nd reference to page 77794647
> On tree page 49348817 cell 40: 2nd reference to page 77794339
> On tree page 49348817 cell 39: 2nd reference to page 77793992
> On tree page 49348817 cell 38: 2nd reference to page 77793667
> On tree page 49348817 cell 37: 2nd reference to page 77793387
> On tree page 74474051 cell 18: 2nd reference to page 77794984
> On tree page 74474051 cell 44: 2nd reference to page 77794646
> On tree page 74474051 cell 43: 2nd reference to page 77794312
> On tree page 74474051 cell 42: 2nd reference to page 77793944
> On tree page 74474051 cell 41: 2nd reference to page 77793595
> On tree page 23617480 cell 6: 2nd reference to page 77348882
> On tree page 23617480 cell 45: 2nd reference to page 77348544
> On tree page 23617480 cell 44: 2nd reference to page 77797542
> On tree page 23617480 cell 43: 2nd reference to page 77797224
> On tree page 23617480 cell 42: 2nd reference to page 77796839
> On tree page 23617480 cell 41: 2nd reference to page 77796444
> On tree page 23617480 cell 40: 2nd reference to page 77796068
> On tree page 23617480 cell 39: 2nd reference to page 77795675
> On tree page 23617480 cell 38: 2nd reference to page 77795283
> On tree page 23617480 cell 37: 2nd reference to page 77794931
> On tree page 23617480 cell 36: 2nd reference to page 77794576
> On tree page 23617480 cell 35: 2nd reference to page 77794219
> On tree page 44645394 cell 13: 2nd reference to page 77794197
> On tree page 44645394 cell 45: 2nd reference to page 77793853
> On tree page 44645394 cell 44: 2nd reference to page 77793543
> On tree page 44645394 cell 43: 2nd reference to page 77793249
> On tree page 44645394 cell 42: 2nd reference to page 77792913
> On tree page 44645394 cell 41: 2nd reference to page 77792600
> On tree page 44645394 cell 40: 2nd reference to page 77792259
> On tree page 44645394 cell 39: 2nd reference to page 77791944
> On tree page 44645394 cell 38: 2nd reference to page 77573982
> On tree page 44645394 cell 37: 2nd reference to page 77573636
> On tree page 44645394 cell 36: 2nd reference to page 77573305
> On tree page 44645394 cell 35: 2nd reference to page 77572949
> On tree page 44645394 cell 34: 2nd reference to page 77572603
> On tree page 9022708 cell 22: 2nd reference to page 77795655
> On tree page 

[sqlite] How to make sqlite3_release_memory produce a result?

[Bart Smissaert]

OK, will try that.
sqlite3_release_memory doesn't have the DB connection as an argument, but
found
sqlite3_db_release_memory and that has that as an argument and that may
work better.

RBS

On Thu, Jan 7, 2016 at 6:05 AM, Scott Hess  wrote:

> On Wed, Jan 6, 2016 at 3:03 PM, Bart Smissaert 
> wrote:
>
> > Have compiled sqlite3.dll (latest) compiled with
> ENABLE_MEMORY_MANAGEMENT,
> > but sofar
> > not been able yet to make sqlite3_release_memory produce anything else
> than
> > 0.
> > What would be the simplest way to make this happen?
> > I don't want to do this with C coding, so it should be some SQL scenario
> or
> > to do with simple
> > SQLite functions such as sqlite3_step, _prepare, -finalize etc.
>
>
> Last time I was paying attention to this, I believe that I found that the
> biggest effect was to free unpinned pages from the page cache.  So it might
> not free pages if you're in a transaction, for instance.  I would guess
> that if you had memory-mapped mode on and are doing only reads, there would
> be no pages to free (mmap pages aren't in the page cache).  So you should
> see results if you start a transaction, do a few update statements, commit
> the transaction, the call sqlite3_release_memory(db).
>
> -scott
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] How to make sqlite3_release_memory produce a result?

[Scott Hess]

In that case - oops.  I definitely was experimenting with the db-centric
version.  It looks to me like the lowest-level mechanism is similar between
the two, but sqlite3_db_release_memory() isn't optionally compiled.

-scott


On Thu, Jan 7, 2016 at 12:32 AM, Bart Smissaert 
wrote:

> OK, will try that.
> sqlite3_release_memory doesn't have the DB connection as an argument, but
> found
> sqlite3_db_release_memory and that has that as an argument and that may
> work better.
>
> RBS
>
> On Thu, Jan 7, 2016 at 6:05 AM, Scott Hess  wrote:
>
> > On Wed, Jan 6, 2016 at 3:03 PM, Bart Smissaert  >
> > wrote:
> >
> > > Have compiled sqlite3.dll (latest) compiled with
> > ENABLE_MEMORY_MANAGEMENT,
> > > but sofar
> > > not been able yet to make sqlite3_release_memory produce anything else
> > than
> > > 0.
> > > What would be the simplest way to make this happen?
> > > I don't want to do this with C coding, so it should be some SQL
> scenario
> > or
> > > to do with simple
> > > SQLite functions such as sqlite3_step, _prepare, -finalize etc.
> >
> >
> > Last time I was paying attention to this, I believe that I found that the
> > biggest effect was to free unpinned pages from the page cache.  So it
> might
> > not free pages if you're in a transaction, for instance.  I would guess
> > that if you had memory-mapped mode on and are doing only reads, there
> would
> > be no pages to free (mmap pages aren't in the page cache).  So you should
> > see results if you start a transaction, do a few update statements,
> commit
> > the transaction, the call sqlite3_release_memory(db).
> >
> > -scott
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>