database#

Defines BookmarkDB, the SQLite database interface used to store item properties, such as custom descriptions, flags, width, height, bookmarks.tokens.tokens values, etc.

Each bookmark item has its own database file stored in the root of the bookmark item’s cache folder as defined by common.bookmark_cache_dir.

1f'{server}/{job}/{root}/{common.bookmark_cache_dir}/{common.bookmark_database}'

The database table layout is defined by TABLES. It maps the SQLite column types to the python types used in the application.

To get and set values use get_db(), the preferred database interface getter. This will return cached, thread-specific database controllers. E.g.:

1from bookmarks import database
2
3db = database.get_db(server, job, root)
4v = db.value(*args)

Each BookmarkDB.value() and BookmarkDB.set_value() call will autocommit. You can batch commits together by using the built-in context manager:

1from bookmarks import database
2
3db = database.get_db(server, job, root)
4with db.connection():
5    db.set_value(*args)

There are two tables that hold item data: common.BookmarkTable and common.AssetTable. The asset table is meant to be used as a general table to hold descriptions and notes for all items where the bookmark table contains properties specifically related to the bookmark item.

Warning

Bookmark items should store their description in the asset table as it is a general property, but they don’t and instead use the superfluous bookmark table ‘description’ column. Sorry about that…

Classes:

BookmarkDB(server, job, root[, parent])

Database connector used to interface with the bookmark item's sqlite3 database.

Functions:

b64decode(v)

Base64 decode function.

b64encode(v)

Base64 encode function.

convert_return_values(table, key, value)

Utility function used enforce data types.

get_db(server, job, root[, force])

Creates a database controller associated with a bookmark item.

load_json(value)

Load a base 64 encoded json value.

remove_all_connections()

Closes and deletes all database controller instances.

remove_db(server, job, root)

Removes and closes a cached a bookmark database connection.

set_flag(server, job, root, k, mode, flag)

A utility method used by the base view to set an item flag to the database.

sleep()

Utility script used to sleep for a certain amount of time.

Data:

AssetTable

Database table name

BookmarkTable

Database table name

InfoTable

Database table name

TABLES

sqlite3 database structure definition
class bookmarks.database.BookmarkDB(server, job, root, parent=None)[source]#

Bases: QObject

Database connector used to interface with the bookmark item’s sqlite3 database.

Methods:

close()

Closes the connection.

connect_to_db([memory])

Connects to the database file.

connection()

Returns the connection instance.

get_row(source, table)

Gets a row from the database.

is_valid()

Returns the database's status.

set_value(source, key, value[, table])

Sets the given value in the database.

source(*args)

The source path of the database.

value(source, key, table)

Returns a value from the database.
close()[source]#

Closes the connection.

connect_to_db(memory=False)[source]#

Connects to the database file.

The database can be locked for a brief period of time whilst it is being used by another other controller instance in another thread. This might raise an exception, but it is safe to wait on a little and re-try before deeming the operation unsuccessful.

When a database is unreachable, we’ll create an in-memory database instead, and mark the instance invalid (BookmarkDB.is_valid() will return False).

connection()[source]#

Returns the connection instance.

get_row(source, table)[source]#

Gets a row from the database.

Parameters:

  • source (str) – A source file path.

  • table (str) – A database 01table name.

Returns:

A dictionary of column/value pairs.

Return type:

dict

is_valid()[source]#

Returns the database’s status.

Returns:

True if the database is valid, False otherwise.

Return type:

bool

set_value(source, key, value, table=AssetTable)[source]#

Sets the given value in the database.

1db = database.get_db(server, job, root)
2source = f'//{server}/{job}/{root}/sh0010/scenes/my_scene.ma'
3db.set_value(source, 'description', 'hello world')
Parameters:

  • source (str) – Source file path.

  • key (str) – A database column name.

  • value (object) – The value to set in the database.

  • table (str) – A database table.

source(*args)[source]#

The source path of the database.

Parameters:

args (tuple) – A tuple of path segments to be added to the base path.

Returns:

The source path of the bookmark database.

Return type:

str

value(source, key, table)[source]#

Returns a value from the database.

Parameters:

  • source (str) – Path to a file or folder.

  • key (str) – A column, or a list of columns.

  • table (str, optional) – Optional table parameter, defaults to AssetTable.

Returns:

The value stored in the database, or None.

bookmarks.database.b64decode(v)[source]#

Base64 decode function.

bookmarks.database.b64encode(v)[source]#

Base64 encode function.

bookmarks.database.convert_return_values(table, key, value)[source]#

Utility function used enforce data types.

bookmarks.database.get_db(server, job, root, force=False)[source]#

Creates a database controller associated with a bookmark item.

sqlite3 cannot share the same connection instance between threads, hence we create and cache controllers per thread. The cached entries are stored in common.db_connections.

