Create connections in SQL - generate GUID?

[bluedevilva]

I am trying to write some tools/scripts that will operate directly on the database repository for a project.  The motivation for this is that I intend to export the values from a relationship matrix to a file, make numerous changes, and then essentially 'import' the values back into the project.

I would like to add new relationships directly to the t_connector table, but this will require generating a GUID for each connection.  Is there an easy way to do this?  I have not been able to find any documentation suggesting how GUIDs are generated, and the SQL up-sizing script from Sparx does not appear to contain any procedures to facilitate this operation.

Related question, but more a request for opinions: am I barking up the wrong tree with a SQL-based approach?  Will I have the same flexibility, and a better API, if I attempt to write an add-in instead?

Thanks,
BlueDevilVA

[Frank Horn]

am I barking up the wrong tree with a SQL-based approach?  Will I have the same flexibility, and a better API, if I attempt to write an add-in instead?

Yes and no. Using the api will be less work for most operations (e.g. you create a connector, call the .Update() method and a GUID will be created and assigned. But there are some little gaps in the api which have caused  people to write to the database from their add ins now and then.

[bluedevilva]

Issue resolved, with thanks to Sparx support and a little further research...

For anyone who is interested in doing direct repository manipulation, particularly with insertion of a new value of pretty much any kind, the Sparx GUID is derived from the same method as NEWID() in most SQL databases (the function is UUID() in MySQL).  The result is a character string that is unique in both time and (logical) space, so it is independent of your data set.

Sparx repositories use curly brackets on either end of the GUID, so to insert a value into (for example) the t_connector table, a script would look something like this (using MySQL syntax):

Code: [Select]

INSERT INTO t_connector (ea_guid, ...) VALUES (CONCAT('{', UUID(), '}'), ...);
Obviously the UUID() call will be different depending on the database you are using (the SQL community has yet to standardize this functionality).  The result is a value that looks like {aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee}, which conforms to Sparx EA format for GUID.