Exploration de la structure d'une base de données Access

Messages postés
171
Date d'inscription
vendredi 1 septembre 2006
Statut
Membre
Dernière intervention
14 novembre 2019
-
Bonjour,

Attention : il s'agit ici d'une base Access 2002-2003 (*.mdb - Jet.OLEDB.4.0)
J'utilise en effet toujours ce format pour mes applications C# car le moteur Jet est présent par défaut dans Windows 7 et ultérieur. Il n'y a donc pas besoin d'installateur ni d'environnement spécifique pour faire tourner ces applications. On pourrait sans doute aussi utiliser Jet.OLEDB.12.0, mais je n'ai pas essayé.

Je développe actuellement une appli destinée à transformer une base Access (*.mdb) en base MySql et réciproquement. J'ai donc eu besoin de décortiquer la structure des bases Access pour pouvoir générer les requêtes de création de tables en MySql.

Après avoir farfouillé le net à la recherche de code répondant à mon besoin, et n'en trouvant que des bribes éparses, j'ai généré une classe contenant la plupart des informations de structure après moult recherches en run dans le volet "Variables Locales".

Cette classe est loin d'être complète et beaucoup de points restent obscurs. Les données ramenées par GetOleDbSchemaTable sont très mal documentées, ou je n'ai pas su trouver un document complet et exhaustif.

Toujours est-il que la classe actuelle contient les éléments suffisants pour recréer une base MySql : champs, indexes et relations. Les types de champs sont difficiles à déterminer : j'ai créé une table Access témoin comprenant un champ de chaque type connu par Access (17 types différents). Certains champs de types différents renvoient la même valeur pour Type dans la SchemaTable. Par exemple, les champsTexte et Memo renvoient tous les deux le type numérique 130. Après observation, j'ai pu les différencier grâce au Flag "Long". Mais il y a encore beaucoup de subtilités que je n'ai pas trouvées.

Notamment, il y a un flou entre la propriété Nullable d'un champ et ses flags IsNullable et MayBeNull. J'avoue ne pas avoir compris leur signification précise et leur interaction.

C'est pourquoi, si cela intéresse quelqu'un, il serait bon de continuer à gratter dans la SchemaTable pour trouver les subtilités qui manquent. Je commence à saturer.

Je mets le code de la classe en fin de ce post. Pour l'utiliser, il suffit d'exécuter la ligne ci-dessous et de coller le presse-papiers dans un tableur type Excel.
Cnx est une connection OleDbConnection ouverte.
Le "Replace" est facultatif, mais nécessaire pour coller dans le bloc-note.
Clipboard.SetText(MgdTools.Access.AccessSchema.GetSerializedSchema(Cnx).Replace("\n", "\r\n"));


Merci pour votre collaboration.

Voici le code de ma classe :
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;

namespace MgdTools.Access
{
    public static class AccessSchema
    {
        /// <summary>
        /// Correspondance entre les types de champs Access (numériques) et leur équivalent MySql
        /// </summary>
        /// <remarks>Table établie expérimentalement - Il peut manquer des valeurs</remarks>
        private static readonly Dictionary<int, string> FieldTypes = new Dictionary<int, string>()
        {
            {2,"smallint"},
            {3,"int"},
            {4,"float"},
            {5,"double"},
            {6,"decimal"},
            {7,"datetime"},
            {8,"varchar"},
            {11,"boolean"},
            {12,"blob"},
            {14,"decimal"},
            {16,"tinyint"},
            {17,"tinyint unsigned"},
            {18,"smallint unsigned"},
            {19,"int unsigned"},
            {20,"int"},
            {21,"bigint unsigned"},
            {128,"binary"},         // Si flag "Long", => LONGBINARY
            {129,"char"},
            {130,"varchar"},        // Si flag "Long", => LONGTEXT (Mémo)
            {131,"decimal"},
            {133,"date"},
            {134,"time"},
            {135,"timestamp"},
            {138,"blob"},
            {200,"varchar"},
            {201,"longtext"},
            {202,"varchar"},
            {203,"varchar"},
            {204,"varbinary"},
            {205,"longblob"}
        };


