Re: [sqlite] Cross-compiled sqlite3 tool runs into segmentation fault when creating a table

2011-12-12 Thread Richard Hipp 
On Mon, Dec 12, 2011 at 11:34 PM, imin imup  wrote:

>  Hello,
>
> I'm cross-compiling SQLite 3.6.12 onto Fedora 14 on MIPS cpu. The command
> line tool sqlite3 runs into segmentation fault when I tried to create a
> table:
>
>
> # sqlite3_mipsel test.db
> SQLite version 3.6.12
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table tbl1(one varchar(10), two smallint);
> Segmentation fault
>
>
> Any idea how to fix this?
>

Compile the following patch instead:
http://www.sqlite.org/src/ci/54cc119811?sbs=0


> It runs well on Fedora14 on i386 CPU.
>
>
> Thanks
> Imin
> ___
> 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] Cross-compiled sqlite3 tool runs into segmentation fault when creating a table

2011-12-12 Thread imin imup 
 Hello,

I'm cross-compiling SQLite 3.6.12 onto Fedora 14 on MIPS cpu. The command
line tool sqlite3 runs into segmentation fault when I tried to create a
table:


# sqlite3_mipsel test.db
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table tbl1(one varchar(10), two smallint);
Segmentation fault


Any idea how to fix this?
It runs well on Fedora14 on i386 CPU.


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

Re: [sqlite] Bash Scripting

2011-12-12 Thread Black, Michael (IS) 
You need to surround your entire sqlite3 command with back tics.



TomaCampo=`sqlite3 /Users/.and RecordTy8pe='R';"`



That's how you execute a command in bourne shell.





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 Alberto De La Torre [albe...@hidromar.es]
Sent: Monday, December 12, 2011 4:10 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Bash Scripting

Hello

I have a problem scripting with sqlite3

I can use, on the same bash the line

sqlite3
/Users/alberto/Documents/Casa/Domótica/Programas/cronizados/WeatherTracker/casa\
Database.sdb "select OutsideTemp from WXData where
RecDateTime>='2011-12-07 21:22' and RecDateTime<='2011-12-07 21:27' and
RecordType='R';"

This gives me the result (being a number)

If I use the same line inside a bash script like:

TomaCampo=sqlite3
/Users/alberto/Documents/Casa/Domótica/Programas/cronizados/WeatherTracker/casa\
Database.sdb "select OutsideTemp from WXData where
RecDateTime>='2011-12-07 21:22' and RecDateTime<='2011-12-07 21:27' and
RecordType='R';"

It raises me up an error:

/Users/alberto/Documents/Casa/Domótica/Programas/cronizados/WeatherTracker/pruebaSqLite:
line 27:
/Users/alberto/Documents/Casa/Domótica/Programas/cronizados/WeatherTracker/casa
Database.sdb: Permission denied

The file "casa Database.sdb" has read rights for owner, group and others

Any ideas how to deal with this matter?

Thank you,

Zenaida
___
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] Bash Scripting

2011-12-12 Thread Alberto De La Torre 

Hello

I have a problem scripting with sqlite3

I can use, on the same bash the line

sqlite3 
/Users/alberto/Documents/Casa/Domótica/Programas/cronizados/WeatherTracker/casa\ 
Database.sdb "select OutsideTemp from WXData where 
RecDateTime>='2011-12-07 21:22' and RecDateTime<='2011-12-07 21:27' and 
RecordType='R';"


This gives me the result (being a number)

If I use the same line inside a bash script like:

TomaCampo=sqlite3 
/Users/alberto/Documents/Casa/Domótica/Programas/cronizados/WeatherTracker/casa\ 
Database.sdb "select OutsideTemp from WXData where 
RecDateTime>='2011-12-07 21:22' and RecDateTime<='2011-12-07 21:27' and 
RecordType='R';"


It raises me up an error:

/Users/alberto/Documents/Casa/Domótica/Programas/cronizados/WeatherTracker/pruebaSqLite: 
line 27: 
/Users/alberto/Documents/Casa/Domótica/Programas/cronizados/WeatherTracker/casa 
Database.sdb: Permission denied


The file "casa Database.sdb" has read rights for owner, group and others

