Added Foreign & Composite Primary Key support to mysqloo driver

[nodgear]

This change provides the following features:

• Composite primary key
• Foreign key relations

Both without breaking changes.

Example model:

-- Creates a table with composite primary key with one of them being foreign
sqlier.Model({
 Table = "experience",
 Columns = {
   charid = {
     Type = sqlier.Type.Integer,
     Relation = {
       Table = "character",
       Column = "id"
     }
   },
   category = {
     Type = sqlier.Type.String,
     MaxLength = 120
   },
   amount = {
     Type = sqlier.Type.Integer,
     Default = 0
   }
 },
 Identity = { "charid", "category" }
})

Limitation

The limitation is the same as many other ORM's/Abstractions: Tables must be created in the correct order otherwise sgdb will say reference table/column doesn't exist. This limitation should be easy to overcome and natural to any developer.

[ceifa]

Hey, thank you for your PR.

Great addition on composite identities! About the foreign key, I'm not sure if this is something sqlier should solve. One of the main ideas of sqlier is, write model code once and run it in any database, and things like that break it. Tbh on my opinion foreign key constraints doesn't bring real benefits and can be solved on the application layer.

[ceifa]

Anyway, just for reference, this is how sequelize deal with relationships: https://sequelize.org/docs/v6/core-concepts/assocs/

[ceifa]

A good relationship code suggestion:

local User = sqlier.Model({
    Table = "user",
    Columns = {
        Rank = {
            Type = sqlier.Type.String,
            MaxLength = 15
        },
        SteamId64 = {
            Type = sqlier.Type.SteamId64
        }
    },
    Identity = "SteamId64"
})

local Character = sqlier.Model({
    Table = "character",
    Columns = {
        Id = {
            Type = sqlier.Type.Integer,
            AutoIncrement = true
        },
        Name = {
            Type = sqlier.Type.String
        },
        UserId = User,
        Identity = "Id"
    }
})