Macros/Base/ManageDBTableRelationships

Description

This example illustrate how to create and suppress relationships between FournisseursProduits table and Produits (Products) and Fournisseurs (Providers) reference tables.

Unrelated Base Tables

Scripts start with deleting existing indexes then:

• Define FournisseursProduits primary key
• Add the relationship with Providers table
• Add the relationship with Products table
• Show Table Relationships dialog

Index management uses createDataDescriptor method that defines the foreign key column, index type and referential integrity

The relationship with Products and Providers tables is set while:

• Naming the target table
• Identifying its primary key
• Defining cascading update rules

Code

Basic

Option Explicit

Sub SetRelationships
	''' Suppress and create relationships between FournisseursProduits table
	' and Produits/Fournisseurs tables.
	' Show 'Relation Design' dialog'
	'''
	Dim connection As Object ' com.sun.star.sdbc.drivers.OConnectionWrapper
	Dim tbl As Object ' com.sun.star.sdb.dbaccess.ODBTableDecorator
	Dim i As Long ' table index count
	Dim pk as Object ' com.sun.star.sdb.OTableColumnWrapper
	Dim desc As Object ' com.sun.star.sdbcx.VKeyDescriptor
	Dim ID_col As Object ' com.sun.star.sdbcx.VKeyDescriptor

	connection = ThisDatabaseDocument.DataSource.getConnection("","") 
	tbl = connection.Tables.getByName("FournisseursProduits")

	' Suppress existing indexes
	For i = tbl.keys.count - 1 to 0 step -1
		tbl.keys.dropByIndex(i)
	Next i 

	' primary key index creation
	pk = tbl.columns.getByName("pk_FournisseurProduit")
	desc = tbl.Keys.createDataDescriptor
	desc.Type = com.sun.star.sdbcx.KeyType.PRIMARY
	desc.Columns.appendByDescriptor(pk)
	tbl.Keys.appendByDescriptor(desc) ' add to index
	
	' create relation with 'Fournisseurs' table
	Set desc = Nothing
	desc = tbl.Keys.createDataDescriptor
	ID_col = desc.Columns.createDataDescriptor
	ID_col.Name = "fk_Fournisseur"
	ID_col.Type = com.sun.star.sdbcx.KeyType.FOREIGN
	ID_col.RelatedColumn = "pk_Fournisseur"
	desc.Type = com.sun.star.sdbcx.KeyType.FOREIGN
	desc.ReferencedTable = "Fournisseurs"
	desc.UpdateRule = com.sun.star.sdbc.KeyRule.NO_ACTION
	desc.DeleteRule = com.sun.star.sdbc.KeyRule.CASCADE
	desc.Columns.appendByDescriptor(ID_col)
	tbl.Keys.appendByDescriptor(desc) ' add to index

	' create relation with 'Produits' table
	Set desc = Nothing
	desc = tbl.Keys.createDataDescriptor
	ID_col = desc.Columns.createDataDescriptor
	ID_col.Name = "fk_Produit"
	ID_col.Type = com.sun.star.sdbcx.KeyType.FOREIGN
	ID_col.RelatedColumn = "pk_Produit"
	desc.Type = com.sun.star.sdbcx.KeyType.FOREIGN
	desc.ReferencedTable = "Produits"
	desc.UpdateRule = com.sun.star.sdbc.KeyRule.NO_ACTION
	desc.DeleteRule = com.sun.star.sdbc.KeyRule.SET_NULL
	desc.Columns.appendByDescriptor(ID_col)
	tbl.Keys.appendByDescriptor(desc) ' add to index
	
	Call _UIDbRelationships

' Credits: 
'	Pierre-Yves Samyn, 	Nathan Ullberg
End Sub

