SQL Server
SQL Server
Google Drive - Build a Local Cache of Metadata
See more Google Drive Examples
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 SQL Server Downloads
-- 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 = 0
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
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
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
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
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
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonMaster OUT
DECLARE @jsonMasterArr int
EXEC @hr = sp_OACreate 'Chilkat.JsonArray', @jsonMasterArr OUT
EXEC sp_OAMethod @jsonMaster, 'AppendArray2', @success OUT, 'fileIds', @jsonMasterArr
-- Also keep a list of file paths.
DECLARE @jsonMasterPaths int
EXEC @hr = sp_OACreate 'Chilkat.JsonArray', @jsonMasterPaths OUT
EXEC sp_OAMethod @jsonMaster, 'AppendArray2', @success OUT, 'filePaths', @jsonMasterPaths
-- The default page size is 100, with a max of 1000.
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'pageSize', '200'
DECLARE @json int
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
-- Check to see if the above loop exited with errors...
EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
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 @jsonMasterArr
EXEC @hr = sp_OADestroy @jsonMasterPaths
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 @jsonMasterArr
EXEC @hr = sp_OADestroy @jsonMasterPaths
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
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
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPathForFileId OUT
-- Used for storing the file name and parents[0] in the hashTable.
DECLARE @saFileInfo int
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 = 0
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 @jsonMasterArr
EXEC @hr = sp_OADestroy @jsonMasterPaths
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 @jsonMasterArr
EXEC @hr = sp_OADestroy @jsonMasterPaths
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
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
-- 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 @jsonMasterArr
EXEC @hr = sp_OADestroy @jsonMasterPaths
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