741 "LEFT JOIN pg_catalog.pg_namespace n " |
741 "LEFT JOIN pg_catalog.pg_namespace n " |
742 "ON n.oid = c.connamespace " |
742 "ON n.oid = c.connamespace " |
743 "WHERE c.conname = %(r)s AND n.nspname=%(n)s", |
743 "WHERE c.conname = %(r)s AND n.nspname=%(n)s", |
744 {'r': name, 'n': self.pg_schema}).fetchone()[0] |
744 {'r': name, 'n': self.pg_schema}).fetchone()[0] |
745 |
745 |
746 def application_indexes(self, tablename): |
746 def index_list(self, tablename): |
747 """ Iterate over all the indexes """ |
|
748 # This SQL query (cf http://www.postgresql.org/message-id/432F450F.4080700@squiz.net) |
747 # This SQL query (cf http://www.postgresql.org/message-id/432F450F.4080700@squiz.net) |
749 # aims at getting all the indexes for each table. |
748 # aims at getting all the indexes for each table. |
750 sql = '''SELECT c.relname as "Name" |
749 sql = '''SELECT c.relname as "Name" |
751 FROM pg_catalog.pg_class c |
750 FROM pg_catalog.pg_class c |
752 JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid |
751 JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid |
756 WHERE c.relkind IN ('i','') |
755 WHERE c.relkind IN ('i','') |
757 AND c2.relname = '%s' |
756 AND c2.relname = '%s' |
758 AND i.indisprimary = FALSE |
757 AND i.indisprimary = FALSE |
759 AND n.nspname NOT IN ('pg_catalog', 'pg_toast') |
758 AND n.nspname NOT IN ('pg_catalog', 'pg_toast') |
760 AND pg_catalog.pg_table_is_visible(c.oid);''' % tablename |
759 AND pg_catalog.pg_table_is_visible(c.oid);''' % tablename |
761 indexes_list = self.cnx.system_sql(sql).fetchall() |
760 return self.cnx.system_sql(sql).fetchall() |
|
761 |
|
762 def application_indexes(self, tablename): |
|
763 """ Iterate over all the indexes """ |
|
764 indexes_list = self.index_list(tablename) |
762 indexes = {} |
765 indexes = {} |
763 for name, in indexes_list: |
766 for name, in indexes_list: |
764 indexes[name] = self.index_query(name) |
767 indexes[name] = self.index_query(name) |
765 return indexes |
768 return indexes |
766 |
769 |
767 def application_constraints(self, tablename): |
770 def constraint_list(self, tablename): |
768 """ Iterate over all the constraints """ |
|
769 sql = '''SELECT i.conname as "Name" |
771 sql = '''SELECT i.conname as "Name" |
770 FROM pg_catalog.pg_class c |
772 FROM pg_catalog.pg_class c |
771 JOIN pg_catalog.pg_constraint i ON i.conrelid = c.oid |
773 JOIN pg_catalog.pg_constraint i ON i.conrelid = c.oid |
772 JOIN pg_catalog.pg_class c2 ON i.conrelid=c2.oid |
774 JOIN pg_catalog.pg_class c2 ON i.conrelid=c2.oid |
773 LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner |
775 LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner |
775 WHERE |
777 WHERE |
776 c2.relname = '%s' |
778 c2.relname = '%s' |
777 AND n.nspname NOT IN ('pg_catalog', 'pg_toast') |
779 AND n.nspname NOT IN ('pg_catalog', 'pg_toast') |
778 AND pg_catalog.pg_table_is_visible(c.oid) |
780 AND pg_catalog.pg_table_is_visible(c.oid) |
779 ''' % tablename |
781 ''' % tablename |
780 indexes_list = self.cnx.system_sql(sql).fetchall() |
782 return self.cnx.system_sql(sql).fetchall() |
|
783 |
|
784 def application_constraints(self, tablename): |
|
785 """ Iterate over all the constraints """ |
|
786 constraint_list = self.constraint_list(tablename) |
781 constraints = {} |
787 constraints = {} |
782 for name, in indexes_list: |
788 for name, in constraint_list: |
783 query = self.constraint_query(name) |
789 query = self.constraint_query(name) |
784 constraints[name] = 'ALTER TABLE %s ADD CONSTRAINT %s %s' % (tablename, name, query) |
790 constraints[name] = 'ALTER TABLE %s ADD CONSTRAINT %s %s' % (tablename, name, query) |
785 return constraints |
791 return constraints |