Thread: JDBC idea with special JDBC views
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
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
>
>
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