Support Single and Multi-Column Indexes and Arbitrary Constraints.

[GrammAcc]

Is your feature request related to a problem? Please describe.

godot-sqlite currently only has support for single-column Foreign Key constraints, but a couple of extremely common optimizations in sql databases are Unique constraints and column Indexes, both on single and multiple columns.

Simple database schemas may work for game saves, but json is a better format for game saves anyway since sql is usually slower in these use cases, so I think this extension will see more use in tools than in actual games, and that is what I am currently using the extension for.

Of course, it is possible to create any kind of table with raw sql, but I think most users of a sql extension like this expect to be able to use constraints and indexes from the ORM, so I think this is worth adding.

Describe the solution you'd like

Somehow add the ability to declare single or multi-column indexes and arbitrary constraints (ones supported by sqlite3) to tables without using raw sql.

To make a multi-column index/constraint work with the current table_columns dict API, I think adding special "INDEXES" and "CONSTRAINTS" keys would work (UPPER_CASE used to show they are reserved and not column names).

Basically, it would work like this in gdscript:

Single column version:

var item_table_columns: Dictionary = {
    "id": {
        "data_type": "int",
        "primary_key": true,
        "auto_increment": true,
    },
    "name": {
        "data_type": "char(12)",
        "not_null": true,
        "unique": true,
        "index": true,
    },
    "power": {
        "data_type": "int",
        "index": true,
    },
    "category": {
        "data_type": "int",
        "not_null": true,
        "foreign_key": "item_categories.id",
    },
}

Multi-column version:

var item_table_columns: Dictionary = {
    "id": {
        "data_type": "int",
        "primary_key": true,
        "auto_increment": true,
    },
    "name": {
        "data_type": "char(12)",
        "not_null": true,
    },
    "category": {
        "data_type": "int",
        "not_null": true,
        "foreign_key": "item_categories.id",
    },
    "INDEXES": {
        # key is the name of the declared index.
        # accepts an Array of column names declared in the same table_columns dict.
        "name_category_idx": ["name", "category"],
        "name_power_idx": ["name", "power"],
    },
    "CONSTRAINTS": {
        # keys in the CONSTRAINTS dict indicate the type of constraint to create over multiple columns.
        "UNIQUE":
            # same syntax as INDEXES above, key is constraint name, and value is an Array of column names.
            {"name_category_unq": ["name", "category"]},
    },
}

Note: The above schema is just an example and may not be an efficient use of Indexes. :)

Describe alternatives you've considered

At first, I was thinking that it would be best to just mimic the declarative ORM in SQLAlchemy or Django, but I think that would be overkill, and it would likely require a lot more work than modifying the current API with support for arbitrary multi-column constraints.

Additional context

As an aside, this feature would also allow multi-column primary and foreign keys which are very useful in some more complex schemas.

Lastly, I would submit a PR for this myself, but I'm a python programmer, and I can hardly read C++, so I can't do much with the code, but if you need any other information from me or help testing, please let me know!

Thank you!

[2shady4u]

Hi @GrammAcc!

Am I correct in stating that the so-called Single column version is already supported and that this issue solely pertains itself to the Multi-column version?

[GrammAcc]

Hi @GrammAcc!

Am I correct in stating that the so-called Single column version is already supported and that this issue solely pertains itself to the Multi-column version?

Thank you for your quick reply!

This issue pertains to both the single and multi-column versions of Indexes and Constraints.

As far as I can tell from the documentation and experimenting with the API myself (I haven't delved into the C++ source), single-column Primary and Foreign keys are supported, but the multi-column versions are not, and neither single nor multi-column versions of other constraints such as UNIQUE or CHECK are supported.

Additionally, Indexes are not supported either on single or multiple columns as far as I can tell.

So, I guess this feature might be a bit broad, but I think it boils down to two related functionalities being added:

1. Single or multi-column indexes.
2. Arbitrary single or multi-column constraints (not just primary and foreign key constraints).

I used multi-column indexes and UNIQUE constraints in my example since those are the features I use most in my current project, but I think that if indexes and a UNIQUE constraint are going to be added, then it would be better to just implement a generic API that allows declaring arbitrary constraints.

I'm not sure how this would need to be implemented in C++, so I'm basing these assumptions on software design in object-oriented languages like Python.

It occurs to me that even if there is a common API in GDScript for declaring constraints, due to the slightly different syntax for different constraints in SQL, some portion of the implementation would likely need to be different for each of them, so I guess for this specific feature request, support for single and multi-column Indexes and the UNIQUE constraint would be what I need most, and support for other constraints could be added gradually after that since the related boilerplate would already be in place?

Sorry if I'm making this more complicated. I realized after going over the details again that the scale is likely larger than I initially thought.

Please let me know if you need any other clarification from me.

Thank you again for your time!

[GrammAcc]

Hi @2shady4u,

I have switched from godot-sqlite to a custom implementation of a document-based NoSQL database for my current project as it gives me more flexibility in the schema design during development.

I may switch back to SQL once I'm further along in development and don't need as much flexibility, but I wanted to give you a heads up that this issue no longer pertains to my specific use case in my current project.

If other users are interested in this feature, I would be happy to help with testing on the GDScript/user side, but if no one else needs this, please feel free to close this issue as you see fit.

Please let me know if I can help with anything related to this issue.

Thank you for your time!