pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feed(resend) NULL constrains inheritance to views
2+ messages / 2 participants
[nested] [flat]
* (resend) NULL constrains inheritance to views
@ 2005-01-06 17:05 Bradley Kieser <brad@kieser.net>
0 siblings, 1 reply; 2+ messages in thread
From: Bradley Kieser @ 2005-01-06 17:05 UTC (permalink / raw)
To: pgsql-admin
(resend as my other mail seems to not have gotten through)
I have created a schema with views on top of tables. We use JDBC to
connect to the schema and we use JBuilderX to develop our app.
Every table as an "id" column of type serial. I.e. It is not null with
default nextval(...).
However, it seems that when a column from a table is used in a view, the
not null part (as well as the default values) seems to be dropped.
Serials become integers.
I can use "alter table" to restore default values on the views, but
"alter table" to make a view column not null results in an error saying
that the viewname is not a table! I.e. I don't seem to be able to make
the column not null.
I think that this is throwing JBuilder's resolver into chaos because it
doesn't use the column that I have specified as ROWID (column "id" for
each view) to resolve update rows back to the view, it tries to use
every single column, a sure sign that it thinks that the column I have
told it is a rowid isn't up to the job. I am pretty certain that it's
because it thinks that column can be null.
Unfortunately I have to use views and cannot use tables because of
security in the app. The views have rules set on them as well as
permissions to specific users. So the answer isn't to open up the
database and use the underlying tables instead. That would open a
security hole in the app. Moreover, I use views to do complex processing
in PG (e.g. multi-table joins or rules and triggers on updates to ensure
data integrity and implement business logic). JBuilder, being a RAD is
great at building apps from simple datasets (i.e. single views and
tables), but it quickly starts to wilt under complex parametised queries!
So for these reasons I need to find a way to tell JBuilder (through
JDBC) that the ID columns are not nullable. But how? Can someone please
help me?
Thanks,
Brad
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: (resend) NULL constrains inheritance to views
@ 2005-01-07 07:33 Markus Bertheau <twanger@bluetwanger.de>
parent: Bradley Kieser <brad@kieser.net>
0 siblings, 0 replies; 2+ messages in thread
From: Markus Bertheau @ 2005-01-07 07:33 UTC (permalink / raw)
To: Bradley Kieser <brad@kieser.net>; +Cc: pgsql-admin
You could probably construct the views manually by creating tables with
a ON SELECT rule.
У Чтв, 2005-01-06 у 17:05 +0000, Bradley Kieser пише:
> (resend as my other mail seems to not have gotten through)
>
> I have created a schema with views on top of tables. We use JDBC to
> connect to the schema and we use JBuilderX to develop our app.
>
> Every table as an "id" column of type serial. I.e. It is not null with
> default nextval(...).
> However, it seems that when a column from a table is used in a view, the
> not null part (as well as the default values) seems to be dropped.
> Serials become integers.
>
> I can use "alter table" to restore default values on the views, but
> "alter table" to make a view column not null results in an error saying
> that the viewname is not a table! I.e. I don't seem to be able to make
> the column not null.
>
> I think that this is throwing JBuilder's resolver into chaos because it
> doesn't use the column that I have specified as ROWID (column "id" for
> each view) to resolve update rows back to the view, it tries to use
> every single column, a sure sign that it thinks that the column I have
> told it is a rowid isn't up to the job. I am pretty certain that it's
> because it thinks that column can be null.
>
> Unfortunately I have to use views and cannot use tables because of
> security in the app. The views have rules set on them as well as
> permissions to specific users. So the answer isn't to open up the
> database and use the underlying tables instead. That would open a
> security hole in the app. Moreover, I use views to do complex processing
> in PG (e.g. multi-table joins or rules and triggers on updates to ensure
> data integrity and implement business logic). JBuilder, being a RAD is
> great at building apps from simple datasets (i.e. single views and
> tables), but it quickly starts to wilt under complex parametised queries!
>
> So for these reasons I need to find a way to tell JBuilder (through
> JDBC) that the ID columns are not nullable. But how? Can someone please
> help me?
>
> Thanks,
>
> Brad
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Markus Bertheau <twanger@bluetwanger.de>
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-01-07 07:33 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-06 17:05 (resend) NULL constrains inheritance to views Bradley Kieser <brad@kieser.net>
2005-01-07 07:33 ` Markus Bertheau <twanger@bluetwanger.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox