#
# 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