I develop software for the trucking industry and I want to be able to have fully dynamic schema. Let me give you an overview of the scenario. The Data Abstract server would be hosted on Azure. A central database would contain the user account details (user name, password) for my users and the database connection string parameters for their customer specific database. The schema for the customer specific database will vary from customer to customer. So, when a client application logs in the server will know the database log in credentials based on the user that signed in. My thinking is that the schema might be built by querying the structure of the database or possibly created using some form of schema builder tool like what you provide.
The goal of this is to synchronize data between a local database and the cloud databases on Azure. The software then runs off the local SQL Server instance so it can keep running trucks even if the Internet connection is down.
Right now the RemObjects SDK client / server that I’m using are completely ignorant of the schema. The client connects to the local database, queries the schema, then uses this schema to format sql statements and send them to the server. The server executes the select statement and streams the resulting DataTable to a Binary object in XML format. The client then reads the XML back into a DataTable object. When a record needs to be updated the row is updated in the DataTable and the DataTable is sent back to the server via a Binary object. The server iterates the rows and applies the changes to the remote database. Neither side knows the schema of the database and I can use this setup with any application / database schema without making changes. This does not currently interact with Azure it is purely for synchronizing data between different locations such as a central office. The central office adds new customers, orders, etc. and those get pushed down to the remote offices. Transactions are recorded locally and sent back to the central office. Technically I could update the server to save the database connection string into the session and use it accordingly. It would be nice to utilize some of the more advanced features of Data Abstract such as scripting change notices and so on.
Yes, it is possible to build Schema at runtime (after all Schema Modeler and Project Wizard do exactly that). It is even possible to store the schema in CosmosDB to persist it between user logins.
The only disadvantage is that the DA LINQ features won’t be available if the schema is fully dynamic, as DA LINQ relies on typed representation of schema tables in the client code.
DA SQL and DataTable-based data requests should work without issues.
Yes, this is relatively simple task. Assuming user Session already contains Schema tou will need to handle the DataService event BeforeFindServiceSchema as (this is Oxygene code, but you will see the idea):
method DataService.DataService_BeforeFindServiceSchema(sender: Object; e: BeforeFindServiceSchemaEventArgs);
begin
e.ServiceSchema := ...get Schema object from session...
end;
This code (actually it is a full console app code) creates a Schema file for the sample SQLite database (only Tables):
using RemObjects.DataAbstract.Schema;
using RemObjects.DataAbstract.Server;
namespace SchemaBuilder
{
class Program
{
static void Main(string[] args)
{
// Load DA configuration. This step is done by any application that use Data Abstract server-side code
RemObjects.DataAbstract.Server.Configuration.Load();
var schema = new Schema();
// Create Schema
using var connection = new BaseConnection("<< Connection >>", @"SQLite.NET?Server=localhost;Database=C:\Users\Public\Documents\RemObjects Samples\Database\PCTrade.sqlite.db;");
connection.Open();
// Get database table names
var tableNames = connection.GetTableNames();
foreach (var tableName in tableNames)
{
var schemaTable = new SchemaDataTable();
// General info
schemaTable.Name = tableName.StartsWith("dbo.") ? tableName.Substring(4) : tableName;
schemaTable.IsPublic = true;
// Table fields
var tableFields = connection.GetTableFields(tableName, true, null);
foreach (var tableField in tableFields)
{
schemaTable.Fields.Add(tableField);
}
// Table statement
var schemaStatement = new SchemaSQLStatement();
schemaStatement.ConnectionType = connection.ConnectionType;
schemaStatement.StatementType = StatementType.AutoSQL;
schemaStatement.TargetTable = tableName;
// Statement mapping
foreach (var tableField in tableFields)
{
schemaStatement.ColumnMappings.Add(new SchemaColumnMapping(tableField.Name, tableField.Name, tableField.Name));
}
schemaTable.Statements.Add(schemaStatement);
schema.DataTables.Add(schemaTable);
}
connection.Close();
// Write down Schema to a file
schema.Serialization.SaveToFile(@"c:\Temp\Schema.daSchema");
}
}
}
Relativity Server is great if you have a static database schema. I want the server to be able to adjust itself accordingly to the database schema and the user that is logged in without having to go back and manually update the schema in Relativity Server.