Re: [sqlite] Would anyone know how to use Sqlite to calculate the streakedness of data? The definition of streakedness is show below. Thank you for your help

[James K. Lowden]

On Tue, 19 Feb 2013 05:37:38 -0800
Frank Chang  wrote:

> Would anyone know how to use Sqlite to calculate the streakedness of
> data? The definition of streakedness is show below. Thank you for
> your help.
> 
> [EDIT] From our company's chief software architect, here is the
> requirement for a statistical measure. Could someone please define a
> statistical formula based onour architect's definition of data
> streakedness? -- February 19th 2013 8:45 AM

One way to go about this is to ask your architect the criteria by which
cases A-F were sorted.  IOW, what is the definition of "less than"?  It
seems to be:

0.  non-streak has length zero
1.  longest streak wins
2.  if tied for longest streak, more instances of longest rank wins
3.  if tied for #2, repeat (1,2) with next longest streak

But is that true? It seems like you might be measuring serial
correlation, for which you could conventionally use e.g.
Durbin-Watson.  

To use something like your "streakedness" (not
streakiness?) measure is very difficult without some math
functions that SQLite doesn't have.  Essentially, you want

sum( N * R^S )

where 

R is the longest possible streak
"^" is exponentiation
S is the streak size
N is the number of streaks of length S

Using this table

select name, N, streak from cases;
nameN   streak
--  --  --
A   1   13
B   1   7 
B   1   6 
C   1   7 
C   1   6 
D   1   7 
D   1   3 
D   1   2 
E   1   7 
E   1   3 
F   1   7 

in SQL Server, the query would be:

select name
, sum( N * power(biggest, streak) ) as rank 
from cases 
cross join (
select 1.0*max(streak) as biggest 
from cases
) as A 
group by name 
order by rank desc; 
name rank 
  -
 A302875106592253.0
 B   67575326.0
 C   67575326.0
 D   62750883.0
 E   62750714.0
 F   62748517.0

Without the power() function, you'd need a table of all possible values
of R^S, then

select name, sum(N * p.value) as rank
from cases 
join powers as p
on cases.streak = p.S
where p.R = (
select 1.0*max(streak) as biggest 
from cases
) 
group by name, order by rank desc;

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

Re: [sqlite] [PATCH] only build shell timer on platforms with getrusage()

[Thomas Cort]

> To make the suggested change would require that all makefiles for SQLlite
> that are not built using configure (and there are lots of these) would have
> to be revised to add -DHAVE_GETRUSAGE in order to get the .timer feature.
> That is unacceptable.

Okay. The reason for the patch is that the getrusage() stuff is
causing a compile error on Minix because Minix doesn't support
getrusage(). Instead of the HAVE_GETRUSAGE stuff, could you add a "&&
!defined(__minix)" to the ifdef that's already there in shell.c:93
which disables the timer code on platforms that don't have
getrusage().

Here's a new patch to do that: http://tomcort.com/minix/patch-minix-shell.c

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

Re: [sqlite] database is locked with create

[Choi, David]

Hi Richard,

Thank you Richard. 

I get the latest source(sqlite-amalgamation-3071502.zip) and cross-compile. And 
follow your instruction:
sqlite3 -vfs unix-none ex1

That issue is gone.

Really appreciated for your quick and exact response.

David J. Choi


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Tuesday, February 19, 2013 4:04 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked with create

On Tue, Feb 19, 2013 at 6:37 PM, Choi, David  wrote:

> Hi Richard,
>
> It seems that my version is not accept the command. By the way, what 
> is the version that you are referring to?
>

Get the latest: 3.7.15.1.  There is no reason not to.


>
> Here is my operation:
> /home # sqlite3 --help
> Usage: sqlite3 [OPTIONS] FILENAME [SQL] FILENAME is the name of an 
> SQLite database. A new database is created if the file does not 
> previously exist.
> OPTIONS include:
>-helpshow this message
>-init filename   read/process named file
>-echoprint commands before execution
>-[no]header  turn headers on or off
>-bailstop after hitting an error
>-interactive force interactive I/O
>-batch   force batch I/O
>-column  set output mode to 'column'
>-csv set output mode to 'csv'
>-htmlset output mode to HTML
>-lineset output mode to 'line'
>-listset output mode to 'list'
>-separator 'x'   set output field separator (|)
>-nullvalue 'text'set text string for NULL values
>-version show SQLite version
> /home # sqlite3 -version
> 3.6.23.1
> /home # sqlite3 -vfs unix-none ex1
> sqlite3: Error: unknown option: -vfs
> Use -help for a list of options.
>
>
> Regards,
> David J. Choi
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: Tuesday, February 19, 2013 1:44 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] database is locked with create
>
> On Tue, Feb 19, 2013 at 4:39 PM, Choi, David 
> wrote:
>
> > Hi Richard,
> >
> > I think your guess is highly possible because the error comes from 
> > sqliteErrorFromPosixError(). In that case, how can I fix the issue?
> >
>
> Try adding the -vfs unix-none command-line option:
>
>  sqlite3 -vfs unix-none ex1
>
> That will disable file locking.  Be warned, however, that with file 
> locking disabled, two processes trying to access the database at the 
> same time can lead to problems.-- D. Richard Hipp 
> drh@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
>



--
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

Re: [sqlite] database is locked with create

[Richard Hipp]

On Tue, Feb 19, 2013 at 6:37 PM, Choi, David  wrote:

> Hi Richard,
>
> It seems that my version is not accept the command. By the way, what is
> the version that you are referring to?
>

Get the latest: 3.7.15.1.  There is no reason not to.


>
> Here is my operation:
> /home # sqlite3 --help
> Usage: sqlite3 [OPTIONS] FILENAME [SQL]
> FILENAME is the name of an SQLite database. A new database is created
> if the file does not previously exist.
> OPTIONS include:
>-helpshow this message
>-init filename   read/process named file
>-echoprint commands before execution
>-[no]header  turn headers on or off
>-bailstop after hitting an error
>-interactive force interactive I/O
>-batch   force batch I/O
>-column  set output mode to 'column'
>-csv set output mode to 'csv'
>-htmlset output mode to HTML
>-lineset output mode to 'line'
>-listset output mode to 'list'
>-separator 'x'   set output field separator (|)
>-nullvalue 'text'set text string for NULL values
>-version show SQLite version
> /home # sqlite3 -version
> 3.6.23.1
> /home # sqlite3 -vfs unix-none ex1
> sqlite3: Error: unknown option: -vfs
> Use -help for a list of options.
>
>
> Regards,
> David J. Choi
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: Tuesday, February 19, 2013 1:44 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] database is locked with create
>
> On Tue, Feb 19, 2013 at 4:39 PM, Choi, David 
> wrote:
>
> > Hi Richard,
> >
> > I think your guess is highly possible because the error comes from
> > sqliteErrorFromPosixError(). In that case, how can I fix the issue?
> >
>
> Try adding the -vfs unix-none command-line option:
>
>  sqlite3 -vfs unix-none ex1
>
> That will disable file locking.  Be warned, however, that with file
> locking disabled, two processes trying to access the database at the same
> time can lead to problems.-- D. Richard Hipp 
> drh@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
>



