Greetings, Attached is round #1. I've covered some of the "big" tables up the the point where I got lured away by the sunshine outside. I've added some foreign keys that will do nothing until you read: http://dev.mysql.com/doc/refman/4.1/en/innodb-foreign-key-constraints.html I'd like to add more database-level integrity checking, but the way some tables are structured, it is impossible. !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! DANGER: Do *not* run this script on your live database without backing it up!!!! Especially the parts that modify the users table; you might be surprised what you find when nickname is forced to be unique... !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! -- Cory R. King -------------- next part -------------- -- indexes and referential integrity! -- -- Cory R. King - July 16, 2006 -- -- Works against scoop.sql CVS version 1.228 -- Tested on an ancient MySQL 3.23.54... BEGIN; ALTER TABLE users MODIFY nickname varchar(50) NOT NULL; ALTER TABLE users MODIFY origemail varchar(50) NOT NULL; ALTER TABLE users ALTER origemail DROP DEFAULT; ALTER TABLE users MODIFY passwd varchar(12) NOT NULL; ALTER TABLE users ALTER passwd DROP DEFAULT; CREATE UNIQUE INDEX nickname_idx ON users(nickname); CREATE INDEX creation_time_idx ON users(creation_time); -- NewUser.pm -> check_email() CREATE INDEX realemail_idx ON users(realemail); CREATE INDEX origemail ON users(origemail); COMMIT; BEGIN; ALTER TABLE stories MODIFY aid int NOT NULL; -- This *DOES NOT DO ANYTHING* on MySQL 3.x.... Scary! -- http://dev.mysql.com/doc/refman/4.1/en/data-type-defaults.html ALTER TABLE stories ALTER aid integer NOT NULL; ALTER TABLE stories MODIFY title varchar(100) NOT NULL; ALTER TABLE stories ALTER title DROP DEFAULT; ALTER TABLE stories MODIFY introtext text NOT NULL; -- This will not get enforced by early versions of MySQL and non InnoDB tables... -- However, it will stay in the schema if you upgrade (??) -- I've made it restrictive - if you nuke a user and didn't clean up the stories -- you'll get an exception. A cascading delete would "auto-clean" the stories, but -- I suspect the code and the admin should to the cleaup, not the database. ALTER TABLE stories ADD CONSTRAINT users_aid_fk FOREIGN KEY (aid) REFERENCES users(uid) ON DELETE RESTRICT; CREATE INDEX aid_index ON stories(aid); COMMIT; BEGIN; ALTER TABLE pollanswers ADD CONSTRAINT users_aid_fk FOREIGN KEY (aid) REFERENCES users(uid) ON DELETE RESTRICT; ALTER TABLE pollvoters ADD CONSTRAINT users_uid_fk FOREIGN KEY (uid) REFERENCES users(uid) ON DELETE RESTRICT; -- There is a mysterious "id" field in pollvoters that is the nickname of the voter. -- It gets referenced in one INSERT statement, but looks like it is -- otherwise never used..... COMMIT; BEGIN; ALTER TABLE comments ADD CONSTRAINT users_uid_fk FOREIGN KEY (uid) REFERENCES users(uid) ON DELETE RESTRICT; COMMIT; BEGIN; -- for _get_story_userviews() CREATE INDEX sid_idx ON viewed_stories(sid); -- This will do a cascading delete on the right system, not throw an error ALTER TABLE viewed_stories ADD CONSTRAINT users_uid_fk FOREIGN KEY (uid) REFERENCES users(uid) ON DELETE CASCADE; COMMIT; -- As for sessions, while it is tempting to index last_update -- it only gets called from a cron job and I bet -- the query optimizer will just sequential scan the table -- anyway... However, this should be tested on a loaded site -- and not based on my hunch here :-) BEGIN; -- This will do a cascading delete on the right system, not throw an error ALTER TABLE userprefs ADD CONSTRAINT users_uid_fk FOREIGN KEY (uid) REFERENCES users(uid) ON DELETE CASCADE; -- It is a varchar(20) in pref_items... ALTER TABLE userprefs MODIFY prefname varchar(20) NOT NULL; ALTER TABLE userprefs ADD CONSTRAINT item_fk FOREIGN KEY (prefname) REFERENCES pref_items(prefname) ON DELETE CASCADE; CREATE INDEX user_idx ON userprefs(uid); COMMIT; BEGIN; ALTER TABLE calendars MODIFY title varchar(50) NOT NULL; -- the "owner" column of calanders is either a uid, or 0 if it is global. -- Either way, there isn't a bit of scoop code inserting null values... -- so it *must* be something -- -- Sadly, since 'owner' is has two meanings, we can't enforce any -- referential integrity against users(uid)... ALTER TABLE calendars MODIFY owner int NOT NULL; ALTER TABLE event_watch ADD CONSTRAINT users_uid_fk FOREIGN KEY (uid) REFERENCES users(uid) ON DELETE CASCADE; ALTER TABLE event_watch ADD CONSTRAINT events_fk FOREIGN KEY (eid) REFERENCES events(eid) ON DELETE CASCADE; ALTER TABLE event_properties ADD CONSTRAINT events_fk FOREIGN KEY (eid) REFERENCES events(eid) ON DELETE CASCADE; -- this might be risky, because the code might be lazy, but that is -- why they invented FK's in the first place! ALTER TABLE event_properties ADD CONSTRAINT events_prop_fk FOREIGN KEY (property) REFERENCES event_property_items(property) ON DELETE RESTRICT; ALTER TABLE event_rsvp ADD CONSTRAINT events_fk FOREIGN KEY (eid) REFERENCES events(eid) ON DELETE CASCADE; ALTER TABLE event_rsvp ADD CONSTRAINT users_fk FOREIGN KEY (uid) REFERENCES users(uid) ON DELETE CASCADE; ALTER TABLE event_story ADD CONSTRAINT events_fk FOREIGN KEY (eid) REFERENCES events(eid) ON DELETE CASCADE; ALTER TABLE event_story ADD CONSTRAINT stories_fk FOREIGN KEY (sid) REFERENCES stories(sid) ON DELETE CASCADE; CREATE INDEX sid_idx ON event_story(sid); CREATE INDEX eid_idx ON event_story(eid); ALTER TABLE calendar_link ADD CONSTRAINT events_fk FOREIGN KEY (eid) REFERENCES events(eid) ON DELETE RESTRICT; ALTER TABLE calendar_link ADD CONSTRAINT calendar_fk FOREIGN KEY (cal_id) REFERENCES calendars(cal_id) ON DELETE CASCADE; COMMIT; BEGIN; CREATE INDEX last_visit_idx ON whos_online(last_visit); COMMIT; BEGIN; ALTER TABLE commentratings ADD CONSTRAINT users_fk FOREIGN KEY (uid) REFERENCES users(uid) ON DELETE CASCADE; ALTER TABLE commentratings ADD CONSTRAINT comments_fk FOREIGN KEY (cid) REFERENCES comments(cid) ON DELETE CASCADE; -- Why is sid in commentratings? We can get that from comments(cid) -> sid COMMIT; BEGIN; ALTER TABLE section_perms ADD CONSTRAINT sections_fk FOREIGN KEY (section) REFERENCES sections(section) ON DELETE CASCADE; COMMIT; BEGIN; ALTER TABLE post_throttle ADD CONSTRAINT users_fk FOREIGN KEY (uid) REFERENCES users(uid) ON DELETE CASCADE; COMMIT;