Re: [sqlite] Proper way to escape table name and column name

[Paul Linehan]

2011/6/6 Jean-Christophe Deschamps :

>>What is the official way to escape table name that contains a space
>>and column name that contain a spaces?

> You can use square brakets or double-quotes:

> [This is a long name for a small table]
> "This is a long name for a small table as well"


Or, what you can also do is use table names such as

no_special_chars

and/or

do_not_distinguish_between_caps_and_lower_case_in_designing_system

?


Paul...


-- 

lineh...@tcd.ie

Mob: 00 353 86 864 5772
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Jean-Christophe Deschamps]

>Sure, if you're just computing average() then you'll not get any NaNs.

NaNs, NO (if we don't have NaNs in the set) but issues, YES.  It all 
depends.  No one knows.  You don't even know what you're computing exactly.

SQL interpret avg() as "take this data as a set, sum up these numerical 
values and divide by set size".  You can give it a formal mathematical 
unambiguous meaning in the SQL idealized context.

Now, please try this:

create table if not exists TryAvg (data double);
delete from TryAvg;
insert into TryAvg values (1.23456E18);
insert into TryAvg values (60);
insert into TryAvg values (50);
insert into TryAvg values (40);
insert into TryAvg values (-1.23456E18);
select avg(data) from TryAvg;
0

delete from TryAvg;
insert into TryAvg values (60);
insert into TryAvg values (50);
insert into TryAvg values (40);
insert into TryAvg values (1.23456E18);
insert into TryAvg values (-1.23456E18);
select avg(data) from TryAvg;
51.2

delete from TryAvg;
insert into TryAvg values (60);
insert into TryAvg values (50);
insert into TryAvg values (1.23456E18);
insert into TryAvg values (-1.23456E18);
insert into TryAvg values (40);
select avg(data) from TryAvg;
8

delete from TryAvg;
insert into TryAvg values (1.23456E18);
insert into TryAvg values (-1.23456E18);
insert into TryAvg values (60);
insert into TryAvg values (50);
insert into TryAvg values (40);
select avg(data) from TryAvg;
30

Please someone show me what is the correct value of this avg() in 
practice.  One can consider avg() being a randomized multi-valued 
function, depending on the dataset and the order in which a given run 
will process it, which might vary between runs.

I agree this example is just made up but you will recognize that I 
didn't push it far by using exceedingly small/large values 
either.  Magnitudes like these occur in real-world scientific data 
everyday.  That or you don't need FP altogether and can switch to integers.

We are already far from the unambiguous definition above.

The general problem is that, when you perform a calculation that you 
suspect (and you must have a good idea of that _before_ ignition) has 
some chance of getting FP-wise unstable just like the above very simple 
example, then _you_ as an engineer need to conduct that computation 
with great care if you want the result to be meaningful at all and 
closest to the idealized mathematical definition.

Given that SQLite (just like other engines) doesn't allow you to force 
any ordering in the data being fed to aggregate functions, I hardly see 
how you can blindly rely on such computation to manipulate/produce 
scientific data.  The problem with FP is that if you need meaningful 
results, you have to care about such details at every stage of the 
computation.

>  But you might be dividing averages, or whatever.  It's not just the
>aggregate functions, but what you do with their results.

Correct.  And in this context, there are steps in a given computation 
where NaNs are to be simply discarded/ignored and others where they 
absolutely need signaling (that they are quiet or signaling NaNs in the 
IEEE sense is something else).
If some operation is going to result in a NaN, then something has gone 
wrong already and either needs fixing or ignoring the entry.

Major scientific software products --actually used for scientific 
computations whose result impact our lives-- treat NaNs somehow like 
NULLs (e.g. consider the NaN data doesn't exist in a 
summation).  Octave or MatLab are easy examples.  You can't pretend 
they are toy tools for kids.

Also if you want both NaNs support and decent '754 compliance you also 
have to support +0 and -0 as two distinct values, and five rounding 
methods.  How are you going to stuff this in SQL?  And what about 
portability over the giant range of applications using SQLite?

>precision may not be important, yet the ability to handle infinities
>and NaNs might be.

That itself would require far more control at the SQL level than 
possible to implement, as seen from the examples given with round() and 
avg() which are completely basic or simple computations.

>(That said, I think it's clear that there's some
>demand for SQLite3 to be extensible such that extended precision FP
>libraries could be transparently integrated.

That seems way more useful than the dev team spending months/years 
bringing a huge FP library and supporting it on an unbounded range of 
hardware/software platforms.

>And whether SQLite3's existing, limited FP functionality is sufficient 
>or not for any particular app is yet another story.)

In fact this is the whole point.  It works fairly well for what it is 
(Lite), but doesn't hold water for processing FP data susceptible of 
flirting too closely with the limits of flight enveloppe.

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

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Sidney Cadot]

On Jun 6, 2011, at 21:55, Jean-Christophe Deschamps wrote:

> You have a DOUBLE column where you need to store NaN?  Go ahead and 
> store 'NaN' in offending rows.

This cannot be done. They will be turned into NULL.

Sidney

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

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Sidney Cadot]

Hi Jay,

> One should never assume a database uses IEEE 754, so one should never
> assume it uses similar semantics.

One should not assume it unless it is documented, of course. Postgres, for 
example, half-heartedly embraces IEEE-754 'on platforms that use it' (see 
section 8.1.3 of its manual). It documents the fact that +/- infinity and NaN 
are useable on such systems.

> Even those databases that do use
> IEEE 754 for a select few of their types have other considerations.
> In the bigger picture, IEEE 754 makes up, at most, a small part of
> the SQL numeric environment.

For SQL: yes. For SQLite though, it is the only game in town.

> Using 754 as a reference for the rest of the environment strikes me as
> poorly thought out and putting the tail before the dog.

In terms of generic SQL you may be right (although I'd be willing to argue for 
it). However, I think that for a specific DB product, it is a good thing to 
document without ambiguity what the properties and guarantees of the numeric 
types and operations are; and IEEE-754 is the only game in town when it comes 
to properly specified floating point numbers.

I feel this is especially true for the light-weight database system that is 
SQLite. I get the impression that you are advocating to keep floating point 
operations deliberately vague and underspecified (please correct me if I am 
wrong). To me as a developer that is useless; I will never be able to reason 
about the correctness of anything, and I am effectively dependent on the 
(undocumented) effort that the makers of the FP implementation did. 
Effectively, that would be a return to the pre-IEEE 754 wild west of floating 
point calculations.

> [...] Its express purpose was to allow non-technical people to write
> queries and build business applications.

That may have been the optimistic idea 40 years ago, but I think it is time to 
admit that this was completely misguided. If 40 years of relational database 
experience has taught us anything, it is that doing proper SQL (anything beyond 
a basic SELECT) is an actual skill that requires technical prowess.

> [...] This is what most high-level scripting languages like Perl and Python 
> do.

Perl and Python support NaNs and infinities just fine.

> If you want bare metal IEEE 754 for your scientific computing
> application, then you might want to rethink doing your math operations
> in a data storage system.

You are making it sound as if proper support for IEEE-754 types would open up 
some can of worms for regular users, but I really don't see why you think that 
is the case. They would see an occasional "NaN" instead of NULL if they did 
something naughty; I personally think that is a lot more informative.

Compare currently:

sqlite> SELECT 0.0/0.0, 1.0/0.0;
|
sqlite> 

... versus what I would like to see:

sqlite> SELECT 0.0/0.0, 1.0/0.0;
NaN|Inf
sqlite> 

> As you've pointed out, SQLite is more than capable of storing and retrieving 
> non-numeric IEEE 754 values

