Thread: Auto-increment serial (Postgresql JDBC driver w/ OpenOffice.org ;) )

Hi,

<comment>
I know my English is very strange ;)
</comment>

Under OO 'serial' columns don't work, because OO doesn't see
'serial' as auto-increment type. I know it's pseuso-type, but
we can detect it. What do you think, about add this feature
to driver?

This patch is my simple implementation.

Best regards
Jarek

--
8  \\|||//  Jaroslaw J. Pyszny <arghil@poczta.onet.pl>
b   (o|o)    Linux admin/devel ("Vitae,non scholae,discimus")
i  --\_/--   Linux user: #96704    (http://counter.li.org)
t =->computer - daj znac jesli chcesz sie pozbyc <-=

On Mon, 21 Sep 2004, Jaroslaw J. Pyszny wrote:

> Under OO 'serial' columns don't work, because OO doesn't see
> 'serial' as auto-increment type. I know it's pseuso-type, but
> we can detect it. What do you think, about add this feature
> to driver?
>

The basic idea looks good to me, but you've made a mistake in the
isAutoIncrement() method.  You are using the column number from the
ResultSet to look up the default when you need to actually use that
column's position in the table.  In fact it looks like the whole method
body should be moved into the Field class much like the isNullable method
does.  This also avoid the contrived hash key value.

Finally we prefer patches in context diff format (diff -c).

Kris Jurka

Can you pull the sql string out into a final static ? It's never going
to change. Also is there a way to do this using the information schema ?

Dave
On Mon, 2004-09-20 at 21:50, Kris Jurka wrote:
> On Mon, 21 Sep 2004, Jaroslaw J. Pyszny wrote:
>
> > Under OO 'serial' columns don't work, because OO doesn't see
> > 'serial' as auto-increment type. I know it's pseuso-type, but
> > we can detect it. What do you think, about add this feature
> > to driver?
> >
>
> The basic idea looks good to me, but you've made a mistake in the
> isAutoIncrement() method.  You are using the column number from the
> ResultSet to look up the default when you need to actually use that
> column's position in the table.  In fact it looks like the whole method
> body should be moved into the Field class much like the isNullable method
> does.  This also avoid the contrived hash key value.
>
> Finally we prefer patches in context diff format (diff -c).
>
> Kris Jurka
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
--
Dave Cramer
519 939 0336
ICQ # 14675561
www.postgresintl.com

W liście z wto, 21-09-2004, godz. 03:50, Kris Jurka pisze:
> On Mon, 21 Sep 2004, Jaroslaw J. Pyszny wrote:
>

> The basic idea looks good to me, but you've made a mistake in the
> isAutoIncrement() method.  You are using the column number from the
> ResultSet to look up the default when you need to actually use that
> column's position in the table.  In fact it looks like the whole method
> body should be moved into the Field class much like the isNullable method
> does.  This also avoid the contrived hash key value.
>
> Finally we prefer patches in context diff format (diff -c).
Thanks :)
I'll try fix it.

Jarek
--
8  \\|||//  Jarosław J. Pyszny <arghil@poczta.onet.pl>
b   (o|o)    Linux admin/devel ("Vitae,non scholae,discimus")
i  --\_/--   Linux user: #96704    (http://counter.li.org)
t =->computers - let me know if you want to get rid of it <-=

This is probably a much more portable way to get the same information.

 select column_default from information_schema.columns where table_name
= 'xyz';

Dave
On Tue, 2004-09-21 at 15:25, Jaroslaw J. Pyszny wrote:
> W liście z wto, 21-09-2004, godz. 03:50, Kris Jurka pisze:
> > On Mon, 21 Sep 2004, Jaroslaw J. Pyszny wrote:
> >
>
> > The basic idea looks good to me, but you've made a mistake in the
> > isAutoIncrement() method.  You are using the column number from the
> > ResultSet to look up the default when you need to actually use that
> > column's position in the table.  In fact it looks like the whole method
> > body should be moved into the Field class much like the isNullable method
> > does.  This also avoid the contrived hash key value.
> >
> > Finally we prefer patches in context diff format (diff -c).
> Thanks :)
> I'll try fix it.
>
> Jarek
--
Dave Cramer
519 939 0336
ICQ # 14675561
www.postgresintl.com

Sorry, missed one more where clause.

select column_default from information_schema.columns where table_name =
'xyz' and column_name = 'id';

This assumes a table defined like create table xyz (id serial);

Dave
On Tue, 2004-09-21 at 16:31, Dave Cramer wrote:
> This is probably a much more portable way to get the same information.
>
>  select column_default from information_schema.columns where table_name
> = 'xyz';
>
> Dave
> On Tue, 2004-09-21 at 15:25, Jaroslaw J. Pyszny wrote:
> > W liście z wto, 21-09-2004, godz. 03:50, Kris Jurka pisze:
> > > On Mon, 21 Sep 2004, Jaroslaw J. Pyszny wrote:
> > >
> >
> > > The basic idea looks good to me, but you've made a mistake in the
> > > isAutoIncrement() method.  You are using the column number from the
> > > ResultSet to look up the default when you need to actually use that
> > > column's position in the table.  In fact it looks like the whole method
> > > body should be moved into the Field class much like the isNullable method
> > > does.  This also avoid the contrived hash key value.
> > >
> > > Finally we prefer patches in context diff format (diff -c).
> > Thanks :)
> > I'll try fix it.
> >
> > Jarek
--
Dave Cramer
519 939 0336
ICQ # 14675561
www.postgresintl.com

