I got tired of of the fact that when I do "tg-admin sql create" it
doesn't work if two tables have constraints that both reference each
other. So I created a quick script to find all the constraints and
move them to ALTER TABLE commands after all the tables have been
created.
I'm using it as a command in TextMate, but a sample usage is below in
case it is useful for anyone else:
###
import re
sql = """
CREATE TABLE content (
id SERIAL PRIMARY KEY,
body TEXT,
page_id INT, CONSTRAINT page_id_exists FOREIGN KEY (page_id)
REFERENCES page (id)
);
CREATE TABLE page (
id SERIAL PRIMARY KEY,
title VARCHAR(64),
content_id INT, CONSTRAINT content_id_exists FOREIGN KEY
(content_id) REFERENCES content (id)
);
"""
create_rgx = re.compile("""(CREATE TABLE (\w*)\s+([^;]*);)""")
contstraint_rgx = re.compile("""(?s)(,\s+(CONSTRAINT.*?))(,|\);)""")
creates = create_rgx.findall(sql)
for create in creates:
constraints = contstraint_rgx.findall(create[0])
if constraints:
constraint_list = ["\tADD " + constraint[1] for constraint in
constraints]
constraint_sql = "ALTER TABLE " + create[1] + "\n" +
",\n".join(constraint_list) + ";\n\n"
for constraint in constraints:
sql = sql.replace(constraint[0], '', 1)
sql = sql + constraint_sql
print sql
###
Which will then output:
CREATE TABLE content (
id SERIAL PRIMARY KEY,
body TEXT,
page_id INT);
CREATE TABLE page (
id SERIAL PRIMARY KEY,
title VARCHAR(64),
content_id INT);
ALTER TABLE content
ADD CONSTRAINT page_id_exists FOREIGN KEY (page_id) REFERENCES
page (id)
;
ALTER TABLE page
ADD CONSTRAINT content_id_exists FOREIGN KEY (content_id)
REFERENCES content (id)
;
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"TurboGears" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/turbogears?hl=en
-~----------~----~----~----~------~----~------~--~---
