o Actually storing and deleting new/gone artifacts from Nexus.

1 files changed, 38 insertions, 12 deletions

diff --git a/src/main/resources/ddl-nexus.sql b/src/main/resources/ddl-nexus.sql
index 9ee35e0..15ed482 100755
--- a/src/main/resources/ddl-nexus.sql
+++ b/src/main/resources/ddl-nexus.sql
@@ -1,34 +1,60 @@
 BEGIN;
 
 DROP TABLE IF EXISTS nexus_event;
+DROP TABLE IF EXISTS nexus_artifact;
 DROP TABLE IF EXISTS nexus_repository;
 DROP TABLE IF EXISTS nexus_server;
 
 CREATE TABLE nexus_server (
-  url VARCHAR(1000) PRIMARY KEY
+  url  VARCHAR(1000) PRIMARY KEY,
+  name VARCHAR(100) NOT NULL
 );
 
 CREATE TABLE nexus_repository (
-  id                     VARCHAR(1000) PRIMARY KEY,
-  nexus_server_url       VARCHAR(1000),
+  id                     VARCHAR(1000),
+  server_url             VARCHAR(1000) NOT NULL,
   group_ids              VARCHAR(100) [],
   created_date           TIMESTAMP,
   last_update            TIMESTAMP,
   last_successful_update TIMESTAMP,
-  CONSTRAINT nexus_server_fk FOREIGN KEY (nexus_server_url) REFERENCES nexus_server (url)
+  CONSTRAINT nexus_repository_pk PRIMARY KEY (id, server_url),
+  CONSTRAINT nexus_server_fk FOREIGN KEY (server_url) REFERENCES nexus_server (url)
+);
+
+CREATE TABLE nexus_artifact (
+  server_url             VARCHAR(1000) NOT NULL,
+  repository_id          VARCHAR(1000) NOT NULL,
+  group_id               VARCHAR(100)  NOT NULL,
+  artifact_id            VARCHAR(100)  NOT NULL,
+  version                VARCHAR(100)  NOT NULL,
+  snapshot_version       VARCHAR(100),
+  classifiers            VARCHAR(100) [],
+  packagings             VARCHAR(100) [],
+  created_date           TIMESTAMP,
+  last_update            TIMESTAMP,
+  last_successful_update TIMESTAMP,
+  CONSTRAINT nexus_artifact_pk PRIMARY KEY (server_url, repository_id, group_id, artifact_id, version),
+  CONSTRAINT nexus_repository_fk FOREIGN KEY (server_url, repository_id) REFERENCES nexus_repository (server_url, id)
 );
 
 CREATE TABLE nexus_event (
+  timestamp     VARCHAR(100),
+  uuid          VARCHAR(100),
+  server_url    VARCHAR(1000) NOT NULL,
   repository_id VARCHAR(1000) NOT NULL,
-
-  groupId       VARCHAR(100),
-  artifactId    VARCHAR(100),
-  version       VARCHAR(100),
-  files         VARCHAR(100),
-  CONSTRAINT nexus_repository_fk FOREIGN KEY (repository_id) REFERENCES nexus_repository (id)
+  group_id      VARCHAR(100)  NOT NULL,
+  artifact_id   VARCHAR(100)  NOT NULL,
+  version       VARCHAR(100)  NOT NULL,
+  files         VARCHAR(100) [],
+  CONSTRAINT nexus_event_pk PRIMARY KEY (timestamp, server_url, repository_id, group_id, artifact_id, version),
+  CONSTRAINT nexus_artifact_fk FOREIGN KEY (server_url, repository_id, group_id, artifact_id, version) REFERENCES nexus_artifact (server_url, repository_id, group_id, artifact_id, version)
 );
 
-INSERT INTO nexus_server(url) VALUES('https://oss.sonatype.org');
-INSERT INTO nexus_repository(id, nexus_server_url, group_ids) VALUES('codehaus', 'https://oss.sonatype.org', ARRAY['io.trygvis', 'org.codehaus.mojo']);
+INSERT INTO nexus_server (url, name) VALUES ('https://oss.sonatype.org', 'Central Repository');
+-- INSERT INTO nexus_repository (id, server_url, group_ids) VALUES ('releases', 'https://oss.sonatype.org', ARRAY ['io.trygvis', 'no.arktekk', 'org.codehaus']);
+INSERT INTO nexus_repository (id, server_url, group_ids) VALUES ('releases', 'https://oss.sonatype.org', ARRAY ['io.trygvis', 'no.arktekk', 'org.codehaus']);
+
+INSERT INTO nexus_server (url, name) VALUES ('http://nexus.codehaus.org', 'Codehaus Snapshots');
+INSERT INTO nexus_repository (id, server_url, group_ids) VALUES ('snapshots', 'http://nexus.codehaus.org', ARRAY ['org.codehaus.mojo']);
 
 COMMIT;