Any ideas how to deal with this matter?

Thank you,

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

Re: [sqlite] Fts */or. Inconsistencies

2011-12-12 Thread Dan Kennedy 

On 12/13/2011 02:29 AM, Ephraim Stevens wrote:

I'm using a custom tokenizer in each scenario (yes it works and the proof
is enclosed). In the first dataset, the data was tokenized such that any
alphanumeric character qualifies as part of a token.

In the second dataset, the data was tokenized such that anything other than
a semicolon qualifies as part of a token.\

The issues I'm raising is that 1) the '*' expansion doesn't seem to work
for a alphanumeric/non-alphanumeric token matches nor does the OR operator.
I haven't figured out what I'm missing here. I've been banging my head all
morning against tihs. I appreciate any help.


Do your custom tokenizers allow whitespace or "*" characters
to be part of tokens? If so, try changing them so that they
do not.



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

[sqlite] Fts */or. Inconsistencies

2011-12-12 Thread Ephraim Stevens 
SQLite Gurus,

In SQLIte FTS3/4, does the '*' (wildcard expansion character) discriminate
between alphanumeric characters vs non-alpha numeric characters when
matching? I have two test cases below which causes me to believe that it
does. Also, the OR operator appears to fail when matching against a
combination of alphanumerics and non-alphanumerics.

I'm using a custom tokenizer in each scenario (yes it works and the proof
is enclosed). In the first dataset, the data was tokenized such that any
alphanumeric character qualifies as part of a token.

In the second dataset, the data was tokenized such that anything other than
a semicolon qualifies as part of a token.\

The issues I'm raising is that 1) the '*' expansion doesn't seem to work
for a alphanumeric/non-alphanumeric token matches nor does the OR operator.
I haven't figured out what I'm missing here. I've been banging my head all
morning against tihs. I appreciate any help.

I've listed a working and non working example below since a comparison
should clarify best:

[THIS WORKS FINE]

MY DATASET:

STATE|NAMES

---

maryland|fred,louis,jenny

virginia|ruth,greg,denise

maine|richard,norman,willis

TOKENIZER CRITERIA USED: = any word characters (\w+)

(comment: all these names should be split into regular tokens and they were
as shown below)

FULL TEXT INDEX:

sqlite> select * from ft_terms;

term col documents occurrences

-- -- -- ---

fred * 1 1

fred 1 1 1

jenny * 1 1

jenny 1 1 1

louis * 1 1

louis 1 1 1

maryland * 1 1

maryland 0 1 1

ruth * 1 1

ruth 1 1 1

virginia * 1 1

virginia 0 1 1

QUERY:

select * from word where word match 'mary* jen*'

RETURNS:

maryland|fred,louis,jenny

QUERY:

select * from word where word match 'mary* OR v*'

RETURNS:

maryland|fred,louis,jenny

virginia|ruth,greg,denise

All the above behaves as expected. Now lets introduce some non-alphanumerics

--

