[Pypi-checkins] r957 - trunk/pypi/tools

richard python-checkins at python.org
Tue Aug 23 08:36:12 CEST 2011


Author: richard
Date: Tue Aug 23 08:36:12 2011
New Revision: 957

Added:
   trunk/pypi/tools/create_all_fks.sql
   trunk/pypi/tools/drop_all_fks.sql
Log:
sql from Andy Todd for managing FKs when attempting to import a package database dump; assumes older redundant FKs have already been dropped

Added: trunk/pypi/tools/create_all_fks.sql
==============================================================================
--- (empty file)
+++ trunk/pypi/tools/create_all_fks.sql	Tue Aug 23 08:36:12 2011
@@ -0,0 +1,55 @@
+begin;
+-- OpenID tables
+
+ALTER TABLE openids ADD CONSTRAINT openids_name_fkey FOREIGN KEY (name) REFERENCES users (name) ON DELETE CASCADE;
+
+ALTER TABLE openid_stypes ADD CONSTRAINT openid_stypes_id_fkey FOREIGN KEY (id) REFERENCES openid_sessions ON DELETE CASCADE;
+
+ALTER TABLE cookies ADD CONSTRAINT cookies_name_fkey FOREIGN KEY (name) REFERENCES users ON DELETE CASCADE;
+
+ALTER TABLE sshkeys ADD CONSTRAINT sshkeys_name_fkey FOREIGN KEY (name) REFERENCES users ON DELETE CASCADE;
+
+ALTER TABLE rego_otk ADD CONSTRAINT rego_otk_name_fkey FOREIGN KEY (name) REFERENCES users ON DELETE CASCADE;
+
+ALTER TABLE journals ADD CONSTRAINT journals_submitted_by_fkey FOREIGN KEY (submitted_by) REFERENCES users ON DELETE CASCADE;
+
+ALTER TABLE cheesecake_subindices ADD CONSTRAINT cheesecake_subindices_main_index_id_fkey FOREIGN KEY (main_index_id) REFERENCES cheesecake_main_indices;
+
+ALTER TABLE releases ADD CONSTRAINT releases_name_fkey FOREIGN KEY (name) REFERENCES packages ON DELETE CASCADE;
+
+ALTER TABLE releases ADD CONSTRAINT releases_cheesecake_installability_id_fkey FOREIGN KEY (cheesecake_installability_id) REFERENCES cheesecake_main_indices; 
+
+ALTER TABLE releases ADD CONSTRAINT releases_cheesecake_documentation_id_fkey FOREIGN KEY (cheesecake_documentation_id) REFERENCES cheesecake_main_indices;
+
+ALTER TABLE releases ADD CONSTRAINT releases_cheesecake_code_kwalitee_id_fkey FOREIGN KEY (cheesecake_code_kwalitee_id) REFERENCES cheesecake_main_indices; 
+
+ALTER TABLE release_classifiers ADD CONSTRAINT release_classifiers_trove_id_fkey FOREIGN KEY (trove_id) REFERENCES trove_classifiers;
+
+ALTER TABLE release_classifiers ADD CONSTRAINT release_classifiers_name_fkey FOREIGN KEY (name, version) REFERENCES releases (name, version);
+
+ALTER TABLE release_dependencies ADD CONSTRAINT release_dependencies_name_fkey FOREIGN KEY (name, version) REFERENCES releases (name, version) ON UPDATE CASCADE;
+
+ALTER TABLE release_files ADD CONSTRAINT release_files_name_fkey FOREIGN KEY (name, version) REFERENCES releases (name, version) ON UPDATE CASCADE;
+
+ALTER TABLE release_urls ADD CONSTRAINT release_urls_name_fkey FOREIGN KEY (name, version) REFERENCES releases (name, version) ON UPDATE CASCADE;
+
+ALTER TABLE description_urls ADD CONSTRAINT description_urls_name_fkey FOREIGN KEY (name, version) REFERENCES releases (name, version) ON UPDATE CASCADE;
+
+ALTER TABLE roles ADD CONSTRAINT roles_user_name_fkey FOREIGN KEY (user_name) REFERENCES users;
+ALTER TABLE roles ADD CONSTRAINT roles_package_name_fkey FOREIGN KEY (package_name) REFERENCES packages ON UPDATE CASCADE;
+
+ALTER TABLE mirrors ADD CONSTRAINT mirrors_user_name_fkey FOREIGN KEY (user_name) REFERENCES users;
+
+ALTER TABLE ratings ADD CONSTRAINT ratings_user_name_fkey FOREIGN KEY (user_name) REFERENCES users ON DELETE CASCADE;
+
+ALTER TABLE ratings ADD CONSTRAINT ratings_name_fkey FOREIGN KEY (name, version) REFERENCES releases ON UPDATE CASCADE ON DELETE CASCADE;
+
+ALTER TABLE comments ADD CONSTRAINT comments_rating_fkey FOREIGN KEY (rating) REFERENCES ratings (id) ON DELETE CASCADE;
+
+ALTER TABLE comments ADD CONSTRAINT comments_user_name_fkey FOREIGN KEY (user_name) REFERENCES users ON DELETE CASCADE;
+
+ALTER TABLE comments ADD CONSTRAINT comments_in_reply_to_fkey FOREIGN KEY (in_reply_to) REFERENCES comments ON DELETE CASCADE;
+
+ALTER TABLE comments_journal ADD CONSTRAINT comments_journal_submitted_by_fkey FOREIGN KEY (submitted_by) REFERENCES users ON DELETE CASCADE;
+
+ALTER TABLE comments_journal ADD CONSTRAINT comments_journal_name_fkey FOREIGN KEY (name, version) REFERENCES releases ON UPDATE CASCADE ON DELETE CASCADE;

