[sqlite] about date question

[YAN HONG YE]

sqlite> select fmn,DATE('NOW'),supplierDate,date('now'),strftime(supplierDate),s
trftime(supplierDate)-date('now') from t93c_adl where strftime(supplierDate)

why use supplierDate,date('now'),strftime(supplierDate) the result is not same 
result format?
the one is 2012-11-09
another is 12/01/2012
?
and how to get the subtraction of two column?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS substring behavior

[Yongil Jang]

A little bit different.
That sentence of "full text search" is split as [ful, tex, sea, ull, ll, l,
ext, xt, xt, ear, arc, rch, ch, h] with 3 maximum length of key.
Of course, search pattern string length is limited to 3 and query is
changed as follows.

[Example: search 'earch']
SELECT ... FROM [FTS table] WHERE [search fields or table] MATCH 'ear' AND
([search field1] like '%earch%' OR [search field2] like '%earch%' OR ... OR
[search fieldn] like '%earch%')

The reason of ...
 1) 3 characters: To minimize FTS index size and to improve search
performance
 2) Adding like clauses: To refine result set
 2) many likes: To search all columns in FTS table when a table name is
used in MATCH clause.

BR,
Yongil Jang.



2012/11/9 Paul Vercellotti 

>
>
> That's a promising project; I hope it reaches maturity.
>
> I assume your modified tokenizer did a similar thing, like tokenizing
> "full text search" as [full, text, search, ull, ll, l, ext, xt, xt, earch,
> arch, rch, ch, h]?
>
> What worked and what did not work?
>
> Thanks,
> Paul
>
>
> 
>  From: Yongil Jang 
> To: General Discussion of SQLite Database 
> Sent: Thursday, November 8, 2012 2:26 PM
> Subject: Re: [sqlite] FTS substring behavior
>
> How about look at following URL?
>
> https://github.com/jonasfj/trilite
>
> AFAIK, FTS doesn't support substring search.
> I also tried to edit FTS to find substring by changing simple tokenizer.
> It was worked partially, but not a good solution to use generally.
>
>
> 2012/11/9 Dan Kennedy 
>
> > On 11/09/2012 01:49 AM, Paul Vercellotti wrote:
> >
> >>
> >>
> >> Hi there,
> >>
> >> I wanted to clarify if FTS could provide any optimization for substring
> >> matches like '*ion*' or similar?
> >>
> >
> > No. I think it will actually search for tokens that start with the 4
> > ASCII characters "*ion" if you try that.
> >
> > Dan.
> >
> > __**_
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<
> 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] how to use julianday function

[Simon Davies]

On 9 November 2012 02:42, YAN HONG YE  wrote:
> sqlite> select 
> fmn,DATE('NOW'),supplierDate,julianday('now'),julianday(supplierDate),julianday('supplierDate')
>  from t93c_adl where supplierDate>julianday('now') limit 5 ;
> 854954|2012-11-09|12/01/2012|2456240.61186149||
> 854952|2012-11-09|03/01/2013|2456240.61186149||
> 635604|2012-11-09|05/27/2011|2456240.61186149||
> 635605|2012-11-09|03/16/2012|2456240.61186149||
> 635607|2012-11-09|09/15/2012|2456240.61186149||

http://www.sqlite.org/lang_datefunc.html

 ISO-8601 format for date strings

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

[sqlite] how to use julianday function

[YAN HONG YE]

sqlite> select 
fmn,DATE('NOW'),supplierDate,julianday('now'),julianday(supplierDate),julianday('supplierDate')
 from t93c_adl where supplierDate>julianday('now') limit 5 ;
854954|2012-11-09|12/01/2012|2456240.61186149||
854952|2012-11-09|03/01/2013|2456240.61186149||
635604|2012-11-09|05/27/2011|2456240.61186149||
635605|2012-11-09|03/16/2012|2456240.61186149||
635607|2012-11-09|09/15/2012|2456240.61186149||
sqlite>


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

