Re: [SQL] Composite UNIQUE across two tables?
create a function that, given the siteid returns the sitegroupid create a unique index on username and that function >>> "Jamie Tufnell" <[EMAIL PROTECTED]> 2008-03-06 20:04 >>> Hi, I'm remodeling our authentication tables at the moment to accomodate future changes. I've hit a wall and thought I'd ask here for some help. Hopefully I'm just missing something simple. I'll do my best to explain the domain.. We have users, sites, and groups of sites. Usernames should be unique within a site group. Each user is tied to one, and only one, site. Each site belongs to exactly one group, and one group can contain many sites. (some sites are actually groupless in reality, but at one point in my design I figured it might make it easier to force them to be in a group of 1 -- perhaps that's part of my problem?). A user has implied permission to access any site in their group of sites. So far this is what I have: CREATE TABLE site_groups ( id serial, name varchar(120) not null, PRIMARY KEY (id) ); CREATE TABLE sites ( id serial, site_group_id integer not null, name varchar(120) not null, PRIMARY KEY (id), FOREIGN KEY (site_group_id) REFERENCES site_groups (id) ); CREATE TABLE users ( id serial, site_id integer not null, username varchar(120) not null, PRIMARY KEY (id), FOREIGN KEY (site_id) REFERENCES sites (id) ); The problem is the "Usernames should be unique within a site group." I could include a site_group_id FK in the users table, and put a UNIQUE(username, site_group_id), but it feels like I'm doing something wrong here since the user's site_group_id is already implied by the user's site_id. When users login I get their username, password and site name. Without a UNIQUE constraint on username+site_group_id, it's possible I'll get more than one user id matching the same login information which shouldn't happen. I suppose I could write a trigger to enforce this uniqueness, but it just feels like I'm probably doing something wrong elsewhere. Any help/abuse greatly appreciated :-) Cheers, J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql
Re: [SQL] Composite UNIQUE across two tables?
I haven't tested these two statements, but I'm using exactly this
concept on some tables myself.
My equivalent of your users table contains some 3,000,000 records.
My equivalent of your sites table contains some 150,000 records.
And it works fine...
CREATE OR REPLACE FUNCTION "fnc_idx_sitegroupid" (p_siteid
sites.id%TYPE) RETURNS site_groups.id%TYPE AS
$body$
DECLARE
v_sitegroupid site_groups.id%TYPE ;
BEGIN
SELECT site_group_id INTO v_sitegroupid FROM sites WHERE id =
p_siteid;
RETURN v_sitegroupid;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY
INVOKER;
CREATE UNIQUE INDEX "users_unq" ON "users"
USING btree ("username", (fnc_idx_sitegroupid(site_id)));
>>> Sébastien Meudec <[EMAIL PROTECTED]> 2008-03-07 9:40 >>>
Hi Bart,
I'm following this topic with interest.
Could you describe me more how you design an unique index with both a
column
name and a function name by an example
Thx.
Sebastien
De : [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
De la part de Bart Degryse
Envoyé : vendredi 7 mars 2008 08:46
À : Jamie Tufnell; pgsql-sql@postgresql.org
Objet : Re: [SQL] Composite UNIQUE across two tables?
create a function that, given the siteid returns the sitegroupid
create a unique index on username and that function
>>> "Jamie Tufnell" <[EMAIL PROTECTED]> 2008-03-06 20:04 >>>
Hi,
I'm remodeling our authentication tables at the moment to accomodate
future changes. I've hit a wall and thought I'd ask here for some
help. Hopefully I'm just missing something simple.
I'll do my best to explain the domain..
We have users, sites, and groups of sites.
Usernames should be unique within a site group.
Each user is tied to one, and only one, site.
Each site belongs to exactly one group, and one group can contain
many sites. (some sites are actually groupless in reality, but at
one point in my design I figured it might make it easier to force
them to be in a group of 1 -- perhaps that's part of my problem?).
A user has implied permission to access any site in their group of
sites.
So far this is what I have:
CREATE TABLE site_groups (
id serial,
name varchar(120) not null,
PRIMARY KEY (id)
);
CREATE TABLE sites (
id serial,
site_group_id integer not null,
name varchar(120) not null,
PRIMARY KEY (id),
FOREIGN KEY (site_group_id) REFERENCES site_groups (id)
);
CREATE TABLE users (
id serial,
site_id integer not null,
username varchar(120) not null,
PRIMARY KEY (id),
FOREIGN KEY (site_id) REFERENCES sites (id)
);
The problem is the "Usernames should be unique within a site group."
I could include a site_group_id FK in the users table, and put a
UNIQUE(username, site_group_id), but it feels like I'm doing
something wrong here since the user's site_group_id is already implied
by the user's site_id.
When users login I get their username, password and site name.
Without a UNIQUE constraint on username+site_group_id, it's possible
I'll get more than one user id matching the same login information
which shouldn't happen.
I suppose I could write a trigger to enforce this uniqueness, but it
just feels like I'm probably doing something wrong elsewhere.
Any help/abuse greatly appreciated :-)
Cheers,
J.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sq
l
Re: [SQL] Composite UNIQUE across two tables?
Hi Bart, I'm following this topic with interest. Could you describe me more how you design an unique index with both a column name and a function name by an example Thx. Sebastien De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Bart Degryse Envoyé : vendredi 7 mars 2008 08:46 À : Jamie Tufnell; pgsql-sql@postgresql.org Objet : Re: [SQL] Composite UNIQUE across two tables? create a function that, given the siteid returns the sitegroupid create a unique index on username and that function >>> "Jamie Tufnell" <[EMAIL PROTECTED]> 2008-03-06 20:04 >>> Hi, I'm remodeling our authentication tables at the moment to accomodate future changes. I've hit a wall and thought I'd ask here for some help. Hopefully I'm just missing something simple. I'll do my best to explain the domain.. We have users, sites, and groups of sites. Usernames should be unique within a site group. Each user is tied to one, and only one, site. Each site belongs to exactly one group, and one group can contain many sites. (some sites are actually groupless in reality, but at one point in my design I figured it might make it easier to force them to be in a group of 1 -- perhaps that's part of my problem?). A user has implied permission to access any site in their group of sites. So far this is what I have: CREATE TABLE site_groups ( id serial, name varchar(120) not null, PRIMARY KEY (id) ); CREATE TABLE sites ( id serial, site_group_id integer not null, name varchar(120) not null, PRIMARY KEY (id), FOREIGN KEY (site_group_id) REFERENCES site_groups (id) ); CREATE TABLE users ( id serial, site_id integer not null, username varchar(120) not null, PRIMARY KEY (id), FOREIGN KEY (site_id) REFERENCES sites (id) ); The problem is the "Usernames should be unique within a site group." I could include a site_group_id FK in the users table, and put a UNIQUE(username, site_group_id), but it feels like I'm doing something wrong here since the user's site_group_id is already implied by the user's site_id. When users login I get their username, password and site name. Without a UNIQUE constraint on username+site_group_id, it's possible I'll get more than one user id matching the same login information which shouldn't happen. I suppose I could write a trigger to enforce this uniqueness, but it just feels like I'm probably doing something wrong elsewhere. Any help/abuse greatly appreciated :-) Cheers, J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sq l -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] UPDATE .. FROM
I'm kind of stuck as to why postgresql doesn't understand what I mean in the
following queries:
UPDATE tag_data td SET td.usage_counter = td.usage_counter + 1 FROM
tag_list_tag_data ltd WHERE ltd.tag_id = td.id AND ltd.id = 102483;
ERROR: column "td" of relation "tag_data" does not exist
LINE 1: UPDATE tag_data td SET td.usage_counter = td.usage_counter +...
^
I tried without aliases:
UPDATE tag_data SET tag_data.usage_counter = tag_data.usage_counter + 1 FROM
tag_list_tag_data ltd WHERE ltd.tag_id = tag_data.id AND ltd.id = 102483;
ERROR: column "tag_data" of relation "tag_data" does not exist
LINE 1: UPDATE tag_data SET tag_data.usage_counter = tag_data.usage_...
^
Without a table specifier the error is understandable:
UPDATE tag_data SET usage_counter = usage_counter + 1 FROM tag_list_tag_data
ltd WHERE ltd.tag_id = tag_data.id AND ltd.id = 102483;
ERROR: column reference "usage_counter" is ambiguous
LINE 1: UPDATE tag_data SET usage_counter = usage_counter + 1 FROM t...
^
The non-FROM form works:
UPDATE tag_data SET usage_counter = usage_counter + 1 WHERE id IN (SELECT
tag_id FROM tag_list_tag_data WHERE id = 102483);
UPDATE 1
Table schemata:
# \d+ tag_data
Table "public.tag_data"
Column | Type |
Modifiers | Description
---++---+-
id| bigint | not null default
nextval('tag_data_id_seq'::regclass) |
ns_id | bigint | not
null |
name | character varying(128) | not
null |
usage_counter | bigint | not null default
0|
Indexes:
"tag_data_pkey" PRIMARY KEY, btree (id)
"tag_data_ns_id_key" UNIQUE, btree (ns_id, name)
# \d+ tag_list_tag_data
Table "public.tag_list_tag_data"
Column | Type |
Modifiers| Description
---+++-
id| bigint | not null default
nextval('tag_list_tag_data_id_seq'::regclass) |
list_id | bigint | not
null |
tag_id| bigint | not
null |
usage_counter | bigint | not null default
0 |
Indexes:
"tag_list_tag_data_pkey" PRIMARY KEY, btree (id)
"tag_list_tag_data_list_id_key" UNIQUE, btree (list_id, tag_id)
This is 8.3.0.
Thanks
--
Markus Bertheau
Blog: http://www.bluetwanger.de/blog/
Re: [SQL] RETURN QUERY generates error
> The error sounds suspiciously like what would happen if you tried to > use RETURN QUERY in a pre-8.3 version. > > regards, tom lane > Thanks a lot to all. Actually there were a mass of errors in my function. Now I rewrite it so as it works. It would be much more complicated to achieve my goal without your advices. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] UPDATE .. FROM
"Markus Bertheau" <[EMAIL PROTECTED]> writes: > I'm kind of stuck as to why postgresql doesn't understand what I mean in the > following queries: > UPDATE tag_data td SET td.usage_counter = td.usage_counter + 1 FROM > tag_list_tag_data ltd WHERE ltd.tag_id = td.id AND ltd.id = 102483; > ERROR: column "td" of relation "tag_data" does not exist You aren't supposed to specify a table name (nor alias) for a target variable in a SET clause. It's useless (since you can't update more than one table) and it's ambiguous because of the possibility of composite fields. What you want is something like UPDATE tag_data td SET usage_counter = td.usage_counter + 1 FROM ... regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Composite UNIQUE across two tables?
Hi Ray, On 3/7/08, Ray Madigan <[EMAIL PROTECTED]> wrote: > How I think about it. > > A user has access to a site and all of the sites within the site group that > the site is in. > > if you reword your condition > > A user has access to all of the sites in a site group with a default defined > by site_id. Then there is no problem having both variables in the table. One field in the users table (site_id) implicitly ties the user to a site_group_id. One field in the users table (site_group_id) explicitly ties the user to a site_group_id. The problem I have (or had.. read below) was enforcing that those site_group_id's are equal. > Also, you have to trade off the cost of the table join to get the group_id > in all of the queries as opposed to the extra integer required. My thought > is that the extra Integer is small compared to the number of wueries that > have to run and would then adopt the second wording of the constraint. > > Even if you come up with an alternative, composite key you will still have > to deal with all of the table joins. The table joins isn't a big deal, but > it is unnecessary. When I asked about a composite FK in my previous message, I'd planned to use it in addition to your solution (not in place of)... the idea being to solve the problem mentioned above. What I was thinking is something like this .. I'm interested to hear your thoughts on this: users table: FOREIGN KEY (site_id, site_group_id) REFERENCES sites (id, site_group_id) sites table: UNIQUE (id, site_group_id) Assuming that's going to work... then I could use your suggestion of adding site_group_id to the users table. As you said, that will make writing SELECTs a lot simpler. How would I handle INSERTs / UPDATEs though without having to always specify both fields? I have a feeling it will require some functions being called by triggers / default values. That's all doable, but I wonder if there's a way of expressing these relationships without having to duplicate site_group_id. Cheers, J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Composite UNIQUE across two tables?
Hi Bart,
On 3/7/08, Bart Degryse <[EMAIL PROTECTED]> wrote:
> I haven't tested these two statements, but I'm using exactly this
> concept on some tables myself.
> My equivalent of your users table contains some 3,000,000 records.
> My equivalent of your sites table contains some 150,000 records.
> And it works fine...
>
> CREATE OR REPLACE FUNCTION "fnc_idx_sitegroupid" (p_siteid
> sites.id%TYPE) RETURNS site_groups.id%TYPE AS
> $body$
> DECLARE
> v_sitegroupid site_groups.id%TYPE ;
> BEGIN
> SELECT site_group_id INTO v_sitegroupid FROM sites WHERE id =
> p_siteid;
> RETURN v_sitegroupid;
> END;
> $body$
> LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY
> INVOKER;
>
> CREATE UNIQUE INDEX "users_unq" ON "users"
> USING btree ("username", (fnc_idx_sitegroupid(site_id)));
Thank you for your suggestion and example! I really like this idea
but I haven't been able to get it to work.
When I try to create the index I get the following error:
ERROR: functions in index expression must be marked IMMUTABLE
After consulting the docs
(http://www.postgresql.org/docs/8.2/static/sql-createfunction.html)
I get the impression I shouldn't declare this function IMMUTABLE since
it queries the database? It seems to me it should be STABLE.
Out of curiosity, I declared it IMMUTABLE and it worked for the
purposes of my small, isolated test,.
Am I opening myself up to problems by doing this?
Cheers,
J.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Composite UNIQUE across two tables?
Hi Jorge, On 3/7/08, Jorge Godoy <[EMAIL PROTECTED]> wrote: > Em Thursday 06 March 2008 22:10:14 Jamie Tufnell escreveu: > > I'm not sure how I would enforce that the site_group_id > > added to the users table would correspond correctly with > > the site_id (as per the sites table). Perhaps I would make a > > composite foreign key? > > Or you manage site_group_id from a trigger. When the user creates / chooses > a site_id and updates/inserts this information then you fill site_group_id > with a trigger. This way you guarantee the correctness of this parameter. Hmm. So your advice would be to duplicate the site_group_id field into the users table as well? That looks to be the consensus.. so I'm not sure why it feels like I'm doing something wrong :-) Thanks for your input! J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] plpythonu + os.spawnv
Dear all,
I'm not sure if I'm at the right place to ask my question so please excuse me
if I'm not.
I'm trying to spawn a process from plpythonu using psql 8.1.11. The code is
working fine from Python shell but if I put it into a plpythonu function it
seems like the process isn't starting at all.
The code I'm using is (with additional comment on the important parts):
CREATE OR REPLACE FUNCTION top_inline_query( VARCHAR( 50 ), TEXT )
RETURNS TEXT
AS $$
import os
import re
import sets
import random
def f2vars( string ):
vars_re = re.compile( r'[?][^-:,\]\[]*' )
all_vars = vars_re.findall( string )
set_vars = sets.Set( all_vars )
for i in all_vars:
if i.startswith( '?_' ):
try:
set_vars.remove( i )
except:
pass
return list( set_vars )
''' THE SPAWN FUNCTION '''
def run(program, *args):
return os.spawnv(os.P_WAIT, program, (program,) + args)
def f2payser( query, module, path ):
''' THE PROCESS TO BE RUN '''
florahome = '/path/to/flora2/runflora' #where runflora is a shell script
that runs a shell-like environment (take a look at http://flora.sf.net)
quote_re = re.compile( r"['][^']*[']" )
quotes = quote_re.findall( query )
for i in quotes:
cp = i.replace( ' ', r'\s' )
query = query.replace( i, cp )
query = query[ :-1 ].replace( ' ', '' )
query_vars = f2vars( query )
#print query_vars
vars_print = ''
for i in query_vars:
vars_print += ",%write('" + i + "=')@_io,%write(" + i + ")@_io,[EMAIL
PROTECTED]"
randfilename = path + str( random.random() )
while os.path.exists( randfilename ):
randfilename = path + str( random.random() )
'''THE QUERY STRING TO BE PASSED TO FLORA'''
query_str = "-e \"['" + module + "'].\" -e \"%tell('" + randfilename
+ "')@_io," + query + vars_print + ",[EMAIL PROTECTED]" -e \"_halt.\""
plpy.info( florahome + ' ' + str( query_str ) )
run(florahome, query_str)
vars_re = re.compile( '([?].*)[=](.*)')
result = {}
results = []
warning = False
try:
file = open( randfilename, 'r' )
except:
file = []
for i in file:
val_pair = vars_re.findall( i )
if len( val_pair ) == 1:
if not ( val_pair[ 0 ][ 1 ].startswith( '(' ) and val_pair[ 0 ][
1 ].endswith( ')' ) and val_pair[ 0 ][ 1 ].find( '?_' ) != -1 ):
result[ val_pair[ 0 ][ 0 ] ] = val_pair[ 0 ][ 1 ]
else:
warning = True
elif val_pair == []:
if result != {}:
if not warning:
results.append( result )
result = {}
warning = False
else:
raise ValueError,'Syntax error!'
try:
os.remove( randfilename )
except:
pass
res_copy = results[ : ]
for i in res_copy:
results.remove( i )
if not i in results:
results.append( i )
return results
project = args[ 0 ]
query = args[ 1 ]
'''THE ORIGINAL QUERY COMMENTED OUT '''
#onto_query = 'SELECT top_export_flora2_ontology( \'%s\' ) AS ontology;' %
project
#ontology = plpy.execute( onto_query )[ 0 ][ 'ontology' ]
ontology = 'markus:person[ name->markus, surename->schatten ].\n'
path = '/path/to/chmoded/directory/to/store/files/in'
randfilename = path + str( int( random.random() * 100 ) ) + '.flr'
while os.path.exists( randfilename ):
randfilename = path + str( int( random.random() * 100 ) ) + '.flr'
onto_file = open( randfilename, 'w' )
onto_file.write( ontology )
onto_file.close()
os.chmod( randfilename, 0755 )
module = randfilename[ :-4 ]
results = f2payser( query, module, path )
'''CLEAN UP'''
os.remove( randfilename )
return str( results )
$$
LANGUAGE plpythonu;
The funny thing is that the same code (as said before) works fine when called
from a Python environment so my question is (if I'm not missing something in
the code) is there a difference in handling spawned processes in plpthonu
and "normal" python?
The query passed to FLORA reads an ontology file queries it and writes the
results to another file that is than read by the plpythonu function. I tried
a lot of different approaches but none of them seems to be starting the FLORA
process at all. It seem like plpythonu just runs over the spawn function
doing nothing continuing the execution of the rest of the code.
Any help would be very appreciated because I'm running out of ideas ;)
Best regards,
--
Markus Schatten, MSc
Faculty of Organization and Informatics
Varaždin, Croatia
e-mail: [EMAIL PROTECTED]
http://www.foi.hr
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