        //-------------------------------------------------------------------------------------------------------------
        /// <summary>Classe contenant tous les paramètres d'une table</summary>
        public class TableInfo
        {
            /// <summary> Nom de la table</summary>
            public string TableName { get; set; }
            /// <summary>Liste des champs</summary>
            public List<Field> Fields { get; set; } = new List<Field>();
            /// <summary>Liste des relations</summary>
            public List<FKey> FKeys { get; set; } = new List<FKey>();
            /// <summary>Liste des indexes</summary>
            public List<Index> Indexes { get; set; } = new List<Index>();
            /// <summary>Nom du champ clé primaire</summary>
            public string PrimaryKey { get; set; }
        }

        //-------------------------------------------------------------------------------------------------------------
        /// <summary>Classe contenant les paramètres d'un champ</summary>
        /// <remarks>Il est impératif de remplir la valeur de Type et de Flags avant d'appeler TypeString</remarks>
        public class Field : IComparable
        {
            /// <summary>Nom du champ</summary>
            public string Name { get; set; }
            /// <summary>Drapeaux</summary>
            public int Flags { get; set; }
            /// <summary>Type du champ</summary>
            public int Type { get; set; }
            /// <summary>Champ pouvant être null</summary>
            public bool Nullable { get; set; }
            /// <summary>Nombre max de caractères (champs alphanumériques)</summary>
            public int MaxChars { get; set; }
            /// <summary>Nombre de digits (champs numériques)</summary>
            public int Digits { get; set; }
            /// <summary>Nombre de décimales (champs numériques)</summary>
            public int Decimals { get; set; }
            /// <summary>Champ de grande taille (mémo, OLE, etc.)</summary>
            public bool IsLong { get { return (Flags & 0x80) != 0; } }
            /// <summary>>Champ pouvant avoir la propriété nullable (?)</summary>
            public bool IsNullable { get { return (Flags & 0x20) != 0; } }
            /// <summary>>Champ pouvant avoir une valeur null</summary>
            public bool MayBeNull { get { return (Flags & 0x40) != 0; } }
            /// <summary>Champ à longueur fixe</summary>
            public bool FixedLength { get { return (Flags & 0x10) != 0; } }
            /// <summary>Position ordinale du champ</summary>
            public int Position { get; set; }
            /// <summary>Type de champ MySql correspondant</summary>
            public string SqlTypeString
            {
                get
                {
                    try
                    {
                        if (Type == 130 && IsLong) return FieldTypes[201];          // varchar => longtext
                        else if (Type == 128 && IsLong) return FieldTypes[205];     // binary => longblob
                        else return FieldTypes[Type];
                    }
                    catch { return "unknown"; }
                }
            }

            /// <summary>Routine de comparaison pour le tri par position ordinale</summary>
            public int CompareTo(object obj)
            {
                Field Fd = obj as Field;
                if (Fd == null)
                    return 0;
                if (Fd.Position > this.Position)
                    return -1;
                else if (Fd.Position < this.Position)
                    return 1;
                else
                    return 0;
            }
        }

        //-------------------------------------------------------------------------------------------------------------
        /// <summary>Classe contenant les paramètres d'un index</summary>
        /// <remarks>Les indexes à champs multiples sont définis par un même nom, et des positions incrémentées</remarks>
        public class Index : IComparable
        {
            /// <summary>Nom de l'index</summary>
            public string Name { get; set; }
            /// <summary>Type d'index (toujours 1 ?)</summary>
            public string Type { get; set; }
            /// <summary>Champ objet de l'index</summary>
            public string Field { get; set; }
            /// <summary>Index primaire</summary>
            public bool Primary { get; set; }
            /// <summary>Index unique</summary>
            public bool Unique { get; set; }
            /// <summary>Index avec mise à jour automatique</summary>
            public bool AutoUpdate { get; set; }
            /// <summary>Position ordinale de l'index</summary>
            public long Position { get; set; }

            /// <summary>Routine de comparaison pour le tri</summary>
            /// <remarks>Tri par nom, puis par index pour regrouper les indexes multi-champs</remarks>
            public int CompareTo(object obj)
            {
                Index Ix = obj as Index;
                if (Ix == null)
                    return 0;
                if (Ix.Name == this.Name)
                {
                    if (Ix.Position > this.Position)
                        return -1;
                    else if (Ix.Position < this.Position)
                        return 1;
                    else
                        return 0;
                }
                else
                    return string.Compare(this.Name, Ix.Name);
            }
        }

        //-------------------------------------------------------------------------------------------------------------
        /// <summary> Classe contenant les paramètres des relations</summary>
        public class FKey : IComparable
        {
            /// <summary>Nom du champ local</summary>
            public string Name { get; set; }
            /// <summary>Table étrangère</summary>
            public string Table { get; set; }
            /// <summary>Nom du champ étranger</summary>
            public string Column { get; set; }
            /// <summary>Postion ordinale de la clé</summary>
            public long Position { get; set; }

            /// <summary>Routine de comparaison pour le tri</summary>
            public int CompareTo(object obj)
            {
                FKey Key = obj as FKey;
                if (Key == null)
                    return 0;
                if (Key.Position > this.Position)
                    return -1;
                else if (Key.Position < this.Position)
                    return 1;
                else
                    return 0;
            }
        }

        //=============================================================================================================

        //-------------------------------------------------------------------------------------------------------------
        public static Dictionary<string, TableInfo> getSchema(OleDbConnection Cnx)
        {
            Dictionary<string, TableInfo> BaseStruct = new Dictionary<string, TableInfo>();
            DataTable dtTables = Cnx.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            foreach (DataRow row in dtTables.Rows)
            {
                // Recherche des tables
                if (row["TABLE_TYPE"] as string == "TABLE")
                {
                    TableInfo Table = new TableInfo { TableName = row["TABLE_NAME"] as string };
                    if (Table.TableName[0] == '~')      // Table temporaire
                        continue;

                    BaseStruct[Table.TableName] = Table;

                    // Recherche des champs de la table
                    DataTable dtFields = Cnx.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, Table.TableName });
                    foreach (DataRow DC in dtFields.Rows)
                    {
                        Field Champ = new Field();
                        Champ.Name = DC["COLUMN_NAME"].ToString();
                        Champ.Position = GetIntValue(DC["ORDINAL_POSITION"].ToString());
                        Champ.Flags = GetIntValue(DC["COLUMN_FLAGS"].ToString());
                        Champ.Type = GetIntValue(DC["DATA_TYPE"].ToString());
                        Champ.MaxChars = GetIntValue(DC["CHARACTER_MAXIMUM_LENGTH"].ToString());
                        Champ.Digits = GetIntValue(DC["NUMERIC_PRECISION"].ToString());
                        Champ.Decimals = GetIntValue(DC["NUMERIC_SCALE"].ToString());
                        BaseStruct[Table.TableName].Fields.Add(Champ);
                    }
                }
            }

