Thread: JDBC idea with special JDBC views

JDBC idea with special JDBC views

From
snpe
Date:
Hello,
  I have idea for few methods in JDBC
We can  add views pg_gettables, pg_getcolumns, pg_getprimarykeys
etc. and method work only connection.executeQuery("select * from
pg_gettables") (and like).Columns in view are like JDBC specification.
Now JDBC source stay unchanged and we can speedup query for view and change
with change JDBC specification
In begining schema can be null, but later we add schema support with only
change views (without change JDBC source)
We need COLUMN_SIZE and PRECISION for any view.We can make function like
format_type in postgresql source or simple view like (for getColumns) :
create view pg_getcolumns as (this is not complete view for getColumns, only
example - can be much better)
select null::varchar as "TABLE CAT",
        null::varchar as "TABLE_SCHEM",
        c.relname as "TABLE_NAME",a.attname as "COLUMN_NAME",
        CASE t.typname
                when 'int2' then 'Types.SMALLINT'
                when 'int4' then 'Types.INTEGER'
                when 'oid' then 'Types.INTEGER'
                when 'numeric' then 'Types.NUMERIC'
                else
                        'Types.OTHER'
        end as "DATA_TYPE",
        typname as "TYPE_NAME",
        case typname
                when 'varchar' then
                        case atttypmod
                                when -1 then null
                        else
                                 (atttypmod-4)
                        end
                when 'bpchar' then
                        case atttypmod
                                when -1 then null
                        else
                                 (atttypmod-4)
                        end
        else
                attlen
        end as "COLUMN_SIZE",
        null::int4 as "BUFFER_LENGTH",
        case typname
                when 'numeric' then
                        case atttypmod
                                when -1 then null
                        else
                                (atttypmod-4) >> 16
                        end
                when 'decimal' then
                        case atttypmod
                                when -1 then null
                        else
                                 (atttypmod-4) >>16
                        end
        end as "DECIMAL_DIGITS",
        case typname
                when 'numeric' then
                        case atttypmod
                                when -1 then null
                        else
                                (int4(atttypmod)-int4('4')) & int4('65535')
                        end
                when 'decimal' then
                        case atttypmod
                                when -1 then null
                        else
                                (int4(atttypmod)-int4('4')) & int4('65535')
                        end
        end as "NUM_PREC_RADIX"
from pg_class c,pg_catalog.pg_type t,pg_catalog.pg_attribute a
where c.oid=a.attrelid and t.oid=a.atttypid;

getColumns is like :

    connection.CreateStatement().executeQuery("SELECT * " +
        "FROM pg_getcolumns " +
        "WHERE \"TABLE_CAT\" LIKE " + catalog==null ? "%" : catalog +
etc

   Maybe is better with PreparedStatement in this method

regards
Haris Peco

Re: JDBC idea with special JDBC views

From
Dave Cramer
Date:
This is a very good idea, however it will be a tough sell to the core
group to add views for an interface. Keep in mind that if the jdbc
interface gets a few views, then all the other interfaces will want them
as well. There is a new table called pg_depend which will help in
identifying relationships between tables.

Dave
On Thu, 2002-09-12 at 18:40, snpe wrote:
> Hello,
>   I have idea for few methods in JDBC
> We can  add views pg_gettables, pg_getcolumns, pg_getprimarykeys
> etc. and method work only connection.executeQuery("select * from
> pg_gettables") (and like).Columns in view are like JDBC specification.
> Now JDBC source stay unchanged and we can speedup query for view and change
> with change JDBC specification
> In begining schema can be null, but later we add schema support with only
> change views (without change JDBC source)
> We need COLUMN_SIZE and PRECISION for any view.We can make function like
> format_type in postgresql source or simple view like (for getColumns) :
> create view pg_getcolumns as (this is not complete view for getColumns, only
> example - can be much better)
> select null::varchar as "TABLE CAT",
>         null::varchar as "TABLE_SCHEM",
>         c.relname as "TABLE_NAME",a.attname as "COLUMN_NAME",
>         CASE t.typname
>                 when 'int2' then 'Types.SMALLINT'
>                 when 'int4' then 'Types.INTEGER'
>                 when 'oid' then 'Types.INTEGER'
>                 when 'numeric' then 'Types.NUMERIC'
>                 else
>                         'Types.OTHER'
>         end as "DATA_TYPE",
>         typname as "TYPE_NAME",
>         case typname
>                 when 'varchar' then
>                         case atttypmod
>                                 when -1 then null
>                         else
>                                  (atttypmod-4)
>                         end
>                 when 'bpchar' then
>                         case atttypmod
>                                 when -1 then null
>                         else
>                                  (atttypmod-4)
>                         end
>         else
>                 attlen
>         end as "COLUMN_SIZE",
>         null::int4 as "BUFFER_LENGTH",
>         case typname
>                 when 'numeric' then
>                         case atttypmod
>                                 when -1 then null
>                         else
>                                 (atttypmod-4) >> 16
>                         end
>                 when 'decimal' then
>                         case atttypmod
>                                 when -1 then null
>                         else
>                                  (atttypmod-4) >>16
>                         end
>         end as "DECIMAL_DIGITS",
>         case typname
>                 when 'numeric' then
>                         case atttypmod
>                                 when -1 then null
>                         else
>                                 (int4(atttypmod)-int4('4')) & int4('65535')
>                         end
>                 when 'decimal' then
>                         case atttypmod
>                                 when -1 then null
>                         else
>                                 (int4(atttypmod)-int4('4')) & int4('65535')
>                         end
>         end as "NUM_PREC_RADIX"
> from pg_class c,pg_catalog.pg_type t,pg_catalog.pg_attribute a
> where c.oid=a.attrelid and t.oid=a.atttypid;
>
> getColumns is like :
>
>     connection.CreateStatement().executeQuery("SELECT * " +
>         "FROM pg_getcolumns " +
>         "WHERE \"TABLE_CAT\" LIKE " + catalog==null ? "%" : catalog +
> etc
>
>    Maybe is better with PreparedStatement in this method
>
> regards
> Haris Peco
>
>



Re: JDBC idea with special JDBC views

From
snpe
Date:
That is special views and pg_depned don't help.
For core group : we need sql script when start with initdb and views for
any interfaces go here with special syntax, btw for jdbc are pg_jdbc_xxx
, for c pg_jdbc_c. etc
We can that core sql script call special sql script (jdbc_views.sql,
c_view.sql etc.)
jdbc_views.sql maintain JDBC group etc
regards
Haris Peco
On Friday 13 September 2002 02:33 am, Dave Cramer wrote:
> This is a very good idea, however it will be a tough sell to the core
> group to add views for an interface. Keep in mind that if the jdbc
> interface gets a few views, then all the other interfaces will want them
> as well. There is a new table called pg_depend which will help in
> identifying relationships between tables.
>
> Dave
>
> On Thu, 2002-09-12 at 18:40, snpe wrote:
> > Hello,
> >   I have idea for few methods in JDBC
> > We can  add views pg_gettables, pg_getcolumns, pg_getprimarykeys
> > etc. and method work only connection.executeQuery("select * from
> > pg_gettables") (and like).Columns in view are like JDBC specification.
> > Now JDBC source stay unchanged and we can speedup query for view and
> > change with change JDBC specification
> > In begining schema can be null, but later we add schema support with only
> > change views (without change JDBC source)
> > We need COLUMN_SIZE and PRECISION for any view.We can make function like
> > format_type in postgresql source or simple view like (for getColumns) :
> > create view pg_getcolumns as (this is not complete view for getColumns,
> > only example - can be much better)
> > select null::varchar as "TABLE CAT",
> >         null::varchar as "TABLE_SCHEM",
> >         c.relname as "TABLE_NAME",a.attname as "COLUMN_NAME",
> >         CASE t.typname
> >                 when 'int2' then 'Types.SMALLINT'
> >                 when 'int4' then 'Types.INTEGER'
> >                 when 'oid' then 'Types.INTEGER'
> >                 when 'numeric' then 'Types.NUMERIC'
> >                 else
> >                         'Types.OTHER'
> >         end as "DATA_TYPE",
> >         typname as "TYPE_NAME",
> >         case typname
> >                 when 'varchar' then
> >                         case atttypmod
> >                                 when -1 then null
> >                         else
> >                                  (atttypmod-4)
> >                         end
> >                 when 'bpchar' then
> >                         case atttypmod
> >                                 when -1 then null
> >                         else
> >                                  (atttypmod-4)
> >                         end
> >         else
> >                 attlen
> >         end as "COLUMN_SIZE",
> >         null::int4 as "BUFFER_LENGTH",
> >         case typname
> >                 when 'numeric' then
> >                         case atttypmod
> >                                 when -1 then null
> >                         else
> >                                 (atttypmod-4) >> 16
> >                         end
> >                 when 'decimal' then
> >                         case atttypmod
> >                                 when -1 then null
> >                         else
> >                                  (atttypmod-4) >>16
> >                         end
> >         end as "DECIMAL_DIGITS",
> >         case typname
> >                 when 'numeric' then
> >                         case atttypmod
> >                                 when -1 then null
> >                         else
> >                                 (int4(atttypmod)-int4('4')) &
> > int4('65535') end
> >                 when 'decimal' then
> >                         case atttypmod
> >                                 when -1 then null
> >                         else
> >                                 (int4(atttypmod)-int4('4')) &
> > int4('65535') end
> >         end as "NUM_PREC_RADIX"
> > from pg_class c,pg_catalog.pg_type t,pg_catalog.pg_attribute a
> > where c.oid=a.attrelid and t.oid=a.atttypid;
> >
> > getColumns is like :
> >
> >     connection.CreateStatement().executeQuery("SELECT * " +
> >         "FROM pg_getcolumns " +
> >         "WHERE \"TABLE_CAT\" LIKE " + catalog==null ? "%" : catalog +
> > etc
> >
> >    Maybe is better with PreparedStatement in this method
> >
> > regards
> > Haris Peco