X-Git-Url: https://pd.if.org/git/?a=blobdiff_plain;f=db.sql;h=548dba8bcca125c6d5533814a75d8d21f22298e3;hb=5cc943f078123837c33a4eeebf6eb96a581ddb47;hp=c7040349b38dc2004ed9970eb3e9ca9a414da33a;hpb=b6e8d469897f58ed6ddd5e428acdac0f4a597e4e;p=zpackage

diff --git a/db.sql b/db.sql
index c704034..548dba8 100644
--- a/db.sql
+++ b/db.sql
@@ -15,6 +15,17 @@ CREATE TABLE files (
 )
 ;
 
+create view filerefs as
+select F.hash,
+count(PF.hash) + count(S.hash) + count(EL.file) + count(N.file) as refcount
+from files F
+left join packagefiles PF on PF.hash = F.hash
+left join scripts S on S.hash = F.hash
+left join elflibraries EL on EL.file = F.hash
+left join notes N on N.file = F.hash
+group by F.hash
+;
+
 -- information about packages
 -- a package is identified by a package,version,release triple
 create table packages (
@@ -117,9 +128,9 @@ create table packagefiles (
 	release	integer,
 
 	path	text, -- filesystem path
-	mode	text not null, -- perms, use text for octal rep?
-	username	text not null, -- name of owner
-	groupname	text not null, -- group of owner
+	mode	text not null default '0644', -- perms, use text for octal rep?
+	username	text not null default 'root', -- name of owner
+	groupname	text not null default 'root', -- group of owner
 	uid	integer, -- numeric uid, generally ignored
 	gid	integer, -- numeric gid, generally ignored
 	configuration integer not null default 0, -- boolean if config file
@@ -194,6 +205,23 @@ begin
 end
 ;
 
+create trigger packagefiles_delete_trigger instead of
+delete on packagefiles_pkgid
+begin
+	delete from packagefiles
+	where package = OLD.package
+	and version = OLD.version
+	and release = OLD.release
+	and path = OLD.path
+	;
+	update packages set hash = null
+	where package = OLD.package
+	and version = OLD.version
+	and release = OLD.release
+	;
+end
+;
+
 create view installed_ref_count as
 select I.path, count(*) as refcount
 from installedfiles I
@@ -250,15 +278,37 @@ create table elflibraries (
 )
 without rowid
 ;
+create index elf_library_name_index on elflibraries(soname);
 
 create table elfneeded (
-	file	text,
+	file	text, -- hash of file
 	needed	text, -- soname of dependency
 	primary key (file, needed)
 )
 without rowid
 ;
 
+create view package_libraries as
+select distinct PF.pkgid, EL.soname
+from packagefiles_pkgid PF join elflibraries EL on PF.hash = EL.file
+;
+
+create view package_libraries_needed as
+with pkglibs as (
+		select distinct EN.needed as soname, PF.pkgid
+		from elfneeded EN
+		join packagefiles_pkgid PF on PF.hash = EN.file
+		),
+     pkgprovides as (
+		     select distinct EL.soname, PF.pkgid
+		     from elflibraries EL
+		     join packagefiles_pkgid PF on PF.hash = EL.file
+		    )
+     select distinct PL.pkgid,PL.soname, PP.soname is not null as selfsatisfied
+     from pkglibs PL
+     left join pkgprovides PP on PL.pkgid = PP.pkgid and PL.soname = PP.soname
+;
+
 -- package scripts: table of package, stage, file
 create table scripts (
 	package	text,
@@ -345,6 +395,8 @@ create table repository (
 	priority	integer not null default 1,
 	refreshed	integer -- last refresh time
 );
+-- force the url to be repourl/info.repo
+-- package urls repourl/pkgid.zpm
 
 -- urls for downloading packages.  possibly unneeded
 create table repository_packages (
@@ -407,6 +459,19 @@ from syncstatus BASE
 where path in (select path from md_conflict where mdcount > 1)
 ;
 
+create view needed_libraries as
+with recursive
+libs(file,needs,provider) as (
+	select N.file, N.needed as needs, L.file as provider
+	from elfneeded N left join elflibraries L on N.needed = L.soname
+	union
+	select L.file, N.needed as needs, EL.file as provider
+	from libs L
+	join elfneeded N on N.file = L.provider
+	left join elflibraries EL on N.needed = EL.soname
+)
+select * from libs;
+
 create view syncinfo as
 with
 preserved as (
@@ -449,7 +514,10 @@ modified as (
 	select distinct
 	SS.path, 
 	SS.username, SS.uid, SS.groupname, SS.gid, SS.mode, SS.filetype,
-	SS.mtime, SS.hash, SS.configuration, SS.target, SS.device,
+	SS.mtime, SS.hash,
+	SS.configuration + case when OS.configuration = 1 then 2 else 0 end
+	as configuration,
+       	SS.target, SS.device,
 	OS.hash as ohash, SS.mds, OS.mds as omds
 	from syncstatus SS
 	join syncstatus OS
@@ -461,6 +529,7 @@ modified as (
 	SS.rstatus in ('installing')
 ),
 -- preserve: libraries needed by something in installed or installing
+/*
 needed as (
 	select distinct
        	ED.library
@@ -468,13 +537,23 @@ needed as (
 	where status in ('installed', 'installing')
 	and library is not null
 ),
+*/
+needed as (
+	select NL.file as needslib, NL.provider as libraryhash
+	from needed_libraries NL
+	inner join syncstatus SS on SS.hash = NL.file
+	and SS.status in ('installed', 'installing')
+),
 preserve as (
 	select distinct
        	path,username,uid,groupname,gid,mode,filetype,mtime,hash,
 	configuration,target,device, null as ohash
 	from syncstatus SS
-	where path in (select library from needed)
-	and SS.rstatus in ('removing', 'removed')
+	join needed N on SS.hash = N.libraryhash
+	where SS.rstatus in ('removing', 'removed')
+--	where SS.hash in (select libraryhash from needed)
+--	where path in (select library from needed)
+--	and SS.rstatus in ('removing', 'removed')
 ),
 -- remove: cur, not preserved, not in final set
 remove as (