Re: [sqlite] fixing time-warp

[Warren Young]

On Nov 3, 2014, at 7:01 PM, E. Timothy Uy  wrote:

> Is that a philosophical question? :)

No, it’s a persuasion challenge.

I’ll propose it in two parts:

1. Why is it a good idea for you, E. Timothy Uy, to dump the SQLite code repo 
into a Git repo?  What does this achieve, that keeping it in Fossil does not?

2. Why is it a good idea for our BDFL, D. Richard Hipp, to modify the SQLite 
repo to make it easier for you to dump it into a Git repo?  Keep in mind that 
the costs here are not just his time, but also the loss of a test case.

I don’t think you can convince anyone that #2 is a good idea, but I’m curious 
about why #1 is a good idea.

Bonus persuasion point 3: Why not persuade the Git people to modify their tool 
to cope with time warps?  Isn’t their major value proposition w.r.t the other 
open source DVCSes that Git is more powerful and flexible?  Here we see Fossil 
doing something Git cannot or will not do, and it’s not a matter of mission 
scope, as with the bug tracker or wiki features of Fossil.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] x64 vs x32 DLL

[Warren Young]

On Nov 4, 2014, at 5:25 PM, Keith Medcalf  wrote:

> Assuming that you do rebuild the entire application as 64-bit, it will 
> consume at least twice the amount of memory as the x86 version and run slower 
> in user code.

[citation needed]

On 32-bit Cygwin:

$ ls -lh `which sqlite3`
-rwxrwxr-x 1 Warren None 66K Nov  4 03:00 /usr/bin/sqlite3

And on 64-bit Cygwin:

$ ls -lh `which sqlite3`
-rwxrwxr-x 1 Warren None 65K Nov  4 01:16 /usr/bin/sqlite3

I know you said “memory” and not “disk space,” but part of memory use is the 
size of the code loaded.

Run time RAM use also shows no significant difference for the two versions of 
sqlite3.exe on Cygwin.  If I say `sqlite3 nonexistent.db`, Windows Task Manager 
reports the same RAM usage (0.9 MB) on my machine for both executables.

I then loaded up a 284 MB DB instead of the empty one.  Initially, both builds 
of sqlite3.exe consumed about the same amount of RAM.  After a few big SELECT 
calls, the 64-bit one was using 3.4 MB of RAM, while the 32-bit one was a bit 
slimmer, at 3.2 MB, a difference of only 6-8%, depending on whether you factor 
out the 0.9 MB constant or not.

Note that the on-disk usage difference for the DB is always 0%, since SQLite’s 
DB format is platform-independent.  That means the explanation for the RAM size 
difference probably comes down entirely to pointer size differences, which is 
swamped by the size of the actual data being managed.

It’s also debatable whether the user space code will run slower.  Every 
benchmark I’ve seen puts the difference down in the single-digit range, with 
the 64-bit code sometimes being the faster version.

(64-bit code is sometimes a bit faster despite the single-digit percentage RAM 
hit because you don’t have to go through the WOW64 layer, the compiler can use 
wider data transfer instructions in some cases, etc.)

You can wipe these tiny differences away by waiting for a few weeks’ worth of 
Moore’s Law improvements.

> ...open to kernel mode code injection exploits when running x64 applications, 
> just like x86 applications on x86 kernels are open to such exploits, because 
> the kernel is mapped into the process address space.  Such injection exploits 
> against x64 kernel code and processes being impossible from an x86 process.

If you’re trying to protect against that, you should probably just be running 
your programs in a restricted VM, rather than depend on the WOW64 compatibility 
layer to break the exploit code.

You’ve also left an implication here, that 32-on-32 and 64-on-64 have the same 
level risk.  The 64-bit Windows kernel is a fair bit more secure than the 
32-bit one, if only because Microsoft was finally able to remove some 
long-deprecated exploit paths.

> Unless there is a specific reason for converting the entire application to 
> x64 (such as a requirement to access more than 3.9 GB of per-process memory) 
> or to take advantage of other specific architectural differences (which are 
> almost all entirely dependent on the compiler you choose) there is no 
> advantage to x64 applications over x86 applications on an x64 Operating 
> System.

This is all true, as far as it goes.  Security, speed, and RAM usage just 
aren’t good justifications.

Want a good justification?  Opportunity cost.  Effort *not* spent converting is 
effort you can spend on something that gets you a benefit you *will* notice.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Open DB from stream to use System.IO.Packaging.Package

[Warren Young]

On Aug 20, 2015, at 5:27 AM, Steffen Mangold  
wrote:
> 
> What I want is to use the System.IO.Packaging.Package class to build a custom 
> file format.
> Inside this a SQLite DB should be one System.IO.Packaging.PackagePart.

It seems to me that SQLite and this .NET hierarchy are both trying to do 
essentially the same thing, which is allow you to store heterogeneous data in a 
single file.

If you want to use SQLite for part of that, why not use it for *all* of that?  
What is it that you want to store in System.IO.Packaging.Package that can?t 
live in a SQLite table instead?

[sqlite] whish list for 2016

[Warren Young]

On Dec 24, 2015, at 9:14 AM, Simon Slavin  wrote:
> 
> ALTER TABLE DROP COLUMN ...
> 
> ...the way SQLite3 is written makes it difficult or inefficient to implement 
> them.

I wouldn?t mind if SQLite did nothing more than the recommended manual process 
for emulating the ALTER TABLE affordances in other DBMSes which are missing in 
SQLite.

For my purposes, the table copy isn?t the problem, the problem is that a simple 
one-liner in other DBMSes becomes a dozen lines in SQLite:

   BEGIN TRANSACTION;
   ALTER TABLE Foo RENAME TO oldFoo;
   CREATE TABLE Foo ? a bunch of repeated stuff 
? almost identical to the initial
? DBMS creation code with just one
? or two differences yet everything
? that has stayed the same still has
? to be repeated just because SQLite
? doesn?t fully support ALTER TABLE
   INSERT INTO Foo SELECT some,subset,of,columns FROM oldFoo;
   COMMIT;

It would be entirely possible for SQLite to generate and run this code for me.  
Writing such code is a waste of human brain power.

[sqlite] whish list for 2016

[Warren Young]

On Dec 24, 2015, at 8:26 AM, Bernardo Sulzbach  
wrote:
> 
> I don't
> know if alter table is used at all in production anywhere (why would
> it be? the column names and ordering should not be part of the data).

Requirements change.

In the past dozen years, the database schema I?m working on right now has 
changed about a hundred times.  Many of those changes were batched, so that 
there are ?only? about 40 separate schema versions.  But, about a third of 
those involved changes that SQLite doesn?t support directly, but other SQL 
DBMSes do.

SQLite currently only supports adding new features.  (ADD COLUMN, CREATE 
TABLE.) It doesn?t deal with mutating features (MODIFY/CHANGE COLUMN) or 
removed features (DROP COLUMN) nearly as well.

SQLite?s nearly typeless nature does allow you to paper over many changes that 
would require an ALTER TABLE in another DBMS.  (e.g. extension of an 8 bit 
integer column to 32 bits, or changing an integer column to a string column)  
But, some application level changes do still require a bunch of code at the 
SQLite layer that would be a one-liner in other DBMSes.

> In the
> end, if you spent enough time in the design phase to prepare all your
> schemas, you should not have to drop (or alter) any of the tables at
> all.

What you?re describing is the old waterfall development dream, where all we 
need to do is spend more time in the design phase, and we?ll produce perfect 
software on time, every time.  The industry ran on that mantra for decades 
before the agile movement coalesced, finally providing a better set of coherent 
philosophies.

ALTER TABLE is agile.  To the extent that agile is good, stronger ALTER TABLE 
support is good, too.

I am not arguing for an abandonment of up-front design.  The software that uses 
the DBMS I describe above changed tens of thousands of times over that same 
span, so the fact that the DBMS only had to change about 100 times is a 
testament to good up front design.  Yet, changes still occur, so it?s best if 
we don?t have to jump through hoops when that happens.

[sqlite] whish list for 2016

[Warren Young]

On Dec 24, 2015, at 11:17 AM, Warren Young  wrote:
> 
>   BEGIN TRANSACTION;
>   ALTER TABLE Foo RENAME TO oldFoo;
>   CREATE TABLE Foo ? a bunch of repeated stuff 
>? almost identical to the initial
>? DBMS creation code with just one
>? or two differences yet everything
>? that has stayed the same still has
>? to be repeated just because SQLite
>? doesn?t fully support ALTER TABLE
>   INSERT INTO Foo SELECT some,subset,of,columns FROM oldFoo;
>   COMMIT;
> 
> It would be entirely possible for SQLite to generate and run this code for 
> me.  Writing such code is a waste of human brain power.

On re-reading this, I see that there is a bug in that code, which only 
underscores my point: I wouldn?t have made the error if I?d only had to write

   ALTER TABLE Foo DROP COLUMN bar;

[sqlite] whish list for 2016

[Warren Young]

On Dec 24, 2015, at 7:49 PM, Simon Slavin  wrote:
> 
> What makes "ALTER TABLE table-name DROP ?COLUMN" hard is checking the schema 
> to make sure that nothing in the schema refers to the dropped column.

Given that the current alternative to this feature is hand-rolled code like I 
gave earlier in the thread, I don?t see that SQLite *must* solve this problem.  
It would certainly be nice if it maintained consistency for you, but since the 
alternative doesn?t allow you to do that, what?s wrong with just putting a 
warning in the docs: ?If you use ALTER TABLE DROP COLUMN, you risk breaking 
consistency checks.?

Then you can push off automatic consistency check maintenance to the 
quasi-mythical SQLite 4.

Again, all I?m advocating for is automatic generation and running of the SQL I 
gave above.  That is, implement the feature in terms of existing facilities, 
don?t go creating a bunch of new code just to handle this case.  Not only does 
this make implementation easier, it will reduce the temptation to make the 
feature conditional to keep the embedded users happy, which in turn makes 
testing harder, since it doubles the number of test cases.

[sqlite] What software is deployed more than SQLite?

[Warren Young]

On May 3, 2015, at 6:50 PM, jungle Boogie  wrote:
> 
> On 3 May 2015 at 11:18, Richard Hipp  wrote:
>> Any input you can provide is appreciated!
> 
> Congratulations to you and your team on SQLite's achievement and I
> wish you continued success.
> 
> "Most Widely Deployed And Used Database Engine"
> 
> I don't think the A in and needs capitalization.

Both are correct.  The only incorrect thing to do is to mix styles on titles 
within a single work.

  http://www.quickanddirtytips.com/education/grammar/title-capitalization-rules

It?s kind of like C brace style, in that respect.

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

[Warren Young]

On 12/3/2013 17:29, James K. Lowden wrote:


Determinism is a property of a function; there is no such
thing as a function that is sometimes deterministic and sometimes not.


Unless you're new to this computing thing, you must have noticed that 
software developers almost never mean the same thing as mathematicians 
when we use the word "function".


There are tiny corners of the programming world (FP) where this is not 
the case, but then you get into questions of purity, and databases are 
about as far from side-effect-free as you can get.


Anyway, all this arguing over how SQLite *should* behave seems misguided 
to me.  What matters is how SQL is specified.  SQLite should follow the 
spec in areas like this.

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

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

[Warren Young]

On 12/5/2013 14:45, Klaas V wrote:

Warren wrote 4 dec 2013:

| There are tiny corners of the programming world (FP) where this is not the 
case, but then you get  into
|   questions of purity, and databases are about as far from side-effect-free 
as you can get.

That's a wee bit exaggerated,


To prove my point, I decided to divide the SQLite commands[1] into those 
that modify the DB and those that do not:


Has side effects Limited side effects   No side effects
~~      ~~~
ALTER TABLE  ATTACH DATABASEANALYZE
CREATE TRIGGER   CREATE TABLE   CREATE INDEX
DELETE   CREATE VIEWDROP INDEX
DETACH DATABASE[2]   CREATE VIRTUAL TABLE   EXPLAIN
DROP TABLE   DROP TRIGGER[3]PRAGMA
DROP VIEW   REINDEX
INSERT  SELECT
REPLACE VACUUM
UPDATE

Commands in the first column clearly have side effects: they can affect 
the results of another process's SELECT queries.


The second column is for commands that are unlikely to affect another 
process's queries, but they do alter the user-visible DB schema, so it's 
hard to argue that they're side-effect-free.


The third column looks longer than it really is.  You can look at it as 
SELECT plus a bunch of "DBA commands."  Those latter commands merely 
affect how well the SQLite DB engine runs, and they're unlikely to be 
used very often once the DB is set up and working well.


If you strip away the setup, DBA, and rarely-used commands from the 
other columns, too, you're still left with SELECT on one side vs about 
half a dozen SQL commands with side effects on the other.  That's what I 
was getting at with my quoted comment above.


We should also consider SQLite's "functions".

Most of the core functions[4] are pure, but there are several impure 
ones: changes(), last_insert_rowid(), random(), randomblob(), and 
total_changes().


The date and time functions[5] are pure, unless you pass 'now', which is 
what all the argument here is about, of course.


The aggregate functions[6] are all pure.

I think my point stands: SQL is awfully impure, from an FP/mathematical 
standpoint.






-

Footnotes:

[1] From https://www.sqlite.org/lang.html but leaving out the keywords 
that aren't independent commands.  I also left out the TRANSACTION and 
SAVEPOINT commands, since they just group other commands.


[2] I put the DETACH and DROP commands in the first column even though 
their corresponding ATTACH and CREATE commands are in the second because 
there is nothing stopping these destructive commands from affecting 
another process's queries.


[3] DROP TRIGGER is interesting: From the perspective of figuring out 
whether it could affect another process through side effects, this 
command actually /stops/ future side effects from occurring, even though 
it modifies the DB file to do so.  Thus, it goes into the second column, 
not the first, where all the other DROP commands are.


[4] https://www.sqlite.org/lang_corefunc.html

[5] https://www.sqlite.org/lang_datefunc.html

[6] https://www.sqlite.org/lang_aggfunc.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concrete example of corruption

[Warren Young]

On 12/5/2013 17:00, Scott Robison wrote:

Might there be a way to implement a custom VFS for Mac to deal with this?


Wouldn't it be a lot simpler to just put the DB file into a Mac package 
(i.e. directory) so the associated WAL and whatever other files get 
created in the package, too?

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

Re: [sqlite] Concrete example of corruption

[Warren Young]

On 12/5/2013 20:31, Stephen Chrzanowski wrote:

@Warren>
the package/sandbox idea won't work due to certain
constraints the OS puts on the file.