Parameters:

  • server (str) – server path segment.

  • job (str) – job path segment.

  • root (str) – root path segment.

  • force (bool) – Force retry connecting to the database.

Returns:

Database controller instance.

Return type:

BookmarkDB

Raises:

  • RuntimeError – When the database is locked.

  • OSError – When the database is missing.

bookmarks.database.load_json(value)[source]#

Load a base 64 encoded json value.

bookmarks.database.remove_all_connections()[source]#

Closes and deletes all database controller instances.

bookmarks.database.remove_db(server, job, root)[source]#

Removes and closes a cached a bookmark database connection.

Parameters:

  • server (str) – server path segment.

  • job (str) – job path segment.

  • root (str) – root path segment.

bookmarks.database.set_flag(server, job, root, k, mode, flag)[source]#

A utility method used by the base view to set an item flag to the database.

bookmarks.database.sleep()[source]#

Utility script used to sleep for a certain amount of time.

bookmarks.database.AssetTable = 'AssetData'#

Database table name

bookmarks.database.BookmarkTable = 'BookmarkData'#

Database table name

bookmarks.database.InfoTable = 'InfoData'#

Database table name

bookmarks.database.TABLES = {'AssetData': {'cut_duration': {'sql': 'INT', 'type': <class 'int'>}, 'cut_in': {'sql': 'INT', 'type': <class 'int'>}, 'cut_out': {'sql': 'INT', 'type': <class 'int'>}, 'description': {'sql': 'TEXT', 'type': <class 'str'>}, 'flags': {'sql': 'INT DEFAULT 0', 'type': <class 'int'>}, 'id': {'sql': 'TEXT PRIMARY KEY COLLATE NOCASE', 'type': <class 'str'>}, 'notes': {'sql': 'TEXT', 'type': <class 'dict'>}, 'progress': {'sql': 'TEXT', 'type': <class 'dict'>}, 'shotgun_id': {'sql': 'INT', 'type': <class 'int'>}, 'shotgun_name': {'sql': 'TEXT', 'type': <class 'str'>}, 'shotgun_type': {'sql': 'TEXT', 'type': <class 'str'>}, 'thumbnail_stamp': {'sql': 'REAL', 'type': <class 'float'>}, 'url1': {'sql': 'TEXT', 'type': <class 'str'>}, 'url2': {'sql': 'TEXT', 'type': <class 'str'>}, 'user': {'sql': 'TEXT', 'type': <class 'str'>}}, 'BookmarkData': {'applications': {'sql': 'TEXT', 'type': <class 'dict'>}, 'description': {'sql': 'TEXT', 'type': <class 'str'>}, 'duration': {'sql': 'INT', 'type': <class 'int'>}, 'framerate': {'sql': 'REAL', 'type': <class 'float'>}, 'height': {'sql': 'INT', 'type': <class 'int'>}, 'id': {'sql': 'TEXT PRIMARY KEY COLLATE NOCASE', 'type': <class 'str'>}, 'identifier': {'sql': 'TEXT', 'type': <class 'str'>}, 'prefix': {'sql': 'TEXT', 'type': <class 'str'>}, 'shotgun_api_key': {'sql': 'TEXT', 'type': <class 'str'>}, 'shotgun_domain': {'sql': 'TEXT', 'type': <class 'str'>}, 'shotgun_id': {'sql': 'INT', 'type': <class 'int'>}, 'shotgun_name': {'sql': 'TEXT', 'type': <class 'str'>}, 'shotgun_scriptname': {'sql': 'TEXT', 'type': <class 'str'>}, 'shotgun_type': {'sql': 'TEXT', 'type': <class 'str'>}, 'slacktoken': {'sql': 'TEXT', 'type': <class 'str'>}, 'startframe': {'sql': 'INT', 'type': <class 'int'>}, 'teamstoken': {'sql': 'TEXT', 'type': <class 'str'>}, 'tokens': {'sql': 'TEXT', 'type': <class 'dict'>}, 'url1': {'sql': 'TEXT', 'type': <class 'str'>}, 'url2': {'sql': 'TEXT', 'type': <class 'str'>}, 'width': {'sql': 'INT', 'type': <class 'int'>}}, 'InfoData': {'created': {'sql': 'REAL NOT NULL', 'type': <class 'float'>}, 'host': {'sql': 'TEXT NOT NULL', 'type': <class 'str'>}, 'id': {'sql': 'TEXT PRIMARY KEY COLLATE NOCASE', 'type': <class 'str'>}, 'job': {'sql': 'TEXT NOT NULL', 'type': <class 'str'>}, 'root': {'sql': 'TEXT NOT NULL', 'type': <class 'str'>}, 'server': {'sql': 'TEXT NOT NULL', 'type': <class 'str'>}, 'user': {'sql': 'TEXT NOT NULL', 'type': <class 'str'>}}}#

sqlite3 database structure definition