-- 
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] SQLite error near "16": syntax error

[Pavel Ivanov]

On Tue, Feb 19, 2013 at 1:39 AM, mikkelzuuu  wrote:
> Hey guys, I'm getting this error (see title)
> I'm using C# by the way.
>
> string StrQuery = @"INSERT INTO Test VALUES (" +
> dataGridView1.Rows[i].Cells["Column1"].Value + ", " +
> dataGridView1.Rows[i].Cells["Column2"].Value + ", " +
> dataGridView1.Rows[i].Cells["Column3"].Value + ", " +
> dataGridView1.Rows[i].Cells["Column4"].Value + ", " +
> dataGridView1.Rows[i].Cells["Column5"].Value + ");";
>
> That would then be my query. I can't see a 16 anywhere in my query code.

This is a great example of sql injection (you can google "sql
injection" to understand what's that) and the reason why you shouldn't
construct your sql queries like that. :)


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

Re: [sqlite] database is locked with create

[Choi, David]

Hi Richard,

It seems that my version is not accept the command. By the way, what is the 
version that you are referring to?

Here is my operation:
/home # sqlite3 --help
Usage: sqlite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist.
OPTIONS include:
   -helpshow this message
   -init filename   read/process named file
   -echoprint commands before execution
   -[no]header  turn headers on or off
   -bailstop after hitting an error
   -interactive force interactive I/O
   -batch   force batch I/O
   -column  set output mode to 'column'
   -csv set output mode to 'csv'
   -htmlset output mode to HTML
   -lineset output mode to 'line'
   -listset output mode to 'list'
   -separator 'x'   set output field separator (|)
   -nullvalue 'text'set text string for NULL values
   -version show SQLite version
/home # sqlite3 -version
3.6.23.1
/home # sqlite3 -vfs unix-none ex1
sqlite3: Error: unknown option: -vfs
Use -help for a list of options.


Regards,
David J. Choi


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Tuesday, February 19, 2013 1:44 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked with create

On Tue, Feb 19, 2013 at 4:39 PM, Choi, David  wrote:

> Hi Richard,
>
> I think your guess is highly possible because the error comes from 
> sqliteErrorFromPosixError(). In that case, how can I fix the issue?
>

Try adding the -vfs unix-none command-line option:

 sqlite3 -vfs unix-none ex1

That will disable file locking.  Be warned, however, that with file locking 
disabled, two processes trying to access the database at the same time can lead 
to problems.-- 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

Re: [sqlite] SQLite 4

[Cory Nelson]

On Tue, Feb 19, 2013 at 3:25 AM, Gabriel Corneanu
 wrote:
> I understand, but I wanted to make a performance comparison. I read some
> good news, but I need to test it for my case.
> Am in a situation where the bottleneck is the CPU (sqlite), not IO.
> Therefore I'm very interested in an early idea about performance.
> Even if it's not ready, I could at least prepare it better for a later
> switch.

A daily amalgamation would be pretty cool. I'd love to start playing
around with LSM.

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

[sqlite] :memory: performance in sqlite 3.6

[Seebs]

I'm afraid I haven't got a nicely isolated reproducer for this.

I maintain a package, called pseudo, which includes a server built 
around an sqlite database. In the process of debugging some performance 
issues, I did some casual benchmarking. One of the first things I tried 
was an in-memory database. Now, as has been observed, this is not 
necessarily expected to be dramatically faster than an on-disk database.


What I observed was a very, very, large slowdown. Time for an overall 
task relying on the database was increased by a factor of 2-3 -- and the 
database code is not that significant a part of the runtime, usually. I 
used the sqlite3_profile() and observed that the sum of reported 
processing time from that was within a few percent of the total increase 
in execution time, which is at least suspicious.


I did a bunch of testing trying to figure out more about this (and many 
thanks to the friendly folks in #sqlite IRC who helped suggest some).


First: No, not swapping or paging. We're talking 10MB of database 
against 12GB of RAM with several GB free. The database on disk was 
running synchronous = OFF, so I wasn't necessarily expecting huge 
improvements.


In all cases, I was running against brand-new freshly created databases, 
whether in memory or on disk.


What I found:
1. This problem appears to occur with sqlite 3.6.20, or 3.6.22.
2. It does not appear to occur with sqlite 3.7, or at least not to 
nearly such a degree.

3. It is dramatically reduced in degree by pragma page_size = 8192.
4. It scales roughly with database size; at 28,000 rows, it's quite 
noticeable, and at 84,000 it's painful.
5. Times reported by sqlite3_profile callbacks were alternating 0 and 
43000 ns with a file database, and with a tiny (couple thousand item) 
in-memory database, and more like 215,000ns by the time the database got 
large.


Looking around, I found a 2003-era thing listing sqlite performance 
tips, which listed the in-memory DB as a huge and dramatic performance 
increase. My own experimentation in the past had suggested that I ought 
to see dramatic increases in performance for at least some workloads.


I did find one thing that made me at least a little suspicious even in 
3.7 (specifically 3.7.15.2). In sqlite3PagerMovepage, there's a comment 
right up at the top about journaling the page we're moving from, so 
there's a call to sqlite3PagerWrite() if MEMDB. There's no check for 
journaling mode, and it seems to me that if journaling is off, this 
shouldn't be needed.


But that's not nearly enough to explain this.

Admittedly, a performance issue which seems mostly fixed in 3.7 is 
probably a lowish priority. What concerns me is that it seems to me that 
the performance of :memory: may have taken a severe hit at some point, 
leading to a flood of internet forum posts, stackoverflow questions, and 
the like about poor performance of :memory:. Since sqlite is so fast to 
begin with, this may not have gotten noticed.


Unfortunately, I haven't got a test case I can easily use to test this. 
pseudo doesn't work with versions prior to 3.6 (except maybe it would, I 
think we imposed that check because of a specific failure on some broken 
version of 3.3.6 we encountered on some host).