Quoting the Apple docs[1] Doug pointed to: "...you can access the 
document's contents using any appropriate file-system routines."


I'd like to see a simple C test case for whatever problem is claimed.

I'm not doubting you, exactly.  File locking is a deep, tangled morass. 
 But, it can sometimes be helpful to dive to the bottom of the swamp on 
things like this.  Sometimes surprisingly simple solutions are found 
below the reeds, alligators and algae at the surface.



Being that I'm a Windows Only developer, I've never had to run into this
problem.  If something has its hand on the file, it holds it in place.


There are pluses and minuses to the Windows way of file locking.  A lot 
of us in the Unix, Linux, BSD and OS X camps would say that the minuses 
outweigh the pluses.


    


File handling is NOT SQLites responsibility


I'm not sure about that.  SQLite, at least at one time, was billed as a 
competitor for fopen() rather than for Oracle.


Maybe all that's needed is a mode where SQLite never creates any 
external files.  Disable the WAL feature, don't create a lock file, etc. 
 I think all the configuration options for this already exist.  Has the 
OP tried disabling the interfering features?



On a personal note, I still cannot fathom why anyone would WANT to do file
management while working on an active document.


I do it frequently.  It's one of the benefits you get from using a 
system that uses advisory locking by default.


For instance, I might have a program transcoding a movie from one 
digital video file format to another, and it decides to put the output 
next to the input file.  If I actually wanted it to land somewhere else, 
I could:


a) Abort the transcode and hunt down the option that makes it put the 
output somewhere else; or


b) Just move the output file, knowing that as long as it stays on the 
same filesystem, the transcoder won't even notice the move.


This only works for files that won't be closed and then immediately 
reopened, requiring that the old path remain valid.  It also requires 
that one file to be self-contained.  Thus my suggestions above.

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

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

[Warren Young]

On 12/7/2013 12:15, James K. Lowden wrote:

On Wed, 04 Dec 2013 12:04:07 -0700
Warren Young <war...@etr-usa.com> wrote:


Determinism is a property of a function; there is no such
thing as a function that is sometimes deterministic and sometimes
not.


databases are about as far from side-effect-free as you can get.


I'm not sure what you're referring to.


I think your sense of the term "side effect" comes from the everyday 
use, which is most influenced by medical side effects.  i.e., something 
bad and unintended.


The term means something rather different in CS:

https://en.wikipedia.org/wiki/Side_effect_%28computer_science%29

Specifically here, I mean that most SQL statements other than SELECT 
modify global state: the SQLite DB file.  Any statement that modifies 
the DB file has the potential to change the result from *any* SQL 
statement, including SELECT.


Example:

SELECT * FROM foo WHERE id=42;
UPDATE foo SET bar='qux' where id=42;
SELECT * FROM foo WHERE id=42;

The first and third statements return different results, even though 
they are side effect free, because UPDATE is not side effect free.


Consider also that the UPDATE could come from another process, at an 
indeterminate time.  This is why concerns over side effects -- in the CS 
sense -- matter.


SQLite offers many ways to *control* this indeterminacy, features 
generally grouped under the acronym ACID, but you can't say "DBMS X is 
ACID compliant therefore it will never surprise me with unexpected results."

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

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

[Warren Young]

On 12/7/2013 12:53, James K. Lowden wrote:

On Thu, 05 Dec 2013 17:52:47 -0700
Warren Young <war...@etr-usa.com> wrote:


To prove my point, I decided to divide the SQLite commands[1] into
those that modify the DB and those that do not:


Oh, let me help you out here: these aren't functions.


I was careful to call them commands, and to treat what SQL calls 
"functions" separately.


Nevertheless, I think you're trying to draw a dictionary-based line here 
instead of looking at fundamental concepts.  A proper mathematical 
function takes N arguments and returns a single constant result for 
those arguments.  If you have a static SQLite DB file, any SELECT 
statement against it involving only tables and the pure SQL functions 
qualifies as a pure function itself.


The point of this exercise was to dig down to this conceptual level, 
bypassing the fuzzy terminology.


SQL is a mixed bag of true functions and non-functional [1] elements. 
This thread is about one confusion that can result when these two 
aspects of SQL intermix[2] in unexpected ways.



I remember reading an essay by a user of controlled substances


Your next reading assignment is a book[3] on a functional programming 
language, preferably one with immutable-by-default values.  Haskell is 
the current hotness, but Erlang would work just as well.


There are less pure FP languages that can teach the same lessons, if you 
diligently avoid the impure bits: the ML family[4], Scala, Scheme...


The rest of your post I answered indirectly in my reply to your other 
message in this thread.





Footnotes:

[1] In the mathematical sense.  I.e. not meaning "broken".

[2] e.g. "SELECT ... date('now')"

[3] Free online FP books:

http://learnyouahaskell.com/chapters
http://learnyousomeerlang.com/content
http://ocaml.org/learn/books.html
https://en.wikibooks.org/wiki/F_Sharp_Programming
http://www.scala-lang.org/documentation/books.html
http://www.scheme.com/tspl4/
https://mitpress.mit.edu/sicp/

[4] OCaml and F# are the most-used flavors of ML in practice currently. 
 Academia still has a lot of Standard ML holdouts.

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

Re: [sqlite] New DLLs and sources. Was: SQLite version 3.8.5 beta

[Warren Young]

On 5/28/2014 11:20, jose isaias cabrera wrote:


I would rather have the speed
then the size.


Many years ago, I read an article written by a Microsoft employee where 
they said they built Windows' own binaries optimized for size rather 
than speed, since in today's L1/L2/L3 world, size *is* speed.


Bigger code gets kicked out of the processor cache faster, so the 
processor has to go back to main memory more often.

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

Re: [sqlite] New DLLs and sources. Was: SQLite version 3.8.5 beta

[Warren Young]

On 5/28/2014 12:26, Warren Young wrote:

On 5/28/2014 11:20, jose isaias cabrera wrote:


I would rather have the speed
then the size.


in today's L1/L2/L3 world, size *is* speed.


Also, there is a pretty hard limit on how much micro code optimizations 
can help a DBMS.  It's a fundamentally I/O limited problem.  Disk is 
many (4ish?) orders of magnitude slower than main RAM, and the CPU 
caches are orders of magnitude faster than that.


http://www.eecs.berkeley.edu/~rcs/research/interactive_latency.html

That is to say, if you made every code path in SQLite zero length, it 
would do approximately *squat* to improve the time it takes to get your 
query results.


Only intelligent algorithms matter here, not micro-optimizations. 
Better indexes, smarter query planners, etc.

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

Re: [sqlite] New DLLs and sources. Was: SQLite version 3.8.5 beta

[Warren Young]

On 5/28/2014 12:35, Drago, William @ MWG - NARDAEAST wrote:


Bigger code gets kicked out of the processor cache faster, so the
processor has to go back to main memory more often.


Don't modern compilers consider what effects the speed optimizations will have 
on the pipeline and the cache and optimize accordingly?


The compiler probably won't be looking at things like cache pressure. 
All the compiler will care about is that this function now executes 
twice as fast, yay!  It won't consider that it had to make it twice as 
large as the -Os version, which will therefore stay in cache 4x as long, 
so that cache thrashing will throw away the 2x benefit.


As you say, it probably won't make a function so large that it never 
fits into L1 in the first place.

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

Re: [sqlite] GUI INfo

[Warren Young]

On 9/10/2014 14:56, Maxine Nietz wrote:

I am an experienced Access VBA programmer. I know about the SQLite commands
to create and manipulate data in tables and queries.



What I want to know is where do I find info on creating a graphical user
interface such as menus, forms and reports. What additional programs are
required to do this?


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

Re: [sqlite] OSX path

[Warren Young]

On 6/17/2011 4:50 PM, john darnell wrote:
>
> I am essentially a Windows programmer

Is that also your excuse for top-posting? :)

> I will have to take your
> word on the use of HFS-style paths vs posix/Unix style paths on Mac
> platforms.

That would be wise, because Simon is correct.

> I will have to say, however, that at least the InDesign SDK, which is
> my chief habitat when it comes to writing Mac code, encourages the
> use of colon-laden paths--or at least does not greatly discourage it,

That's because all Adobe software created before about 2006[*] was built 
on top of the Carbon SDK, which interprets colon-delimited paths for 
backwards compatibility with Classic Mac OS.  OS X's native 
POSIX/Mach/Cocoa APIs understand only slash-based paths.

SQLite is built on top of the POSIX layer of OS X, so it only 
understands POSIX paths.

As more Mac programs move to 64-bit, they must move from Carbon to 
Cocoa, and thus will require POSIX paths, unless they've built in their 
own portability layer.  I can see Adobe doing that, to preserve legacy 
compatibility.

[*] Lightroom was the first Cocoa-based Adobe app.  Its first public 
beta came out in 2006.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "Shipping a New Mindset" - SQLite in Windows 10

[Warren Young]

On Nov 10, 2015, at 1:29 PM, Rousselot, Richard A  wrote:
> 
> What no love for their own Access DB?  

This from the same company that gave us ODBC, ESQL, OLE DB, MDAC/Jet, DAO, RDO, 
ADO, ADO.NET, ADO Entity Framework, LINQ, the registry, Access, SQL Server 
Express?

  https://msdn.microsoft.com/en-us/library/ee730343.aspx

Giving developers yet another DBMS or API for a DBMS is kinda what Microsoft 
does. :)

Microsoft is finally getting to a place Apple?s been since 2005.

(SQLite first shipped with OS X Tiger, and has shipped on every iDevice.)

I don?t mean to be dismissive.  Obviously getting SQLite into Windows is a 
great thing.  It?s just that it would have been even nicer a decade ago.  I 
love that Nadella?s Microsoft is giving up on NIH.

[sqlite] SQLite Release 3.9.0

[Warren Young]

On Oct 14, 2015, at 4:48 PM, Craig Maynard  wrote:
> 
> I'm not sure what semantic versioning says about this

semver.org is not a very long page.

Spec point 2 covers this: it does not allow empty X, Y, or Z components.

> why not just drop the trailing .0 and call the release 3.9? That seems 
> cleaner and emphasizes the fact that this is a new minor version.

It also makes version numbers harder to automatically parse, which is part of 
the point of semver.

If all software used semver, your OS?s package manager could tell you about 
version incompatibilities, instead of leaving it to the linker or runtime.

[sqlite] OT: Oracle functions for SQlite

[Warren Young]

On Sep 13, 2015, at 3:06 AM, Domingo Alvarez Duarte  wrote:
> 
> Due the way sqlite manages it's source code (with fossil-scm) I propose to
> anyone that has any extension/custom sqlite code fork this project on github:

Fossil allows anonymous clones, and the Fossil server on sqlite.org is 
configured not to allow checkins from anonymous users.  Therefore, your local 
changes affect your personal repository only, just as with Github.  The only 
difference is that your personal fork of the repository isn?t automatically 
shared with the entire world.

When the time does come to share, Fossil has the concept of ?bundles,? which 
allows you to send a subset of your local repository to someone with permission 
to check it in, preserving all details of the change you made.  Not just file 
content changes, but also checkin comments, branch points, merges, etc.

Fossil bundles are far better than patch(1) files if your change is complicated 
enough to need more than a single checkin.

  http://fossil-scm.org/xfer/help?cmd=bundle

The simplest option is ?export --branch?, since that isolates your local 
changes from any made to the main repo?s trunk since your initial clone.

[sqlite] sqlite3 file as database

[Warren Young]

On Sep 14, 2015, at 8:38 AM, Stephen Chrzanowski  wrote:
> 
> There are many extensions of the same .. err..
> name(?)...value(?)..structure(?) that are completely different things.

It?s fairly bad in the electronics engineering world, where it seems like half 
the tools use *.sch for schematics and *.brd/pcb for printed circuit board 
layouts, but none of the tools agree on the format of the actual file data.  If 
you have two such apps installed, you have to make a hard choice about which 
app becomes the default to open such files, and occasionally have to fix it 
when updating the other app, as it takes over the extensions again.

This widespread unwillingness to get beyond the 8.3 limits, particularly on 
Windows, is annoying.  We haven?t had to worry about compatibility with 
3-character file extensions since Windows NT 3.5 and Windows 95, two decades 
ago now.

Call your files *.myspiffyapp, or something completely unique, please.  Just 
because the data inside is managed by SQLite doesn?t mean all SQLite-based apps 
would like to open your app?s files.

[sqlite] sqlite3 file as database

[Warren Young]

On Sep 14, 2015, at 1:02 PM, Tim Streater  wrote:
> 
> On 14 Sep 2015 at 19:29, Warren Young  wrote: 
> 
>> We haven?t had to worry about compatibility with
>> 3-character file extensions since Windows NT 3.5 and Windows 95, two decades
>> ago now.
> 
> Of course in a sensible world, OS providers would all have implemented a 
> common metadata API, and no one would need or use extensions.

There have been many such APIs and file formats.  HFS (creator+type code), 
IPTC/EXIF/XMP/Dublin Core, EDI (balkanized into EDIFACT, X12, ODETTE?), MARC 
records (similarly balkanized), etc.

They?re all ?standards? in the XKCD sense:

  https://xkcd.com/927/

[sqlite] Creating a stable database

[Warren Young]

On Sep 25, 2015, at 11:59 AM, Richard Hipp  wrote:
> 
> On 9/25/15, Aaron Digulla  wrote:
>> 
>> I was wondering if it was possible to create the same database (= no binary
>> difference) twice with sqlite.
> 
> It works fine to create identical database files when I try it:
> 
>   drh at bella:~/sqlite/bld$ ./sqlite3 db1drh at bella:~/sqlite/bld$ ./sqlite3 db2  dump.sql
sqlite3 a.db < dump.sql
sqlite3 b.db < dump.sql

If a.db differs from b.db in that condition, then narrowing the test case down 
by hand-editing dump.sql would be the way to find out why the difference is 
occurring.

If no difference occurs in this case, then it sounds like your current method 
to create the DBs isn?t identical, Aaron.

Another way to attack it would be:

sqlite3 my-a.db .dump > a-dump.sql
sqlite3 my-b.db .dump > b-dump.sql
diff -u ?-dump.sql

If that results in differences, it will probably clue you into why the 
difference is occurring.

If there is no difference in the dump outputs, then there is a structural-only 
change to the sqlite DB files, which suggests that the difference might be 
purely structural.  Perhaps a VACUUM would fix it.  Or, just dump and re-load, 
as above.

[sqlite] Windows A and W APIs dual support

[Warren Young]

