=================
Database Facility
=================

About
-----

Databases play an integral role in storing and aggregating data in distributed environments. The Database Management Facility implemented in ``omni.services.facilities.database.manager`` offers APIs to manage the lifecycle of databases, abstracting away the low-level concerns of instantiation and connection pooling in order to simplify queries.

Configuration
-------------

Database configuration options for SQLite, MySQL or PostgreSQL are expected to be found in the application settings, under the ``dbs`` property of the Extension which is initiating the Facility. The following TOML settings provide an example of configuration for a MySQL database to be used by an Extension initializing the Facility:

.. code-block:: toml
   :linenos:
   :caption: extension.toml

   # [...]

   [settings.exts."my.extension.name".dbs.database-handle]
   connection_string = "mysql:///host.name"
   min_size = 4
   max_size = 10

Note that the ``connection_string`` property is required for all databases, but any other property supplied will be forwarded to `databases`, the underlying Python package powering the database abstraction layer. See https://www.encode.io/databases for additional information about available configuration options.

Any additional arguments can also be supplied as querystring arguments to the connection string. For example, the following is functionally equivalent to the settings above:

.. code-block:: toml
   :linenos:
   :caption: extension.toml
   :emphasize-lines: 4

   # [...]

   [settings.exts."my.extension.name".dbs.database-handle]
   connection_string = "mysql:///host.name?min_size=4&max_size=10"

Usage
-----

Once provided, a connection to a database can be obtained by referring to its handle, provided as key to the ``dbs`` property of the Extension settings. For example, using the configuration sample provided above:

.. code-block:: python
   :linenos:
   :caption: sample.py

   database_facility = DatabaseManagerFacility("my.extension.name")
   async with database_facility.get("database-handle") as db:
       # Create a table:
       query = "CREATE TABLE IF NOT EXISTS HighScores (id INTEGER PRIMARY KEY, name VARCHAR(100), score INTEGER)"
       await db.execute(query=query)

       # Insert some data:
       query = "INSERT INTO HighScores(name, score) VALUES (:name, :score)"
       values = [
           {"name": "Daisy", "score": 92},
           {"name": "Neil", "score": 87},
           {"name": "Carol", "score": 43},
       ]
       await database.execute_many(query=query, values=values)

       # Execute a database query:
       query = "SELECT * FROM HighScores"
       rows = await database.fetch_all(query=query)
       print("High scores:", rows)

Implementation References
-------------------------

For examples of the Database Facility being used in the Omniverse Farm ecosystem, consult the following extensions:

 * ``omni.services.facilities.database.manager``: Reference implementation of Database management features
 * ``omni.services.farm.facilities.store.db``: Database storage for Omniverse Farm tasks