break up db.sql

diff --git a/Makefile b/Makefile
index 22c8d36ebcac21dd96a22876b3f0132e378131b0..e57ecbceaae8380259543030d13d4d8bb35ef731 100644 (file)
--- a/Makefile
+++ b/Makefile
@@ -159,6 +159,11 @@ zpm-parse: zpm-parse.o lib/parse.o
 zpm-quote: zpm-quote.o
        $(CC) $(CFLAGS) $(LDFLAGS) -o $@ $<
 
+db.sql: schema/main.sql schema/syncconflicts.sql
+       echo 'begin;' > $@
+       cat $+ >> $@
+       echo 'commit;' >> $@
+
 newdb.c: db.sql
        echo "char createdb[] = {" > $@
        xxd -i < $< >> $@
@@ -200,4 +205,5 @@ clean:
        rm -f *.o sqlite/*.o lib/*.o $(LZMAOBJ) liblzma.a \
                libelf.a libzpm.a zpm-addfile soname \
                *.xz \
-               local.db t/ctap/prove.o $(ZPKGBIN)
+               local.db t/ctap/prove.o $(ZPKGBIN) \
+               db.sql

diff --git a/db.sql b/schema/main.sql
similarity index 93%
rename from db.sql
rename to schema/main.sql
index b1343d00a3b6a7f829452af0868f1de2c04798e1..6ea34f27caeb8fc4fa12dd4add1a18a99409e421 100644 (file)
--- a/db.sql
+++ b/schema/main.sql
@@ -1,5 +1,3 @@
-begin;
-
 PRAGMA application_id = 0x5a504442;
 PRAGMA user_version = 1;
 
@@ -332,9 +330,7 @@ create table packagedeps (
        package text,
        version text,
        release integer,
-       requires text, -- package name (only)
-       minimum text,
-       maximum text,
+       requires text, -- package, can be partial
        primary key (package,version,release,package),
        foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 );
@@ -417,35 +413,6 @@ create table packagesource (
        repository      text references repository
 );
 
-create view syncconflicts as
-with 
--- metadata different
-md_conflict as (
-       select path, count(distinct
-       printf('%s:%s:%s:%s', PFA.filetype, PFA.mode, PFA.username, PFA.groupname)) as mdcount,
-               count(distinct PFA.hash) as hashcount
-       from packagefiles PFA
-       join packages PA
-       on PA.package = PFA.package and PA.version = PFA.version and PA.release = PFA.release
-       where PA.status in ('installing', 'installed')
-       group by path
-       having (count(distinct printf('%s:%s:%s:%s', PFA.filetype, PFA.mode, PFA.username, PFA.groupname)
-       ) > 1 or count(distinct PFA.hash) > 1)
-)
-select PFH.*,
-       printf('%s-%s-%s', PFH.package, PFH.version, PFH.release) as pkgid,
-       printf('%s:%s:%s:%s', PFH.filetype, PFH.mode, PFH.username, PFH.groupname) as mds,
-'hash' as conflict
-from packagefiles PFH
-where path in (select path from md_conflict where hashcount > 1)
-union
-select PFM.*,
-       printf('%s-%s-%s', PFM.package, PFM.version, PFM.release) as pkgid,
-       printf('%s:%s:%s:%s', PFM.filetype, PFM.mode, PFM.username, PFM.groupname) as mds,
-'md' as conflict
-from packagefiles PFM
-where path in (select path from md_conflict where mdcount > 1)
-;
 
 create view needed_libraries as
 with recursive
@@ -620,5 +587,3 @@ from preserve PFP
        -- left join packages P on P.package = PFP.package ...
        -- where P.status not in ('installed','installing')
 ;
-
-commit;

diff --git a/schema/syncconflicts.sql b/schema/syncconflicts.sql
new file mode 100644 (file)
index 0000000..eddb9d1
--- /dev/null
+++ b/schema/syncconflicts.sql
@@ -0,0 +1,35 @@
+create view syncconflicts as
+with 
+-- metadata different
+md_conflict as (
+       select path, count(distinct
+       printf('%s:%s:%s:%s', PFA.filetype, PFA.mode, PFA.username, PFA.groupname)) as mdcount,
+               count(distinct PFA.hash) as hashcount
+       from packagefiles PFA
+       join packages PA
+       on PA.package = PFA.package and PA.version = PFA.version and PA.release = PFA.release
+       where PA.status in ('installing', 'installed')
+       group by path
+       having (count(distinct printf('%s:%s:%s:%s', PFA.filetype, PFA.mode, PFA.username, PFA.groupname)
+       ) > 1 or count(distinct PFA.hash) > 1)
+)
+select PFH.*,
+       printf('%s-%s-%s', PFH.package, PFH.version, PFH.release) as pkgid,
+       printf('%s:%s:%s:%s', PFH.filetype, PFH.mode, PFH.username, PFH.groupname) as mds,
+'hash' as conflict
+from packagefiles PFH
+       join packages PA
+       on PA.package = PFH.package and PA.version = PFH.version and PA.release = PFH.release
+where path in (select path from md_conflict where hashcount > 1)
+       and PA.status in ('installing', 'installed')
+union
+select PFM.*,
+       printf('%s-%s-%s', PFM.package, PFM.version, PFM.release) as pkgid,
+       printf('%s:%s:%s:%s', PFM.filetype, PFM.mode, PFM.username, PFM.groupname) as mds,
+'md' as conflict
+from packagefiles PFM
+       join packages PA
+       on PA.package = PFM.package and PA.version = PFM.version and PA.release = PFM.release
+where path in (select path from md_conflict where hashcount > 1)
+       and PA.status in ('installing', 'installed')
+;