Re: [sqlite] Using SQL or C for data analysis?

[Petite Abeille]

On Mar 28, 2012, at 12:50 AM, Simon wrote:

>  - Look at sqlite's source code and try to implement analytical functions
> in a way that leads to an optimization better than log(n^2) and contribute
> my findings on this topic back to the community.

Enhancing SQLite with analytics would be a major, and very valuable, 
achievement. I wish you success! :)

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

Re: [sqlite] Using SQL or C for data analysis?

[Darren Duncan]

Simon Slavin wrote:

On 27 Mar 2012, at 11:50pm, Simon  wrote:


Thank you all very much for all your answers, they have been most useful.


You're welcome.  Something else to consider is whether you should be doing this 
in C.  C++ can do everything, but it's not ideally suited to heavy mathematical 
operations.  You might want to consider using R (a free Matlab-like system 
available for pretty-much every OS) which already has a ton of mathematical and 
graphical functions:



I can tell you from experience, writing code to extract, rearrange, analyse and 
plot data is /far/ faster in R than it is in C, even if you don't know R very 
well.  R has a package RSQLite which gives it access to data stored in SQLite 
databases.  So you could write your data-gathering code in C, use that to feed 
the data into an SQLite database, then use R to do your analysis.  You might 
like to take a look at this:



If you're using C because you already have something else you want to interface 
with, sorry for wasting your time.


Something not mentioned, but an alternative solution is to use Postgres 8.4+ 
(9.1 latest), which lets you do window functions directly in SQL.  I know thats 
a not-SQLite solution, but it may be the least work to accomplish what you want, 
as its still terse/declarational SQL, and its also open source. -- Darren Duncan

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

Re: [sqlite] Using SQL or C for data analysis?

[Simon Slavin]

On 27 Mar 2012, at 11:50pm, Simon  wrote:

> Thank you all very much for all your answers, they have been most useful.

You're welcome.  Something else to consider is whether you should be doing this 
in C.  C++ can do everything, but it's not ideally suited to heavy mathematical 
operations.  You might want to consider using R (a free Matlab-like system 
available for pretty-much every OS) which already has a ton of mathematical and 
graphical functions:



I can tell you from experience, writing code to extract, rearrange, analyse and 
plot data is /far/ faster in R than it is in C, even if you don't know R very 
well.  R has a package RSQLite which gives it access to data stored in SQLite 
databases.  So you could write your data-gathering code in C, use that to feed 
the data into an SQLite database, then use R to do your analysis.  You might 
like to take a look at this:



If you're using C because you already have something else you want to interface 
with, sorry for wasting your time.

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

Re: [sqlite] Feature request: references from view to himself

[Igor Tandetnik]

On 3/27/2012 7:10 PM, Alexey Pechnikov wrote:

Is it possible to permit this behaviour?

CREATE TEMP TABLE user_record (id INTEGER PRIMARY KEY, user_id INTEGER,
name TEXT);
CREATE TEMP VIEW v_user AS SELECT id, name, (SELECT name FROM v_user WHERE
id=r.user_id) as "user" FROM user_record as r;
Error: no such table: v_user


How do you expect this to work? In order to figure out what records 
belong to v_user, SQLite needs to already know what records are in v_user.


Are you perhaps looking for something like this:

CREATE TEMP VIEW v_user AS
SELECT r.id as id, r.name as name, r2.name as user
FROM user_record r left join user_record r2 on (r2.id = r.user_id);

--
Igor Tandetnik

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

[sqlite] Feature request: references from view to himself

[Alexey Pechnikov]

Is it possible to permit this behaviour?

CREATE TEMP TABLE user_record (id INTEGER PRIMARY KEY, user_id INTEGER,
name TEXT);
CREATE TEMP VIEW v_user AS SELECT id, name, (SELECT name FROM v_user WHERE
id=r.user_id) as "user" FROM user_record as r;
Error: no such table: v_user

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

Re: [sqlite] Using SQL or C for data analysis?

[Simon]

