#
# CDDL HEADER START
#
# The contents of this file are subject to the terms of the
# Common Development and Distribution License (the "License").
# You may not use this file except in compliance with the License.
#
# See LICENSE.txt included in this distribution for the specific
# language governing permissions and limitations under the License.
#
# When distributing Covered Code, include this CDDL HEADER in each
# file and include the License file at LICENSE.txt.
# If applicable, add the following below this CDDL HEADER, with the
# fields enclosed by brackets "[]" replaced with your own identifying
# information: Portions Copyright [yyyy] [name of copyright owner]
#
# CDDL HEADER END
#
#
# Copyright (c) 2009, 2012, Oracle and/or its affiliates. All rights reserved.
#
createTableRepositories=\
CREATE TABLE OPENGROK.REPOSITORIES (\
ID INT GENERATED ALWAYS AS IDENTITY \
CONSTRAINT REPOSITORIES_PK PRIMARY KEY, \
PATH VARCHAR(32672) \
CONSTRAINT REPOSITORIES_PATH_UNIQUE UNIQUE NOT NULL)
createTableDirectories=\
CREATE TABLE OPENGROK.DIRECTORIES (\
ID INT CONSTRAINT DIRECTORIES_PK PRIMARY KEY, \
PATH VARCHAR(32672) NOT NULL, \
PARENT INT CONSTRAINT DIRS_FK_DIRS \
REFERENCES OPENGROK.DIRECTORIES ON DELETE CASCADE, \
CONSTRAINT DIRECTORIES_PARENT_PATH_UNIQUE UNIQUE (PARENT, PATH), \
REPOSITORY INT NOT NULL \
CONSTRAINT DIRS_FK_REPO \
REFERENCES OPENGROK.REPOSITORIES ON DELETE CASCADE, \
CONSTRAINT DIRECTORIES_REPO_PATH_UNIQUE UNIQUE (REPOSITORY, PATH))
createTableFiles=\
CREATE TABLE OPENGROK.FILES (\
ID INT CONSTRAINT FILES_PK PRIMARY KEY, \
NAME VARCHAR(32672) NOT NULL, \
DIRECTORY INT NOT NULL \
CONSTRAINT FILES_FK_DIR \
REFERENCES OPENGROK.DIRECTORIES ON DELETE CASCADE, \
CONSTRAINT FILES_DIR_NAME_UNIQUE UNIQUE (DIRECTORY, NAME))
createTableAuthors=\
CREATE TABLE OPENGROK.AUTHORS (\
ID INT CONSTRAINT AUTHORS_PK PRIMARY KEY, \
REPOSITORY INT NOT NULL \
CONSTRAINT AUTHORS_FK_REPO \
REFERENCES OPENGROK.REPOSITORIES ON DELETE CASCADE, \
NAME VARCHAR(32672), \
CONSTRAINT AUTHORS_REPO_NAME_UNIQUE UNIQUE (REPOSITORY, NAME))
createTableChangesets=\
CREATE TABLE OPENGROK.CHANGESETS (\
ID INT CONSTRAINT CHANGESETS_PK PRIMARY KEY, \
REPOSITORY INT NOT NULL \
CONSTRAINT CHANGESETS_FK_REPO \
REFERENCES OPENGROK.REPOSITORIES ON DELETE CASCADE, \
REVISION VARCHAR(1024) NOT NULL, \
OLD_REV VARCHAR(1024), \
AUTHOR INT NOT NULL \
CONSTRAINT CHANGESETS_FK_AUTH \
REFERENCES OPENGROK.AUTHORS ON DELETE CASCADE, \
TIME TIMESTAMP NOT NULL, \
MESSAGE VARCHAR(32672) NOT NULL, \
CONSTRAINT CHANGESETS_REPO_REV_UNIQUE UNIQUE (REPOSITORY, REVISION))
createIndexChangesetsRepoIdDesc=\
CREATE UNIQUE INDEX IDX_CHANGESETS_REPO_ID_DESC ON \
OPENGROK.CHANGESETS(REPOSITORY, ID DESC)
createTableDirchanges=\
CREATE TABLE OPENGROK.DIRCHANGES (\
DIRECTORY INT NOT NULL \
CONSTRAINT DIRCHANGES_FK_DIR \
REFERENCES OPENGROK.DIRECTORIES ON DELETE CASCADE, \
CHANGESET INT NOT NULL \
CONSTRAINT DIRCHANGES_FK_CS \
REFERENCES OPENGROK.CHANGESETS ON DELETE CASCADE, \
CONSTRAINT DIRCHANGES_PK PRIMARY KEY (DIRECTORY, CHANGESET))
createTableFilechanges=\
CREATE TABLE OPENGROK.FILECHANGES (\
FILE INT NOT NULL \
CONSTRAINT FILECHANGES_FK_FILE \
REFERENCES OPENGROK.FILES ON DELETE CASCADE, \
CHANGESET INT NOT NULL \
CONSTRAINT FILECHANGES_FK_CS \
REFERENCES OPENGROK.CHANGESETS ON DELETE CASCADE, \
CONSTRAINT FILECHANGES_PK PRIMARY KEY (FILE, CHANGESET))
hasCacheForDirectory=\
SELECT 1 FROM OPENGROK.REPOSITORIES R, OPENGROK.DIRECTORIES D \
WHERE D.REPOSITORY = R.ID AND R.PATH = ? AND D.PATH = ?
getFileHistory=\
SELECT CS.REVISION, A.NAME, CS.TIME, CS.MESSAGE, CS.ID, CS.OLD_REV \
FROM \
OPENGROK.CHANGESETS CS, OPENGROK.FILECHANGES FC, \
OPENGROK.REPOSITORIES R, OPENGROK.FILES F, OPENGROK.DIRECTORIES D, \
OPENGROK.AUTHORS A \
WHERE \
R.PATH = ? AND D.PATH = ? AND F.NAME = ? AND \
D.REPOSITORY = R.ID AND D.ID = F.DIRECTORY AND \
CS.ID = FC.CHANGESET \
AND FC.FILE = F.ID AND A.ID = CS.AUTHOR \
ORDER BY CS.ID DESC
getDirHistory=\
SELECT CS.REVISION, A.NAME, CS.TIME, CS.MESSAGE, CS.ID, CS.OLD_REV \
FROM \
OPENGROK.CHANGESETS CS, OPENGROK.DIRCHANGES DC, OPENGROK.REPOSITORIES R, \
OPENGROK.DIRECTORIES D, OPENGROK.AUTHORS A \
WHERE \
R.PATH = ? AND D.PATH = ? AND D.REPOSITORY = R.ID AND \
CS.ID = DC.CHANGESET \
AND DC.DIRECTORY = D.ID AND A.ID = CS.AUTHOR \
ORDER BY CS.ID DESC
getFilesInChangeset=\
SELECT D.PATH || '/' || F.NAME \
FROM OPENGROK.DIRECTORIES D, OPENGROK.FILES F, OPENGROK.FILECHANGES FC \
WHERE D.ID = F.DIRECTORY AND F.ID = FC.FILE AND FC.CHANGESET = ?
getRepository=SELECT ID FROM OPENGROK.REPOSITORIES WHERE PATH = ?
addRepository=INSERT INTO OPENGROK.REPOSITORIES(PATH) VALUES ?
addChangeset=\
INSERT INTO OPENGROK.CHANGESETS \
(REPOSITORY, REVISION, AUTHOR, TIME, MESSAGE, ID, OLD_REV) \
VALUES (?,?,?,?,?,?,?)
addDirchange=INSERT INTO OPENGROK.DIRCHANGES(CHANGESET, DIRECTORY) VALUES (?,?)
addFilechange=INSERT INTO OPENGROK.FILECHANGES(CHANGESET, FILE) VALUES (?,?)
getAuthors=SELECT NAME, ID FROM OPENGROK.AUTHORS WHERE REPOSITORY = ?
addAuthor=INSERT INTO OPENGROK.AUTHORS (REPOSITORY, NAME, ID) VALUES (?,?,?)
getDirectories=SELECT PATH, ID FROM OPENGROK.DIRECTORIES WHERE REPOSITORY = ?
getFiles=\
SELECT D.PATH || '/' || F.NAME, F.ID \
FROM OPENGROK.FILES F, OPENGROK.DIRECTORIES D \
WHERE D.REPOSITORY = ? AND F.DIRECTORY = D.ID
addDirectory=INSERT INTO OPENGROK.DIRECTORIES(REPOSITORY, PATH, ID, PARENT) \
VALUES (?,?,?,?)
addFile=INSERT INTO OPENGROK.FILES(DIRECTORY, NAME, ID) VALUES (?,?,?)
getLatestCachedRevision=\
SELECT REVISION FROM OPENGROK.CHANGESETS WHERE ID = \
(SELECT MAX(CS.ID) FROM OPENGROK.CHANGESETS CS, OPENGROK.REPOSITORIES R \
WHERE CS.REPOSITORY = R.ID AND R.PATH = ?)
getMaxFileId=SELECT MAX(ID) FROM OPENGROK.FILES
getMaxDirId=SELECT MAX(ID) FROM OPENGROK.DIRECTORIES
getMaxChangesetId=SELECT MAX(ID) FROM OPENGROK.CHANGESETS
getMaxAuthorId=SELECT MAX(ID) FROM OPENGROK.AUTHORS
# Cascading deletes will take care of deleting all data associated with the
# repository from the other tables.
clearRepository=DELETE FROM OPENGROK.REPOSITORIES WHERE ID=?
# Statements used when upgrading a database from 0.11 format to 0.12 format.
alterTableDirectoriesParent=ALTER TABLE OPENGROK.DIRECTORIES \
ADD COLUMN PARENT INT CONSTRAINT DIRS_FK_DIRS \
REFERENCES OPENGROK.DIRECTORIES ON DELETE CASCADE
alterTableDirectoriesParentPathConstraint=ALTER TABLE OPENGROK.DIRECTORIES \
ADD CONSTRAINT DIRECTORIES_PARENT_PATH_UNIQUE UNIQUE (PARENT, PATH)
# hybrid repo support (hgsubversion)
alterTableChangesetsOldRev=ALTER TABLE OPENGROK.CHANGESETS \
ADD COLUMN OLD_REV VARCHAR(1024)
getAllDirectories=SELECT * FROM OPENGROK.DIRECTORIES
updateDirectoriesParent=UPDATE OPENGROK.DIRECTORIES SET PARENT = \
CASE \
WHEN PATH = '' THEN NULL \
ELSE (SELECT ID FROM OPENGROK.DIRECTORIES WHERE REPOSITORY=? AND PATH=?) \
END \
WHERE ID=?
# Get the last modified times of all files/subdirectories in a directory.
getLastModifiedTimes= \
SELECT T.NAME, MAX(CS.TIME), MAX(CS.REVISION) FROM \
( \
SELECT F.DIRECTORY, F.NAME, MAX(FC.CHANGESET) FROM OPENGROK.FILECHANGES FC, \
OPENGROK.FILES F WHERE FC.FILE = F.ID GROUP BY F.DIRECTORY, F.NAME \
UNION ALL \
SELECT SD.PARENT, \
CAST(SUBSTR(SD.PATH, LENGTH(D.PATH)+2) || '/' AS VARCHAR(1024)), \
MAX(DC.CHANGESET) \
FROM OPENGROK.DIRCHANGES DC, OPENGROK.DIRECTORIES SD, \
OPENGROK.DIRECTORIES D \
WHERE DC.DIRECTORY = SD.ID AND SD.PARENT = D.ID \
GROUP BY SD.PARENT, SUBSTR(SD.PATH, LENGTH(D.PATH)+2) \
) T(PARENT, NAME, ID), \
OPENGROK.CHANGESETS CS, OPENGROK.DIRECTORIES D, OPENGROK.REPOSITORIES R \
WHERE R.PATH=? AND D.PATH=? AND R.ID = D.REPOSITORY AND \
CS.ID = T.ID AND T.PARENT=D.ID \
GROUP BY T.NAME
# Get the last modified time for all $source_root/[^/]+ dirs
getLastModifiedTimesSrcRoot = \
SELECT T.NAME, CS.TIME, CS.REVISION FROM \
( \
SELECT SD.ID, CAST(SUBSTR(SD.PATH, 2) || '/' AS VARCHAR(1024)), \
MAX(DC.CHANGESET) \
FROM OPENGROK.DIRECTORIES D, OPENGROK.DIRECTORIES SD, OPENGROK.DIRCHANGES DC \
WHERE D.ID=SD.PARENT AND D.PARENT IS NULL AND SD.ID = DC.DIRECTORY \
GROUP BY SD.PATH, SD.ID \
) T(ID, NAME, CS), OPENGROK.CHANGESETS CS \
WHERE CS.ID = T.CS \
GROUP BY T.NAME, CS.REVISION, CS.TIME