No, it doesn't support storing and retrieving NaNs. That is an arbitrary limit 
that bites those of us who actually know what they are doing.

Sidney


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

[sqlite] Fwd: Possible small memory leak

[Ronald Burgman]

Hej,

I was wondering about the following piece of code. It is part of the
find_home_dir procedure in shell.c.

2506: #if !defined(_WIN32) && !defined(WIN32) && !defined(__OS2__) &&
!defined(_WIN32_WCE) && !defined(__RTP__) && !defined(_WRS_KERNEL)
               struct passwd *pwent;
               uid_t uid = getuid();
               if( (pwent=getpwuid(uid)) != NULL) {
                       home_dir = pwent->pw_dir;
               }
2512: #endif

I think that pwent might be assigned with some allocated memory by the
call of getpwuid. If that is the case pwent is never freed and there
is a small memroy leak (I don't think this procedure is called that
often).

Now, I'm not sure if getpwuid actually allocates memory. Some
documentation does not mention anything about it; some mention it is not
possible; some mention that getpwuid can result in an ENOMEM
(allocation failed) error,
which clearly indicates that getpwuid tries to allocate some memory.
Since I'm not sure I thought I should ask the experts.

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

Re: [sqlite] Fwd: Possible small memory leak

[Stephan Beal]

On Tue, Jun 7, 2011 at 12:31 PM, Ronald Burgman <
r.w.burg...@student.utwente.nl> wrote:

> Now, I'm not sure if getpwuid actually allocates memory. Some
> documentation does not mention anything about it; some mention it is not
> possible; some mention that getpwuid can result in an ENOMEM
> (allocation failed) error,
> which clearly indicates that getpwuid tries to allocate some memory.
> Since I'm not sure I thought I should ask the experts.
>

According to the linux docs:


  The  return value may point to a static area, and may be overwritten
by
   subsequent calls to getpwent(3), getpwnam(), or  getpwuid().   (Do
 not
   pass the returned pointer to free(3).)

CONFORMING TO
   SVr4, 4.3BSD, POSIX.1-2001.


Since they do not list the do-not-free() as being incompatible with the
listed specifications, my strong instinct is to trust this description. The
linux man pages tend to explicitly mark any behaviours which do not conform
to the spec(s) mentioned in the "CONFORMING TO" section of the given man
page.

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

Re: [sqlite] Fwd: Possible small memory leak

[Alessandro Marzocchi]

> Now, I'm not sure if getpwuid actually allocates memory. Some
> documentation does not mention anything about it; some mention it is not
> possible; some mention that getpwuid can result in an ENOMEM
> (allocation failed) error,
> which clearly indicates that getpwuid tries to allocate some memory.
> Since I'm not sure I thought I should ask the experts.

Hi, reading in getpwuid man page (IEEE Std 1003.1-2008)

"The return value may point to a static area which is overwritten by a
subsequent call to getpwent(), getpwnam(), or getpwuid()."

I think that any real implementation of this function will use a
static area, or otherwise will use a dynamically allocated one but
managed by the library itself. So i belive no free is necessary (and
anyways i can't put a free knowing that the returned pointer may be to
a static area). Regards
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Example Showing ACTUAL Use of sqlite3_auto_extension()

[Steve and Amy]

Would someone, please, show me an example (several actual, successive 
lines of code) in C (or C++) showing a proper use of

sqlite3_auto_extension()?  I have searched the web looking for examples, but 
there are none that I can tell.  I have also, to the best of my understanding, 
readhttp://www.sqlite.org/c3ref/auto_extension.html  numerous times and I have 
had no success.  I am trying to use SQLite with extensionfunctions.c, but 
extensionfunctions.c lacks a function called "xEntryPoint()".  I do not know if 
I am actually supposed to pass a pointer to a function called "xEntryPoint()" 
or not.  I am leaning toward 'no' and in reality I would think "xEntryPoint()" 
is merely to represent some function found in extensionfunctions.c.

Below is code I have tried:
if (sqlite3Database)
sqlite3_close(sqlite3Database);
iCommandResult = sqlite3_open(":memory:",);
bContinue = (iCommandResult == 0);
if (bContinue)
{
iCommandResult = sqlite3_exec(sqlite3Database,
"CREATE TABLE data_table 
(DataID INT, RandomData INT, V_In INT);",
,
0,
);
... Some Code to Fill the sqlite3Database in memory with data
bContinue = (iCommandResult == 0);
}
if (bContinue)
{
iCommandResult = sqlite3_exec(sqlite3Database,
"SELECT DataID, RandomData, 
V_In FROM data_table",
,
this,
);
}

if (sqlite3Database)
{
iCommandResult = sqlite3_auto_extension(0);
if (iCommandResult == 0)
iCommandResult = sqlite3_exec(sqlite3Database,
"SELECT 
STDEV(RandomData) AS RandomData_STDEV, V_In FROM data_table GROUP BY V_In",
,
this,
);
}

Since I cannot find a function called "xEntryPoint()", I tried zero as an 
argument to sqlite3_auto_extension().  Not surprisingly, that did not work.


Here is some more code I tried ('adapted' from the commented out help section 
at the top of extensionfunctions.c):
if (sqlite3Database)
sqlite3_close(sqlite3Database);
iCommandResult = sqlite3_open(":memory:",);
iCommandResult = sqlite3_enable_load_extension(sqlite3Database, 1);
bContinue = (iCommandResult == 0);
if (bContinue)
{
iCommandResult = sqlite3_exec(sqlite3Database,
"CREATE TABLE data_table 
(DataID INT, RandomData INT, V_In INT);",
,
0,
);
... Some Code to Fill the sqlite3Database in memory with data
bContinue = (iCommandResult == 0);
}
if (bContinue)
{
iCommandResult = sqlite3_exec(sqlite3Database,
"SELECT DataID, RandomData, 
V_In FROM data_table",
,
this,
);
}


if (sqlite3Database)
{
iCommandResult = sqlite3_load_extension(sqlite3Database, 0, 0, 
ppcLoadExtErrorMessage);
if (iCommandResult == 0)
iCommandResult = sqlite3_exec(sqlite3Database,
"SELECT 
STDEV(RandomData) AS RandomData_STDEV, V_In FROM data_table GROUP BY V_In",
,
this,
);
}
After executing sqlite3_load_extension, iCommandResult was set to SQLITE_ERROR 
(exactly what I expected), but ppcLoadExtErrorMessage was NULL, so I could not 
tell what the error was about.

Usage instructions from top of extensionfunctions.c:
Usage instructions for applications calling the sqlite3 API functions:
In your application, call sqlite3_enable_load_extension(db,1) to
allow loading external libraries.  Then load the library 
libsqlitefunctions
using sqlite3_load_extension; the third argument should be 0.
See http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions.
Select statements may now use these functions, as in

Re: [sqlite] Cannot load SQLite.Interop.dll but file is in the folder

[Cyrille]

Dear Pavel,

Thanks for your answer. Sorry, I am not sure to understand. You mean 
that, in some case, the application will only work with the same .net 
framework used to compile the DLL. Correct?