[THIS DOESN'T WORK]

DATASET:

ROWSET|PAIR

--

1 A=15;B=16;C=38

2 D=15;E=25;F=16

TOKENIZER CRITERIA USED: = any character that is NOT a semicolon ([^;]+)

(comment: all these PAIR values should be tokenized by semicolon and they
were as shown below)

FULL TEXT INDEX:

term col documents occurrences

-- -- -- ---

1 * 1 1

1 0 1 1

2 * 1 1

2 0 1 1

A=15 * 1 1

A=15 1 1 1

B=16 * 1 1

B=16 1 1 1

C=38 * 1 1

C=38 1 1 1

D=15 * 1 1

D=15 1 1 1

E=25 * 1 1

E=25 1 1 1

F=16 * 1 1

F=16 1 1 1

QUERY1:

select * from NUMMY where NUMMY MATCH 'A=* OR D=*'

RETURNS:

(nothing)

comment: Should have returned ROWSET 1 and 2 (refer to above dataset)

QUERY2:

select * from NUMMY where NUMMY MATCH 'A* C*'

RETURNS:

(nothing)

comment: Should have returned ROWSET 1 (refer to above dataset)

--HOWEVER SPECIFYING THE FULL TOKEN WORKS--

QUERY3:

select * from NUMMY where NUMMY MATCH 'A=15'

RETURNS:

1|A=15;B=16;C=38

QUERY4:

select * from NUMMY where NUMMY MATCH 'A=15'

RETURNS:

1|A=15;B=16;C=38

QUERY5:

select * from NUMMY where NUMMY MATCH 'E=25'

RETURNS:

2|D=15;E=25;F=16

--THIS SUCCESS IS SHORTLIVED--

QUERY6:

select * from NUMMY where NUMMY MATCH 'E=25 OR B=16'

RETURNS:

(nothing)

This query should have returned rowset 1 and two as this was an OR query
and both creiteria are met.

In summary, the wildcard expansion as well as the OR operator seems not to
work in the second example. There only main difference between the two data
sets in that the first set is composed of alpha characters only and the
second is a combination of alphanumeric and non-aplhanumerica characters.
The (*) expansion character is not matching these. A match only occurs when
you specify the full token.

Thanks for your time in looking at this issue.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Aggregating Forex data

2011-12-12 Thread Igor Tandetnik 

On 12/12/2011 9:45 AM, Rafael Garcia Leiva wrote:

Now I need the Open and the Close of the 5 minutes interval, where Open
is the Open of the first minute of the interval, and Close is the Close
of the last minute of the interval.


Something like this:

select strftime('%Y-%m-%d %H:%M', min(date)) as Date,
  max(high) as High, min(Low) as Low,
  max(case when strftime('%s', date) % (5*60) = 0 then open else null 
end) as Open,
  max(case when strftime('%s', date) % (5*60) = 4*60 then close else 
null end) as Close

from eurusd group by strftime('%s', date) / (5*60);

--
Igor Tandetnik

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

Re: [sqlite] EXT :Re: Poor performance with nested query in outer join

2011-12-12 Thread Richard Hipp 
On Mon, Dec 12, 2011 at 9:33 AM, Nick Smallbone wrote:

> "Black, Michael (IS)" 
> writes:
>
> > Why do you have a subselectwhat are you doing there that you can't
> > do in the "on" clause?
>
> In the "real" example I'm joining with a view:
>  create view v as select * from b where ...
>  select * from a left natural join v where id = 1;
>
> IIUC, when I execute the query on the second line, SQLite replaces the
> use of "v" with v's definition, "select * from b where ...", so as far
> as the query planner is concerned there is a subquery. The only way to
> avoid the subquery is not to use the view.
>

We used to try to optimize the right side of a LEFT JOIN by "flattening"
the subquery into the main query.  But that doesn't always work (as ticket
http://www.sqlite.org/cvstrac/tktview?tn=3300 demonstrated) so that
optimization was disabled.


>
> Nick
>
> ___
> 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] Aggregating Forex data

2011-12-12 Thread Rafael Garcia Leiva 

El 24/11/2011 19:18, Rafael Garcia Leiva escribió:

El 24/11/2011 14:49, 雷钦 escribió:

On 2011-11-24 08:02:21 +, Simon Slavin wrote:

On 24 Nov 2011, at 7:42am, Rafael Garcia Leiva wrote:


CREATE TABLE eurusd (
date   TEXT NOT NULL PRIMARY KEY,
open   REAL NOT NULL,
high   REAL NOT NULL,
lowREAL NOT NULL,
close  REAL NOT NULL
);

The granularity of the data is one minute, for example:

INSERT INTO eurusd (date, open, high, low, close) VALUES (
'2011-11-01 00:01:00', '1.1212', '1.2323', '1.3434', '1.4545'
);

For the analysis of the data it is important to aggreate table rows 
using other timeframes. If I want to do analysis of data aggregated 
by months I can use the following query:


SELECT MAX(high) AS High, MIN(low) as Low,
STRFTIME("%Y-%m-%d", date) as Date
FROM eurusd GROUP BY STRFTIME("%Y-%m-%d", date);

In the same way I can aggregate the data by days and hours.

The problem is that I have to aggregate and analyze the data with 
other less conventional time frames, like 5 minutes, 15 minutes, or 
even 23 minutes.
I recommend that you store the datestamp in a numeric form.  You can 
do either keep your existing column and add a new one, writing the 
data to both columns, or replace the existing text datestamp.


