---
name: "Parse::Dia::SQL::Output::SQLite3"
section: "3pm"
platform: "linux"
package: "libparse-dia-sql-perl"
version: "0.31-2"
release: "Debian 13 (Trixie)"
platforms: ["linux"]
url: "https://new.carta.tech/Parse::Dia::SQL::Output::SQLite3"
---

# Parse::Dia::SQL::Output::SQLite3(3pm)

Create SQL for SQLite version 3.

## NAME

Parse::Dia::SQL::Output::SQLite3 - Create 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

This sub-class creates SQL for the SQLite database version 3.

### new

The constructor.

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

### \_get\_create\_table\_sql

Generate create table statement for a single table using SQLite syntax:

Includes class comments before the table definition.

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)/"`\>)

### 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:

See "\_get\_create\_association\_sql" for more details.

- *constraint\_name*\_bi\_tr

  - *constraint\_name*\_bu\_tr

  - *constraint\_name*\_buparent\_tr

  - *constraint\_name*\_bdparent\_tr

  ### \_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.

### get\_permissions\_drop

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

### \_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.

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**.

```
  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:

*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