Forum OpenACS Q&A: Warning: I'm about to hack OpenACS for Multiple Domains

I have a customer who wants to run two websites to hawk essentially the same set of overpriced wares. Since running one of these sites is painful enough, I want to make it one site with a few configuration differences. Here is how I wish to do this:

• Change the users table to include a new column and a different uniqueness constraint:

  email varchar(100) NOT NULL, domain varchar(100) NOT NULL, unique (email,domain)

• Rewrite ad_parameter to get values from an nsv array keyed to the current domain. I know this will not work for startup parameters, but some of the look and feel parameters should be enough to fool most users.

How do I make the database change? I already have lots of data. Multi-step instructions I have seen only seem to apply to tables without external constraints.

Maybe it will be easier to drop the uniqueness constraint from the email column, and hack the user-new scripts to enforce my desired uniqueness of email,domain.

Yes! I discovered this nice feature after posting here. I read the docs. However, I was trying to figure out how to find the name of the index, which I never had to do. Thanks for the info. My other database is Interbase which has a SHOW INDEX command. Very nice for the dimwitted, which is the reason for the WARNING above. You never know what the actions of an idiot might lead to.

Is there not a system table that holds info like this, there must be? What I need is a AOLserver style object browser for pg.

Because AOLserver3.0 has a broken ns_getcsv function, I have to run a copy of 2.3.3, which has built in table browsing under /NS/Db/. There is no index browsing, but you do get system tables listed. After using Don's ls method above, I tried the following with success:

1. Create a table with a unique index:

   create table my_table ( user_id integer not null primary key, email varchar(100) not null, unique(email))

2. List indexes on table of interest (my_table):

   select * from pg_indexes where tablename = 'my_table'

   This gave me indexes named my_table_pkey and my_table_email_key.

3. Insert some data.
4. Alter table: alter table my_table add domain varchar(100) not null
5. Insert some domain data: update my_table set domain = 'mydomain.com'
6. Drop unique index on email: drop index my_table_email_key
7. Create new unique index: create unique index my_table_email_domain_key on my_table(email,domain)

Sounds like a job for the FAQ module.