Two easy-to-convert formats would be Julian Day and Unix Epoch.  
Julian Days are floats where 1 = 1 dayr; Unix Epochs generated by 
SQLite are floats where 1 = 1 second.  It appears that you're 
interested in sub-day units so the unix format might be most useful 
for you.


See



Your SELECT would be something like

SELECT MAX(high) AS High, MIN(low) as Low,
STRFTIME("%Y-%m-%d", date) as Date
FROM eurusd GROUP BY round(timestamp / 23 * 60)

I think it is the same as

SELECT MAX(high) AS High, MIN(low) as Low,
STRFTIME('%Y-%m-%d',date) as Date
FROM eurusd GROUP BY round(STRFTIME('%s',date) / (23 * 60))


Many thanks for the answers. That's exactly what I was looking for!

Just one final remark, the round() function groups minutes from, from 
example, 3 to 7, but I really want to group minutes from 0 to 4. That 
should be the work of the floor() function, but unfortunately it is 
not part of the standard sqlite distribution (I have to learn this 
loadable extensions mechanism). But I think that I can get the same 
result performing a CAST(... AS INTEGER).


SELECT MAX(high) AS High, MIN(low) as Low,
STRFTIME('%Y-%m-%d %H', date) as Date
FROM eurusd GROUP BY CAST(STRFTIME('%s', date) / (5 * 60) AS INTEGER);



Dear all,

I'm still working on this problem :-(

Now I need the Open and the Close of the 5 minutes interval, where Open 
is the Open of the first minute of the interval, and Close is the Close 
of the last minute of the interval. I know how to get that information 
with individual queries, for example:


SELECT open, date FROM eurusd WHERE date IN
(select min(date) from eurusd group by STRFTIME("%Y-%m-%d", date));

But it would be very nice (and perhaps more computationally efficient?) 
to get all the information, that is Open, High, Low and Close in just 
one single query. I have tried something like:


SELECT STRFTIME('%Y-%m-%d %H %M', date) AS Date,
   (SELECT open from eurusd e2 where e2.date = MIN(e1.date)) AS Open,
   MAX(high) as High,
   MIN(low) as Low,
   (SELECT close from eurusd e3 where e3.date = MAX(e1.date)) AS Close
FROM eurusd e1
GROUP BY CAST(STRFTIME('%s', e1.date) / (5 * 60) AS INTEGER)

but I have got an error of "misuse of aggregate function MIN()".

Any help would be very welcome. Also I would like to apologize if this 
question is not relevant to the sqlite mailing list.


Best regards

Rafael




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

Re: [sqlite] EXT :Re: Poor performance with nested query in outer join

2011-12-12 Thread Nick Smallbone 
"Black, Michael (IS)" 
writes:

> Why do you have a subselectwhat are you doing there that you can't
> do in the "on" clause?

In the "real" example I'm joining with a view:
  create view v as select * from b where ...
  select * from a left natural join v where id = 1;

IIUC, when I execute the query on the second line, SQLite replaces the
use of "v" with v's definition, "select * from b where ...", so as far
as the query planner is concerned there is a subquery. The only way to
avoid the subquery is not to use the view.

Nick

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

Re: [sqlite] EXT :Re: Poor performance with nested query in outer join

2011-12-12 Thread Black, Michael (IS) 
Why do you have a subselectwhat are you doing there that you can't do in 
the "on" clause?



Can you show your subselect?  Your example isn't enough.





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 Nick Smallbone [n...@8325.org]
Sent: Saturday, December 10, 2011 4:35 PM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Poor performance with nested query in outer join

"Black, Michael (IS)" 
writes:

> Natural joins are generally considered to be evil.  Too many columns
> in common can be bad.
>
> If you just spell it out it works as expected
>
> sqlite> explain query plan select * from a left join b where a.id=1 and 
> b.id=a.id;
> 0|0|0|SEARCH TABLE a USING COVERING INDEX sqlite_autoindex_a_1 (id=?) (~1 
> rows)
> 0|1|1|SEARCH TABLE b USING COVERING INDEX sqlite_autoindex_b_1 (id=?)
> (~1 rows)

