natural diversity nd_reagent multiple schemas common and specific data postgres

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

natural diversity nd_reagent multiple schemas common and specific data postgres

Matija Brozović
Hi all,

I have a specific problem which I resolved but as always we need to
strive for better solutions.

I have postgres database with chado  with 4 separate database
schemas(different species) and a public schema.

Within the ND module I have a nd_reagent table and a table called
treatment_reagent which links nd_reagent with some treatments(a custom
need simple 3 column table).

The problem I encountered was concerning the foreign key constraint
referring to nd_reagent table from treatment_reagent table.
It worked well with only one species, but when others were
introduced(all in separate schemas) there was no foreign key references
for nd_reagent in those other specific species schemas.
Also there is some small number of records which are used by all
schemas, but if I'd copy those in all schemas this is a problem of
multiplication.
If I would copy everything into public I would loose the origin of data
for specific species within each schema.

So what I did ...

I copied the records being used by all species into public.nd_reagent and
removed it from other species specific nd_reagent tables).

I left the species specific records in their own nd_reagent tables,

and created a new table in the public schema called all_nd_reagent and
put in all the nd_reagent_ids.

Then I created triggers (insert/update/delete) on all species specific
nd_reagent tables, and the public nd_reagent table to keep this new
all_nd_reagent table up to date.

Linked all different species treatment_reagent tables foreign keys to
this new table.
this way I preserved the foreign key constraint.

I hope my explanation is not too complicated.

I would just like to know if you have any ideas for more simple solutions ?

cheers
Matija






------------------------------------------------------------------------------
Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
Reply | Threaded
Open this post in threaded view
|

Re: natural diversity nd_reagent multiple schemas common and specific data postgres

Karl O. Pinc
Why did you need to introduce separate schemas and
separate tables for different species?  What's the goal?

On 09/24/2014 06:25:20 AM, Matija Brozović wrote:

> Hi all,
>
> I have a specific problem which I resolved but as always we need to
> strive for better solutions.
>
> I have postgres database with chado  with 4 separate database
> schemas(different species) and a public schema.
>
> Within the ND module I have a nd_reagent table and a table called
> treatment_reagent which links nd_reagent with some treatments(a
> custom
>
> need simple 3 column table).
>
> The problem I encountered was concerning the foreign key constraint
> referring to nd_reagent table from treatment_reagent table.
> It worked well with only one species, but when others were
> introduced(all in separate schemas) there was no foreign key
> references
> for nd_reagent in those other specific species schemas.
> Also there is some small number of records which are used by all
> schemas, but if I'd copy those in all schemas this is a problem of
> multiplication.
> If I would copy everything into public I would loose the origin of
> data
> for specific species within each schema.
>
> So what I did ...
>
> I copied the records being used by all species into public.nd_reagent
> and
> removed it from other species specific nd_reagent tables).
>
> I left the species specific records in their own nd_reagent tables,
>
> and created a new table in the public schema called all_nd_reagent
> and
>
> put in all the nd_reagent_ids.
>
> Then I created triggers (insert/update/delete) on all species
> specific
>
> nd_reagent tables, and the public nd_reagent table to keep this new
> all_nd_reagent table up to date.
>
> Linked all different species treatment_reagent tables foreign keys to
> this new table.
> this way I preserved the foreign key constraint.
>
> I hope my explanation is not too complicated.
>
> I would just like to know if you have any ideas for more simple
> solutions ?
>
> cheers
> Matija
>
>
>
>
>
>
> ------------------------------------------------------------------------------
> Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
> Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS
> Reports
> Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
> Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
> http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/
> ostg.clktrk
> _______________________________________________
> Gmod-schema mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gmod-schema
>
>




Karl <[hidden email]>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

------------------------------------------------------------------------------
Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
Reply | Threaded
Open this post in threaded view
|

Re: natural diversity nd_reagent multiple schemas common and specific data postgres

Matija Brozović
I actually inherited the job from a previous person, the schemas
were introduced to separate access to the data from php framework, with
different
access roles defined for different species. This is how the database was
planned.