The test case I was using was pseudo version 1.4.5, on Linux hosts, 
using the pseudo wrapper to untar a 28,000 file tarball. I am not sure 
how easy or hard it would be to duplicate this with a simpler test case, 
and won't have time to look more closely for a while, if ever. I'm 
passing this on in case this rings a bell for someone, and to have it in 
the archives if someone else comes looking.


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

Re: [sqlite] database is locked with create

[Richard Hipp]

On Tue, Feb 19, 2013 at 4:39 PM, Choi, David  wrote:

> Hi Richard,
>
> I think your guess is highly possible because the error comes from
> sqliteErrorFromPosixError(). In that case, how can I fix the issue?
>

Try adding the -vfs unix-none command-line option:

 sqlite3 -vfs unix-none ex1

That will disable file locking.  Be warned, however, that with file locking
disabled, two processes trying to access the database at the same time can
lead to problems.--
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] database is locked with create

[Choi, David]

Hi Richard,

I think your guess is highly possible because the error comes from 
sqliteErrorFromPosixError(). In that case, how can I fix the issue?

Regards,
David J. Choi


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Tuesday, February 19, 2013 9:44 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked with create

On Tue, Feb 19, 2013 at 12:01 PM, Choi, David  wrote:

> Hi all,
>
> I am new to sqlite. I build sqlite with buildroot without any issue. 
> After loading sqlite on my embedded board, I always get one error 
> message:"database is locked".
>
> Here is my operation on  my board:
>
> #sqlite3 ex1
> SQLite version 3.6.23.1
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table tbl1(one varchar(10), two smallint);
> Error: database is locked
> sqlite >
>
> Any suggestion will be highly appreciated.
>

I'm guessing that whatever embedded operating system you are using does not
(correctly) support posix advisory locks.   But that's just a guess.

--
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

Re: [sqlite] database is locked with create

[Choi, David]

Hi Simon,

Thank you for your clear instruction. But still I have the same issue.


Here is my test procedures:

#cd /home
#sqlite3 ex1
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>create table tbl1(one varchar(10),two smallint);
sqlite3_exec: PH3: rc=101sqlite3_exec: PH7:rc=101sqlite3_exec: 
PH8:rc=0sqlite3_exec: PH9sqlite3_exec: PH10:rc=0sqliteErrorFromPosixError: 
SQLITE_BUSY: sqliteIOErr=3850
Error: database is locked
sqlite> .exit
/home # ls -l
total 4
drwxr-xr-x2 default  default  0 Feb 19 11:08 default
-rw-r--r--1 root root 0 Feb 19 13:26 ex1
/home # rm ex1
/home # sqlite3 ex1
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table tbl1(one varchar(10), two smallint);
sqlite3_exec: PH3: rc=101sqlite3_exec: PH7:rc=101sqlite3_exec: 
PH8:rc=0sqlite3_exec: PH9sqlite3_exec: PH10:rc=0sqliteErrorFromPosixError: 
SQLITE_BUSY: sqliteIOErr=3850
Error: database is locked
sqlite> .exit
/home # ls
default  ex1
/home # ls -l ex1
-rw-r--r--1 root root 0 Feb 19 13:27 ex1
/home # rm ex1 
/home # touch aaa
/home # ls
aaa  default  ex1
/home # echo "this is test file" > aaa
/home # cat aaa
this is test file

Regards,
David J. Choi


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Tuesday, February 19, 2013 9:14 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked with create


On 19 Feb 2013, at 5:01pm, "Choi, David"  wrote:

> I am new to sqlite. I build sqlite with buildroot without any issue. After 
> loading sqlite on my embedded board, I always get one error message:"database 
> is locked".
> 
> Here is my operation on  my board:
> 
> #sqlite3 ex1
> SQLite version 3.6.23.1
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table tbl1(one varchar(10), two smallint);
> Error: database is locked
> sqlite >
> 
> Any suggestion will be highly appreciated.

Quit the shell program.
Locate the database file called 'ex1' and make sure it is in the folder you 
expect it to be in.
If the database file still exists, delete it.
Try again, letting the shell program create a new database.

If this also doesn't work,

Check the privileges in the folder where the database file is created.
Do you have write privileges inside that folder ?
Try using copy command or a simple text editor to make a file in that folder.
Does it work or do you get a similar error to 'file is locked' ?

Simon.
___
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] query optimization with "order by" in a view

[Konrad Hambrick]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf
> Of James K. Lowden
> Sent: Tuesday, February 19, 2013 12:07 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] query optimization with "order by" in a view
> 
> On Tue, 19 Feb 2013 10:19:26 +0100
> "Gabriel Corneanu"  wrote:
> 
> > I included the "order by" in view because it's meant for some
> > end-users and I wanted to avoid mistakes.
> ...
> > Am I doing a mistake??
> 
> Well, yes, by including ORDER BY in the view definition.  Most DBMSs
> don't allow that, and the SQL standard doesn't allow it.  So don't do
> it!  :-)
> 
> ORDER BY is best understood as *not* part of the SELECT statement.
> Rather, it's a post-processor.  Consider that there can be many SELECTs
> in a query, but only one ORDER BY.
> 
> According to the SQL standard, SELECT produces a "table expression" that
> can be used wherever a table can be used.  ORDER BY *reads* a table
> expression; what it returns is technically a "cursor".
> 
> It's tempting to think, OK, but the view's ORDER BY would be processed
> first and the final ORDER BY would be processed last, so the order is
> predictable.  In fact, though, there is no first and last.  The SQL
> statement is a declaration, not an imperative.  It describes which rows
> and columns to retrieve.  It specifies only an outcome, not an
> algorithm or an order of operation.
> 
> In effect, your query specified
> 
>   ORDER BY id
>   AND
>   ORDER BY data
> 
> which you would never do, and SQLite can't, either.  ;-)
> 
> HTH.
> 
> --jkl


Very nice explanation, James !

Thank you.

-- kjh( I had to look twice to make sure I was in my SQLite Mailbox and not in 
FreeTDS :)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to use SQLITE to calculate the streakedness of numeric data?

[Frank Chang]

 joe.fis...@tanguaylab.com, Michael Black, Marc L. Allen, and Simon
Slavin, Our software architect defined data streakedness based upon
Chauvenet's criterion. Thank you for all of your help.

