Générer une base de données SQL Server à partir de XSD

Question

Duplicata: Générer un schéma SQL à partir de XML

---

Dans un projet sur lequel je travaille, j'ai besoin de prendre en charge soit un ensemble de données fortement typé pour stocker les données au format XML, soit de stocker les données dans SQL Server. J'ai déjà créé le schéma XSD et je voudrais pouvoir créer une base de données SQL Server en utilisant les tables et relations définies dans le XSD.

Est-ce possible? Et si oui, quelle est la meilleure façon d'aborder ce problème?

---

Clarification: Ce que je recherche, c'est un moyen de faire ce qui précède via du code au moment de l'exécution avec C# et SQL Server. Est-ce possible?

Answer 1

J'ai réussi à créer la classe suivante basée sur les objets de gestion de serveur SQL :

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using Rule=System.Data.Rule;

namespace XSD2SQL
{
public class XSD2SQL
{
    private readonly Server _server;
    private readonly SqlConnection _connexion;
    private Database _db;
    private DataSet _source;
    private string _nomBaseDeDonnees;

    public XSD2SQL(string chaineConnexion, DataSet source)
    {
        _connexion = new SqlConnection(chaineConnexion);
        _server = new Server(new ServerConnection(_connexion));
        _source = source;
    }

    public void CreerBaseDeDonnees(string nomBaseDeDonnees)
    {
        _nomBaseDeDonnees = nomBaseDeDonnees;
        _db = _server.Databases[nomBaseDeDonnees];
        if (_db != null) _db.Drop();
        _db = new Database(_server, _nomBaseDeDonnees);
        _db.Create();
    }

    public void PeuplerBaseDeDonnees()
    {
        CreerTables(_source.Tables);
        CreerRelations();
    }

    private void CreerRelations()
    {
        foreach (DataTable table in _source.Tables)
        {
            foreach (DataRelation rel in table.ChildRelations)
                CreerRelation(rel);
        }
    }

    private void CreerRelation(DataRelation relation)
    {
        Table tablePrincipale = _db.Tables[relation.ParentTable.TableName];
        Table tableEnfant = _db.Tables[relation.ChildTable.TableName];

        ForeignKey cleEtrangere = new ForeignKey(tableEnfant, relation.RelationName);
        cleEtrangere.ReferencedTable = tablePrincipale.Name;

        cleEtrangere.DeleteAction = SQLActionTypeToSMO(relation.ChildKeyConstraint.DeleteRule);
        cleEtrangere.UpdateAction = SQLActionTypeToSMO(relation.ChildKeyConstraint.UpdateRule);

        for (int i = 0; i < relation.ChildColumns.Length; i++)
        {
            DataColumn colonne = relation.ChildColumns[i];
            ForeignKeyColumn fkc = new ForeignKeyColumn(cleEtrangere, colonne.ColumnName, relation.ParentColumns[i].ColumnName);

            cleEtrangere.Columns.Add(fkc);
        }

        cleEtrangere.Create();

    }

    private void CreerTables(DataTableCollection tables)
    {
        foreach (DataTable table in tables)
        {                
            SupprimerTableExistante(table.TableName);
            Table nouvelleTable = new Table(_db, table.TableName);

            PeuplerTable(ref nouvelleTable, table);                
            DefinirClesPrimaires(ref nouvelleTable, table);
            nouvelleTable.Create();

        }
    }

    private void PeuplerTable(ref Table tableSortie, DataTable tableEntree)
    {
        foreach (DataColumn colonne in tableEntree.Columns)
        {
            CreerColonnes(ref tableSortie, colonne, tableEntree);
        }
    }

    private void CreerColonnes(ref Table tableSortie, DataColumn colonneEntree, DataTable tableEntree)
    {
        Column nouvelleColonne = new Column(tableSortie, colonneEntree.ColumnName);
        nouvelleColonne.DataType = CLRTypeToSQLType(colonneEntree.DataType);
        nouvelleColonne.Identity = colonneEntree.AutoIncrement;
        nouvelleColonne.IdentityIncrement = colonneEntree.AutoIncrementStep;
        nouvelleColonne.IdentitySeed = colonneEntree.AutoIncrementSeed;
        nouvelleColonne.Nullable = colonneEntree.AllowDBNull;
        nouvelleColonne.UserData = colonneEntree.DefaultValue;

        tableSortie.Columns.Add(nouvelleColonne);
    }

    private void DefinirClesPrimaires(ref Table tableSortie, DataTable tableEntree)
    {
        Index nouvelIndex = new Index(tableSortie, "PK_" + tableSortie.Name);
        nouvelIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
        nouvelIndex.IsClustered = false;

        foreach (DataColumn colonneCle in tableEntree.PrimaryKey)
        {                                
            nouvelIndex.IndexedColumns.Add(new IndexedColumn(nouvelIndex, colonneCle.ColumnName, true));                
        }
        if (nouvelIndex.IndexedColumns.Count > 0)
            tableSortie.Indexes.Add(nouvelIndex);
    }

    private DataType CLRTypeToSQLType(Type type)
    {
        switch (type.Name)
        {
            case "String":
                return DataType.NVarCharMax;

            case "Int32":
                return DataType.Int;

            case "Boolean":
                return DataType.Bit;

            case "DateTime":
                return DataType.DateTime;

            case "Byte[]":
                return DataType.VarBinaryMax;

        }

        return DataType.NVarCharMax;
    }

    private ForeignKeyAction SQLActionTypeToSMO(Rule rule)
    {
        string ruleStr = rule.ToString();

        return (ForeignKeyAction)Enum.Parse(typeof (ForeignKeyAction), ruleStr);
    }

    private void SupprimerTableExistante(string nomTable)
    {
        Table table = _db.Tables[nomTable];
        if (table != null) table.Drop();
    }

}
}

Il n'a pas encore été rigoureusement testé, et il doit y avoir plus de types SQL à CLR à mapper, mais il crée une nouvelle base de données, toutes les tables, colonnes, clés primaires et clés étrangères.

Pour que ce code fonctionne, quelques assemblies doivent être référencées :

Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SqlEnum

En espérant que cela aidera quelqu'un d'autre.

Answer 2

Je voudrais écrire un peu de XSLT pour convertir le XSD en instructions de création SQL.

Answer 3

Si vous travaillez sur SQL2005, vous avez la possibilité de créer une table avec des colonnes XML fortement typées, de sorte que chaque valeur soit validée par rapport à une Collection de Schémas XML (c'est-à-dire un XSD). Cependant, je ne peux rien vous dire sur les performances, la scalabilité, etc.

Si vous essayez de traduire un XSD en un ensemble de tables relationnelles, vous constaterez qu'il n'existe pas de mappage unique entre les éléments XSD et les tables SQL :

Un élément enfant XSD peut être implémenté en tant que table de détails, en tant qu'ensemble de colonnes représentant l'élément (si un seul enfant est autorisé), ou en tant que relation 1:1/1:n obligatoire/facultative.

Une collection d'éléments enfants XSD peut être une relation maître-détail, ou une relation n:m stockée dans une table séparée avec les attributs.

À ma connaissance, il n'y a pas de définition de contraintes primaires et uniques dans XSD, ce qui pose un autre problème dans la génération automatique du schéma.

Tout cela ne signifie pas que personne n'a encore pris la peine de développer un outil pour une telle tâche. Mais cela signifie certainement que la tâche ne peut pas être entièrement automatisée.