>
>   I would love to do an sql query that would look like the following
> ones.  I wonder if they are possible and valid applications for SQL and
> what would be the proper implementation for these.  I know I can calculate
> all this using C, but it would be most useful (to my later projects) to do
> it in SQL directly (my last example gives you an idea where I'm going).
>
> select max(opening_price, closing_price, high_price) - min(opening_price,
> closing_price, low_price) as day_range...
> select closing_price, moving_average(20,closing_price),
> exp_mov_avg(20,closing_price)...
> select closing_price, moving_average( funky_oscillator( closing_price )
> )...
>


I have described the problem that I was facing.
You guys provided all the pieces of the puzzle.
I now have to solve the problem.


Here's the path that I have decided to take for now, more or less in
priority:

  - Implement what I need now in plain C++, after the data is extracted
from sqlite.
  - Understand what analytical functions are, look at their implementations
and attempt my own in plain C++
  - Experiment at creating my own sqlite functions, agg.functions and
virtual tables as these will be instrumental to my goal or future goals.
  - Look into the libraries you guys suggested for analyzing the data (I
keep this mostly last because my analytic skills are still at a minimum, so
this could be the most difficult path and I also see educational value in
re-inventing the wheel for now, but I do understand this step may be
required to move to higher levels of analysis).
  - Look at sqlite's source code and try to implement analytical functions
in a way that leads to an optimization better than log(n^2) and contribute
my findings on this topic back to the community.

Thank you all very much for all your answers, they have been most useful.

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

Re: [sqlite] Using SQL or C for data analysis?

[Simon]

> I think it is possible to create a custom aggregate function which would
> work on a cross-join of the data to analyse.  The cross-join makes all the
> data available to each bucket (group by Date, for example), and each
bucket
> is basically one row of the whole data.  The aggregate function would thus
> work on all the data, and output its result in every row of the result
> set.  The aggregate function would be in charge of dealing its own window
> (similar to the "varianceStep" function that ignores null values).

You're going to find a big distinction in what SQLite calls 'aggregate
> functions'.  The built-in ones are here:
>
> 
>
> and you can write your own very sophisticated ones.  No problem with that:
> good luck and have fun.  But there are some thing that can't be done within
> the grammar: functions which depend on the order of retrieved results, like
> some kinds of the 'moving_average()' function you mentioned in your
> original post.  This is because SQL tables have no inherent order: you can
> get all the values you want but you don't really know what order they'll
> show up in.  For that, you need your programming language, whatever it is.
>

I've been reading on how these were implemented in sqlite.  This is how I
had the idea to use a cross-join.  But after thinking about it in the metro
back home, I decided this was the least efficient approach.  Because sqlite
first assigns a series of rows to a bucket and then runs the aggregate
function on that bucket's rows, and since I intend to provide all rows to
all buckets (which represent all rows), then I get an optimization of
log(n^2) or worst depending on how I implement my own calculations.  The
only advantage to such a waste of cpu would be in being able to call it in
an sql query.  And this advantage is only viable if my intuition of this
whole project is worth it.

But thanks for reminding the importance of not relying on order!  It's
something I might have tried to force using twisted queries!

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

Re: [sqlite] Using SQL or C for data analysis?

[Simon Slavin]

On 27 Mar 2012, at 9:47pm, Simon  wrote:

> But I don't think aggregates is the key here...  Basically, the kind of
> function I need is something like this:
>  For each row, in this column, calculate the foobar result on all (or a
> group of) the values of another column.
> I think this is the definition of windowing (I just read a few lines on the
> topic at the moment).
> 
> I think it is possible to create a custom aggregate function which would
> work on a cross-join of the data to analyse.  The cross-join makes all the
> data available to each bucket (group by Date, for example), and each bucket
> is basically one row of the whole data.  The aggregate function would thus
> work on all the data, and output its result in every row of the result
> set.  The aggregate function would be in charge of dealing its own window
> (similar to the "varianceStep" function that ignores null values).

You're going to find a big distinction in what SQLite calls 'aggregate 
functions'.  The built-in ones are here:



and you can write your own very sophisticated ones.  No problem with that: good 
luck and have fun.  But there are some thing that can't be done within the 
grammar: functions which depend on the order of retrieved results, like some 
kinds of the 'moving_average()' function you mentioned in your original post.  
This is because SQL tables have no inherent order: you can get all the values 
you want but you don't really know what order they'll show up in.  For that, 
you need your programming language, whatever it is.

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

Re: [sqlite] Using SQL or C for data analysis?

[Simon]

>
> A DBMS is a good way to keep your raw data.  But I highly doubt that a
> majority of your analysis algorithms are going to be expressible in SQL
> without going way beyond the intended purpose of the language.


I think you are right, but the cases where it can be expressed in SQL means
it can also be exploited by SQL and that's what I'm after.
What I mean, is simply being able to apply existing functions that work on
float values, and apply them to the result of other functions.  The
financial math is filled with these...  for example, one guy develops the
rate of change of the price of a stock (roc) which shows the basic
difference between price today and price X days ago.  Then another guy
decides to smooth the result through a moving average.  There is yet one
more guy to come in the future and divide the result by some other result,
and yet another guy in the future to smooth that through another moving
average, and so on.
I understand there are other ways outside SQL to do this.  I know.  I
believe I can code a moving average within 5-15 minutes, but that's not the
point, that's the last resort.


>  You will either find yourself limiting the analyses to what is convenient
> to express in SQL, or you will spend much more time writing queries than
> you would spend describing your data processing in a form more suited to
> functions.  SQL is primarily a language for extracting sets from other
> sets, according to defined criteria (which include set relationships). Your
> analyses, unless they are like nothing I've ever seen or imagined, are
> going to be derived from functions on time series rather than sets, per se.
>  I expect you would find a signal processing library, such as can be found
> in Matlab, Octave, or Scilab, to be a much better start than what you might
> write in SQL in reasonable time.
>

I will definitely look into it. Although I might appear to, I don't want to
close doors just because I have a strong desire to do things my way! ;)


