Changeset 34901 for gs2-extensions

Timestamp:

2021-02-19T16:06:54+13:00 (3 years ago)

Author:

su41

Message:

Added a new sql query to test. It works fine but it's not 100% complete query. Also removed the option of projectId since we'll just pull everything from all projects, and let Greenstone handle sorting that out

File:

• gs2-extensions/tipple-bridge/trunk/src/src/main/java/sqlCli.java (modified) (4 diffs)

gs2-extensions/tipple-bridge/trunk/src/src/main/java/sqlCli.java

@@ -47 +47 @@
     String dbName;
 
-    //project id - it should be required right?
-    //if they omit, the query would come up empty.
-    @Option(names = {"--projectID"}, description = "ID number of the project")
-    String projectID;
-
     @Option(names = {"--host"}, description = "Type of host e.g. localhost ",
             required = true, defaultValue = "localhost")
@@ -75 +70 @@
         //else we're good to go.
        // databaseConnect(userName, password, port, dbName, projectID, jsonOutputFile, host);
-        int status = databaseConnect(userName, password, port, dbName, projectID, jsonOutputFile, host);
+        int status = databaseConnect(userName, password, port, dbName, jsonOutputFile, host);
         return status;
     }
@@ -86 +81 @@
 
     private static int databaseConnect(String user, String pass, int port, String dbName,
-                                        String projectID, String fileName, String host){
+                                         String fileName, String host){
         Connection conn;
         Statement stmt;
@@ -122 +117 @@
              */
 
-            String sql = "SELECT name, id, typeId, created from td_ContentItem where projectId="+projectID;
-/*
-            String sql = "select td_Document.id, td_Document.content " +
-                    "from td_Document, td_ContentItem, td_ContentItem_Document " +
-                    "where td_ContentItem.projectId=" + projectID + " and td_ContentItem.id =
-                    td_ContentItem_Document.ContentItemId " +
-                    "and td_ContentItem_Document.documentId = td_Document.id;";
-*/
+           // String sql = "SELECT name, id, typeId, created from td_ContentItem where projectId="+projectID;
+
+            String sql = "SELECT  cItem.name, cItem.created, mFile.path\n" +
+                    "FROM td_ManagedFile AS mFile, td_ContentItem AS cItem, td_ContentItem_MediaItem AS cMediaItem, td_MediaItem AS mItem\n" +
+                    "WHERE\n" +
+                    "cItem.id = cMediaItem.contentItemId \n" +
+                    "and cMediaItem.mediaItemId = mItem.id\n" +
+                    "And mItem.fileId = mFile.id\n" +
+                    "ORDER BY mFile.projectId;\n";
             ResultSet myResult = stmt.executeQuery(sql);
             ResultSetMetaData resultMetaData = myResult.getMetaData();