cheers,
M.

On 09/24/2014 03:44 PM, Karl O. Pinc wrote:

> Why did you need to introduce separate schemas and
> separate tables for different species?  What's the goal?
>
> On 09/24/2014 06:25:20 AM, Matija Brozović wrote:
>> Hi all,
>>
>> I have a specific problem which I resolved but as always we need to
>> strive for better solutions.
>>
>> I have postgres database with chado  with 4 separate database
>> schemas(different species) and a public schema.
>>
>> Within the ND module I have a nd_reagent table and a table called
>> treatment_reagent which links nd_reagent with some treatments(a
>> custom
>>
>> need simple 3 column table).
>>
>> The problem I encountered was concerning the foreign key constraint
>> referring to nd_reagent table from treatment_reagent table.
>> It worked well with only one species, but when others were
>> introduced(all in separate schemas) there was no foreign key
>> references
>> for nd_reagent in those other specific species schemas.
>> Also there is some small number of records which are used by all
>> schemas, but if I'd copy those in all schemas this is a problem of
>> multiplication.
>> If I would copy everything into public I would loose the origin of
>> data
>> for specific species within each schema.
>>
>> So what I did ...
>>
>> I copied the records being used by all species into public.nd_reagent
>> and
>> removed it from other species specific nd_reagent tables).
>>
>> I left the species specific records in their own nd_reagent tables,
>>
>> and created a new table in the public schema called all_nd_reagent
>> and
>>
>> put in all the nd_reagent_ids.
>>
>> Then I created triggers (insert/update/delete) on all species
>> specific
>>
>> nd_reagent tables, and the public nd_reagent table to keep this new
>> all_nd_reagent table up to date.
>>
>> Linked all different species treatment_reagent tables foreign keys to
>> this new table.
>> this way I preserved the foreign key constraint.
>>
>> I hope my explanation is not too complicated.
>>
>> I would just like to know if you have any ideas for more simple
>> solutions ?
>>
>> cheers
>> Matija
>>
>>
>>
>>
>>
>>
>> ------------------------------------------------------------------------------
>> Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
>> Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS
>> Reports
>> Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
>> Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
>> http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/
>> ostg.clktrk
>> _______________________________________________
>> Gmod-schema mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/gmod-schema
>>
>>
>
>
>
> Karl <[hidden email]>
> Free Software:  "You don't pay back, you pay forward."
>                   -- Robert A. Heinlein


------------------------------------------------------------------------------
Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
Reply | Threaded
Open this post in threaded view
|

Re: natural diversity nd_reagent multiple schemas common and specific data postgres

Karl O. Pinc
On 09/24/2014 08:53:09 AM, Matija Brozović wrote:
> I actually inherited the job from a previous person, the schemas
> were introduced to separate access to the data from php framework,
> with
> different
> access roles defined for different species. This is how the database
> was
> planned.

To be sure I understand, the design is due to security?
You used it as a means of controlling authorization?