> That said, it is not hard to imagine that selection of datasets might be
> done with SQL, and perhaps some query criteria might include measures
> derived from your own custom functions on time series.


As I mentionned in reply to petite-abeille, a mix of a cross-join and
custom aggregate functions might lead to the concept of analytical
functions.


> There is no real either/or choice posed here.  You can create your own
> custom functions to be incorporated into SQLite queries, including
> aggregate functions.  The aggregate functions might produce some output
> other than what is returned to SQLite.  What I think will be unreasonable
> or unduly limiting is making it happen in bare SQLite.  You will need more.
>

It comes back to your first statement "I highly doubt that a majority of
your analysis algorithms are going to be expressible in SQL [...]".  The
key is determining what needs to be in the core and what needs not.  And
perhaps, what simply cannot be...!  But I'm just getting started and I will
start my undergrad studies in maths in September, until then, I have plenty
of time to struggle with the basics! ;)

Thanks a lot for the information and guidance you provided,
  Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQL or C for data analysis?

[Simon]

> Generally speaking, analytical functions (aka windowing functions [1])
> would appear to be the most useful for your endeavor.
>
> Sadly, SQLite doesn't provide anything like this out-of-the-box.
>

I wasn't aware of the term.  Thanks!  I'll be able to google on that now!
;)

And here are examples of custom functions, including stdev, variance, mode,
> median, lower_quartile, upper_quartile, etc:
>
> http://www.sqlite.org/contrib/download/extension-functions.c?get=25
>

This example file is seriously awesome!  I'll practice a few things, thanks!

But I don't think aggregates is the key here...  Basically, the kind of
function I need is something like this:
  For each row, in this column, calculate the foobar result on all (or a
group of) the values of another column.
I think this is the definition of windowing (I just read a few lines on the
topic at the moment).

I think it is possible to create a custom aggregate function which would
work on a cross-join of the data to analyse.  The cross-join makes all the
data available to each bucket (group by Date, for example), and each bucket
is basically one row of the whole data.  The aggregate function would thus
work on all the data, and output its result in every row of the result
set.  The aggregate function would be in charge of dealing its own window
(similar to the "varianceStep" function that ignores null values).

Although this seems a possible path, I don't feel it's a very appealing
path.  But I'll think about it and maybe I can come up with something.

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

Re: [sqlite] Using SQL or C for data analysis?

[Gabor Grothendieck]

On Tue, Mar 27, 2012 at 3:02 PM, Simon  wrote:
> select closing_price, moving_average( funky_oscillator( closing_price ) )...

There is a moving average calculation in SQLite here but given the
complexity you might prefer to do the analytical portion in your
program:
http://code.google.com/p/sqldf/#Example_16._Moving_Average

It would be nice if sqlite had sql windowing functions to simplify
these sorts of calculations.

-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQL or C for data analysis?

[Doug Currie]

On Mar 27, 2012, at 3:46 PM, Larry Brasfield wrote:

> A DBMS is a good way to keep your raw data.  But I highly doubt that a 
> majority of your analysis algorithms are going to be expressible in SQL 
> without going way beyond the intended purpose of the language.  You will 
> either find yourself limiting the analyses to what is convenient to express 
> in SQL, or you will spend much more time writing queries than you would spend 
> describing your data processing in a form more suited to functions.  

Yes

> […]  I expect you would find a signal processing library, such as can be 
> found in Matlab, Octave, or Scilab, to be a much better start than what you 
> might write in SQL in reasonable time.

Or use a Statistical Computing language and environment such as R with SQLite

http://www.r-project.org/

http://cran.r-project.org/web/packages/RSQLite/index.html


e

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

Re: [sqlite] Using SQL or C for data analysis?

[Larry Brasfield]

Hi there,
  I'm about to start a project I have been thinking about for a long
while.  I basically wish to analyse stock market data.  I already have the
data in a table and I'm now in the process of writing my own indicators and
oscillators.  I hope to learn while re-inventing this wheel and perhaps
explore different things unavailable in most stocks analysis software I
found.

  I would love to do an sql query that would look like the following ones.