In statistical theory, *Chauvenet's criterion* (named for William
Chauvenet
[1] ) is
a means of assessing whether one piece of experimental data — an
outlier— from a set of
observations, is likely to be spurious.

To apply Chauvenet's criterion, first calculate the
meanand standard
deviation  of the observed
data. Based on how much the suspect datum differs from the mean, use the normal
distribution  function
(or a table thereof) to determine the
probabilitythat a given data
point will be at the value of the suspect data point.
Multiply this probability by the number of data points taken. If the result
is less than 0.5, the suspicious data point may be discarded, i.e., a
reading may be rejected if the probability of obtaining the particular
deviation from the mean is less than 1/(2*n*).


On Tue, Feb 19, 2013 at 11:05 AM, Frank Chang wrote:

>joe.fis...@tanguaylab.com, Michael Black, Marc. L Allen and Simon
> Slavin, Thank you for your help in helping me to convince our company's
> software architect that it is possible to calculate the streakedness of
> numeric data.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to use SQLITE to calculate the streakedness of numeric data?

[Frank Chang]

   joe.fis...@tanguaylab.com, Michael Black, Marc. L Allen and Simon
Slavin, Thank you for your help in helping me to convince our company's
software architect that it is possible to calculate the streakedness of
numeric data.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query Plan with FTS Tables

[GB]

Hi all,

we are facing some Problems with Queries joining several FTS-Tables. 
There seems to be no equivalent to the ANALYZE Command for FTS-Indexes 
so they do not contain statistical info for the Query Planner. This 
sometimes leads to execution Plans with poor Performance. Some of them 
run 20 times slower than their hand-optimized counterpart.


Is there any means to obtain statistical info from an FTS Index so we 
can do some reordering by hand? Or some way to provide hints to the 
Query Planner like ANALYZE does for regular Table Indexes?


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

Re: [sqlite] query optimization with "order by" in a view

[James K. Lowden]

On Tue, 19 Feb 2013 10:19:26 +0100
"Gabriel Corneanu"  wrote:

> I included the "order by" in view because it's meant for some
> end-users and I wanted to avoid mistakes.
...
> Am I doing a mistake??

Well, yes, by including ORDER BY in the view definition.  Most DBMSs
don't allow that, and the SQL standard doesn't allow it.  So don't do
it!  :-)  

ORDER BY is best understood as *not* part of the SELECT statement.
Rather, it's a post-processor.  Consider that there can be many SELECTs
in a query, but only one ORDER BY.  

According to the SQL standard, SELECT produces a "table expression" that
can be used wherever a table can be used.  ORDER BY *reads* a table
expression; what it returns is technically a "cursor".  

It's tempting to think, OK, but the view's ORDER BY would be processed
first and the final ORDER BY would be processed last, so the order is
predictable.  In fact, though, there is no first and last.  The SQL
statement is a declaration, not an imperative.  It describes which rows
and columns to retrieve.  It specifies only an outcome, not an
algorithm or an order of operation.  

In effect, your query specified

ORDER BY id
AND
ORDER BY data

which you would never do, and SQLite can't, either.  ;-)

HTH.

--jkl


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

Re: [sqlite] database is locked with create

[Richard Hipp]

On Tue, Feb 19, 2013 at 12:01 PM, Choi, David  wrote:

> Hi all,
>
> I am new to sqlite. I build sqlite with buildroot without any issue. After
> loading sqlite on my embedded board, I always get one error
> message:"database is locked".
>
> Here is my operation on  my board:
>
> #sqlite3 ex1
> SQLite version 3.6.23.1
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table tbl1(one varchar(10), two smallint);
> Error: database is locked
> sqlite >
>
> Any suggestion will be highly appreciated.
>

I'm guessing that whatever embedded operating system you are using does not
(correctly) support posix advisory locks.   But that's just a guess.

-- 
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] database is locked with create

[Simon Slavin]

On 19 Feb 2013, at 5:01pm, "Choi, David"  wrote:

> I am new to sqlite. I build sqlite with buildroot without any issue. After 
> loading sqlite on my embedded board, I always get one error message:"database 
> is locked".
> 
> Here is my operation on  my board:
> 
> #sqlite3 ex1
> SQLite version 3.6.23.1
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table tbl1(one varchar(10), two smallint);
> Error: database is locked
> sqlite >
> 
> Any suggestion will be highly appreciated.

Quit the shell program.
Locate the database file called 'ex1' and make sure it is in the folder you 
expect it to be in.
If the database file still exists, delete it.
Try again, letting the shell program create a new database.

If this also doesn't work,

Check the privileges in the folder where the database file is created.
Do you have write privileges inside that folder ?
Try using copy command or a simple text editor to make a file in that folder.
Does it work or do you get a similar error to 'file is locked' ?

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

Re: [sqlite] Required sqlite binaries for DOS urgently

[Mahesh Chavan]

Dear  Sir,
I am in computer field since 1988 and I have started my career with pc with
two 360k floppy drivers.
I have some accounting package I have written using sqlite as a back end
and even though my pc is not accepting hard disk I have to run this
software any how.
I am on Windows platform and do not have in deth knowledge about Linux.
Regarding remving reference og mmap and munmap one gentlmen
fromcomp.os.msdos.djgpp claimed, he has compiled sqlite bu removing these
references and that he is using the library without any problem.  I have
got othersuch replies from stackoverflow too. I have used malloc function
instead and I shall be replacing it with memory allocation routines for
DPMI.
In my about 25 years carrer in software I have developed many commercial
applications which fit on a single floppy. Although I m used to Microsoft
Vista and bulky software, I still love the immediate startup though floppy.
I certainly lack knowledge of linux architecture although I am using
puppylinux for some years now.
I shall be thankful to you, if you kindly guide me in compiling sqlite3 in
proper way.
Thanking you.
-Mahesh Chavan
On Tue, Feb 19, 2013 at 10:52 PM, Ryan Johnson
wrote:

> On 19/02/2013 8:08 AM, Mahesh Chavan wrote:
>
>> My IDE bus is refusing to recognise my HDD or CDROM, I am compelled to
>> access my PC using floppy only.
>> I intend to run all my database software on floppy using sqlite3.
>> I need to develop the software on warfooting. I have already compiled some
>> programs in Puppy Linux previously.
>>
> It's been about 25 years since I last saw a DOS PC with no hard drive that
> ran everything from floppy
>
> Meta question: Maybe I'm missing something here, but why are you trying to
> do *anything* with the PC you describe, let alone run sqlite3? Even if it
> had an HDD with data worth recovering, you'd have to move it to a machine
> with working IDE bus to recover it.
>
>  When I tried to compile sqlite3 on DOS using Djgpp, the libsqlite3.a file
>> is produced when I removed references to mmap and munmap from original
>> source anf added stub for fchown.
>> But when I tried to produce sqlite3.exe from shell.o, it gave me error* ld
>>
>> can not find lgcc and lc. However, libgcc.a anf libc.a are both there in
>> c:\djgpp\lib directory.*
>>
>> The same problem arises when  I try to compile any program using
>> libsqlite3.a.
>> Please guide me how to gt binaries on DOS using  DJGPP
>>
> It sounds like you lack basic knowledge of how compilers, linkers, and
> operating systems work. If so, you'd probably be better off asking for help
> on a list that deals in those sorts of issues. In particular, removing
> calls to mmap/munmap from program sources might let you compile and link
> the code, but it is highly unlikely to produce a working executable
> (especially not on DOS, when the code assumes Linux conventions).
>
> Ryan
>
> __**_
> 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] database is locked with create

[Choi, David]

Hi all,

I am new to sqlite. I build sqlite with buildroot without any issue. After 
loading sqlite on my embedded board, I always get one error message:"database 
is locked".

Here is my operation on  my board:

#sqlite3 ex1
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table tbl1(one varchar(10), two smallint);
Error: database is locked
sqlite >

Any suggestion will be highly appreciated.

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

Re: [sqlite] type definitions or aliases

[Patrik Nilsson]

On 02/19/2013 05:00 PM, Stephen Chrzanowski wrote:
> My best suggestion would be to modify the code so when you compile the
> string to make the select/delete/update, it uses the table name.  For
> testing purposes, you could have a combo box, or text field, or a text file
> that has the contents to point to the table you need.

I hoped not to rewrite most of the statements...:( Too many to debug!

> Either that, copy your subject table to a memory table with the backup API,
> and run against that.  In one of my applications, I offer the user the
> choice to run the "database" from memory or from the drive directly.  When
> the application is run, it first reads the HDD file first, drops it to
> memory, works off memory, and when the application closes, it drops the
> data back to the drive.

Generally no. I have databases bigger than my RAM. And when I go public
with my application... I can't.

> (I'm sick - if that last sentence doesn't make sense, blame my kids and the
> meds! ;) )

(Everything you wrote made sense...)

It seems to me that the easiest way is to create a table "version" and
put some information into it.

> On Tue, Feb 19, 2013 at 10:37 AM, Patrik Nilsson
> wrote:
> 
>> In my case I read the restriction of the triggers: I use database.table
>> and I insert with default values.
>>
>> I can't use triggers for this.
>>
>> /Patrik
>>
>> On 02/19/2013 04:27 PM, Dave McKee wrote:
 Is it possible to use triggers for this?
>>>
>>> Yes.
>>>
>>> "Triggers may be created on views, as well as ordinary tables, by
>>> specifying INSTEAD OF in the CREATE TRIGGER statement. If one or more ON
>>> INSERT, ON DELETE or ON UPDATE triggers are defined on a view, then it is
>>> not an error to execute an INSERT, DELETE or UPDATE statement on the
>> view,
>>> respectively. Instead, executing an INSERT, DELETE or UPDATE on the view
>>> causes the associated triggers to fire. The real tables underlying the
>> view
>>> are not modified (except possibly explicitly, by a trigger program)."
>>>
>>> http://www.sqlite.org/lang_createtrigger.html
>>> ___
>>> 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] type definitions or aliases

[Stephen Chrzanowski]

My best suggestion would be to modify the code so when you compile the
string to make the select/delete/update, it uses the table name.  For
testing purposes, you could have a combo box, or text field, or a text file
that has the contents to point to the table you need.

Either that, copy your subject table to a memory table with the backup API,
and run against that.  In one of my applications, I offer the user the
choice to run the "database" from memory or from the drive directly.  When
the application is run, it first reads the HDD file first, drops it to
memory, works off memory, and when the application closes, it drops the
data back to the drive.

(I'm sick - if that last sentence doesn't make sense, blame my kids and the
meds! ;) )

On Tue, Feb 19, 2013 at 10:37 AM, Patrik Nilsson
wrote:

> In my case I read the restriction of the triggers: I use database.table
> and I insert with default values.
>
> I can't use triggers for this.
>
> /Patrik
>
> On 02/19/2013 04:27 PM, Dave McKee wrote:
> >> Is it possible to use triggers for this?
> >
> > Yes.
> >
> > "Triggers may be created on views, as well as ordinary tables, by
> > specifying INSTEAD OF in the CREATE TRIGGER statement. If one or more ON
> > INSERT, ON DELETE or ON UPDATE triggers are defined on a view, then it is
> > not an error to execute an INSERT, DELETE or UPDATE statement on the
> view,
> > respectively. Instead, executing an INSERT, DELETE or UPDATE on the view
> > causes the associated triggers to fire. The real tables underlying the
> view
> > are not modified (except possibly explicitly, by a trigger program)."
> >
> > http://www.sqlite.org/lang_createtrigger.html
> > ___
> > 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] type definitions or aliases

[Patrik Nilsson]

In my case I read the restriction of the triggers: I use database.table
and I insert with default values.

I can't use triggers for this.

/Patrik

On 02/19/2013 04:27 PM, Dave McKee wrote:
>> Is it possible to use triggers for this?
> 
> Yes.
> 
> "Triggers may be created on views, as well as ordinary tables, by
> specifying INSTEAD OF in the CREATE TRIGGER statement. If one or more ON
> INSERT, ON DELETE or ON UPDATE triggers are defined on a view, then it is
> not an error to execute an INSERT, DELETE or UPDATE statement on the view,
> respectively. Instead, executing an INSERT, DELETE or UPDATE on the view
> causes the associated triggers to fire. The real tables underlying the view
> are not modified (except possibly explicitly, by a trigger program)."
> 
> http://www.sqlite.org/lang_createtrigger.html
> ___
> 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] Using VIEW as a foreign key?

[Simon Slavin]

On 19 Feb 2013, at 3:01pm, Thomas Knox  wrote:

> Is it possible to use a field in a VIEW as the foreign key for another
> table?

No.  If this truly is the database structure that's useful to you I would 
suggest that either (A) you enforce your foreign keys in your software and not 
have SQLite do it or (B) you create and maintain another TABLE which exists 
purely to act as the foreign key index.

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

