Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Nico Williams]

On Wed, Mar 6, 2013 at 4:20 PM, Petite Abeille  wrote:
> All good. Small sanity check though:
>
> select 2 = cast( '2.1' as integer );
>> 1
>
> Hmmm….
>
>
> select 2 = cast( '2abc' as integer );
>> 1
>
> What?!? Oh… "When casting a TEXT value to INTEGER, the longest possible 
> prefix of the value that can be interpreted as an integer number is extracted 
> from the TEXT value and the remainder ignored. "… ah… ok… weird…

Actually, while this is true here, it doesn't render the CHECK
constraint I mentioned earlier unsafe:

sqlite> CREATE TABLE toy1(a INTEGER);
sqlite> CREATE TABLE toy2(a INTEGER CHECK(typeof(a) = 'integer'));
sqlite> CREATE TABLE toy3(a INTEGER CHECK(a = CAST(a AS INTEGER)));
sqlite> INSERT INTO toy1 VALUES ('2a');
sqlite> INSERT INTO toy2 VALUES ('2a');
Error: constraint failed
sqlite> INSERT INTO toy3 VALUES ('2a');
Error: constraint failed
sqlite> INSERT INTO toy3 VALUES ('2.1');
Error: constraint failed
sqlite> INSERT INTO toy3 VALUES (2);
sqlite>
sqlite> INSERT INTO toy1 VALUES (2.1);
sqlite> INSERT INTO toy1 VALUES ('2a');
sqlite> INSERT INTO toy1 VALUES ('2.1');
sqlite> SELECT a, quote(a), typeof(a) FROM toy1;
2.1|2.1|real
2a|'2a'|text
2.1|2.1|real
sqlite>

The reason for this: the CAST didn't work the same as affinity type
conversions.  The value of [a] fails to get converted to integer when
it's '2a', '2.1', or 2.1, causing the equality conversion to fail.

This works well for INTEGER, REAL, and NUMERIC (check for yourself).
For BLOB and TEXT CHECK(a = CAST(a AS ...)) has the same effect as
CHECK(typeof(a) = '...').  If you use "a NONE CHECK(a = CAST(a AS
NONE))" that seems to work as if one has used NUMERIC.

In conclusion, if you want to allow affine type conversions on INSERT,
but not disallow values that cannot be so converted, then
CHECK(my_column = CAST(my_column AS )) works.  And if you want
to disallow values of incorrect types even when type conversion is
possible then use CHECK(typeof(my_column = ).  That's pretty
cool, IMO.

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Petite Abeille]

On Mar 6, 2013, at 11:53 PM, Nico Williams  wrote:

>> o k i d o k i . . .
> 
> Oh.  Oh..  Ew..   Never mind then!

Yeah… a bit of a mind melt… nevertheless… such check should work as advertised… 
even handles nulls properly… perhaps too clever too... :D

"All magic comes with a price, Dearie" -- Mr Gold

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

Re: [sqlite] Bullzip ms Access to MySql

[Kevin Benson]

On Wed, Mar 6, 2013 at 4:36 PM,  wrote:

>
> Hi,
>
>

> *SNIP



>

one more thing, is there a way to search through the archived forums?
>
> Vance
>


Here's one way:

http://www.mail-archive.com/search?a=1=sqlite-users@sqlite.org=1d==datenewest=Search
--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A question about the ancient history of SQLite triggers

[Philip Warner]

On 6/03/2013 1:59 AM, Jay A. Kreibich wrote:
> In this case, it is any trigger that invokes any other trigger.
>   Prior to 3.6.18 there was no trigger "stack" and triggers could be
>   only one layer deep.

Ah, thanks. That solves the problem. I can dynamically generate a single 
inefficient trigger for 3.5.9.


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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Nico Williams]

On Wed, Mar 6, 2013 at 4:20 PM, Petite Abeille  wrote:
> All good. Small sanity check though:
>
>
> select 2 = cast( '2' as integer );
>> 1
>
> Ok… '2' is can be casted to 2… great...
>
>
> select 2 = cast( 'a2' as integer );
>> 0
>
> Ok… 'a2' cannot really be casted to an integer… cool...
>
>
> select 2 = cast( '2.1' as integer );
>> 1
>
> Hmmm….
>
>
> select 2 = cast( '2abc' as integer );
>> 1
>
> What?!? Oh… "When casting a TEXT value to INTEGER, the longest possible 
> prefix of the value that can be interpreted as an integer number is extracted 
> from the TEXT value and the remainder ignored. "… ah… ok… weird…
>
> So…
>
> select cast( 'abc' as integer );
>> 0
>
> o k i d o k i . . .