Sub _UIDbRelationships
	'''
	GlobalScope.BasicLibraries.LoadLibrary("ScriptForge")
	Dim doc As Object : doc = CreateScriptService("SFDocuments.Document", ThisDatabaseDocument)
	doc.RunCommand("DBRelationDesign" )
End Sub

Python

# coding: utf-8
from __future__ import unicode_literals
import uno  # background scripts require this module
from com.sun.star.sdbcx.KeyType import PRIMARY, FOREIGN
from com.sun.star.sdbc.KeyRule import CASCADE, NO_ACTION, SET_NULL

def SetRelationships():
    ''' 1. Suppress and create relationships between FournisseursProduits
    table and Produits/Fournisseurs tables.
    2. Show 'Relation Design' dialog

    connection  com.sun.star.sdbc.drivers.OConnectionWrapper
    tbl         com.sun.star.sdb.dbaccess.ODBTableDecorator
    pk          com.sun.star.sdb.OTableColumnWrapper
    desc,ID_col com.sun.star.sdbcx.VKeyDescriptor
    '''
    doc = XSCRIPTCONTEXT.getDocument()
    connection = doc.DataSource.getConnection('', '')
    tbl = connection.Tables.getByName('FournisseursProduits')

    # suppress existing indexes
    for x in range(tbl.Keys.Count-1, -1,-1):
        tbl.Keys.dropByIndex(x)

    # primary key index creation
    pk = tbl.Columns.getByName('pk_FournisseurProduit')
    desc = tbl.Keys.createDataDescriptor()
    desc.Type = PRIMARY
    desc.Columns.appendByDescriptor(pk)
    tbl.Keys.appendByDescriptor(desc)  # add to index

    # create relation with 'Fournisseurs' table
    fk = tbl.Keys.createDataDescriptor()
    ID_col = fk.Columns.createDataDescriptor()
    ID_col.Name = 'fk_Fournisseur'
    ID_col.Type = FOREIGN
    ID_col.RelatedColumn = 'pk_Fournisseur'
    fk.Type = FOREIGN
    fk.ReferencedTable = 'Fournisseurs'
    fk.UpdateRule = NO_ACTION
    fk.DeleteRule = CASCADE
    fk.Columns.appendByDescriptor(ID_col)
    tbl.Keys.appendByDescriptor(fk)  # add to index

    # create relation with 'Produits' table
    fk = tbl.Keys.createDataDescriptor()
    ID_col = fk.Columns.createDataDescriptor()
    ID_col.Name = 'fk_Produit'
    ID_col.Type = FOREIGN
    ID_col.RelatedColumn = 'pk_Produit'
    fk.Type = FOREIGN
    fk.ReferencedTable = 'Produits'
    fk.UpdateRule = NO_ACTION
    fk.DeleteRule = SET_NULL
    fk.Columns.appendByDescriptor(ID_col)
    tbl.Keys.appendByDescriptor(fk)  # add to index
    
    _UIDbRelationships()

def _UIDbRelationships():
    ''' show 'Table Relationships' dialog '''
    dh = _createUnoService('com.sun.star.frame.DispatchHelper')
    url = '.uno:DBRelationDesign'
    StarDesktop = _createUnoService('com.sun.star.frame.Desktop')
    return dh.executeDispatch(StarDesktop.ActiveFrame, url,'', 0, ())

def _createUnoService(svc_name: str) -> uno:
    ''' params:
    svc_name    Service API name e.g. ..beans.PropertyValue '''
    ctx = XSCRIPTCONTEXT.getComponentContext()
    smgr = ctx.getServiceManager()
    return smgr.createInstanceWithContext(svc_name, ctx)

g_exportedScripts = SetRelationships,

Get in-depth UNO services properties and methods descriptions for:

• com.sun.star.sdbc.drivers.OConnectionWrapper
• com.sun.star.sdb.dbaccess.ODBTableDecorator
• com.sun.star.sdb.OTableColumnWrapper
• com.sun.star.sdbcx.VKeyDescriptor

Next to setting relationships between tables, scripts create additional indexes for FournisseursProduits table:

Newly created indexes

Base file to test macro

• Download example ODB file containing Basic/Python scripts