Files

Update, delete, and query the available Files in Box.

Table Specific Information

Select

If you search for files without specifying any condition in the WHERE Clause, by default only the files that are at most 5 levels deep from the root folder will be returned. You can change the default depth value in the connection string; e.g. DirectoryRetrievalDepth=10.

SELECT * FROM Files

By default, the search starts from the root directory, identified as directory '0'. To customize the starting directory for your search, specify its ID using the SearchRootId column.

SELECT * FROM Files WHERE SearchRootId = '293533136411'

To search all the Files in your enterprise, query the Files table with the column SearchTerms.

SELECT * FROM Files WHERE SearchTerms LIKE '%untitled%'

To search all the Files within a specific folder, query the Files table with a filter on the relevant folder's Id in the ParentId column.

SELECT * FROM Files WHERE ParentId = '12'

Note: When using a ParentId filter, avoid specifying a SearchRootId simultaneously. If both are used, the search will recursively start from the specified SearchRootId instead of the ParentId, which may result in slower performance.

Update

Any column where ReadOnly=False can be updated.

UPDATE Files SET Description = 'example description', Sharedlink = 'http://sharedlink.com', OwnedbyId = '321', ParentId = '12', Name = 'updated file name' WHERE Id = '123'

Delete

Files can be deleted by providing an Id and issuing a DELETE statement. This file is then moved to TrashedItems

DELETE FROM Files WHERE Id = '100'

Columns


Name	Type	ReadOnly	Description
SearchTerms	String	True	Query to search the files database.
Id [KEY]	String	True	The Id of the file.
Name	String	False	The name of the file.
Sha1	String	False	The SHA-1 encryption of the file.
Etag	String	False	The Etag of the file
SequenceId	String	False	The sequence Id of the file.
Description	String	False	The description of the file.
Size	Integer	True	The size of the file.
CreatedAt	Datetime	True	The date the file was created at.
ModifiedAt	Datetime	True	The date the file was modified at.
TrashedAt	Datetime	True	The date the file was trashed at.
PurgedAt	Datetime	True	The date the file was purged at.
ContentCreatedAt	Datetime	True	The date the content of the file was created at.
ContentModifiedAt	Datetime	True	The date the content of the file was modified at.
CreatedById	String	True	The Id of the user that created the file.
CreatedByName	String	True	The Name of the user that created the file.
CreatedByLogin	String	True	The Login of the user that created the file.
SharedLink	String	False	The shared url of the file.
ModifiedById	String	True	The Id of the user that last modified the file.
ModifiedByName	String	True	The Name of the user that last modified the file.
ModifiedByLogin	String	True	The Login of the user that last modified the file.
OwnedById	String	False	The Id of the user that owns the file.
OwnedByName	String	False	The Name of the user that owns the file.
OwnedByLogin	String	False	The Login of the user that owns the file.
ParentId	String	False	The Id of the folder that contains the file.
ItemStatus	String	False	The status of the file.
Path	String	True	The full path of the file.
SearchRootId	String	True	The folder Id to start the recursive search through the folder tree. Default is '0' as root folder.

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。


Name	Type	Description
Scope	String	The scope of the search.
FileExtension	String	The extension of the file.
ContentTypes	String	The content to search the query at, separated by commas. This can contain a mixture of the following: name, file_content, description, comments, tags.
OwnerUserIDs	String	The Ids of the owners to limit the search to, separated by a comma.
AncestorfolderIDs	String	The Ids of the folders to limit the search to, separated by a comma.
AsUserId	String	The Id of the user you want to impersonate. Only works with Admin, Co-Admin and Service Accounts.