Added: trunk/pypi/tools/drop_all_fks.sql
==============================================================================
--- (empty file)
+++ trunk/pypi/tools/drop_all_fks.sql	Tue Aug 23 08:36:12 2011
@@ -0,0 +1,35 @@
+/*
+
+File Name  : drop_all_fks.sql
+Description: Drop all foreign keys for a PYPI database to allow pg_restore to
+             be run and clone the database
+*/
+
+ALTER TABLE openids DROP CONSTRAINT openids_name_fkey;
+ALTER TABLE openid_stypes DROP CONSTRAINT openid_stypes_id_fkey;
+ALTER TABLE cookies DROP CONSTRAINT cookies_name_fkey;
+ALTER TABLE sshkeys DROP CONSTRAINT sshkeys_name_fkey;
+ALTER TABLE rego_otk DROP CONSTRAINT rego_otk_name_fkey;
+ALTER TABLE journals DROP CONSTRAINT journals_submitted_by_fkey;
+ALTER TABLE cheesecake_subindices DROP CONSTRAINT cheesecake_subindices_main_index_id_fkey;
+ALTER TABLE releases DROP CONSTRAINT releases_name_fkey;
+ALTER TABLE releases DROP CONSTRAINT releases_cheesecake_installability_id_fkey;
+ALTER TABLE releases DROP CONSTRAINT releases_cheesecake_documentation_id_fkey;
+ALTER TABLE releases DROP CONSTRAINT releases_cheesecake_code_kwalitee_id_fkey;
+ALTER TABLE release_classifiers DROP CONSTRAINT release_classifiers_trove_id_fkey;
+ALTER TABLE release_classifiers DROP CONSTRAINT release_classifiers_name_fkey;
+ALTER TABLE release_dependencies DROP CONSTRAINT release_dependencies_name_fkey;
+ALTER TABLE release_files DROP CONSTRAINT release_files_name_fkey;
+ALTER TABLE release_urls DROP CONSTRAINT release_urls_name_fkey;
+ALTER TABLE description_urls DROP CONSTRAINT description_urls_name_fkey;
+ALTER TABLE roles DROP CONSTRAINT roles_user_name_fkey;
+ALTER TABLE roles DROP CONSTRAINT roles_package_name_fkey;
+ALTER TABLE mirrors DROP CONSTRAINT mirrors_user_name_fkey;
+ALTER TABLE ratings DROP CONSTRAINT ratings_user_name_fkey;
+ALTER TABLE ratings DROP CONSTRAINT ratings_name_fkey;
+ALTER TABLE comments DROP CONSTRAINT comments_rating_fkey;
+ALTER TABLE comments DROP CONSTRAINT comments_user_name_fkey;
+ALTER TABLE comments DROP CONSTRAINT comments_in_reply_to_fkey;
+ALTER TABLE comments_journal DROP CONSTRAINT comments_journal_submitted_by_fkey;
+ALTER TABLE comments_journal DROP CONSTRAINT comments_journal_name_fkey;
+


More information about the Pypi-checkins mailing list