general search improvements

[zpackage] / db.sql

diff --git a/db.sql b/db.sql
index 00c5df0f6f1e316c964dd380fe84570f95cbc169..48ff62587a052f0873fd9a1eb057e244aa396933 100644 (file)
--- 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
@@ -269,7 +280,7 @@ without rowid
 ;
 
 create table elfneeded (
-       file    text,
+       file    text, -- hash of file
        needed  text, -- soname of dependency
        primary key (file, needed)
 )
@@ -424,6 +435,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 (
@@ -466,7 +490,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
@@ -478,6 +505,7 @@ modified as (
        SS.rstatus in ('installing')
 ),
 -- preserve: libraries needed by something in installed or installing
+/*
 needed as (
        select distinct
                ED.library
@@ -485,13 +513,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 (