Oh.  Oh..  Ew..   Never mind then!

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Petite Abeille]

On Mar 6, 2013, at 10:49 PM, Nico Williams  wrote:

> Ah, your confusion comes from the fact that type conversion still
> happens when the INSERT gets around to making the record.  The CHECK
> constraint happens before the record is made.  See the vdbe that gets
> generated.

All good. Small sanity check though:


select 2 = cast( '2' as integer );
> 1

Ok… '2' is can be casted to 2… great...


select 2 = cast( 'a2' as integer );
> 0

Ok… 'a2' cannot really be casted to an integer… cool...


select 2 = cast( '2.1' as integer );
> 1

Hmmm….


select 2 = cast( '2abc' as integer );
> 1

What?!? Oh… "When casting a TEXT value to INTEGER, the longest possible 
prefix of the value that can be interpreted as an integer number is extracted 
from the TEXT value and the remainder ignored. "… ah… ok… weird…

So…

select cast( 'abc' as integer );
> 0

o k i d o k i . . .







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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Nico Williams]

On Wed, Mar 6, 2013 at 3:49 PM, Nico Williams  wrote:
> On Wed, Mar 6, 2013 at 3:47 PM, Petite Abeille  
> wrote:
>> Indeed. Never mind :)
>
> Ah, your confusion comes from the fact that type conversion still
> happens when the INSERT gets around to making the record.  The CHECK
> constraint happens before the record is made.  See the vdbe that gets
> generated.

Oh... I wonder if there's a guarantee of that, or if it'd be possible
that in the future CHECK constraints might get post-type conversion
values.  But I think it'd be risky to ever make such a change.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Petite Abeille]

On Mar 6, 2013, at 10:47 PM, Nico Williams  wrote:

>> Hmmm… on second thought… is that an assignment in that check constraint?!? 
>> I.e. are you reassigning a to a new cast value?!?
> 
> No.  The only place where = is an assignment is in UPDATE statements,
> in the SET clause.

Yeah… dazed and confused… should get a grip... :))

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Nico Williams]

On Wed, Mar 6, 2013 at 3:47 PM, Petite Abeille  wrote:
> Indeed. Never mind :)

Ah, your confusion comes from the fact that type conversion still
happens when the INSERT gets around to making the record.  The CHECK
constraint happens before the record is made.  See the vdbe that gets
generated.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Petite Abeille]

On Mar 6, 2013, at 10:43 PM, Petite Abeille  wrote:

>> CREATE TABLE toy(a INTEGER CHECK(a = CAST(a AS INTEGER)));
> 
> Hmmm… on second thought… is that an assignment in that check constraint?!? 
> I.e. are you reassigning a to a new cast value?!?
> 
> Are not check constraint suppose to be boolean expression? 
> 
> Dazed and confused.

Indeed. Never mind :)

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Nico Williams]

On Wed, Mar 6, 2013 at 3:43 PM, Petite Abeille  wrote:
> On Mar 6, 2013, at 10:24 PM, Nico Williams  wrote:
>> CREATE TABLE toy(a INTEGER CHECK(a = CAST(a AS INTEGER)));
>
> Hmmm… on second thought… is that an assignment in that check constraint?!? 
> I.e. are you reassigning a to a new cast value?!?

No.  The only place where = is an assignment is in UPDATE statements,
in the SET clause.

> Are not check constraint suppose to be boolean expression?

It is.  That's an equality operator.

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Petite Abeille]

On Mar 6, 2013, at 10:24 PM, Nico Williams  wrote:

> Nah, use this sort of CHECK constraint:
> 
> CREATE TABLE toy(a INTEGER CHECK(a = CAST(a AS INTEGER)));

Hmmm… on second thought… is that an assignment in that check constraint?!? I.e. 
are you reassigning a to a new cast value?!?

Are not check constraint suppose to be boolean expression? 

Dazed and confused.

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

Re: [sqlite] Bullzip ms Access to MySql

[Robert Hairgrove]

On Wed, 2013-03-06 at 21:36 +, ven...@intouchmi.com wrote:
> ENGINE=myisam DEFAULT CHARSET=utf8;

Try removing the bit in the above quote. This is MySQL-specific code.

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

[sqlite] Bullzip ms Access to MySql

[veneff]

Hi,

I have been trying to use Bullzip's Access to MySql converter to generate a SQL 
file that I can import into SQLite.
I'm using SQLite Manager under FireFox to import the resultant SQL file.
First, has anyone successfully done this.  Is there a better way.

