diff options
| -rw-r--r-- | contrib/spi/preprocessor/README.MAX | 76 | ||||
| -rw-r--r-- | contrib/spi/preprocessor/example.sql | 37 | ||||
| -rw-r--r-- | contrib/spi/preprocessor/step1.c | 27 | ||||
| -rwxr-xr-x | contrib/spi/preprocessor/step2.pl | 123 |
4 files changed, 0 insertions, 263 deletions
diff --git a/contrib/spi/preprocessor/README.MAX b/contrib/spi/preprocessor/README.MAX deleted file mode 100644 index 7969438c602..00000000000 --- a/contrib/spi/preprocessor/README.MAX +++ /dev/null @@ -1,76 +0,0 @@ - -Here are general trigger functions provided as workable examples -of using SPI and triggers. "General" means that functions may be -used for defining triggers for any tables but you have to specify -table/field names (as described below) while creating a trigger. - -1. refint.c - functions for implementing referential integrity. - -check_primary_key () is to used for foreign keys of a table. - - You are to create trigger (BEFORE INSERT OR UPDATE) using this -function on a table referencing another table. You are to specify -as function arguments: triggered table column names which correspond -to foreign key, referenced table name and column names in referenced -table which correspond to primary/unique key. - You may create as many triggers as you need - one trigger for -one reference. - -check_foreign_key () is to used for primary/unique keys of a table. - - You are to create trigger (BEFORE DELETE OR UPDATE) using this -function on a table referenced by another table(s). You are to specify -as function arguments: number of references for which function has to -performe checking, action if referencing key found ('cascade' - to delete -corresponding foreign key, 'restrict' - to abort transaction if foreign keys -exist, 'setnull' - to set foreign key referencing primary/unique key -being deleted to null), triggered table column names which correspond -to primary/unique key, referencing table name and column names corresponding -to foreign key (, ... - as many referencing tables/keys as specified -by first argument). - Note, that NOT NULL constraint and unique index have to be defined by -youself. - - There are examples in refint.example and regression tests -(sql/triggers.sql). - - To CREATE FUNCTIONs use refint.sql (will be made by gmake from -refint.source). - -# Excuse me for my bad english. Massimo Lambertini -# -# -# New check foreign key -# -I think that cascade mode is to be considered like that the operation over -main table is to be made also in referenced table . -When i Delete , i must delete from referenced table , -but when i update , i update referenced table and not delete like unmodified refint.c . - -I made a patch that when i update it check the type of modified key ( if is a text , char() i -added '') and then create a update query that do the right think . - -For my point of view that policy is helpfull because i do not have in referenced table -loss of information . - - -In preprocessor subdir i have placed a little utility that from a SQL92 table definition, -it create all trigger for foreign key . - - -the schema that i use to analyze the problem is this - -create table -A -( key int4 not null primary key ,...) ; -create table -REFERENCED_B -( key int 4 , ... , -foreign key ( key ) references A -- -); - - - - - - diff --git a/contrib/spi/preprocessor/example.sql b/contrib/spi/preprocessor/example.sql deleted file mode 100644 index e53c84a1517..00000000000 --- a/contrib/spi/preprocessor/example.sql +++ /dev/null @@ -1,37 +0,0 @@ --- Note the syntax is strict because i have no time to write better perl filter. --- --- [blank] is 1 blank --- at the end of an interesting line must be a [,] or [--] --- [ending] must be a , or -- --- --- foreign[blank]key[blank]([blank]keyname,..,keyname[blank])[blank]references[blank]table[blank][ending] --- --- step1 < example.sql | step2.pl > foreign_key_triggers.sql --- --- step1.c is a simple program that UPPERCASE ALL . I know that is simple implementing in Perl --- bu i haven't time - - -CREATE TABLE -gruppo -( -codice_gruppo int4 NOT NULL, -descrizione varchar(32) NOT NULL -primary key ( codice_gruppo ) - -) ; - --- --- fa_parte : Appartenenza di una Azienda Conatto o Cliente ad un certo GRUPPO --- - -CREATE TABLE -fa_parte -( -codice_gruppo int4 NOT NULL, -codice_contatto int4 NOT NULL, - -primary key ( codice_gruppo,codice_contatto ) , -foreign key ( codice_gruppo ) references gruppo -- -); - diff --git a/contrib/spi/preprocessor/step1.c b/contrib/spi/preprocessor/step1.c deleted file mode 100644 index 8a5379e8e04..00000000000 --- a/contrib/spi/preprocessor/step1.c +++ /dev/null @@ -1,27 +0,0 @@ -#include <stdio.h> - -char * -strtoupper(char *string) -{ - int i; - - for (i = 0; i < strlen(string); i++) - string[i] = toupper((unsigned char) string[i]); - return string; -} - - - -void -main(char argc, char **argv) -{ - char str[250]; - int sw = 0; - - while (fgets(str, 240, stdin)) - { - if (sw == 0) - printf("%s", strtoupper(str)); - } - -} diff --git a/contrib/spi/preprocessor/step2.pl b/contrib/spi/preprocessor/step2.pl deleted file mode 100755 index 76ce7944cc2..00000000000 --- a/contrib/spi/preprocessor/step2.pl +++ /dev/null @@ -1,123 +0,0 @@ -#!/usr/bin/perl - -## -## MAIN -## -$table_name=""; -$old_name=""; -$references_table=""; -$references_column=""; -$is_create=0; - - - -while ( <> ) -{ - chop; - $str=$_ ; - - if ($is_create == 1) { - $table_name=$str; - $is_create=2; - } - if ( $str =~ /^CREATE TABLE/ ){ - $is_create=1; - } - if ($is_create == 2) { - if ($str =~ /^FOREIGN KEY/){ - ($d1,$d2,$d3,$columns,$d4,$d5,$references_table,$d6) = split (/ /,$str,8); - #printf "Table $table_name $columns $references_table\n"; - - if ($table_name ne $old_name ){ - printf "--\n-- Trigger for $table_name\n--\n\n"; - } - - foreach $i ( split(/,/ , $columns ) ){ - print "CREATE INDEX I_$table_name"; - print "_$i ON $table_name ( $i ) ;\n"; - } - - printf "\nCREATE TRIGGER T_P_$table_name"; - printf "_$references_table BEFORE INSERT OR UPDATE ON $table_name FOR EACH ROW\n" ; - printf "EXECUTE PROCEDURE\n"; - printf "check_primary_key("; - $val=0; - foreach $i ( split(/,/ , $columns ) ){ - print "'$i',"; - $val=$val+1 ; - } - print "'$references_table',"; - - $t=1; - foreach $i ( split(/,/,$columns ) ){ - print "'$i'"; - if ( $t < $val ) { - printf ","; - } - $t=$t+1; - } - print " );\n\n"; - - printf "CREATE TRIGGER T_F_D_$references_table"; - printf "_$table_name BEFORE DELETE ON $references_table FOR EACH ROW\n" ; - printf "EXECUTE PROCEDURE\n"; - printf "check_foreign_key(1,'cascade',"; - $val=0; - foreach $i ( split(/,/ , $columns ) ){ - print "'$i',"; - $val=$val+1 ; - } - print "'$table_name',"; - - $t=1; - foreach $i ( split(/,/,$columns ) ){ - print "'$i'"; - if ( $t < $val ) { - printf ","; - } - $t=$t+1; - } - print " );\n\n"; - - printf "CREATE TRIGGER T_F_U_$references_table"; - printf "_$table_name AFTER UPDATE ON $references_table FOR EACH ROW\n" ; - printf "EXECUTE PROCEDURE\n"; - printf "check_foreign_key(1,'cascade',"; - $val=0; - foreach $i ( split(/,/ , $columns ) ){ - print "'$i',"; - $val=$val+1 ; - } - print "'$table_name',"; - - $t=1; - foreach $i ( split(/,/,$columns ) ){ - print "'$i'"; - if ( $t < $val ) { - printf ","; - } - $t=$t+1; - } - print " );\n\n"; - - if ($table_name ne $old_name ){ - printf "-- ********************************\n\n\n"; - } - $old_name=$table_name ; - - - - - } - } - if ($str =~ /^\)\;/ ) { - $is_create = 0 ; - } - -} - - - - - - |