W liście z sro, 22-09-2004, godz. 00:50, Dave Cramer pisze:
> Sorry, missed one more where clause.
>
> select column_default from information_schema.columns where table_name =
> 'xyz' and column_name = 'id';
>
> This assumes a table defined like create table xyz (id serial);
It's works but only for the owner of the table
or I do something wrong ;)

Jarek
--
8  \\|||//  Jarosław J. Pyszny <arghil@poczta.onet.pl>
b   (o|o)    Linux admin/devel ("Vitae,non scholae,discimus")
i  --\_/--   Linux user: #96704    (http://counter.li.org)
t =->komputer(y) - daj znać jeśli chcesz się pozbyć <-=

On Wed, 22 Sep 2004, Jaroslaw J. Pyszny wrote:

> W liście z sro, 22-09-2004, godz. 00:50, Dave Cramer pisze:
> > Sorry, missed one more where clause.
> >
> > select column_default from information_schema.columns where table_name =
> > 'xyz' and column_name = 'id';
> >
> > This assumes a table defined like create table xyz (id serial);
> It's works but only for the owner of the table
> or I do something wrong ;)
>

The information_schema takes into account permissions on objects and
doesn't show things you can't access.  So in this case a GRANT SELECT ON
<table> TO <user> would make it show up.  This seems OK because to get a
ResultSet you clearly have to be able to query the table.

The problem I came across was that if you only grant access to the table
it doesn't propagate to the underlying sequence.  So if a user doesn't
have permissions to use the sequence it doesn't show up as the default in
the information_schema view.  I'm leaning towards believing this is a bug
in the information_schema, but it just seems safer to use the direct
catalog access method you wrote.

Kris Jurka

W liście z sro, 22-09-2004, godz. 21:59, Kris Jurka pisze:
> On Wed, 22 Sep 2004, Jaroslaw J. Pyszny wrote:
[...]

second patch

--
8  \\|||//  Jarosław J. Pyszny <arghil@poczta.onet.pl>
b   (o|o)    Linux admin/devel ("Vitae,non scholae,discimus")
i  --\_/--   Linux user: #96704    (http://counter.li.org)
t =->komputer(y) - daj znać jeśli chcesz się pozbyć <-=

On Wed, 23 Sep 2004, Jaroslaw J. Pyszny wrote:

> W li�cie z sro, 22-09-2004, godz. 21:59, Kris Jurka pisze:
> > On Wed, 22 Sep 2004, Jaroslaw J. Pyszny wrote:
> [...]
>
> second patch
>

I've applied the ResultSetMetaData.isAutoIncrement part of this patch, but
you changed what you were doing in DatabaseMetaData and I wanted to
discuss that first.  Now you are adding the serial pseudotypes to the
getTypeInfo results.

1) With the addition of the new types, should we only return true for
AUTO_INCREMENT on the serial types and not the integer types?

2) Is it necessary to return all four versions of the types (serial,
serial4, bigserial, and serial8)?  To me this seems to just add confusion
and perhaps we should just pick a pair of these to use.

3) If we are going to add these as valid types, it seems we should alter
DatabaseMetaData.getColumns to return "serial" for the TYPE_NAME column as
well.

Kris Jurka

W liscie z czw, 23-09-2004, godz. 07:40, Kris Jurka pisze:
> >

> 1) With the addition of the new types, should we only return true for
> AUTO_INCREMENT on the serial types and not the integer types?
OK

> 2) Is it necessary to return all four versions of the types (serial,
> serial4, bigserial, and serial8)?  To me this seems to just add confusion
> and perhaps we should just pick a pair of these to use.
Only serial and bigserial ?

> 3) If we are going to add these as valid types, it seems we should alter
> DatabaseMetaData.getColumns to return "serial" for the TYPE_NAME column as
> well.
Ok

Jarek
--
8  \\|||//  Jarosław J. Pyszny <arghil@poczta.onet.pl>
b   (o|o)    Linux admin/devel ("Vitae,non scholae,discimus")
i  --\_/--   Linux user: #96704    (http://counter.li.org)
t =->computers - let me know if you want to get rid of it <-=

W liście z wto, 28-09-2004, godz. 17:37, Jaroslaw J. Pyszny pisze:
Hi,
 3rd patch: (getColumns returns serial or bigserial)

 What do you think about it?

Jarek
--
8  \\|||//  Jarosław J. Pyszny <arghil@poczta.onet.pl>
b   (o|o)    Linux admin/devel ("Vitae,non scholae,discimus")
i  --\_/--   Linux user: #96704    (http://counter.li.org)
t =->computers - let me know if you want to get rid of it <-=

On Thu, 30 Sep 2004, Jaroslaw J. Pyszny wrote:

> W li�cie z wto, 28-09-2004, godz. 17:37, Jaroslaw J. Pyszny pisze:
> Hi,
>  3rd patch: (getColumns returns serial or bigserial)
>

Applied.  Thanks.

Kris Jurka