Thanks, but this is a red herring: it makes no difference whether you
use natural join or an explicit join here (and I would've been very
surprised if it had, because they are exactly the same
operation). Rather, the difference between my query and your query is
that I have a subquery (select * from b) and you don't.

Nick

___
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] Strange behavior on SQLite 3.7x compared with 3.6.22

2011-12-12 Thread Alessandro Merolli 

Thank you D. Richard Hipp, for your time and nice work.


On 10/12/2011, at 15:25, Richard Hipp wrote:

On Thu, Dec 8, 2011 at 12:25 PM, Alessandro Merolli wrote:



Hi,

  We've being working with SQLite version 3.6.22 in our project  
and
we wish to upgrade it to the latest one. During the tests with the  
new
library version, we noticed a strange behavior related to a trigger  
which
updates the last inserted row. We where able to simplify the data  
model and

produce a test case which can be executed using the sqlite3 shell



This is a real problem that was introduced when we added recursive
triggers.  But it is very obscure and only comes up when you have  
really

complicated queries inside of triggers - queries that make use of
manifested views and/or automatic indices.  The trouble ticket is at
http://www.sqlite.org/src/info/7bbfb7d442 and the fix is checked in at
http://www.sqlite.org/src/info/557c69055a with additional changes at
http://www.sqlite.org/src/info/0064bab771 that fix related issues and
verify with assert() statements that similar kinds of problems do not
recur.  These changes will be in the next release.

Thanks for providing a test case.  That was very helpful in tracking  
down

the problem.




Here are the outputs:

Execution with latest SQLite 3.7.9:
c:\>sqlite3shell.exe -init .\test_case.sql ":memory:"
-- Loading resources from .\test_case.sql
Expected result: 31|10
Problematic result: 31|0
31|0
SQLite version 3.7.9
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .quit
c:\>

Execution with latest SQLite 3.6.22:
c:\>sqlite3shell.exe -init .\test_case.sql ":memory:"
-- Loading resources from .\test_case.sql
Expected result: 31|10
Problematic result: 31|0
31|10
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .quit

We are using the amalgamation version of the source for both  
libraries and

here are the compilation defines:
TEMP_STORE=3
SQLITE_THREADSAFE=2
SQLITE_DEFAULT_CACHE_SIZE=**65568
SQLITE_DEFAULT_TEMP_CACHE_**SIZE=65568
SQLITE_MAX_ATTACHED=30
SQLITE_ENABLE_COLUMN_METADATA
SQLITE_ENABLE_UNLOCK_NOTIFY

The error was also reproduced on the original library available for  
Ubuntu

10.10.
Additional information is available in the test_case.sql script  
comments.


  Thanks for the assistance.
  Regards,
  Alessandro Merolli.



__**_
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] deriving Foreign Key constraints

2011-12-12 Thread Simon Slavin 

On 12 Dec 2011, at 9:26am, Roughbert Strong wrote:

> Suppose that we want to know what the foreign key 
> constraints are on columns returned by a query. Can we obtain this 
> information 
> without parsing the query itself and looking at the PRAGMA foreign_key_list 
> for each table thus 
> discovered?

You might be able to do it using EXPLAIN or EXPLAIN QUERY PLAN but I don't 
think there's an ideal way.

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

Re: [sqlite] Poor performance with nested query in outer join

2011-12-12 Thread Nick Smallbone 
Simon Slavin  writes:
> I understand that SQLite handles VIEWs as if you had defined and saved
> a SELECT statement.  So if you JOIN with a VIEW, does SQLite handle it
> as as JOIN or a sub-SELECT ?

It's just the same as if you JOIN with a sub-SELECT (the query for the
view), as far as I can tell. So if you LEFT JOIN with a VIEW you always
get this problem.

Nick

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

[sqlite] deriving Foreign Key constraints

2011-12-12 Thread Roughbert Strong 
Suppose that we want to know what the foreign key 
constraints are on columns returned by a query. Can we obtain this information 
without parsing the query itself and looking at the PRAGMA foreign_key_list for 
each table thus 
discovered?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users