Anyway, I have directly used the DLL provided by installing SQLite. 
Could you please let me know how to re-compile both DLLs? Sorry if my 
question is stupid :(

Cyrille


Le 31/05/2011 15:57, Pavel Ivanov a écrit :
> I would ask one more question: do they have the same Windows and .NET
> Framework version as you have? AFAIK, Windows and .NET specifically
> are very picky on all dlls versions. So if for example you have
> SQLite.Interop.dll compiled with a newer .NET Framework and manifest
> in it requires your version then it won't load in older .NET
> Framework. I believe without extra steps during compilation the
> opposite is also true - if you compile on older .NET Framework it
> won't load in newer one.
> But don't ask me how to make it accept different versions of Framework
> and other libraries. I'm no expert in that, just had some chance in
> the past to struggle with such problems.
>
>
> Pavel
>
>
> On Tue, May 31, 2011 at 8:54 AM, Black, Michael (IS)
>   wrote:
>> Unfortunately you're also hitting a rather lousy error message which doesn't 
>> tell you "file not found" or "permission denied" or such..for which I always 
>> deduct points for my students.
>>
>>
>>
>> So...try this..
>>
>> http://www.softpedia.com/get/Programming/Debuggers-Decompilers-Dissasemblers/Strace%20NT.shtml
>>
>> That will hopefully show you what's happening.
>>
>>
>>
>> I suspect that there could be something funky in the file name.
>>
>> You can have them try to rename it and see if that works if they just retype 
>> the whole filename.
>>
>>
>>
>> Also...is there any path mungling going on for 8-char path names?
>> Try putting the app in a simpler directory name structure that doesn't 
>> exceed 8 chars for each dir name.
>>
>>
>>
>> And what happens if they execute from a command prompt?
>>
>>
>>
>> And what's the "working directory" of the menu entry?
>>
>>
>>
>>
>>
>> Michael D. Black
>>
>> Senior Scientist
>>
>> NG Information Systems
>>
>> Advanced Analytics Directorate
>>
>>
>>
>> 
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
>> behalf of Cyrille [rssxpr...@free.fr]
>> Sent: Monday, May 30, 2011 12:19 PM
>> To: General Discussion of SQLite Database
>> Subject: EXT :Re: [sqlite] Cannot load SQLite.Interop.dll but file is in the 
>> folder
>>
>> Dear Michael,
>>
>> Thank you very much for your link. What is strange is that, whatever the
>> SafeDllSearchMode is enable or not, the first location searched is the
>> application one. ANd, as mentioned in my first message, the DLL is in
>> the same folder as the EXE file.
>> I do not know if there is a link but the user who experiences this
>> problem is running my application under WIndows 7 32bit. Is there any
>> possible link?
>>
>> Thanks again
>> Cyrille
>>
>>
>>
>> Le 29/05/2011 20:33, Black, Michael (IS) a écrit :
>>> Welcome to DLL hell...Microsoft keeps changing it...
>>>
>>>
>>>
>>> http://msdn.microsoft.com/en-us/library/ms682586(v=vs.85).aspx
>>>
>>>
>>>
>>>
>>>
>>> Michael D. Black
>>>
>>> Senior Scientist
>>>
>>> NG Information Systems
>>>
>>> Advanced Analytics Directorate
>>>
>>>
>>>
>>> 
>>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
>>> behalf of Cyrille [rssxpr...@free.fr]
>>> Sent: Sunday, May 29, 2011 1:29 PM
>>> To: General Discussion of SQLite Database
>>> Subject: EXT :[sqlite] Cannot load SQLite.Interop.dll but file is in the 
>>> folder
>>>
>>> Dear all,
>>>
>>> One of the users of my application has a critical issue. When he
>>> launches it, he gets an error message "Impossible to load DLL
>>> "SQLite.Interop.DLL". However, the file is in the same folder as the EXE
>>> one.
>>> Stranger: he sent me the whole folder and the application runs very well
>>> from my computers.
>>>
>>> Does somebody have some ideas about the possible cause?
>>>
>>> Thanks for your kind help.
>>> Regards,
>>> Cyrille
>>> ___
>>> 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
>> ___
>> 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
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> 

[sqlite] AIX 6.1 Autoconf support

[Jaco Breitenbach]

Dear experts,

I've been trying to get SQLite compiled on an AIX 6.1 system.  The configure
script that came with version 3.7.3 contains several exceptions for
different AIX versions, notably versions 4 and 5, but not 6.  This means for
example that it is unable to generate a shared library because the
appropriate tests are not performed correctly.  After editing the configure
script and adding a case for AIX 6, I managed to persuade it to create the
shared library instead of the static one.

I was just wondering if there were any plans in the pipeline to add AIX 6.1
support.

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

Re: [sqlite] Example Showing ACTUAL Use of sqlite3_auto_extension()

[Jay A. Kreibich]

On Tue, Jun 07, 2011 at 07:52:37AM -0500, Steve and Amy scratched on the wall:
> Would someone, please, show me an example (several actual, successive 
> lines of code) in C (or C++) showing a proper use of
> sqlite3_auto_extension()?  

  "Using SQLite", p209-211.http://oreilly.com/catalog/9780596521196

> I am trying to use SQLite with extensionfunctions.c, but
> extensionfunctions.c lacks a function called "xEntryPoint()".
> I do not know if I am actually supposed to pass a pointer to a
> function called "xEntryPoint()" or not.  I am leaning toward 'no'
> and in reality I would think "xEntryPoint()" is merely to represent
> some function found in extensionfunctions.c.

  You need to pass a pointer to the extension's entry point function,
  which is not actually named "xEntryPoint"...  that's just the name of
  the variable that accepts the function pointer (generally, any
  variable that starts with "x" in the SQLite source is a function
  pointer).  The entry point function is the same function name that
  is passed to sqlite3_load_extension().

  Also note that the typing on sqlite3_auto_extension() pointer is wrong.
  You must cast the function pointer.  Don't ask; I have no idea.

  By default, most extensions use an entry point function called
  "sqlite3_extension_init()".  This allows sqlite3_load_extension() to 
  automatically find the entry point when the module is loaded.
  You'll notice that extension-functions.c has this function, but only
  if COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE is defined.

  The issue is that if you want to compile a module in statically, all
  the function names need to be unique.  While the dynamic library
  system behind sqlite3_load_extension() can deal with multiple .DLL,
  .so, or .dylib files that all have unique functions with the same
  name, the static linker cannot.  You cannot build a stand-alone
  executable with multiple, global functions that have the same name.
  This is why extension-functions.c omits the default named entry point
  if you're trying to build it for static usage.

  In the case of extension-functions.c, it looks like you should just
  call RegisterExtensionFunctions() directly.  Unfortunately, you'd have
  to do that every time you open a database-- which is exactly the
  issue sqlite3_auto_extension() is trying to avoid.

  If you want to use extension-functions.c with sqlite3_auto_extension(),
  we need to get a bit hacky with the code.

  First, we will want to build it for static use.  To do this,
  comment out this line (line 111 in the current code):


#define COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE 1


  Second, the entry point functions are designed to use the external
  linkages, no matter how they're built.  While this isn't strictly
  required for a static build, I think it keeps it cleaner.  So we need
  to be sure the external linkages are always used.  To do that turn
  this (like 123):


#ifdef COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
#else
#include "sqlite3.h"
#endif


  Into this:


#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1


  Last, we need to be sure the entry point function is always built,
  but we need to  define a globally unique name for when the module
  is used statically.  To do that, we'll make sure the entry point
  function is always built, but we'll build it with a different name
  depending on how the module will be used.  Turn this (line 1837
  in the original file):


#ifdef COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE
int sqlite3_extension_init(
sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi){
  SQLITE_EXTENSION_INIT2(pApi);
  RegisterExtensionFunctions(db);
  return 0;
}
#endif /* COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE */


   Into this:


#ifdef COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE
int sqlite3_extension_init(
#else /* COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE */
int extension_functions_init(
#endif /* COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE */
sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi){
  SQLITE_EXTENSION_INIT2(pApi);
  RegisterExtensionFunctions(db);
  return 0;
}


  Finally, you can call this in your application:

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Sidney Cadot]

> Please someone show me what is the correct value of this avg() in practice.

There are a number of answers to this.

To the level of precision that you specified, all answer are
completely fine; the error is, in all cases, very small relative to
the variance of your input data.

It is an interesting exercise, though. It would be a good idea to sort
the input data on absolute magnitude:

SELECT AVG(data) FROM TryAvg ORDER BY ABS(data) DESC;

But apparently, SQLite ignores the ORDER BY clause because of the
AVG() function, perhaps -erroneously- assuming AVG() is commutative.

This works, but isn't guaranteed to work:

SELECT AVG(data) FROM (SELEFT data FROM TryAvg ORDER BY ABS(data) DESC);

Funnily enough, in Postgres, the test seems to expose an actual bug,
or at least a strange error message:

sidney=# select AVG(data) from TryAvg ORDER BY data DESC;
ERROR:  column "tryavg.data" must appear in the GROUP BY clause or be
used in an aggregate function
LINE 1: select AVG(data) from TryAvg ORDER BY data DESC;
  ^


Now, to address your actual point. The fact that we cannot control
evaluation order in SQL does mean that one has to be wary in case
one's data is badly conditioned. The problem is in SQL: it assumes
commutativity for aggregate functions, and that is a property that no
floating point format conceived can deliver. This is an interesting
fact (as is the fact that, as a consequence of this, identical
invocations of aggregate functions can yield different results under
seemingly identical circumstances in SQL), but I disagree that this
disqualifies using SQL for use in a scientific setting. There are
preciously little types of measurements where one has to calculate
with 16 orders of magnitude as your example does, and in such
circumstances, the scientist needs to be exceedingly on his or her
guard to know what she is doing in ANY language, with ANY storage
mechanism. I think therefore that this example, interesting as it is,
does not provide a conclusive argument in the discussion at hand.

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

[sqlite] Dynamically loaded sqlite (linux)

[Max Vlasov]

Hi,

I'm trying to use sqlite with linux (Ubuntu, Pascal, Lazarus). I'm still not
very familiar with linux development so I might miss something essential.

Two scenarios work ok
- statically linked latest version compiled (3.7.6.3), no options or defines
changed
- Dynamically loaded (dlopen) sqlite used from the installed package
libsqlite3 (libsqlite3.so)


But when I try to use shared library compiled from the same sources and use
it with dlopen some errors appear.
- If I just compile without mapping sqlite3_initialize to init proc, first
call to sqlite3GlobalConfig.m.xRoundup(n) in mallocWithAlarm gives SIGSEGV
error.
- If I map sqlite3_initialize to init, the same happen with the first call
to sqlite3_mutex_enter call.

The library is build with

gcc -g -c -fPIC sqlite3.c
gcc -shared -Wl,-init=sqlite3_initialize -o customsqlite.so sqlite3.o

What am I missing here?

Thanks,

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

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Jay A. Kreibich]

