diff options
Diffstat (limited to 'gentoobrowse-api/service/sql/maintenance')
5 files changed, 26 insertions, 0 deletions
diff --git a/gentoobrowse-api/service/sql/maintenance/gitListCreate.sql b/gentoobrowse-api/service/sql/maintenance/gitListCreate.sql new file mode 100644 index 0000000..df9f947 --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/gitListCreate.sql @@ -0,0 +1,11 @@ +CREATE TEMPORARY TABLE filelist AS +SELECT + fl.repoid, + fl.status, + fl.filename, + 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/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 |