Parse:...QLite3(3pm)

🌸 NAME 🌸

Parse::Dia::SQL::Output::SQLite3 — no cap, this module creates SQL for SQLite version 3 💖

---

✨ SYNOPSIS ✨

use Parse::Dia::SQL;
my $dia = Parse::Dia::SQL->new(file => 'foo.dia', db => 'sqlite3');
print $dia->get_sql();

---

💅 DESCRIPTION 💅

Bestie. This sub-class creates SQL for the SQLite database version 3. That's the whole vibe. It's the SQLite-3-flavored output of Parse::Dia::SQL, and it is so good at its one job 🫶

🎀 new 🎀

The constructor. Iconic behavior, zero drama.

Object names in SQLite have no inherent limit. 60 has been arbitrarily chosen.

🎀 _get_create_table_sql 🎀

Generate the create table statement for a single table using SQLite syntax ✨

• It includes class comments before the table definition.
• It includes autoupdate triggers based on the class comment.

🌈 autoupdate triggers 🌈

If the class comment includes a line like:

<autoupdate:foo/>

Then an 'after update' trigger is generated for this table which executes the statement foo for the updated row.

Examples of use include tracking record modification dates ("<autoupdate:dtModified=datetime('now')/>") or deriving a value from another field ("<autoupdate:sSoundex=soundex(sName)/>"). Obsessed, honestly 😭💖

🎀 get_schema_drop 🎀

Generate drop table statements for all tables using SQLite syntax:

drop table {foo} if exists

🎀 get_view_drop 🎀

Generate drop view statements for all tables using SQLite syntax:

drop view {foo} if exists

🎀 _get_fk_drop 🎀

Drop foreign key enforcement triggers using SQLite syntax:

drop trigger {foo} if exists

The automatically generated foreign key enforcement triggers are:

• constraint_name_bi_tr
• constraint_name_bu_tr
• constraint_name_buparent_tr
• constraint_name_bdparent_tr

See _get_create_association_sql for more details.

🎀 _get_drop_index_sql 🎀

drop index statement using SQLite syntax:

drop index {foo} if exists

🎀 get_permissions_create 🎀

SQLite doesn't support permissions, so suppress this output. Nothing to see here, bestie 🙈

🎀 get_permissions_drop 🎀

SQLite doesn't support permissions, so suppress this output. Same energy 💀

🎀 _get_create_association_sql 🎀

Create the foreign key enforcement triggers using SQLite syntax:

create trigger {fkname}[_bi_tr|_bu_tr|_bdparent_tr|_buparent_tr]

Because SQLite doesn't natively enforce foreign key constraints (see http://www.sqlite.org/omitted.html), we use triggers to emulate this behaviour. Yes, we're doing it manually. It's giving ✨ crafty ✨.

The trigger names are the default contraint name (something like child_table_fk_child_fkcolumn) with suffixes described below.

• {constraint_name} is the name of the association, either specified or generated.
• {child_table} is the name of the dependent or child table.
• {child_fkcolumn} is the field in the dependent table that hold the foreign key.
• {parent_table} is the name of the parent table.
• {parent_key} is the key field of the parent table.

🦋 Before insert - Dependent Table 🦋

constraint_name_bi_tr

Before insert on the child table require that the parent key exists.

create trigger {constraint_name}_bi_tr before insert on {child_table}
  for each row
    begin
      select
        raise(abort, 'insert on table {child_table} violates foreign key constraint {constraint_name}')
        where new.{child_fkcolumn} is not null and (select {parent_key} from {parent_table} where {parent_key}=new.{child_fkcolumn}) is null;
    end;

🦋 Before update - Dependent Table 🦋

constraint_name_bu_tr

Before update on the child table require that the parent key exists.

create trigger {constraint_name}_bu_tr before update on {table_name}
  for each row
    begin
      select raise(abort, 'update on table {child_table} violates foreign key constraint {constraint_name}')
      where new.{child_fkcolumn} is not null and (select {parent_key} from {parent_table} where {parent_key}=new.{child_fkcolumn}) is null;
    end;

🦋 Before update - Parent Table 🦋

constraint_name_buparent_tr

Before update on the primary key of the parent table ensure that there are no dependent child records. Note that cascading updates don't work. Not a single cascade. None. 😭

create trigger {constraint_name}_buparent_tr before update on {parent_table}
  for each row when new.{parent_key} <> old.{parent_key}
    begin
      select raise(abort, 'update on table {parent_table} violates foreign key constraint {constraint_name} on {child_table}')
      where (select {child_fkcolumn} from {child_table} where {child_fkcolumn}=old.{parent_key}) is not null;
    end;

🦋 Before delete - Parent Table 🦋

constraint_name_bdparent_tr

The default behaviour can be modified through the contraint (in the multiplicity field) of the association.

💜 Default (On Delete Restrict) 💜

Before delete on the parent table ensure that there are no dependent child records.

create trigger {constraint_name}_bdparent_tr before delete on {parent_table}
  for each row
    begin
      select raise(abort, 'delete on table {parent_table} violates foreign key constraint {constraint_name} on {child_table}')
      where (select {child_fkcolumn} from {child_table} where {child_fkcolumn}=old.{parent_key}) is not null;
    end;

💜 On Delete Cascade 💜

Before delete on the parent table delete all dependent child records.

create trigger {constraint_name}_bdparent_tr before delete on {parent_table}
  for each row
    begin
      delete from {child_table} where {child_table}.{child_fkcolumn}=old.{parent_key};
    end;

💜 On Delete Set Null 💜

Before delete on the parent table set the foreign key field(s) in all dependent child records to NULL.

create trigger {constraint_name}_bdparent_tr before delete on {parent_table}
  for each row
    begin
      update {child_table} set {child_table}.{child_fkcolumn}=null where {child_table}.{child_fkcolumn}=old.{parent_key};
    end;

---

👑 TODO 👑

Things that might get added in future versions. Manifesting it ✨🕯️

🍓 Mandatory constraints 🍓

The current foreign key triggers allow NULL in the child table. This might use a keyword in the multiplicity field (perhaps 'required') or could check the 'not null' state of the child fkcolumn.

🍓 Views 🍓

Views haven't been tested. They might already work, but who knows... 🙈

🍓 Other stuff 🍓

Bugs etc. The eternal "other stuff" category, we love her 💅

---

Okay bestie, that's the whole page 🫶 this doc shipped with perl v5.36.0, dated 2022-11-19, and it lives in the User Contributed Perl Documentation. Go forth and make your SQLite 3 schema, you absolute main character 👑💖✨