代码之家 › 专栏 › 技术社区 › Mike

LINQ到XML以生成DDL

ddl linq-to-xml linq xml c#

Mike · 技术社区 · 15 年前

在这个问题的最后,我们将数据库定义存储为XML格式。我遇到的问题是获取一个模式列表,这些模式中的表,这些表中的列(所有列都有相关的信息)。我当前的代码(包含在示例XML下面)获取所有内容,完全忽略嵌套,并且由于存在多个架构,因此会多次返回每个表/列。

示例XML:

<schemas>
    <schema>
        <name>schema_name1</name>
        <tables>
            <table>
                <name>table_name2</name>
                <comment>comment string2</comment>
                <type>innodb2</type>
                <columns>
                    <column>
                        <name>column_name3</name>
                        <type>data_type3</type>
                        <size>3</size>
                        <nullable>not null3</nullable>
                        <comment>comment string3</comment>
                    </column>
                    <column>
                        <name>column_name4</name>
                        <type>data_type4</type>
                        <size>4</size>
                        <nullable>not null4</nullable>
                        <comment>comment string4</comment>
                    </column>
                </columns>
            </table>
        </tables>
    </schema>
    <schema>
        <name>schema_name5</name>
        <tables>
            <table>
                <name>table_name6</name>
                <comment>comment string6</comment>
                <type>innodb6</type>
                <columns>
                    <column>
                        <name>column_name7</name>
                        <type>data_type7</type>
                        <size>7</size>
                        <nullable>not null7</nullable>
                        <comment>comment string7</comment>
                    </column>
                </columns>
            </table>
        </tables>
    </schema>
</schemas>

C代码:

    XDocument xml_input = XDocument.Load(FILE_IN);
    string column_create = "";
    //build a list of all schemas in xml
    var schemas = from s in xml_input.Descendants("schema")
                  select new
                  {
                      name = s.Element("name").Value
                  };
    //loop through all schemas
    foreach (var s in schemas)
    {
        //write the schema creation lines
        Console.WriteLine("DROP SCHEMA IF EXISTS " + s.name + ";");
        Console.WriteLine("CREATE SCHEMA " + s.name + ";");
        //build a list of all tables in schema
        var tables = from t in xml_input.Descendants("schema")
                                        .Descendants("table")
                     select new
                     {
                         name = t.Element("name").Value,
                         comment = t.Element("comment").Value,
                         type = t.Element("type").Value
                     };
        //loop through all tables in schema
        foreach (var t in tables)
        {
            //write the beginning of the table creation lines
            Console.WriteLine("CREATE TABLE " + s.name + "." + t.name + " (");
            //build a list of all columns in the schema
            var columns = from c in xml_input.Descendants("schema")
                                             .Descendants("table")
                                             .Descendants("column")
                          select new
                          {
                              name = c.Element("name").Value,
                              type = c.Element("type").Value,
                              size = c.Element("size").Value,
                              comment = c.Element("comment").Value
                          };
            //loop through all columns in table
            foreach (var c in columns)
            {
                //build the column creation line
                column_create = c.name + " " + c.type;
                if (c.size != null)
                {
                    column_create += "(" + c.size + ")";
                }
                if (c.comment != null)
                {
                    column_create += " COMMENT '" + c.comment + "'";
                }
                column_create += ", ";
                //write the column creation line
                Console.WriteLine(column_create);
            }
            //write the end of the table creation lines
            Console.WriteLine(")");
            if (t.comment != null)
            {
                Console.WriteLine("COMMENT '" + t.comment + "'");
            }
            if (t.type != null)
            {
                Console.WriteLine("TYPE = " + t.type);
            }
            Console.WriteLine(";");
        }
    }

关于如何保护筑巢结构有什么想法吗?我还无法处理可选的XML元素(例如表注释或大小字段,这些元素不适用于所有数据类型)。

谢谢!

1 回复 | 直到 15 年前

CoderDennis 15 年前

下面是如何保留嵌套结构。将Xelement添加到匿名类型以用作嵌套查询的源。

    XDocument xml_input = XDocument.Load(FILE_IN);
        string column_create = "";
        //build a list of all schemas in xml
        var schemas = from s in xml_input.Descendants("schema")
                      select new
                      {
                          schema = s,
                          name = s.Element("name").Value
                      };
        //loop through all schemas
        foreach (var s in schemas)
        {
            //write the schema creation lines
            Console.WriteLine("DROP SCHEMA IF EXISTS " + s.name + ";");
            Console.WriteLine("CREATE SCHEMA " + s.name + ";");
            //build a list of all tables in schema
            var tables = from t in s.schema.Descendants("table")
                         select new
                         {
                             table = t,
                             name = t.Element("name").Value,
                             comment = t.Element("comment").Value,
                             type = t.Element("type").Value
                         };
            //loop through all tables in schema
            foreach (var t in tables)
            {
                //write the beginning of the table creation lines
                Console.WriteLine("CREATE TABLE " + s.name + "." + t.name + " (");
                //build a list of all columns in the schema
                var columns = from c in t.table.Descendants("column")
                              select new
                              {
                                  name = c.Element("name").Value,
                                  type = c.Element("type").Value,
                                  size = c.Element("size").Value,
                                  comment = c.Element("comment").Value
                              };
                //loop through all columns in table
                foreach (var c in columns)
                {
                    //build the column creation line
                    column_create = c.name + " " + c.type;
                    if (c.size != null)
                    {
                        column_create += "(" + c.size + ")";
                    }
                    if (c.comment != null)
                    {
                        column_create += " COMMENT '" + c.comment + "'";
                    }
                    column_create += ", ";
                    //write the column creation line
                    Console.WriteLine(column_create);
                }
                //write the end of the table creation lines
                Console.WriteLine(")");
                if (t.comment != null)
                {
                    Console.WriteLine("COMMENT '" + t.comment + "'");
                }
                if (t.type != null)
                {
                    Console.WriteLine("TYPE = " + t.type);
                }
                Console.WriteLine(";");
            }
        }

处理可选元素的一种方法是,当没有值时,XML将包含空元素,如本列的注释中所示:

      <column>
        <name>column_name4</name>
        <type>data_type4</type>
        <size>4</size>
        <nullable>not null4</nullable>
        <comment/>
      </column>

这将在查询中返回空字符串,因此将代码更改为:

if (!string.IsNullOrEmpty(c.comment))
    {
        column_create += " COMMENT '" + c.comment + "'";
    }