baseline sql search

[ToniPepperoni]

Hi guys,

I use the baseline feature and have a question. I structered my model in the following way:
- model package
    - (some cross-system views)
    - System x
            - some system-specific views
                         - element X
    - System y
             - again some system-specific view
                          - element Y

the baseline of the model represents a snapshot of my system-landscape. The system(-packages) have its own baseline. The elements (e.g. element X, element Y) have also a version (in their version-property).

I want to know now which baseline (from root-Model or at least from the parent package) contains my element in version x.y.

I guess the SQL-Search is the only solution for me, so I tried a statement like this:

SELECT pack.Package_ID, doc.Version, obj.Version, obj.Name, doc.DocName, pack.ea_guid
FROM `t_document` AS doc, `t_object` AS obj, `t_package` AS pack
WHERE obj.Package_ID = pack.Package_ID AND pack.ea_guid = doc.ElementID AND obj.Name like 'element X' AND obj.Version = '7.7'

But this statement is wrong and has also some confusing behavior. For example, if I restore the baseline from the root, which contains the element "elemnt X" in verson 7.7,
then this statement gives all baselines from the parent package. If i restore a root-model-baseline which does not contain the element in version 7.7, then this does not give any result.

Please note that I am not very familiar with sql.

Thank you very much.

Regards,
Toni

Edit: I just opened the .eap-File with access and it is still confusing me. If I restore the model-baseline 1.1, which cointains element X in version 1.0, the table "t_object" contains this element in version 1.0.

But when I open the file again in EA and restore the model-baseline 2.2, wich contains element X in version 7.7, the table "t_object" contains this element in version 7.7. So where does ea save the element version?

[OpenIT Solutions]

Hi,

I beleive the baseline itself is stored as a binary object. We use the SQL below to tell us which package a baseline relates to - this may help?

SELECT DISTINCT t_object.ea_guid AS CLASSGUID, t_object.Object_Type AS CLASSTYPE,  
  t_object.stereotype As 'Level', t_object.Name As 'Name', t_object.Status, t_object.Author, t_object.Version, t_object.ModifiedDate, t_object.CreatedDate,
t_document.DocName As [Baseline Name],  
t_document.Version As [Baseline Version],
t_document.Notes As [Baseline Notes],
t_document.DocDate As [Basline Date]
FROM t_object, t_package left outer join t_document on t_document.ElementID = t_package.ea_guid and t_document.DocType = 'Baseline'
WHERE t_package.package_id IN (#Branch#)
AND t_package.ea_guid = t_object.ea_guid

This works for us against SQL Server.

Regards,

Jon.

[Stefan Bolleininger]

Hi Toni,

The snapshot of the baseline is stored as a binary file (blob).

To retrieve them you need to get alle baselines bound to a specific package.
IF you baseline the whole model (which i do NOT recommend in your cases) then you need all baselines from your model.

If you have these baselines (which can be found by a sql search depending on packageguid/packageid) you will need to decode them (encoded64) and unzip them. Afterwards you hold a file in xml-syntax and can search after your part of the string.

This cannot be done within SQL standalone, your need some script for it.

This method is very slow  :-/

SQL-query: for retrieving the baseline:

Code: [Select]

Repo.SQLQuery("SELECT Version,Notes,Docdate,Author  FROM `t_document` WHERE `DocType` = 'Baseline' AND ElementID LIKE \"" + DocName + "\" Order by Docdate desc");
Decode a single baseline-file

Code: [Select]

byte[] b = Convert.FromBase64String(ea.ENARTalis_main.delxml(_Repo.SQLQuery("SELECT `BinContent` FROM t_document WHERE `ElementID` = '" + pkgguid + "' AND `Version` = '" + pversion + "'")));
Save the files to disc

Code: [Select]

               // Opens existing zip file
                ZipStorer zip = ZipStorer.Open(memsin, FileAccess.ReadWrite);

                // Read all directory contents
                List<ZipStorer.ZipFileEntry> dir = zip.ReadCentralDir();

                // Extract all files in target directory
                bool result;
                foreach (ZipStorer.ZipFileEntry entry in dir)
                {
                    MemoryStream ms = new MemoryStream();
                    string targetpath = filenameoutput;
                    result = zip.ExtractFile(entry, targetpath);
                    MessageBox.Show("History File created");
                }This uses the free Zipstorer which can be found in google.

You could also write them into a stream and don't have to save them.

That could be one way.

regards

Stefan

[ToniPepperoni]

Thank you very much guys.
I adapted the idea from Stefan and its working now.