Re: [sqlite] Querying column collation setting via SQL

[Simon Slavin]

On 15 May 2012, at 11:25pm, Jeremy Stephens  
wrote:

> Is it possible to find the collation setting of a column via SQL? I've 
> noticed there is a way to do this using the C API (by using 
> sqlite3_table_column_metadata), but short of parsing the CREATE TABLE command 
> found in sqlite_master (which does show the COLLATE info), I can't figure out 
> a way to find this information via SQL.

If it's anywhere I'd expect to see it in "PRAGMA table_info(table-name)":



In the version I'm using … no, it doesn't seem to do it.  Sorry:

SQLite version 3.7.12 2012-03-31 02:46:20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE twoCols (firstCol TEXT, secondCol TEXT COLLATE NOCASE);
sqlite> PRAGMA table_info(twoCols);
0|firstCol|TEXT|0||0
1|secondCol|TEXT|0||0

It doesn't seem to be shown in "PRAGMA index_info(index-name)" either.  Sorry.

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

[sqlite] Querying column collation setting via SQL

[Jeremy Stephens]

Hi all,

Is it possible to find the collation setting of a column via SQL? I've 
noticed there is a way to do this using the C API (by using 
sqlite3_table_column_metadata), but short of parsing the CREATE TABLE 
command found in sqlite_master (which does show the COLLATE info), I 
can't figure out a way to find this information via SQL.


Thanks,
Jeremy



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

Re: [sqlite] finalizing all statements before closing connection

[Simon Slavin]

On 15 May 2012, at 9:03pm, "Jay A. Kreibich"  wrote:

>  That's missing the point.  You're never supposed to get there.
>  Having unaccounted for statements when you close the database is
>  essentially a memory leak.  You've got data structures the
>  application lost track of and didn't clean up.
> 
>  Yes, you can loop over the current statements and finalize them, but
>  this also frees the memory used by those statements, making it invalid.
>  That means one of two things MUST be true:
> 
>  1) Your application lost track of statements.  This is a
> memory/resource leak.  You should fix the problem, not the
> consequences.
> 
>  2) After the call to _close(), your application now has a non-NULL
> pointer somewhere that it THINKS points to valid statement, but in
> reality points to a random chunk of memory.  Attempting to do
> *anything* with this pointer-- including another call to
> _finalize()-- will likely crash the program.

Fair point.  You end up putting headers into the data structure to verify the 
fact that the memory you've been handed is a valid pointer.  And that way 
madness lies.  Okay I see why its not appropriate.  Better not to do any 
cleaning up, to report the non-zero result returned by _close() to the user, 
and quit.

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

Re: [sqlite] finalizing all statements before closing connection

[Jay A. Kreibich]

On Tue, May 15, 2012 at 08:49:50PM +0100, Simon Slavin scratched on the wall:
> 
> On 15 May 2012, at 8:38pm, Baruch Burstein  wrote:
> 
> >  I am working on a  C++ wrapper for sqlite. It
> > is the wrapper's user's responsibility to make sure no statement objects
> > still exist before the database object gets destroyed. This is just a
> > precaution.

> I am surprised that doing _close() without finalizing doesn't do the
> finalizing before (or instead of) generating the error code.  After
> all, one is unlikely to call _close() in error, and slowing down
> _close() isn't going to slow down most apps very much.

  That's missing the point.  You're never supposed to get there.
  Having unaccounted for statements when you close the database is
  essentially a memory leak.  You've got data structures the
  application lost track of and didn't clean up.

  Yes, you can loop over the current statements and finalize them, but
  this also frees the memory used by those statements, making it invalid.
  That means one of two things MUST be true:

  1) Your application lost track of statements.  This is a
 memory/resource leak.  You should fix the problem, not the
 consequences.
 
  2) After the call to _close(), your application now has a non-NULL
 pointer somewhere that it THINKS points to valid statement, but in
 reality points to a random chunk of memory.  Attempting to do
 *anything* with this pointer-- including another call to
 _finalize()-- will likely crash the program.

  About the only time it is valid to loop over statements and finalize
  them is when an application is about to exit.  Even then, you risk
  issues, especially if you've got a swarm of C++ objects with
  distructors.  You would likely be safer to call exit and just let the
  OS dump everything.  You might still risk a crash, however.

> I'm also surprised at how strong the warning is on the page about
> sqlite3_finalize():
> 
> 
> 
> This might be the strongest warning in the whole of the API functionu
> documentation.  Couldn't _finalize() just put a null somewhere in the
> statement as a flag that the statement had already been finalized ?
> I guess checking for it would slow up later calls too much.

  _finalize() frees the statement data structure.  There is no place to
  flag or mark the statement as invalid.  All you have is a bunch of
  pointers that point to junk...  Just like the second loop iteration
  of the first choice from the OP's post.


   -j

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

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] finalizing all statements before closing connection

