Data model - export column attributes

[skiwi]

A question for which I am seeking assistance.

How would I go about extracting and exporting to a spreadsheet (or CSV) a list of all tables, their columns, and attribute details (eg name, type, length, PK, FK, not null, unique, ...) from a package (and all sub packages)?



tia

simon

[Paolo F Cantoni]

I'd go direct in via the DB.  Use the MS Access functionality to do it.

If you're using a Server based repository - just link the tables...

HTH,
Paolo

[skiwi]

Hmmm, thanks for the quick reply.
I guess I'll have to find my way around the data model.
I've just had a "quick look"™ and it appears that there are no relationships (referential integrity) defined, or is that just my import not picking them up?

[Paolo F Cantoni]

Hmmm, thanks for the quick reply.
I guess I'll have to find my way around the data model.
I've just had a "quick look"™ and it appears that there are no relationships (referential integrity) defined, or is that just my import not picking them up?

No, this is common for these types of applications...

The RI is handled by the code.  However it is fairly easy to navigate and get your head around the structures.  If you have any questions, just ask.  We've got a lot of experience on how to link the data.  The problem comes with those aggregate columns that contain multiple properties.

HTH,
Paolo

[skiwi]

I'd really appreciate a quick start for the tables to join to extract
Table, Column,
I see the t_attribute table
and the in t_object some classes are the tables I want, I'm guessing to use Stereotype to select them.

I'm hasppy to post my script afterwards

thanks

Simon

[mrf]

Hey skiwi,

If you've got version EA version 7.5 have a look at the script "Recursive Model Dump Example" (in the "Local Scripts" group) which will show you how to recursively process packages and output a list of their elements.

While it doesn't do entirely what you want, it's a good start and you could extend the function DumpElements to obtain a collection of attributes for the element in question and then list those in your own CSV format.

[Paolo F Cantoni]

I'd really appreciate a quick start for the tables to join to extract
Table, Column,
I see the t_attribute table
and the in t_object some classes are the tables I want, I'm guessing to use Stereotype to select them.

I'm hasppy to post my script afterwards

thanks

Simon

t_object is the table that contains the Table info (Object_ID) - and yes, you'll need to filter by stereotype.  It joints to t_attribute on t_attribute.Object_ID for the columns.  Also you'll need to join to t_operation on t_operation .Object_ID for the keys etc

HTH,
Paolo

[Geert Bellekens]

Simon,

What I've done to export the model details to an excel file is:

• Use Repository.SQLQuery to get the details into an xml file