I wonder if they are possible and valid applications for SQL and what would
be the proper implementation for these.  I know I can calculate all this
using C, but it would be most useful (to my later projects) to do it in SQL
directly (my last example gives you an idea where I'm going).

select max(opening_price, closing_price, high_price) - min(opening_price,
closing_price, low_price) as day_range...
select closing_price, moving_average(20,closing_price),
exp_mov_avg(20,closing_price)...
select closing_price, moving_average( funky_oscillator( closing_price ) )...

I think creating a module and using virtual tables may be an idea...  but
doing the above selects would involve some serious twists I think.  What
I'd need are virtual functions or something like that...


A DBMS is a good way to keep your raw data.  But I highly doubt that a 
majority of your analysis algorithms are going to be expressible in SQL 
without going way beyond the intended purpose of the language.  You will 
either find yourself limiting the analyses to what is convenient to 
express in SQL, or you will spend much more time writing queries than 
you would spend describing your data processing in a form more suited to 
functions.  SQL is primarily a language for extracting sets from other 
sets, according to defined criteria (which include set relationships). 
Your analyses, unless they are like nothing I've ever seen or imagined, 
are going to be derived from functions on time series rather than sets, 
per se.  I expect you would find a signal processing library, such as 
can be found in Matlab, Octave, or Scilab, to be a much better start 
than what you might write in SQL in reasonable time.


That said, it is not hard to imagine that selection of datasets might be 
done with SQL, and perhaps some query criteria might include measures 
derived from your own custom functions on time series.



Can you guys confirm with me whether I'm asking too much and I should
concentrate on a C or C++ implementation of my functions, or is there a way
to make it happen in sqlite?


There is no real either/or choice posed here.  You can create your own 
custom functions to be incorporated into SQLite queries, including 
aggregate functions.  The aggregate functions might produce some output 
other than what is returned to SQLite.  What I think will be 
unreasonable or unduly limiting is making it happen in bare SQLite.  You 
will need more.



Thanks,
  Simon

Have fun!
--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQL or C for data analysis?

[Petite Abeille]

On Mar 27, 2012, at 9:02 PM, Simon wrote:

>  I would love to do an sql query that would look like the following ones.
> I wonder if they are possible and valid applications for SQL and what would
> be the proper implementation for these.

Generally speaking, analytical functions (aka windowing functions [1]) would 
appear to be the most useful for your endeavor.

Sadly, SQLite doesn't provide anything like this out-of-the-box.

> I think creating a module ... may be an idea… 

You can always create you own (aggregate) functions:

http://www.sqlite.org/c3ref/create_function.html

Here is some inspiration:

SQL for Analysis and Reporting
http://docs.oracle.com/cd/B28359_01/server.111/b28313/analysis.htm

And here are examples of custom functions, including stdev, variance, mode, 
median, lower_quartile, upper_quartile, etc:

http://www.sqlite.org/contrib/download/extension-functions.c?get=25


[1] http://en.wikipedia.org/wiki/Window_function_(SQL)#Window_function
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Using SQL or C for data analysis?

[Simon]

Hi there,
  I'm about to start a project I have been thinking about for a long
while.  I basically wish to analyse stock market data.  I already have the
data in a table and I'm now in the process of writing my own indicators and
oscillators.  I hope to learn while re-inventing this wheel and perhaps
explore different things unavailable in most stocks analysis software I
found.

  I would love to do an sql query that would look like the following ones.
I wonder if they are possible and valid applications for SQL and what would
be the proper implementation for these.  I know I can calculate all this
using C, but it would be most useful (to my later projects) to do it in SQL
directly (my last example gives you an idea where I'm going).

select max(opening_price, closing_price, high_price) - min(opening_price,
closing_price, low_price) as day_range...
select closing_price, moving_average(20,closing_price),
exp_mov_avg(20,closing_price)...
select closing_price, moving_average( funky_oscillator( closing_price ) )...

I think creating a module and using virtual tables may be an idea...  but
doing the above selects would involve some serious twists I think.  What
I'd need are virtual functions or something like that...
Can you guys confirm with me whether I'm asking too much and I should
concentrate on a C or C++ implementation of my functions, or is there a way
to make it happen in sqlite?

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

Re: [sqlite] VACUUMing large DBs

[Simon Slavin]

On 27 Mar 2012, at 5:53pm, Pete  wrote:

> Interesting. Does that mean any open transaction other than the VACUUM
> transaction?  I'm still confused.

This is my fault.  I forgot VACUUM was an exception.  Please ignore what I 
wrote and believe what Peter wrote: You must /not/ have a transaction open when 
you do a VACUUM, and VACUUM executes without a transaction.

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

Re: [sqlite] VACUUMing large DBs

[Jay A. Kreibich]

On Tue, Mar 27, 2012 at 09:53:38AM -0700, Pete scratched on the wall:
> Interesting. Does that mean any open transaction other than the VACUUM
> transaction?  I'm still confused.

  The database connection running the VACUUM command cannot currently
  be in a transaction, nor can there be any active (stepping) statements
  associated with the connection (which would have an implied transaction).

  Other connections can be doing whatever they want, but the VACUUM
  command needs a full set of locks to run.  Normal locking policy
  applies.

-j

> On Tue, Mar 27, 2012 at 9:00 AM,  wrote:
> > Date: Mon, 26 Mar 2012 10:25:49 -0700 (PDT)
> > From: Peter Aronson 
> > To: General Discussion of SQLite Database 
> > Subject: Re: [sqlite] VACUUMing large DBs
> > Message-ID:
> ><1332782749.22198.yahoomai...@web180307.mail.gq1.yahoo.com>
> > Content-Type: text/plain; charset=iso-8859-1
> >
> > Actually, it can't be in a transaction.? To quote: "A VACUUM will fail if
> > there
> > is an open transaction, or if there are one or more active SQL statements
> > when
> > it is run."? (See http://www.sqlite.org/lang_vacuum.html).

-- 
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] VACUUMing large DBs

[Pete]

Interesting. Does that mean any open transaction other than the VACUUM
transaction?  I'm still confused.
Pete

On Tue, Mar 27, 2012 at 9:00 AM,  wrote:

> Message: 5
> Date: Mon, 26 Mar 2012 10:25:49 -0700 (PDT)
> From: Peter Aronson 
> To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] VACUUMing large DBs
> Message-ID:
><1332782749.22198.yahoomai...@web180307.mail.gq1.yahoo.com>
> Content-Type: text/plain; charset=iso-8859-1
>
> Actually, it can't be in a transaction.? To quote: "A VACUUM will fail if
> there
> is an open transaction, or if there are one or more active SQL statements
> when
> it is run."? (See http://www.sqlite.org/lang_vacuum.html).
>
> Best regards,
>
> Peter
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query to remove duplicate values ​​in a field?