Re: [sqlite] FTS substring behavior

[Paul Vercellotti]

That's a promising project; I hope it reaches maturity.

I assume your modified tokenizer did a similar thing, like tokenizing "full 
text search" as [full, text, search, ull, ll, l, ext, xt, xt, earch, arch, rch, 
ch, h]?

What worked and what did not work?

Thanks,
Paul



 From: Yongil Jang 
To: General Discussion of SQLite Database  
Sent: Thursday, November 8, 2012 2:26 PM
Subject: Re: [sqlite] FTS substring behavior
 
How about look at following URL?

https://github.com/jonasfj/trilite

AFAIK, FTS doesn't support substring search.
I also tried to edit FTS to find substring by changing simple tokenizer.
It was worked partially, but not a good solution to use generally.


2012/11/9 Dan Kennedy 

> On 11/09/2012 01:49 AM, Paul Vercellotti wrote:
>
>>
>>
>> Hi there,
>>
>> I wanted to clarify if FTS could provide any optimization for substring
>> matches like '*ion*' or similar?
>>
>
> No. I think it will actually search for tokens that start with the 4
> ASCII characters "*ion" if you try that.
>
> Dan.
>
> __**_
> 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] sqlite-users Digest, Vol 59, Issue 7

[Simon Davies]

On 9 November 2012 01:40, YAN HONG YE  wrote:
> select date( supplierDate, '+2 days' ) ;
> use this cmd couldn't solve the problem;
> will have nothing on this column:
>
> sqlite> select fmn,'84 day'+supplierDate from t93c_adl where fmn like 
> '85493%';
> 854939|95
> 854938|95
> 854937|95
> sqlite> select fmn,date('84 day'+supplierDate) from t93c_adl where fmn like 
> '854
> 93%';
> 854939|-4712-02-27
> 854938|-4712-02-27
> 854937|-4712-02-27
> sqlite> select fmn,date(supplierDate) from t93c_adl where fmn like '85493%';
> 854939|
> 854938|
> 854937|
> sqlite> select fmn,supplierDate from t93c_adl where fmn like '85493%';
> 854939|11/15/2012
> 854938|11/30/2012
> 854937|11/30/2012

The dates you are trying to process are not ISO-8601 standard
The link provided earlier to the date and time functions indicate that
the textual date/time must conform to (a subset of)  ISO-8601

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

Re: [sqlite] couldn't use is null function

[Igor Tandetnik]

On 11/8/2012 8:49 PM, YAN HONG YE wrote:

sqlite> SELECT fmn,IMPLANTATIONgrading FROM T93C_ADL WHERE IMPLANTATIONgrading i
s not null and fmn like '85495%';
854954|R
854952|  
//-here is 
NULL, but I don't know why it shows in the not null result


It's not NULL, it's an empty string. Run these statemetns, see for yourself:

SELECT fmn,IMPLANTATIONgrading FROM T93C_ADL WHERE IMPLANTATIONgrading 
!= '' and fmn like '85495%';
SELECT fmn,IMPLANTATIONgrading FROM T93C_ADL WHERE IMPLANTATIONgrading = 
'' and fmn like '85495%';


--
Igor Tandetnik

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

Re: [sqlite] couldn't use is null function

[Simon Davies]

On 9 November 2012 01:49, YAN HONG YE  wrote:
> sqlite> SELECT fmn,IMPLANTATIONgrading FROM T93C_ADL WHERE 
> IMPLANTATIONgrading i
> s not null and fmn like '85495%';
> 854954|R
> 854952|  
> //-here is 
> NULL, but I don't know why it shows in the not null result

What you are claiming is null is an empty string; they are not the same thing.

> 854951|R
> 854953|R
> 854950|R
> 854959|G
> 854957|
> 854955|R
> 854956|R
> 854958|

http://en.wikipedia.org/wiki/Null_(SQL)

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