Code: [Select]

      /// <summary>
        /// returns an xml document with a flat list of the owned elements modified after the given date
        /// </summary>
        /// <param name="modifiedAfter">the date after wich the elements should be modified</param>
        /// <returns>the owned elements in xml format</returns>
        public XmlDocument exportOwnedElements(DateTime modifiedAfter)
        {
            string sqlDateModified = modifiedAfter.ToString("yyyy-MM-dd");
            string sqlQuery =
                " SELECT o.Object_ID as ID,o.Object_Type as Type,isNull(o.Name,'') as Name,ISNULL(t.value,0) as ImportanceLevel,isnull(o.Author,'') as Author,o.CreatedDate as Created,o.ModifiedDate as Modified,isnull(o.Note,'') as Documentation,isnull(o.Package_ID,'') as ParentID,isnull(p.name,'') as ParentName  " +
                " ,isnull(p9.Name +'.','') + isnull(p8.Name+'.','')+ isnull(p7.Name+'.','')+ isnull(p6.Name+'.','') " +
                " + isnull(p5.Name+'.','')+ isnull(p4.Name+'.','')+ isnull(p3.Name+'.','')+ isnull(p2.Name+'.','')+ isnull(p.Name,'') as QualifiedName " +
                " FROM t_object as o  " +
                " left join t_objectproperties t on t.Object_ID = o.Object_ID " +
                " left join t_package p on o.Package_ID = p.Package_ID " +
                " left join t_package p2 on p.Parent_ID = p2.Package_ID " +
                " left join t_package p3 on p2.Parent_ID = p3.Package_ID " +
                " left join t_package p4 on p3.Parent_ID = p4.Package_ID " +
                " left join t_package p5 on p4.Parent_ID = p5.Package_ID " +
                " left join t_package p6 on p5.Parent_ID = p6.Package_ID " +
                " left join t_package p7 on p6.Parent_ID = p7.Package_ID " +
                " left join t_package p8 on p7.Parent_ID = p8.Package_ID " +
                " left join t_package p9 on p8.Parent_ID = p9.Package_ID " +

                " where (t.Property = 'ImportanceLevel' or t.Property is null)" +
                " and o.ModifiedDate > '" + sqlDateModified + "' " +
                " and (p.package_ID = " + this.getPackageID() + "  " +
                    " or p2.package_ID = " + this.getPackageID() + "  " +
                    " or p3.package_ID = " + this.getPackageID() + "  " +
                    " or p4.package_ID = " + this.getPackageID() + "  " +
                    " or p5.package_ID = " + this.getPackageID() + "  " +
                    " or p6.package_ID = " + this.getPackageID() + "  " +
                    " or p7.package_ID = " + this.getPackageID() + "  " +
                    " or p8.package_ID = " + this.getPackageID() + "  " +
                    " or p9.package_ID = " + this.getPackageID() + " ) " +
                " order by QualifiedName";
            return ((EAModel)this.model).SQLQuery(sqlQuery);


• Read the xml file into an 2 dimensional array (see reply 2)
• Dump the array into Excel using the Value2 (see reply 3)

Geert

[Geert Bellekens]

Simon,

What I've done to export the model details to an excel file is:

• Use Repository.SQLQuery to get the details into an xml file (see reply 1)
• Read the xml file into an 2 dimensional array

Code: [Select]

       /// <summary>
        /// converts the given xml document to a two dimensional array of strings
        /// </summary>
        /// <param name="xmlElements">the xml document containing the information about the elements</param>
        /// <returns>the converted two dimensional array of strings</returns>
        protected object[,] MarshalXmlDocumentToArray(XmlDocument xmlElements)
        {


            object[,] marshalledElements;
            //first check if there is data to be imported
            if (xmlElements.SelectSingleNode("//Row") != null)
            {
                //convert to XDocument for better performance
                XDocument xElements = XDocument.Load(xmlElements.CreateNavigator().ReadSubtree());

                var query = from xElem in xElements.Element("EADATA").Element("Dataset_0").Element("Data").Elements("Row")
                            select new ExportRow
                            {
                                ID = xElem.Element("ID").Value,
                                Type = xElem.Element("Type").Value,
                                Name = xElem.Element("Name").Value,
                                ImportanceLevel = xElem.Element("ImportanceLevel").Value,
                                Author = xElem.Element("Author").Value,
                                Created = xElem.Element("Created").Value,
                                Modified = xElem.Element("Modified").Value,
                                Documentation = xElem.Element("Documentation").Value,
                                ParentName = xElem.Element("ParentName").Value,
                                ParentID = xElem.Element("ParentID").Value,
                                QualifiedName = xElem.Element("QualifiedName").Value
                            };
                //query
                List<ExportRow> rows = query.ToList();

                //first add the headers
                marshalledElements = new object[rows.Count, 11];
                //headers now in the template excel file
                //marshalledElements = this.addHeaders(marshalledElements);
                //then add all rows
                for (int i = 0; i < rows.Count; i++)
                {
                    ExportRow row = rows[i];
                    marshalledElements[i , 0] = rows[i].ID;
                    marshalledElements[i , 1] = rows[i].Type;
                    marshalledElements[i , 2] = rows[i].Name;
                    marshalledElements[i , 3] = rows[i].ImportanceLevel;
                    marshalledElements[i , 4] = rows[i].Author;
                    DateTime createdDate;
                    if (DateTime.TryParse(rows[i].Created, out createdDate))
                    {
                        marshalledElements[i , 5] = createdDate;
                    }
                    else
                    {
                        //if the date cannot be parsed then just add the string as is
                        marshalledElements[i , 5] = rows[i].Created;
                    }
                    DateTime modifiedDate;
                    if (DateTime.TryParse(rows[i].Modified, out modifiedDate))
                    {
                        marshalledElements[i , 6] = modifiedDate;
                    }
                    else
                    {
                        //if the date cannot be parsed then just add the string as is
                        marshalledElements[i , 6] = rows[i].Modified;
                    }
                    marshalledElements[i , 7] = rows[i].QualifiedName;
                    if (rows[i].Documentation.Length < 255)
                    {
                        marshalledElements[i , 8] = rows[i].Documentation;
                    }
                    else
                    {
                        marshalledElements[i , 8] = rows[i].Documentation.Substring(0, 254);
                    }
                    marshalledElements[i , 9] = rows[i].ParentName;
                    marshalledElements[i , 10] = rows[i].ParentID;
                }

            }
            else
            {
                //no data, just returns the array with the headers
                marshalledElements = new string[1, 11];
                //marshalledElements = this.addHeaders(marshalledElements);
            }
            return marshalledElements;
        }

• Dump the array into Excel using the Value2 (see reply 3)

Geert

[Geert Bellekens]

Simon,

What I've done to export the model details to an excel file is:

• Use Repository.SQLQuery to get the details into an xml file (see reply 1)
• Read the xml file into an 2 dimensional array (see reply 2)
• Dump the array into Excel using the Value2

Code: [Select]

       /// <summary>
        /// writes the details of the marshalled elements to the excel file
        /// </summary>
        /// <param name="marshalledElements">the marshalled elements</param>
        protected override void write(object[,] marshalledElements)
        {
            if (excel == null)
            {
                excel = new ExcelConnector(System.IO.Path.Combine(System.Reflection.Assembly.GetExecutingAssembly().CodeBase, @"..\Files\EA_Export.xlt"), true); //TODO change into relative path -> installation
            }
            //no headers so start writing at row 2
            excel.write(marshalledElements,2);
            //figure out which columns contain dates. those columns should be formatted as dates.
            for (int i = 0; i < marshalledElements.GetUpperBound(1); i++)
            {
                //look in the second row as the first one will contain the headers
                if (marshalledElements.GetUpperBound(0)> 1 && marshalledElements[1, i] is DateTime)
                {
                    excel.FormatColumnAsDate(i);
                }
            }
            if (!refresh)
            {
                excel.autofitColumns();
                excel.freezePanes();
                excel.setVariable("sourceID", this.source.getID());
                excel.Save(this.excelFilePath);
                excel.close();
            }
        }
        /// <summary>
        /// write the contents to the excel sheet starting at the given row
        /// </summary>
        /// <param name="contents">the context to write</param>
        /// <param name="beginrow">the row to start from</param>
        public void write(object[,] contents, int beginrow)
        {
            //get the exact range where the contents should be placed
            Range targetRange = sheet.get_Range(sheet.Cells[beginrow, 1], sheet.Cells[contents.GetUpperBound(0)+1 + beginrow-1, contents.GetUpperBound(1)+1]);
            targetRange.Value2 = contents;
        }
[/List]

Geert

Adjusting the sql query to include attribute and Key details should be pretty straightforward.

Geert