[Simon Slavin]

On 27 Mar 2012, at 5:12pm, Alessio Forconi  wrote:

> is there a way to update a field in this way?
> 
> Fieldtoupdate:
> 1) Location: Rome. Location: New York.
> 2) Location: Florence. Location: Tuscany. Place: Arezzo
> 
> I want to update in this manner:
> 1) Location: Rome. Lazio.
> 2) Location: Florence. Tuscany. Arezzo

Use the replace function



to replace 'Location: ' with ''.  Then put one location back in at the 
beginning of the string using || like this:

'Location: '||FieldToupdate

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

[sqlite] Query to remove duplicate values ​​in a field?

[Alessio Forconi]

Hello everyone,
is there a way to update a field in this way?

Fieldtoupdate:
1) Location: Rome. Location: New York.
2) Location: Florence. Location: Tuscany. Place: Arezzo

I want to update in this manner:
1) Location: Rome. Lazio.
2) Location: Florence. Tuscany. Arezzo

It can be done with a query?

Thank you.

--
Alessio Forconi

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

Re: [sqlite] memory handling problems in 3.710 - 3.7.11

[Black, Michael (IS)]

Don't see any problems here with valgrind.



Red Hat Enterprise Linux Server release 5.7 (Tikanga)



[sqlite-amalgamation-3071100]$ gcc -g -o shell shell.c sqlite3.c -ldl -lpthread
[sqlite-amalgamation-3071100]$ ./shell
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a(b int);
sqlite> [mblack@melb0113 sqlite-amalgamation-3071100]$
[sqlite-amalgamation-3071100]$ valgrind --leak-check=full ./shell
==28575== Memcheck, a memory error detector
==28575== Copyright (C) 2002-2009, and GNU GPL'd, by Julian Seward et al.
==28575== Using Valgrind-3.5.0 and LibVEX; rerun with -h for copyright info
==28575== Command: ./shell
==28575==
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a(b int);
sqlite> .quit
==28575==
==28575== HEAP SUMMARY:
==28575== in use at exit: 0 bytes in 0 blocks
==28575==   total heap usage: 171 allocs, 171 frees, 126,074 bytes allocated
==28575==
==28575== All heap blocks were freed -- no leaks are possible
==28575==
==28575== For counts of detected and suppressed errors, rerun with: -v
==28575== ERROR SUMMARY: 0 errors from 0 contexts (suppressed: 4 from 4)



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 Laszlo Boszormenyi [g...@debian.hu]
Sent: Monday, March 26, 2012 12:58 PM
To: Richard Hipp
Cc: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] memory handling problems in 3.710 - 3.7.11

Dear Richard,

On Mon, 2012-03-26 at 08:20 -0400, Richard Hipp wrote:
> On Sun, Mar 25, 2012 at 9:32 PM, Laszlo Boszormenyi 
> wrote:
> On Debian and amd64 architecture SQLite3 has a severe problem.
> If I just
> start it, I can create a simple table like "create table a(b
> int);".
> However when I set "export MALLOC_CHECK_=1" then the mentioned
> table
> creation reports:
> *** glibc detected *** sqlite3: free(): invalid pointer:
> 0x7fba85c30560 ***
>
> Usually, this kind of thing is an indication that your application
> (not SQLite) is corrupting the heap, and SQLite just happen to have
> the misfortune of being the first to bump into that corruption.
 We misunderstand each other somewhere. As I wrote, I just start sqlite3
itself. It's not embedded or used by anything else. Here is what happens
on my amd64 box.
julia login: gcs
Password:
Last login: Sat Mar 24 06:10:27 CET 2012 on tty1
Linux julia 3.2.0-2-amd64 #1 SMP Tue Mar 13 16:54:04 UTC 2012 x86_64

