RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
DB2 gives this: CREATE TABLE t1(a INTEGER, b REAL) INSERT INTO t1 VALUES(5,5) SELECT a/2, b/2 FROM t1 1 2 --- 2 +2.50E+000 1 record(s) selected. -Original Message- From: Rob Lohman [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 02, 2005 11:07 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 A quick test here on MSSQL Oracle: Microsoft SQL 2000 SQL 2005 (beta): create table MATHTEST ( CINT int null, CDEC decimal null, CDPREC double precision null, CFLOAT floatnull, CNUM numeric null, CREALreal null ) go insert into MATHTEST (CINT, CDEC, CDPREC, CFLOAT, CNUM, CREAL) values (5, 5, 5, 5, 5, 5); go select CINT/2, CDEC/2, CDPREC/2, CFLOAT/2, CNUM/2, CREAL/2 from MATHTEST go 2 2.50 2.5 2.5 2.50 2.5 - Oracle 8i2: create table MATHTEST ( CDEC DEC, CDECIMAL DECIMAL, CDPREC DOUBLE PRECISION, CFLOAT FLOAT, CINT INT, CNUM NUMBER, CREALREAL ); insert into MATHTEST (CDEC, CDECIMAL, CDPREC, CFLOAT, CINT, CNUM, CREAL) values (5, 5, 5, 5, 5, 5, 5); select CDEC/2, CDECIMAL/2, CDPREC/2, CFLOAT/2, CINT/2, CNUM/2, CREAL/2 from MATHTEST; CDEC/2 CDECIMAL/2 CDPREC/2 CFLOAT/2 CINT/2 CNUM/2 CREAL/2 -- -- -- -- -- -- -- 2,52,52,52,52,52,5 2,5 So it seems these two have different opinions on this as well. Rob - Original Message - From: [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Wednesday, November 02, 2005 4:36 PM Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 Consider the following SQL: CREATE TABLE t1(a INTEGER, b REAL); INSERT INTO t1 VALUES(5,5); SELECT a/2, b/2 FROM t1; From the above SQL, SQLite version 3.2.7 and earlier will return 2|2 If my proposed changes for 3.3.0 go in, then the result will be: 2.5|2.5 If I understand what most people are saying, the SQL standard says that the result should be: 2|2.5 Does this correctly summarize the situation? Do other SQL database engines consistently return the 3rd case? Am I alone in thinking that a division operator that does different things depending on the declared datatype of a column is an abomination? Does anybody have a real-world example where any of this will actually make a difference, or is this really just an academic argument? -- D. Richard Hipp [EMAIL PROTECTED]
RE: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
There is clearly no 1 correct answer. So instead of arguing the point over and over, why don't the people who object simply apply the proposed change and report back what issues your application has? Let's see how many people are actually using this functionality, what breaks and weigh the decision on that. -Original Message- From: Clark Christensen [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 02, 2005 1:19 PM To: SQLite List Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 Was this intended as part of the division discussion? If so, the original statement was: Am I alone in thinking that a division operator that does different things depending on the declared datatype of a column is an abomination? Yes. It was in answer to (paraphrased), what results do other DBs return for the SQL under consideration? Looks like I forgot to include the question with the answer. Sorry about that :-) -Clark - Original Message From: Jay Sprenkle [EMAIL PROTECTED] To: sqlite-users@sqlite.org; Clark Christensen [EMAIL PROTECTED] Sent: Wednesday, November 02, 2005 9:29:13 AM Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 On 11/2/05, Clark Christensen [EMAIL PROTECTED] wrote: - Original Message From: [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Wednesday, November 02, 2005 07:36:58 Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 Consider the following SQL: CREATE TABLE t1(a INTEGER, b REAL); INSERT INTO t1 VALUES(5,5); SELECT a/2, b/2 FROM t1; D. Richard Hipp [EMAIL PROTECTED] --- Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production SQL create table t1 (a integer, b real); Table created. SQL insert into t1 values (5,5); 1 row created. SQL select a/2, b/2 from t1; A/2B/2 -- -- 2.52.5 Was this intended as part of the division discussion? If so, the original statement was: Am I alone in thinking that a division operator that does different things depending on the declared datatype of a column is an abomination? This shows that the engine does do different things based on the data type. It changed the result type so it would not lose precision. If you require all divisions to be done the same way then you must have only one numerical type. Floating point.
RE: [sqlite] My first post, a few wishes..
I took it to mean that he wants: SELECT sql FROM sqlite_master WHERE type='table'; But I could be wrong. -Original Message- From: Kiel W. [mailto:[EMAIL PROTECTED] Sent: Thursday, July 07, 2005 8:52 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] My first post, a few wishes.. On 7/7/05, Edwin Knoppert [EMAIL PROTECTED] wrote: 1) I wish for a better way to recreate a table's definition. The FAQ example is no good if you are unknown to it's fields. For example, imy dbms obtains the create table query for the user, he might want to reset table name as add or remove fields. After that the copy as suggested in the FAQ does not know what fields to use. SELECT name FROM sqlite_master WHERE type='table' ORDER BY name; Is this what you are after? This is (9) of the FAQ. See also #2 2) Empty table does not return fields. After i create a table + fields, there seems to be no way to enumerate the fieldnames if there isn't any data inserted. Both cols and rows return 0. I'm using sqlite3_get_table() I heard before to make use of prepare but i can't figure out how. 3) May i suggest a better (easier) forum for sqlite? I can setup a forum independant of my own sites. No commercials etc.. -- Kiel W. [EMAIL PROTECTED] -- time is swift
RE: [sqlite] Insert all rows from old table into new table but in sorted order
The solution is to always specify an order when you want to return data in a particular order. SQL standard does not specify that rows come back in a particular order unless you specify. Order is never assumed as to enhance speed - especially for functions where order is irrelevant like totaling columns. If you're familiar with how hashes are stored in C++ or Perl (and others I'm not familiar with), it's the same situation. If you don't pass it through a sort function, the interpreter decides how to return the values in the most efficient way which may not be the way they were originally inserted. _brad -Original Message- From: Ajay [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 8:21 AM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order Yaa that's what I wanted to do , So what do you think what could be the solution for this ? -Original Message- From: Steve O'Hara [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 5:28 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order I might be wrong, but if you don't specify a sort column, you will get the rows out in PRIMARY KEY order, irrespective of how you loaded the data. Therefore, you will need to do something a little more interesting with your loading statement to perhaps exclude the primary key and let the insert re-generate them. Just a thought. Steve -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] rg]On Behalf Of Paul Smith Sent: 30 June 2005 12:23 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Insert all rows from old table into new table but in sorted order I can insert all rows of existing table into new table having same columns using query : Insert into NEWTABLE select * from OLDTABLE But I want all rows of NEWTABLE sorted by field No, So I used query Insert into NEWTABLE select * from OLDTABLE order by no desc But it is not giving me sorted output as new table? Can you tell me where I am wrong ??? You can't do that. The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in the right order , but then, when you do an unordered query on 'NEWTABLE', the results are returned in an undefined order - not necessarily in the order they were inserted into the table You should do the sorting when you read 'NEWTABLE' So, instead of Insert into NEWTABLE select * from OLDTABLE order by no desc select * from NEWTABLE do Insert into NEWTABLE select * from OLDTABLE select * from NEWTABLE order by no desc PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
RE: [sqlite] Insert all rows from old table into new table but in sorted order
My apologies for being long-winded. Basically the answer is to not insert in a particular order and do your order by when you recall the data from NEWTABLE. Insert the data: Insert into NEWTABLE select * from OLDTABLE Then to get the data back in the order you want: select * from NEWTABLE order by no desc _brad -Original Message- From: Ajay [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 9:38 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order Seems to be top of my head, Is there any simple and sweet solution ? -Original Message- From: Brad DerManouelian [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 6:36 PM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order The solution is to always specify an order when you want to return data in a particular order. SQL standard does not specify that rows come back in a particular order unless you specify. Order is never assumed as to enhance speed - especially for functions where order is irrelevant like totaling columns. If you're familiar with how hashes are stored in C++ or Perl (and others I'm not familiar with), it's the same situation. If you don't pass it through a sort function, the interpreter decides how to return the values in the most efficient way which may not be the way they were originally inserted. _brad -Original Message- From: Ajay [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 8:21 AM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order Yaa that's what I wanted to do , So what do you think what could be the solution for this ? -Original Message- From: Steve O'Hara [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 5:28 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order I might be wrong, but if you don't specify a sort column, you will get the rows out in PRIMARY KEY order, irrespective of how you loaded the data. Therefore, you will need to do something a little more interesting with your loading statement to perhaps exclude the primary key and let the insert re-generate them. Just a thought. Steve -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] rg]On Behalf Of Paul Smith Sent: 30 June 2005 12:23 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Insert all rows from old table into new table but in sorted order I can insert all rows of existing table into new table having same columns using query : Insert into NEWTABLE select * from OLDTABLE But I want all rows of NEWTABLE sorted by field No, So I used query Insert into NEWTABLE select * from OLDTABLE order by no desc But it is not giving me sorted output as new table? Can you tell me where I am wrong ??? You can't do that. The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in the right order , but then, when you do an unordered query on 'NEWTABLE', the results are returned in an undefined order - not necessarily in the order they were inserted into the table You should do the sorting when you read 'NEWTABLE' So, instead of Insert into NEWTABLE select * from OLDTABLE order by no desc select * from NEWTABLE do Insert into NEWTABLE select * from OLDTABLE select * from NEWTABLE order by no desc PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
RE: [sqlite] SQLite on Tiger.
I've been using Tiger since Friday night and feel the need to comment on how fast the indexing/searching is. It took about an hour to index my drive (100,000+ files on a PB G4, GREAT speed for a relatively slow drive, I thought) and I can return 86,920 files in about 45 seconds. I'll never need to remember what I named or where I put a file/email thanks to Tiger's SQLite integration. -Original Message- From: Bill Bumgarner [mailto:[EMAIL PROTECTED] Sent: Friday, April 29, 2005 7:06 PM To: sqlite-users@sqlite.org Subject: [sqlite] SQLite on Tiger. So, now the cat is out of the bag, so to speak. Tiger ships with SQLite 3.1.3 + a couple of tweaks. Specifically, the SQLite3 on Tiger supports locking on network filesystems, including AFP and Samba. Tiger uses SQLite3 in a number of roles. It is a backing store to Core Data -- the new object persistence object graph management solution in Tiger -- and is also used by a number of applications on the system to provide better searching/indexing capabilities and as a primary data store. Personally, I would like to extend a very hearty thank you to Richard and all SQLite contributors. Working with SQLite has been a joy and Tiger benefits greatly from the addition of this wonderful tool! b.bum
RE: [sqlite] 50MB Size Limit?
Mail system likely has a quota. Check this link: http://www.webservertalk.com/archive280-2004-6-280358.html -Original Message- From: Jonathan Zdziarski [mailto:[EMAIL PROTECTED] Sent: Monday, April 11, 2005 12:27 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] 50MB Size Limit? G. Roderick Singleton wrote: quotas? That crossed my mind, but all of these databases are being stored in system space (/usr/local/var/dspam) and owned by the mail system.
[sqlite] DBD::SQLite not closing file handles
Sorry if this is the wrong forum, but has anyone run into a problem with
leaking file handles when using SQLite with Perl DBI and DBD::SQLite?
I am calling finish() and disconnect() when I'm done with each
connection, but lsof reports my database file opens once for each
connection and never closes. Curious if it's an issue with SQLite, DBI
or the DBD::SQLite driver itself.
Sample code to print every row from every table:
#!/usr/bin/perl -w
use strict;
use DBI;
my ( $query, $table_name, $sth, $sth2, $dbh, $dbh2, @rows, @rows2,
$count );
my $database_name = /home/brad/test.db;
$query = SELECT name FROM sqlite_master WHERE type='table' ORDER BY
name;
$dbh = DBI-connect( DBI:SQLite:dbname=$database_name,,,
{ PrintError = 1 } ) or die Cannot
connect to SQLite database $database_name;
$sth = $dbh-prepare( $query );
$sth-execute();
while ( @rows = $sth-fetchrow() )
{
foreach $table_name ( @rows )
{
$count++;
print $count$table_name\n;
$query = select * from $table_name;
$dbh2 = DBI-connect( DBI:SQLite:dbname=$database_name,,,
{ PrintError = 1 } ) or die
Cannot connect to SQLite database $database_name;
$sth2 = $dbh-prepare( $query );
$sth2-execute();
while ( @rows2 = $sth2-fetchrow() )
{
foreach my $row_contents ( @rows2 )
{
print \t$row_contents;
}
print \n;
}
$sth2-finish();
$dbh2-disconnect();
}
}
$sth-finish();
$dbh-disconnect();
## End of code
$count tells me it dies at the 1021st table with Can't locate
Carp/Heavy.pm in @INC error.
Using RedHat AS2, Perl 5.8.0, latest SQLite, DBI and SQLite as of 2
weeks ago.
This code works when connecting to MySQL, Oracle, DB2 and MS SQL Server
via ODBC which makes me suspect SQLite or DBD::SQLite.
Thanks for any help!
-Brad
RE: [sqlite] DBD::SQLite not closing file handles
Sorry.. I actually had a typo in that sample, but even with it fixed I
get the same results (just runs slower). New code with fixed typo:
#!/usr/bin/perl -w
use strict;
use DBI;
my ( $query, $table_name, $sth, $sth2, $dbh, $dbh2, @rows, @rows2,
$count ); my $database_name = /home/brad/test.db; $query = SELECT
name FROM sqlite_master WHERE type='table' ORDER BY name; $dbh =
DBI-connect( DBI:SQLite:dbname=$database_name,,,
{ PrintError = 1 } ) or die Cannot
connect to SQLite database $database_name; $sth = $dbh-prepare( $query
); $sth-execute();
while ( @rows = $sth-fetchrow() )
{
foreach $table_name ( @rows )
{
$count++;
print $count$table_name\n;
$query = select * from $table_name;
$dbh2 = DBI-connect( DBI:SQLite:dbname=$database_name,,,
{ PrintError = 1 } ) or die
Cannot connect to SQLite database $database_name;
$sth2 = $dbh2-prepare( $query ); #Typo was here
$sth2-execute();
while ( @rows2 = $sth2-fetchrow() )
{
foreach my $row_contents ( @rows2 )
{
print \t$row_contents;
}
print \n;
}
$sth2-finish();
$dbh2-disconnect();
}
}
$sth-finish();
$dbh-disconnect();
## End of code
Brad DerManouelian
Sane Solutions, LLC
Phone: 401-295-4809 x122
http://www.sane.com
This e-mail message may contain confidential information. If you are not
the intended recipient, any use, dissemination, distribution or copying
of this e-mail message is strictly prohibited. If you have received this
message in error, please immediately notify the sender and delete this
e-mail message from your computer.
-Original Message-
From: Brad DerManouelian [mailto:[EMAIL PROTECTED]
Sent: Monday, March 28, 2005 12:24 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] DBD::SQLite not closing file handles
Sorry if this is the wrong forum, but has anyone run into a problem with
leaking file handles when using SQLite with Perl DBI and DBD::SQLite?
I am calling finish() and disconnect() when I'm done with each
connection, but lsof reports my database file opens once for each
connection and never closes. Curious if it's an issue with SQLite, DBI
or the DBD::SQLite driver itself.
Sample code to print every row from every table:
#!/usr/bin/perl -w
use strict;
use DBI;
my ( $query, $table_name, $sth, $sth2, $dbh, $dbh2, @rows, @rows2,
$count ); my $database_name = /home/brad/test.db; $query = SELECT
name FROM sqlite_master WHERE type='table' ORDER BY name; $dbh =
DBI-connect( DBI:SQLite:dbname=$database_name,,,
{ PrintError = 1 } ) or die Cannot
connect to SQLite database $database_name; $sth = $dbh-prepare( $query
); $sth-execute();
while ( @rows = $sth-fetchrow() )
{
foreach $table_name ( @rows )
{
$count++;
print $count$table_name\n;
$query = select * from $table_name;
$dbh2 = DBI-connect( DBI:SQLite:dbname=$database_name,,,
{ PrintError = 1 } ) or die
Cannot connect to SQLite database $database_name;
$sth2 = $dbh-prepare( $query );
$sth2-execute();
while ( @rows2 = $sth2-fetchrow() )
{
foreach my $row_contents ( @rows2 )
{
print \t$row_contents;
}
print \n;
}
$sth2-finish();
$dbh2-disconnect();
}
}
$sth-finish();
$dbh-disconnect();
## End of code
$count tells me it dies at the 1021st table with Can't locate
Carp/Heavy.pm in @INC error.
Using RedHat AS2, Perl 5.8.0, latest SQLite, DBI and SQLite as of 2
weeks ago.
This code works when connecting to MySQL, Oracle, DB2 and MS SQL Server
via ODBC which makes me suspect SQLite or DBD::SQLite.
Thanks for any help!
-Brad
