(SQL Server) Google Drive - Build a Local Cache of Metadata

This example demonstrates how to download the metadata for all files in a Google Drive account to create a local filesystem cache with the information. The cache can be used to fetch information without having to query Google Drive.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    DECLARE @iTmp1 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @sTmp1 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 1

    -- It requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- This example uses a previously obtained access token having permission for the 
    -- Google Drive scope.

    DECLARE @gAuth int
    -- Use "Chilkat_9_5_0.AuthGoogle" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.AuthGoogle', @gAuth OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @gAuth, 'AccessToken', 'GOOGLE_DRIVE_ACCESS_TOKEN'

    DECLARE @rest int
    -- Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT

    -- Connect using TLS.
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'www.googleapis.com', 443, 1, @bAutoReconnect

    -- Provide the authentication credentials (i.e. the access token)
    EXEC sp_OAMethod @rest, 'SetAuthGoogle', @success OUT, @gAuth

    -- -------------------------------------------------------------------
    -- Initialize our cache object.  Indicate the location of the root cache directory, and how many cache levels are to exist.
    -- For small caches (level 0) all cache files are in the root directory.
    -- For medium caches (level 1) cache files are located in 256 sub-directories from the root.
    -- For large caches (level 2) cache files are located in 256x256 sub-directories two levels down from the root.
    DECLARE @gdCache int
    -- Use "Chilkat_9_5_0.Cache" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cache', @gdCache OUT

    EXEC sp_OASetProperty @gdCache, 'Level', 0
    -- Use a root directory that makes sense on your operating system..
    EXEC sp_OAMethod @gdCache, 'AddRoot', NULL, 'C:/ckCache/googleDrive'

    -- If we are re-building the cache, we can first delete the entire contents of the cache.
    DECLARE @numCacheFilesDeleted int
    EXEC sp_OAMethod @gdCache, 'DeleteAll', @numCacheFilesDeleted OUT

    -- Create a date/time object with an time 7 days from the current date/time.
    DECLARE @dtExpire int
    -- Use "Chilkat_9_5_0.CkDateTime" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dtExpire OUT

    EXEC sp_OAMethod @dtExpire, 'SetFromCurrentSystemTime', @success OUT
    EXEC sp_OAMethod @dtExpire, 'AddDays', @success OUT, 7

    -- Indicate that we want ALL possible fields.
    -- If no fields are indicated, then only the basic fields are returned.
    DECLARE @allFields nvarchar(4000)
    SELECT @allFields = 'appProperties,capabilities,contentHints,createdTime,description,explicitlyTrashed,fileExtension,folderColorRgb,fullFileExtension,headRevisionId,iconLink,id,imageMediaMetadata,isAppAuthorized,kind,lastModifyingUser,md5Checksum,mimeType,modifiedByMeTime,modifiedTime,name,originalFilename,ownedByMe,owners,parents,permissions,properties,quotaBytesUsed,shared,sharedWithMeTime,sharingUser,size,spaces,starred,thumbnailLink,trashed,version,videoMediaMetadata,viewedByMe,viewedByMeTime,viewersCanCopyContent,webContentLink,webViewLink,writersCanShare'

    -- We're going to keep a master list of fileId's as we iterate over all the files in this Google Drive account.
    -- This master list will also be saved to the cache under the key "AllGoogleDriveFileIds".
    DECLARE @jsonMaster int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonMaster OUT

    DECLARE @jsonMasterArr int
    EXEC sp_OAMethod @jsonMaster, 'AppendArray', @jsonMasterArr OUT, 'fileIds'
    -- Also keep a list of file paths.
    DECLARE @jsonMasterPaths int
    EXEC sp_OAMethod @jsonMaster, 'AppendArray', @jsonMasterPaths OUT, 'filePaths'

    -- The default page size is 100, with a max of 1000.
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'pageSize', '200'

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    DECLARE @jsonFileMetadata int

    DECLARE @i int

    DECLARE @numFiles int

    -- Send the request for the 1st page.
    DECLARE @jsonResponse nvarchar(4000)
    EXEC sp_OAMethod @rest, 'FullRequestNoBody', @jsonResponse OUT, 'GET', '/drive/v3/files'

    DECLARE @pageNumber int
    SELECT @pageNumber = 1
    DECLARE @pageToken nvarchar(4000)

    DECLARE @bContinueLoop int
    EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp1 OUT
    SELECT @bContinueLoop = @iTmp0 and (@iTmp1 = 200)

    WHILE @bContinueLoop = 1
      BEGIN



        PRINT '---- Page ' + @pageNumber + ' ----'
        EXEC sp_OAMethod @json, 'Load', @success OUT, @jsonResponse

        EXEC sp_OAMethod @json, 'SizeOfArray', @numFiles OUT, 'files'
        SELECT @i = 0
        WHILE @i < @numFiles
          BEGIN
            -- Add this file ID to the master list.
            EXEC sp_OASetProperty @json, 'I', @i
            EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'files[i].id'
            EXEC sp_OAMethod @jsonMasterArr, 'AddStringAt', @success OUT, -1, @sTmp0

            SELECT @i = @i + 1
          END

        -- Get the next page of files.
        -- If the "nextPageToken" is present in the JSON response, then use it in the "pageToken" parameter
        -- for the next request.   If no "nextPageToken" was present, then this was the last page of files.
        EXEC sp_OAMethod @json, 'StringOf', @pageToken OUT, 'nextPageToken'
        SELECT @bContinueLoop = 0
        DECLARE @bHasMorePages int
        EXEC sp_OAGetProperty @json, 'LastMethodSuccess', @bHasMorePages OUT
        IF @bHasMorePages = 1
          BEGIN
            EXEC sp_OAMethod @rest, 'ClearAllQueryParams', @success OUT
            EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'pageSize', '200'
            EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'pageToken', @pageToken
            EXEC sp_OAMethod @rest, 'FullRequestNoBody', @jsonResponse OUT, 'GET', '/drive/v3/files'
            EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
            EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp1 OUT
            SELECT @bContinueLoop = @iTmp0 and (@iTmp1 = 200)
            SELECT @pageNumber = @pageNumber + 1
          END

      END

    EXEC @hr = sp_OADestroy @jsonMasterArr

    -- Check to see if the above loop exited with errors...
    EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @gAuth
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @gdCache
        EXEC @hr = sp_OADestroy @dtExpire
        EXEC @hr = sp_OADestroy @jsonMaster
        EXEC @hr = sp_OADestroy @json
        RETURN
      END
    -- Check to see if the above loop exited with errors...
    -- A successful response will have a status code equal to 200.
    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN

        EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
        PRINT 'response status code = ' + @iTmp0

        EXEC sp_OAGetProperty @rest, 'ResponseStatusText', @sTmp0 OUT
        PRINT 'response status text = ' + @sTmp0

        EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
        PRINT 'response header: ' + @sTmp0

        PRINT 'response JSON: ' + @jsonResponse
        EXEC @hr = sp_OADestroy @gAuth
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @gdCache
        EXEC @hr = sp_OADestroy @dtExpire
        EXEC @hr = sp_OADestroy @jsonMaster
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- Iterate over the file IDs and download the metadata for each, saving each to the cache...
    -- Also, keep in-memory hash entries of the name and parent[0] so we can quickly 
    -- build the path-->fileId cache entries. (Given that the Google Drive REST API uses
    -- fileIds, this gives us an easy way to lookup a fileId based on a filePath.)
    DECLARE @hashTable int
    -- Use "Chilkat_9_5_0.Hashtable" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Hashtable', @hashTable OUT

    -- Set the capacity of the hash table to something reasonable for the number of files
    -- to be hashed.
    EXEC sp_OAMethod @hashTable, 'ClearWithNewCapacity', @success OUT, 521

    DECLARE @sbPathForFileId int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPathForFileId OUT

    -- Used for storing the file name and parents[0] in the hashTable.
    DECLARE @saFileInfo int
    -- Use "Chilkat_9_5_0.StringArray" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringArray', @saFileInfo OUT

    EXEC sp_OASetProperty @saFileInfo, 'Unique', 0

    DECLARE @fileId nvarchar(4000)

    EXEC sp_OAMethod @jsonMaster, 'SizeOfArray', @numFiles OUT, 'fileIds'
    SELECT @i = 0
    WHILE @i < @numFiles
      BEGIN
        EXEC sp_OASetProperty @jsonMaster, 'I', @i
        EXEC sp_OAMethod @jsonMaster, 'StringOf', @fileId OUT, 'fileIds[i]'
        EXEC sp_OAMethod @sbPathForFileId, 'SetString', @success OUT, '/drive/v3/files/'
        EXEC sp_OAMethod @sbPathForFileId, 'Append', @success OUT, @fileId

        EXEC sp_OAMethod @rest, 'ClearAllQueryParams', @success OUT
        EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'fields', @allFields
        EXEC sp_OAMethod @sbPathForFileId, 'GetAsString', @sTmp0 OUT
        EXEC sp_OAMethod @rest, 'FullRequestNoBody', @jsonResponse OUT, 'GET', @sTmp0
        EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
        EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp1 OUT
        IF (@iTmp0 <> 1) or (@iTmp1 <> 200)
          BEGIN
            -- Force an exit of this loop..
            SELECT @numFiles = 0
          END

        -- Save this file's metadata to the local cache.
        -- The lookup key is the fileId.
        EXEC sp_OAMethod @gdCache, 'SaveTextDt', @success OUT, @fileId, @dtExpire, '', @jsonResponse

        -- Get this file's name and parent[0], and put this information
        -- in our in-memory hashtable to be used below..
        EXEC sp_OAMethod @json, 'Load', @success OUT, @jsonResponse

        EXEC sp_OAMethod @saFileInfo, 'Clear', NULL
        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'name'
        EXEC sp_OAMethod @saFileInfo, 'Append', @success OUT, @sTmp0
        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'parents[0]'
        EXEC sp_OAMethod @saFileInfo, 'Append', @success OUT, @sTmp0
        EXEC sp_OAMethod @saFileInfo, 'Serialize', @sTmp0 OUT
        EXEC sp_OAMethod @hashTable, 'AddStr', @success OUT, @fileId, @sTmp0

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'name'

        EXEC sp_OAMethod @json, 'StringOf', @sTmp1 OUT, 'parents[0]'
        PRINT @sTmp0 + ', ' + @sTmp1

        SELECT @i = @i + 1
      END

    -- Check to see if the above loop exited with errors...
    EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @gAuth
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @gdCache
        EXEC @hr = sp_OADestroy @dtExpire
        EXEC @hr = sp_OADestroy @jsonMaster
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @hashTable
        EXEC @hr = sp_OADestroy @sbPathForFileId
        EXEC @hr = sp_OADestroy @saFileInfo
        RETURN
      END
    -- Check to see if the above loop exited with errors...
    -- A successful response will have a status code equal to 200.
    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN

        EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
        PRINT 'response status code = ' + @iTmp0

        EXEC sp_OAGetProperty @rest, 'ResponseStatusText', @sTmp0 OUT
        PRINT 'response status text = ' + @sTmp0

        EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
        PRINT 'response header: ' + @sTmp0

        PRINT 'response JSON: ' + @jsonResponse
        EXEC @hr = sp_OADestroy @gAuth
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @gdCache
        EXEC @hr = sp_OADestroy @dtExpire
        EXEC @hr = sp_OADestroy @jsonMaster
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @hashTable
        EXEC @hr = sp_OADestroy @sbPathForFileId
        EXEC @hr = sp_OADestroy @saFileInfo
        RETURN
      END

    -- Now that all the fileId's are in the cache, let's build the directory path
    -- for each fileID.  

    -- (Technically, a fileId can have multiple parents, which means it can be in multiple directories
    -- at once.  This is only going to build directory paths following the 0'th parent ID in the parents list.)

    -- The directory path for files in "My Drive" will be just the filename.
    -- For files in sub-directories, the path will be relative, such as "subdir1/subdir2/something.pdf"
    -- 


    PRINT '---- building paths ----'

    DECLARE @sbPath int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPath OUT

    EXEC sp_OAMethod @jsonMaster, 'SizeOfArray', @numFiles OUT, 'fileIds'
    SELECT @i = 0
    WHILE @i < @numFiles
      BEGIN
        EXEC sp_OASetProperty @jsonMaster, 'I', @i

        EXEC sp_OAMethod @sbPath, 'Clear', NULL

        EXEC sp_OAMethod @jsonMaster, 'StringOf', @fileId OUT, 'fileIds[i]'
        DECLARE @bFinished int
        SELECT @bFinished = 0
        WHILE (@bFinished = 0)
          BEGIN
            EXEC sp_OAMethod @saFileInfo, 'Clear', NULL
            EXEC sp_OAMethod @hashTable, 'LookupStr', @sTmp0 OUT, @fileId
            EXEC sp_OAMethod @saFileInfo, 'AppendSerialized', @success OUT, @sTmp0
            -- Append this file or directory name.
            EXEC sp_OAMethod @saFileInfo, 'GetString', @sTmp0 OUT, 0
            EXEC sp_OAMethod @sbPath, 'Prepend', @success OUT, @sTmp0
            -- Get the parent fileId
            EXEC sp_OAMethod @saFileInfo, 'GetString', @fileId OUT, 1
            -- If this fileId is not in the hashtable, then it's the fileId for "My Drive", and we are finished.
            EXEC sp_OAMethod @hashTable, 'Contains', @iTmp0 OUT, @fileId
            IF @iTmp0 = 0
              BEGIN
                SELECT @bFinished = 1
              END
            ELSE
              BEGIN
                EXEC sp_OAMethod @sbPath, 'Prepend', @success OUT, '/'
              END
          END


        EXEC sp_OAMethod @sbPath, 'GetAsString', @sTmp0 OUT
        PRINT @i + ': ' + @sTmp0

        -- Store the filePath --> fileId mapping in our local cache.
        EXEC sp_OAMethod @jsonMaster, 'StringOf', @fileId OUT, 'fileIds[i]'
        EXEC sp_OAMethod @sbPath, 'GetAsString', @sTmp0 OUT
        EXEC sp_OAMethod @gdCache, 'SaveTextDt', @success OUT, @sTmp0, @dtExpire, '', @fileId

        EXEC sp_OAMethod @sbPath, 'GetAsString', @sTmp0 OUT
        EXEC sp_OAMethod @jsonMasterPaths, 'AddStringAt', @success OUT, -1, @sTmp0

        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @jsonMasterPaths

    -- Save the master list of file IDs and file paths to the local cache.
    EXEC sp_OASetProperty @jsonMaster, 'EmitCompact', 0
    DECLARE @strJsonMaster nvarchar(4000)
    EXEC sp_OAMethod @jsonMaster, 'Emit', @strJsonMaster OUT
    EXEC sp_OAMethod @gdCache, 'SaveTextNoExpire', @success OUT, 'AllGoogleDriveFileIds', '', @strJsonMaster

    PRINT 'JSON Master Record:'

    PRINT @strJsonMaster

    -- The JSON Master Cache Record looks something like this:
    -- An application can load the JSON master record and iterate over all the files
    -- in Google Drive by file ID, or by path.  
    -- {
    --   "fileIds": [
    --     "0B53Q6OSTWYolQlExSlBQT1phZXM",
    --     "0B53Q6OSTWYolVHRPVkxtYWFtZkk",
    --     "0B53Q6OSTWYolRGZEV3ZGUTZfNFk",
    --     "0B53Q6OSTWYolS2FXSjliMXQxSU0",
    --     "0B53Q6OSTWYolZUhxckMzb0dRMzg",
    --     "0B53Q6OSTWYolbUF6WS1Gei1oalk",
    --     "0B53Q6OSTWYola296ODZUSm5GYU0",
    --     "0B53Q6OSTWYolbTE3c3J5RHBUcHM",
    --     "0B53Q6OSTWYolTmhybWJSUGd5Q2c",
    --     "0B53Q6OSTWYolY2tPU1BnYW02T2c",
    --     "0B53Q6OSTWYolTTBBR2NvUE81Zzg",
    --   ],
    --   "filePaths": [
    --     "testFolder/abc/123/pigs.json",
    --     "testFolder/starfish20.jpg",
    --     "testFolder/penguins2.jpg",
    --     "testFolder/starfish.jpg",
    --     "testFolder/abc/123/starfish.jpg",
    --     "testFolder/abc/123/penguins.jpg",
    --     "testFolder/abc/123",
    --     "testFolder/abc",
    --     "testFolder/testHello.txt",
    --     "testFolder",
    --     "helloWorld.txt",
    --   ]
    -- }


    PRINT 'Entire cache rebuilt...'

    EXEC @hr = sp_OADestroy @gAuth
    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @gdCache
    EXEC @hr = sp_OADestroy @dtExpire
    EXEC @hr = sp_OADestroy @jsonMaster
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @hashTable
    EXEC @hr = sp_OADestroy @sbPathForFileId
    EXEC @hr = sp_OADestroy @saFileInfo
    EXEC @hr = sp_OADestroy @sbPath


END
GO