The programs included with the Debian GNU/Linux system are free
software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
No mail.
gcs@julia:~$ id gcs
uid=1000(gcs) gid=1000(gcs)
groups=1000(gcs),20(dialout),24(cdrom),25(floppy),29(audio),44(video),46(plugdev),112(kvm),132(uml-net)
gcs@julia:~$ dpkg -l sqlite3
Desired=Unknown/Install/Remove/Purge/Hold
|
Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name   VersionDescription
+++-==-==-
ii  sqlite33.7.11-2   Command line interface for SQLite 3
gcs@julia:~$ sqlite3 --version
3.7.11 2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d0062dc364669
gcs@julia:~$ sqlite3
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a(b int);
sqlite> [control+d]
gcs@julia:~$ export MALLOC_CHECK_=1
gcs@julia:~$ sqlite3
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a(b int);
*** glibc detected *** sqlite3: free(): invalid pointer: 0x7f89909f78d0 ***
sqlite> [control+d]
gcs@julia:~$ export MALLOC_CHECK_=2
gcs@julia:~$ sqlite3
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a(b int);
Aborted
gcs@julia:~$

> Have you tried running your application using valgrind?
 You mean 'valgrind /bin/sh sqlite3'? Nope, only the following.
gcs@julia:~$ valgrind sqlite3
==14129== Memcheck, a memory error detector
==14129== Copyright (C) 2002-2011, and GNU GPL'd, by Julian Seward et al.
==14129== Using Valgrind-3.7.0 and LibVEX; rerun with -h for copyright info
==14129== Command: sqlite3
==14129==
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a(b 

Re: [sqlite] HOW TO EXPORT TABLE HEAD

[Gabor Grothendieck]

On Mon, Mar 26, 2012 at 11:38 PM, YAN HONG YE  wrote:
> WHEN I export sqlite database to a html file or txt file, I couldn't know how 
> to include the database table head.
> who can tell me?
> Thank you!

Use -header like this:
  sqlite3 -html -header my.db "select * from mytable"

See output of:
  sqlite3 -help
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] NaN in, 0.0 out?

[Simon Slavin]

On 27 Mar 2012, at 3:42am, "Jay A. Kreibich"  wrote:

> On Tue, Mar 27, 2012 at 03:30:03AM +0100, Simon Slavin scratched on the wall:
>> 
>> On 27 Mar 2012, at 3:12am, Jay A. Kreibich  wrote:
>> 
>>> On Sun, Mar 25, 2012 at 05:48:01AM +0100, Simon Slavin scratched on the 
>>> wall:
>>> 
 Can those values be passed from a 'double' C variable ?  I believe so.
 So I see no reason why SQLite shouldn't be storing them.
>>> 
>>> If, in the sense of "can I arrange the bits that way", then yes.
>>> However, in the sense of "is this a valid floating point number?" the
>>> answer is no.
>> 
>> Okay.  That's the heart of the matter: what do we mean by "REAL" in the
>> documentation.  The word "REAL" right next to "IEEE" makes me think it 
>> handles NaN, +Inf and -Inf.  But it's not explicitly stated.  So fair
>> point: if you don't claim to handle NaN then you don't have to.
> 
>  Not do drag things out,

On /this/ list ?

> but how would you "handle" a NaN?  If someone
>  writes a signaling-NaN into the database, the DB will start to throw
>  floating point exceptions, terminating the host process, with almost
>  any calculation.  Is that really something anyone would consider
>  "correct" behavior?  Sounds more like a great attack vector to me.

Okay, you inspired me to look up what special values exist in IEEE.  I didn't 
remember many of them.  According to Wikipedia you get no more than the 
following

+0, -0
Subnormal numbers
+Infinity, -Infinity
Quiet and signaling NaN

I think it might be reasonable to handle a signaling NaN at bind time -- to 
reject it before it even gets stored.  Make up a new SQLITE_ result code which 
means 'attempt to bind unhandled value' which you could use for this and 
perhaps other things, like an attempt to bind a BLOB or zeroblob with negative 
length (unless there's already a result code for these, in which case use that 
for signaling NaNs).

I think the OS's (or platform's) library routines can be used to handle 
everything else.  They should give consistent results over all platforms.

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

Re: [sqlite] memory handling problems in 3.710 - 3.7.11

[Laszlo Boszormenyi]

Dear Richard,

On Mon, 2012-03-26 at 08:20 -0400, Richard Hipp wrote:
> On Sun, Mar 25, 2012 at 9:32 PM, Laszlo Boszormenyi 
> wrote:
> On Debian and amd64 architecture SQLite3 has a severe problem.
> If I just
> start it, I can create a simple table like "create table a(b
> int);".
> However when I set "export MALLOC_CHECK_=1" then the mentioned
> table
> creation reports:
> *** glibc detected *** sqlite3: free(): invalid pointer:
> 0x7fba85c30560 ***
> 
> Usually, this kind of thing is an indication that your application
> (not SQLite) is corrupting the heap, and SQLite just happen to have
> the misfortune of being the first to bump into that corruption.
 We misunderstand each other somewhere. As I wrote, I just start sqlite3
