Re: [sqlite] Compiling libtclsqlite3.so on Cygwin
On 5/13/2013 16:36, Keith Christian wrote: gcc -o libtclsqlite3.so -shared tea/generic/tclsqlite3.c -lpthread -ldl -ltcl Don't build it that way. It appears that the TEA build system sees Cygwin and thinks "oh, this is Windows, so it must be VC++ or MinGW". That prevents it from linking to the Cygwin SQLite library. This seems to do the right thing: ... at the top-level of the source tree: $ cd tea $ ./configure --with-system-sqlite $ make Do a "make clean" before the last step if you have any *.o files laying around. The shipped build system isn't smart enough to rebuild everything when you reconfigure with a very different set of compile and link options. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Compiling libtclsqlite3.so on Cygwin
I have the latest version of Cygwin installed (CYGWIN_NT-6.1-WOW64 1.7.18(0.263/5/3) 2013-04-19 10:39 i686 Cygwin) and the latest autoconf archive (sqlite-autoconf-3071602.tar.gz) because it had the required tclsqlite3.c file in order to build the libtclsqlite3.so shared object. I ran the configure / make commands from the top level subdirectory (sqlite-autoconf-3071602.) Configure / make were successful. Here is the command line attempted for compiling tclsqlite3.c, and the results: gcc -o libtclsqlite3.so -shared tea/generic/tclsqlite3.c -lpthread -ldl -ltcl /tmp/ccnyhcg8.o:tclsqlite3.c:(.text+0x7fc2b): undefined reference to `__imp__Sqlite3_Init' /tmp/ccnyhcg8.o:tclsqlite3.c:(.text+0x7fc54): undefined reference to `__imp__Sqlite3_Init' /tmp/ccnyhcg8.o:tclsqlite3.c:(.text+0x7fc69): undefined reference to `__imp__Sqlite3_Init' /usr/lib/gcc/i686-pc-cygwin/4.5.3/../../../../i686-pc-cygwin/bin/ld: /tmp/ccnyhcg8.o: bad reloc address 0x100 in section `.data' collect2: ld returned 1 exit status I plan to use the libtclsqlite3.so object with the 3.7.16.2 version of Sqlite3 that ships with Cygwin (same version as here.) Any ideas of how to get tclsqlite3.c to compile and produce libtclsqlite3.so? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and integer division 1/2=0
On May 12, 2013 11:36 PM, "James K. Lowden" wrote: I'd add also that syntactically the key need is to distinguish "use floating point arithmetic" from "use integer arithmetic" where no other type information is available, specifically in numeric constant literals. Having a decimal part on numeric constant literals (even though 1.0 and so on are still integers, mathematically-speaking) traditionally serves this role. It's difficult to imagine a more compact and recognizable syntax than that! In some languages this is achieved by having a multiplicity of different operators for the same arithmetic operations, one set for integer arithmetic, another for dousing point, but this requires remembering more things. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in type conversion prior to comparison
On May 13, 2013, at 6:12 PM, Simon Slavin wrote: > I should have asked you for (1,2,20) as well and we could see whether it > outputs '10' or '10.0'. But yes, it would appear that in Oracle, NUMERIC > means FLOAT. Nah. Plus there is no such type as 'NUMERIC' per se in Oracle. Just NUMBER( precision, scale ) or… FLOAT( precision ). (ignoring binary types for simplicity's sake). http://docs.oracle.com/cd/E11882_01/server.112/e17118/sql_elements001.htm#sthref118 A so-called 'integer' in Oracle is simple a number defined as NUMBER(p,0). Th ANSI names such as NUMERIC(19,0), or DECIMAL(p,s), etc… are aliases for the relevant Oracle types, e.g. NUMBER(19,0). Anyhow… none of this helps SQLite much… :P ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign Key constraint problem while dropping tables inside transaction
Понедельник, 13 мая 2013, 17:03 +01:00 от Simon Davies : >On 13 May 2013 16:52, Simon Slavin < slav...@bigfraud.org > wrote: >> >> On 13 May 2013, at 3:54pm, Григорий Григоренко < grigore...@mail.ru > wrote: >> >>> sample database is: >>> >>> PRAGMA FOREIGN_KEYS=1; >>> CREATE TABLE cat(id INTEGER PRIMARY KEY, name); >>> INSERT INTO cat VALUES (1, 'Alice'); >>> CREATE TABLE owner(pet INTEGER REFERENCES cat(id)); >>> INSERT INTO owner VALUES(1); >>> >>> This script fails to drop tables with 'foreign key constraint failed': >>> >>> SAVEPOINT edit; >>> PRAGMA FOREIGN_KEYS=0; >>> DROP TABLE cat; >>> DROP TABLE owner; >>> RELEASE edit; >>> PRAGMA FOREIGN_KEYS=1; >> >> You are DROPping your tables in the wrong order. The 'owner' table refers >> to the 'cat' table. If you DROP 'cat' you would be left with an 'owner' >> table which refers to a table which doesn't exist. Swap the order of the >> DROPs and your database will not risk that kind of disaster. >> >>> This script works OK: >>> >>> PRAGMA FOREIGN_KEYS=0; >>> DROP TABLE cat; >>> DROP TABLE owner; >>> PRAGMA FOREIGN_KEYS=1; >> >> Your problem with the one with SAVEPOINT is that SQLite doesn't know when >> you're going to do the RELEASE. If you left out the second DROP, then >> issued a RELEASE, the database would be corrupt, so SQLite issues the error >> message to warn you about it. The version without the SAVEPOINT never has >> to worry about you doing that. >> >> Simon. > >http://www.sqlite.org/pragma.html#pragma_foreign_keys : > >"...foreign key constraint enforcement may only be enabled or disabled >when there is no pending BEGIN or SAVEPOINT. " Ups. My fault, must have read docs more carefully. Thank you. > >Regards, >Simon >___ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in type conversion prior to comparison
May just be showing best representation main() { float a=1,b=2,c=20; printf("%g\n",a/b*c); } Answer: 10 For c=21 Answer: 10.5 SQL> insert into numtypes values(1,2,20); 1 row created. SQL> select A/B*C from numtypes; A/B*C -- 12.5 13.5 11.5 10.5 10 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Monday, May 13, 2013 11:12 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Possible bug in type conversion prior to comparison On 13 May 2013, at 5:08pm, Michael Black wrote: > Would appear it's not doing any casting to promote values but just promoting > everything to float. I should have asked you for (1,2,20) as well and we could see whether it outputs '10' or '10.0'. But yes, it would appear that in Oracle, NUMERIC means FLOAT. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in type conversion prior to comparison
Actually, to be more accurate, the internal storage may be far from a float (as in IEEE double) but a divide on an integer-looking value will certainly be done with floating point math. On Mon, May 13, 2013 at 6:13 PM, Paul van Helden wrote: > > I should have asked you for (1,2,20) as well and we could see whether it >> outputs '10' or '10.0'. But yes, it would appear that in Oracle, NUMERIC >> means FLOAT. >> >> Of course it does! All the others too. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in type conversion prior to comparison
> I should have asked you for (1,2,20) as well and we could see whether it > outputs '10' or '10.0'. But yes, it would appear that in Oracle, NUMERIC > means FLOAT. > > Of course it does! All the others too. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in type conversion prior to comparison
On 13 May 2013, at 5:08pm, Michael Black wrote: > Would appear it's not doing any casting to promote values but just promoting > everything to float. I should have asked you for (1,2,20) as well and we could see whether it outputs '10' or '10.0'. But yes, it would appear that in Oracle, NUMERIC means FLOAT. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in type conversion prior to comparison
Added that... Would appear it's not doing any casting to promote values but just promoting everything to float. SQL> insert into numtypes values(1,2,21); 1 row created. SQL> select A/B*C from numtypes; A/B*C -- 12.5 13.5 11.5 10.5 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Monday, May 13, 2013 11:01 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Possible bug in type conversion prior to comparison On 13 May 2013, at 4:57pm, Michael Black wrote: > Oracle gives the right answer too for example(contrary to what somebody said > earlier). > > create table numtypes (A NUMERIC, B NUMERIC, C NUMERIC); > insert into numtypes values (1, 2, 25.23); > insert into numtypes values (1.0, 2, 27.17); > insert into numtypes values (1.1, 2, 22.92); > select A/B*C from numtypes; > > A/B*C > -- > 12.5 > 13.5 > 11.5 Please add to your INSERTs (1,2,21) and see whether Oracle is using integer arithmetic or not. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign Key constraint problem while dropping tables inside transaction
On 13 May 2013 16:52, Simon Slavin wrote: > > On 13 May 2013, at 3:54pm, Григорий Григоренко wrote: > >> sample database is: >> >> PRAGMA FOREIGN_KEYS=1; >> CREATE TABLE cat(id INTEGER PRIMARY KEY, name); >> INSERT INTO cat VALUES (1, 'Alice'); >> CREATE TABLE owner(pet INTEGER REFERENCES cat(id)); >> INSERT INTO owner VALUES(1); >> >> This script fails to drop tables with 'foreign key constraint failed': >> >> SAVEPOINT edit; >> PRAGMA FOREIGN_KEYS=0; >> DROP TABLE cat; >> DROP TABLE owner; >> RELEASE edit; >> PRAGMA FOREIGN_KEYS=1; > > You are DROPping your tables in the wrong order. The 'owner' table refers to > the 'cat' table. If you DROP 'cat' you would be left with an 'owner' table > which refers to a table which doesn't exist. Swap the order of the DROPs and > your database will not risk that kind of disaster. > >> This script works OK: >> >> PRAGMA FOREIGN_KEYS=0; >> DROP TABLE cat; >> DROP TABLE owner; >> PRAGMA FOREIGN_KEYS=1; > > Your problem with the one with SAVEPOINT is that SQLite doesn't know when > you're going to do the RELEASE. If you left out the second DROP, then issued > a RELEASE, the database would be corrupt, so SQLite issues the error message > to warn you about it. The version without the SAVEPOINT never has to worry > about you doing that. > > Simon. http://www.sqlite.org/pragma.html#pragma_foreign_keys: "...foreign key constraint enforcement may only be enabled or disabled when there is no pending BEGIN or SAVEPOINT. " Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in type conversion prior to comparison
On 13 May 2013, at 4:57pm, Michael Black wrote: > Oracle gives the right answer too for example(contrary to what somebody said > earlier). > > create table numtypes (A NUMERIC, B NUMERIC, C NUMERIC); > insert into numtypes values (1, 2, 25.23); > insert into numtypes values (1.0, 2, 27.17); > insert into numtypes values (1.1, 2, 22.92); > select A/B*C from numtypes; > > A/B*C > -- > 12.5 > 13.5 > 11.5 Please add to your INSERTs (1,2,21) and see whether Oracle is using integer arithmetic or not. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in type conversion prior to comparison
Seems to me the SQL standard makes no distinction between columns and literals does it? Why should literals be ignored? Oracle gives the right answer too for example(contrary to what somebody said earlier). create table numtypes (A NUMERIC, B NUMERIC, C NUMERIC); insert into numtypes values (1, 2, 25.23); insert into numtypes values (1.0, 2, 27.17); insert into numtypes values (1.1, 2, 22.92); select A/B*C from numtypes; A/B*C -- 12.5 13.5 11.5 SQL> desc numtypes; Name Null?Type - A NUMBER(38) B NUMBER(38) C NUMBER(38) -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Sunday, May 12, 2013 6:29 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Possible bug in type conversion prior to comparison On Sun, May 12, 2013 at 7:55 AM, Tomasz Pawlak < tomasz.paw...@cs.put.poznan.pl> wrote: > > So, type of '1' is 'text'. > > * If one operand has INTEGER, REAL or NUMERIC affinity and the other > operand as TEXT or NONE affinity then NUMERIC affinity is applied to other > operand. " > > So, if we compare 1 with '1' (e.g. 1='1'), '1' should be converted to > numeric, right? > No. '1' has type 'text' but it has no affinity at all. Likewise 1 has type 'integer' but no affinity. So no conversions take place, and the answer is FALSE. Affinity is only associated with table columns. Literals never have affinity. -- 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] Foreign Key constraint problem while dropping tables inside transaction
On 13 May 2013, at 3:54pm, Григорий Григоренко wrote: > sample database is: > > PRAGMA FOREIGN_KEYS=1; > CREATE TABLE cat(id INTEGER PRIMARY KEY, name); > INSERT INTO cat VALUES (1, 'Alice'); > CREATE TABLE owner(pet INTEGER REFERENCES cat(id)); > INSERT INTO owner VALUES(1); > > This script fails to drop tables with 'foreign key constraint failed': > > SAVEPOINT edit; > PRAGMA FOREIGN_KEYS=0; > DROP TABLE cat; > DROP TABLE owner; > RELEASE edit; > PRAGMA FOREIGN_KEYS=1; You are DROPping your tables in the wrong order. The 'owner' table refers to the 'cat' table. If you DROP 'cat' you would be left with an 'owner' table which refers to a table which doesn't exist. Swap the order of the DROPs and your database will not risk that kind of disaster. > This script works OK: > > PRAGMA FOREIGN_KEYS=0; > DROP TABLE cat; > DROP TABLE owner; > PRAGMA FOREIGN_KEYS=1; Your problem with the one with SAVEPOINT is that SQLite doesn't know when you're going to do the RELEASE. If you left out the second DROP, then issued a RELEASE, the database would be corrupt, so SQLite issues the error message to warn you about it. The version without the SAVEPOINT never has to worry about you doing that. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Foreign Key constraint problem while dropping tables inside transaction
Hi, sample database is: PRAGMA FOREIGN_KEYS=1; CREATE TABLE cat(id INTEGER PRIMARY KEY, name); INSERT INTO cat VALUES (1, 'Alice'); CREATE TABLE owner(pet INTEGER REFERENCES cat(id)); INSERT INTO owner VALUES(1); This script fails to drop tables with 'foreign key constraint failed': SAVEPOINT edit; PRAGMA FOREIGN_KEYS=0; DROP TABLE cat; DROP TABLE owner; RELEASE edit; PRAGMA FOREIGN_KEYS=1; This script works OK: PRAGMA FOREIGN_KEYS=0; DROP TABLE cat; DROP TABLE owner; PRAGMA FOREIGN_KEYS=1; Why? -- Григорий Григоренко ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and integer division 1/2=0
Tim, Simon & Darren, if you read my whole OP you will see that I've discovered this: use REAL instead. My point is that the behaviour of a NUMERIC column is not intuitive and gives mixed results which wouldn't be a problem if the division operator could be modified. My suggestion cannot be too outlandish if MySQL does it "my way". Simon says: "The PRAGMAs allow SQLite to switch between different behaviours when the standard doesn't say what should happen". I would venture to say perhaps the standard wasn't too clear on this, or at the very least the fact that MySQL does it differently means there is a bit of a smudge on this part. Darren says: "declaring NUMERIC types is saying you don't care about the behavior". I do care about behaviour, so I'll change my management system to exclude NUMERIC as an option since I have no use for it then! I cannot expect my clients to know little quirks to this level of detail. I agree with what Darren says about the option of having 2 operators, / and div, that's what MySQL does and it is also a feature of Pascal and other languages. Please don't get me wrong. I haven't used MySQL for new projects in years, so I'm not promoting it in any way. Also, if NUMERIC wasn't so ubiquitous in the SQL world, I wouldn't even have raised the issue. If I am correct in taking away from this discussion "don't use NUMERIC column definitions if you want to do any calculations [with divisions] on them", then we can let it rest now. I'll dream of seeing NUMERIC(p,s) one day that enforces (p,s) (and doesn't do integer division unless s=0 !) :-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users