On Tue, Jun 07, 2011 at 05:38:09PM +0200, Sidney Cadot scratched on the wall:
> > Please someone show me what is the correct value of this avg() in practice.

> SELECT AVG(data) FROM TryAvg ORDER BY ABS(data) DESC;
> 
> But apparently, SQLite ignores the ORDER BY clause because of the
> AVG() function, perhaps -erroneously- assuming AVG() is commutative.

  No, this is basic SQL order of operations.  The ORDER BY is applied
  *after* the aggregation.  That SELECT returns one row, so the ORDER
  BY does nothing.

  Actually... the ORDER BY doesn't even make sense.  You're asking it
  to ORDER the AVG() result by the last "data" value in the GROUP,
  only you have no idea which actual "data" value that is.  Most DBs
  consider this statement to be an error.

> This works, but isn't guaranteed to work:
> 
> SELECT AVG(data) FROM (SELEFT data FROM TryAvg ORDER BY ABS(data) DESC);

  No, it isn't guaranteed to work, because ORDER BY only makes sense for
  output.  Row order within SELECTs are undefined, and that remains true
  of a sub-SELECT, even if it contains an ORDER BY.

> Funnily enough, in Postgres, the test seems to expose an actual bug,
> or at least a strange error message:
> 
> sidney=# select AVG(data) from TryAvg ORDER BY data DESC;
> ERROR:  column "tryavg.data" must appear in the GROUP BY clause or be
> used in an aggregate function
> LINE 1: select AVG(data) from TryAvg ORDER BY data DESC;

  Not a bug, just as the message says...  You can't ORDER BY something
  you didn't GROUP BY, or wasn't the output of an aggregate.
  Using an aggregate without a GROUP BY implies a group across the
  whole selection.  Unlike SQLite, PostgreSQL will not let you attempt
  to ORDER BY undefined data.  Just remember the AVG() is computed,
  grouped rows are collapsed, *then* the ORDER BY is applied.

  I like this aspect of SQLite, but many people consider it questionable.
  Personally I'd rather have the freedom to shoot myself in the foot.

> Now, to address your actual point. The fact that we cannot control
> evaluation order in SQL does mean that one has to be wary in case
> one's data is badly conditioned. The problem is in SQL: it assumes
> commutativity for aggregate functions, and that is a property that no
> floating point format conceived can deliver.

  Which really an issue with floating-point.  Addition *is*
  commutative, as any math teacher can tell you.  The fact that 
  it isn't (nor are a lot of other basic arithmetic principals) when
  using a floating-point representation is an issue with the
  representation.  This very fact is one of the main reasons
  traditional RDBMS systems have always kept away from using FP for
  real numbers-- especially in financial operations, where every penny
  counts, no matter how large the sum.

> This is an interesting
> fact (as is the fact that, as a consequence of this, identical
> invocations of aggregate functions can yield different results under
> seemingly identical circumstances in SQL), but I disagree that this
> disqualifies using SQL for use in a scientific setting. There are
> preciously little types of measurements where one has to calculate
> with 16 orders of magnitude as your example does, and in such
> circumstances, the scientist needs to be exceedingly on his or her
> guard to know what she is doing in ANY language, with ANY storage
> mechanism. I think therefore that this example, interesting as it is,
> does not provide a conclusive argument in the discussion at hand.

  Which sounds more or less like "using the right tool for the job."
  If you're aware enough to understand these kinds of issues**
  and if you truly need IEEE 754 down to the bit, you need to be
  writing to the raw hardware and doing all your data manipulation
  yourself.  If you're doing common sense operations with values in
  a similar number domain, SQL, SQLite, Perl, Python, or whatever--
  despite not presenting a 100% IEEE 754 environment-- will generally
  do something "close enough" to the right thing that nobody cares.

  Even if you do care, careful ordering of FP operations isn't so much
  about getting the "right" answer (in most cases) as it is about
  tightening the "close enough" until it falls back into the "nobody
  cares" range.

  ** In my own experiences working at NCSA, two national labs, and
 several university HPC projects, most people doing "scientific
 computing" don't have a damn clue.  Most of the "scientific
 computing" code I saw was written by domain grad students or
 interns that would have a hard time writing Hello World without
 their notes.  If it is a physics program, chances are still
 better than not it is written in FORTRAN.  Error bars?  What are
 those?

   -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

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Nico Williams]

On Tue, Jun 7, 2011 at 4:36 AM, Jean-Christophe Deschamps
 wrote:
> Now, please try this:

You miss the point.  Not every app requires extended precision.  But
just because you don't require extended precision doesn't mean you
can't use FP at all.  It depends on the app.

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

Re: [sqlite] Dynamically loaded sqlite (linux)

[Jay A. Kreibich]

