.\" Automatically generated by Pod::Man 4.09 (Pod::Simple 3.35)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is >0, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.if !\nF .nr F 0
.if \nF>0 \{\
.    de IX
.    tm Index:\\$1\t\\n%\t"\\$2"
..
.    if !\nF==2 \{\
.        nr % 0
.        nr F 2
.    \}
.\}
.\" ========================================================================
.\"
.IX Title "Parse::Dia::SQL::Output::SQLite3 3pm"
.TH Parse::Dia::SQL::Output::SQLite3 3pm "2018-01-01" "perl v5.26.1" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
Parse::Dia::SQL::Output::SQLite3 \- Create SQL for SQLite version 3.
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.Vb 3
\&    use Parse::Dia::SQL;
\&    my $dia = Parse::Dia::SQL\->new(file => \*(Aqfoo.dia\*(Aq, db => \*(Aqsqlite3\*(Aq);
\&    print $dia\->get_sql();
.Ve
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
This sub-class creates \s-1SQL\s0 for the SQLite database version 3.
.SS "new"
.IX Subsection "new"
The constructor.
.PP
Object names in SQLite have no inherent limit. 60 has been arbitrarily chosen.
.SS "_get_create_table_sql"
.IX Subsection "_get_create_table_sql"
Generate create table statement for a single table using SQLite
syntax:
.PP
Includes class comments before the table definition.
.PP
Includes autoupdate triggers based on the class comment.
.PP
\fIautoupdate triggers\fR
.IX Subsection "autoupdate triggers"
.PP
If the class comment includes a line like:
.PP
<autoupdate:\fIfoo\fR/>
.PP
Then an 'after update' trigger is generated for this table which
executes the statement \fIfoo\fR for the updated row.
.PP
Examples of use include tracking record modification dates
(\f(CW\*(C`<autoupdate:dtModified=datetime(\*(Aqnow\*(Aq)/\*(C'\fR>) or deriving a value from
another field (\f(CW\*(C`<autoupdate:sSoundex=soundex(sName)/\*(C'\fR>)
.SS "get_schema_drop"
.IX Subsection "get_schema_drop"
Generate drop table statements for all tables using SQLite syntax:
.PP
.Vb 1
\&  drop table {foo} if exists
.Ve
.SS "get_view_drop"
.IX Subsection "get_view_drop"
Generate drop view statements for all tables using SQLite syntax:
.PP
.Vb 1
\&  drop view {foo} if exists
.Ve
.SS "_get_fk_drop"
.IX Subsection "_get_fk_drop"
Drop foreign key enforcement triggers using SQLite syntax:
.PP
.Vb 1
\&  drop trigger {foo} if exists
.Ve
.PP
The automatically generated foreign key enforcement triggers are:
.PP
See \*(L"_get_create_association_sql\*(R" for more details.
.IP "\fIconstraint_name\fR_bi_tr" 4
.IX Item "constraint_name_bi_tr"
.PD 0
.IP "\fIconstraint_name\fR_bu_tr" 4
.IX Item "constraint_name_bu_tr"
.IP "\fIconstraint_name\fR_buparent_tr" 4
.IX Item "constraint_name_buparent_tr"
.IP "\fIconstraint_name\fR_bdparent_tr" 4
.IX Item "constraint_name_bdparent_tr"
.PD
.SS "_get_drop_index_sql"
.IX Subsection "_get_drop_index_sql"
drop index statement using SQLite syntax:
.PP
.Vb 1
\&  drop index {foo} if exists
.Ve
.SS "get_permissions_create"
.IX Subsection "get_permissions_create"
SQLite doesn't support permissions, so suppress this output.
.SS "get_permissions_drop"
.IX Subsection "get_permissions_drop"
SQLite doesn't support permissions, so suppress this output.
.SS "_get_create_association_sql"
.IX Subsection "_get_create_association_sql"
Create the foreign key enforcement triggers using SQLite syntax:
.PP
.Vb 1
\&  create trigger {fkname}[_bi_tr|_bu_tr|_bdparent_tr|_buparent_tr]
.Ve
.PP
Because SQLite doesn't natively enforce foreign key constraints (see <http://www.sqlite.org/omitted.html>), 
we use triggers to emulate this behaviour.
.PP
The trigger names are the default contraint name (something like \fIchild_table\fR_fk_\fIchild_fkcolumn\fR) with suffixes described below.
.IP "\fI{constraint_name}\fR is the name of the association, either specified or generated." 4
.IX Item "{constraint_name} is the name of the association, either specified or generated."
.PD 0
.IP "\fI{child_table}\fR is the name of the dependent or child table." 4
.IX Item "{child_table} is the name of the dependent or child table."
.IP "\fI{child_fkcolumn}\fR is the field in the dependent table that hold the foreign key." 4
.IX Item "{child_fkcolumn} is the field in the dependent table that hold the foreign key."
.IP "\fI{parent_table}\fR is the name of the parent table." 4
.IX Item "{parent_table} is the name of the parent table."
.IP "\fI{parent_key}\fR is the key field of the parent table." 4
.IX Item "{parent_key} is the key field of the parent table."
.PD
.PP
\fIBefore insert \- Dependent Table\fR
.IX Subsection "Before insert - Dependent Table"
.PP
\&\fIconstraint_name\fR_bi_tr
.PP
Before insert on the child table require that the parent key exists.
.PP
.Vb 7
\&  create trigger {constraint_name}_bi_tr before insert on {child_table}
\&    for each row 
\&      begin 
\&        select 
\&          raise(abort, \*(Aqinsert on table {child_table} violates foreign key constraint {constraint_name}\*(Aq)
\&          where new.{child_fkcolumn} is not null and (select {parent_key} from {parent_table} where {parent_key}=new.{child_fkcolumn}) is null;
\&      end;
.Ve
.PP
\fIBefore update \- Dependent Table\fR
.IX Subsection "Before update - Dependent Table"
.PP
\&\fIconstraint_name\fR_bu_tr
.PP
Before update on the child table require that the parent key exists.
.PP
.Vb 6
\&  create trigger {constraint_name}_bu_tr before update on {table_name} 
\&    for each row 
\&      begin 
\&        select raise(abort, \*(Aqupdate on table {child_table} violates foreign key constraint {constraint_name}\*(Aq) 
\&        where new.{child_fkcolumn} is not null and (select {parent_key} from {parent_table} where {parent_key}=new.{child_fkcolumn}) is null;
\&      end;
.Ve
.PP
\fIBefore update \- Parent Table\fR
.IX Subsection "Before update - Parent Table"
.PP
\&\fIconstraint_name\fR_buparent_tr
.PP
Before update on the primary key of the parent table ensure that there are no dependent child records.
Note that cascading updates \fBdon't work\fR.
.PP
.Vb 6
\&  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, \*(Aqupdate on table {parent_table} violates foreign key constraint {constraint_name} on {child_table}\*(Aq) 
\&        where (select {child_fkcolumn} from {child_table} where {child_fkcolumn}=old.{parent_key}) is not null;
\&      end;
.Ve
.PP
\fIBefore delete \- Parent Table\fR
.IX Subsection "Before delete - Parent Table"
.PP
\&\fIconstraint_name\fR_bdparent_tr
.PP
The default behaviour can be modified through the contraint (in the multiplicity field) of the association.
.PP
Default (On Delete Restrict)
.IX Subsection "Default (On Delete Restrict)"
.PP
Before delete on the parent table ensure that there are no dependent child records.
.PP
.Vb 6
\&  create trigger {constraint_name}_bdparent_tr before delete on {parent_table}
\&    for each row 
\&      begin 
\&        select raise(abort, \*(Aqdelete on table {parent_table} violates foreign key constraint {constraint_name} on {child_table}\*(Aq) 
\&        where (select {child_fkcolumn} from {child_table} where {child_fkcolumn}=old.{parent_key}) is not null;
\&      end;
.Ve
.PP
On Delete Cascade
.IX Subsection "On Delete Cascade"
.PP
Before delete on the parent table delete all dependent child records.
.PP
.Vb 5
\&  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;
.Ve
.PP
On Delete Set Null
.IX Subsection "On Delete Set Null"
.PP
Before delete on the parent table set the foreign key field(s) in all dependent child records to \s-1NULL.\s0
.PP
.Vb 5
\&  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;
.Ve
.SH "TODO"
.IX Header "TODO"
Things that might get added in future versions:
.PP
\fIMandatory constraints\fR
.IX Subsection "Mandatory constraints"
.PP
The current foreign key triggers allow \s-1NULL\s0 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.
.PP
\fIViews\fR
.IX Subsection "Views"
.PP
Views haven't been tested. They might already work, but who knows...
.PP
\fIOther stuff\fR
.IX Subsection "Other stuff"
.PP
Bugs etc