CASCADE verbs

[ghost]

HI, I'm exepriencing an issue with foreign key triggers.

After database creation only the ON DELETE CASCADE Keyword is present. The key ON UPDATE CASCADE is missing.
I'm using the System.ComponentModel.DataAnnotations.Schema ForeignKeyAttribute (which doesn't provide any other options).

Do you think I'm doing something wrong?

[ghost]

I saw in your ForeignKeyStatementBuilder.cs you have only the CascadeVerb (always on).
Why don't you add a custom ForeignKeySQLiteAttribute inheriting the ForeignKeyAttribute and adding to this the optional params OnCascade and OnUpdate verbs?

Something like:

public class ForeignKeySQLiteAttribute : ForeignKeyAttribute
{
    public bool OnUpdate { get; set; }
    public bool OnDelete { get; set; }

    public ForeignKeySQLite(string name) : base(name)
    {
        OnDelete = false;
        OnUpdate = false;
    }
}

[msallin]

You don't do anything wrong. This isn't supported by the Entity Framework and therefore also not by this library.

A custom attribute could easily be implemented of course. However, I try to do this as little as possible. Can you elaborate on your use case?

[ghost]

Lets make a real example. This is an extract for my entities:

[Table("t_works")]
public class Work
{

[Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Column("id")]
    [Autoincrement]
    public int Id { get; private set; }

    [Column("type", TypeName = "INT")]
    [SqlDefaultValue(DefaultValue = "0")]
    [Required]
    public WorkTypes Type { get; set; }

...

public virtual ICollection<WorkEvent> Events { get; set; }

}

[Table("t_works_event")]
public class WorkEvent
{
[ForeignKey("WorkID")]
public Work WorkParent { get; set; }

    [Key]
    [Column("ref_id_work", TypeName = "INT", Order = 0)]
    public int WorkID { get; internal set; }

    [Key]
    [Column("type", TypeName = "INT", Order = 1)]
    [Required]
    public WorkEventType Type { get; set; }

    [Key]
    [Column("insert_timestamp", TypeName = "DATETIME", Order = 3)]
    public DateTime ElevationTime { get; set; }

...

}

---

This is an extract of the original DB I've used (created by hand):

(this table has an independent rowid)
CREATE TABLE [t_works](
[id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[type] INT NOT NULL DEFAULT 0,

...

(this table has no rowid)
CREATE TABLE [t_works_event](
[ref_id_work] INTEGER NOT NULL REFERENCES t_works ON DELETE CASCADE ON UPDATE CASCADE,
[type] INT NOT NULL,
[insert_timestamp] DATETIME,

...

PRIMARY KEY([ref_id_work], [type], [insert_timestamp] ASC)) WITHOUT ROWID;
CREATE INDEX [idx_refence] ON [t_works_event]([ref_id_work] ASC);

---

This is the output for a created DB with your library:

CREATE TABLE "t_works" (
[id] INTEGER PRIMARY KEY AUTOINCREMENT,
[type] int NOT NULL DEFAULT (0),

...

CREATE TABLE [t_works_event](
[ref_id_work] int NOT NULL REFERENCES t_works ON DELETE CASCADE,
[type] int NOT NULL,
[insert_timestamp] datetime NOT NULL,

...

PRIMARY KEY([ref_id_work], [type], [insert_timestamp] ASC));
CREATE INDEX [IX_t_works_event_ref_id_work] ON t_works_event;

---

As you can see there are some differences between the original and the generated versions:

• the rowid (a SQLite specific feature) is used everywhere instead only on the first entity type.
• the ON DELETE CASCADE works correctly but not the ON UPDATE verb.

So if I update (change the value) of the "id" field (that is not the real id because of the rowid) in my "work" entity, the change is not propagated through all the child elements of "work_events".

My suggestion is to add two attributes: one for rowid (not strictly necessary) and one for drive delete/cascade verbs in code fisrt. The second could be a derived class from ForeignKeyAttribute.
In this way you maintain the compatibilty with the original EF framework which, i case, will get the default attribute.

Lorenzo

[NicholasRafka]

``

_{Sent with GitHawk}

[msallin]

Thanks for your explanations. I'm okay with having these custom attributes. Feel free to open a PR. Please take care that no breaking change occurs and to add corresponding unit tests & integration tests.