A couple of my problems involve indexes:
The way Bullzip indicates Primary keys is different that what I see specified 
in the SQLite syntax definition.  And because of that it seems as if SQLite 
won't accept the AUTOINCREMENT (actually, AUTO INCREMENT was AUTO INCREMENT) 
constraint.

If a field is declared a Primary Key, is an index automatically built base on 
that field?

Here is an example of the create statement being generated:
CREATE TABLE `Condition_Result_Type` (
  `ConditionType_ID` INTEGER NOT NULL AUTOINCREMENT, 
  `ConditionType` VARCHAR(50), 
  `Description` VARCHAR(50), 
  PRIMARY KEY (`ConditionType_ID`)
) ENGINE=myisam DEFAULT CHARSET=utf8;

I can write a program to re-arange the Primary Key declaration to help correct 
this problem.

So, the bottom line is that I need to convert this create statement into one 
that SQLite will accept.

one more thing, is there a way to search through the archived forums?

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Nico Williams]

On Wed, Mar 6, 2013 at 3:29 PM, Petite Abeille  wrote:
> On Mar 6, 2013, at 10:24 PM, Nico Williams  wrote:
>> CREATE TABLE toy(a INTEGER CHECK(a = CAST(a AS INTEGER)));
>
> Any idea on the cost of such check? In term of overhead? Just curious.

Well, it's more than the cost of no check constraint.  Here's the
additional opcodes generated in my case:

5|SCopy|2|3|0||00|
6|ToInt|3|0|0||00|
7|Eq|3|9|2|collseq(BINARY)|6b|
8|Halt|19|2|0||00|

Of these the expensive one will be ToInt, though maybe SCopy too.
Dunno how expensive.

Note that the typeof() check constraint generates one more opcode:

5|Copy|2|4|0||00|
6|Function|0|4|3|typeof(1)|01|
7|String8|0|5|0|integer|00|
8|Eq|5|10|3||6a|
9|Halt|19|2|0||00|

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Petite Abeille]

On Mar 6, 2013, at 10:24 PM, Nico Williams  wrote:

> CREATE TABLE toy(a INTEGER CHECK(a = CAST(a AS INTEGER)));

Any idea on the cost of such check? In term of overhead? Just curious.

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Nico Williams]

On Wed, Mar 6, 2013 at 9:30 AM, Dominique Devienne  wrote:
> You don't have to use triggers, you can use a check constraint instead
> (simpler, but also perhaps faster as well?).
>
> If you do, you loose some of the implicit type conversions SQLite does,
> based on type affinity, so the "1" no longer gets converted to 1.

Nah, use this sort of CHECK constraint:

CREATE TABLE toy(a INTEGER CHECK(a = CAST(a AS INTEGER)));

sqlite> insert into toy values (1);
sqlite> insert into toy values ('1');
sqlite> select quote(a) from toy;
1
1
sqlite>

Cool, no?

> I also would prefer a strong-typing mode though (as an opt-in pragma for
> example), rather than adding a bunch of check constraints, and built-in
> strong-typing would likely be faster. But Dr. Hipp prefers dynamic typing,
> so dynamic typing it is :).

I too would like an option for stronger typing, but CHECK constraints
will do for now.

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

Re: [sqlite] How can I improve this query?

[Petite Abeille]

On Mar 5, 2013, at 10:41 PM, Yuzem  wrote:

> Hello, I have the following tables:
> CREATE TABLE movies (movies,name);
> CREATE TABLE genres (movies,genres);

IMDB? 

> 
> Every movie has many genres and every genre has many movies.
> I want to list all genres but those who match a specified movie must be
> marked.

You might be better off normalizing your data a bit…

Say… movie, genre, movie_genre… so…

selectmovie.title,
  genre.code
from  movie

join  movie_genre
onmovie_genre.movie_id = movie.id

join  genre
ongenre.id = movie_genre.genre_id

where movie.title = 'Ted (2012)'

order by  movie.title,
  genre.code

title|code
Ted (2012)|Comedy
Ted (2012)|Fantasy

Ok, now, given a movie, you can get its genre.

And now you can easily show all genres and the matching movie:

selectgenre.code,
  MovieGenre.title
from  genre

left join (
select  movie.title,
movie_genre.genre_id
frommovie

joinmovie_genre
on  movie_genre.movie_id = movie.id

where   movie.title = 'Ted (2012)'
  ) 
asMovieGenre
onMovieGenre.genre_id = genre.id

order by  genre.code


code|title

