Changeset 34905

Timestamp:

2021-02-19T17:56:36+13:00 (3 years ago)

Author:

davidb

Message:

Code tidy up; added in example SQL statement for next planned stage of work, which could retrieve files (e.g. photos) associated with content items

File:

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

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

@@ -114 +114 @@
             //remember to put in the projectId parameter instead of hardcoding it
 
-            /*
-            if we get up to the point where we allow boundary setting, i.e  user specifies
-            between x and y number of records
-            could use an if statement to check
-            if the paramter exists or not, if it does then run a statement with those x and y
-            if not, then run a different statement without them
-             */
-
+
+        /* Step 1: retrieve all the content items associated with the given projectId */
+        
             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;";
-*/
+
+        // consider usingLIMIT to get the returned results in controled batch size, rather than getting
+        // all the results at once.
+        
             ResultSet myResult = stmt.executeQuery(sql);
             ResultSetMetaData resultMetaData = myResult.getMetaData();
@@ -136 +128 @@
             System.out.println(" Number of columns in table retrieved: " + dbColumn);
 
+
+        /* Step 2: Iterate through each content item, and retrieve the media files that content item has */
+        
+        /* The following is an example SELECT statement (for a hard-wired contentId = 333) that retrieves all the
+           images (both the original name, and the hashed 'path' that maps to the media files on the file
+           system (the contentItem itself at the time of writing was in projectID=4) */
+
+        /*
+SELECT mFile.filename, mFile.path 
+
+FROM td_ManagedFile AS mFile, td_ContentItem AS cItem, td_ContentItem_MediaItem AS cMediaItem, td_MediaItem AS mItem
+
+WHERE
+
+cItem.id = 333
+
+And cMediaItem.contentItemId = cItem.id
+
+and cMediaItem.mediaItemId = mItem.id
+
+And mItem.fileId = mFile.id
+        */
+
+
+        
             //create gson object and have the file write the opening bracket for valid json
             Gson myGSon = new Gson();
@@ -169 +186 @@
                     }
                 }
-               String dbRowAsJson = myGSon.toJson(dbRow);
-
-                    //printing statements for checking output
-               // System.out.println("As normal: " + dbRow);
-               // System.out.println("====");
-                //System.out.println("As JSON: " + anObject);
-
+        String dbRowAsJson = myGSon.toJson(dbRow);
+        
+        
                 //saving to json file
-
                 file.write(dbRowAsJson);