Re: [sqlite] sqlite-users Digest, Vol 59, Issue 7

[Igor Tandetnik]

On 11/8/2012 8:40 PM, YAN HONG YE wrote:

sqlite> select fmn,supplierDate from t93c_adl where fmn like '85493%';
854939|11/15/2012


Values in supplierDate column are not in a format that SQLite's 
date/time functions recognize. They expect '2012-11-15', that is 
year-month-day. If you are willing to reformat the column, you can do so 
by running this statement:


update t93c_adl set supplierDate=substr(supplierDate, 7) || '-' || 
substr(supplierDate, 1, 2) || '-' || substr(supplierDate, 4, 2);


*After* this is done, date() would work as expected:

select date(supplierDate,'+2 days') from t93c_adl;
--
Igor Tandetnik

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

Re: [sqlite] couldn't use is null function

[YAN HONG YE]

sqlite> SELECT fmn,IMPLANTATIONgrading FROM T93C_ADL WHERE IMPLANTATIONgrading i
s not null and fmn like '85495%';
854954|R
854952|  
//-here is 
NULL, but I don't know why it shows in the not null result
854951|R
854953|R
854950|R
854959|G
854957|
854955|R
854956|R
854958|
sqlite> SELECT fmn,IMPLANTATIONgrading FROM T93C_ADL WHERE IMPLANTATIONgrading i
s null and fmn like '85495%';
sqlite> SELECT fmn,IMPLANTATIONgrading FROM T93C_ADL WHERE fmn like '85495%';
854954|R
854952|
854951|R
854953|R
854950|R
854959|G
854957|
854955|R
854956|R
854958|
sqlite>



Message: 6
Date: Wed, 7 Nov 2012 08:44:01 +
From: Simon Davies 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] couldn't use is null function
Message-ID:

Re: [sqlite] sqlite-users Digest, Vol 59, Issue 7

[YAN HONG YE]

select date( supplierDate, '+2 days' ) ;
use this cmd couldn't solve the problem;
will have nothing on this column:

sqlite> select fmn,'84 day'+supplierDate from t93c_adl where fmn like '85493%';
854939|95
854938|95
854937|95
sqlite> select fmn,date('84 day'+supplierDate) from t93c_adl where fmn like '854
93%';
854939|-4712-02-27
854938|-4712-02-27
854937|-4712-02-27
sqlite> select fmn,date(supplierDate) from t93c_adl where fmn like '85493%';
854939|
854938|
854937|
sqlite> select fmn,supplierDate from t93c_adl where fmn like '85493%';
854939|11/15/2012
854938|11/30/2012
854937|11/30/2012
sqlite> select fmn,date(supplierDate,'+2 days') from t93c_adl where fmn like '85
493%';
854939|
854938|
854937|
sqlite>



Message: 10
Date: Wed, 7 Nov 2012 10:48:24 +
From: Simon Davies 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] FW: about date question
Message-ID:

Content-Type: text/plain; charset=ISO-8859-1

On 7 November 2012 09:42, YAN HONG YE  wrote:
> the table had a column: SupplierDate Date
> I wanna add 84 days :
> SELECT SupplierDate+84 as date1  FROM ADL;
> BUT result have noting
> when use:
> select date(supplierdate+'2 day') from t93c_adl limit 3;
> the result is:
> -4713-12-08
> -4713-11-29
> -4713-12-01
>

select date( supplierDate, '+2 days' )

http://www.sqlite.org/lang_datefunc.html

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

Re: [sqlite] FTS substring behavior

[Yongil Jang]

How about look at following URL?

https://github.com/jonasfj/trilite

AFAIK, FTS doesn't support substring search.
I also tried to edit FTS to find substring by changing simple tokenizer.
It was worked partially, but not a good solution to use generally.


2012/11/9 Dan Kennedy 

