From b3c05e3e2a4b5cf159a2ab94b1c6696ceeb8c1a8 Mon Sep 17 00:00:00 2001
From: Nathan Wagner <nw@hydaspes.if.org>
Date: Thu, 6 Dec 2018 08:34:28 +0000
Subject: [PATCH] break up db.sql

---
 Makefile                  |  8 +++++++-
 db.sql => schema/main.sql | 37 +------------------------------------
 schema/syncconflicts.sql  | 35 +++++++++++++++++++++++++++++++++++
 3 files changed, 43 insertions(+), 37 deletions(-)
 rename db.sql => schema/main.sql (93%)
 create mode 100644 schema/syncconflicts.sql

diff --git a/Makefile b/Makefile
index 22c8d36..e57ecbc 100644
--- 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 b1343d0..6ea34f2 100644
--- 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
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')
+;
-- 
2.40.0