Une façon sensée de transmettre des données Web en XML à une base de données SQL Server

Question

Après avoir exploré plusieurs façons différentes de transmettre des données Web à une base de données à des fins de mise à jour, je me demande si XML pourrait être une bonne stratégie. La base de données est actuellement SQL 2000. Dans quelques mois, elle passera à SQL 2005 et je pourrai modifier les choses si nécessaire, mais j'ai besoin d'une solution SQL 2000 maintenant.

Tout d'abord, la base de données en question utilise le format Modèle VAE . Je sais que ce type de base de données est généralement très mal vu, alors pour les besoins de cette question, veuillez accepter que cela ne changera pas.

La méthode de mise à jour actuelle fait que le serveur web insère des valeurs (qui ont toutes été converties d'abord en leurs types sous-jacents corrects, puis en sql_variant) dans une table temporaire. Une procédure stockée est ensuite exécutée, qui s'attend à ce que la table temporaire existe et qui se charge de mettre à jour, d'insérer ou de supprimer des éléments selon les besoins.

Jusqu'à présent, seul un seul élément a dû être mis à jour à la fois. Mais maintenant, il est nécessaire de pouvoir modifier plusieurs éléments à la fois et de prendre en charge les éléments hiérarchiques, chacun d'entre eux pouvant avoir sa propre liste d'attributs. Voici un exemple de XML que j'ai tapé à la main pour démontrer ce à quoi je pense.

Notez que dans cette base de données, l'entité est Element et qu'un ID de 0 signifie "create", c'est-à-dire l'insertion d'un nouvel élément.

<Elements>
  <Element ID="1234">
    <Attr ID="221">Value</Attr>
    <Attr ID="225">287</Attr>
    <Attr ID="234">
      <Element ID="99825">
        <Attr ID="7">Value1</Attr>
        <Attr ID="8">Value2</Attr>
        <Attr ID="9" Action="delete" />
      </Element>
      <Element ID="99826" Action="delete" />
      <Element ID="0" Type="24">
        <Attr ID="7">Value4</Attr>
        <Attr ID="8">Value5</Attr>
        <Attr ID="9">Value6</Attr>
      </Element>
      <Element ID="0" Type="24">
        <Attr ID="7">Value7</Attr>
        <Attr ID="8">Value8</Attr>
        <Attr ID="9">Value9</Attr>
      </Element>
    </Attr>
    <Rel ID="3827" Action="delete" />
    <Rel ID="2284" Role="parent">
      <Element ID="3827" />
      <Element ID="3829" />
      <Attr ID="665">1</Attr>
    </Rel>
    <Rel ID="0" Type="23" Role="child">
      <Element ID="3830" />
      <Attr ID="67"
    </Rel>
  </Element>
  <Element ID="0" Type="87">
    <Attr ID="221">Value</Attr>
    <Attr ID="225">569</Attr>
    <Attr ID="234">
      <Element ID="0" Type="24">
        <Attr ID="7">Value10</Attr>
        <Attr ID="8">Value11</Attr>
        <Attr ID="9">Value12</Attr>
      </Element>
    </Attr>
  </Element>
  <Element ID="1235" Action="delete" />
</Elements>

Certains attributs sont des types de valeurs simples, comme l'AttrID 221. Mais l'AttrID 234 est un type spécial "multi-valeur" qui peut avoir une liste d'éléments sous lui, et chacun d'eux peut avoir une ou plusieurs valeurs. Les types n'ont besoin d'être présentés que lorsqu'un nouvel élément est créé, puisque l'ElementID implique pleinement le type s'il existe déjà. Je vais probablement supporter de ne passer que les éléments modifiés (tels que détectés par javascript). Et il pourrait y avoir une Action="Delete" sur les éléments Attr aussi, puisque les NULL sont traités comme "non sélectionnés" - parfois il est très important de savoir si une question Oui/Non a été intentionnellement répondue Non ou si personne n'a encore pris la peine de dire Oui.

Il existe également un autre type de données, les relations. Pour l'instant, elles sont mises à jour par des appels AJAX individuels au fur et à mesure que les choses sont modifiées dans l'interface utilisateur, mais j'aimerais les inclure afin que les modifications apportées aux relations puissent être annulées (pour l'instant, une fois que vous les modifiez, c'est terminé). Ce sont donc aussi des éléments, mais ils sont appelés Rel au lieu d'Element. Les relations sont implémentées comme ElementID1 et ElementID2, donc le RelID 2284 dans le XML ci-dessus est dans la base de données comme :

ElementID 2284 ElementID1 1234 ElementID2 3827

Le fait d'avoir plusieurs enfants dans une même relation n'est pas pris en charge actuellement, mais ce serait bien plus tard.

Cette stratégie et l'exemple XML ont-ils un sens ? Existe-t-il une méthode plus judicieuse ? Je cherche simplement une critique générale pour m'éviter de m'engager dans une mauvaise voie. Tout ce que vous voudrez bien commenter sera utile.

Il se trouve que le langage web est ASP classique, mais cela pourrait changer en ASP.Net à un moment donné. Un moteur de persistance comme Linq ou nHibernate n'est probablement pas acceptable pour le moment - je veux juste améliorer cette application qui fonctionne déjà sans avoir à y consacrer beaucoup de temps de développement.

Je choisirai la réponse qui fait preuve d'expérience et qui présente un équilibre entre les bons avertissements sur ce qu'il ne faut pas faire, les confirmations de ce que j'ai l'intention de faire et les recommandations sur ce qu'il faut faire. Je serai aussi objectif que possible.

P.S. J'aimerais pouvoir gérer les caractères unicodes ainsi que les chaînes de caractères très longues (10k +).

UPDATE

Cela fonctionne depuis un certain temps et j'ai utilisé l'astuce ADO Recordset Save-To-Stream pour faciliter la création du XML. Le résultat semble être assez rapide, mais si la vitesse devient un problème, je pourrais revoir cette méthode.

Entre-temps, mon code permet de gérer un nombre illimité d'éléments et d'attributs sur la page en même temps, y compris la mise à jour, la suppression et la création de nouveaux éléments en une seule fois.

J'ai opté pour un schéma comme celui-ci pour tous mes éléments :

• Éléments de données existants

  Exemple : nom d'entrée e12345_a678 (élément 12345, attribut 678), la valeur d'entrée est la valeur de l'attribut.

• Nouveaux éléments

  Javascript copie un modèle caché de l'ensemble des éléments HTML nécessaires pour le type à l'emplacement correct sur la page, incrémente un compteur pour obtenir un nouvel ID pour cet élément, et ajoute le numéro aux noms des éléments du formulaire.

  var newid = 0;
  
  function metadataAdd(reference, nameid, value) {
     var t = document.createElement('input');
     t.setAttribute('name', nameid);
     t.setAttribute('id', nameid);
     t.setAttribute('type', 'hidden');
     t.setAttribute('value', value);
     reference.appendChild(t);
  }
  
  function multiAdd(target, parentelementid, attrid, elementtypeid) {
     var proto = document.getElementById('a' + attrid + '_proto');
     var instance = document.createElement('p');
     target.parentNode.parentNode.insertBefore(instance, target.parentNode);
     var thisid = ++newid;
     instance.innerHTML = proto.innerHTML.replace(/{prefix}/g, 'n' + thisid + '_');
     instance.id = 'n' + thisid;  
     instance.className += ' new';
     metadataAdd(instance, 'n' + thisid + '_p', parentelementid);
     metadataAdd(instance, 'n' + thisid + '_c', attrid);
     metadataAdd(instance, 'n' + thisid + '_t', elementtypeid);
     return false;
  }

  Exemple : Le nom d'entrée du modèle _a678 devient n1_a678 (un nouvel élément, le premier de la page, attribut 678). Tous les attributs de ce nouvel élément sont balisés avec le même préfixe n1. Le nouvel élément suivant sera n2, et ainsi de suite. Des entrées de formulaire cachées sont créées :

  n1_t, value est le type d'élément de l'élément à créer. n1_p, la valeur est l'identifiant parent de l'élément (s'il s'agit d'une relation) n1_c, valeur est l'identifiant de l'enfant de l'élément (s'il s'agit d'une relation)

• Suppression d'éléments

  Une entrée cachée est créée dans le formulaire e12345_t avec la valeur 0. Les contrôles existants affichant les valeurs de cet attribut sont désactivés et ne sont donc pas inclus dans l'affichage du formulaire. Ainsi, "set type to 0" est traité comme une suppression.

Avec ce schéma, chaque élément de la page a un nom unique et peut être distingué correctement, et chaque action peut être représentée correctement.

Lorsque le formulaire est affiché, voici un exemple de construction d'un des deux recordsets utilisés (code ASP classique) :

Set Data = Server.CreateObject("ADODB.Recordset")
Data.Fields.Append "ElementID", adInteger, 4, adFldKeyColumn
Data.Fields.Append "AttrID", adInteger, 4, adFldKeyColumn
Data.Fields.Append "Value", adLongVarWChar, 2147483647, adFldIsNullable Or adFldMayBeNull
Data.CursorLocation = adUseClient
Data.CursorType = adOpenDynamic
Data.Open

C'est le jeu d'enregistrements pour les valeurs, l'autre est pour les éléments eux-mêmes.

Je parcours le formulaire affiché et, pour le jeu d'enregistrements des éléments, j'utilise un Scripting.Dictionary peuplé d'instances d'une classe personnalisée qui possède les propriétés dont j'ai besoin, de sorte que je puisse ajouter les valeurs au coup par coup, puisqu'elles ne viennent pas toujours dans l'ordre. Les nouveaux éléments sont ajoutés en négatif pour les distinguer des éléments réguliers (plutôt que de nécessiter une colonne distincte pour indiquer s'il s'agit d'un nouvel élément ou s'il s'adresse à un élément existant). J'utilise une expression régulière pour décortiquer les clés du formulaire : "^(e|n)([0-9]{1,10})_(a|p|t|c)([0-9]{0,10})$"

Ensuite, l'ajout d'un attribut ressemble à ceci.

Data.AddNew
ElementID.Value = DataID
AttrID.Value = Integerize(Matches(0).SubMatches(3))
AttrValue.Value = Request.Form(Key)
Data.Update

ElementID, AttrID et AttrValue sont des références aux champs du jeu d'enregistrements. Cette méthode est nettement plus rapide que l'utilisation de Data.Fields("ElementID").Value à chaque fois.

Je parcours en boucle le dictionnaire des mises à jour d'éléments et j'ignore celles qui ne contiennent pas toutes les informations appropriées, en ajoutant les bonnes à l'ensemble des enregistrements.

Ensuite, j'appelle ma procédure stockée de mise à jour des données comme ceci :

Set Cmd = Server.CreateObject("ADODB.Command")
With Cmd
   Set .ActiveConnection = MyDBConn
   .CommandType = adCmdStoredProc
   .CommandText = "DataPost"
   .Prepared = False
   .Parameters.Append .CreateParameter("@ElementMetadata", adLongVarWChar, adParamInput, 2147483647, XMLFromRecordset(Element))
   .Parameters.Append .CreateParameter("@ElementData", adLongVarWChar, adParamInput, 2147483647, XMLFromRecordset(Data))
End With
Result.Open Cmd ' previously created recordset object with options set

Voici la fonction qui effectue la conversion xml :

Private Function XMLFromRecordset(Recordset)
   Dim Stream
   Set Stream = Server.CreateObject("ADODB.Stream")
   Stream.Open
   Recordset.Save Stream, adPersistXML
   Stream.Position = 0
   XMLFromRecordset = Stream.ReadText
End Function

Au cas où la page Web aurait besoin de le savoir, le SP renvoie un jeu d'enregistrements de tous les nouveaux éléments, en indiquant leur valeur de page et leur valeur créée (je peux donc voir que n1 est maintenant e12346 par exemple).

Voici quelques extraits clés de la procédure stockée. Notez qu'il s'agit de SQL 2000 pour l'instant, mais que je pourrai bientôt passer à 2005 :

CREATE PROCEDURE [dbo].[DataPost]
   @ElementMetaData ntext,
   @ElementData ntext
AS
DECLARE @hdoc int

--- snip ---

EXEC sp_xml_preparedocument @hdoc OUTPUT, @ElementMetaData, '<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" />'
INSERT #ElementMetadata (ElementID, ElementTypeID, ElementID1, ElementID2)
SELECT *
FROM
   OPENXML(@hdoc, '/xml/rs:data/rs:insert/z:row', 0)
   WITH (
      ElementID int,
      ElementTypeID int,
      ElementID1 int,
      ElementID2 int
   )
ORDER BY ElementID -- orders negative items (new elements) first so they begin counting at 1 for later ID calculation

EXEC sp_xml_removedocument @hdoc

--- snip ---

UPDATE E
SET E.ElementTypeID = M.ElementTypeID
FROM
   Element E
   INNER JOIN #ElementMetadata M ON E.ElementID = M.ElementID
WHERE
   E.ElementID >= 1
   AND M.ElementTypeID >= 1

La requête suivante effectue la corrélation entre les identifiants négatifs des nouveaux éléments et ceux des éléments nouvellement insérés :

UPDATE #ElementMetadata -- Correlate the new ElementIDs with the input rows
SET NewElementID = Scope_Identity() - @@RowCount + DataID
WHERE ElementID < 0

D'autres requêtes basées sur des ensembles effectuent toutes les autres tâches consistant à valider que les attributs sont autorisés, qu'ils sont du bon type de données, et à insérer, mettre à jour et supprimer des éléments et des attributs.

J'espère que cette brève présentation sera utile à d'autres personnes un jour ! La conversion d'ADO Recordsets en un flux XML a été un grand succès pour moi, car elle m'a permis de gagner beaucoup de temps et d'avoir un espace de noms et un schéma déjà définis qui ont permis aux résultats de sortir correctement.

Il était également beaucoup plus facile d'utiliser un format XML plus plat avec deux entrées que de s'en tenir à un idéal consistant à tout avoir dans un seul flux XML.

Answer 1

Si je comprends bien, vous vous intéressez aux avantages et aux inconvénients de l'utilisation de XML en tant que format de données entre la base de données et l'application (dans ce cas, une application web).

Si vous disposez de l'ensemble des données à insérer/mettre à jour/supprimer sous la forme d'un sac de données pratique dans votre client, il est logique de les envoyer en XML. La raison en est simple : cela permettrait un seul aller-retour de la base de données vers le serveur, et réduire les allers-retours est toujours une bonne idée. bon penser. Mais l'avantage le plus important est que vous pouvez utiliser le saint-graal des performances des bases de données : traitement orienté vers les ensembles . En utilisant les méthodes XML, spécialement nœuds y valeur Combiné à quelques compétences modérées en XPath-fu, vous pouvez déchiqueter l'ensemble des paramètres XML reçus de l'application en ensembles relationnels, et utiliser des opérations orientées ensemble pour effectuer les écritures dans la base de données.

Prenons par exemple le XML de votre message, disons qu'il a été transmis comme un paramètre nommé @x de type XML. Vous pouvez le déchiqueter en attributs pour le fusionner avec des éléments existants :

select x.value(N'@ID', N'int') as ID,
  x.value(N'.', N'varchar(max)') as [Value]
from  @x.nodes('//Element[not(@Action="delete") and not (@ID=0)]/Attr') t(x)

Vous pouvez détruire les attributs qui entrent dans les nouveaux éléments :

select x.value(N'@ID', N'int') as ID,
  x.value(N'.', N'varchar(max)') as [Value]
from  @x.nodes('//Element[@ID=0]/Attr') t(x);

Et vous pouvez détruire les éléments à supprimer :

select x.value(N'@ID', N'int') as ID
from  @x.nodes('//Element[@Action="delete"]') t(x);

Ces fixe peuvent être manipulés par le biais de DML SQL normaux : insérés, supprimés, mis à jour ou fusionné dans les tables EAV, en un seul passage. Notez que le déchiquetage XML que je montre ici est trivial et probablement incorrect pour vous, mais c'est juste pour montrer la façon de le faire.

Maintenant, si c'est le meilleur chemin à parcourir, je ne sais pas. Il y a beaucoup trop de variables et de pièces mobiles, et elles se trouvent principalement dans les compétences de votre équipe de développement et dans la base de code existante. Il est certain que le XML est un bon format à appeler dans la base de données pour mettre à jour des ensembles, mais le XML a aussi ses défauts : il est verbeux et lourd, plus lent à analyser que les formats binaires, et il est en fait tout à fait difficile à comprendre par les programmeurs : une fois que vous avez dépassé la couche de sucre de '<' et '>', il y a un problème de sécurité. profond (et parfois désordonnée) de XPath, XQuery, espaces de noms, encodages, cdata et le reste.

Je dirais allez-y, faites un prototype, faites-nous savoir comment ça se passe...

Answer 2

Je ne vois aucune raison de ne pas utiliser les colonnes XML dans SQL Server 2005, et de faire tout votre travail via des procédures stockées.

Vous n'avez probablement pas le temps d'abstraire votre accès aux données pour cacher la laideur du modèle de données, alors pourquoi ne pas y accéder tel quel, en utilisant XML ? Vous pouvez utiliser XQuery dans SQL Server pour effectuer des mises à jour, des requêtes, etc.

Maintenant que j'y pense, vous pourriez toujours mettre une couche d'abstraction entre les pages ASP et la base de données. Cela vous permettrait à l'avenir d'utiliser XSLT pour transformer la structure de votre XML en un format plus performant dans la base de données.