Re: [sqlite] Would anyone know how to use Sqlite to calculate the streakedness of data? The definition of streakedness is show below. Thank you for your help

[Simon Slavin]

On 19 Feb 2013, at 1:37pm, Frank Chang  wrote:

> Would anyone know how to use Sqlite to calculate the streakedness of data?

Yes, technically it might be possible to do this as a collection of SQL 
statements and table updates, but it would be extremely inefficient.

SQLite is a database management system.  To do a calculation, either do it in 
your programming language or implement a SQLite extension which does it.  At 
some point you're actually going to have to learn how to write software.

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

Re: [sqlite] type definitions or aliases

[Dave McKee]

> Is it possible to use triggers for this?

Yes.

"Triggers may be created on views, as well as ordinary tables, by
specifying INSTEAD OF in the CREATE TRIGGER statement. If one or more ON
INSERT, ON DELETE or ON UPDATE triggers are defined on a view, then it is
not an error to execute an INSERT, DELETE or UPDATE statement on the view,
respectively. Instead, executing an INSERT, DELETE or UPDATE on the view
causes the associated triggers to fire. The real tables underlying the view
are not modified (except possibly explicitly, by a trigger program)."

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

Re: [sqlite] type definitions or aliases

[Patrik Nilsson]

Quite good, but I read that "You cannot DELETE, INSERT, or UPDATE a
view". I need to do that!

Is it possible to use triggers for this?

Patrik

On 02/19/2013 04:06 PM, Stephan Beal wrote:
> On Tue, Feb 19, 2013 at 4:04 PM, Patrik Nilsson
> wrote:
> 
>> (some alias command: abc is an alias of abc_v1 for this connection)
>>
>> select * from abc
>>
>> (The last command would then "select * from abc_v1".)
>>
> 
> How about:
> 
> CREATE TEMPORARY VIEW abc AS SELECT * FROM abc_v1;
> 
> e.g.
> 
> sqlite> create temporary view x as select * from sqlite_master;
> sqlite> select * from x;
> sqlite> create table t(a,b,c);
> sqlite> select * from x;
> table|t|t|2|CREATE TABLE t(a,b,c)
> 
> 
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] type definitions or aliases

[Stephan Beal]

On Tue, Feb 19, 2013 at 4:04 PM, Patrik Nilsson
wrote:

> (some alias command: abc is an alias of abc_v1 for this connection)
>
> select * from abc
>
> (The last command would then "select * from abc_v1".)
>

How about:

CREATE TEMPORARY VIEW abc AS SELECT * FROM abc_v1;

e.g.

sqlite> create temporary view x as select * from sqlite_master;
sqlite> select * from x;
sqlite> create table t(a,b,c);
sqlite> select * from x;
table|t|t|2|CREATE TABLE t(a,b,c)



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

[sqlite] type definitions or aliases

[Patrik Nilsson]

Hi,

I change database format frequently and would like to have an easy way
to distinguish the tables.

Is it possible to have a pointer (like typedef in C) that points to the
current used table? As an temporary pointer?

Example:

I have a table named abc, which I version as abc_v1, abc_v2, ... Can the
table abc automatically point to a table of my choise?

create table abc_v1(a integer,b integer)

(some alias command: abc is an alias of abc_v1 for this connection)

select * from abc

(The last command would then "select * from abc_v1".)

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

[sqlite] Using VIEW as a foreign key?

[Thomas Knox]

Hello,

Is it possible to use a field in a VIEW as the foreign key for another
table? Something like:

CREATE TABLE "stuff" ("id" INTEGER PRIMARY KEY NOT NULL, "desc" TEXT);
CREATE TABLE "morestuff" ("id" INTEGER PRIMARY KEY NOT NULL, "desc" TEXT);
CREATE VIEW "allstuff" AS SELECT id, desc FROM stuff UNION SELECT id, desc
FROM morestuff;
CREATE TABLE "thistable" ("myid" INTEGER PRIMARY KEY AUTOINCREMENT NOT
NULL, "id" INTEGER, "desc" TEXT, FOREIGN KEY(id) REFERENCES allstuff(id));

If I try to do something like that, when I go to INSERT into "thistable" I
get a foreign key violation, even though doing a SELECT against "allstuff"
shows the row with the "id" value that matches the INSERT into "thistable".

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

Re: [sqlite] Required sqlite binaries for DOS urgently

[Ryan Johnson]

On 19/02/2013 8:08 AM, Mahesh Chavan wrote:

My IDE bus is refusing to recognise my HDD or CDROM, I am compelled to
access my PC using floppy only.
I intend to run all my database software on floppy using sqlite3.
I need to develop the software on warfooting. I have already compiled some
programs in Puppy Linux previously.
It's been about 25 years since I last saw a DOS PC with no hard drive 
that ran everything from floppy


Meta question: Maybe I'm missing something here, but why are you trying 
to do *anything* with the PC you describe, let alone run sqlite3? Even 
if it had an HDD with data worth recovering, you'd have to move it to a 
machine with working IDE bus to recover it.



When I tried to compile sqlite3 on DOS using Djgpp, the libsqlite3.a file
is produced when I removed references to mmap and munmap from original
source anf added stub for fchown.
But when I tried to produce sqlite3.exe from shell.o, it gave me error* ld
can not find lgcc and lc. However, libgcc.a anf libc.a are both there in
c:\djgpp\lib directory.*
The same problem arises when  I try to compile any program using
libsqlite3.a.
Please guide me how to gt binaries on DOS using  DJGPP
It sounds like you lack basic knowledge of how compilers, linkers, and 
operating systems work. If so, you'd probably be better off asking for 
help on a list that deals in those sorts of issues. In particular, 
removing calls to mmap/munmap from program sources might let you compile 
and link the code, but it is highly unlikely to produce a working 
executable (especially not on DOS, when the code assumes Linux 
conventions).


Ryan

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

Re: [sqlite] [PATCH] only build shell timer on platforms with getrusage()

[Richard Hipp]

On Thu, Feb 14, 2013 at 6:49 AM, Thomas Cort  wrote:

> Hello,
>
> The timer code in src/shell.c relies heavily on getrusage(). Some
> platforms, Minix for example, don't have getrusage() yet, but it may
> get it in the future. The attached patch "patch-configure.ac" adds a
> check for the getrusage function. The other attached patch
> "patch-src_shell.c" disables the timer code (beginTimer, endTimer,
> etc) on platforms that don't have HAVE_GETRUSAGE defined.
>

