Re: String Translation

On Thursday, October 16, 2003, at 10:05 AM, Josh Berkus wrote:

> So your purpose is to eliminate all non-[0-9A-Za-z_] characters?

Yeah, and change them into "_".

> That's rather odd ... I've done search-and-replace using Perl on
> 30,000 large
> text fields in a couple minutes. Does this field have foriegn keys
> and/or
> several indexes on it?

Yes, here's the table declaration:

CREATE TABLE story_data_tile (
id NUMERIC(10,0) NOT NULL
DEFAULT
NEXTVAL('seq_story_data_tile'),
name VARCHAR(64) NOT NULL,
key_name VARCHAR(64) NOT NULL,
description VARCHAR(256),
element_data__id NUMERIC(10,0) NOT NULL,
object_instance_id NUMERIC(10,0) NOT NULL,
parent_id NUMERIC(10,0) NOT NULL,
hold_val NUMERIC(1,0) NOT NULL
DEFAULT 0
CONSTRAINT ck_sd_tile__hold_val
CHECK (hold_val IN (0,1)),

place NUMERIC(10,0) NOT NULL,
object_order NUMERIC(10,0) NOT NULL,
date_val TIMESTAMP,
short_val TEXT,
blob_val TEXT,
active NUMERIC(1,0) NOT NULL
DEFAULT 1
CONSTRAINT ck_sd_tile__active
CHECK (active IN (0,1)),

CONSTRAINT pk_story_data_tile__id PRIMARY KEY (id)
);

CREATE INDEX idx_story_data_tile__key_name ON
story_data_tile(LOWER(key_name));
CREATE INDEX fkx_story_instance__sd_tile ON
story_data_tile(object_instance_id);
CREATE INDEX fkx_element__sd_tile ON story_data_tile(element_data__id);
CREATE INDEX fkx_sc_tile__sd_tile ON story_data_tile(parent_id);

ALTER TABLE story_data_tile
ADD CONSTRAINT fk_story_instance__sd_tile FOREIGN KEY
(object_instance_id)
REFERENCES story_instance(id) ON DELETE CASCADE;

ALTER TABLE story_data_tile
ADD CONSTRAINT fk_sc_tile__sd_tile FOREIGN KEY (parent_id)
REFERENCES story_container_tile(id) ON DELETE CASCADE;

ALTER TABLE story_data_tile
ADD CONSTRAINT fk_at_data__sd_tile FOREIGN KEY (element_data__id)
REFERENCES at_data(id) ON DELETE CASCADE;

Perhaps I should drop the idx_story_data_tile__key_name index, make the
changes, and then add it again? This is important, because while my
database has only 2200 records, there will be Bricolage installations
with millions of records in this table.

> Well, the new substring can do substitutions, but not neatly in the
> s//g
> format the way Perl can. What about PL/perl?

Ah, in 7.4? I don't see making users recompile pg or add in PL/perl
just for a single, one-time upgrade script.

> Not necessarily. I think your execution time problem is not releated
to the
> queries themselves, but to some major database dependency.
>
> Wanna show me, since I'll soon be getting paid to tune Bric anyway?

Yeah, send me your ssh public keys and I'll give you a login.

Cheers,

David

--
David Wheeler AIM: dwTheory
david(at)kineticode(dot)com ICQ: 15726394
http://www.kineticode.com/ Yahoo!: dew7e
Jabber: Theory(at)jabber(dot)org
Kineticode. Setting knowledge in motion.[sm]