[Jay A. Kreibich]

On Tue, May 15, 2012 at 10:38:29PM +0300, Baruch Burstein scratched on the wall:
> On Tue, May 15, 2012 at 10:34 PM, Jay A. Kreibich  wrote:
> 
> > On Tue, May 15, 2012 at 10:17:41PM +0300, Baruch Burstein scratched on
> > the wall:
> > > Which of the following should I be running right before calling
> > > sqlite3_close?
> > >
> > > while(stmt = sqlite3_next_stmt(db, stmt))
> > > sqlite3_finalize(stmt);
> > >
> > > while(stmt = sqlite3_next_stmt(db, NULL))
> > > sqlite3_finalize(stmt);
> > >
> > > I am not sure which will have the desired effect.
> >
> >   The second, since "stmt" will be an invalid pointer by the time
> >  the loop comes around and hits sqlite3_next_stmt() again.
> >
> >  Ideally, however, your application should manage its own statements
> >  correctly.  This type of brute-force clean-up can act as a safety net,
> >  but it can also leave dangling pointers elsewhere in the code.
> >  Something in your code must know about those statements... and if
> >  not, then you're leaking memory, which is just as bad.
> >
> 
> I am aware of this. However, I am working on a  C++ wrapper for sqlite. It
> is the wrapper's user's responsibility to make sure no statement objects
> still exist before the database object gets destroyed. This is just a
> precaution.

  If the statement object has a destructor that finalizes the SQLite
  statement, it is very likely to cause an bus error when it tries to
  clean up those objects.

  To really do this right, the database object needs to know about all
  the staement objects associated with it, or the database object needs
  some way to find a statement object, given a database pointer and a
  stmt pointer (for example, a class-static hash of all instanced
  statement objects).  When doing something like this you wouldn't need
  to (or want to) destroy the statement objects, but you'd at least want
  to NULL out the stmt pointer inside the statement object so the object
  doesn't attempt to double finalize it, and the object itself knows it
  is no longer valid.

   -j

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

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] finalizing all statements before closing connection

[Simon Slavin]

On 15 May 2012, at 8:38pm, Baruch Burstein  wrote:

>  I am working on a  C++ wrapper for sqlite. It
> is the wrapper's user's responsibility to make sure no statement objects
> still exist before the database object gets destroyed. This is just a
> precaution.

I am surprised that doing _close() without finalizing doesn't do the finalizing 
before (or instead of) generating the error code.  After all, one is unlikely 
to call _close() in error, and slowing down _close() isn't going to slow down 
most apps very much.

I'm also surprised at how strong the warning is on the page about 
sqlite3_finalize():



This might be the strongest warning in the whole of the API function 
documentation.  Couldn't _finalize() just put a null somewhere in the statement 
as a flag that the statement had already been finalized ?  I guess checking for 
it would slow up later calls too much.

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

Re: [sqlite] finalizing all statements before closing connection

[Baruch Burstein]

On Tue, May 15, 2012 at 10:34 PM, Jay A. Kreibich  wrote:

> On Tue, May 15, 2012 at 10:17:41PM +0300, Baruch Burstein scratched on
> the wall:
> > Which of the following should I be running right before calling
> > sqlite3_close?
> >
> > while(stmt = sqlite3_next_stmt(db, stmt))
> > sqlite3_finalize(stmt);
> >
> > while(stmt = sqlite3_next_stmt(db, NULL))
> > sqlite3_finalize(stmt);
> >
> > I am not sure which will have the desired effect.
>
>   The second, since "stmt" will be an invalid pointer by the time
>  the loop comes around and hits sqlite3_next_stmt() again.
>
>  Ideally, however, your application should manage its own statements
>  correctly.  This type of brute-force clean-up can act as a safety net,
>  but it can also leave dangling pointers elsewhere in the code.
>  Something in your code must know about those statements... and if
>  not, then you're leaking memory, which is just as bad.
>

I am aware of this. However, I am working on a  C++ wrapper for sqlite. It
is the wrapper's user's responsibility to make sure no statement objects
still exist before the database object gets destroyed. This is just a
precaution.

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] finalizing all statements before closing connection

[Simon Slavin]

On 15 May 2012, at 8:17pm, Baruch Burstein  wrote:

> Which of the following should I be running right before calling
> sqlite3_close?
> 
> while(stmt = sqlite3_next_stmt(db, stmt))
>sqlite3_finalize(stmt);

You would, of course, have to initialise your value for stmt before entering 
that loop.