To make the suggested change would require that all makefiles for SQLlite
that are not built using configure (and there are lots of these) would have
to be revised to add -DHAVE_GETRUSAGE in order to get the .timer feature.
That is unacceptable.

If you can suggest a patch that tests to see if getrusage() is missing and
sets a macro only if it is missing, then we will consider such a patch.

-- 
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] Would anyone know how to use Sqlite to calculate the streakedness of data? The definition of streakedness is show below. Thank you for your help

[Marc L. Allen]

I haven't done SQLite coding in several months, and it's quite rusty, so I'll 
paraphrase.  I haven't tested and if this is bogus, I'm sorry in advance.  But 
maybe it'll give someone the right idea.

You might be better off with a custom function, though.

It would be something like this:

CREATE TABLE D
(
Pos INTEGER PRIMARY KEY AUTOINCREMENT,
Value INTEGER,
Streak INTEGER
)

(insert data into table D, with Streak set to 0.)
Insert invalid data point at the end (e.g. -1)

Update Streak such that it's equal to 1 if it is higher or equal to the value 
at POS - 1.
(This should result in the first POS, streak staying 0 and the final invalid 
point also having streak = 0)

UPDATE D SET Streak = 1
WHERE Pos IN (SELECT d2.POS FROM D d1 INNER JOIN D d2 WHERE d1.POS = d2.POS - 1 
AND d2.value >= d1.value)

Now, D should contain zero's for the beginning positions for each streak.  
Simply examine the distance between consecutive zeros. 

CREATE TABLE Streak
(
First INTEGER,
Last INTEGER,
Length INTEGER
)

INSERT INTO Streak
SELECT d1.pos, d2.pos, d2.pos - d1.pos 
FROM D d1
INNER JOIN D d2 ON d2.pos > d1.pos
WHERE d1.value = 0 and d2.value = 0 and 
not exists (SELECT d3.pos FROM D d3 where d3.value = 0 and 
d3.pos > d1.pos and d3.pos < d2.pos)

That should give you a list of streaks, including streaks of 1.  Aggregate any 
way you want.  

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Frank Chang
Sent: Tuesday, February 19, 2013 8:38 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Would anyone know how to use Sqlite to calculate the 
streakedness of data? The definition of streakedness is show below. Thank you 
for your help

Would anyone know how to use Sqlite to calculate the streakedness of data?
The definition of streakedness is show below. Thank you for your help.

[EDIT] From our company's chief software architect, here is the requirement for 
a statistical measure. Could someone please define a statistical formula based 
onour architect's definition of data streakedness? -- February 19th 2013 8:45 AM

Equal numbers are a streak. 1,2,3,3,3,4,5 has a streak of 7.

Case A: 1,2,3,4,5,6,7,8,9,10,11,12,13 has a longest streak of 13.

Case B: 1,2,3,4,5,6,7,3,8,9,10,11,12 has a longest streak of 7, a second 
smaller streak of 6.

Case C: 1,2,3,4,5,6,7,1,2,3,4,5,6 has a longest streak of 7, and a second 
smaller streak of 6.

Case D: 1,2,3,4,5,6,7,1,2,3,1,2,1 has a longest streak of 7, a second smaller 
streak of 3, and a third smallest streak of 2

Case E: 1,2,3,4,5,6,7,6,5,4,1,2,3 has a longest streak of 7, and a second 
smaller streak of 3.

Case F: 1,2,3,4,5,6,7,6,5,4,3,2,1 has a longest streak of 7, and no smaller 
streaks.

The cases A - F are ordered in 'most sorted to least sorted', but all have the 
same length longest streak. Using the averages of streak length is not
appropriate:

A: Average = 13/1 = 13

B: Average = (7+6)/2 = 6.5

C: Average = (7+6)/2 = 6.5

D: Average = (7+3+2)/3 = 4

E: Average = (7+3)/2 = 5

F: Average = 7/1 = 7

Factoring in non-streaks (counting them as 1's):

A: Average = 13/1 = 13

B: Average = (7+6)/3 = 4.3

C: Average = (7+6)/2 = 6.5

D: Average = (7+3+2+1)/4 = 3.25

E: Average = (7+1+1+1+3)/5 = 2.6

F: Average = (7+1+1+1+1+1+1)/7 = 1.85
___
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] Would it be possible to use SQLIte to calculate Chauvents Criterion as a proxy for data streakedness?

[Frank Chang]

  Good morning, Would it be possible to use SQLIte to calculate Chauvents
Criterion as a proxy for data streakedness? Thank you.

http://math.stackexchange.com/questions/198105/chauvenets-criterion-all-my-data-points-are-outliers?rq=1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization with "order by" in a view

[Richard Hipp]

On Tue, Feb 19, 2013 at 8:30 AM, Gabriel Corneanu  wrote:

> I hoped it was either a slip or would be relatively simple to implement.
>

Good rule of thumb:  Nothing is ever simple in a query optimizer


-- 
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] query optimization with "order by" in a view

[Gabriel Corneanu]

It's hard to accept this conclusion... it seems like a simple justification.
If you say so, why is "select from v order by id" not doing a sort (with 
the data from view)?

Obviously it "sees" the id is the primary key and uses it for sorting.

I read here lots of messages about complex query optimizations, and 
there is a whole chapter about this here:

http://www.sqlite.org/optoverview.html#flattening

I will obviously handle this somehow, but I hoped it was either a slip 
or would be relatively simple to implement.
I have the feeling (at least for this case) that a simple rule (for the 
optimizer) is, only the last sort should be honored.
That means, ignore the sort from the view; and because this case seems 
to be correctly handled


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

[sqlite] Required sqlite binaries for DOS urgently

[Mahesh Chavan]

My IDE bus is refusing to recognise my HDD or CDROM, I am compelled to
access my PC using floppy only.
I intend to run all my database software on floppy using sqlite3.
I need to develop the software on warfooting. I have already compiled some
programs in Puppy Linux previously.
When I tried to compile sqlite3 on DOS using Djgpp, the libsqlite3.a file
is produced when I removed references to mmap and munmap from original
source anf added stub for fchown.
But when I tried to produce sqlite3.exe from shell.o, it gave me error* ld
can not find lgcc and lc. However, libgcc.a anf libc.a are both there in
c:\djgpp\lib directory.*
The same problem arises when  I try to compile any program using
libsqlite3.a.
Please guide me how to gt binaries on DOS using  DJGPP
*I got sqlitex.zip from iblilio, containing sqlite3.exe but it is corrupt
and gives disk i/o error*
But I shall be thankful to you, if somebody can send compiled binaries of
sqlite3 for DOS
Thanking you.
*-Mahesh Chavan*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite error near "16": syntax error