On Tue, Jun 07, 2011 at 07:47:25PM +0400, Max Vlasov scratched on the wall:
> Hi,
> 
> I'm trying to use sqlite with linux (Ubuntu, Pascal, Lazarus). I'm still not
> very familiar with linux development so I might miss something essential.
> 
> Two scenarios work ok
> - statically linked latest version compiled (3.7.6.3), no options or defines
> changed
> - Dynamically loaded (dlopen) sqlite used from the installed package
> libsqlite3 (libsqlite3.so)

  That's not how dynamic libraries work (not normally, anyways).
  Generally you simply tell the compiler/linker to link in the library
  at build time, and allow the usage of dynamic libs.  The dynamic
  link is then done on application start-up by the OS.  In Windows
  terms, it is like using an .DLL by linking in the associated .lib
  file.  Moving from a static library to a dynamic library requires no
  code changes.

  The dlopen() and related functions are for application controlled
  linking.  They're like the LoadLibrary() functions under Windows.

   -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] Fwd: Possible small memory leak

[Nico Williams]

On Tue, Jun 7, 2011 at 5:31 AM, Ronald Burgman
 wrote:
> Now, I'm not sure if getpwuid actually allocates memory. Some
> documentation does not mention anything about it; some mention it is not
> possible; some mention that getpwuid can result in an ENOMEM
> (allocation failed) error,
> which clearly indicates that getpwuid tries to allocate some memory.
> Since I'm not sure I thought I should ask the experts.

getpwuid() and friends do NOT return allocated memory, ever.  They
*may* allocate memory for internal purposes while computing their
results, which is why they may return ENOMEM.  There is no memory leak
here.

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

Re: [sqlite] Dynamically loaded sqlite (linux)

[Martin Gadbois]

On Tue, Jun 7, 2011 at 12:52 PM, Jay A. Kreibich  wrote:

> On Tue, Jun 07, 2011 at 07:47:25PM +0400, Max Vlasov scratched on the wall:
> > Hi,
> >
> > I'm trying to use sqlite with linux (Ubuntu, Pascal, Lazarus). I'm still
> not
> > very familiar with linux development so I might miss something essential.
> >
> > Two scenarios work ok
> > - statically linked latest version compiled (3.7.6.3), no options or
> defines
> > changed
> > - Dynamically loaded (dlopen) sqlite used from the installed package
> > libsqlite3 (libsqlite3.so)
>
>   That's not how dynamic libraries work (not normally, anyways).
>  Generally you simply tell the compiler/linker to link in the library
>  at build time, and allow the usage of dynamic libs.  The dynamic
>  link is then done on application start-up by the OS.  In Windows
>  terms, it is like using an .DLL by linking in the associated .lib
>  file.  Moving from a static library to a dynamic library requires no
>  code changes.
>


There is a way to do a _good_ shared library. I suggest reading the
excellent paper: http://www.akkadia.org/drepper/dsohowto.pdf

As for the OP question, do
gcc -shared -Wl,-init=sqlite3_initialize -o libsqlite.so sqlite3.o

and then link your application with
gcc -L. -lsqlite -o test test.c

This assumes that libsqlite.so is in your current path: . (thus the -L.)

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

[sqlite] Converting sqlite3 to sqlite2.

[Joe Goldthwaite]

I've got a problem. I need to convert an sqlite3 database back to sqlite2's
format on a Windows machine.  I've found instructions on how to do it but
they require sqlite.exe which I can't seem to find anywhere! Does anyone
have a link where they can download the older version?

P.S. I know the sqlite3.exe is backward compatable with the old one but that
doesn't work for me. The development environment I'm using is locked into
Python 2.4 and pysql for python 2.4 can only handle the older sqlite format.
The database I'm reading however has been upgraded to sqlite3.  That leaves
me with no way to read it other than creating an entirely different
development environment using a newer version of Python. It would be much
easier to convert the sqlite3 back to sqlite2 if I could just find a copy of
sqlite.exe for Windows.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Converting sqlite3 to sqlite2.

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/07/2011 11:41 AM, Joe Goldthwaite wrote:
> The development environment I'm using is locked into
> Python 2.4 and pysql for python 2.4 can only handle the older sqlite format.

I'd suggest getting working with SQLite 3.  SQLite 2 is positively ancient
and you'll have trouble finding modern tools that work with it correctly.

The only pysql I can find works only with Oracle.

My own Python wrapper for SQLite (APSW) supports SQLite 3 and Python 2.3
onwards.  pysqlite is also SQLite 3 only and there are Windows binaries for
Python 2.{5,6,7} but I suspect it will compile just fine against Python 2.4.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk3uc4IACgkQmOOfHg372QQW3QCfXP88Hjpj0qEhoqViAaFZ7eoO
COwAn3p2/dmML9dcuwDbjD4I6X6imLjt
=KQtt
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Converting sqlite3 to sqlite2.

[Joe Goldthwaite]

You're right. It looks like the last release of sqlite 3 released in
September '04. It seems like the version of pysqlite that I'm using, which
was released Nov '09' would support it.  Maybe the version of sqlite isn't
my problem. I'm trying to read the Firefox cookies file (cookies.sqlite). It
worked fine in Firefox 3.5 and 3.6 but this is the first time I've tried it
since I upgraded to Firefox 4.  Now I'm getting an error message
"DatabaseError: file is encrypted or is not a database".  I can open the
file with sqlite3.exe and list the cookies.  I can also open it with the
SQLite Manager firefox add-on.

According to SQLite Manager, the file is Schema Version 5, User Version 4
and it's encoded with UTF-8.  I posted a question on the pysqlite list to
see if someone there can give me a clue.

Thanks for the response Roger.

On Tue, Jun 7, 2011 at 11:52 AM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 06/07/2011 11:41 AM, Joe Goldthwaite wrote:
> > The development environment I'm using is locked into
> > Python 2.4 and pysql for python 2.4 can only handle the older sqlite
> format.
>
> I'd suggest getting working with SQLite 3.  SQLite 2 is positively ancient
> and you'll have trouble finding modern tools that work with it correctly.
>
> The only pysql I can find works only with Oracle.
>
> My own Python wrapper for SQLite (APSW) supports SQLite 3 and Python 2.3
> onwards.  pysqlite is also SQLite 3 only and there are Windows binaries for
> Python 2.{5,6,7} but I suspect it will compile just fine against Python
> 2.4.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAk3uc4IACgkQmOOfHg372QQW3QCfXP88Hjpj0qEhoqViAaFZ7eoO
> COwAn3p2/dmML9dcuwDbjD4I6X6imLjt
> =KQtt
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Converting sqlite3 to sqlite2.

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

I see you have now taken this up on the python-sqlite mailing list where
we'll deal with the issue.  You've seriously mixed up Python, SQLite and
pyqlite issues and Firefox's databases are SQLite 3 so SQLite 2 is
completely irrelevant.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk3ujTUACgkQmOOfHg372QQgLACfRlNEXhkd8VzgA/Iv4nspuCo+
yUgAoMERTzSiI3KjBYo25dp3CZ9qAprK
=/m2b
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Converting sqlite3 to sqlite2.

[Joe Goldthwaite]

Hi Roger,

I posted over there because it started looking like it's more of a pysql
problem than a sqlite 2 problem.  I copied the cookies.sqlite file from
Firefox 3.6 and opened it with the sqlite manager. It says the file is
schema version 1, user version 2.  That's a big difference from Firefox 4's
version.  Now I'm thinking that it still might be a version difference. It's
just not a sqlite 2 to 3 difference, it's possibly a schema 1 to 5  or user
2 to 4 difference.


