Re: [sqlite] Compiling libtclsqlite3.so on Cygwin

2013-05-13 Thread Warren Young

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

2013-05-13 Thread Keith Christian
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

2013-05-13 Thread Nico Williams
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

2013-05-13 Thread Petite Abeille

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

2013-05-13 Thread Григорий Григоренко

Понедельник, 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

2013-05-13 Thread Michael Black
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

2013-05-13 Thread Paul van Helden
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

2013-05-13 Thread Paul van Helden
> 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

2013-05-13 Thread Simon Slavin

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

2013-05-13 Thread Michael Black
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

2013-05-13 Thread Simon Davies
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

2013-05-13 Thread Simon Slavin

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

2013-05-13 Thread Michael Black
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

2013-05-13 Thread Simon Slavin

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

2013-05-13 Thread Григорий Григоренко
 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

2013-05-13 Thread Paul van Helden
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


Re: [sqlite] SQLite and integer division 1/2=0

2013-05-13 Thread Darren Duncan

On 2013.05.12 11:42 AM, Simon Slavin wrote:

I think your problem is just that you have columns declared as NUMERIC.  You 
can have REAL behaviour if you want: just declare your columns as REAL instead:


I agree with this.  In principle, the behavior of addition should be tied to the 
data type or to the operator or both.  If you want integer behavior, declare 
INTEGER types, if you want real behavior, declare REAL types; declaring NUMERIC 
types is saying you don't care about the behavior.  That's the proper way to do 
this.  (Or have 2 operators, say "/" and "div", where the former can produce a 
fractional result while the latter guarantees a whole number result.)  The 
pragma is a bad idea. -- Darren Duncan


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