(Just what the authorization is for, viewing data,
updating data, etc., isn't important.)

>
> cheers,
> M.
>
> On 09/24/2014 03:44 PM, Karl O. Pinc wrote:
> > Why did you need to introduce separate schemas and
> > separate tables for different species?  What's the goal?
> >
> > On 09/24/2014 06:25:20 AM, Matija Brozović wrote:
> >> Hi all,
> >>
> >> I have a specific problem which I resolved but as always we need
> to
> >> strive for better solutions.
> >>
> >> I have postgres database with chado  with 4 separate database
> >> schemas(different species) and a public schema.
> >>
> >> Within the ND module I have a nd_reagent table and a table called
> >> treatment_reagent which links nd_reagent with some treatments(a
> >> custom
> >>
> >> need simple 3 column table).
> >>
> >> The problem I encountered was concerning the foreign key
> constraint
> >> referring to nd_reagent table from treatment_reagent table.
> >> It worked well with only one species, but when others were
> >> introduced(all in separate schemas) there was no foreign key
> >> references
> >> for nd_reagent in those other specific species schemas.
> >> Also there is some small number of records which are used by all
> >> schemas, but if I'd copy those in all schemas this is a problem of
> >> multiplication.
> >> If I would copy everything into public I would loose the origin of
> >> data
> >> for specific species within each schema.
> >>
> >> So what I did ...
> >>
> >> I copied the records being used by all species into
> public.nd_reagent
> >> and
> >> removed it from other species specific nd_reagent tables).
> >>
> >> I left the species specific records in their own nd_reagent
> tables,
> >>
> >> and created a new table in the public schema called all_nd_reagent
> >> and
> >>
> >> put in all the nd_reagent_ids.
> >>
> >> Then I created triggers (insert/update/delete) on all species
> >> specific
> >>
> >> nd_reagent tables, and the public nd_reagent table to keep this
> new
> >> all_nd_reagent table up to date.
> >>
> >> Linked all different species treatment_reagent tables foreign keys
> to
> >> this new table.
> >> this way I preserved the foreign key constraint.
> >>
> >> I hope my explanation is not too complicated.
> >>
> >> I would just like to know if you have any ideas for more simple
> >> solutions ?
> >>
> >> cheers
> >> Matija
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> ------------------------------------------------------------------------------
> >> Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
> >> Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS
> >> Reports
> >> Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White
> paper
> >> Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog
> Analyzer
> >> http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/
> >> ostg.clktrk
> >> _______________________________________________
> >> Gmod-schema mailing list
> >> [hidden email]
> >> https://lists.sourceforge.net/lists/listinfo/gmod-schema
> >>
> >>
> >
> >
> >
> > Karl <[hidden email]>
> > Free Software:  "You don't pay back, you pay forward."
> >                   -- Robert A. Heinlein
>
>




Karl <[hidden email]>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

------------------------------------------------------------------------------
Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
Reply | Threaded
Open this post in threaded view
|

Re: natural diversity nd_reagent multiple schemas common and specific data postgres

Matija Brozović
Exactly, for the purposes of our custom cms.

M.

On 09/24/2014 04:12 PM, Karl O. Pinc wrote:

> On 09/24/2014 08:53:09 AM, Matija Brozović wrote:
>> I actually inherited the job from a previous person, the schemas
>> were introduced to separate access to the data from php framework,
>> with
>> different
>> access roles defined for different species. This is how the database
>> was
>> planned.
> To be sure I understand, the design is due to security?
> You used it as a means of controlling authorization?
>
> (Just what the authorization is for, viewing data,
> updating data, etc., isn't important.)
>
>> cheers,
>> M.
>>
>> On 09/24/2014 03:44 PM, Karl O. Pinc wrote:
>>> Why did you need to introduce separate schemas and
>>> separate tables for different species?  What's the goal?
>>>
>>> On 09/24/2014 06:25:20 AM, Matija Brozović wrote:
>>>> Hi all,
>>>>
>>>> I have a specific problem which I resolved but as always we need
>> to
>>>> strive for better solutions.
>>>>
>>>> I have postgres database with chado  with 4 separate database
>>>> schemas(different species) and a public schema.
>>>>
>>>> Within the ND module I have a nd_reagent table and a table called
>>>> treatment_reagent which links nd_reagent with some treatments(a
>>>> custom
>>>>
>>>> need simple 3 column table).
>>>>
>>>> The problem I encountered was concerning the foreign key
>> constraint
>>>> referring to nd_reagent table from treatment_reagent table.
>>>> It worked well with only one species, but when others were
>>>> introduced(all in separate schemas) there was no foreign key
>>>> references
>>>> for nd_reagent in those other specific species schemas.
>>>> Also there is some small number of records which are used by all
>>>> schemas, but if I'd copy those in all schemas this is a problem of
>>>> multiplication.
>>>> If I would copy everything into public I would loose the origin of
>>>> data
>>>> for specific species within each schema.
>>>>
>>>> So what I did ...
>>>>
>>>> I copied the records being used by all species into
>> public.nd_reagent
>>>> and
>>>> removed it from other species specific nd_reagent tables).
>>>>
>>>> I left the species specific records in their own nd_reagent
>> tables,
>>>> and created a new table in the public schema called all_nd_reagent
>>>> and
>>>>
>>>> put in all the nd_reagent_ids.
>>>>
>>>> Then I created triggers (insert/update/delete) on all species
>>>> specific
>>>>
>>>> nd_reagent tables, and the public nd_reagent table to keep this
>> new
>>>> all_nd_reagent table up to date.
>>>>
>>>> Linked all different species treatment_reagent tables foreign keys
>> to
>>>> this new table.
>>>> this way I preserved the foreign key constraint.
>>>>
>>>> I hope my explanation is not too complicated.
>>>>
>>>> I would just like to know if you have any ideas for more simple
>>>> solutions ?
>>>>
>>>> cheers
>>>> Matija
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>> ------------------------------------------------------------------------------
>>>> Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
>>>> Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS
>>>> Reports
>>>> Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White
>> paper
>>>> Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog
>> Analyzer
>>>> http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/
>>>> ostg.clktrk
>>>> _______________________________________________
>>>> Gmod-schema mailing list
>>>> [hidden email]
>>>> https://lists.sourceforge.net/lists/listinfo/gmod-schema
>>>>
>>>>
>>>
>>>
>>> Karl <[hidden email]>
>>> Free Software:  "You don't pay back, you pay forward."
>>>                    -- Robert A. Heinlein
>>
>
>
>
> Karl <[hidden email]>
> Free Software:  "You don't pay back, you pay forward."
>                   -- Robert A. Heinlein