itself. It's not embedded or used by anything else. Here is what happens
on my amd64 box.
julia login: gcs
Password: 
Last login: Sat Mar 24 06:10:27 CET 2012 on tty1
Linux julia 3.2.0-2-amd64 #1 SMP Tue Mar 13 16:54:04 UTC 2012 x86_64

The programs included with the Debian GNU/Linux system are free
software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
No mail.
gcs@julia:~$ id gcs
uid=1000(gcs) gid=1000(gcs)
groups=1000(gcs),20(dialout),24(cdrom),25(floppy),29(audio),44(video),46(plugdev),112(kvm),132(uml-net)
gcs@julia:~$ dpkg -l sqlite3
Desired=Unknown/Install/Remove/Purge/Hold
|
Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name   VersionDescription
+++-==-==-
ii  sqlite33.7.11-2   Command line interface for SQLite 3
gcs@julia:~$ sqlite3 --version
3.7.11 2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d0062dc364669
gcs@julia:~$ sqlite3
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a(b int);
sqlite> [control+d]
gcs@julia:~$ export MALLOC_CHECK_=1
gcs@julia:~$ sqlite3
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a(b int);
*** glibc detected *** sqlite3: free(): invalid pointer: 0x7f89909f78d0 ***
sqlite> [control+d]
gcs@julia:~$ export MALLOC_CHECK_=2
gcs@julia:~$ sqlite3
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a(b int);
Aborted
gcs@julia:~$ 

> Have you tried running your application using valgrind?
 You mean 'valgrind /bin/sh sqlite3'? Nope, only the following.
gcs@julia:~$ valgrind sqlite3
==14129== Memcheck, a memory error detector
==14129== Copyright (C) 2002-2011, and GNU GPL'd, by Julian Seward et al.
==14129== Using Valgrind-3.7.0 and LibVEX; rerun with -h for copyright info
==14129== Command: sqlite3
==14129== 
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a(b int);
sqlite> ==14129== 
==14129== HEAP SUMMARY:
==14129== in use at exit: 127,884 bytes in 488 blocks
==14129==   total heap usage: 923 allocs, 435 frees, 306,164 bytes allocated
==14129== 
==14129== LEAK SUMMARY:
==14129==definitely lost: 60 bytes in 1 blocks
==14129==indirectly lost: 240 bytes in 10 blocks
==14129==  possibly lost: 0 bytes in 0 blocks
==14129==still reachable: 127,584 bytes in 477 blocks
==14129== suppressed: 0 bytes in 0 blocks
==14129== Rerun with --leak-check=full to see details of leaked memory
==14129== 
==14129== For counts of detected and suppressed errors, rerun with: -v
==14129== ERROR SUMMARY: 0 errors from 0 contexts (suppressed: 8 from 6)
gcs@julia:~$ 

So let's do it again with --leak-check=full as seen below.
gcs@julia:~$ valgrind --leak-check=full sqlite3
==14158== Memcheck, a memory error detector
==14158== Copyright (C) 2002-2011, and GNU GPL'd, by Julian Seward et al.
==14158== Using Valgrind-3.7.0 and LibVEX; rerun with -h for copyright info
==14158== Command: sqlite3
==14158== 
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a(b int);
sqlite> ==14158== 
==14158== HEAP SUMMARY:
==14158== in use at exit: 127,844 bytes in 488 blocks
==14158==   total heap usage: 923 allocs, 435 frees, 306,018 bytes allocated
==14158== 
==14158== 300 (60 direct, 240 indirect) bytes in 1 blocks are definitely lost 
in loss record 27 of 53
==14158==at 0x402894D: malloc (in 
/usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so)
==14158==by 0x4FF899C: nss_parse_service_list (nsswitch.c:626)
==14158==by 0x4FF9171: __nss_database_lookup (nsswitch.c:167)
==14158==

[sqlite] Parser bug on sub joins when on right side of the join operator

[Mathieu TAUZIN]

Hi,

 

According to their definition
(http://sqlite.org/syntaxdiagrams.html#single-source) , Join sources (named
single-source) are either : 

* a table or view with an optional alias and/or with an
optional index

* a sub query with an optional alias

* a sub join (with no alias)

 

In SQLite parser.y source code we can find on line 496 the grammar rule
handling those three cases (in the same order)

 



...

seltablist(A) ::= stl_prefix(X) nm(Y) dbnm(D) as(Z) indexed_opt(I) on_opt(N)
using_opt(U). {

  A = sqlite3SrcListAppendFromTerm(pParse,X0,N,U);

  sqlite3SrcListIndexedBy(pParse, A, ); }

 

seltablist(A) ::= stl_prefix(X) LP select(S) RP

as(Z) on_opt(N) using_opt(U). {

A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,,S,N,U);

  }

 

seltablist(A) ::= stl_prefix(X) LP seltablist(F) RP