            // Recherche des clés primaires
            dtTables = Cnx.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null);
            foreach (DataRow row in dtTables.Rows)
            {
                string table_name = row["TABLE_NAME"] as string;
                if (BaseStruct.ContainsKey(table_name))
                    BaseStruct[table_name].PrimaryKey = row["COLUMN_NAME"] as string;
            }

            // Recherche des indexes
            dtTables = Cnx.GetOleDbSchemaTable(OleDbSchemaGuid.Indexes, null);
            foreach (DataRow row in dtTables.Rows)
            {
                Index Ix = new Index();
                string table_name = row["TABLE_NAME"] as string;
                Ix.Name = row["INDEX_NAME"].ToString();
                Ix.Field = row["COLUMN_NAME"].ToString();
                Ix.Position = (long)row["ORDINAL_POSITION"];
                Ix.Primary = (bool)row["PRIMARY_KEY"];
                Ix.Unique = (bool)row["UNIQUE"];
                Ix.AutoUpdate = (bool)row["AUTO_UPDATE"];
                Ix.Type = row["TYPE"].ToString();
                KeyValuePair<string, TableInfo> Info = BaseStruct.FirstOrDefault(x => x.Key == table_name);
                if (!Info.Equals(default(KeyValuePair<string, TableInfo>)))
                    Info.Value.Indexes.Add(Ix);
            }

            // Recherche des clés étrangères
            dtTables = Cnx.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, null);
            foreach (DataRow row in dtTables.Rows)
            {
                string fk_table_name = row["FK_TABLE_NAME"] as string;
                if (BaseStruct.ContainsKey(fk_table_name))
                {
                    FKey key = new FKey();
                    key.Name = row["FK_COLUMN_NAME"].ToString();
                    key.Table = row["PK_TABLE_NAME"].ToString();
                    key.Column = row["PK_COLUMN_NAME"].ToString();
                    key.Position = (long)row["ORDINAL"];
                    BaseStruct[fk_table_name].FKeys.Add(key);
                }
            }

            // Tri des listes
            foreach (KeyValuePair<string, TableInfo> Info in BaseStruct)
            {
                TableInfo Table = Info.Value;
                Table.Fields.Sort();
                Table.Indexes.Sort();
                Table.FKeys.Sort();
            }
            return BaseStruct;
        }

        //-------------------------------------------------------------------------------------------------------------
        /// <summary>
        /// Retourne la structure de la base sous forme de texte tablulé à coller dans un tableur pour visualisation
        /// </summary>
        /// <param name="Cnx">OleDbConnection ouverte</param>
        /// <returns>Chaine de caractères tabulée</returns>
        public static string GetSerializedSchema(OleDbConnection Cnx)
        {
            string Resu = "";

            Dictionary<string, TableInfo> Struct = getSchema(Cnx);
            foreach (KeyValuePair<string, TableInfo> Info in Struct.OrderBy(key => key.Key))    // Tri alphabétique des tables
            {
                TableInfo Table = Info.Value;
                Resu += Table.TableName + "\n";

                // Champs
                string FmtFields = "";
                for (int i = 0; i < 14; i++)
                    FmtFields += "{" + i.ToString() + "}\t";
                FmtFields = FmtFields.Substring(0, FmtFields.Length - 1) + "\n";
                Resu += string.Format(FmtFields, "Champ", "Nom", "Primary", "Type Num", "Type MySql", "Flags", "Nullable",
                                                 "Chars", "Digits", "Dec.", "Long", "Null", "Nullable", "Long.Fixe");
                foreach (Field FD in Table.Fields)
                {
                    Resu += string.Format(FmtFields,
                        FD.Position,
                        FD.Name,
                        FD.Name == Table.PrimaryKey,
                        FD.Type,
                        FD.SqlTypeString,
                        FD.Flags,
                        FD.Nullable,
                        FD.MaxChars,
                        FD.Digits,
                        FD.Decimals,
                        FD.IsLong,
                        FD.IsNullable,
                        FD.MayBeNull,
                        FD.FixedLength
                        );
                }

                // Indexes
                if (Table.Indexes.Count > 0)
                {
                    string FmtIndex = "{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}\n";
                    Resu += string.Format(FmtIndex, "Indexes", "Nom", "Champ", "Type", "Primary", "Unique", "AutoUpd");
                    foreach (Index Ix in Table.Indexes)
                    {
                        Resu += string.Format(FmtIndex,
                            Ix.Position, Ix.Name, Ix.Field, Ix.Type, Ix.Primary, Ix.Unique, Ix.AutoUpdate);
                    }
                }


                // Relations
                if (Table.FKeys.Count > 0)
                {
                    string FmtKeys = "{0}\t{1}\t{2}\t{3}\n";
                    Resu += string.Format(FmtKeys, "Relations", "Champ local", "Table étrangère", "Champ étranger");
                    foreach (FKey Key in Table.FKeys)
                        Resu += string.Format(FmtKeys, Key.Position, Key.Name, Key.Table, Key.Column);
                }

                Resu += "\n";
            }

            return Resu;
        }

        //-------------------------------------------------------------------------------------------------------------
        private static int GetIntValue(string StrValue)
        {
            int Result = 0;
            int.TryParse(StrValue, out Result);
            return Result;
        }
    }
}
Afficher la suite