------------------------------------------------------------------------------
Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
Reply | Threaded
Open this post in threaded view
|

Re: natural diversity nd_reagent multiple schemas common and specific data postgres

Matija Brozović
One more reason was to keep the tables smaller and separated in this way.

M.
On 09/24/2014 04:15 PM, Matija Brozović wrote:

> Exactly, for the purposes of our custom cms.
>
> M.
>
> On 09/24/2014 04:12 PM, Karl O. Pinc wrote:
>> On 09/24/2014 08:53:09 AM, Matija Brozović wrote:
>>> I actually inherited the job from a previous person, the schemas
>>> were introduced to separate access to the data from php framework,
>>> with
>>> different
>>> access roles defined for different species. This is how the database
>>> was
>>> planned.
>> To be sure I understand, the design is due to security?
>> You used it as a means of controlling authorization?
>>
>> (Just what the authorization is for, viewing data,
>> updating data, etc., isn't important.)
>>
>>> cheers,
>>> M.
>>>
>>> On 09/24/2014 03:44 PM, Karl O. Pinc wrote:
>>>> Why did you need to introduce separate schemas and
>>>> separate tables for different species?  What's the goal?
>>>>
>>>> On 09/24/2014 06:25:20 AM, Matija Brozović wrote:
>>>>> Hi all,
>>>>>
>>>>> I have a specific problem which I resolved but as always we need
>>> to
>>>>> strive for better solutions.
>>>>>
>>>>> I have postgres database with chado  with 4 separate database
>>>>> schemas(different species) and a public schema.
>>>>>
>>>>> Within the ND module I have a nd_reagent table and a table called
>>>>> treatment_reagent which links nd_reagent with some treatments(a
>>>>> custom
>>>>>
>>>>> need simple 3 column table).
>>>>>
>>>>> The problem I encountered was concerning the foreign key
>>> constraint
>>>>> referring to nd_reagent table from treatment_reagent table.
>>>>> It worked well with only one species, but when others were
>>>>> introduced(all in separate schemas) there was no foreign key
>>>>> references
>>>>> for nd_reagent in those other specific species schemas.
>>>>> Also there is some small number of records which are used by all
>>>>> schemas, but if I'd copy those in all schemas this is a problem of
>>>>> multiplication.
>>>>> If I would copy everything into public I would loose the origin of
>>>>> data
>>>>> for specific species within each schema.
>>>>>
>>>>> So what I did ...
>>>>>
>>>>> I copied the records being used by all species into
>>> public.nd_reagent
>>>>> and
>>>>> removed it from other species specific nd_reagent tables).
>>>>>
>>>>> I left the species specific records in their own nd_reagent
>>> tables,
>>>>> and created a new table in the public schema called all_nd_reagent
>>>>> and
>>>>>
>>>>> put in all the nd_reagent_ids.
>>>>>
>>>>> Then I created triggers (insert/update/delete) on all species
>>>>> specific
>>>>>
>>>>> nd_reagent tables, and the public nd_reagent table to keep this
>>> new
>>>>> all_nd_reagent table up to date.
>>>>>
>>>>> Linked all different species treatment_reagent tables foreign keys
>>> to
>>>>> this new table.
>>>>> this way I preserved the foreign key constraint.
>>>>>
>>>>> I hope my explanation is not too complicated.
>>>>>
>>>>> I would just like to know if you have any ideas for more simple
>>>>> solutions ?
>>>>>
>>>>> cheers
>>>>> Matija
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>> ------------------------------------------------------------------------------
>>>>> Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
>>>>> Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS
>>>>> Reports
>>>>> Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White
>>> paper
>>>>> Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog
>>> Analyzer
>>>>> http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/
>>>>> ostg.clktrk
>>>>> _______________________________________________
>>>>> Gmod-schema mailing list
>>>>> [hidden email]
>>>>> https://lists.sourceforge.net/lists/listinfo/gmod-schema
>>>>>
>>>>>
>>>>
>>>> Karl <[hidden email]>
>>>> Free Software:  "You don't pay back, you pay forward."
>>>>                     -- Robert A. Heinlein
>>
>>
>> Karl <[hidden email]>
>> Free Software:  "You don't pay back, you pay forward."
>>                    -- Robert A. Heinlein
>
> ------------------------------------------------------------------------------
> Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
> Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
> Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
> Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
> http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
> _______________________________________________
> Gmod-schema mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gmod-schema


