Manually adding links in the Postgres database

(Douglas (@finnatic at @waikato)) #1

Matrix Version:

In order to work around problems that we’re waiting on work to occur around, I’ve had a look at manually adding a link between two assets in Matrix, via SQL at the postgres command prompt.

I suspect I need a subquery to populate a linkid value before my insert command will work based on the error I got back on our test system from a rollback trigger.

Are there any resource accessible to clients that goes into the database structure? Or would I need to log a support request to get assistance?

(Douglas (@finnatic at @waikato)) #2

Following the SQL provided in Db questions I’ve got the INSERT working on our test system, although I’m not sure if I need to be more careful with the sort_order value - and I’m avoiding populating the is_dependant and is_exclusive values at the moment e.g. SQL is only:

INSERT INTO sq_ast_lnk (linkid,majorid,minorid,link_type,value,sort_order,updated,updated_userid) VALUES (NEXTVAL('sq_ast_lnk_seq')...

(David Schoen) #3

Hi Douglas,

That forum post you’ve linked to was from nearly 10 years ago and I’d actually just meant it to be illustrating a problem I was having with the indexes applied to the table at the time. I would strongly encourage you to avoid adding links manually as there’s a complex relationship between tables that isn’t fully constrained by DB code.

Normally we’d encourage clients to use the JS API:
https://matrix.squiz.net/manuals/web-services/chapters/javascript-api#linking

If PHP is more your style:

$GLOBALS['SQ_SYSTEM']->setRunLevel(SQ_RUN_LEVEL_FORCED);
$linkid = $GLOBALS['SQ_SYSTEM']->am->createAssetLink(
    $majorAsset,
    $minorAsset,
    $linkType
);
$GLOBALS['SQ_SYSTEM']->restoreRunLevel();

but there’s a lot less sanity checking on calls to the PHP API (especially with the run level downgraded) so it’s safe to say this isn’t supported.

(Douglas (@finnatic at @waikato)) #4

Hence my query as to whether there’s any client accessible resources that discuss the database structure, how links (etc) get created and why you really should use an API instead of direct SQL.

If we write something (in whatever language we choose) that uses the SOAP API (https://matrix.squiz.net/manuals/web-services/chapters/soap-api-link-service) I’m assuming that’s supported?

(David Schoen) #5

I don’t think we have anything client accessible currently, sorry.

Yep, if you’d prefer to use the SOAP API instead of the JS API, that’s fine too.

#6

This is old, but the schema hasn’t really changed that much over the years so you will get a good idea on where everything is: http://matrixusers.com/news/matrix-secrets-revealed/matrix-secrets-final.pdf

Note to self: try not to resurrect dead threads in the future.