> On 11/09/2012 01:49 AM, Paul Vercellotti wrote:
>
>>
>>
>> Hi there,
>>
>> I wanted to clarify if FTS could provide any optimization for substring
>> matches like '*ion*' or similar?
>>
>
> No. I think it will actually search for tokens that start with the 4
> ASCII characters "*ion" if you try that.
>
> Dan.
>
> __**_
> 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] newest 3 entries

[Rob Richardson]

Select id, eventdate, eventtype,  FROM eventlog WHERE eventtype in
('special')  order by eventdate desc limit 3

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of deltagam...@gmx.net
Sent: Thursday, November 08, 2012 3:10 PM
To: sqlite-users
Subject: [sqlite] newest 3 entries

I have a select statement like:
"Select id, eventdate, eventtype,  FROM eventlog WHERE eventtype in
('special')  "

Now I like to receive only the newest 3 entries in the table eventlog where the 
eventtype is 'special'


___
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] FTS substring behavior

[Dan Kennedy]

On 11/09/2012 01:49 AM, Paul Vercellotti wrote:



Hi there,

I wanted to clarify if FTS could provide any optimization for substring matches 
like '*ion*' or similar?


No. I think it will actually search for tokens that start with the 4
ASCII characters "*ion" if you try that.

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

Re: [sqlite] newest 3 entries

[Baruch Burstein]

On Thu, Nov 8, 2012 at 10:17 PM, Igor Tandetnik  wrote:

> On 11/8/2012 3:10 PM, deltagam...@gmx.net wrote:
>
>> I have a select statement like:
>> "Select id, eventdate, eventtype,  FROM eventlog WHERE eventtype in
>> ('special')  "
>>
>> Now I like to receive only the newest 3 entries in the table eventlog
>> where the eventtype is 'special'
>>
>
> select id, eventdate, eventtype FROM eventlog WHERE eventtype in
> ('special')
> order by eventdate desc limit 3;
>
I'm guessing that  "WHERE eventtype = 'special' " is more efficient then
"WHERE eventtype in ('special') "
Just a guess.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] newest 3 entries

[deltagam...@gmx.net]

Am 08.11.2012 21:17, schrieb Igor Tandetnik:

On 11/8/2012 3:10 PM, deltagam...@gmx.net wrote:

I have a select statement like:
"Select id, eventdate, eventtype,  FROM eventlog WHERE eventtype in
('special')  "

Now I like to receive only the newest 3 entries in the table eventlog
where the eventtype is 'special'


select id, eventdate, eventtype FROM eventlog WHERE eventtype in 
('special')

order by eventdate desc limit 3;

Yes, thx Igor, I got it too now, long time that I was occupied with 
sqlite ;)


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

Re: [sqlite] newest 3 entries

[Igor Tandetnik]

On 11/8/2012 3:10 PM, deltagam...@gmx.net wrote:

I have a select statement like:
"Select id, eventdate, eventtype,  FROM eventlog WHERE eventtype in
('special')  "

Now I like to receive only the newest 3 entries in the table eventlog
where the eventtype is 'special'


select id, eventdate, eventtype FROM eventlog WHERE eventtype in ('special')
order by eventdate desc limit 3;

--
Igor Tandetnik

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

[sqlite] newest 3 entries

[deltagam...@gmx.net]

I have a select statement like:
"Select id, eventdate, eventtype,  FROM eventlog WHERE eventtype in 
('special')  "


Now I like to receive only the newest 3 entries in the table eventlog 
where the eventtype is 'special'



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

Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns

[gwenn]

If you want, you can verify automatically that all the FK columns have a
type matching the referenced columns by using (and tweaking) an old tool
whose name is 'genfkey' (see http://www.sqlite.org/faq.html#q22 but the
'readme' link is broken).
Regards.



On Thu, Nov 8, 2012 at 6:29 PM, Simon Slavin  wrote:

>
> On 8 Nov 2012, at 5:27pm, stahl...@dbs.uni-hannover.de wrote:
>
> > But inferring the FK's type from the referenced PK would cause
> applications
> > which rely on the FK's type affinity being 'none' to be broken, no?
>
> At this sort of level of bug-compatibility, you have to say "Will not be
> fixed until SQLite4."
>
> 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

[sqlite] FTS substring behavior

[Paul Vercellotti]

Hi there,

I wanted to clarify if FTS could provide any optimization for substring matches 
like '*ion*' or similar?

That is, does it only scan the token index for matching tokens to locate the 
main table records that contain those tokens, or does it do a full table scan 
of the main table?

The number of unique tokens we have is small compared to the total number of 
records, so if it only scanned the token index it would in theory help.

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

Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns

[Simon Slavin]

On 8 Nov 2012, at 5:27pm, stahl...@dbs.uni-hannover.de wrote:

> But inferring the FK's type from the referenced PK would cause applications
> which rely on the FK's type affinity being 'none' to be broken, no?

At this sort of level of bug-compatibility, you have to say "Will not be fixed 
until SQLite4."

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

Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns

[stahlhut]

Quoting Ryan Johnson :


On 08/11/2012 8:04 AM, stahl...@dbs.uni-hannover.de wrote:

[...]

I think this is the documented behaviour:
http://www.sqlite.org/datatype3.html

tab1.id has integer affinity, and '42' is coerced to integer
tab2.id has none affinity, and '42' is not coerced

[...]


There are actually users *relying* on this incoherent behaviour?
Granted, I don't have insight in the full consequences of this problem,
but I find that hard to believe: This would clearly be bad database design.


Agree, but badly-designed software has an annoying habit of showing  
up in production and then yelling loudly when you fix the bug it  
depends on.


So true... :-(


If this is actually a concern, then I think the best way of fixing would be
to do what PostgreSQL does: Make '[column-name] REFERENCES...' a  
syntax error and explicitly require a datatype in this case.


I actually liked your idea of making the FK field inherit the type  
of the PK field it references.


I think that this would be the best solution.

But inferring the FK's type from the referenced PK would cause applications
which rely on the FK's type affinity being 'none' to be broken, no?

With a change of syntax on the other hand, the break would at least be clearly
visible:
Existing databases would continue to behave as is.
But statements in the form of 'CREATE TABLE ( ... [column-name]  
REFERENCES... )'

which used to be accepted, would now be rejected.

Screaming users could then be informed that a proper datatype must be chosen
for [column-name] -- 'BLOB' if the user actually relies on the foreign key
to have type affinity 'none'.

Meanwhile, you might want to tell sqlite to enforce those foreign  
key constraints you so carefully specified:  
http://www.sqlite.org/foreignkeys.html#fk_enable [1].


Then, the only way to insert '24' into the FK table and have it  
mismatch the 24 in your PK table is if your PK table has '24' in it  
as well. But that would clearly be bad database design :P


Hehe! You caught me... :-D
(I actually tripped over this myself as I tried to feed my example to Oracle!)

[1] I always forget that check's off by default... somehow I doubt  
it would catch problems with previously inserted keys, but you could  
fix them in one fell swoop with: update $fktable set $fk=cast($fk as  
int)


Thanks! I forget to turn on the check too... I guess I'm somewhat spoiled
by "non-lite" DBMSes. :-)

Maybe the 'foreign-keys' pragma could also turn on the FK-type inheritance.
This seems reasonable and might mitigate the badly-designed-software-yelling
somewhat.

best regards,
Christian


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

Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns

[stahlhut]

Quoting Ryan Johnson :


On 07/11/2012 7:58 PM, Simon Davies wrote:

On 7 November 2012 20:36,   wrote:

[...]

I think this is the documented behaviour:
http://www.sqlite.org/datatype3.html

tab1.id has integer affinity, and '42' is coerced to integer
tab2.id has none affinity, and '42' is not coerced



[...]