On Feb 12, 2016, at 1:49 PM, Clemens Ladisch  wrote:
> 
> Olivier Mascia wrote:
>> Are there Windows platforms, supported by SQLite source code of course, 
>> where the 'W' version of the APIs are not available?
> 
> Once upon a time, SQLite supported Windows 95/98/Me.

The DOS-based versions of Windows still have the ?W? functions for binary 
compatibility with the NT-based versions, but for the most part they treat 
their arguments according to the 8-bit code page or MBCS rules, which means you 
generally get garbage output when you feed in UCS-2.

There are a few exceptions: https://support.microsoft.com/en-us/kb/210341

Note that Windows didn?t move from UCS-2 to UTF-16 until Windows 2000, which is 
effectively after the development time of the DOS-based versions of Windows.  
(There?s a tiny overlap there with Windows ME, but that?s last-gasp stuff.)

I assume if you pass strings using characters beyond the BMP to the ?16? APIs 
in SQLite, they would do the wrong thing on Windows NT 3.x and 4.x systems, too.

I doubt there would be much crying if SQLite dropped the ?A? support.  I 
suspect the only reason SQLite still has it is that it?s more work to remove it 
than to leave it alone.

[sqlite] Windows A and W APIs dual support

[Warren Young]

On Feb 12, 2016, at 4:42 PM, Scott Robison  wrote:
> 
> I find it kind of interesting that Microsoft takes a lot
> of (deserved) flack for not adhering to standards, yet UTF-8 came about
> specifically because some didn't want to use UCS-2

?for good reason.  UCS-2/UTF-16 isn?t compatible with C strings.  I know you 
know this, but it?s a huge consideration.  Outside of Mac OS Classic and a few 
even smaller enclaves, C and its calling standards were the lingua franca of 
the computing world when Unicode first came on the scene, and those enclaves 
are now all but gone.

We?ll be living with the legacy of C for quite a long time yet.  Until C is 
completely stamped out, we?ll have to accommodate 0-terminated strings somehow.

> Had Microsoft come up with it first, I'm sure they'd be crucified by some of
> the same people who today are critical of them for using wide characters
> instead of UTF-8!

I think if we were to send a copy of the Unicode 8.0 standard back to the early 
1960s as a model for those designing ASCII, Unicode would look very different 
today.

I think the basic idea of UTF-8 would remain.  Instead of being sold as a 
C-compatible encoding, we?d still have a need for it as a packed encoding.  A 
kind of Huffman encoding for language, if you will.

But, I think we?d probably reorder the Unicode character points so that it 
packed even more densely on typical texts.  Several of the ASCII punctuation 
characters don?t deserve a place in the low 7 bits, and we could relocate the 
control characters, too.  We could probably get all of Western Europe?s 
characters into the lower 7 that way.

The next priority would be to pack the rest of the Western world?s characters 
into the lower 11 bits.  Cyrillic, Greek, Eastern European accented Latin 
characters, etc.

That should still leave space for several other non-Asian, non-Latin character 
sets.  Devanagari, Hebrew, Arabic?pack as many of them in as we can.  We should 
be able to cover about half the world?s population in the same space as UCS-2, 
while allowing most Western texts to be smaller, thoroughly outcompeting it.

UCS-2 feels like the 90?s version of ?640 kB is enough for everything!? to me, 
and UTF-16 like bank switching/segmentation.  We?re going to be stuck with 
those half-measure decisions for decades now.  Thanks, Microsoft.

The POSIX platforms did the right thing here: UTF-32 when speed matters more 
than space, and UTF-8 when space or compatibility matters more.

> Note: I still wish [Microsoft] supported UTF-8 directly from the API.

If wishes were changes, I?d rather that all languages and platforms supported 
tagged UTF-8 and UTF-32 strings, with automatic conversion as necessary.  Pack 
your strings down as UTF-8 when space matters, and unpack them as UTF-32 when 
speed matters.  Unicode could define a sensible conversion rule set, similar to 
the way sign extension works when mixing integer sizes.

Since the Unicode Consortium has stated that Unicode won?t grow beyond 2^21-1 
code points to prevent UTF-8 from going beyond 4 bytes per character, that tag 
could be an all-1s upper byte.  The rule could be that if you pass at least 4 
bytes to a function expecting a string, the buffer length is evenly divisible 
by 4, and the first 32-bit word has 0xFF on either end, it?s a tagged UTF-32 
value.  Otherwise, it?s UTF-8.

Simple and straightforward.

Too bad it will never happen.

[sqlite] Performance comparison between SQLite and SQL Server?

[Warren Young]

On Feb 15, 2016, at 1:02 AM, Clemens Ladisch  wrote:
> 
> SQLite uses a much simpler locking scheme that can be faster if there
> aren't concurrent accesses.  SQL Server has higher concurrency.

SQLite also doesn?t have the IPC overhead of a client/server DBMS, so if you 
don?t need concurrency or remote access, SQLite can be faster, since all data 
is moved around inside a single process.

[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] 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] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Warren Young]

On Jan 8, 2016, at 12:39 AM, Darren Duncan  wrote:
> 
> 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.

Yes.

> one can just list the bound values separately / afterwards rather than having 
> to rewrite the SQL to interpolate those values.

Of course, but the question is not whether a caller *can* do this, it?s whether 
the caller *should have to* do this.

[sqlite] hard links and SQLite

[Warren Young]

On Jan 11, 2016, at 11:25 AM, Felipe Gasper  wrote:
> 
>   To prevent race conditions where a 2nd process accesses a newly-created 
> SQLite file before the creator process can set up the schema

On POSIX systems, you can securely create a temp file that only your user can 
see via the mkstemp(3) C library call.  SQLite will happily open the resulting 
0-byte file, allowing you to create your schema inside it.  Then when the file 
is set up, you can move it into the desired location and change its file modes 
so that the other processes can open it.

There must be an equivalent of mkstemp() on Windows, doubtless taking 3 times 
as many parameters and with a function name 4 times as long. :)

[sqlite] hard links and SQLite

[Warren Young]

On Jan 11, 2016, at 11:57 AM, Stephan Beal  wrote:
> 
> On Mon, Jan 11, 2016 at 7:55 PM, Warren Young  wrote:
> 
>> There must be an equivalent of mkstemp() on Windows, doubtless taking 3
>> times as many parameters and with a function name 4 times as long. :)
> 
> sqlite exposes the functionality of fetching a temp file name using its
> mechanism, but i don't recall at the moment how it's done.

I see sqlite3_temp_directory(), but it isn?t documented here:

  https://www.sqlite.org/c3ref/funclist.html

so I don?t think you can count on it to remain available.

SQLite doesn?t use mkstemp(3), so it must be reinventing that particular wheel.

[sqlite] hard links and SQLite

[Warren Young]

On Jan 11, 2016, at 11:57 AM, Simon Slavin  wrote:
> 
> Just do BEGIN IMMEDIATE immediately after you open the database.

Doesn?t that set up a race condition?  What prevents the other process from 
opening the DB and running its own SQL if the OS?s scheduler happens to take 
the CPU away from the legitimate process between sqlite3_open_v2() and 
sqlite3_exec(?, ?BEGIN IMMEDIATE?, ?) ?

[sqlite] hard links and SQLite

[Warren Young]

On Jan 11, 2016, at 12:27 PM, Simon Slavin  wrote:
> 
> On 11 Jan 2016, at 7:06pm, Warren Young  wrote:
> 
>> On Jan 11, 2016, at 11:57 AM, Simon Slavin  wrote:
>> 
>>> Just do BEGIN IMMEDIATE immediately after you open the database.
>> 
>> Doesn?t that set up a race condition?  What prevents the other process from 
>> opening the DB and running its own SQL if the OS?s scheduler happens to take 
>> the CPU away from the legitimate process between sqlite3_open_v2() and 
>> sqlite3_exec(?, ?BEGIN IMMEDIATE?, ?) ?
> 
> Okay, so your concern is that the other process intrudes between the _open() 
> and the BEGIN.
> 
> If that's the case then I suggest that you create your new database 
> (including schema and any initial data) with a different filename.  Then once 
> this is complete, close it (as far as SQLite is concerned) then use your OS 
> calls to rename the file to whatever your database is normally called.

Which is what I suggested with my mkstemp(3) solution.

I neglected to say that you should close the DB file before moving it, though, 
so I guess it?s good to have it restated.

Another previously unstated detail is that you can leave the mkstemp-created 
file permissions alone until after your process has moved and re-opened the 
database.  That lets your process do further modifications to the DB before 
calling chmod on it to allow other users? processes access to it.

> My question is what the other process does.

The OP was vague about that, but I think the point of his current gymnastics is 
to prevent the other process from creating a rogue schema, or to insert 
compromising data into a correct schema.

To make it concrete, you could probably write a black hat process that could 
get in between the creation of a new Fossil DB file and the initial schema 
setup, then add an all-powerful Admin user with a known password.  When the 
fossil binary regains control from the OS, it will see that the ?users? table 
already exists, so it won?t install its own, leaving the DB file backdoored.

That?s why you have to use something like mkstemp() to create the DB file 
somewhere only your process can see it, and with permissions that allow only 
your user to open or modify it.

[sqlite] Are there practical limits to a not-so simple schema?

[Warren Young]

On Jan 13, 2016, at 5:29 AM, Simon Slavin  wrote:
> 
> My only concern with what you wrote is that you mention 100 tables.  In order 
> to find the table you specify SQLite has to go through a list of (hashed, I 
> think) table names, and going through an average of 50 of them per command 
> can be slow.

Wouldn?t that be log2(100) = 6.6 or log(100 = 4.6 maximum node visits?

Most hash table implementations have logarithmic lookup time, not linear.

[sqlite] Are there practical limits to a not-so simple schema?

[Warren Young]

On Jan 13, 2016, at 1:45 PM, Simon Slavin  wrote:
> 
> On 13 Jan 2016, at 7:36pm, Warren Young  wrote:
> 
>> Wouldn?t that be log2(100) = 6.6 or log(100 = 4.6 maximum node visits?
>> 
>> Most hash table implementations have logarithmic lookup time, not linear.
> 
> You're quite right.

No, not entirely. :)

Hash tables are not inherently balanced, so depending on the input data and the 
hashing scheme used, it could be as bad as 100 visits.  A good hashing 
algorithm reduces that worst case chance to near zero, but some amount of 
imbalance is to be expected.

The logarithmic values are best typical case, not worst case.

Contrast a red-black or AVL tree, which is *always* balanced.  That?s why C++ 
STL implementations use one of those two for std::map and such.  The efficiency 
guarantees in the C++ standard disallow implementing them in terms of hash 
tables.  Later (C++11) they added std::unordered_map and friends with different 
restrictions tat allow hash table implementations.

[sqlite] Setting SQLITE_OMIT_FLOATING_POINT has surprising undocumented consequences

[Warren Young]

For no especially good reason, I decided to turn off all SQLite features I?m 
not using now and which I have no plans to use in the future.

My current DB doesn?t use any FP columns, so I rebuild SQLite with 
SQLITE_OMIT_FLOATING_POINT and ran ran into a bunch of breakage:

1. The (double) cast on line 66942 of the current amalgamation in 
valueFromExpr() produces a warning.  Most of that block probably should go away 
if FP is disabled.

2. Similar problem on 138811 in FTS3: the cost estimator uses FP.

3. Setting this implicitly defines SQLITE_OMIT_TRACE, which removes 
sqlite3_profile(), even though that interface doesn?t use FP.  I didn?t look 
deeply into it, but apparently it does use FP internally.  

At minimum, this should be documented.

It would be better if SQLite decoupled the two reasons to disable FP: a. My 
processor has no FPU, so I don?t want *any* FP code in sqlite3.o; and b. I 
don?t use FP in my DB, so I want to disable REAL and related things, but it?s 
fine if SQLite uses it internally.  (Profiling, FTS3, etc.)  It?s actually the 
latter I was expecting when I set this build option.

4. If the current all-or-nothing approach to FP continues, setting this should 
probably implicitly set SQLITE_RTREE_INT_ONLY.  Currently, you get a complaint 
due to use of double on lines and 156404 and 158887 of the current amalgamation.

5. #4 happens whether you have SQLITE_ENABLE_RTREE defined or not.  I couldn?t 
work out why, but this tells me the r*tree code is being compiled in despite 
being unasked-for.

[sqlite] Setting SQLITE_OMIT_FLOATING_POINT has surprising undocumented consequences

[Warren Young]

On Jan 15, 2016, at 3:11 AM, Simon Davies  
wrote:
> 
> On 14 January 2016 at 22:31, Warren Young  wrote:
>> 
>> I rebuild SQLite with SQLITE_OMIT_FLOATING_POINT and ran ran into a bunch of 
>> breakage:
> 
> http://www.sqlite.org/compile.html#omitfeatures
> 
> SQLITE_OMIT_xxx options "may only be used when the library is built
> from canonical source, not from the amalgamation?

Fine:

fossil clone http://www.sqlite.org/cgi/src ~/museum/sqlite.fossil
mkdir -p ~/src/sqlite/head
cd ~/src/sqlite/head
fossil open ~/museum/sqlite.fossil
cp Makefile.linux-gcc Makefile
# edit TOP: the default should be ., not ../sqlite!
# edit OPTS: add -DSQLITE_OMIT_FLOATING_POINT
rm -f sqlite3.c# just to be sure
make

?and the thrust of what I wrote remains true: there?s a bunch of code in SQLite 
that tries to use double even though this option is enabled.

It actually dies trying to build the shell:

./src/shell.c: In function ?timeOfDay?:
./src/shell.c:172:5: warning: passing argument 2 of ?clockVfs->xCurrentTime? 
from incompatible pointer type [enabled by default]
 clockVfs->xCurrentTime(clockVfs, );
 ^
./src/shell.c:172:5: note: expected ?sqlite3_int64 *? but argument is of type 
?double *?

Realize that I?m reporting this mainly as a heads-up to the developers, not 
because I particularly want this fixed.  Every FPU-less platform I?ve ever 
developed for was too small to run SQLite in the first place.  I was just 
trying to remove code I don?t use.

Given that, I see several options:

1. Ignore the known breakage.

2. Declare that SQLite now requires FP, and remove the option.

3. Rework it like I proposed in the previous post: one option for completely 
FP-free builds, and a separate one to just remove FP features from the 
supported SQL language: no REAL columns, no likelihood(), no round(), etc.

It?s the last option I actually wanted, because in my app, an FP literal must 
be a bug, so I?d prefer that SQLite refused to accept it.

It?s hard to imagine another SQLite data type that someone would want to treat 
that way, except possibly BLOB.  But FP?  There are *many* applications where 
FP is not only not used, but also not *wanted*.

[sqlite] Using sqlite3.exe as a subprocess

[Warren Young]

On Jan 14, 2016, at 8:53 PM, Matthew Allen  wrote:
> 
>p = subprocess.Popen(["sqlite3.exe", "Database.sqlite"],
> stdout=subprocess.PIPE)

It looks like you?re trying to use both stdin and stdout, but you really only 
need stdout here, since sqlite3.exe will accept SQL or sqlite3 shell commands 
on its command line after the database argument.  You don?t need to feed it the 
commands over stdin.

In fact, you don?t even need to use stdout, if you?re willing to leave it all 
to the command shell:

   cmd.exe /c sqlite3 Database.sqlite .dump | sqlite3 NewDatabase.sqlite

Then replace Database.sqlite with NewDatabase.sqlite, optionally moving the 
former to a backup location first.

[sqlite] Find SQLITE_BUSY reason?

[Warren Young]

On Jan 21, 2016, at 5:25 AM, Daniel Polski  wrote:
> 
> Den 2016-01-21 kl. 11:30, skrev Simon Slavin:
>> On 21 Jan 2016, at 9:44am, Daniel Polski  wrote:
> 
>>> PRAGMA journal_mode = WAL;
>> Once the database is in WAL mode that fact is saved in the file.
> 
> We actually can't be sure that the database is already in WAL mode.

Why not?  Didn?t you create it?

If the user took it back out of WAL mode manually, presumably they know what 
they?re doing.

If prior versions of your software didn?t use WAL, but migrated to it later, 
move the PRAGMA to your normal DB upgrade process.

(I?m presuming your schema has to change occasionally to cope with new 
features, changed features, and removed obsolete features.  Set the PRAGMA in 
that process.)

>> PRAGMA busy_timeout = 10 * 1000;
>> 
>> then just take the first _LOCKED or _BUSY as a failure.
> 
> We already tried that, but that doesn't seem to have any effect. If it fails 
> locked or busy the timeout doesn't seem to make any difference. (This may as 
> well be a quirks of PHP on top of it)

That means someone is holding the database locked, such as with an unclosed 
BEGIN TRANSACTION call.  This *is* the information you?re looking for.  
Switching from the built-in retry logic to custom logic doesn?t help.  You have 
to fix the process that holds the lock too long.

With SQLite?s lack of row-level locking, your usage pattern should distill to 
?get in, get done, and get out, ASAP.?  Many fine-grained queries are better 
than heroic multi-statement queries that change the world.

[sqlite] Find SQLITE_BUSY reason?

[Warren Young]

On Jan 21, 2016, at 9:01 PM, Rowan Worth  wrote:
> 
> On 22 January 2016 at 06:33, Warren Young  wrote:
> 
>> get in, get done, and get out, ASAP.
> 
> To a point I agree, but in reality there's a fixed amount of work involved
> with each write transaction. I recently profiled an operation involving
> ~75,000 rows that took ~8.5 minutes to complete, and found that 80% of the
> time was spent waiting for COMMIT to complete. Rewriting the code so that
> all the work happened in a single transaction immediately dropped the
> overall time down to ~1.66 minutes.

Yes, it?s a well known fact that batching up many INSERTs and such into a 
single transaction makes things faster.  It?s why sqlite3 .dump wraps its 
output in a transaction, for example.

But implicit in that comment is the assumption from up-thread that we?re using 
a 10 second timeout, which would result in the BUSY errors the OP is seeing.  
If the OP has talked himself into large transactions for reasons similar to 
yours and is unwilling to split them, then his timeout needs to be *at least* 
as long as the longest possible transaction running time.

So, more like 2 minutes in a case like yours, not 10 seconds.

[sqlite] Store the value from a variable into a field in database table

[Warren Young]

On Jan 22, 2016, at 6:15 AM, Hick Gunter  wrote:
> 
> You can either printf() the statement to insert the value into the text

Please don?t say such things to newbies.  It?s fine for an integer, but he?s 
going to move on to strings next and then he?ll have a SQL injection 
vulnerability.

[sqlite] Find SQLITE_BUSY reason?

[Warren Young]

On Jan 22, 2016, at 11:54 AM, James K. Lowden  
wrote:
> 
> On Fri, 22 Jan 2016 06:24:08 +
> Simon Slavin  wrote:
> 
>> This is, of course, all about waiting for a rotating disc to be in
>> the right place.
> 
> All true, but I think you're exaggerating if you're implying that's
> what the user will see.  A call to write(2) doesn't necessarily involve
> the rotating media; it merely transfers the data from userspace to the
> kernel buffer cache (using Linux as an example).  Even fsync, on
> consumer-grade disks, may return when the data have been flushed to the
> device's cache, before they come to rest on the platter.  Both buffers
> ameliorate the effects of latency and track-to-track seek.  

First, SQLite *does* fsync() each transaction before returning, on purpose, to 
provide the D in ACID:

  https://www.sqlite.org/lockingv3.html

Second, even if you?re using the sort of consumer-grade disk that lies about 
fsync [*] you still have seek time to cope with.  The track on disk where the 
data lands is probably not the track where the indices and other metadata 
structures live.  The head may have to go back and forth several times to 
complete a transaction.

Even when the disk lies about fsync, that cost eventually has to be paid.  If 
it?s left unpaid too long, the write buffer fills up, and then SQLite will have 
to wait for buffer space to open up.

> Given...the capacity of the raw disk (about 100 MB/s)

You mean transfer rate, not capacity, of course.

But you only get 100 MByte/sec in linear reads, not random writes, which is 
what multi-track writes effectively are.  Typical disks drop into the single 
digits of MByte/sec on random writes.


[*] See "Disks from the Perspective of a File System?, by Marshall Kirk 
McKusick [**] in ACM Queue: https://queue.acm.org/detail.cfm?id=2367378 

[**] https://en.wikipedia.org/wiki/Marshall_Kirk_McKusick

[sqlite] Bug: Successfully committed transaction rolled back after power failure

[Warren Young]

On Jan 25, 2016, at 8:47 AM, Richard Hipp  wrote:
> 
> The feedback I receive is that most users of SQLite would much rather
> avoid the extra directory syncs, even if it means having the last
> transaction rollback following a power loss.

Why not do the directory fsync in sqlite3_close_v2()?

As I understand the original problem, this is happening in a system doing a 
controlled shutdown, not a crash or spontaneous reboot.

I?m close to this problem at the moment because we recently switched to using 
WAL mode by default, and I noticed that if there is a code path that causes 
sqlite3_close_v2() to be skipped, the WAL file remains behind, causing a 
SQLITE_NOTICE_RECOVER_WAL complaint when the app starts back up again.

If the last writer to a SQLite DB closes its connection down gracefully, there 
should be no uncertainty about whether all transactions have durably hit the 
disk.

I can live with such uncertainty if the last writer *doesn?t* gracefully close 
its connection.  That?s kind of concomitant with using an in-process DBMS.

(Contrasting with a client-server DBMS, where durability is not compromised if 
a remote client disappears after COMMIT without gracefully closing its TCP 
connection afterward.)

[sqlite] DB-Journal

[Warren Young]

On Mar 2, 2016, at 6:00 PM, Simon Slavin  wrote:
> 
> On 2 Mar 2016, at 1:48pm, Itxaso Perez  wrote:
> 
>> - How can I 'execute' the DB-Jounal file just to try to 'correct' the
>> database before execting NHibernate?
> 
> Having a journal file on disk should not crash your app.

It is possible that NHibernate?s SQLite driver is calling

  sqlite3_config(SQLITE_CONFIG_LOG, ?)

in order to catch SQLite errors.  This works since most calls to sqlite3_log() 
signal some kind of error, so it is easy to think of the log message callback 
as an error handler.

But, there are a few things that SQLite reports via this mechanism such as 
SQLITE_NOTICE_RECOVER_ROLLBACK and SQLITE_NOTICE_RECOVER_WAL, which are not 
errors, just FYI notices.  They should not kill the program.

Another odd case is SQLITE_ERROR with the log string containing ?syntax error?, 
which happens when your SQL is incorrect.  I log that without exiting in my 
programs because it just turns into an empty result set, which my program can 
cope with.  Thus, I?d rather have the program continue running instead of 
immediately dying.

(I wish there were a SQLITE_SYNTAX_ERROR constant to separate this case out.)

[sqlite] .DUMP output compatibility

[Warren Young]

On May 5, 2016, at 4:56 PM, Tony Papadimitriou  wrote:
> 
> Windows!

So install Cygwin.

There may be more to it than the quoting style.  I?ve used the following script 
for moving data the other direction (MySQL to SQLite):

  https://gist.github.com/esperlu/943776

You might have to create the inverse of some of those transformations to get 
MySQL to accept the data.

[sqlite] SQLite custom function for regular expression using c/c++

[Warren Young]

On May 12, 2016, at 7:36 AM, Bhagwat Balshetwar  wrote:
> 
> Can you provide me link of PCRE support for SQLite. I need to install it on
> Linux RHEL ES Release 4 with i686.

First Google result for ?sqlite pcre?:

  https://github.com/ralight/sqlite3-pcre

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[Warren Young]

On May 18, 2016, at 4:23 AM, Gerald Bauer  wrote:
> 
>> I would be interested what you find wrong about Git and is better in your
>> version control system.
> 
> [1] http://www.fossil-scm.org/xfer/doc/trunk/www/fossil-v-git.wiki

Also http://fossil-scm.org/xfer/doc/tip/www/quotes.wiki

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[Warren Young]

On May 18, 2016, at 4:43 AM, Kees Nuyt  wrote:
> 
> On Wed, 18 May 2016 11:39:28 +0200, Cecil Westerhof
>  wrote:
> 
>> I would be interested what you find wrong about Git and is better in your
>> version control system.
> 
> Check the archives of the fossil-users mailing list

Links to a few of the wider-ranging Git vs Fossil threads in recent years:

  https://goo.gl/rVzYTx
  https://goo.gl/8xKoZy
  https://goo.gl/RPJLEq
  https://goo.gl/Gq3Cga

One of those threads didn?t start out as ?Fossil vs Git,? but ended up there 
eventually.  It?s nearly inevitable when someone brings up Git on the Fossil 
mailing list. :)

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[Warren Young]

On May 18, 2016, at 11:04 AM, Jonathan Moules  
wrote:
> 
> I currently use Mercurial where I have a choice.
> I don't seem to be able to find much about Fossil v's Mercurial.

Best take it up on the Fossil mailing list.

> This blog post looked interesting though:
> http://www.omiyagames.com/farewell-fossil-version-control/

That blog post was pretty thoroughly rebutted on the Fossil mailing list last 
year:

  https://goo.gl/eX2tBO

Re: [sqlite] Help needed for validating SQLite under Linux/Cygwin

[Warren Young]

On 11/20/2012 06:42, brijesh_philips wrote:


I need to validate few tools including Sqlite on Linux environment.
Currently i'm using "cygwin" for validating the tools.


Don't do that.

Cygwin tries hard to be Linux compatible, but there are several cases 
where Cygwin simply cannot behave like Linux because the underlying 
Windows kernel doesn't have proper POSIX semantics, and it's too 
difficult -- even impossible sometimes -- to emulate those semantics on 
top of Windows semantics.


If you want to test Linux behavior, test on a Linux machine.  If you 
must use a Windows host for this, get one of the many virtual machine 
systems, and set up a Linux VM.  If you're already on Windows 8, you may 
be able to use its new Client Hyper-V feature for this.  If not, you can 
choose from VMware, VirtualBox, Parallels...


Here is just one example of an incompatibility between Cygwin and Linux, 
which is directly relevant to SQLite:


http://stackoverflow.com/questions/11007024/


- Do i need to include additional file as part of the amalgation file while
building the SQLite library?


I'm not sure what you're asking.  Are you asking if the library you 
successfully built is somehow incomplete?


The whole point of the amalgamation distribution is that it *is* 
functionally complete.



- Link from where i can download TCL Test suite (scripts) to test SQLite
version 3.7.14.1 for linux?


https://www.sqlite.org/testing.html

I found that with Google for "SQLite Tcl test suite".  What search terms 
did you try?


And yes, I'm aware that I did not give you a direct link to the test 
code.  If you cannot figure out what "...contained in the same source 
tree as the SQLite core..." means, you have no business doing this 
evaluation.



- Can we validate SQLite under cygwin?


Sure.

...and when you have finished your validation tests, you will have 
validated that SQLite works within certain known parameters *on Cygwin*.


You will also have learned enough to be able to make some informed 
guesses about how SQLite might behave on Linux, but you will not know if 
these guesses are correct until you actually test on Linux.

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

Re: [sqlite] Bug in Cygwin SQLite: temporary table creation fails

[Warren Young]

On 12/26/2012 22:19, Daniel Colascione wrote:

The reason this operation fails is that SQLite cannot create a
temporary file in "C:\Windows",


...unless you run as Admin, which is why you don't hear this complaint 
come up more often.


Thanks for diagnosing this in any case.  It's clarified some details of 
what's going on for me.



In a Cygwin environment, SQLite should avoid GetTempPathW and instead
use unixGetTempname to find the temporary directory, translating paths
as appropriate.


Actually, you *can* get SQLite to do that.  There are two ways to build 
SQLite under Cygwin, which I call "Windows mode" and "Unix mode".  The 
current Cygwin SQLite package builds it in Windows mode so that Cygwin 
programs linked to it can interoperate with native Windows programs also 
using SQLite.


If you rebuild SQLite in Unix mode, I think your immediate problem will 
be solved, but you might buy yourself a new problem along these lines:


http://stackoverflow.com/questions/11007024/#11887905

As indicated in that answer, until Cygwin gets a method for selecting 
mandatory locking, the official Cygwin SQLite package will continue to 
be built in Windows mode.

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

Re: [sqlite] Bug in Cygwin SQLite: temporary table creation fails

[Warren Young]

On 12/27/2012 17:43, Daniel Colascione wrote:

Yep: there was recently a long thread on the Cygwin mailing list
about which mode to use for the official sqlite3 package:


Yeah, I know, I was there.

(Hello from the Cygwin SQLite package maintainer.)


I hope you'll be able to fix this bug.


In the end, building in Unix mode is the right thing.  We just can't 
safely do that right now.


Until Cygwin gets the facilities that would make switching to Unix mode 
a sane choice, you have to decide:


a) Will you build SQLite in Unix mode locally, and administratively 
ensure that there is no attempt to mix native Windows and Cygwin users 
of a single SQLite DB? Or:


b) Will you accept that the current impure Windows mode build has its 
plusses and minuses, and accept the former happily and cope with the latter?

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

Re: [sqlite] Bug in Cygwin SQLite: temporary table creation fails

[Warren Young]

On 12/27/2012 18:08, Daniel Colascione wrote:

Yes, we have to accept the minuses we can't easily fix, but this
one, we can can. Porting the Unix-mode temporary file logic to the
Windows build seems workable enough.


"Who is this 'we,' kemosabe?"

You think it's easy, try it.

You'll find that you can't simply make a one-line patch that swaps a 
unixGetTempname() call in for an osGetTempPathW() call.  It compiles and 
then fails to link, because unixGetTempname() is ifdef'd out 5000 lines 
away from the actual definition.  You end up duplicating about 80 lines 
of code, simply because that's far easier than doing the ifdef 
gymnastics to reuse what's already there.


We aren't going to tell Mr. Hipp about this patch, lest he barf all over 
his shoes.  (Shhh.)


Here's the patch, for those with strong stomachs:

  http://etr-usa.com/cygwin/sqlite3/src.patch

If you can come up with a cleaner patch, by all means, lay it on me.

Here are the packages built with it, for testing:

  http://etr-usa.com/cygwin/sqlite3/libsqlite3-devel-3.7.15.1-1.tar.bz2
  http://etr-usa.com/cygwin/sqlite3/libsqlite3_0-3.7.15.1-1.tar.bz2
  http://etr-usa.com/cygwin/sqlite3/sqlite3-3.7.15.1-1-src.tar.bz2
  http://etr-usa.com/cygwin/sqlite3/sqlite3-3.7.15.1-1.tar.bz2

Relative to the current 3.7.13-1 packages on the Cygwin repo mirrors, 
this also changes:


  - Upgrade to 3.7.15.1 upstream
  - Enable a bunch of build options others wanted: column data, FTS...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in Cygwin SQLite: temporary table creation fails

[Warren Young]

On 12/27/2012 21:17, Joe Mistachkin wrote:


I just looked at the patch briefly and I'm wondering if we could use the
existing GetTempPath[A/W] as another fallback directory?


The way I see it is, we are migrating from a hybrid Windows/Cygwin mode 
toward a purer POSIX style.  Eventually, I want Cygwin SQLite to behave 
just like SQLite on any Linux variant, except that it happens to still 
interoperate well with native Windows programs.



Also, is "cygwin_conv_path" superior to "cygwin_conv_to_win32_path" in
some way?


It reflects an API change between Cygwin 1.5 and Cygwin 1.7.  The old 
API still exists -- if it didn't, we'd be calling it Cygwin 2.x -- but 
you get a deprecation warning from the compiler if you call it.  The new 
API is 100% equivalent, it just offers a more flexible API.


I've posted this patch here twice before, but since upstream hasn't 
accepted it, I keep patching Cygwin SQLite.  I guess I should try 
posting it to sqlite-devel.  I believe when I first developed the patch, 
that list was closed to "outsiders" like me.  Now that I've got patching 
it in down as part of the normal build procedure, I haven't bothered 
reposting it until now.

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

Re: [sqlite] SQLite4 questions

[Warren Young]

On 3/7/2013 16:14, Richard Hipp wrote:

On Thu, Mar 7, 2013 at 6:10 PM, Jeff Archer 

Re: [sqlite] Cygwin compilation error

[Warren Young]

On 4/17/2013 08:06, Fulvio Esposito wrote:

I'm trying to use sqlite (tried 3.6 and the latest) in a project using
cygwin


I just released SQLite 3.7.16.2-1 to the Cygwin repo mirrors a few days 
ago.  If your mirror of choice is in sync, you can download it now.


Is there some reason you can't use that, or were you just not aware that 
I'd finally gotten around to replacing the 3.7.13-1 release?



I cannot manage to compile it.


I didn't have any trouble on 32-bit Cygwin.

64-bit Cygwin required a bit of hand massaging before compilation since 
upstream is shipping outdated autoconf outputs, however.


(Richard, if you're reading this, you should upgrade Automake to at 
least 1.10.  Previous releases don't understand 64-bit Cygwin, so we 
have to replace config.guess.  Additionally, Autoconf 2.69 doesn't like 
some outdated constructs in your configure.ac file.  If the syntax were 
up to date, that would have allowed a second choice for fixing this 
portability problem.)



