For my test/automatic build suite I wish to buil the DA schema automatically.
Because I have full control of the database design I think will be a timesaver to do something like:
???.exe --build --connectionName:name
or something like.
I don’t mind if need to build the tool myself, if only exist a way to do it…
Hello,
You can’t do it from a command line, but you can use “Tools->Wizzards->Publish Tables” command in schema modeler for creating datatables and delta commands.
Is not possible to generate it by code? Is possible to mimic Publish tables using the RO clasess?
Hello,
It’s not possible to generate it by code. You should do it using SchemaModeler.
Ok, that is a bummer, but thanks.
I apologize, but the answer above is not correct. Data Abstract exposes all the API needed to load database metadata and to create a Service Schema based on this metadata. Below is an C# console app code that creates a Schema based on provided connection string. Please notify us if you need the same code in Delphi.
You need to create new C# Console Application. Set its TargetFramework to full 3.5 or 4.0 framework (ie not Client Profile). After this add references to assemblies
RemObjects.DataAbstract
and
RemObjects.DataAbstract.Server
And use this code (note that Schema file name and connection string are hardcoded in the Main method. Feel free to change this code to, say, console app that accepts all parameters via command line parameters etc
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Linq;
using RemObjects.DataAbstract.Server;
using RemObjects.DataAbstract.Schema;
namespace ConsoleApplication2
{
class Program
{
static void Main(String[] args)
{
// Needed in any Data Abstract application
// Load configuration
if (!RemObjects.DataAbstract.Server.Configuration.Loaded)
RemObjects.DataAbstract.Server.Configuration.Load();
String connectionString = @"MSSQL.NET?Server=localhost;Database=Northwind;Integrated Security=SSPI";
String schemaFileName = @"U:\TestSchema.daSchema";
ServiceSchema lSchema = GenerateSchema(connectionString);
lSchema.Serialization.SaveToFile(schemaFileName);
}
static ServiceSchema GenerateSchema(String connectionString)
{
ServiceSchema lSchema = new ServiceSchema();
// 0. Determine connection type
String connectionType = connectionString.Substring(0, connectionString.IndexOf('?'));
// 1. Connect to the database
using (BaseConnection connection = new BaseConnection("<>", connectionString, true))
{
// 2. Get Database Table names
StringCollection tables = new StringCollection();
connection.GetTableNames(ref tables);
// 3. Get Database View names
StringCollection views = new StringCollection();
connection.GetViewNames(ref views);
// 4. Combine table and name collections
List entities = tables.Cast().ToList();
entities.AddRange(views.Cast());
// 5. Create Schema Data Tables
for (Int32 i = 0; i < entities.Count; i++)
{
String entityName = entities[i];
SchemaDataTable schemaTable = new SchemaDataTable { Name = entityName, IsPublic = true };
// Load table fields info
SchemaFieldCollection tableFields = new SchemaFieldCollection();
connection.GetTableFields(entityName, ref tableFields, true);
// Add all fields to the Schema Table definition
foreach (SchemaField field in tableFields)
schemaTable.Fields.Add(field);
// Add AutoSQL statement
SchemaSQLStatement statement = new SchemaSQLStatement { ConnectionType = connectionType, StatementType = StatementType.AutoSQL, TargetTable = entityName };
foreach (SchemaField field in tableFields)
statement.ColumnMappings.Add(new SchemaColumnMapping(field.Name, field.Name, field.Name));
schemaTable.Statements.Add(statement);
// Add created Schema Data Table to the Schema
lSchema.DataTables.Add(schemaTable);
}
// 6. Add table relationships
SchemaRelationshipCollection relationships = connection.GetForeignKeys();
foreach (SchemaRelationship relationship in relationships)
{
String relationshipName = String.Format("FK_{0}_{1}", relationship.DetailDataTableName, relationship.MasterDataTableName);
SchemaRelationship schemaRelationship;
if (lSchema.Relationships.FindItem(relationshipName, out schemaRelationship))
{
schemaRelationship.MasterFields = schemaRelationship.MasterFields + "," + relationship.MasterFields;
schemaRelationship.DetailFields = schemaRelationship.DetailFields + "," + relationship.DetailFields;
}
else
{
relationship.Name = relationshipName;
lSchema.Relationships.Add(relationship);
}
}
// 7. Add StoredProcedures (as Commands)
StringCollection storedProcedures = new StringCollection();
connection.GetStoredProcedureNames(ref storedProcedures);
foreach (String storedProcedure in storedProcedures)
{
SchemaCommand schemaCommand = new SchemaCommand { Name = storedProcedure, IsPublic = true };
SchemaParameterCollection commandParameters = new SchemaParameterCollection();
connection.GetCommandParams(storedProcedure, ref commandParameters, true);
foreach (SchemaParameter parameter in commandParameters)
schemaCommand.Parameters.Add(parameter);
SchemaSQLStatement statement = new SchemaSQLStatement { ConnectionType = connectionType, StatementType = StatementType.StoredProcedure, SQL = storedProcedure };
schemaCommand.Statements.Add(statement);
lSchema.Commands.Add(schemaCommand);
};
};
return lSchema;
}
}
}
Hello,
Here is a code in Delphi:
program Project1;
{$APPTYPE CONSOLE}
uses
SysUtils,
uROComInit,
uDAClasses,
uDAInterfaces,
uROClasses,
uDAIBXDriver,
uDADriverManager;
const
aConnStr = 'IBX?Server=localhostatabase=C:\Program Files\Firebird\Firebird_2_1\examples\empbuild\EMPLOYEE.FDB;UserID=sysdba;Password=masterkey;';
aConnName = 'IBX Connection';
aSchemaFileName = 'c:\TestSchema.daSchema';
aConnFileName = 'c:\TestSchema.daConnections';
var
aSchema:TDASchema;
aConnManager:TDAConnectionManager;
aDriverManager:TDADriverManager;
aConn:IDAConnection;
aTables, aViews, entities:IROStrings;
aFields:TDAFieldCollection;
aDataSet:TDADataset;
aStatement:TDAStatement;
I,J:Integer;
begin
aSchema := TDASchema.Create(nil);
aConnManager := TDAConnectionManager.Create(nil);
aDriverManager := TDADriverManager.Create(nil);
try
aConnManager.PoolingEnabled := False;
aConnManager.DriverManager := aDriverManager;
aSchema.ConnectionManager := aConnManager;
with aConnManager.Connections.Add do
begin
Name := aConnName;
ConnectionString := aConnStr;
Default := True;
end;
aConn := aConnManager.NewConnection(aConnName);
aConn.GetTableNames(aTables);
aConn.GetViewNames(aViews);
entities := NewROStrings();
entities.AddStrings(aTables.Strings);
entities.AddStrings(aViews.Strings);
for I: to entities.Count-1 do
begin
aConn.GetTableFields(entities[I], aFields);
aDataSet := aSchema.Datasets.Add();
aDataSet.Name := entities[I];
aStatement := aDataSet.Statements.Add();
aStatement.StatementType := stSQL;
aStatement.TargetTable := aDataSet.Name;
aStatement.Default := True;
aStatement.Connection := aConn.Name;
aStatement.TargetTable := entities[I];
aStatement.StatementType := stAutoSQL;
aConn.GetTableFields(entities[I], aFields);
aDataSet.Fields.AssignFieldCollection(aFields);
with aStatement.ColumnMappings do
begin
for J := 0 to (aFields.Count-1) do
begin
if not (aFields[J].Calculated or aFields[J].ServerCalculated) then
begin
with Add do
begin
DatasetField := aFields[J].Name;
TableField := aFields[J].Name;
SQLOrigin := TableField;
end;
end;
end;
end;
end;
aSchema.SaveToFile(aSchemaFileName);
aConnManager.SaveToFile(aConnFileName);
aConn := nil;
finally
FreeAndNil(aSchema);
FreeAndNil(aConnManager);
FreeAndNil(aDriverManager);
end;
end.
Question.
I need to make a little change on SQL sentence at runtime.
The idea is replace SELECT with some like this
SELECT FIRST :MAXRECORDS
And add MAXRECORDS to the PARAMs array.
All this ti the auto generated schema.
Is possible?
you can solve it more easily, like
procedure TDASampleService.DataAbstractServiceBeforeGetDatasetData(
aSender: TObject; const aDataset: IDADataset; const aIncludeSchema: Boolean;
const aMaxRecords: Integer);
begin
if aMaxRecords <> -1 then begin
(aDataset as IDAServerDataset).SQL := StringReplace((aDataset as IDAServerDataset).SQL,'SELECT', 'SELECT FIRST '+IntToStr(aMaxRecords));
end;
end;
MAXRECORDS value will be passed via table.MaxRecords
@evgenyK thanks, i will try.