------------------------------------------------------------------------------
Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
Reply | Threaded
Open this post in threaded view
|

Re: natural diversity nd_reagent multiple schemas common and specific data postgres

Karl O. Pinc
There's a number of ways to address your problem.
Some depend on having a new-ish version of postgres.

Inheritance is an option, but it seems a bit clunky
to use it this way.  It's not really the right approach.

http://www.postgresql.org/docs/9.3/static/ddl-inherit.html

Triggers are an option, per your existing solution.

Another approach would be to use a view.  The underlying
table would be inaccessible.  The SELECT part of the
view would call a function, declared with SECURITY DEFINER.
The function would grant SELECT access per row.
Triggers can be put on the view for insert/update/delete/etc.

Or there's ways to use views for row-level security
directly.  See:
http://www.postgresql.org/docs/9.3/static/rules-privileges.html

Usually, you address this sort of issue within the cms.

On 09/24/2014 09:19:05 AM, Matija Brozović wrote:

> One more reason was to keep the tables smaller and separated in this
> way.
>
> M.
> On 09/24/2014 04:15 PM, Matija Brozović wrote:
> > Exactly, for the purposes of our custom cms.
> >
> > M.
> >
> > On 09/24/2014 04:12 PM, Karl O. Pinc wrote:
> >> On 09/24/2014 08:53:09 AM, Matija Brozović wrote:
> >>> I actually inherited the job from a previous person, the schemas
> >>> were introduced to separate access to the data from php
> framework,
> >>> with
> >>> different
> >>> access roles defined for different species. This is how the
> database
> >>> was
> >>> planned.
> >> To be sure I understand, the design is due to security?
> >> You used it as a means of controlling authorization?
> >>
> >> (Just what the authorization is for, viewing data,
> >> updating data, etc., isn't important.)
> >>
> >>> cheers,
> >>> M.
> >>>
> >>> On 09/24/2014 03:44 PM, Karl O. Pinc wrote:
> >>>> Why did you need to introduce separate schemas and
> >>>> separate tables for different species?  What's the goal?
> >>>>
> >>>> On 09/24/2014 06:25:20 AM, Matija Brozović wrote:
> >>>>> Hi all,
> >>>>>
> >>>>> I have a specific problem which I resolved but as always we
> need
> >>> to
> >>>>> strive for better solutions.
> >>>>>
> >>>>> I have postgres database with chado  with 4 separate database
> >>>>> schemas(different species) and a public schema.
> >>>>>
> >>>>> Within the ND module I have a nd_reagent table and a table
> called
> >>>>> treatment_reagent which links nd_reagent with some treatments(a
> >>>>> custom
> >>>>>
> >>>>> need simple 3 column table).
> >>>>>
> >>>>> The problem I encountered was concerning the foreign key
> >>> constraint
> >>>>> referring to nd_reagent table from treatment_reagent table.
> >>>>> It worked well with only one species, but when others were
> >>>>> introduced(all in separate schemas) there was no foreign key
> >>>>> references
> >>>>> for nd_reagent in those other specific species schemas.
> >>>>> Also there is some small number of records which are used by
> all
> >>>>> schemas, but if I'd copy those in all schemas this is a problem
> of
> >>>>> multiplication.
> >>>>> If I would copy everything into public I would loose the origin
> of
> >>>>> data
> >>>>> for specific species within each schema.
> >>>>>
> >>>>> So what I did ...
> >>>>>
> >>>>> I copied the records being used by all species into
> >>> public.nd_reagent
> >>>>> and
> >>>>> removed it from other species specific nd_reagent tables).
> >>>>>
> >>>>> I left the species specific records in their own nd_reagent
> >>> tables,
> >>>>> and created a new table in the public schema called
> all_nd_reagent
> >>>>> and
> >>>>>
> >>>>> put in all the nd_reagent_ids.
> >>>>>
> >>>>> Then I created triggers (insert/update/delete) on all species
> >>>>> specific
> >>>>>
> >>>>> nd_reagent tables, and the public nd_reagent table to keep this
> >>> new
> >>>>> all_nd_reagent table up to date.
> >>>>>
> >>>>> Linked all different species treatment_reagent tables foreign
> keys
> >>> to
> >>>>> this new table.
> >>>>> this way I preserved the foreign key constraint.
> >>>>>
> >>>>> I hope my explanation is not too complicated.
> >>>>>
> >>>>> I would just like to know if you have any ideas for more simple
> >>>>> solutions ?
> >>>>>
> >>>>> cheers
> >>>>> Matija
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>
> ------------------------------------------------------------------------------
> >>>>> Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
> >>>>> Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI
> DSS
> >>>>> Reports
> >>>>> Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White
> >>> paper
> >>>>> Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog
> >>> Analyzer
> >>>>> http://pubads.g.doubleclick.net/gampad/clk?
> id=154622311&iu=/4140/
> >>>>> ostg.clktrk
> >>>>> _______________________________________________
> >>>>> Gmod-schema mailing list
> >>>>> [hidden email]
> >>>>> https://lists.sourceforge.net/lists/listinfo/gmod-schema
> >>>>>
> >>>>>
> >>>>
> >>>> Karl <[hidden email]>
> >>>> Free Software:  "You don't pay back, you pay forward."
> >>>>                     -- Robert A. Heinlein
> >>
> >>
> >> Karl <[hidden email]>
> >> Free Software:  "You don't pay back, you pay forward."
> >>                    -- Robert A. Heinlein
> >
> >
> ------------------------------------------------------------------------------
> > Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
> > Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS
> Reports
> > Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White
> paper
> > Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog
> Analyzer
> > http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/
> ostg.clktrk
> > _______________________________________________
> > Gmod-schema mailing list
> > [hidden email]
> > https://lists.sourceforge.net/lists/listinfo/gmod-schema
>
>
> ------------------------------------------------------------------------------
> Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
> Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS
> Reports
> Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
> Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
> http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/
> ostg.clktrk
> _______________________________________________
> Gmod-schema mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gmod-schema
>




