Client Schema (`[Client]`)

Database schemas are logical collections of data objects. The Disco Database has the following schemas:

Client schema ([Client]) - Contains all tables created and maintained in the Client Extension
DBO schema ([dbo]) - Contains DirectScale tables

You need to create your tables in the [Client] schema, which exists in your Database instance.

Both schema names appear in custom reports and data viewed with the SQL Manager or Data Editor.

Creating Custom Tables

Using the ClientConnectionString from the _dataService, write a custom API endpoint or DBDeploy script that calls the following sample code. You can use this code as a starting point.

private int CreateMyTable()
        {
            try
            {
                using (var dbConnection = new SqlConnection(_dataService.ClientConnectionString.ConnectionString))
                {
                    var query = @"
                    IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = N'[Client].[MyTable]' AND type = 'U')
                    BEGIN
                        CREATE TABLE [Client].[MyTable]
                        (
                            [recordnumber] int NOT NULL IDENTITY(1, 1),
                            [last_modified] DATETIME CONSTRAINT DF_MyTable_last_modified DEFAULT (GETDATE()) NOT NULL,
                            [MyUsefulField] varchar(150) NULL
                            CONSTRAINT [MyTable_PrimaryKey] PRIMARY KEY CLUSTERED 
                                (
                                    [recordnumber]
                                ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
                        );
                    END
                    CREATE INDEX ExampleIndex ON [Client].[MyTable] (MyUsefulField);
                    ";
                    dbConnection.Execute(query);
                }
                return 0;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

How it Works

using (var dbConnection = new SqlConnection(_dataService.ClientConnectionString.ConnectionString))

ClientConnectionString is a method in the DataService that provides a read-write connection string that can only access [Client] tables.

var query = @"

The preceding example function uses query, which is a method defined in Dapper. However, using Dapper is optional. You can use whatever method of data access you want. If you're going to use this example code:

Install the Dapper NuGet package.
Add using Dapper; at the top of the file.

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = N'[Client].[MyTable]' AND type = 'U')

We're checking if the table has been previously created. If it hasn't, then we'll create the table. If it has, then we'll update it.

Important: Client Custom tables must have recordnumber (int) Primary Key and last_modified (DateTime) fields.

CREATE INDEX ExampleIndex ON [Client].[MyTable] (MyUsefulField);

Indexes are a fast way to retrieve data. If you anticipate your table storing large amounts of data, then it's a good idea to create an Index for your tables.

Client Schema ([Client])

Creating Custom Tables

How it Works

Client Schema (`[Client]`)