On Tue, Jun 7, 2011 at 1:42 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> I see you have now taken this up on the python-sqlite mailing list where
> we'll deal with the issue.  You've seriously mixed up Python, SQLite and
> pyqlite issues and Firefox's databases are SQLite 3 so SQLite 2 is
> completely irrelevant.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAk3ujTUACgkQmOOfHg372QQgLACfRlNEXhkd8VzgA/Iv4nspuCo+
> yUgAoMERTzSiI3KjBYo25dp3CZ9qAprK
> =/m2b
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Converting sqlite3 to sqlite2.

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/07/2011 01:52 PM, Joe Goldthwaite wrote:
> It says the file is schema version 1, user version 2.  

That has *nothing* to do with the issue.  They are just two fields within a
SQLite 3 database.  You can change them to any arbitrary numbers you want
using pragmas:

  http://www.sqlite.org/pragma.html#pragma_schema_version

A common usage by programs is to keep track of when they update the schemas.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk3ulY4ACgkQmOOfHg372QQHiACfb04Muecwh6tGdjNSFzN6zbqF
rNoAoNzroXLsGlIAgWP30ejk0PMw1JwE
=YW82
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Converting sqlite3 to sqlite2.

[Richard Hipp]

On Tue, Jun 7, 2011 at 4:38 PM, Joe Goldthwaite  wrote:

> I'm trying to read the Firefox cookies file (cookies.sqlite). It
> worked fine in Firefox 3.5 and 3.6 but this is the first time I've tried it
> since I upgraded to Firefox 4.  Now I'm getting an error message
> "DatabaseError: file is encrypted or is not a database".  I can open the
> file with sqlite3.exe and list the cookies.  I can also open it with the
> SQLite Manager firefox add-on.
>

Recent versions of Firefox use "PRAGMA journal_mode=WAL" which requires
SQLite version 3.7.0 or later.  You won't be able to read the database files
with SQLite version 3.6.23.1 or earlier.  You'll get the "file is encrypted
or is not a database" message.


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

Re: [sqlite] Converting sqlite3 to sqlite2.

[Joe Goldthwaite]

Thanks Richard.  It's good to know exactly what the problem is.  I'll stop
posting about it here and follow up with the pysqlite forum.

And thanks again to you Robert.  I didn't realize that the Schema was just a
place for the developer to leave a note. I assumed that it was the sqlite's
internal database schema that was being used to describe some internal
control tables or something like that.

On Tue, Jun 7, 2011 at 2:22 PM, Richard Hipp  wrote:

> On Tue, Jun 7, 2011 at 4:38 PM, Joe Goldthwaite 
> wrote:
>
> > I'm trying to read the Firefox cookies file (cookies.sqlite). It
> > worked fine in Firefox 3.5 and 3.6 but this is the first time I've tried
> it
> > since I upgraded to Firefox 4.  Now I'm getting an error message
> > "DatabaseError: file is encrypted or is not a database".  I can open the
> > file with sqlite3.exe and list the cookies.  I can also open it with the
> > SQLite Manager firefox add-on.
> >
>
> Recent versions of Firefox use "PRAGMA journal_mode=WAL" which requires
> SQLite version 3.7.0 or later.  You won't be able to read the database
> files
> with SQLite version 3.6.23.1 or earlier.  You'll get the "file is encrypted
> or is not a database" message.
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Implicitly creating a table containing the first N consecutive integers?

[Guenther Brunthaler]

Hi all,

I frequently need a table in my queries for several kinds of JOIN
operations which contains just the integers from 1 to N in its rows.

I. e.

SELECT n FROM int_seq where n <= 5;
1
2
3
4
5

However, I would like to achieve the same effect without actually
creating such a table "int_seq".

It it possible in SQLite to create such a table implicitly "on the fly"
using some sort of recursive view/query or built-in special function?

Regards,

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

Re: [sqlite] Implicitly creating a table containing the first N consecutive integers?

[Petite Abeille]

On Jun 7, 2011, at 11:44 PM, Guenther Brunthaler wrote:

> It it possible in SQLite to create such a table implicitly "on the fly"
> using some sort of recursive view/query or built-in special function?

The short of it: no, not out-of-the-box.


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

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Sidney Cadot]

Hi Jay

>  No, this is basic SQL order of operations.

You are right, that first approach I tried was definitely a brainfart.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Converting sqlite3 to sqlite2.

[Richard Hipp]

On Tue, Jun 7, 2011 at 5:41 PM, Joe Goldthwaite  wrote:

> I didn't realize that the Schema was just a
> place for the developer to leave a note. I assumed that it was the sqlite's
> internal database schema that was being used to describe some internal
> control tables or something like that.
>
>
PRAGMA user_version is just a number that the application can set to
whatever it wants.  But PRAGMA schema_version is used internally by SQLite.
If you set it manually, you can potentially corrupt the database file.  So
don't change it unless you really know what you are doing.

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

Re: [sqlite] Implicitly creating a table containing the first N consecutive integers?

[Richard Hipp]

On Tue, Jun 7, 2011 at 5:44 PM, Guenther Brunthaler wrote:

> Hi all,
>
> I frequently need a table in my queries for several kinds of JOIN
> operations which contains just the integers from 1 to N in its rows.
>
> I. e.
>
> SELECT n FROM int_seq where n <= 5;
> 1
> 2
> 3
> 4
> 5
>
> However, I would like to achieve the same effect without actually
> creating such a table "int_seq".
>
> It it possible in SQLite to create such a table implicitly "on the fly"
> using some sort of recursive view/query or built-in special function?
>

See
http://www.sqlite.org/src/artifact/6129adfbe7c7444f2e60cc785927f3aa74e12290for
an example implementation of a virtual table that does something
pretty
much like the above.  We've recently started using it some for our test
cases.



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



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

[sqlite] Re(2): Implicitly creating a table containing the first N consecutive integers?

[Guenther Brunthaler]

On 2011-06-07 23:52, Petite Abeille wrote:

> The short of it: no, not out-of-the-box.

Thanks for the quick reply.

I guess a loadable extension needs to be used for this purpose then.

Is there any such extension already known to be available? I would like
to avoid reinventing the wheel.

I could not find such an extension yet, but perhaps my Google query was
missing the correct search terms.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implicitly creating a table containing the first N consecutive integers?

[Jay A. Kreibich]

On Tue, Jun 07, 2011 at 11:44:20PM +0200, Guenther Brunthaler scratched on the 
wall:
> Hi all,
> 
> I frequently need a table in my queries for several kinds of JOIN
> operations which contains just the integers from 1 to N in its rows.
> 
> I. e.
> 
> SELECT n FROM int_seq where n <= 5;
> 1
> 2
> 3
> 4
> 5
> 
> However, I would like to achieve the same effect without actually
> creating such a table "int_seq".

  Can you use an IN expression, rather than a JOIN?



  If you're doing this from C/C++, you might also check out the
  "intarray" code.  It is designed to be used with IN, but would work
  for this as well.  It basically creates a virtual table that can be
  bound to an in-memory array of integers.  You could setup a large
  array of integers and then bind it to the required length.

  See the notes in the header file.

  src/test_intarray.h
http://www.sqlite.org/cgi/src/artifact/489edb9068bb926583445cb02589344961054207

  src/test_intarray.c
http://www.sqlite.org/cgi/src/artifact/d879bbf8e4ce085ab966d1f3c896a7c8b4f5fc99


   -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] Converting sqlite3 to sqlite2.

[Joe Goldthwaite]

Ah!  Good to know. Thank you!

On Tue, Jun 7, 2011 at 3:10 PM, Richard Hipp  wrote:

