Re: [sqlite] Select statements returned column names

[Gerard Samuel]

On Wednesday 18 February 2004 12:19 pm, Fred Williams wrote:
> Just did some testing on the "other" databases I have available.  This
> "feature" of SQLite does seem to be unique.  I am early in the learning
> curve on SQLite, and had not found this particular "enhancement" as yet.
> Nice to know, and avoid!
>
> Looks like to me it is time for a new PRAGMA!
>
> I am not well versed on the SQL '9x specifications, so don't know if SQLite
> is breaking spec's or just breaking "convention."  Either way I think it
> deserves consideration, as it directly impacts cross platform (i.e.
> database engines) environments.  Something I am painfully familiar with. 
> Just never got burned by this particular situation in the past, so was
> blissfully unaware of the pending potential "all nighters," BUMMER!
>
> Fred
>

In my journey with databases, (mysql, postgresql, sql 2000, ibm db2, and 
sqlite), sqlite is the only one that behaves like this.
IMHO, its not a "feature" but more of a "catch all" regardless of whether the 
results contain same column names.
Like using an axe to split match sticks!!
Lets hope it gets reverted to how it was, or some kind of switch is 
introduced...


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] Assertion in pager.c

[Parker, Ron]

I wasn't exactly sure of how to classify this to enter it as an official
bug.  Also I using a combination of software that I am certain is out of the
ordinary.  First I'll discuss the assertion, then my environment.

What I am seeing is the following message:

assertion "!pPager->noSync" failed: file "src/pager.c", line 1168

This is on SQLite version 2.8.12.  I am not quite sure what might be causing
this or where to begin looking for a solution.

My situation is that I am running cscvs,
http://wiki.gnuarch.org/moin.cgi/cscvs, in an attempt to convert a CVS
repository into a tla archive.  Specifically I am running "cscvs cache -b",
which basically reads the output of "cvs -q log" and generates an SQLite
database with information about the archive.  IIRC this portion of cscvs is
basically a clone of ViewCVS.

Now what is exceptional about my situation is that I am doing this on
Windows 2000 under Cygwin.  I have compiled, tested and installed SQLite
with the default settings.  After a minor patch that I submitted and
dougcourie committed yesterday, all of the tests run without error.  This
gives me an SQLite that uses Microsoft-style paths.  I tried changing the
compile time flags to use "-DOS_UNIX=1 -DOS_WIN=0", since I was running
under Cygwin, but this failed horribly from the start, so I switched back to
the default version that produces a somewhat hybrid result.  I call it a
hybrid, because while it is compiled with Cygwin, which is a POSIX-style
environment, it still uses Microsoft-style paths.

The cscvs program is written in Python, so obviously pysqlite is being used
as well.  I made a one change to pysqlite so that it can be run with the
Cygwin version of Python using Unix style paths.  The patch below converts
the Unix-style paths to their Win32 equivalents before calling SQLite.

diff -ru pysqlite.orig/_sqlite.c pysqlite/_sqlite.c
--- pysqlite.orig/_sqlite.c 2003-12-02 07:45:06.0 -0600
+++ pysqlite/_sqlite.c  2004-02-17 12:11:48.721687500 -0600
@@ -31,6 +31,11 @@
 
 #include "port/strsep.h"
 
+#ifdef __CYGWIN__
+#include 
+#include 
+#endif
+
 /* Compatibility macros
  *
  * From Python 2.2 to 2.3, the way to export the module init function
@@ -252,7 +257,15 @@
 }
 
 /* Open the database */
+#ifdef __CYGWIN__
+{
+   char win_path[MAX_PATH];
+   cygwin_conv_to_win32_path(db_name, win_path);
+   obj->p_db = sqlite_open(win_path, mode, );
+}
+#else
 obj->p_db = sqlite_open(db_name, mode, );