Karl <[hidden email]>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

------------------------------------------------------------------------------
Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
Reply | Threaded
Open this post in threaded view
|

Re: natural diversity nd_reagent multiple schemas common and specific data postgres

Karl O. Pinc
In reply to this post by Matija Brozović
On 09/24/2014 09:53:06 AM, Matija Brozović wrote:
> On 09/24/2014 04:48 PM, Karl O. Pinc wrote:

> > Another approach would be to use a view.  The underlying
> > table would be inaccessible.  The SELECT part of the
> > view would call a function,

The key point here is that you can:


   SELECT ... FROM somefunction;


Karl <[hidden email]>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

------------------------------------------------------------------------------
Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
Reply | Threaded
Open this post in threaded view
|

Re: natural diversity nd_reagent multiple schemas common and specific data postgres

Karl O. Pinc
In reply to this post by Matija Brozović
Oops.  Bcc-ed the gmod-schema list, which the list does not like

Here is the message.


Karl <[hidden email]>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein
-------
On 09/24/2014 09:48:13 AM, Karl O. Pinc wrote:

> There's a number of ways to address your problem.
> Some depend on having a new-ish version of postgres.
>
> Inheritance is an option, but it seems a bit clunky
> to use it this way.  It's not really the right approach.
>
> http://www.postgresql.org/docs/9.3/static/ddl-inherit.html
>
> Triggers are an option, per your existing solution.
>
> Another approach would be to use a view.  The underlying
> table would be inaccessible.  The SELECT part of the
> view would call a function, declared with SECURITY DEFINER.
> The function would grant SELECT access per row.
> Triggers can be put on the view for insert/update/delete/etc.
>
> Or there's ways to use views for row-level security
> directly.  See:
> http://www.postgresql.org/docs/9.3/static/rules-privileges.html
>
> Usually, you address this sort of issue within the cms.
>
> On 09/24/2014 09:19:05 AM, Matija Brozović wrote:
> > One more reason was to keep the tables smaller and separated in
> this
> > way.
> >
> > M.
> > On 09/24/2014 04:15 PM, Matija Brozović wrote:
> > > Exactly, for the purposes of our custom cms.
> > >
> > > M.
> > >
> > > On 09/24/2014 04:12 PM, Karl O. Pinc wrote:
> > >> On 09/24/2014 08:53:09 AM, Matija Brozović wrote:
> > >>> I actually inherited the job from a previous person, the
> schemas
> > >>> were introduced to separate access to the data from php
> > framework,
> > >>> with
> > >>> different
> > >>> access roles defined for different species. This is how the
> > database
> > >>> was
> > >>> planned.
> > >> To be sure I understand, the design is due to security?
> > >> You used it as a means of controlling authorization?
> > >>
> > >> (Just what the authorization is for, viewing data,
> > >> updating data, etc., isn't important.)
> > >>
> > >>> cheers,
> > >>> M.
> > >>>
> > >>> On 09/24/2014 03:44 PM, Karl O. Pinc wrote:
> > >>>> Why did you need to introduce separate schemas and
> > >>>> separate tables for different species?  What's the goal?
> > >>>>
> > >>>> On 09/24/2014 06:25:20 AM, Matija Brozović wrote:
> > >>>>> Hi all,
> > >>>>>
> > >>>>> I have a specific problem which I resolved but as always we
> > need
> > >>> to
> > >>>>> strive for better solutions.
> > >>>>>
> > >>>>> I have postgres database with chado  with 4 separate database
> > >>>>> schemas(different species) and a public schema.
> > >>>>>
> > >>>>> Within the ND module I have a nd_reagent table and a table
> > called
> > >>>>> treatment_reagent which links nd_reagent with some
> treatments(a
> > >>>>> custom
> > >>>>>
> > >>>>> need simple 3 column table).
> > >>>>>
> > >>>>> The problem I encountered was concerning the foreign key
> > >>> constraint
> > >>>>> referring to nd_reagent table from treatment_reagent table.
> > >>>>> It worked well with only one species, but when others were
> > >>>>> introduced(all in separate schemas) there was no foreign key
> > >>>>> references
> > >>>>> for nd_reagent in those other specific species schemas.
> > >>>>> Also there is some small number of records which are used by
> > all
> > >>>>> schemas, but if I'd copy those in all schemas this is a
> problem
> > of
> > >>>>> multiplication.
> > >>>>> If I would copy everything into public I would loose the
> origin
> > of
> > >>>>> data
> > >>>>> for specific species within each schema.
> > >>>>>
> > >>>>> So what I did ...
> > >>>>>
> > >>>>> I copied the records being used by all species into
> > >>> public.nd_reagent
> > >>>>> and
> > >>>>> removed it from other species specific nd_reagent tables).
> > >>>>>
> > >>>>> I left the species specific records in their own nd_reagent
> > >>> tables,
> > >>>>> and created a new table in the public schema called
> > all_nd_reagent
> > >>>>> and
> > >>>>>
> > >>>>> put in all the nd_reagent_ids.
> > >>>>>
> > >>>>> Then I created triggers (insert/update/delete) on all species
> > >>>>> specific
> > >>>>>
> > >>>>> nd_reagent tables, and the public nd_reagent table to keep
> this
> > >>> new
> > >>>>> all_nd_reagent table up to date.
> > >>>>>
> > >>>>> Linked all different species treatment_reagent tables foreign
> > keys
> > >>> to
> > >>>>> this new table.
> > >>>>> this way I preserved the foreign key constraint.
> > >>>>>
> > >>>>> I hope my explanation is not too complicated.
> > >>>>>
> > >>>>> I would just like to know if you have any ideas for more
> simple
> > >>>>> solutions ?
> > >>>>>
> > >>>>> cheers
> > >>>>> Matija
> > >>>>>
> > >>>>>
> > >>>>>
> > >>>>>
> > >>>>>
> > >>>>>
> > >>>>>
> > >>>
> >
> ------------------------------------------------------------------------------
> > >>>>> Meet PCI DSS 3.0 Compliance Requirements with EventLog
> Analyzer
> > >>>>> Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI
> > DSS
> > >>>>> Reports
> > >>>>> Are you Audit-Ready for PCI DSS 3.0 Compliance? Download
> White
> > >>> paper
> > >>>>> Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog
> > >>> Analyzer
> > >>>>> http://pubads.g.doubleclick.net/gampad/clk?
> > id=154622311&iu=/4140/
> > >>>>> ostg.clktrk
> > >>>>> _______________________________________________
> > >>>>> Gmod-schema mailing list
> > >>>>> [hidden email]
> > >>>>> https://lists.sourceforge.net/lists/listinfo/gmod-schema
> > >>>>>
> > >>>>>
> > >>>>
> > >>>> Karl <[hidden email]>
> > >>>> Free Software:  "You don't pay back, you pay forward."
> > >>>>                     -- Robert A. Heinlein
> > >>
> > >>
> > >> Karl <[hidden email]>
> > >> Free Software:  "You don't pay back, you pay forward."
> > >>                    -- Robert A. Heinlein
> > >
> > >
> >
> ------------------------------------------------------------------------------
> > > Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
> > > Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS
> > Reports
> > > Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White
> > paper
> > > Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog
> > Analyzer
> > > http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/
> > ostg.clktrk
> > > _______________________________________________
> > > Gmod-schema mailing list
> > > [hidden email]
> > > https://lists.sourceforge.net/lists/listinfo/gmod-schema
> >
> >
> >
> ------------------------------------------------------------------------------
> > Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
> > Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS
> > Reports
> > Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White
> paper
> > Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog
> Analyzer
> > http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/
> > ostg.clktrk
> > _______________________________________________
> > Gmod-schema mailing list
> > [hidden email]
> > https://lists.sourceforge.net/lists/listinfo/gmod-schema
> >
>
>
>
>
> Karl <[hidden email]>
> Free Software:  "You don't pay back, you pay forward."
>                  -- Robert A. Heinlein
>
>

------------------------------------------------------------------------------
Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema