- c.execute("""CREATE TABLE pending_bin_contents (
- id serial NOT NULL,
- package text NOT NULL,
- version debversion NOT NULL,
- arch int NOT NULL,
- filename text NOT NULL,
- type int NOT NULL,
- PRIMARY KEY(id))""" );
-
- c.execute("""CREATE TABLE deb_contents (
- filename text,
- section text,
- package text,
- binary_id integer,
- arch integer,
- suite integer)""" )
-
- c.execute("""CREATE TABLE udeb_contents (
- filename text,
- section text,
- package text,
- binary_id integer,
- suite integer,
- arch integer)""" )
-
- c.execute("""ALTER TABLE ONLY deb_contents
- ADD CONSTRAINT deb_contents_arch_fkey
- FOREIGN KEY (arch) REFERENCES architecture(id)
- ON DELETE CASCADE;""")
-
- c.execute("""ALTER TABLE ONLY udeb_contents
- ADD CONSTRAINT udeb_contents_arch_fkey
- FOREIGN KEY (arch) REFERENCES architecture(id)
- ON DELETE CASCADE;""")
-
- c.execute("""ALTER TABLE ONLY deb_contents
- ADD CONSTRAINT deb_contents_pkey
- PRIMARY KEY (filename,package,arch,suite);""")
-
- c.execute("""ALTER TABLE ONLY udeb_contents
- ADD CONSTRAINT udeb_contents_pkey
- PRIMARY KEY (filename,package,arch,suite);""")
-
- c.execute("""ALTER TABLE ONLY deb_contents
- ADD CONSTRAINT deb_contents_suite_fkey
- FOREIGN KEY (suite) REFERENCES suite(id)
- ON DELETE CASCADE;""")
-
- c.execute("""ALTER TABLE ONLY udeb_contents
- ADD CONSTRAINT udeb_contents_suite_fkey
- FOREIGN KEY (suite) REFERENCES suite(id)
- ON DELETE CASCADE;""")
-
- c.execute("""ALTER TABLE ONLY deb_contents
- ADD CONSTRAINT deb_contents_binary_fkey
- FOREIGN KEY (binary_id) REFERENCES binaries(id)
- ON DELETE CASCADE;""")
-
- c.execute("""ALTER TABLE ONLY udeb_contents
- ADD CONSTRAINT udeb_contents_binary_fkey
- FOREIGN KEY (binary_id) REFERENCES binaries(id)
- ON DELETE CASCADE;""")
-
- c.execute("""CREATE INDEX ind_deb_contents_binary ON deb_contents(binary_id);""" )
-
-
- suites = self.suites()
-
- for suite in [i.lower() for i in suites]:
- suite_id = DBConn().get_suite_id(suite)
- arch_list = arches(c, suite_id)
- arch_list = arches(c, suite_id)
-
- for (arch_id,arch_str) in arch_list:
- c.execute( "CREATE INDEX ind_deb_contents_%s_%s ON deb_contents (arch,suite) WHERE (arch=2 OR arch=%d) AND suite=$d"%(arch_str,suite,arch_id,suite_id) )
-
- for section, sname in [("debian-installer","main"),
- ("non-free/debian-installer", "nonfree")]:
- c.execute( "CREATE INDEX ind_udeb_contents_%s_%s ON udeb_contents (section,suite) WHERE section=%s AND suite=$d"%(sname,suite,section,suite_id) )
-
-
- c.execute( """CREATE OR REPLACE FUNCTION update_contents_for_bin_a() RETURNS trigger AS $$
- event = TD["event"]
- if event == "DELETE" or event == "UPDATE":
-
- plpy.execute(plpy.prepare("DELETE FROM deb_contents WHERE binary_id=$1 and suite=$2",
- ["int","int"]),
- [TD["old"]["bin"], TD["old"]["suite"]])
-
- if event == "INSERT" or event == "UPDATE":
-
- content_data = plpy.execute(plpy.prepare(
- """SELECT s.section, b.package, b.architecture, ot.type
- FROM override o
- JOIN override_type ot on o.type=ot.id
- JOIN binaries b on b.package=o.package
- JOIN files f on b.file=f.id
- JOIN location l on l.id=f.location
- JOIN section s on s.id=o.section
- WHERE b.id=$1
- AND o.suite=$2
- """,
- ["int", "int"]),
- [TD["new"]["bin"], TD["new"]["suite"]])[0]
-
- filenames = plpy.execute(plpy.prepare(
- "SELECT bc.file FROM bin_contents bc where bc.binary_id=$1",
- ["int"]),
- [TD["new"]["bin"]])
-
- for filename in filenames:
- plpy.execute(plpy.prepare(
- """INSERT INTO deb_contents
- (filename,section,package,binary_id,arch,suite)
- VALUES($1,$2,$3,$4,$5,$6)""",
- ["text","text","text","int","int","int"]),
- [filename["file"],
- content_data["section"],
- content_data["package"],
- TD["new"]["bin"],
- content_data["architecture"],
- TD["new"]["suite"]] )
-$$ LANGUAGE plpythonu VOLATILE SECURITY DEFINER;
-""")
-
-
- c.execute( """CREATE OR REPLACE FUNCTION update_contents_for_override() RETURNS trigger AS $$
- event = TD["event"]
- if event == "UPDATE":
-
- otype = plpy.execute(plpy.prepare("SELECT type from override_type where id=$1",["int"]),[TD["new"]["type"]] )[0];
- if otype["type"].endswith("deb"):
- section = plpy.execute(plpy.prepare("SELECT section from section where id=$1",["int"]),[TD["new"]["section"]] )[0];
-
- table_name = "%s_contents" % otype["type"]
- plpy.execute(plpy.prepare("UPDATE %s set section=$1 where package=$2 and suite=$3" % table_name,
- ["text","text","int"]),
- [section["section"],
- TD["new"]["package"],
- TD["new"]["suite"]])
-
-$$ LANGUAGE plpythonu VOLATILE SECURITY DEFINER;
-""")
- c.execute( """CREATE TRIGGER bin_associations_contents_trigger
- AFTER INSERT OR UPDATE OR DELETE ON bin_associations
- FOR EACH ROW EXECUTE PROCEDURE update_contents_for_bin_a();""")
- c.execute("""CREATE TRIGGER override_contents_trigger
- AFTER UPDATE ON override
- FOR EACH ROW EXECUTE PROCEDURE update_contents_for_override();""")
-
-
- c.execute( "CREATE INDEX ind_deb_contents_name ON deb_contents(package);");
- c.execute( "CREATE INDEX ind_udeb_contents_name ON udeb_contents(package);");
-
+ print "Drop old views."
+ c.execute("DROP VIEW IF EXISTS binfiles_suite_component_arch CASCADE")
+ c.execute("DROP VIEW IF EXISTS srcfiles_suite_component CASCADE")
+
+ print "Create new views."
+ c.execute("""
+CREATE VIEW binfiles_suite_component_arch AS
+ SELECT files.filename, binaries.type, location.path, location.component,
+ bin_associations.suite, binaries.architecture
+ FROM binaries
+ JOIN bin_associations ON binaries.id = bin_associations.bin
+ JOIN files ON binaries.file = files.id
+ JOIN location ON files.location = location.id;
+ """)
+ c.execute("""
+CREATE VIEW srcfiles_suite_component AS
+ SELECT files.filename, location.path, location.component,
+ src_associations.suite
+ FROM source
+ JOIN src_associations ON source.id = src_associations.source
+ JOIN files ON source.file = files.id
+ JOIN location ON files.location = location.id;
+ """)
+
+ print "Committing"
+ c.execute("UPDATE config SET value = '23' WHERE name = 'db_revision'")