Action|
Adult|
Adventure|
Animation|
Biography|
Comedy|Ted (2012)
Crime|
Documentary|
Drama|
Experimental|
Family|
Fantasy|Ted (2012)
Film-Noir|
Game-Show|
History|
Horror|
Lifestyle|
Music|
Musical|
Mystery|
News|
Reality-TV|
Romance|
Sci-Fi|
Short|
Sport|
Talk-Show|
Thriller|
War|
Western|

Bottom line:  there are about 30 genres in IMDB, but about 1,495,677 movie → 
genre mappings. 

Take a look at database normalization:

http://en.wikipedia.org/wiki/Database_normalization


"Normalize until it hurts, denormalize until it works" -- anonymous

But never the other way round :)


Random Ted quote:  Show us how, Flash! 

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

Re: [sqlite] inner vs. outer join inconsistency

[Tom Matrix]

> I would agree that no warning is needed for for columns that don't state 
> any affinity, or for a non-affinity FK that refers to some PK with 
> affinity.
> 
> I tend to agree with OP that an explicitly text foreign key referring to 
> an explicitly int primary key is probably worth a warning (perhaps from 
> the hypothetical "lint mode" that surfaces now and then),

I agree. So it's basically very comfortable to have such a "lazy" type checking,
but a "lint mode" could help similar situations.

Thanks everyone for all the answers,
Tamás


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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Ryan Johnson]

On 06/03/2013 10:30 AM, Dominique Devienne wrote:

On Wed, Mar 6, 2013 at 3:29 PM, Ryan Johnson 
wrote:

Off topic, I'd love a way to request strong typing for a column (so that

attempts to store 'abc' into an int column would fail). You can emulate it
with a pair of before/update triggers (select raise(...) where
typeof(intcol)!='integer'), but that's clunky. Oh well... in retrospect,
most of the times I've been bitten by type mismatches were probably either
due to this bug or (more likely) due to my not specifying any affinity at
all and then being surprised when 1 != '1'.

You don't have to use triggers, you can use a check constraint instead
(simpler, but also perhaps faster as well?).

If you do, you loose some of the implicit type conversions SQLite does,
based on type affinity, so the "1" no longer gets converted to 1.
... which is why I prefer triggers. They kick in after the column's 
numeric affinity has a chance to convert "1" to 1.


Gives the best of both worlds, once you get past the boilerplate:

sqlite> create table t(x number);
sqlite> create trigger t1 before insert on t begin
   ...> select raise(FAIL, 'Numbers only!') where typeof(new.x) =='text';
   ...> end;
sqlite> insert into t values(1);
sqlite> insert into t values('1');
sqlite> insert into t values('a');
Error: Numbers only!
sqlite> select x,typeof(x) from t;
1|integer
1|integer

Ryan

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

Re: [sqlite] Sqlite .net 4.0 provider needed

[Mike King]

Use the official System.Data.Sqlite provider.
http://system.data.sqlite.org
Cheers,
Mike


On 6 March 2013 11:44, moumita  wrote:

> Hi,
>
> I want to user sqlite .net 4.0 provider. from where get that one? Please
> help me in this regards.
>
> Thanks,
> Moumita
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/Sqlite-net-4-0-provider-needed-tp67476.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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite .net 4.0 provider needed

[Mike King]

Use the official System.Data.Sqlite provider.

http://system.data.sqlite.org

Cheers,

Mike



On 6 March 2013 11:44, moumita  wrote:

> Hi,
>
> I want to user sqlite .net 4.0 provider. from where get that one? Please
> help me in this regards.
>
> Thanks,
> Moumita
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/Sqlite-net-4-0-provider-needed-tp67476.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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite .NET 4.0 provider available?

[Larry Brasfield]

moumita wrote, twice:
> I want to user sqlite .net 4.0 provider. from where get that one?
> Please help me in this regards.

See http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

Also, please quell the repeat postings.  Nobody is going to answer
faster, or decide to answer, just because you post redundantly.

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

Re: [sqlite] inner vs. outer join inconsistency

[Jim Morris]

I believe a check constraint with an appropriate typeof comparison has 
been suggested for this usage.


On 3/6/2013 6:29 AM, Ryan Johnson wrote:
I would agree that no warning is needed for for columns that don't 
state any affinity, or for a non-affinity FK that refers to some PK 
with affinity.


I tend to agree with OP that an explicitly text foreign key referring 
to an explicitly int primary key is probably worth a warning (perhaps 
from the hypothetical "lint mode" that surfaces now and then), since 
it's pretty likely that a user who took the trouble to specify 
affinities for both PK and FK probably made a mistake if the types are 
different. Sure, some record might override affinity and store 'abc' 
as its "int" primary key, but even if your app relies on that 
behavior, an "int" foreign key would be harmless for the same reason.