> while(stmt = sqlite3_next_stmt(db, NULL))
>sqlite3_finalize(stmt);
> 
> I am not sure which will have the desired effect.

I think that the second version makes a better clear-up routine.  I think that 
the first one would work too, but the second one looks more fault-tolerant.

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

Re: [sqlite] finalizing all statements before closing connection

[Jay A. Kreibich]

On Tue, May 15, 2012 at 10:17:41PM +0300, Baruch Burstein scratched on the wall:
> Which of the following should I be running right before calling
> sqlite3_close?
> 
> while(stmt = sqlite3_next_stmt(db, stmt))
> sqlite3_finalize(stmt);
> 
> while(stmt = sqlite3_next_stmt(db, NULL))
> sqlite3_finalize(stmt);
> 
> I am not sure which will have the desired effect.

  The second, since "stmt" will be an invalid pointer by the time
  the loop comes around and hits sqlite3_next_stmt() again.

  Ideally, however, your application should manage its own statements
  correctly.  This type of brute-force clean-up can act as a safety net,
  but it can also leave dangling pointers elsewhere in the code.
  Something in your code must know about those statements... and if
  not, then you're leaking memory, which is just as bad.

   -j

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

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] finalizing all statements before closing connection

[Baruch Burstein]

Which of the following should I be running right before calling
sqlite3_close?

while(stmt = sqlite3_next_stmt(db, stmt))
sqlite3_finalize(stmt);

while(stmt = sqlite3_next_stmt(db, NULL))
sqlite3_finalize(stmt);

I am not sure which will have the desired effect.
-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Please review this email to sqlite's mailing list

[Charles Samuels]

I'm using sqlite in addition to another database ("otherdb") storing data in a 
specific manner. I'm trying to keep atomicity of my disk commits. It can take 
several minutes for otherdb to commit, and while it commits it can already 
start accumulating data for a future transaction.

Some of the data coming into this application also goes into the sqlite 
database. But I'd like to keep what's "on the oxide" between sqlite and 
otherdb consistent with eachother. Let's accept that otherdb

At some point, we get a checkpoint; at this instant, what is in otherdb and 
what is in sqlite is what we want committed to sqlite, if either of them 
fails, we can rollback both of them and both databases return to a consistent 
state of a previous checkpoint. The problem is that in the time between  
checkpoint 1 and checkpoint 1 being committed to disk, more data is arriving.

The question here is: where can I put that "more data" so that it won't be 
part of checkpoint 1, but is still accessable by sqlite select statements? 
(Accept that otherdb allows asychronous commits such that I can add more data 
to it that doesn't wind up on disk).

There's a few possibilities with some serious disadvantages:

* When otherdb completes its checkpoint, I commit sqlite; until otherdb and 
sqlite finish their commits, any data going into sqlite instead goes into a 
"mirror" sqlite that I can do queries against meanwhile (but then I have to 
replay *all* of those modifications against the primary sqlite). This can cost 
huge amounts of memory because the sqlite database can get big: 3GiB or more. 
It's also slow because all of a sudden I have to do a whole bunch of sqlite 
statements. It's even slower because now any update I do *normally* has to be 
cloned.

* I could write a virtual filesystem layer for sqlite that somehow accumulates 
changes that I can merge in with insert statements. So it's like the previous 
solution but I use some arm waving in combination with smoke and mirrors to at 
least not make me have two total copies of the database. The problem with this 
one is I don't know how to do it, and even if I did, I wouldn't know how 
reliable it was.

* If sqlite had a "commit transaction to savepoint X", then sqlite commits to 
the oxide everything up to a specific savepoint, keeping the savepoints after 
those committed still as active and uncommitted savepoints. The only 
disadvantage I can think of to this is that sqlite has no such feature.

So how could I do this?


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

[sqlite] Announcement: DRH to be in Munich, Germany July 3rd, 2012

[Stephan Beal]

Hello, all,

(i'm writing this post on behalf of/in conjunction with Richard,
cross-posting to the sqlite and Fossil mailing lists...)

Management summary: DRH will be in Munich, Germany on July 3rd. Would you
like to join him? (If not, you can tap delete now.)

Details:

Richard Hipp will be in Munich, Germany for a few days in July and has
graciously[1] offered to host a presentation and/or hacking session with
regards to sqlite and/or Fossil SCM. Anyone who's within travelling
distance of Munich on that day is welcomed to come along for the ride, with
the following minor caveats:

1) The current plan is that my employer[2] will host the facilities. We can
support up to "about 15-20 people with laptops," and up to about 25
without. We _could_ crowd 50 into one of the rooms if all agree to sit very
close to one another and not move about too much ;). If we manage to get
more people, we will either have to find new facilities or restrict the
number of visitors to a first-come/first-served basis. _Cabled_ internet
access for a small number of people won't be a problem, but we don't
currently have wireless with external net access in the meeting rooms.
AFAIK no German telephone provider offers flat-rate mobile internet (most
cap it at 300MB/month), so it's unlikely that anyone will be willing to
share the mobile phone for tethering purposes.