> On Tue, Jun 7, 2011 at 5:41 PM, Joe Goldthwaite 
> wrote:
>
> > I didn't realize that the Schema was just a
> > place for the developer to leave a note. I assumed that it was the
> sqlite's
> > internal database schema that was being used to describe some internal
> > control tables or something like that.
> >
> >
> PRAGMA user_version is just a number that the application can set to
> whatever it wants.  But PRAGMA schema_version is used internally by SQLite.
> If you set it manually, you can potentially corrupt the database file.  So
> don't change it unless you really know what you are doing.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Re(3): Implicitly creating a table containing the first N consecutive integers?

[Guenther Brunthaler]

On 2011-06-08 00:12, Richard Hipp wrote:

> See
> http://www.sqlite.org/src/artifact/6129adfbe7c7444f2e60cc785927f3aa74e12290for
>> ** Example:
>> **
>> ** CREATE VIRTUAL TABLE nums USING wholenumber;
>> ** SELECT value FROM nums WHERE value<10;
>> **
>> ** Results in:
>> **
>> ** 1 2 3 4 5 6 7 8 9

Thanks a lot! This is exactly what I have been looking for.


Keep up the good work,

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

Re: [sqlite] Implicitly creating a table containing the first N consecutive integers?

[Jay A. Kreibich]

On Tue, Jun 07, 2011 at 06:12:55PM -0400, Richard Hipp scratched on the wall:
> On Tue, Jun 7, 2011 at 5:44 PM, Guenther Brunthaler 
> wrote:
> 
> See
> http://www.sqlite.org/src/artifact/6129adfbe7c7444f2e60cc785927f3aa74e12290
> for an example implementation of a virtual table that does something
> pretty much like the above.  We've recently started using it some for
> our test cases.

  Ooo... that's even more nifty and simple.

-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] Slow rollback - possible causes?

[Eric Sigler]

Hello!

Does anyone know of a reason why we might be seeing SQLite transaction
rollbacks that take between 60 and 240 seconds?  (One particularly odd
occurrence was almost 20 minutes long!)  This doesn't seem to happen
often, but when it does it's painful.  During the rollback, the disk
is definitely seeing a large amount of IO activity.

The transactions being rolled back don't appear to be specific to any
one table (some of the tables have ~200k rows, one table has ~17M
rows), similarly we've seen transactions rolled back for different
UPDATE and INSERT operations.  (Overall, the workload is for a
high-ish traffic web application.  Lots of reads, far fewer writes).

DB file in WAL mode, checkpointing done every 5 seconds by separate
thread in program
SQLite version: 3.7.2
DB filesize: approximately 15GB
Transaction size: sometimes a few KB, up to ~2MB
OS: Ubuntu Linux 10.04

