view release on metacpan or  search on metacpan

lib/Quiq/Sql.pm  view on Meta::CPAN

        $constraintName = $table.'_PK';
        $self->checkName(\$constraintName);
    }

    # Statement generieren

    my ($oracle,$postgresql,$sqlite,$mysql) = $self->dbmsTestVector;

    my $stmt;
    if ($oracle || $postgresql) {
        $stmt = sprintf "ALTER TABLE %s ADD\n".
            "    CONSTRAINT %s\n".
            "    PRIMARY KEY (%s)",
            $tableName,$constraintName,join(', ',@$colNameA);

        if ($tableSpace) {
            $stmt .= "\n    USING INDEX TABLESPACE $tableSpace";
        }

        if ($oracle && $exceptionTable) {
            $stmt .= "\n    EXCEPTIONS INTO $exceptionTable";
        }
    }
    else {
        die;
    }

    return $stmt;
}

# -----------------------------------------------------------------------------

=head3 addForeignKeyConstraint() - Generiere FOREIGN KEY Constraint Statement

=head4 Synopsis

  $stmt = $sql->addForeignKeyConstraint($tableName,\@tableCols,
      $refTableName,@opt);

=head4 Options

=over 4

=item -constraintName => $str (Default: <TABLE>_FK_<REFTABLE>)

Name des Constraint.

=item -defer => $bool (Default: 0)

Constraint-Fehler wird verzögert gemeldet.

=item -disable => $bool (Default: 0)

Constraint wird erzeugt, ist aber abgeschaltet.

=item -exceptionTable => $tableName (Default: keiner)

Constraint-Verletzende Datensätze werden in Tabelle $tableName
protokollliert (nur Oracle).

=item -onDelete => 'cascade'|'null' (Default: keiner)

Legt fest, was bei Löschung des Parent-Datensatzes passieren soll.

=item -refTableCols => \@refTableCols (Default: undef)

Liste der Kolumnen in der referenzierten Tabelle.
Bei MySQL müssen die referenzierten Kolumnen aufgezählt werden, auch wenn
ein Primary Key auf der referenzierten Tabelle definiert ist.

=back

=head4 Description

B<Oracle Syntax>

  ALTER TABLE <TABLE_NAME> ADD
      CONSTRAINT <CONSTRAINT_NAME>
      FOREIGN KEY (<TABLE_COLUMNS>)
      REFERENCES <REF_TABLE_NAME>
      ON DELETE <ACTION>
      DEFERRABLE INITIALLY DEFERRED
      EXCEPTIONS INTO <EXCEPTION_TABLE_NAME>
      DISABLE

B<PostgreSQL Syntax>

  ALTER TABLE <TABLE_NAME> ADD
      CONSTRAINT <CONSTRAINT_NAME>
      FOREIGN KEY (<TABLE_COLUMNS>)
      REFERENCES <REF_TABLE_NAME>
      ON DELETE <ACTION>
      DEFERRABLE INITIALLY DEFERRED

B<MySQL Syntax>

  ALTER TABLE <TABLE_NAME> ADD
      CONSTRAINT <CONSTRAINT_NAME>
      FOREIGN KEY (<TABLE_COLUMNS>)
      REFERENCES <REF_TABLE_NAME> (REF_TABLE_COLUMNS)
      ON DELETE <ACTION>

=cut

# -----------------------------------------------------------------------------

sub addForeignKeyConstraint {
    my $self = shift;
    my $fromName = shift;
    my $cols = shift;
    my $toName = shift;

    # Optionen

    my $constraintName = undef;
    my $defer = 0;
    my $disable = 0;
    my $exceptionTable = undef;
    my $onDelete = undef;
    my $refTableColumns = undef;

lib/Quiq/Sql.pm  view on Meta::CPAN

    my $body = Quiq::String->removeIndentation(shift);

    my ($oracle,$postgresql,$sqlite,$mysql) = $self->dbmsTestVector;

    my $stmt;
    if ($postgresql) {
        $stmt = 'CREATE';
        if ($replace) {
            $stmt .= ' OR REPLACE';
        }
        $stmt .= " FUNCTION $signature";
        if ($returns) {
            $stmt .= "\nRETURNS $returns";
        }
        $stmt .= "\nAS \$SQL\$";
        $stmt .= "\n$body";
        $stmt .= "\n\$SQL\$ LANGUAGE plpgsql";
    }
    else {
        $self->throw('Not implemented');
    }

    return $stmt;
}

# -----------------------------------------------------------------------------

=head3 dropFunction() - Generiere Statement zum Entfernen einer Funktion

=head4 Synopsis

  $stmt = $sql->dropFunction($signature);

=head4 Arguments

=over 4

=item $signature

Signatur der Funktion, also Name mit Typ-Parameterliste und ggf.
Schema-Präfix.

=back

=head4 Description

B<PostgreSQL>

  DROP FUNCTION <signature> CASCADE

=cut

# -----------------------------------------------------------------------------

sub dropFunction {
    my $self = shift;
    # @_: $signature,@opt

    # Argumente

    my $cascade = 0;

    Quiq::Option->extract(\@_,
        -cascade => \$cascade,
    );
    my $signature = shift;

    my ($oracle,$postgresql,$sqlite,$mysql) = $self->dbmsTestVector;

    if ($postgresql) {
        my $stmt = "DROP FUNCTION $signature";
        if ($cascade) {
            $stmt .= ' CASCADE';
        }
        return $stmt;
    }

    $self->throw('Not implemented');
}

# -----------------------------------------------------------------------------

=head3 moveFunction() - Bewege Funktion in ein anderes Schema

=head4 Synopsis

  $stmt = $sql->moveFunction($signature,$schema);

=head4 Arguments

=over 4

=item $signature

Signatur der Funktion, also Name mit Typ-Parameterliste und
Schema-Präfix.

=item $schema

Zielschema, in das die Funktion bewegt wird.

=back

=head4 Description

B<PostgreSQL>

  ALTER FUNCTION <signature> SET SCHEMA <schema>

=cut

# -----------------------------------------------------------------------------

sub moveFunction {
    my ($self,$signature,$schema) = @_;

    my ($oracle,$postgresql,$sqlite,$mysql) = $self->dbmsTestVector;

    if ($postgresql) {
        return "ALTER FUNCTION $signature SET SCHEMA $schema";
    }

    $self->throw('Not implemented');
}

# -----------------------------------------------------------------------------

=head2 Trigger

=head3 createTrigger() - Generiere Statement zum Erzeugen eines Triggers

=head4 Synopsis