1.5) If you happen to know of a *flat-rate* mobile internet provider in
Germany, please let me know!

2) i'm going to conservatively reserve 10 of the places for Richard's
guests and people from my company, meaning we can sign up to 10-15 of you
without any capacity concerns. i do not think that we will require all 10
reserved slots, but i need to reserve them nonetheless to avoid a
potentially very embarrassing situation.

3) Even though Germans are renowned for their organization skills, it is
not fully decided exactly what the agenda will look like (probably because
neither myself nor Richard are German). The most likely scenario so far is
that we sit down and do a hacking sprint on the Fossil SCM. Richard
suggested, for example, implementing side-by-side and/or wysiwyg editing
for Fossil's built-in wiki. If, however, we get 8 people who prefer sqlite
and Richard and myself are the only Fossil hackers, then the plan may
change (Richard has the final say-so).

4) Just in case it's not obvious, any presentation/session will be held in
English. Obwohl er ein bisschen deutsch kann, englisch ist seine
Muttersprache (und meine auch).


If any of you will be in the area on July 3rd, would like to attend, and
realistically could attend[3], please contact me (not Richard) directly
OFF-LIST so that i can manage the logistics[4]. As i said, though, at this
point everything except the date is "somewhat fluid," so i will not be able
to give you much more information than is in this mail. There is no hard
registration deadline, but earlier is better so that we can figure out what
the best activity/activities for the group is/are (suggestions from those
attending are certainly welcomed).


Richard and i hope to meet some new faces from the community on July 3rd.[5]

A good evening to all, and Happy Hacking!

(Footnotes follow...)

[1] = my words, not his ;)
[2] = www.consol.de
[3] = if you would "like to be there" but "[probably] cannot be there," i
would respectfully ask that you _not_ sign up, both to reserve my own
bandwidth and to ensure that we don't fill up all seats before those who
_can_ be there get a chance to register.
[4] = Maybe i'll enter you all in a database. Perhaps you can recommend a
good one? ;)
[5] = again, my words, not his ;)

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiler warnings with 3.7.12

[Black, Michael (IS)]

Same thing on mingw 4.5.1



It's not harmful but this cleans it up and should be harmless also.



#undef popen
#define popen(a,b) _popen((a),(b))
#undef pclose
#define pclose(x) _pclose(x)



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Baruch Burstein [bmburst...@gmail.com]
Sent: Tuesday, May 15, 2012 1:33 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] Compiler warnings with 3.7.12

I just compiled the the sqlite3 shell from the 3.7.12 amalgamation
download. I got the following warnings:

shell.c:71:0: warning: "popen" redefined [enabled by default]
In file included from shell.c:33:0:
[mingw
path]\bin\../lib/gcc/x86_64-w64-mingw32/4.7.0/../../../../x86_64-w64-mingw32/include/stdio.h:450:0:
note: this is the location of the previous definition
shell.c:72:0: warning: "pclose" redefined [enabled by default]
In file included from shell.c:33:0:
[mingw
path]\bin\../lib/gcc/x86_64-w64-mingw32/4.7.0/../../../../x86_64-w64-mingw32/include/stdio.h:451:0:
note: this is the location of the previous definition

I am using the MinGW_w64 compiler. Version 4.7.0 from RubenVB's stable
build, both running on and targeting x64 machines.

--
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to write line feed "\n\r" when output a txt file

[Jean-Denis MUYS]

On 14 mai 2012, at 21:03, Kees Nuyt wrote:

> On Mon, 14 May 2012 05:41:08 +, YAN HONG YE 
> wrote:
> […]

> By the way, common lineendings are platform dependent

> MS Windows: \r\n  = 0x0D 0x0A = cr lf
> Unix/Linux: \n= 0x0A = lf
> Apple  Mac: \r= 0x0D = cr
> 

This is incorrect.

First off, Apple Mac is hardware, and as such, it doesn't use *any* line 
ending. It will use CR-LF (Windows) line endings by default if you install 
Windows, and LF (Unix) line endings by default if you install Unix or Linux - 
or Mac OS X which is Unix.

Apple's Operating System has been Mac OS X - certified Unix - for over a 
decade, and has been using LF line endings by default ever since. The Mac 
hasn't been using CR line endings for over 10 years.

10 years is a very long time in this industry. 

Jean-Denis

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