Creating Custom [Client] Tables
Client Schema ([Client]
)
[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 1 FROM sys.tables WHERE [name] = N'MyTable' AND [type] = 'U' AND [schema_id] = (SELECT [schema_id] FROM sys.schemas WHERE [name] = 'Client'))
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 1 FROM sys.tables WHERE [name] = N'MyTable' AND [type] = 'U' AND [schema_id] = (SELECT [schema_id] FROM sys.schemas WHERE [name] = 'Client'))
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.
Updated about 2 years ago