Off topic, I'd love a way to request strong typing for a column (so 
that attempts to store 'abc' into an int column would fail). You can 
emulate it with a pair of before/update triggers (select raise(...) 
where typeof(intcol)!='integer'), but that's clunky. Oh well... in 
retrospect, most of the times I've been bitten by type mismatches were 
probably either due to this bug or (more likely) due to my not 
specifying any affinity at all and then being surprised when 1 != '1'.


Ryan

On 06/03/2013 7:23 AM, Stephen Chrzanowski wrote:

SQLite doesn't care what kind of data type you are using, so, no, it
shouldn't throw an error.  The logic of this database engine is that you
will always be comparing apples to apples, regardless if one happens 
to be

orange.

On Wed, Mar 6, 2013 at 4:50 AM, Tom Matrix  wrote:


Richard Hipp  writes:


On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix  wrote:


I’ve encountered a problem, which is hardly reproducable on arbitrary
databases, therefore I attached one.

A simple, reproducible test case for (what we think is) your 
problem can

be

seen in this ticket:

  www.sqlite.org/src/tktview/fc7bd6358f59b

This bug has been latent in SQLite for almost four years and you 
are the
first to hit it.  Probably this is because not many applications 
contain
A=B in the WHERE clause where A is a text expression and B is an 
integer

expression.  You can probably work around the problem by changing your
schema so that entries.measurementid is an integer rather than text.

  This
does not excuse SQLite:  It is still getting the wrong answer and 
needs

to

be fixed.  We are working on a fix now.  But a simple change to your

schema

will work around the problem and get you going even before that fix is
available.
Very good example, thank you! It really solved my problem. 
Nevertheless, I

think
I don't have to mention that entries.measurementid must have been an
integer,
and this bug must be fixed in our schema, too.

My only remaining concern is, however: Should not SQLite give an 
error (or

at
least a warning) in cases where a foreign key constraint refers to a
different
data type?

Thanks again,
Tamás

___
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] SQLite3

[Dominique Devienne]

On Wed, Mar 6, 2013 at 3:34 PM,  wrote:
> > it would be wise if you use a tool like the SQLite Expert; that will
> > generate statements that you can use on command line as well.
>
> And a couple of other free tools are:
> SQLite Administrator - http://sqliteadmin.orbmu2k.de/
> SQLite Database Browser - http://sqlitebrowser.sourceforge.net/

SQLiteSpy for Windows is pretty good too:
http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index
(I switched from SQLite Database Browser. SQLiteSpy is faster, and
color-codes cells based on type. I use it to look at DBs mostly)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] hide sqlite database

[Clemens Ladisch]

dd wrote:
>Is there any flag to create sqlite database in hidden mode? (chmod,
> setfileattributes are os specific). I am looking for os independent.

There is no OS-independent way to hide files.
For that matter, several OSes cannot hide files at all.

What is your actual goal?


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

[sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Dominique Devienne]

On Wed, Mar 6, 2013 at 3:29 PM, Ryan Johnson 
wrote:
> Off topic, I'd love a way to request strong typing for a column (so that
attempts to store 'abc' into an int column would fail). You can emulate it
with a pair of before/update triggers (select raise(...) where
typeof(intcol)!='integer'), but that's clunky. Oh well... in retrospect,
most of the times I've been bitten by type mismatches were probably either
due to this bug or (more likely) due to my not specifying any affinity at
all and then being surprised when 1 != '1'.

You don't have to use triggers, you can use a check constraint instead
(simpler, but also perhaps faster as well?).

If you do, you loose some of the implicit type conversions SQLite does,
based on type affinity, so the "1" no longer gets converted to 1.

I also would prefer a strong-typing mode though (as an opt-in pragma for
example), rather than adding a bunch of check constraints, and built-in
strong-typing would likely be faster. But Dr. Hipp prefers dynamic typing,
so dynamic typing it is :).

--DD

sqlite> create table t (id number);
sqlite> insert into t values (1);
sqlite> insert into t values ("1");
sqlite> insert into t values ("1x");
sqlite> select id, typeof(id) from t;
1|integer
1|integer
1x|text

sqlite> create table tt (id number check (typeof(id) = 'integer'));
sqlite> insert into tt values (1);
sqlite> insert into tt values ("1");
Error: constraint failed
sqlite> insert into tt values ("1x");
Error: constraint failed
sqlite> select id, typeof(id) from tt;
1|integer