Also, ironically, the documentation claims that "The dynamic type system of
SQLite is backwards compatible with the more common static type systems of
other database engines...".

I just checked the example with the Oracle and PostgreSQL instances I have
at hand here:
Oracle does what I think is correct and returns a row in both cases.
PostgreSQL does not allow the "id REFERENCES" construction at all and
requires a data type even for foreign keys.

So in this case SQLite is incompatible with two major DBMSes. :-)



Which from what you have said, are also incompatible with each other!



Man with sharp stick has point.


Okay, but at least Oracle and PostgreSQL don't claim to do their
auto-coerce-voodoo because of compatibility with other database engines. :-]

To be fair, though, I have been bitten numerous times by exactly  
this same scenario, where foreign key joins fail because the key  
types somehow end up differing and don't coerce automatically. Very  
easy to forget, or to accidentally let a string slip in where an int  
was intended (say, by loading from csv). Also hard to diagnose.


Interesting... so I'm not the only one bitten by this.

Not sure the best way to "fix" the problem [1], but it might be the  
single most surprising aspect of using sqlite3 in my experience.


[1] especially since there's probably a customer out there somewhere  
whose app actually depends on foreign key join columns having  
different types and not matching '24' with 24.


There are actually users *relying* on this incoherent behaviour?
Granted, I don't have insight in the full consequences of this problem,
but I find that hard to believe: This would clearly be bad database design.

If this is actually a concern, then I think the best way of fixing would be
to do what PostgreSQL does: Make '[column-name] REFERENCES...' a syntax error
and explicitly require a datatype in this case.

Thanks for your insight!

regards,
Christian


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

[sqlite] sqlite feasibilty on Atmel AVR32 AP7000 based system

[Ogaard, Ommund UTCFS]

Hi

We are planning to upgrade the SW on our Atmel AVR32 AP7000, buildroot and 
uclib based embedded system. The functionality of sqlite seems to fit well, but 
I'm a bit concerned about how well it will work with the Linux threads library 
we are stuck with for this architecture. It seems to be impossible to get NPTL 
implemented.

Will Linux threads be OK if we only use sqlite in a single threaded 
environment? Which version of sqlite will be best fitted for this environment?

Best regards
Ommund Øgård
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] finding duplicate records i.e. records with same values across 4 colums...

[Simon Davies]

On 8 November 2012 11:23, e-mail mgbg25171  wrote:
> I'm using SQLIte.
> All columns can have duplicate values but it would be helpful to report
> on those rows which are identical across all columns.
> More specifically I'm looking for matching itm values where the first 3
> cols ALSO match but am not sure of the sqlite select query to do this.
>
> epic, yr, statement, itm
> ==
> mcro, 2002, income, revs
> mcro, 2002, income, cogs
> mcro, 2002, income, sg
> mcro, 2002, income, cogs
> mcro, 2003, balance, gdwil
> etc
>
> in the example I'm looking for a query that would return
> mcro, 2002, income, cogs
> mcro, 2002, income, cogs

select * from t natural join ( select epic, yr, statement, itm from t
group by epic, yr, statement, itm having count(*) > 1 ) t1;

>
> any help much appreciated
>
> BTW no field can be unique in the createtable statement


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

[sqlite] finding duplicate records i.e. records with same values across 4 colums...

[e-mail mgbg25171]

I'm using SQLIte.
All columns can have duplicate values but it would be helpful to report
on those rows which are identical across all columns.
More specifically I'm looking for matching itm values where the first 3
cols ALSO match but am not sure of the sqlite select query to do this.

epic, yr, statement, itm
==
mcro, 2002, income, revs
mcro, 2002, income, cogs
mcro, 2002, income, sg
mcro, 2002, income, cogs
mcro, 2003, balance, gdwil
etc

in the example I'm looking for a query that would return
mcro, 2002, income, cogs
mcro, 2002, income, cogs

any help much appreciated

BTW no field can be unique in the createtable statement
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users