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

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

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

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

Re: [sqlite] Bullzip ms Access to MySql

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

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

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

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

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

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

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 >

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

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

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

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

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

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

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

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

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

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

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

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

Re: [sqlite] Bullzip ms Access to MySql

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

[sqlite] Bullzip ms Access to MySql

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

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

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.

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

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

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

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

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

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

Re: [sqlite] inner vs. outer join inconsistency

> 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

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

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

Re: [sqlite] Sqlite .net 4.0 provider needed

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

Re: [sqlite] Sqlite .net 4.0 provider needed

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

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

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,

Re: [sqlite] inner vs. outer join inconsistency

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.

Re: [sqlite] SQLite3

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

Re: [sqlite] hide sqlite database

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,

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

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

Re: [sqlite] SQLite3

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

Re: [sqlite] inner vs. outer join inconsistency

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.

Re: [sqlite] inner vs. outer join inconsistency

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

[sqlite] hide sqlite database

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

Re: [sqlite] inner vs. outer join inconsistency

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

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

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

[sqlite] cross compiling sqlite

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.

[sqlite] Sqlite .net 4.0 provider needed

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.

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

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] How can I improve this query?

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

Re: [sqlite] [sqlite-dev] SQLite 3.7.16 beta

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

Re: [sqlite] inner vs. outer join inconsistency

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:

Re: [sqlite] inner vs. outer join inconsistency

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.

Re: [sqlite] inner vs. outer join inconsistency

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