// recent SQLite versions also now report the name of the constraint that
failed, if available.
sqlite> create table ttt (id number, constraint id_is_integer check
(typeof(id) = 'integer'));
sqlite> insert into ttt values (1);
sqlite> insert into ttt values ("1");
Error: constraint id_is_integer failed
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3

[fred]

> it would be wise if you use a tool like the SQLite Expert; that will
> generate statements that you can use on command line as well.

And a couple of other free tools are:

SQLite Administrator - http://sqliteadmin.orbmu2k.de/

SQLite Database Browser - http://sqlitebrowser.sourceforge.net/

Fred

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

Re: [sqlite] inner vs. outer join inconsistency

[Simon Slavin]

On 6 Mar 2013, at 9:50am, Tom Matrix  wrote:

> My only remaining concern is, however: Should not SQLite give an error (or at
> least a warning) in cases where a foreign key constraint refers to a different
> data type?

I see why you asked but that won't work in SQLite.  Because SQLite doesn't have 
column types, it just has column affinities, which aren't enforced.  Each 
individual value in a column may have a different type.  You could define the 
column type as 'integer' but put the text 'overdrawn' into the value for one 
row.

You can see the rules that SQLite actually follows when comparing values of 
different types in section 3.3 of



Not that this would help you in the case reported because of the (now fixed) 
bug.

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

Re: [sqlite] inner vs. outer join inconsistency

[Ryan Johnson]

I would agree that no warning is needed for for columns that don't state 
any affinity, or for a non-affinity FK that refers to some PK with 
affinity.


I tend to agree with OP that an explicitly text foreign key referring to 
an explicitly int primary key is probably worth a warning (perhaps from 
the hypothetical "lint mode" that surfaces now and then), since it's 
pretty likely that a user who took the trouble to specify affinities for 
both PK and FK probably made a mistake if the types are different. Sure, 
some record might override affinity and store 'abc' as its "int" primary 
key, but even if your app relies on that behavior, an "int" foreign key 
would be harmless for the same reason.


Off topic, I'd love a way to request strong typing for a column (so that 
attempts to store 'abc' into an int column would fail). You can emulate 
it with a pair of before/update triggers (select raise(...) where 
typeof(intcol)!='integer'), but that's clunky. Oh well... in retrospect, 
most of the times I've been bitten by type mismatches were probably 
either due to this bug or (more likely) due to my not specifying any 
affinity at all and then being surprised when 1 != '1'.


Ryan

On 06/03/2013 7:23 AM, Stephen Chrzanowski wrote:

SQLite doesn't care what kind of data type you are using, so, no, it
shouldn't throw an error.  The logic of this database engine is that you
will always be comparing apples to apples, regardless if one happens to be
orange.

On Wed, Mar 6, 2013 at 4:50 AM, Tom Matrix  wrote:


Richard Hipp  writes:


On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix  wrote:


I’ve encountered a problem, which is hardly reproducable on arbitrary
databases, therefore I attached one.


A simple, reproducible test case for (what we think is) your problem can

be

seen in this ticket:

  www.sqlite.org/src/tktview/fc7bd6358f59b

This bug has been latent in SQLite for almost four years and you are the
first to hit it.  Probably this is because not many applications contain
A=B in the WHERE clause where A is a text expression and B is an integer
expression.  You can probably work around the problem by changing your
schema so that entries.measurementid is an integer rather than text.

  This

does not excuse SQLite:  It is still getting the wrong answer and needs

to

be fixed.  We are working on a fix now.  But a simple change to your

schema

will work around the problem and get you going even before that fix is
available.

Very good example, thank you! It really solved my problem. Nevertheless, I
think
I don't have to mention that entries.measurementid must have been an
integer,
and this bug must be fixed in our schema, too.

My only remaining concern is, however: Should not SQLite give an error (or
at
least a warning) in cases where a foreign key constraint refers to a
different
data type?

Thanks again,
Tamás

___
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] hide sqlite database

[dd]

Hi all,

   Is there any flag to create sqlite database in hidden mode? (chmod,
setfileattributes are os specific). I am looking for os independent. It
runs on 3 major desktop oss.

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

Re: [sqlite] inner vs. outer join inconsistency

[Ryan Johnson]

On 06/03/2013 4:50 AM, Tom Matrix wrote:

Richard Hipp  writes:


On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix  wrote:


I’ve encountered a problem, which is hardly reproducable on arbitrary
databases, therefore I attached one.