as(Z) on_opt(N) using_opt(U). {

if( X==0 && Z.n==0 && N==0 && U==0 ){

  A = F;

}else{

  Select *pSubquery;

  sqlite3SrcListShiftJoinType(F);

  pSubquery = sqlite3SelectNew(pParse,0,F,0,0,0,0,0,0,0);

  A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,,pSubquery,N,U);

}

  }

...



 

Case 1 and 2 are handled properly but as you can see the third definition
(wich should deal with sub joins) contains mistakes :

#1 : It allows an as clause after the parenthesis

#2 : on the right of a join operator (else { ... }) it
generates a subquery instead of merging F (which is a seltabList, not a sub
query) with X into A.

 

I wish I could propose a fix but I have no skills in C/yacc.

 

Hope this will help anyway.

 

Thanks

 

 

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

Re: [sqlite] .headers error

[Simon Davies]

On 27 March 2012 06:03, YAN HONG YE  wrote:
> C:\sqlite\lib>sqlite3 -html foods.db "select * from dzh;" >mm.html "-headers 
> on"
>
> sqlite3: Error: too many options: "-headers on"
> Use -help for a list of options.
> how to write this command?

sqlite3 -html -header foods.db "select * from dzh;" >mm.html

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

Re: [sqlite] how to export to html file with table

[ajm]

Hi:

Perhaps some of you would be interested AscToTab, a free utility that is over 
there from a long time.

http://www.jafsoft.com/asctotab/

HTH.

Adolfo J. Milllán.
Z.S.

>
>  Mensaje original 
> De: Simon Slavin 
> Para:  General Discussion of SQLite Database 
> Fecha:  Tue, 27 Mar 2012 03:26:30 +0200
> Asunto:  Re: [sqlite] how to export to html file with table
>
> 
>
>
>On 27 Mar 2012, at 2:22am, YAN HONG YE  wrote:
>
>> From: Steinar Midtskogen 
>> 
>>> to export to the html file  command is:
>>> 
>>> sqlite3 -html film.db "select * from film" > mm.html
>> 
>> but the result had no table,only a text file without line feed, how to 
>> export the html with tables and table column header?
>
>Are you sure ?  The '-html' is there so it generates HTML table format  Works 
>for me.
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@sqliteorg
>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] NaN in, 0.0 out?

[Dominique Pellé]

Francis J. Monari, Esquire  wrote:

> All,
>
> How are +infinity and -infinity handled?
>
> Frank.

Don't forget signed zero, signaling and quiet NaN.

Here is a short c++ programs to show how +infinity, -infinity
zero, -zero, quiet NaN, signaling NaN are internally represented
in IEEE-754:

$ cat double.cpp
#include 
#include 
#include 

using namespace std;
int main()
{
  union
  {
double   d;
uint64_t i;
  } inf, ninf, zero, nzero, qnan, snan;

  if (numeric_limits::has_infinity)
  {
inf.d = numeric_limits::infinity();
ninf.d = -inf.d;
cout << "+infinity:\t[" <<  inf.d << "]" << "\t0x" << hex <<
inf.i << dec << endl;
cout << "-infinity:\t[" << ninf.d << "]" << "\t0x" << hex <<
ninf.i << dec << endl;

zero.d  = 1/inf.d;
nzero.d = 1/ninf.d;
cout << "zero\t\t["  <<  zero.d << "]" << "\t0x" << hex <<  zero.i
<< dec << endl;
cout << "-zero\t\t[" << nzero.d << "]" << "\t0x" << hex << nzero.i
<< dec << endl;
  }
  if (numeric_limits::has_quiet_NaN)
  {
qnan.d = numeric_limits::quiet_NaN();
cout << "quiet nan:\t[" << qnan.d << "]" << "\t0x" << hex <<
qnan.i << dec << endl;
  }
  if (numeric_limits::has_signaling_NaN)
  {
snan.d = numeric_limits::signaling_NaN();
cout << "signaling nan:\t[" << snan.d << "]" << "\t0x" << hex <<
snan.i << dec << endl;
  }
  return 0;
}

$ g++ -std=gnu++0x -Wall double.cpp
$ ./a.out
+infinity:  [inf]   0x7ff0
-infinity:  [-inf]  0xfff0
zero[0] 0x0
-zero   [-0]0x8000
quiet nan:  [nan]   0x7ff8
signaling nan:  [nan]   0x7ffc


In sqlite:

sqlite> CREATE TABLE t (pos REAL, neg REAL, nn REAL);
sqlite> INSERT into t VALUES (1, 2, 3);
sqlite> .nullvalue ThisIsANull
sqlite> insert into t values ((1e305 * 1e305), (-1e305 * 1e305), 1/0);
sqlite> insert into t values (1/(1e305 * 1e305), 1/(-1e305 * 1e305), 1/0);
spatialite> SELECT * FROM t;
1.0|2.0|3.0
Inf|-Inf|ThisIsANull
0.0|0.0|ThisIsANull

The signed zero seems lost in SQLite.

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