+#endif
 
 if(obj->p_db == 0 || errmsg != NULL)
 {

My Python is:
Python 2.3.3 (#1, Dec 30 2003, 08:29:25)
[GCC 3.3.1 (cygming special)] on cygwin

So far I have made no changes to cscvs itself.  It is coming from the
[EMAIL PROTECTED]/cscvs--experimental--1.1 archive.

I'm willing to do some foot work myself to help resolve this, but thought
someone might be able to at least point me in a general direction, since I
know so little about the SQLite code.

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] Select statements returned column names

[Fred Williams]

Just did some testing on the "other" databases I have available.  This
"feature" of SQLite does seem to be unique.  I am early in the learning
curve on SQLite, and had not found this particular "enhancement" as yet.
Nice to know, and avoid!

Looks like to me it is time for a new PRAGMA!

I am not well versed on the SQL '9x specifications, so don't know if SQLite
is breaking spec's or just breaking "convention."  Either way I think it
deserves consideration, as it directly impacts cross platform (i.e. database
engines) environments.  Something I am painfully familiar with.  Just never
got burned by this particular situation in the past, so was blissfully
unaware of the pending potential "all nighters," BUMMER!

Fred

-Original Message-
From: Gerard Samuel [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 18, 2004 10:47 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: [sqlite] Select statements returned column names


On Wednesday 18 February 2004 11:23 am, Gerard Samuel wrote:
> On Wednesday 18 February 2004 10:58 am, Ilia Alshanetsky wrote:
> > Yes, this is the correct behavior, without the table 'prefix' if you
have
> > common column names in the result set the data would lost.
>
> True about common column names.  But isn't that where aliases come in???
> E.g.  select a.foo as foo1, b.foo as foo2
>

Another question.  Can this behaviour be turned off, reverting sqlite,
making
it more compatible to other databases such as mysql, pgsql, mssql, etc

...


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Select statements returned column names

[Gerard Samuel]

On Wednesday 18 February 2004 11:23 am, Gerard Samuel wrote:
> On Wednesday 18 February 2004 10:58 am, Ilia Alshanetsky wrote:
> > Yes, this is the correct behavior, without the table 'prefix' if you have
> > common column names in the result set the data would lost.
>
> True about common column names.  But isn't that where aliases come in???
> E.g.  select a.foo as foo1, b.foo as foo2
>

Another question.  Can this behaviour be turned off, reverting sqlite, making 
it more compatible to other databases such as mysql, pgsql, mssql, etc

Thanks


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Select statements returned column names

[Gerard Samuel]

On Wednesday 18 February 2004 10:58 am, Ilia Alshanetsky wrote:
> Yes, this is the correct behavior, without the table 'prefix' if you have
> common column names in the result set the data would lost.
>

True about common column names.  But isn't that where aliases come in???
E.g.  select a.foo as foo1, b.foo as foo2

The last time I used sqlite (2.8.3), my query below used to work.  I don't 
understand why the decision was made to change it, to something that is 
incompatible to sql statements from mysql, postgresql, mssql, ibm db2 (the 
ones Im using so far).

While its true that I can do ->
SELECT f.id as id, f.foo as foo FROM table f;

OR

remove the table 'prefix' from the returned column names via php,
to overcome this, but to me that sucks because sqlite takes away the choice 
from the sql author, on whether aliases should be used on a select statement.
Is there a better reason for this sqlite behaviour, as your explanation, 
doesn't really justify (at least in my mind) why sqlite is like this now

Thanks for your input


> On February 18, 2004 10:43 am, Gerard Samuel wrote:
> > Im currently interfacing with sqlite via php 4.3.5-RC2.
> > The latest version of sqlite, distributed by php snaps
> > (http://snaps.php.net/win32/PECL_STABLE/) is 2.8.11.
> > Im currently experiencing behaviour with sqlite that I didn't experience
> > when I was using version 2.8.3 several months ago.
> > If I have a table like
> > id  foo
> > 1   hello
> > 2   world
> >
> > If I execute an sql select like ->
> > SELECT f.id, f.foo FROM table f;
> > The returned data is ->
> > f.idf.foo
> > 1   hello
> > 2   world
> >
> > Instead of the normal (as in other DBs I've used)
> > id  foo
> > 1   hello
> > 2   world
> >
> > Is this the correct/expected behaviour of sqlite?
> > Thanks for any input you may provide...


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] again subselect-problems

[Tim Krah]

Hi,

again I've figured out an inconsistency with subselects (and again I think it's 
sqlite's fault...   ;)


SQLite version 2.8.12
Enter ".help" for instructions
sqlite> .echo on
sqlite> .read test.sql
.read test.sql
DROP VIEW t1view;
DROP VIEW t1view2;
DROP TABLE t1;
CREATE TABLE t1 (
id INTEGER PRIMARY KEY,
name VARCHAR(10),
date VARCHAR(10),
info VARCHAR(20)
);

INSERT INTO t1 VALUES ( NULL, 'first', '2004-01-20', 'first''s first info' );
INSERT INTO t1 VALUES ( NULL, 'second', '2004-02-12', 'second''s first info' );
INSERT INTO t1 VALUES ( NULL, 'first', '2004-02-14' , 'first''s second info' );

-- show me the most current entries per name
CREATE VIEW t1view AS
SELECT  b.*
FROM (
SELECT  name,
max(date) AS date
FROM t1
GROUP BY name
) AS a
LEFT JOIN t1 AS b
ON (a.name=b.name AND a.date=b.date)
;

-- SELECT works ok:
SELECT * FROM t1view;
id  namedateinfo
--  --  --  ---
2   second  2004-02-12  second's first info
3   first   2004-02-14  first's second info

-- SELECT WHERE is wrong:
SELECT * FROM t1view WHERE id=3;
id  namedateinfo
--  --  --  --
NULLNULLNULLNULL
3   first   2004-02-14  first's se


-- the previous view with reordered tables
CREATE VIEW t1view2 AS
SELECT  b.*
FROM t1 AS b
LEFT JOIN (
SELECT  name,
max(date) AS date
FROM t1
GROUP BY name
) AS a
ON (a.name=b.name AND a.date=b.date)
WHERE a.name NOT NULL
;

-- SELECT works ok:
SELECT * FROM t1view2;
id  namedateinfo
--  --  --  ---
2   second  2004-02-12  second's first info
3   first   2004-02-14  first's second info

-- SELECT WHERE works ok:
SELECT * FROM t1view2 WHERE id=3;
id  namedateinfo
--  --  --  ---
3   first   2004-02-14  first's second info
sqlite>


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] mysql to sqlite and linebreaks (\n)

[sascha . wojewsky]

Hi,

i've a problem with linebreaks in sqlite.
my mysqldump has some \n for linebreaks, but sqlite do not undestand this.
how can i escape the linebreak?

THX

Sascha Wojewsky

RE: [sqlite] help! cant unsubscribe

[Andrzej Kukula]

> From: Darren Duncan
> At 3:16 PM +0100 2/17/04, zeb warrior wrote:
> >I've enjoyed this group very much but it's time for me to 
> leave. I've 
> >sent an email to sqlite-user-help and got the information to 
> unsub, but 
> >alas no luck. While the system did ask me to confirm the 
> unsub letter 
> >by replying to it here is the response I got:
> 
> The instructions for unsubscribing are staring you in the face with 
> every email you get through the list.  The list server adds these 
> headers to every message:
> 
> List-Post: 
> List-Help: 
> List-Unsubscribe: 
> List-Subscribe: 
> 
> I don't see how it can be any more plain.  So, if you look at the 
> third one, you see that you send an email to 
> [EMAIL PROTECTED] to get off the list.
> 
> -- Darren Duncan
> 
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]

Has he seen the bottom of any message ever? Instructions for unsubscribing
are posted with every message...

Regards,
Andrzej Kukula



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]