[Gambas-bugtracker] Bug #3068: Support for foreign keys in gb.db2

[<bugtracker@xxxxxxxxxxxxxx>]

http://gambaswiki.org/bugtracker/edit?object=BUG.3068&from=L21haW4-

Gen BRAGA reported a new bug.

Summary
-------

Support for foreign keys in gb.db2

Type             : Request
Priority         : Medium
Gambas version   : Master
Product          : Database component


Description
-----------

Would it be possible to enhance the gb.db2 component to provide native support for defining foreign keys, assuming this functionality is enabled on the underlying database manager (e.g., SQLite)? This enhancement would ideally include options for ON DELETE and ON UPDATE clauses to manage referential actions.

A proposed syntax for this could be similar to the following:

hTable = $con.Tables.Add("Table1")
hTable.Fields.Add("id", db.Serial)
hTable.Fields.Add("ForeignId", db.Integer)
hTable.Fields.Add("Value1", db.Integer)
hTable.PrimaryKey = ["id"]
hTable.ForeignKey.Key = ["ForeignId"] ' The column(s) in the current table that form the foreign key
hTable.ForeignKey.ReferencesTable = "ParentTable" ' The table being referenced
hTable.ForeignKey.ReferencesKey = ["id"] ' The column(s) in the parent table being referenced
hTable.ForeignKey.OnUpdate = db.Cascade ' (or db.NoAction, db.Restrict, db.SetNull, db.SetDefault)
hTable.ForeignKey.OnDelete = db.Restrict ' (or db.Cascade, db.NoAction, db.SetNull, db.SetDefault)

Thanks in advance.


System information
------------------

[System]
Gambas=3.20.99 942b011 (master)
OperatingSystem=Linux
Distribution=Ubuntu 20.04.6 LTS (Focal Fossa)
Kernel=5.15.0-139-generic
Architecture=x86_64
Cores=12
Memory=15902M
Language=pt_BR.UTF-8
Platform=x11
Desktop=CINNAMON
DesktopResolution=96
DesktopScale=7
WidgetTheme=bluementa
Font=Sans,9
DarkTheme=False
[Programs]
dpkg=Programa de gerenciamento de pacotes Debian 'dpkg' versão 1.19.7 (amd64).
gcc=gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0
git=git version 2.25.1
msgmerge=msgmerge (GNU gettext-tools) 0.19.8.1
rpmbuild=RPM version 4.14.2.1
[Libraries]
Cairo=libcairo.so.2.11600.0
Curl=libcurl.so.4.6.0
DBus=libdbus-1.so.3.19.11
GDK2=libgdk-x11-2.0.so.0.2400.32
GDK3=libgdk-3.so.0.2404.16
GStreamer=libgstreamer-1.0.so.0.1603.0
GTK+2=libgtk-x11-2.0.so.0.2400.32
GTK+3=libgtk-3.so.0.2404.16
OpenGL=libGL.so.1.7.0
Poppler=libpoppler.so.97.0.0
QT5=libQt5Core.so.5.12.8
RSvg=librsvg-2.so.2.47.0
SDL=libSDL2-2.0.so.0.10.0
SQLite3=libsqlite3.so.0.8.6
[Environment]
CINNAMON_VERSION=4.4.8
DBUS_SESSION_BUS_ADDRESS=unix:path=/run/user/1000/bus
DESKTOP_SESSION=cinnamon
DISPLAY=:0
GB_GUI=gb.gtk3
GDMSESSION=cinnamon
GDM_LANG=pt_BR
GIO_LAUNCHED_DESKTOP_FILE=/usr/share/applications/gambas3.desktop
GIO_LAUNCHED_DESKTOP_FILE_PID=5813
GJS_DEBUG_OUTPUT=stderr
GJS_DEBUG_TOPICS=JS ERROR;JS LOG
GNOME_DESKTOP_SESSION_ID=this-is-deprecated
GPG_AGENT_INFO=/run/user/1000/gnupg/S.gpg-agent:0:1
GTK_MODULES=gail:atk-bridge
GTK_OVERLAY_SCROLLING=0
HOME=<home>
LANG=pt_BR.UTF-8
LANGUAGE=pt_BR.UTF-8
LC_ADDRESS=pt_BR.UTF-8
LC_IDENTIFICATION=pt_BR.UTF-8
LC_MEASUREMENT=pt_BR.UTF-8
LC_MONETARY=pt_BR.UTF-8
LC_NAME=pt_BR.UTF-8
LC_NUMERIC=pt_BR.UTF-8
LC_PAPER=pt_BR.UTF-8
LC_TELEPHONE=pt_BR.UTF-8
LC_TIME=pt_BR.UTF-8
LOGNAME=<user>
PATH=<home>/.local/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin:/usr/local/go/bin
PWD=<home>
QT_ACCESSIBILITY=1
QT_LOGGING_RULES=*.debug=false
SESSION_MANAGER=local/<hostname>:@/tmp/.ICE-unix/1483,unix/<hostname>:/tmp/.ICE-unix/1483
SHELL=/bin/bash
SHLVL=0
SSH_AGENT_PID=1570
SSH_AUTH_SOCK=/run/user/1000/keyring/ssh
TZ=:/etc/localtime
USER=<user>
XAUTHORITY=<home>/.Xauthority
XDG_CONFIG_DIRS=/etc/xdg/xdg-cinnamon:/etc/xdg
XDG_CURRENT_DESKTOP=X-Cinnamon
XDG_DATA_DIRS=/usr/share/cinnamon:/usr/share/gnome:/usr/local/share/:/usr/share/
XDG_GREETER_DATA_DIR=/var/lib/lightdm-data/<user>
XDG_RUNTIME_DIR=/run/user/1000
XDG_SEAT=seat0
XDG_SEAT_PATH=/org/freedesktop/DisplayManager/Seat0
XDG_SESSION_CLASS=user
XDG_SESSION_DESKTOP=cinnamon
XDG_SESSION_ID=c2
XDG_SESSION_PATH=/org/freedesktop/DisplayManager/Session0
XDG_SESSION_TYPE=x11
XDG_VTNR=7
no_proxy=localhost, 127.0.0.0/8, ::1, 10.0.0.0/8, 192.168.0.0/16


----[ Gambas bugtracker-list is hosted by https://www.hostsharing.net ]----