[sqlite] View workarounds

[Steve Schow]

My suggestion is add the extra columns you need to the view, then when you make 
a query against that view, only specify the more limited set of output columns 
you want in the final output

As others have said already, don?t think of a view as a stored query.  Think of 
it as multiple joined tables into a ?virtual? table which you can then do more 
simple queries against then you would have to do in some monster join.  it 
basically will let you hide all the complicated stuff you say you have now into 
the view, then use very simple select statements on that view to produce your 
final report?which only shows the columns you want.



On May 23, 2016, at 7:02 AM, Balaji Ramanathan  
wrote:

> Hi,
> 
> I have created some views in my database by joining multiple tables to pull
> out specific columns from these tables without having to remember the exact
> SQL and joins (easy repeatability). But it looks like I have misunderstood
> how views work and have run into some limitations when using these views. I
> was wondering if any of you have any workarounds for these limitations.
> 
> 

[sqlite] BUG: readline is not auto-detected

[Steve Schow]

it seemed to detect it for me when I built on Debian 7

I had the following packages installed via apt-get:  readline-common 
libreadline6-dev libreadline6

On May 20, 2016, at 6:07 AM, Jeroen Demeyer  wrote:

> With SQLite 3.13.0 using the autoconf tarball, running ./configure without 
> arguments does not automatically check for readline. This used to work in 
> SQLite 3.8.4. The reason is that the default value for "enable-readline" is 
> set to "no" in configure.ac. The fix is easy:
> 
> diff -ru a/configure.ac b/configure.ac
> --- a/configure.ac  2016-05-20 11:37:09.548488947 +0200
> +++ b/configure.ac  2016-05-20 11:37:21.918632861 +0200
> @@ -41,7 +41,7 @@
> AC_ARG_ENABLE(readline, [AS_HELP_STRING(
>   [--enable-readline],
>   [use readline])],
> -  [], [enable_readline=no])
> +  [], [enable_readline=yes])
> if test x"$enable_editline" != xno ; then
>   sLIBS=$LIBS
>   LIBS=""
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Steve Schow]

ps - I had not heard of veracity before and on the surface it looks quite 
interesting as a direct competitor to fossil, but it also looks a bit 
abandoned.  

On May 18, 2016, at 11:38 AM, Steve Schow  wrote:

> Interesting read, thanks!
> 
> I?m new to fossil, but personally I have fallen in love with it over the past 
> month or so I?ve been using it.  
> 
> My reaction to git after several years of dabbling with it here or there has 
> been 180 degrees opposite?not love
> 
> git is a menace
> 
> 
> 
> On May 18, 2016, at 11:04 AM, Jonathan Moules  lightpear.com> wrote:
> 
>> I've not heard of fossil so this thread piqued my interest; I currently use 
>> Mercurial where I have a choice.
>> I don't seem to be able to find much about Fossil v's Mercurial. This blog 
>> post looked interesting though:
>> http://www.omiyagames.com/farewell-fossil-version-control/
>> 
>> Despite Mercurial being less ... opaque than Git, I guess many of the points 
>> remain the same for that comparison.
>> 
>> 
>>  On Wed, 18 May 2016 16:55:15 +0100 Warren Youngwyml at 
>> etr-usa.com wrote  
>> 
>> On May 18, 2016, at 4:43 AM, Kees Nuyt k.nuyt at zonnet.nl wrote:
>>  
>>  On Wed, 18 May 2016 11:39:28 +0200, Cecil Westerhof
>>  cldwesterhof at gmail.com 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-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
>> 
>> 
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Steve Schow]

Interesting read, thanks!

I?m new to fossil, but personally I have fallen in love with it over the past 
month or so I?ve been using it.  

My reaction to git after several years of dabbling with it here or there has 
been 180 degrees opposite?not love

git is a menace



On May 18, 2016, at 11:04 AM, Jonathan Moules  
wrote:

> I've not heard of fossil so this thread piqued my interest; I currently use 
> Mercurial where I have a choice.
> I don't seem to be able to find much about Fossil v's Mercurial. This blog 
> post looked interesting though:
> http://www.omiyagames.com/farewell-fossil-version-control/
> 
> Despite Mercurial being less ... opaque than Git, I guess many of the points 
> remain the same for that comparison.
> 
> 
>  On Wed, 18 May 2016 16:55:15 +0100 Warren Youngwyml at 
> etr-usa.com wrote  
> 
> On May 18, 2016, at 4:43 AM, Kees Nuyt k.nuyt at zonnet.nl wrote:
>  
>  On Wed, 18 May 2016 11:39:28 +0200, Cecil Westerhof
>  cldwesterhof at gmail.com 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-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Steve Schow]

thanks for letting us know about that, thoroughly enjoyed listening?.


On May 14, 2016, at 2:17 PM, Simon Slavin  wrote:

> Those interested in SQLite might like to listen to
> 
> 
> 
> Play on the page or download as an MP3.
> 
> Unusual information on Dr Hipp's early career, SQLite history, HWACI, and how 
> come SQLite is free but the developers still manage to afford food and 
> somewhere to sleep.
> 
> Question to ponder before you listen: Many of you know about tiny devices 
> which incorporate SQLite but what do you think the biggest one is ?
> 