Hardware-wise, the SQLite instance is running in a VM with 4GB of RAM,
2 virtual CPUs (early 2010 Xeons), IO for this VM runs on a single
750GB SATA disk (Barracuda ES.2) with minimal to moderate other IO
going to it (we'll be separating more of the workload out soon).

Other pragmas that may or may not be relevant:
count_changes = OFF
synchronous = OFF
temp_store = MEMORY
wal_autocheckpoint = 0
cache_size = 300

Any thoughts or ideas?

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

Re: [sqlite] Slow rollback - possible causes?

[Simon Slavin]

On 8 Jun 2011, at 2:02am, Eric Sigler wrote:

> Does anyone know of a reason why we might be seeing SQLite transaction
> rollbacks that take between 60 and 240 seconds?

My initial thought was a faulty hard disk: bad sectors or a duff controller.  
Given that you're running inside a VM, it might also be faulty RAM.

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

[sqlite] WAL and changing modified file time of .db file

[YONGIL JANG]

Dear all,



I have a question of WAL journal mode.


I'm doing some project that uses sqlite with WAL journal mode.

Some days ago, I'd found DB crash and couldn't execute any SQL queries.

But, for a while, I couldn't find any scenarios can make this DB crash.


My question is changing modified file time of .db file can make DB crash
problems?

For some reason, I change last modified time of database file after it has
been opened.

My wondering point is this modified file time.


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

Re: [sqlite] Slow rollback - possible causes?

[Pavel Ivanov]

> DB file in WAL mode, checkpointing done every 5 seconds by separate
> thread in program

Depending on the mode of checkpointing you use it can fail if there
are some other reading or writing transactions in progress. And at the
time you observe very long rollback actual checkpointing happens
because no other transactions are active. Did you monitor the size of
WAL file?


Pavel


On Tue, Jun 7, 2011 at 9:02 PM, Eric Sigler  wrote:
> Hello!
>
> Does anyone know of a reason why we might be seeing SQLite transaction
> rollbacks that take between 60 and 240 seconds?  (One particularly odd
> occurrence was almost 20 minutes long!)  This doesn't seem to happen
> often, but when it does it's painful.  During the rollback, the disk
> is definitely seeing a large amount of IO activity.
>
> The transactions being rolled back don't appear to be specific to any
> one table (some of the tables have ~200k rows, one table has ~17M
> rows), similarly we've seen transactions rolled back for different
> UPDATE and INSERT operations.  (Overall, the workload is for a
> high-ish traffic web application.  Lots of reads, far fewer writes).
>
> DB file in WAL mode, checkpointing done every 5 seconds by separate
> thread in program
> SQLite version: 3.7.2
> DB filesize: approximately 15GB
> Transaction size: sometimes a few KB, up to ~2MB
> OS: Ubuntu Linux 10.04
>
> Hardware-wise, the SQLite instance is running in a VM with 4GB of RAM,
> 2 virtual CPUs (early 2010 Xeons), IO for this VM runs on a single
> 750GB SATA disk (Barracuda ES.2) with minimal to moderate other IO
> going to it (we'll be separating more of the workload out soon).
>
> Other pragmas that may or may not be relevant:
> count_changes = OFF
> synchronous = OFF
> temp_store = MEMORY
> wal_autocheckpoint = 0
> cache_size = 300
>
> Any thoughts or ideas?
>
> -Eric
> ___
> 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] Slow rollback - possible causes?

[Eric Sigler]

We haven't watched the WAL continuously, but we have noticed that the
WAL file grows slowly in size over time between application restarts
(around every 2 weeks).  Currently, the WAL file for one of our DBs is
around 40MB, we've seen it grow up to 130MB or so.  I'll try to catch
the WAL size and see if it changes dramatically.

(Actually, that was another general question we had, should that WAL
file ever shrink during use?  Why would it grow to that size at all?)

-Eric

On Tue, Jun 7, 2011 at 6:44 PM, Pavel Ivanov  wrote:
>> DB file in WAL mode, checkpointing done every 5 seconds by separate
>> thread in program
>
> Depending on the mode of checkpointing you use it can fail if there
> are some other reading or writing transactions in progress. And at the
> time you observe very long rollback actual checkpointing happens
> because no other transactions are active. Did you monitor the size of
> WAL file?
>
>
> Pavel
>
>
> On Tue, Jun 7, 2011 at 9:02 PM, Eric Sigler  wrote:
>> Hello!
>>
>> Does anyone know of a reason why we might be seeing SQLite transaction
>> rollbacks that take between 60 and 240 seconds?  (One particularly odd
>> occurrence was almost 20 minutes long!)  This doesn't seem to happen
>> often, but when it does it's painful.  During the rollback, the disk
>> is definitely seeing a large amount of IO activity.
>>
>> The transactions being rolled back don't appear to be specific to any
>> one table (some of the tables have ~200k rows, one table has ~17M
>> rows), similarly we've seen transactions rolled back for different
>> UPDATE and INSERT operations.  (Overall, the workload is for a
>> high-ish traffic web application.  Lots of reads, far fewer writes).
>>
>> DB file in WAL mode, checkpointing done every 5 seconds by separate
>> thread in program
>> SQLite version: 3.7.2
>> DB filesize: approximately 15GB
>> Transaction size: sometimes a few KB, up to ~2MB
>> OS: Ubuntu Linux 10.04
>>
>> Hardware-wise, the SQLite instance is running in a VM with 4GB of RAM,
>> 2 virtual CPUs (early 2010 Xeons), IO for this VM runs on a single
>> 750GB SATA disk (Barracuda ES.2) with minimal to moderate other IO
>> going to it (we'll be separating more of the workload out soon).
>>
>> Other pragmas that may or may not be relevant:
>> count_changes = OFF
>> synchronous = OFF
>> temp_store = MEMORY
>> wal_autocheckpoint = 0
>> cache_size = 300
>>
>> Any thoughts or ideas?
>>
>> -Eric
>> ___
>> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow rollback - possible causes?

[Pavel Ivanov]

> (Actually, that was another general question we had, should that WAL
> file ever shrink during use?  Why would it grow to that size at all?)

It shrinks, when the full checkpoint is completed successfully. Until
then it grows.


Pavel


On Tue, Jun 7, 2011 at 10:03 PM, Eric Sigler  wrote:
> We haven't watched the WAL continuously, but we have noticed that the
> WAL file grows slowly in size over time between application restarts
> (around every 2 weeks).  Currently, the WAL file for one of our DBs is
> around 40MB, we've seen it grow up to 130MB or so.  I'll try to catch
> the WAL size and see if it changes dramatically.
>
> (Actually, that was another general question we had, should that WAL
> file ever shrink during use?  Why would it grow to that size at all?)
>
> -Eric
>
> On Tue, Jun 7, 2011 at 6:44 PM, Pavel Ivanov  wrote:
>>> DB file in WAL mode, checkpointing done every 5 seconds by separate
>>> thread in program
>>
>> Depending on the mode of checkpointing you use it can fail if there
>> are some other reading or writing transactions in progress. And at the
>> time you observe very long rollback actual checkpointing happens
>> because no other transactions are active. Did you monitor the size of
>> WAL file?
>>
>>
>> Pavel
>>
>>
>> On Tue, Jun 7, 2011 at 9:02 PM, Eric Sigler  wrote:
>>> Hello!
>>>
>>> Does anyone know of a reason why we might be seeing SQLite transaction
>>> rollbacks that take between 60 and 240 seconds?  (One particularly odd
>>> occurrence was almost 20 minutes long!)  This doesn't seem to happen
>>> often, but when it does it's painful.  During the rollback, the disk
>>> is definitely seeing a large amount of IO activity.
>>>
>>> The transactions being rolled back don't appear to be specific to any
>>> one table (some of the tables have ~200k rows, one table has ~17M
>>> rows), similarly we've seen transactions rolled back for different
>>> UPDATE and INSERT operations.  (Overall, the workload is for a
>>> high-ish traffic web application.  Lots of reads, far fewer writes).
>>>
>>> DB file in WAL mode, checkpointing done every 5 seconds by separate
>>> thread in program
>>> SQLite version: 3.7.2
>>> DB filesize: approximately 15GB
>>> Transaction size: sometimes a few KB, up to ~2MB
>>> OS: Ubuntu Linux 10.04
>>>
>>> Hardware-wise, the SQLite instance is running in a VM with 4GB of RAM,
>>> 2 virtual CPUs (early 2010 Xeons), IO for this VM runs on a single
>>> 750GB SATA disk (Barracuda ES.2) with minimal to moderate other IO
>>> going to it (we'll be separating more of the workload out soon).
>>>
>>> Other pragmas that may or may not be relevant:
>>> count_changes = OFF
>>> synchronous = OFF
>>> temp_store = MEMORY
>>> wal_autocheckpoint = 0
>>> cache_size = 300
>>>
>>> Any thoughts or ideas?
>>>
>>> -Eric
>>> ___
>>> 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
>>
> ___
> 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] Slow rollback - possible causes?

[Eric Sigler]

So, should the WAL file shrink back to 0 then?  We're issuing "PRAGMA
wal_checkpoint" to the open DB handle.

-Eric

On Tue, Jun 7, 2011 at 7:06 PM, Pavel Ivanov  wrote:
>> (Actually, that was another general question we had, should that WAL
>> file ever shrink during use?  Why would it grow to that size at all?)
>
> It shrinks, when the full checkpoint is completed successfully. Until
> then it grows.
>
>
> Pavel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow rollback - possible causes?

[Pavel Ivanov]

> We're issuing "PRAGMA
> wal_checkpoint" to the open DB handle.

If you want guaranteed finish of the checkpoint (and thus not growing
WAL-file) you need to issue "PRAGMA wal_checkpoint(RESTART)".


Pavel


On Tue, Jun 7, 2011 at 10:36 PM, Eric Sigler  wrote:
> So, should the WAL file shrink back to 0 then?  We're issuing "PRAGMA
> wal_checkpoint" to the open DB handle.
>
> -Eric
>
> On Tue, Jun 7, 2011 at 7:06 PM, Pavel Ivanov  wrote:
>>> (Actually, that was another general question we had, should that WAL
>>> file ever shrink during use?  Why would it grow to that size at all?)
>>
>> It shrinks, when the full checkpoint is completed successfully. Until
>> then it grows.
>>
>>
>> Pavel
> ___
> 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

[sqlite] sqlite shell tab autocompletion

[prad]

apsw gives a decent completion but runs into issues with python2:
see roger binns post 16 aug 03:24 here:
http://comments.gmane.org/gmane.comp.python.db.sqlite.user/187
(a solution is provided too in this post though i haven't tried it)

what tools are available to provide a decent console experience on sqlite?

-- 
in friendship,
prad

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

Re: [sqlite] sqlite shell tab autocompletion

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/07/2011 08:02 PM, prad wrote:
> apsw gives a decent completion but runs into issues with python2:
> see roger binns post 16 aug 03:24 here:
> http://comments.gmane.org/gmane.comp.python.db.sqlite.user/187
> (a solution is provided too in this post though i haven't tried it)

As far as I know the issue is fixed and I just tested and didn't encounter
it.  If you contact me via email we can diagnose what is happening and get a
fix into the next release.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk3u6UQACgkQmOOfHg372QR39QCgqiOzSdC2ksFJCZZS9TeZBI3Q
NrsAn23Fp8A3EBL6t3RPzL+5ckVHTqNl
=J76i
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite shell tab autocompletion

[prad]

Roger Binns  writes:

> On 06/07/2011 08:02 PM, prad wrote:
>> apsw gives a decent completion but runs into issues with python2:
>> see roger binns post 16 aug 03:24 here:
>> http://comments.gmane.org/gmane.comp.python.db.sqlite.user/187
>> (a solution is provided too in this post though i haven't tried it)
>
> As far as I know the issue is fixed and I just tested and didn't encounter
> it.  If you contact me via email we can diagnose what is happening and get a
> fix into the next release.
>
well hello roger!
i recall reading that there is no issue with python3, but i'm on
python2.6 (debian squeeze) so that might be why i'm having difficulties.

i'll try your workaround as per post later:
>>> shell=apsw.Shell(stdout=codecs.open("/dev/stdout", "w", "utf8"),
stderr=codecs.open("/dev/stderr", "w", "utf8"))
>>> shell.cmdloop()

and if it still doesn't work, then i'll send you an email.
many thx!

-- 
in friendship,
prad

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