diff options
Diffstat (limited to 'gentoobrowse-api/service/sql/maintenance')
9 files changed, 47 insertions, 0 deletions
diff --git a/gentoobrowse-api/service/sql/maintenance/filelistPhases.sql b/gentoobrowse-api/service/sql/maintenance/filelistPhases.sql new file mode 100644 index 0000000..5234f51 --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/filelistPhases.sql @@ -0,0 +1,4 @@ +SELECT phase +FROM filelist +GROUP BY phase +ORDER BY phase diff --git a/gentoobrowse-api/service/sql/maintenance/gitListChangesInPhase.sql b/gentoobrowse-api/service/sql/maintenance/gitListChangesInPhase.sql new file mode 100644 index 0000000..17dfdb3 --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/gitListChangesInPhase.sql @@ -0,0 +1,5 @@ +SELECT repoId, fileTypeId, pathParts, status +FROM filelist +WHERE phase = ? +AND status IS NOT NULL +ORDER BY POSITION(status IN 'DMA'), updateOrder NULLS LAST diff --git a/gentoobrowse-api/service/sql/maintenance/gitListCreate.sql b/gentoobrowse-api/service/sql/maintenance/gitListCreate.sql new file mode 100644 index 0000000..20ca804 --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/gitListCreate.sql @@ -0,0 +1,10 @@ +CREATE TEMPORARY TABLE filelist AS +SELECT + fl.repoid, + fl.status, + ft.filetypeid, + ft.updateOrder, + ft.phase, + (STRING_TO_ARRAY(fl.filename, '/')) pathparts +FROM filelistraw fl, gentoobrowse.filetypes ft +WHERE (STRING_TO_ARRAY(fl.filename, '/')) ~ ft.spec diff --git a/gentoobrowse-api/service/sql/maintenance/gitListCreateIdx.sql b/gentoobrowse-api/service/sql/maintenance/gitListCreateIdx.sql new file mode 100644 index 0000000..1b3a6ca --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/gitListCreateIdx.sql @@ -0,0 +1 @@ +CREATE INDEX idx_filelist_mode ON filelist(phase, status) diff --git a/gentoobrowse-api/service/sql/maintenance/gitListCreateRaw.sql b/gentoobrowse-api/service/sql/maintenance/gitListCreateRaw.sql new file mode 100644 index 0000000..3007cd8 --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/gitListCreateRaw.sql @@ -0,0 +1,4 @@ +CREATE TEMPORARY TABLE filelistraw ( + repoid INT NOT NULL, + filename TEXT NOT NULL, + status CHARACTER(1) NOT NULL) diff --git a/gentoobrowse-api/service/sql/maintenance/reposToGitRefresh.sql b/gentoobrowse-api/service/sql/maintenance/reposToGitRefresh.sql new file mode 100644 index 0000000..068ddb9 --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/reposToGitRefresh.sql @@ -0,0 +1,4 @@ +SELECT repoid, name, path, lastcommit +FROM gentoobrowse.repos +WHERE lastcommit IS NOT NULL + diff --git a/gentoobrowse-api/service/sql/maintenance/reposToUpdate.sql b/gentoobrowse-api/service/sql/maintenance/reposToUpdate.sql new file mode 100644 index 0000000..d373418 --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/reposToUpdate.sql @@ -0,0 +1,4 @@ +SELECT path, upstream, branch +FROM gentoobrowse.repos +WHERE upstream IS NOT NULL +AND branch IS NOT NULL diff --git a/gentoobrowse-api/service/sql/maintenance/updatesMergeFilelistEntries.sql b/gentoobrowse-api/service/sql/maintenance/updatesMergeFilelistEntries.sql new file mode 100644 index 0000000..68cdbee --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/updatesMergeFilelistEntries.sql @@ -0,0 +1,9 @@ +UPDATE filelist SET + status = CASE WHEN status = 'A' THEN 'M' END +WHERE pathparts IN ( + SELECT pathparts + FROM filelist + WHERE filetypeid = 1 + AND status IN ('A', 'D') + GROUP BY pathparts + HAVING COUNT(*) = 2) diff --git a/gentoobrowse-api/service/sql/maintenance/updatesMoveFilelist.sql b/gentoobrowse-api/service/sql/maintenance/updatesMoveFilelist.sql new file mode 100644 index 0000000..0a34c82 --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/updatesMoveFilelist.sql @@ -0,0 +1,6 @@ +UPDATE filelist SET + pathparts[3] = ?, + pathparts[4] = REGEXP_REPLACE(pathparts[4], CONCAT('^', REPLACE(?, '+', '\+'), '(-[0-9])'), CONCAT(?::text, '\1')) +WHERE pathparts[3] = ? +AND pathparts[4] ~ CONCAT('^', replace(?, '+', '\+'), '-[0-9]') +AND filetypeId = 1 |