A simple, reproducible test case for (what we think is) your problem can be
seen in this ticket:

  www.sqlite.org/src/tktview/fc7bd6358f59b

This bug has been latent in SQLite for almost four years and you are the
first to hit it.  Probably this is because not many applications contain
A=B in the WHERE clause where A is a text expression and B is an integer
expression.  You can probably work around the problem by changing your
schema so that entries.measurementid is an integer rather than text.  This
does not excuse SQLite:  It is still getting the wrong answer and needs to
be fixed.  We are working on a fix now.  But a simple change to your schema
will work around the problem and get you going even before that fix is
available.

Very good example, thank you! It really solved my problem. Nevertheless, I think
I don't have to mention that entries.measurementid must have been an integer,
and this bug must be fixed in our schema, too.

My only remaining concern is, however: Should not SQLite give an error (or at
least a warning) in cases where a foreign key constraint refers to a different
data type?
My understanding is that it shouldn't have mattered, because the text 
column should have been converted to int before performing the 
comparison. This works for simpler queries where the non-int column is 
only compared with an int column. Simplifying the test case from the 
ticket above:


sqlite3> select * from t,i where textid=intid;
12|12
34|34

However, when comparing the non-int column with both an int column and a 
non-int one, the bug is that the promotion no longer happens and the 
join fails:


sqlite3> select * from t t1,t t2, i where t1.textid=i.intid and 
t1.textid=t2.textid;

(nothing)

Ryan

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

Re: [sqlite] How can I improve this query?

[Igor Tandetnik]

On 3/5/2013 4:41 PM, Yuzem wrote:

Hello, I have the following tables:
CREATE TABLE movies (movies,name);
CREATE TABLE genres (movies,genres);

Every movie has many genres and every genre has many movies.
I want to list all genres but those who match a specified movie must be
marked.

For example for movie "tt1637725":
SELECT distinct genres,movies from genres WHERE movies='tt1637725'
UNION ALL
SELECT distinct genres,'' FROM genres WHERE genres NOT IN (SELECT distinct
genres FROM genres WHERE movies='tt1637725') group by genres order by
genres;


Try this:

select genres, max(case movies when 'tt1637725' then movies else '' end)
from genres group by genres order by genres;

An index on genres(genres) would help.
--
Igor Tandetnik

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

[sqlite] cross compiling sqlite

[bkk]

Hi
 i have crosscompiled selite for arm board by below command from my ubuntu
machine
1. "./configure --host=arm CC=armv7l-timesys-linux-gnueabi-gcc
--disable-tcl"
2. make
3. make install DESTDIR=/usr/bk

and later put the contents from bk to the target
also i copied the source tree to target.

Can any one tel me , How to run the sqlite test suite on the target ?


Thank you 




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/cross-compiling-sqlite-tp67478.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

[sqlite] Sqlite .net 4.0 provider needed

[moumita]

Hi,

I want to user sqlite .net 4.0 provider. from where get that one? Please
help me in this regards.

Thanks,
Moumita



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Sqlite-net-4-0-provider-needed-tp67476.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] Sqlite .NET 4.0 provider available?

[moumita]

Hi,
I need Sqlite .net 4.0 provider...from where I get that? Please help in this
regards.

Thanks,
Moumita



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Sqlite-NET-4-0-provider-available-tp22459p67475.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

[sqlite] How can I improve this query?

[Yuzem]

Hello, I have the following tables:
CREATE TABLE movies (movies,name);
CREATE TABLE genres (movies,genres);

Every movie has many genres and every genre has many movies.
I want to list all genres but those who match a specified movie must be
marked.

For example for movie "tt1637725":
SELECT distinct genres,movies from genres WHERE movies='tt1637725'
UNION ALL
SELECT distinct genres,'' FROM genres WHERE genres NOT IN (SELECT distinct
genres FROM genres WHERE movies='tt1637725') group by genres order by
genres;

Action|
Adult|
Adventure|
Animation|
Biography|
Comedy|tt1637725
Crime|
Documentary|
Drama|
Family|
Fantasy|tt1637725
Film-Noir|
History|
Horror|
Music|
Musical|
Mystery|
Romance|
Sci-Fi|
Short|
Sport|
Thriller|
War|
Western|

Is there any way to improve that code?
Thanks in advance!



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/How-can-I-improve-this-query-tp67470.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] [sqlite-dev] SQLite 3.7.16 beta

[Bert Huijben]

Hi,

 

I compile sqlite with SQLITE_OMIT_UTF16 (and a few other omit flags) on
Windows and when testing with 3.7.16 I get:

 

