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.

In Postgres, unique constraints are implemented by unique indices (same is true of primary keys).  So, you can drop the index that was built for it - check your database directory for file names like "table_name_pkey" for the primary key index.  I forget what name is given unique indices but it should be obvious from the ls.

Then go into psql and drop the index, then make your new one.

You can add the column with "alter table add column domain varchar(100)"

I strongly recommend trying this on a test database first, though.

Why the "warning"?  This is for your private work, right?  I'm not going to wake up to a broken ACS or anything like that, I hope!  Only aD themselves get to do that to me! :)

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.

Use pgaccess. It's very nice and clean. It's part of the PostgreSQL distribution, but you must have Tcl/Tk installed and run Postgres with the "-i" flag.

This will cause PG to listen at TCP/IP port 5432 which means you should add an ipchains entry to only allow connections from localhost or some oteh security measure (PG has this in pg_hba.conf if I am not mistaken).

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)
Hey, yeah, your way is a lot better, why didn't I think of looking in pg_index?

We need a little FAQ of things like this...

I agree (about having a FAQ). I looked at what pgaccess does to find out how to see the source of a user-defined function since that's not easily found in the docs (I couldn't find).

Sounds like a job for the FAQ module.

Okay, procedure definitions, why not. What a pain to figure out what is being used by the db.

  1. Get unix user id of account that logs into the db --> $UID
  2. Then: select proname,prosrc from pg_proc where proowner = '$UID'

I'll try to come up with a few tcl pages to help the browsing.