sqlite3.c:30467: error: initializer element is not constant
sqlite3.c:30467: error: (near initialization for `aSyscall[0].pCurrent')
sqlite3.c:30467: error: initializer element is not constant
sqlite3.c:30467: error: (near initialization for `aSyscall[0]')
sqlite3.c:30479: error: initializer element is not constant
sqlite3.c:30479: error: (near initialization for `aSyscall[1]')
sqlite3.c:30487: error: initializer element is not constant


I'd guess you have a competing definition for the SYSCALL macro 
somewhere.  It's easy to test.  Add this at line 30445:


#else
#  error Fix your toolchain!

If you get the new error on retrying the compile, I'm right, and you 
need to find out where it is and why it's being invoked.


Do you have multiple gcc's on your system?  If so, are you *certain* 
you're using Cygwin's gcc, and not, say, MinGW's, or some 
cross-compilation toolchain?

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

Re: [sqlite] Compiling libtclsqlite3.so on Cygwin

[Warren Young]

On 5/13/2013 16:36, Keith Christian wrote:

gcc -o libtclsqlite3.so -shared tea/generic/tclsqlite3.c -lpthread -ldl
-ltcl


Don't build it that way.  It appears that the TEA build system sees 
Cygwin and thinks "oh, this is Windows, so it must be VC++ or MinGW". 
That prevents it from linking to the Cygwin SQLite library.


This seems to do the right thing:

... at the top-level of the source tree:
$ cd tea
$ ./configure --with-system-sqlite
$ make

Do a "make clean" before the last step if you have any *.o files laying 
around.  The shipped build system isn't smart enough to rebuild 
everything when you reconfigure with a very different set of compile and 
link options.

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

Re: [sqlite] Compiling libtclsqlite3.so on Cygwin

[Warren Young]

On 5/14/2013 09:14, Keith Christian wrote:


couldn't load file "./tclsqlite3.o": Exec format error


From the Tcl manual: http://tmml.sourceforge.net/doc/tcl/load.html

"...such as a .so file under Solaris or a DLL under Windows."

Not *.o!

My Tcl is awfully rusty, but I managed to get it to load with:

load libsqlite3.7.16.2.dll "SQLite3"

I have no idea what to do with it having loaded it.  (Again, the rust.)

You're rapidly getting off-topic here, though.  There's a good chance 
that if you have further questions that they belong on a Cygwin or Tcl 
forum.

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

Re: [sqlite] An "unable to open database file" error that has nothing to do with opening database file

[Warren Young]

On 6/4/2013 09:22, Philip Goetz wrote:


Is it caused by using a 32-bit sqlite3?


> How does a 32-bit app access a 5G file?

According to https://www.sqlite.org/limits.html, SQLite doesn't really 
have a 32-bit limit.  It's not trying to load all 5 GiB into RAM at 
once.  It manipulates the DB file in chunks, so the limit is in the 
terabytes, regardless of platform.



Is there a 64-bit Windows or Linux SQLite available?


There is a 64-bit Cygwin in development, which you can download here:

ftp://cygwin.com/pub/cygwin/64bit/

It is far from complete, but my SQLite packages are already in it.

I'd suggest that you try one or both of these things first, though:

1. Say "pragma temp_store=2" before your other SQL commands.  This will 
force SQLite to use in-memory temporary tables, avoiding disk.  It may 
be that this will bypass the bug that causes the error.


If this succeeds, I'd like to hear back.  There's currently been some 
speculation on the Cygwin mailing list as to whether in-memory temp 
files are suitable for such large DBs on 32-bit systems.  It would be 
nice to have a concrete test that shows that it does work.


2. Try the 32-bit Cygwin SQLite testing packages, here:

http://etr-usa.com/cygwin/sqlite3/

You may need to download up to 4 different files, three of which are 
buried a level deep in that tree.  Basically, you want to unpack any of 
the files called *3.7.17-2* into the root of your Cygwin tree.  Only 
download files corresponding to the ones you already have downloaded. 
You probably haven't installed the repo version of the -debuginfo 
package already, for example, so don't bother installing the test 
version, either.


These test packages have a purpose entirely different from what it 
sounds like your issue is, but they happen to be built with the 
"TEMP_STORE=2" mode by default.  (Don't count on this build choice to 
make it to the official release.  I haven't decided yet.)


For what it's worth, the main purpose of these packages is that they are 
trying to make Cygwin SQLite switchable between POSIX or BSD advisory 
locks and Windows mandatory locks.  The current method interoperates 
well with native Windows SQLite, but not with programs ported to Cygwin 
that assume POSIX advisory locks.  To test this new feature, you need to 
install Cygwin 1.7.19.  You should also skim this Cygwin mailing list 
thread:


http://cygwin.com/ml/cygwin/2013-06/msg00034.html

If nothing else, the second half of this post:

http://cygwin.com/ml/cygwin/2013-06/msg00078.html

Beware, these packages do not pass the SQLite test suite.  (That mainly 
because no one has yet gotten the test suite to run under Cygwin!)  Back 
up your DB first!

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

Re: [sqlite] An "unable to open database file" error that has nothing to do with opening database file

[Warren Young]

On 6/5/2013 11:25, Philip Goetz wrote:

I suspect it's intercepting calls to the filesystem.


Yes, the Cygwin DLL does translate POSIX paths to Windows paths 
internally.  Then it calls the native APIs for you to give you the POSIX 
effect you asked for via the DLL.


Part of the fun here is that the Cygwin DLL *also* tries to cope with 
Windows style paths, and the Windows APIs *also* sometimes cope with 
forward slashes.


Because relying on this can cause all kinds of havoc, the DLL now warns 
you about it the first time you try it in a session:


$ ls c:/
cygwin warning:
  MS-DOS style path detected: c:/
  Preferred POSIX equivalent is: /c
  CYGWIN environment variable option "nodosfilewarning" turns off this 
warning.

  Consult the user's guide for more details about POSIX paths:
http://cygwin.com/cygwin-ug-net/using.html#using-pathnames

Not all code paths cause this warning, though, and sometimes the hack to 
cope with backslashes doesn't get run at all.  I wouldn't expect it to 
even try in this case, since the first part of the path looks POSIXy.


Bottom line, I suspect there is a bug in SQLite here.  It shouldn't be 
using backslashes in a Cygwin build.

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

Re: [sqlite] An "unable to open database file" error that has nothing to do with opening database file

[Warren Young]

On 6/6/2013 21:56, Philip Goetz wrote:


I think the problem is that the Cygwin distribution has the wrong
version of SQLite, one built for unix.


Nope.  And even if true, it wouldn't be the right explanation.

There are two major ways to build SQLite on Cygwin:

1. By default, building SQLite under Cygwin gets you a special 
Cygwin-aware mode, where SQLite bypasses the Cygwin DLL for some things, 
calling the Win32 API directly.  This is how the current[*] official 
binaries in the Cygwin distro are built.  Such a build still uses POSIX 
APIs for opening files, though, so the paths go to the Cygwin DLL first, 
and then after translation, to the Win32 API.  Since such a built knows 
its running on Windows, it shouldn't be using illegal characters in 
generated file names.


2. You can also build SQLite in a pure POSIX mode, with no direct calls 
to Win32 at all.  This mode is more compatible with other POSIX programs 
running under Cygwin, but less compatible with native Win32 builds of 
SQLite.  This distinction is irrelevant, however, because the file 
opening path is the same as with the Cygwin-aware build.



[*] (You can find test builds of SQLite for Cygwin built in Unix mode 
instead of Cygwin mode, but there hasn't been an official one in many 
months, which was quickly replaced due to the problems it caused.  We 
may be switching the official builds back to Unix mode soon, providing 
we can fix those problems, since the special Cygwin mode of SQLite 
causes its own problems.  It's one of those "having your cake and eating 
it too" kinds of things.)

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

Re: [sqlite] An "unable to open database file" error that has nothing to do with opening database file

[Warren Young]

On 6/9/2013 07:42, Philip Goetz wrote:


/path/to/sqlite3.exe db.sqlite

on the command line just hangs.


It sounds like you're trying to do that from Cygwin's mintty shell.  The 
Windows Console infrastructure isn't particularly robust, so when a 
third-party program like mintty tries to run a native Win32 console app, 
it sometimes does things like what you're seeing.


Run the native Win32 sqlite3.exe from cmd.exe instead.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An "unable to open database file" error that has nothing to do with opening database file

[Warren Young]

On 6/9/2013 05:21, Stephen Chrzanowski wrote:

Warren, when you say "we may be switching the official builds back to Unix
mode soon", you're referring only to the Cygwin builds, correct?


Of course.


The Win32 versions will still be generated as is?


I only maintain the Cygwin packages.  I have no control over packages 
that appear on sqlite.org.

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

Re: [sqlite] Minimizing Internal Function Calls in SQLite

[Warren Young]

On 7/30/2013 06:11, Richard Hipp wrote:


Wow.  What embedded system is it that doesn't support a call stack that is
*only* 35 levels deep?


The 12-bit PIC microcontrollers are limited to 2-level stacks, and many 
of the smaller 14-bit PICs are limited to 8-level stacks.


Not that I expect that Andrew is trying to get SQLite running on a small 
PIC.  Such a processor likely doesn't even have enough code space to run 
SQLite in the first place.


There is a serious point to this, which is that embedded processors can 
go to serious extremes in the name of efficiency or cost.  It wouldn't 
surprise me to learn that someone is making a 32-bit uC with an MMU that 
nevertheless has a 32-level or smaller hardware call stack.

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

Re: [sqlite] Bug report: Sqlite seg fault, probably after database gets corrupt

[Warren Young]

On 8/1/2013 12:20, Brian Vincent wrote:

Let me first say that we sometimes see databases that go corrupt.  I
haven't pinpointed the cause yet,


This may be enlightening: "How to Corrupt an SQLite Database File"

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

Re: [sqlite] Version 3.8.1 beta

[Warren Young]

On 9/30/2013 07:39, Richard Hipp wrote:

SQLite version 3.8.1 will be published before too much longer, probably.
You can find beta versions at http://www.sqlite.org/download.html

Comments, criticisms, and third-party testing of this beta is appreciated.


It can't build a shared library (DLL) on Cygwin any more:

./libtool --mode=link gcc   -g -O2 -DSQLITE_OS_WIN=1 -I. -I./src 
-I./ext/rtree -D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DNDEBUG 
-I/usr/include -DSQLITE_THREADSAFE=1 -DSQLITE_OMIT_LOAD_EXTENSION=1 
-o libsqlite3.la sqlite3.lo  \

 -rpath "/usr/local/lib" -version-info "8:6:8"
libtool: link: warning: undefined symbols not allowed in i686-pc-cygwin 
shared libraries


If new declarations sans definitions weren't actually added, perhaps 
someone forgot to add:


#ifdef _WIN32
__declspec(dllexport)
#endif

...in front of some definition?  Shouldn't SQLITE_API be defined to this 
on Windows anyway?

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

Re: [sqlite] Version 3.8.1 beta

[Warren Young]

On Oct 1, 2013, at 7:49 PM, Joe Mistachkin <sql...@mistachkin.com> wrote:

> Warren Young wrote:
>> 
>> It can't build a shared library (DLL) on Cygwin any more:
>> 
>> ./libtool --mode=link gcc   -g -O2 -DSQLITE_OS_WIN=1 -I. -I./src 
>> -I./ext/rtree -D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DNDEBUG 
>> -I/usr/include -DSQLITE_THREADSAFE=1 -DSQLITE_OMIT_LOAD_EXTENSION=1 
>> -o libsqlite3.la sqlite3.lo  \
>>  -rpath "/usr/local/lib" -version-info "8:6:8"
>> libtool: link: warning: undefined symbols not allowed in i686-pc-cygwin 
>> shared libraries
>> 
> 
> Are there any further details you could provide that would point us in the
> direction of which symbols are "undefined"?

I tried Googling to see if there was a mode for libtool that would make it tell 
you, but I don't see it.

I imagine I could find it with fossil bisect, but I was hoping that just 
mentioning the problem here would cause someone close to the development effort 
to smack their heads and say "Ah, it must be sqlite3_foobie_bletch(), which we 
just added."

Let me know if enlightenment fails to occur; I will begin a-bisecting.

> It looks like you are compiling the amalgamation.  What were the command
> line options used to generate it?

I checked tip out of the fossil repo, and just did "./configure && make".

I actually started with a much more complex build setup, but pared it back 
piece by piece hoping to find out which of my additions was to blame.  Turned 
out, none of them were.

It's possible I missed backing some change out.  Tomorrow I will try re-opening 
the repo in a fresh directory.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Version 3.8.1 beta

[Warren Young]

On 10/2/2013 03:34, Jan Nijtmans wrote:


This patch appears to work


Confirmed.  Thanks!


(but maybe -no-undefined
should come in through @LDFLAGS@


No.  The flag is for libtool, not for gcc, which is the program libtool 
calls on to do the linking on Cygwin.  It will cause the link step to 
fail if you put it in LDFLAGS because gcc doesn't know that flag.


(And in case you're wondering, LDFLAGS=-Wl,-no-undefined is also wrong.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.8.1

[Warren Young]

On 10/4/2013 12:32, Peter Haworth wrote:

I've seen emntion of version 3.8.1 of sqlite - is there a document somwhere
that describes the changes?


https://www.sqlite.org/draft/releaselog/current.html

You could have found that link yourself from the SQLite download page, 
right next to the link to the 3.8.1 pre-release tarball.

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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[Warren Young]

On 5/1/2012 2:06 PM, peter korinis wrote:

Is SQLite the wrong tool for this project?


Probably.

SQLite is a data storage tool.  With enough SQL cleverness, you can turn 
it into a data *reduction* tool.  But a data analysis tool?  No, not 
without marrying it to a real programming language.


Granted, that's what almost everyone does do with SQLite, but if you're 
going to learn a programming language, I'd recommend you learn R, a 
language and environment made for the sort of problem you find yourself 
stuck with.  http://r-project.org/


There are several R GUIs out there.  I like R Studio best: 
http://www.rstudio.org/


You'll still find R Studio a sharp shock compared to Excel.  And yes, it 
will require some programming, and yes, I know you said you aren't a 
programmer.  But in the rest of the thread, it looks like people have 
convinced you to use SQLite from the command line, typing in raw SQL 
commands; guess what, that's programming.  Not on the level of R code, 
but R isn't far down the same slippery slope.


It may help you to know that R is most popular in the statistics 
community, which of course is populated by statisticians, not programmers.


R isn't the easiest programming language to pick up, but it's far from 
the hardest.  It's very similar to JavaScript, though a bit tougher to 
learn, mostly due to having accumulated some strange syntax over its 36 
years.  (That's counting R's predecessor, S.)

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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[Warren Young]

On 5/3/2012 11:59 AM, peter korinis wrote:

is R a good query tool?


It's a programming language.  It can do anything within your power to 
persuade the interpreter.


One of the fundamental data types in R is the data frame, which is 
roughly equivalent to a SQLite table.


This is an R equivalent to "SELECT * FROM MYTABLE WHERE V2 < 9":

results <- subset(my.table, V2 < 9)

But, this is not the place for an R tutorial.  Take the question up on 
an R mailing list if you want to learn more of its capabilities.

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

Re: [sqlite] sqlite3 database unreadable on Mountain Lion

[Warren Young]

On 8/3/2012 1:26 PM, Tobias Giesen wrote:

SQLite version 3.7.12 2012-05-14 01:41:23


Apple's version is 3.7.12 2012-04-03 19:43:07.


Well, that's the problem, then, isn't it?  SQLite 3.7.12 shipped on May 
14.  Apple must have shipped a pre-release version of SQLite 3.7.12, 
with the bug Dan remembers in it.


You should be able to fix it by statically compiling SQLite 3 into your 
program.  Then it doesn't matter that the platform version is broken.

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

Re: [sqlite] 64-bit SQLite3.exe

[Warren Young]

On Aug 10, 2016, at 6:03 AM, Keith Medcalf  wrote:
> 
>> Even on a 64-bit processor, there’s usually no reason to run 64-bit
>> Windows unless you have more than 4 GB of RAM, a threshold we didn’t pass
>> very long ago.
> 
> Yes, please remember to keep the "addressable memory limits" linkage to 
> "processor bitted-ness" is a Microsoft Only phenomenon.  You do NOT need 
> 64-bit processors or 64-bit Operating systems to be able to address more than 
> 4 GB of physical RAM.  In fact that there are 32-bit versions of Windows (NT 
> 3.5, 4.0, 2000, XP, 2003 etc) which have been compiled without this 
> artificially imposed limitation.  You pay more for "properly written and 
> compiled" software however because, well, it is easy to do stupid things and 
> impose stupid limits for no reason and you need higher wattage (therefore 
> more expensive people) if you want software that is not bounded by a crapload 
> of inane if not brain-dead) design decisions.

You must be talking about PAE, which is an unmitigated hack, in the dirtiest 
sense of that word:

  https://en.wikipedia.org/wiki/Physical_Address_Extension

As you hint, some OSes allow individual apps to allocate extra RAM via PAE — 
UnixWare was one such — but due to the way PAE works, it can never be more than 
8 GiB per process at a given time.

The OS *could* page in and out 4 GiB segments to give a single application the 
run of the whole 64 GiB maximum space PAE allows, but I don’t know of any OS 
that does this.

Linux in particular doesn’t let individual applications use PAE to access more 
than 3 GiB of VM space, with the standard 3/1 user/kernel split.  Instead, if 
you have more than 4 GiB of RAM in the machine and are running a PAE kernel, it 
will let you have multiple programs *collectively* using more than 4 GiB of VM 
space.  That’s not going the help the OP.

Quoting Wikipedia, “…regular application software continues to use instructions 
with 32-bit addresses and (in a flat memory model) is limited to 4 gigabytes of 
virtual address space…no single regular application can access [all 64 GiB] 
simultaneously.”

I believe the situation is essentially the same on PAE-enabled versions of 
Windows as on Linux.

It is also the case that most machines that shipped with 32-bit Intel 
processors either didn’t have enough slots to allow > 4 GiB of RAM or didn’t 
have BIOS/EFI/chipset support for that much RAM if you did have the slots for 
it.  And why should they have done?  It just adds cost with a low chance that 
the user can make use of it, so that capability only showed up in high-dollar 
machines.

PAE is also not restricted to non-Windows OSes.  Microsoft simply chose not to 
support it on the non-Server versions of Windows, but that is essentially a 
market segmentation issue, not a technical one.

PAE’s time is long past.  64-bit is the proper solution today.

> It is also quite profitable to claim that your prior incompetencies were to 
> blame on "something else" and if everyone would just spend a boatload of 
> money and replace all the defective crap we sold them with a new boatload of 
> defective crap

So…the software development industry is at fault for not building all their 
apps for 64-bit from the start, going back to the 1950s?  Just think, you could 
be booting your Broadwell i7 into UNIX V1 today instead of this bloated Linux 
stuff!  What a great thing that would be!

>> Or maybe you’d like to look to a less legacy-bound company?  Say, Google,
>> who ships Chrome still built as 32-bit, originally for compatibility with
>> 32-bit NSAPI plugins.  Since they dropped that, I can only guess why
>> they’re still building 32-bit binaries, and that guess is that with the
>> tab-per-process isolation, no single tab needs more than 4 GB of VM space.
> 
> Or they are using defective compilers (primary supplier in that field is 
> Microsoft) that cannot switch memory models without re-writing the code.

Chrome runs just fine as a 64-bit executable.  It just wasn’t the default 
version until recently.  If you’ve been using it for a while, you will still be 
on the 32-bit version, as I am here.  You have to do a full reinstall to switch 
to the 64-bit version, which is now the default for new users on 64-bit systems.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64-bit SQLite3.exe

[Warren Young]

On Aug 10, 2016, at 6:32 PM, Keith Medcalf  wrote:
>> You must be talking about PAE, which is an unmitigated hack, in the
>> dirtiest sense of that word
> 
> It is not a hack.  It is how things work.  I do not see where you get the 
> idea that it is a hack.

Because I know how PAE works, and I have the technical competence to express an 
informed opinion about it.

But if you don’t want to believe me, maybe you’ll believe Linus Torvalds:

  https://cl4ssic4l.wordpress.com/2011/05/24/linus-torvalds-about-pae/

> non-Windows have supported physical address limits beyond 4 GB as standard 
> since a very long time (Linux since 2009).

Yes, via PAE.

If you mean something other than PAE, please give a technical reference to what 
you are talking about.  Like, maybe, a page in an Intel architecture reference 
manual.  Even a Wikipedia link would do.

>> As you hint, some OSes allow individual apps to allocate extra RAM via PAE
>> — UnixWare was one such — but due to the way PAE works, it can never be
>> more than 8 GiB per process at a given time.
> 
> I hinted at no such thing.  The original quoted paragraph said "more than 4 
> GB of RAM".  This has nothing to do with the per-process allocation which is 
> an artifact of how badly or ill-conceived the Operating System architecture 
> and the physical implementation of the V:R handling.  Whether the machine and 
> OS can physically address more than 4 GB of physical RAM has nothing 
> whatsoever to do with the "bitedness" of the OS or the CPU, only the width of 
> the physical address bus and the translation tables and hardware.

You’re describing PAE:

  https://en.wikipedia.org/wiki/Physical_Address_Extension

PAE required extending an IA-32 processor’s normal 32-bit address bus to 36 
bits, giving a maximum virtual address space of 64 GiB.

PAE does not change the machine code instructions for accessing memory, since 
that would require recompiling everything to allow 36-bit addresses at the 
program level.  This would require another incompatible Intel instruction set, 
as different from IA-32 as IA-64 is.

If you look at the GCC manuals, you will not find a “PAE mode” flag for giving 
a binary with 36-bit addresses, because an IA-32 processor doesn’t offer that 
addressing mode.  Such a flag would be on this page in the GCC manual:

  https://gcc.gnu.org/onlinedocs/gcc-6.1.0/gcc/x86-Options.html

Notice that there is no PAE flag, and no -m36 flag.  If you give -m64, you get 
IA-64 code, which won’t run on under a 32-bit kernel, even with PAE enabled.

You also won’t find such a compiler flag for Visual C++:

  https://msdn.microsoft.com/en-us/library/19z1t1wy.aspx

Though the OS kernel can use PAE to address more than 4 GiB via a 3-level TLB 
scheme — as opposed to the 2-level scheme Intel used before PAE — it doesn’t 
let a single program access more than 4 GiB at any given time.

Since this whole thread is about giving a single program — sqlite3.exe — access 
to more RAM, PAE doesn’t solve the OP’s problem.

You can install 32-bit Windows Server 2012 on a PAE-aware box with 16 GiB of 
RAM and run two instances of 32-bit sqlite3.exe on it, but they will only be 
able to chew up half the system’s RAM between themselves, no more.

>> Linux in particular doesn’t let individual applications use PAE to access
>> more than 3 GiB of VM space, with the standard 3/1 user/kernel split.
>> Instead, if you have more than 4 GiB of RAM in the machine and are running
>> a PAE kernel, it will let you have multiple programs *collectively* using
>> more than 4 GiB of VM space.  That’s not going the help the OP.
> 
> And yet more of the same.  You are much confused between "CPU accessing 
> physical RAM (the :R part)" and "processes accessing virtual RAM (the V: 
> part).

Virtual memory still doesn’t solve the OP’s problem.

You can take a PAE-supporting box with 4 GiB of physical RAM in it, install a 
PAE-aware OS on it, then configure that OS for 60 GiB of swap to give 64 GiB of 
total virtual memory space, but the individual programs running under that OS 
*still* won’t be able to address more than 4 GiB of virtual memory each, 
because the memory addressing instructions will continue to use 32-bit pointers.

All virtual memory does in a situation like this is allow another program to 
come along and grab up to 4 GiB of virtual memory space itself.

That doesn’t help the OP, who is running a single program — sqlite3.exe — and 
needs it to address more than 4 GiB of memory.  It doesn’t matter whether you 
call it RAM, physical memory, or virtual memory, IA-32 simply doesn’t solve the 
OP’s problem, with or without PAE.

>> Quoting Wikipedia, “…regular application software continues to use
>> instructions with 32-bit addresses and (in a flat memory model) is limited
>> to 4 gigabytes of virtual address space…no single regular application can
>> access [all 64 GiB] simultaneously.”
> 
> And your point is what exactly?  We are not discussing per 

Re: [sqlite] 64-bit SQLite3.exe

[Warren Young]

On Aug 11, 2016, at 3:19 PM, Scott Robison  wrote:
> 
> I think you guys are just talking past each other.

Well, at least one of us isn’t communicating clearly, that’s certain.  I just 
don’t yet know if it’s me, him, or both of us. :)

> Windows versions that
> support PAE have the Address Windowing Extensions (AWE) which allows a
> single process to access more than 4 GiB total in a single process

From what I can see, AWE and PAE are orthogonal, but AWE without PAE doesn’t 
let you get beyond 4 GiB in a single process on IA-32.

But — and this may be where Keith was trying to go — a 32-bit app running on a 
64-bit OS doesn’t need PAE because the host system does support more than 4 GiB 
of virtual memory.  I have yet to see anything that says that AWE on 64-bit 
Windows couldn’t give a 32-bit app access to some of the VM beyond 4 GiB, even 
on consumer versions of Windows.

If so, that’s what I was trying to get at with my request that he give a 
reference to the specific technology he’s talking about, instead of describing 
it in prose.

> That being said, I don't think it is a reasonable or practical thing to
> expect of cross platform source code like SQLite to use such a platform
> specific API.

Couldn’t it be abstracted behind a layer that used mmap() + tmpfs to pull off a 
similar trick on other 64-bit OSes?  (I’m not talking about > 4 GiB on 32-bit 
OSes here.)

It’d be a lot of work just to avoid rebuilding for 64-bit, but maybe it would 
be an interesting project for someone.  Like a master’s university project, 
maybe.

Still, it looks like we’re on the cusp of all the major OSes moving to 
64-bit-only, so a lot of work made here might be obsolete soon.  OS X and RHEL 
have already made the leap.  Ubuntu and Microsoft both threatened to do this 
recently, but both backed down after user outcry.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64-bit SQLite3.exe

[Warren Young]

On Aug 9, 2016, at 9:30 PM, Rousselot, Richard A 
 wrote:
> 
> I could spend a few hours figuring this out and be fine but it will be 
> painful for me.

Or you can spend many hours waiting for someone to build it for you.  How many 
hours are you willing to wait to save yourself some pain?  (And since when did 
learning something new cause pain?)

As to your problem with corporate IT, will they let you install Cygwin?  SQLite 
is well-supported in Cygwin, and there is a 64-bit version of Cygwin.  Due to 
the way Cygwin works, all packages available for 64-bit Cygwin are also 64-bit.

Cygwin SQLite should be nearly as fast as native SQLite.  There are some big 
speed hits in Cygwin, but for the things SQLite does, I can’t see that you’re 
going to run into any of the biggest ones.

> The last 32-bit Intel CPU was the PIII in 2004

That’s simply not true.  Many P4s were 32-bit, the Atom processors were 32-bit 
only until 2008, and I believe the Core Solo processors were also 32-bit only.

(That latter caused a lot of trouble for me when Apple went 64-bit only and cut 
off a bunch of the still-useful Macs I had still in use.)

> no supported Windows OS requires 32-bit CPUs

But equally, Microsoft retrenched from their threat to make Windows 10 the 
first 64-bit-only version of Windows.  Wonder why? :)

> The 64-bit version will, I assume, happily work on DBs created in the 32-bit 
> version.

Yes.

> What am I missing?

Someone has to do it.  Time is not free.

> Are windows command line tools 32-bit only?

The opposite, actually: the first 64-bit versions of the Visual C++ tool set 
were command-line only, as I recall.  I believe that was back in the 
pre-VC++2005 days.

> Why add powerful features like CTE if you can't access their power?

Because most of the SQLite binaries are shipped by third parties, not directly 
from sqlite.org.  The biggest sources are OSes (virtually all mobile phones, 
Mac OS X, Windows, etc.) and third-party applications (virtually all web 
browsers, many Adobe and Apple products, etc.)  These third parties built 
SQLite to meet their needs.

I’d bet the number of regularly run instances of binaries downloaded directly 
from sqlite.org is under 0.01% of the total usage of SQLite.

(That’s a considered guess, not a wild guess.  There are billions of SQLite 
instances in the world, and I’m betting there are less than 100,000 users of 
the SQLite.org binaries.  I wouldn’t be surprised if it’s under 0.001%.)

Of that tiny percentage, only a small fraction will actually need a 64-bit, and 
of that fraction of a fraction, only a small number will be unable to acquire 
or build a 64-bit binary.

Why spend a lot of effort on such a small user base?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64-bit SQLite3.exe

[Warren Young]

On Aug 9, 2016, at 11:39 PM, Rousselot, Richard A 
 wrote:
> compiling a 64-bit binary is not a useful skill

It keeps me fed pretty well. :)

> Your 32-bit Mac is not windows machine

What, you think Intel only made Core Solos for Apple?

> How long do I have to wait for everyone to upgrade?  So, if there is one 
> person in the universe still using a 32-bit windows machine we all have to 
> wait?

It would be interesting to get data on 32-bit vs 64-bit Windows installs.  I 
wouldn’t be surprised if it’s still more than 50% 32-bit.  It wasn’t that long 
ago that XP installs finally dropped below 50%, and a huge chunk of those were 
32-bit.

Even on a 64-bit processor, there’s usually no reason to run 64-bit Windows 
unless you have more than 4 GB of RAM, a threshold we didn’t pass very long ago.

>>> no supported Windows OS requires 32-bit CPUs
>> 
>> But equally, Microsoft retrenched from their threat to make Windows 10 the 
>> first 64-bit-only version of Windows.  Wonder why? :)
>> 
> Microsoft keeps 32-bit compatibility for legacy applications.

You mean like Visual Studio 2015, released less than a year ago today?  Yes, 
the VS IDE remains 32-bit, even though the underlying compilers will build 
64-bit executables.

Maybe you’d like to talk about PowerPoint?  The version currently on my 64-bit 
Windows 10 machine runs as 32-bit.

Or maybe you’d like to look to a less legacy-bound company?  Say, Google, who 
ships Chrome still built as 32-bit, originally for compatibility with 32-bit 
NSAPI plugins.  Since they dropped that, I can only guess why they’re still 
building 32-bit binaries, and that guess is that with the tab-per-process 
isolation, no single tab needs more than 4 GB of VM space.

>> Someone has to do it.  Time is not free.
>> 
> I agree, time is not free.   If I compile a 64-bit SQLite3.exe that only 
> helps me and wastes a lot of my time.

…And teaches you a useful skill that you may use later.

You say you don’t trust software from others.  What is a more trustworthy way 
to get executables than those you have built yourself from source code?

> The 64-bit version will probably shave an hour off my many 8 hour processing 
> jobs.

As a rule, 64-bit software runs a bit slower than 32-bit software on Intel 
CPUs.I/O channels all run the same speed, so for anything not CPU-bound, 
there is no advantage.  64-bit executables are a fair bit larger, which means 
you have more cache misses.  Unless you’re running something able to be highly 
register-optimized, you don’t get the only real speed advantage of Intel 64-bit 
CPUs, that being access to more registers.

> That will add up very quickly for me.

I’d love to study your benchmarks after you manage to get a 64-bit executable.

>>> Why add powerful features like CTE if you can't access their power?
>> 
>> Because most of the SQLite binaries are shipped by third parties, not 
>> directly from sqlite.org.
>> 
> This doesn't make sense, what does a 3rd party binary based on a dll have to 
> do with a command line tool?

I’m saying that the vast majority of SQLite users are not using the Windows EXE 
downloaded from sqlite.org, therefore the answer to your question asking how 
anyone could use powerful features like CTE and > 2 GB of RAM is that the vast 
majority of SQLite users aren’t affected by the lack of a 64-bit Windows 
executable.

If I run sqlite3 on my Mac, I get a 64-bit executable shipped by Apple.

If I run an app on my iPhone and it opens a SQLite DB, it runs a 64-bit version 
of SQLite also shipped by Apple.

Those two alone account for roughly a billion of the installations of SQLite.

> Are you saying that no one needs the command line tool so its development 
> should be abandoned?

What’s with the hyperbole?  100,000 users (my informed guess) is not zero; it 
is merely small compared to the total SQLite user base.  That small user base 
is further reduced by the number of those users who would actually benefit from 
a 64-bit Windows executable.

> Why spend time making a 32-bit version for the minority of people still 
> running 8 year old equipment?

You’re making an unwarranted assumption that the last 32-bit Windows 
installation was done in 2008 just because that was the last 32-bit Intel CPU 
introduction date.  (And the latter turns out to be incorrect anyway.)

Here’s an Atom E620, introduced in Q3 2010, 32-bit only, which you can still 
buy today:

  https://www.amazon.com/dp/B0137IIR88/ref=cm_sw_r_tw_dp_x_dxSQxb2J53HJC

Here’s a mini PC rocking a 32-bit Intel processor a couple of years newer than 
that one, still commercially available:

  https://www.amazon.com/dp/B00AXK56M4/ref=cm_sw_r_tw_dp_x_3DSQxbD1Q2K25

Here’s Intel’s complete 32-bit-only CPU product table, limited to current 
products only:

  http://goo.gl/jw1vyA

I get 122 results today.

And this totally ignores all the 64-bit Intel based boxes and VMs still running 
32-bit Windows for whatever reason.

Re: [sqlite] 64-bit SQLite3.exe

[Warren Young]

On Aug 11, 2016, at 7:50 PM, Scott Robison  wrote:
> 
>> It’d be a lot of work just to avoid rebuilding for 64-bit, but maybe it
>> would be an interesting project for someone.  Like a master’s university
>> project, maybe.
>> 
> 
> At first I thought to myself that a custom memory allocator for SQLite
> could do this, but the real problem would be once a pointer is given to
> SQLite, it is expected that pointer will be valid until disposed of

Yeah, you’d need something like the handle lock/unlock pattern you see on some 
OSes, where the app generally holds only handles long-term, locking them down 
to yield a pointer only for the duration of a single function invocation at 
most.

> Certainly a valuable tool for heavy processes that need to run on 32-bit
> PAE hardware with > 4 GiB of addressable ram. Anyone want to start work on
> SQLHeavy? ;)

I was thinking more “valuable for the educational experience” than valuable in 
any practical sense, given the easy availability of 64-bit OSes and hardware.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64-bit SQLite3.exe

[Warren Young]

On Aug 10, 2016, at 3:22 AM, J Decker  wrote:
> 
> I'd think many of you would know 64 bit
> mode has more general purpose registers to carry values and the default
> calling ABI is improved to be more of a register centric model.

SQLite is largely I/O bound.

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

Re: [sqlite] IS read data from sqlite cost too much time more than few tens or hundred Mega Bytes

[Warren Young]

On Aug 2, 2016, at 9:04 PM, 梨田 <1565050...@qq.com> wrote:
> 
> I find when the data in database  is larger than tens of mega bytes，it spends 
> more than 5~10s time to read it.

If the time required to run a given SELECT call increases linearly as a 
function of the database size, you’re probably doing either linear table scans 
or have an un-indexed query.

Would you care to share your database’s schema and the queries you’re running 
on it?

What do you get when you use the EXPLAIN QUERY PLAN feature?

  https://www.sqlite.org/eqp.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple web query tool

[Warren Young]

On Feb 1, 2017, at 11:45 AM, Brian Curley  wrote:
> 
> internal file shares are all that's needed to
> connect to a distributed file.

…as long as your networked file system does locking properly:

   https://www.sqlite.org/lockingv3.html#how_to_corrupt

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

Re: [sqlite] Bulk Insert in Sqlite3

[Warren Young]

On Feb 6, 2017, at 10:36 PM, Niti Agarwal  wrote:

> I read about SQLITE_MAX_SQL_LENGTH,

If this is why you’re making many transactions, there’s no requirement that all 
of the SQL that’s part of a single transaction be in a single SQL string given 
to the DB.  You can execute a bare “BEGIN TRANSACTION” SQL statement, do your 
10 million separate insert statements, and then execute a COMMIT statement, at 
which time all of the rows will be visible to other users of that DB.

This is simpler, still fast, and doesn’t require that you do all that manual 
batching-up, or worry about internal SQLite buffer sizes.

> Also, read about sql bind feature, but not very sure how to do in Golang?

There are a bunch of SQLite wrappers for Go:

   http://go-lang.cat-v.org/library-bindings

Which one are you using?

The first one on that list appears to be MIA, but the second one includes this 
module, which includes a Bind() function:

   https://github.com/kuroneko/gosqlite3/blob/master/query_parameter.go

Seems straightforward to me.

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

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[Warren Young]

On Feb 3, 2017, at 9:15 AM, Simon Slavin  wrote:
> 
> My solicitors will be in the post.

How much does it cost to mail a lawyer where you are?  Do they charge by weight 
or unusual shape?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DELETE when DB is full

[Warren Young]

On Jan 31, 2017, at 2:03 PM, Ward WIllats  wrote:
> 
> the delete sometimes (very rarely) fails with a 13 "disk or database full" 
> error. I assume because the purger is late to the party and it needs pages in 
> the WAL to be able to rollback if necessary.

Is there an especially good reason you have to do this in a single shot?

If you get this error, shrink the date range or pages-to-free value by half and 
try again.  Repeat until it works, then repeat at that size until you’ve 
deleted as much as you need to.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Warren Young]

On Jan 26, 2017, at 8:40 AM, Clyde Eisenbeis  wrote:
> 
> When I tried entering:
> 
>  sqlite3_bind_
> 
> the compiler starts complaining.

What I wrote was "sqlite3_bind_*()” which you were expected to understand as a 
reference to the 15 functions beginning with “sqlite3_bind_” listed on the 
first page I linked you to yesterday.

> I'm searching the internet for sqlite3_bind_* () examples.

This is why programming by copy-and-paste is a problem.  When the copy-able 
code runs out, you’re stuck.

Take it as a challenge: write this one on your own using only the information 
you’ve been given so far.  You’ll learn much.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Warren Young]

On Jan 25, 2017, at 8:33 AM, Clyde Eisenbeis  wrote:
> 
> The use of .Parameters in OLE DB fixes this problem.  Is there an
> equivalent for SQLite?

You’re looking for prepared statements with parameters:

   https://sqlite.org/c3ref/stmt.html
   https://sqlite.org/lang_expr.html#varparam

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

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Warren Young]

On Jan 25, 2017, at 8:50 AM, Clyde Eisenbeis  wrote:
> 
> Are there code examples similar to the following (OLE DB)?

Code examples?  Maybe, but the second link I gave you is pretty clear.  The 
bits you want are even in bold text.

> stCmdString += " AND " + stLikeFieldName + " LIKE '%" +
> liststLikeFieldValue[iii] + "%’";

stCmdString += " AND ‘%?1%’ LIKE ‘%?2%’”;

Then use the sqlite3_bind_*() calls to insert parameters 1 and 2 into the 
string.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Warren Young]

On Jan 27, 2017, at 1:09 PM, Clyde Eisenbeis  wrote:
> 
> The SQLite websites would be more useful with examples.

Certainly.  No question.

The thing is, I think most of us are primarily grateful that SQLite *exists* 
and that it works as well as it does.  Given a choice of where our benefactors 
choose to put their efforts, I think most people on this list would prefer that 
it be put into SQLite itself, rather than the docs, which are already 
uncommonly good.

One of the great virtues of open source is that when some behavior is not 
explicitly documented, you can dig in and find out why it behaves the way it 
does.  Closed-source products often have to have more extensive documentation 
because that’s the only way you have to understand it, short of breaking out 
the disassembler.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Query truncating String column at 255 chars long

[Warren Young]

On Jan 27, 2017, at 12:09 PM, John McKown  wrote:
> 
> On Fri, Jan 27, 2017 at 12:58 PM, Antonio Carlos Jorge Patricio <
> antonio...@gmail.com> wrote:
> 
>> In my tests, they got almost all chopped at 255 chars long
>> 
> ​I'm guessing this is a problem with SQLite.NET

I don’t see any 255 or 256 constants in the C# code implementing SQLite.NET 
1.0.8.

> or .NET itself

Highly doubtful.  .NET isn’t quite as all-powerful as C, but it’s close.  The 
max length of a .NET string is about a gigabyte:

   https://stackoverflow.com/questions/140468/

As far as I’m aware, VB.NET doesn’t have any additional restrictions over .NET, 
other than its syntax.  (Which is a considerable barrier, no question.)

> There’s no obfuscated Perl contest because it’s pointless.

Perl is not pointless:

   http://search.cpan.org/~markov/Geo-Point-0.96/lib/Geo/Point.pod
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Thread safety of serialized mode

[Warren Young]

Taking it off-list, since there is zero remaining connection to SQLite now:

> On Feb 16, 2017, at 2:49 PM, Tim Streater  wrote:
> 
> On 16 Feb 2017 at 18:30, James K. Lowden  wrote: 
> 
>> On Tue, 14 Feb 2017 17:05:30 -0800
>> Darren Duncan  wrote:
>> 
>>> There is nothing inherently wrong with threads in principle
>> 
>> What's inherently wrong with threads in principle is that there is no
>> logic that describes them, and consequently no compiler to control that
>> logic.  
> 
> [snip remainder of long whinge about threads]
> 
> Sounds, then, like I'd better eliminate threads from my app. In which case 
> when the user initiates some action that may take some minutes to complete, 
> he can just lump it when the GUI becomes unresponsive.

CSPs, the actor model, message passing architectures, etc. all give you ways to 
have concurrent processing without your program explicitly dealing with 
OS-level threads.

> That OK with you? Can I point the user your way when he gives me grief about 
> it? Or should I just say that no, he can't have a responsive GUI under those 
> conditions because some guy on the Internet says so?

“Fear is the path to the dark side. Fear leads to anger. Anger leads to hate. 
Hate leads to suffering.”

Let go your anger. :)

(And lest you think I fear threads and that this sword cuts both ways, no: I 
avoid using threads whenever possible because I *understand* threads.)

> I'll just bring my 50 years experience of writing software to the table, 
> including threaded apps for PDP-11s and VAXes. It's called debugging.

Some kinds of debugging are easier than others.  Why set yourself up for a much 
harder problem than necessary by using inherently problematic mechanisms?  

(Plural, meaning threads and all the synchronization primitives that go along 
with them, which drag in new problems like deadlocking that you didn’t have 
before you added mutexes to try and solve the problems you bought by adding 
that one little ol' thread.)

Have you read the Lee paper referenced in the mailing list thread?

https://www2.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.pdf

Threads aren’t just distasteful from an implementation standpoint, they’re 
*mathematically unsound*.

Did you study the ARM assembly language comparison I linked to?  How can we 
expect people to write threaded programs when even a simple integer increment 
is prone to race conditions and read-modify-write errors?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Options

[Warren Young]

On Feb 17, 2017, at 7:32 AM, R Smith  wrote:
> 
> You can even checkout the latest commits via SVN

There’s a Subversion mirror of the official Fossil code repository for SQLite?

I tried to search the web for it, but since Subversion uses SQLite internally 
to manage its own code repositories, I get pages and pages of irrelevant 
results.

As far as I know, if you want the current tip-of-trunk source code to SQLite3, 
you need to either clone it via Fossil or work out how to construct a Zip URL 
per

https://goo.gl/KzLcV8

(Excuse the shortener, it’s a raly long URL.)

I could give you that Zip file link, but I suspect it’s purposely not being 
published to avoid load on the SQLite repository server caused by bots 
repeatedly requesting Zip files and tarballs.

Using Fossil is far more efficient than downloading Zip archives, but as I keep 
getting reminded in my own Fossil-hosted public project, some people just 
refuse to install and use anything they don’t absolutely have to.  It’s six 
easy steps, but apparently that’s too many for some.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Thread safety of serialized mode

[Warren Young]

On Feb 15, 2017, at 4:40 AM, Darren Duncan  wrote:
> 
> On 2017-02-15 2:40 AM, Clemens Ladisch wrote:
>> Cecil Westerhof wrote:
>> 
>> And just like with assembly code, you also have to count the time spent
>> writing it, and debugging the result.
> 
> Also, its a long time since hand-writing assembly code was any good for 
> performance, unless you're a 1% top expert with a good reason.

While true insofar as it goes, that attitude leads to people being ignorant of 
what the compiler produces from the code you give it.  This almost caused a 
threading bug in a program I was modifying recently, and we only caught it 
ahead of time because someone questioned some basic assumptions, causing me to 
go look at the generated assembly.

Consider this humble lone line of code:

++i;

The threading bug is right there, staring at you.

What, you don’t see it?  How about now:

https://godbolt.org/g/xfJ9SQ

Yeah, that’s right, friends, integer increment isn’t a single instruction on 
ARM, even with gcc -O2, hence it is not atomic!  It takes at least three 
instructions (load, modify, store) and for some reason GCC chose to use 6 in 
this particular case.  (Probably some remnant of the function calling 
convention.)

That means that if you’re depending on that increment to be atomic across 
threads, you’re going to be in for a shock of the old bank balance transaction 
problem form.  (You know, the one every SQL newbie gets taught, where the 
account gets double-debited or double-credited if you don’t use transactions.)

The solution is to use GCC’s atomic increment primitive — also shown via the 
above link for comparison — which adds a couple of “dmb” ARM instructions to 
lock the code to a single CPU core through that critical section.

A software developer who refuses to learn about his processor’s assembly 
language is like trying to become an electrical engineer without learning 
anything about physics.  A typical practicing EE won’t need to break out 
Maxwell’s equations every day, but understanding the implications of those 
equations is what separates engineering from tinkering.

> If you want speed, write in C or something else that isn't assembly.  The 
> odds are like 99% that the modern C compiler will generate faster code than 
> you could ever write yourself in assembly, and it will be much less buggy.

Just to be sure people understand my position here, I will agree with this 
again.

If you don’t like my example above as presented, consider also that it supports 
the “threads are evil” hypothesis.  If you can’t count on a simple preincrement 
to be atomic, what else are you misunderstanding about what’s going on at the 
low levels of the system when it runs your multithreaded program?

(And no, it wasn’t my idea to use threads in the program I was modifying in the 
first place!  One of the planned upcoming changes is to redesign it from a 
2-thread system to two cooperating single-threaded programs communicating over 
an IPC channel.)

> Similarly with threads, for the vast majority of people, using other 
> concurrency models with supported languages are better; they will still get 
> the performance benefit of using multiple CPU cores but do it much more 
> safely than if you are explicitly using "threads" in code.

Also agreed.  I recommend starting with message-passing, and move on to other 
methods only when you can prove that won’t give the required benefit.

I also recommend that you go learn you some Erlang (for great good):

   http://learnyousomeerlang.com/content

If you can’t get past the syntax, you can paper over it with the Ruby-like 
Elixir front-end:

   http://elixir-lang.org/

But to bring all of this back around on topic, beware that a SQL DB is 
basically a global variable store, albeit with arbitrated access.  You can 
create cross-process problems by misuse of the data store just as you can with 
global variables in a traditional threaded program.

Message-passing concurrency is just a tool that increases your chances of 
effortless success, it is not a guarantee of it.  No system that allows side 
effects can guarantee proper ordering of operations without some thought given 
to it, whether the mechanisms involved are mutexes, transactions, or something 
else.

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

Re: [sqlite] Thread safety of serialized mode

[Warren Young]

On Feb 15, 2017, at 5:03 AM, a...@zator.com wrote:
> 
> I suppose someday, programming languages can do an analogous translation in 
> our limited but safe, sequential programs.

Not as long as we require side effects to achieve anything of practical value.  
Any form of I/O is a “side effect” by my definition, whether that’s disk, 
network, GUI, or what have you.

Avoiding threads is good because the well known problems with global variables 
magnify a combinatorially when multiple threads can access them simultaneously 
— literally *simultaneously* on a modern multi-core processor! — in any pattern 
you can conceive, and more you probably haven’t even thought of.  The problem 
is combinatoric on the number of instructions in the program and the number of 
threads, which gives you a really big number really fast.  Humans aren’t good 
at thinking about all N billion execution paths through a given program.

Synchronization — whether that’s mutexes or transactions or message passing or 
something else — helps, but it always eats into the speed advantage of raw 
threads, so there will continue to be a continuous pressure to reduce 
synchronization rather than add more automatically.  Go look up “lock free data 
structures” if you want to see the kind of thing being done in this area.

Computers can help with the combinatoric explosion, but I’m not seeing a whole 
lot of progress on this with real world programs.  I want a tool like lint(1) 
that will detect synchronization errors statically, but for now, I think you 
have to rely on dynamic tools like Helgrind:

   http://valgrind.org/docs/manual/hg-manual.html

The problem with dynamic error detection is that it can only catch errors in 
code paths that you can trigger while the tool watches.  This is about more 
than just simple code coverage, it’s about *combinatoric* code path coverage.  
If you don’t test all possible interleavings of instructions among the threads 
and cores, you can still miss an error, even if the tool knows how to detect it.

I have to believe static thread correctness analysis is at least possible in 
principle, because humans do manage to see threading problems just by staring 
at the code long enough.  It might require strong AI to do it, but it’s got to 
be possible to at least do as well as an expert human.  But, that just means 
this becomes yet another, “Won’t it be great when we have strong AI?” wish.

A tool like Helgrind isn’t enough by itself.  It’ll blithely ignore your 
lock-free data structure code, for example.  It’ll also fail to flag logical 
errors in your SQLite code where you’re missing transactions, for another.

It’s worth repeating: Concurrency is hard.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SpeedTest1 Comparison of 32 vs 64 bit on Windows 10 13483.15

[Warren Young]

On Aug 17, 2016, at 10:38 PM, Keith Medcalf  wrote:
> 
> Same code, same compile options, same compiler version
> options -s -O3 -pipe -march=native -mtune=native -falign-functions=16 
> -falign-loops=16 -flto

Ah, good, actual science this time instead of apples-to-oranges. :)

Thank you for doing this and posting the results.

Even better, thank you for using a test program that comes with SQLite, so we 
can get numbers we can compare.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.15.0 scheduled for 2016-10-14

[Warren Young]

On Sep 25, 2016, at 4:50 AM, Cecil Westerhof  wrote:
> 
> 2016-09-22 21:04 GMT+02:00 Richard Hipp :
> 
>> Our current schedule for the next SQLite release (3.15.0) is for
>> 2016-10-14.
>> 
>> Your beta-tests are appreciated.
> 
> ​What can I do to beta test?

First, just try it against your existing application, to see if there are any 
regressions.

Second, go through the changelog and see if there are any new features or fixes 
that interest you:

  https://www.sqlite.org/draft/releaselog/current.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

[Warren Young]

On Sep 7, 2016, at 11:02 AM, Richard Hipp  wrote:
> 
> On 9/7/16, dmp  wrote:
> 
>> The draft site looks and works fine, is fast for loads, doesn't
>> seem to require scripting, GOOD!
> 
> Sorry to disappoint, but some pages (ex:
> https://www.sqlite.org/draft/c3ref/funclist.html) use JS to split long
> lists into multiple columns.  You can see this in action by dragging
> your browser window very wide or very narrow and pressing Reload on
> the example given in the previous sentence.  If JS is disabled, the
> list will not display at all.
> 
> I don't know of a way to do that using only CSS.  If you know of a
> way, please enlighten me.

There is, but it requires using a feature of CSS3 that isn’t universally 
deployed yet, particularly among the benighted IE users:

  https://responsivedesign.is/develop/css/css3-multiple-columns
  http://caniuse.com/#feat=multicolumn

If you don’t want to use that, then may I suggest that you attach the layout 
reflow to the window.onresize event, so you don’t have to reload the page for 
the layout to reflow?

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

Re: [sqlite] .mode column .width .separator

[Warren Young]

On Oct 14, 2016, at 1:27 PM, Don V Nielsen  wrote:
> 
> I can read C, but I don't know how to compile it.

https://www.sqlite.org/howtocompile.html

Once you get SQLite to build, make the suggested change and say “make” again.  
The sqlite3 program will be rebuilt, since make knows that sqlite3 depends on 
shell.c.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] freebsd 11 SQLite build: readline/readline.h file not found

[Warren Young]

On Oct 16, 2016, at 12:45 AM, jungle Boogie  wrote:
> 
> I just re-installed freebsd 11 on a machine of mine and as usual, I
> build sqlite from source. However, I see this:
> sqlite3/src/shell.c:66:11: fatal error: 'readline/readline.h' file not found

Did you ./configure after the upgrade?  If not, do so.

Readline is one of those contentious issues because it’s licensed under the 
full-strength GPL, rather than the LGPL.  GPL partisans will tell you that the 
widespread dependence on Readline has forced software to GPL or LGPL where that 
otherwise wouldn’t have happened, so that’s a Good Thing™ in their book.

The other side of the coin is that all this did was force the creation of 
libedit, which the BSDs have all adopted, and which is spreading around the 
world now to counter the taint of Readline. :)

  http://thrysoee.dk/editline/

SQLite (and Fossil) supports both, and will use the first one it finds, 
preferring Readline if both are installed.

This is why reconfiguring can help.  If a prior configure found Readline but 
it’s gone now, reconfiguring may find libedit instead.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users