[Clemens Ladisch]

mikkelzuuu wrote:
> string StrQuery = @"INSERT INTO Test VALUES (" +
> dataGridView1.Rows[i].Cells["Column1"].Value + ", " +
> dataGridView1.Rows[i].Cells["Column2"].Value + ", " +
> dataGridView1.Rows[i].Cells["Column3"].Value + ", " +
> dataGridView1.Rows[i].Cells["Column4"].Value + ", " +
> dataGridView1.Rows[i].Cells["Column5"].Value + ");";
>
> That would then be my query. I can't see a 16 anywhere in my query code.

Then it is part of one of the cell values.

Show the value of StrQuery.


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

[sqlite] SQLite error near "16": syntax error

[mikkelzuuu]

Hey guys, I'm getting this error (see title)
I'm using C# by the way.

string StrQuery = @"INSERT INTO Test VALUES (" +
dataGridView1.Rows[i].Cells["Column1"].Value + ", " +
dataGridView1.Rows[i].Cells["Column2"].Value + ", " +
dataGridView1.Rows[i].Cells["Column3"].Value + ", " +
dataGridView1.Rows[i].Cells["Column4"].Value + ", " +
dataGridView1.Rows[i].Cells["Column5"].Value + ");";

That would then be my query. I can't see a 16 anywhere in my query code. No
clue whats going on. I've been playing around with this now for about a
week. I'm like 90% finished with the program lol.

If anyway can help would be awesome.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/SQLite-error-near-16-syntax-error-tp67086.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization with "order by" in a view

[Simon Slavin]

On 19 Feb 2013, at 9:19am, "Gabriel Corneanu"  wrote:

> As a summary, it seems that having multiple "order by" disturbs the query 
> builder; of course, I expected the "optimizer" to recognize that i was the 
> same order and avoid extra sorting.
> Am I doing a mistake??

I think you have figured it out correctly.  SQLite prepares the data you asked 
for in two stages.  First it executes the SELECT which you defined to make the 
VIEW.  Then it executes a SELECT on the results.  It does not merge the two 
SELECTs into one command, so it never notices that the result of the first 
SELECT already has the rows in the right order.

I think you laid out the three possible approaches to ORDER BY nicely and can 
pick whichever one suits you best: either depend on the VIEW always returning 
rows in the right order or don't.

However, in SELECT from a TABLE (rather than from a VIEW) you can never depend 
on the order of the returned rows.  So perhaps it's better not to have your 
VIEW do sorting.  That way your VIEW has the same behaviour as SQL users would 
expect from a TABLE.

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

Re: [sqlite] SQLite 4

[Gabriel Corneanu]

I understand, but I wanted to make a performance comparison. I read some  
good news, but I need to test it for my case.
Am in a situation where the bottleneck is the CPU (sqlite), not IO.  
Therefore I'm very interested in an early idea about performance.
Even if it's not ready, I could at least prepare it better for a later  
switch.


Gabriel

--
Using Opera's revolutionary email client: http://www.opera.com/mail/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query optimization with "order by" in a view

[Gabriel Corneanu]

I need some clarifications on this issue. Here is a simplified example.
There is a table:
CREATE TABLE t(id integer primary key, data integer);
and a (simplified) view:
CREATE VIEW v as SELECT * FROM "t" order by id;

I included the "order by" in view because it's meant for some end-users  
and I wanted to avoid mistakes.
BUT I queried the view myself; I wanted to also be "safe" and included  
another "order by":


explain query plan SELECT * FROM "v" order by id;

As one can see, the plan uses 2 scans and an extra sort (btree)!! Which  
can be very expensive, of course...

I could find the following:
- if I query the view w/o "order by", it works as expected (uses primary  
key)
- if I define the view w/o "order by", and use "order by" in query, it  
also works as expected
- if I use the query with "order by rowid" (instead of id), the query plan  
is a little different; it has 2 scans, but it doesn't use a temporary  
btree anymore


As a summary, it seems that having multiple "order by" disturbs the query  
builder; of course, I expected the "optimizer" to recognize that i was the  
same order and avoid extra sorting.

Am I doing a mistake??

Thanks,
Gabriel


--
Using Opera's revolutionary email client: http://www.opera.com/mail/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] creating a summary table

[Paul Sanderson]

That did the job - Thank You


On 18 February 2013 18:15, James K. Lowden  wrote:

> On Mon, 18 Feb 2013 17:02:53 +
> Paul Sanderson  wrote:
>
> > nc
> > 1a
> > 2a
> > 3a
> > 4b
> > 5b
> > 3b
> > 4b
> > 2b
> > 3a
> > 5b
> > 2b
> >
> >
> > I have a table as above
> >
> > I want to create a summary table that shows in the first column the
> > total number of occurrences of a value in the first column (n) and in
> > the second column for each value in n a count of the unique entries
> > in c
>
> Is this what you have in mind?
>
> sqlite> select n, count(*) as occurence, count(distinct c) as uniq
> from t group by n;
> n   occurence   uniq
> --  --  --
> 1   1   1
> 2   3   2
> 3   3   2
> 4   2   1
> 5   2   1
>
> --jkl
> ___
> 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] SQLite 4

[Dan Kennedy]

On 02/19/2013 03:50 PM, Gabriel Corneanu wrote:

I am also on final steps of a new project, and I would love to compare
sqlite4 before release (the data files will be public, therefore a later
switch would be problematic).
Is there any chance to get it for windows? I usually need the dll.
Last time I could not compile it (mingw), there are some memory mapping
operations only for unix/linux (lsm_unix).
They have equivalents for windows, so it should be possible to port it.



Realistically speaking it's not ready to be used for that sort of
thing. There are probably even (minor) file format changes still to
come.

Dan.


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

Re: [sqlite] SQLite 4

[Gabriel Corneanu]

I am also on final steps of a new project, and I would love to compare  
sqlite4 before release (the data files will be public, therefore a later  
switch would be problematic).

Is there any chance to get it for windows? I usually need the dll.
Last time I could not compile it (mingw), there are some memory mapping  
operations only for unix/linux (lsm_unix).

They have equivalents for windows, so it should be possible to port it.

Thanks,
Gabriel

--
Using Opera's revolutionary email client: http://www.opera.com/mail/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users