Godot SQLite Foreign Key-CodePudding

Could someone please show me how to create a foreign key relationship between two tables in Godot. I can not figure based on the current documentation.

extends Node2D

const SQLite = preload("res://addons/godot-sqlite/bin/gdsqlite.gdns")

var save_path = "user://data.sqlite"
var db 

func _ready():

    
    db = SQLite.new()
    db.path = save_path
    db.foreign_keys = true
    db.open_db()
    
    db.create_table("dog_names", {
        "id": {"data_type": "int", "primary_key": true, "auto_increment": true},
        "first_name": {"data_type": "text"},
        "last_name": {"data_type": "text"},
    })
    
    db.create_table("owners", {
        "id": {"data_type": "int", "primary_key": true, "auto_increment": true}, 
        "name": {"data_type": "text"}
    })
    db.query("SELECT * FROM dog_names;")
    print(db.query_result[0]["first_name"])

Essentially I just want to add a many-to-one relationship between table owner and table dog. One owner can have many dogs but a dog can only have one owner.

Thanks

CodePudding user response：

First of all, you need to enable them before opening the database:

db.foreign_keys = true
# …
db.open_db()

Then you need a field that references the other table, and add an entry of the form "foreign_key": "TABLE_NAME.FIELD_NAME" (the data_type must match, the referenced field must be either "primary_key": true or "unique": true):

For example:

db.create_table("dog", {
        # …
        "owner_id": {"data_type": "int", "foreign_key": "owner.id"}
    })

And that would make your relationship. In this case the dog zero or one owner (you can use "not_null": true if you want to enforce it must have one). And the owner has zero to many dogs.

With the foreign key constraint, when you insert or modify the records of the dog table, the owner_id field must match one of the id of one of the records on the owner table (or be null).

For example, if the record on the dog table says owner_id is 123, then there must be a record on the owner table which id is 123. It won't let you insert or modify that value in such way that it violates that constraint.

So that once you have inserted data in your database, you know that you can use the owner_id to query into the owner table:

var dog_query_str := "SELECT owner_id FROM dog;"
var owner_query_str := "SELECT name FROM owner WHERE id=?;"
db.query(dog_query_str)
for result in db.query_result:
    db.query_with_bindings(owner_query_str, [result["owner_id"]])
    print(db.query_result)

Note that here I'm using prepared statements.

Or you can use a JOIN:

var query_str := "SELECT name FROM dog JOIN owner ON dog.owner_id = owner.id;"
db.query(query_str)
print(db.query_result)

You would, of course, modify this to your needs. Please refer to SQL As Understood By SQLite for the SQL syntax supported by SQLite.

CodePudding user response：

So is this the best/only way to get information from the foreign_key relationship?

db.query("SELECT owner FROM dog_names;")
    print(db.query_result)
    print(db.query_result[0])
    var o = db.query_result[0]["owner"]
    print(o)
    var o_str = "SELECT name FROM owners WHERE id = {int};"
    var o_form = o_str.format({"int": o})
    db.query(o_form)
    print(db.query_result)