sqlite3.lib(sqlite3.obj) : error LNK2019: unresolved external symbol
_sqlite3_result_text16le referenced in function _charFunc 

fatal error LNK1120: 1 unresolved externals

 

By just looking at the error message this appears to be related to the
introduction of the new char() function.

 

Bert

 

From: sqlite-dev-boun...@sqlite.org [mailto:sqlite-dev-boun...@sqlite.org]
On Behalf Of Richard Hipp
Sent: zaterdag 2 maart 2013 17:30
To: General Discussion of SQLite Database; sqlite-dev
Subject: [sqlite-dev] SQLite 3.7.16 beta

 

The anticipated release date for SQLite 3.7.16 is currently 2013-03-12.
Additional information about the forthcoming 3.7.16 release:

   (1)  http://www.sqlite.org/draft/releaselog/3_7_16.html
   (2)  http://www.sqlite.org/draft/download.html
   (3)  http://www.sqlite.org/checklists/3071600

See a summary of changes at (1).  Download a snapshot of the latest code (in
the form of an amalgamation "sqlite3.c" source file) from (2).  The status
board at (3) will show the current state of release testing.  When the
status board goes all green, we will cut the release.  The links above, and
indeed the entire http://www.sqlite.org/draft/ sub-website, will be updated
periodically between now and the official release so check back frequently.

Please report any problems encountered.

-- 
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] inner vs. outer join inconsistency

[Stephen Chrzanowski]

SQLite doesn't care what kind of data type you are using, so, no, it
shouldn't throw an error.  The logic of this database engine is that you
will always be comparing apples to apples, regardless if one happens to be
orange.

On Wed, Mar 6, 2013 at 4:50 AM, Tom Matrix  wrote:

> Richard Hipp  writes:
>
> >
> > On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix  wrote:
> >
> > >
> > > I’ve encountered a problem, which is hardly reproducable on arbitrary
> > > databases, therefore I attached one.
> > >
> >
> > A simple, reproducible test case for (what we think is) your problem can
> be
> > seen in this ticket:
> >
> >  www.sqlite.org/src/tktview/fc7bd6358f59b
> >
> > This bug has been latent in SQLite for almost four years and you are the
> > first to hit it.  Probably this is because not many applications contain
> > A=B in the WHERE clause where A is a text expression and B is an integer
> > expression.  You can probably work around the problem by changing your
> > schema so that entries.measurementid is an integer rather than text.
>  This
> > does not excuse SQLite:  It is still getting the wrong answer and needs
> to
> > be fixed.  We are working on a fix now.  But a simple change to your
> schema
> > will work around the problem and get you going even before that fix is
> > available.
>
> Very good example, thank you! It really solved my problem. Nevertheless, I
> think
> I don't have to mention that entries.measurementid must have been an
> integer,
> and this bug must be fixed in our schema, too.
>
> My only remaining concern is, however: Should not SQLite give an error (or
> at
> least a warning) in cases where a foreign key constraint refers to a
> different
> data type?
>
> Thanks again,
> Tamás
>
> ___
> 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] inner vs. outer join inconsistency

[Tom Matrix]

Richard Hipp  writes:

> 
> On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix  wrote:
> 
> >
> > The following query reports 18900080 rows (after some computation time):
> >
> 
> Is this the correct answer for the query below?

Yes, it seems to be correct.

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

Re: [sqlite] inner vs. outer join inconsistency

[Tom Matrix]

Richard Hipp  writes:

> 
> On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix  wrote:
> 
> >
> > I’ve encountered a problem, which is hardly reproducable on arbitrary
> > databases, therefore I attached one.
> >
> 
> A simple, reproducible test case for (what we think is) your problem can be
> seen in this ticket:
> 
>  www.sqlite.org/src/tktview/fc7bd6358f59b
> 
> This bug has been latent in SQLite for almost four years and you are the
> first to hit it.  Probably this is because not many applications contain
> A=B in the WHERE clause where A is a text expression and B is an integer
> expression.  You can probably work around the problem by changing your
> schema so that entries.measurementid is an integer rather than text.  This
> does not excuse SQLite:  It is still getting the wrong answer and needs to
> be fixed.  We are working on a fix now.  But a simple change to your schema
> will work around the problem and get you going even before that fix is
> available.

Very good example, thank you! It really solved my problem. Nevertheless, I think
I don't have to mention that entries.measurementid must have been an integer,
and this bug must be fixed in our schema, too.

My only remaining concern is, however: Should not SQLite give an error (or at
least a warning) in cases where a foreign key constraint refers to a different
data type?

Thanks again,
Tamás

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