[sqlite] 2 different SQLite versions inside the same process space

[Steve Schow]

Ok?starting to sound safer.  :-)

at a minimum this problem only occurs when multi-threading is being used to 
access a sqlite DB file.  but I think its probably even more specific then that?

When you say ?two copies of sqlite in the same address space?, this is the part 
I am getting confused about I guess.  I guess you mean the single-file sqlite 
dot c file is compiled into a single executable more then once, somehow 
avoiding namespace conflicts.

In such a case, then each of those code-instances would have what it thinks is 
a global to keep track of the db file lock, but in actuality they are each 
using their own private global.

Do I understand it correctly now?



On May 11, 2016, at 8:36 AM, Richard Hipp  wrote:

> On 5/11/16, Steve Schow  wrote:
>> 
>> Typically concurrency happens when two different users execute their program
>> that has sqlite compiled into it;?.. concurrently.
> 
> The problem only comes up with two different copies of SQLite are
> running within the same process.  The same program being run twice is
> fine.
> 
> The problem is a bug in the posix APIs for file locking.  I say "bug".
> It is not an implementation problem.  The implementation is correct on
> all major unix systems.  The problem is in the *design*.
> 
> In posix, suppose you open a file "xyzzy" and take a lock on that file
> using the file descriptor returned by open().  That works fine.
> 
> But then if another thread in the same process (technically: another
> thread with the same result from getpid()) does this:
> 
> close(open("xyzzy"))
> 
> The close() operation cancels all locks held on the "xyzzy" file, even
> locks created by different threads using different file descriptors.
> 
> This is crazy.  Everybody acknowledges that it is crazy. But it is the
> posix standard.
> 
> In order to work around the problem, SQLite has to use global
> variables to track every close() operation and defer those that are
> against files with locks held by other file descriptors.  But if you
> have two copies of SQLite in the same address space, they will use
> different global variables and may not know about each other, and
> hence locks can get cancelled.
> -- 
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 2 different SQLite versions inside the same process space

[Steve Schow]

On May 11, 2016, at 8:22 AM, Steve Schow  wrote:
> 
> Oh that actually makes more sense?but also even more concerning in a way, 
> unless I?m still misunderstanding the conundrum.   

Just thinking out loud?.is this problem related to specifically when people try 
to compile sqlite into a shared library and then use that shared lib from two 
different running processes at the same time?

[sqlite] 2 different SQLite versions inside the same process space

[Steve Schow]

On May 10, 2016, at 4:15 PM, Richard Hipp  wrote:

> On Tue, 10 May 2016 22:47 +0100, Tim Streater  wrote:
>> 
>> I read it as two different *copies*. It doesn't sound to me as if the
>> versions have anything to do with it.
>> 
> 
> Correct.  Two different *copies*of the library.  They can both have
> the same version number - that doesn't matter.
> ? 


Oh that actually makes more sense?but also even more concerning in a way, 
unless I?m still misunderstanding the conundrum.   

Typically concurrency happens when two different users execute their program 
that has sqlite compiled into it;?.. concurrently.   Is this problem unique to 
some library I am not aware about or would it effect any two people trying to 
use the sqlite3 binary at the same time (each one calling their own instance of 
it)?or say?calling fossil at the same time, for example?

Each user instance starts a  process running, with its own globals?and they 
could each try to hit a certain sqlite DB concurrently?from different 
processes?  But I thought this was supposed to be the whole point of 
sqlite?that it can handle that?it just may force one or the other of them to 
wait until the file is unlocked..  So I was under the impression that sqlite 
lite was safe, but slow, for concurrent attempts to write to a DB file.  But it 
sounds like you?re saying its not safe?though its still not entirely clear to 
me if I am understanding the problem right or how to avoid it.

[sqlite] 2 different SQLite versions inside the same process space

[Steve Schow]

I would like to understand this issue a little bit better?


On May 10, 2016, at 2:31 PM, Richard Hipp  wrote:
> 
> In unix, SQLite has to use global variables to work around the
> well-known design bugs in posix advisory locks.  And so if you have
> two different instances of SQLite running on unix, they will use
> different global variables, causing them to break each others locks
> and you will get database corruption.


are you saying that on UNIX, if two different versions of the sqlite3 binary 
attempt to access a DB file at the same time?then the globals that are used in 
the sqlite3 binaries related to locking may be different in the two different 
binaries, and may result in DB corruption?

If that is the case, then although the internal DB file format may be backwards 
compatible between versions of sqlite3, its very important that I take care not 
to allow two different versions of the SQLITE executable code attempt to access 
the DB file at the same time.  As long as they are totally separate 
non-concurrent accesses, it sounds like it should be fine?but if they attempt 
concurrently, then concurrency locking between them can?t be garaunteed due to 
changes in the way you are handling it with globals as the code has evolved.  
On UNIX anyway.  Do I have that right?

That?s a very important thing to keep in mind with so many different versions 
of sqlite3 executable code floating around..its built into python a lot older 
then the sqlite3 binary I have installed, which might be different from what is 
compiled into fossil, etc..