File: nielsen.html

package info (click to toggle)
lg-issue69 2-1
  • links: PTS
  • area: main
  • in suites: sarge
  • size: 1,996 kB
  • ctags: 141
  • sloc: perl: 131; sh: 59; sql: 49; makefile: 45
file content (1061 lines) | stat: -rw-r--r-- 40,847 bytes parent folder | download
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
<!--startcut  ==============================================-->
<!-- *** BEGIN HTML header *** -->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML><HEAD>
<title>Combining Perl and PostgreSQL, Part 2: Procedures with PL/pgSQL LG #69</title>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#0000AF"
ALINK="#FF0000">
<!-- *** END HTML header *** -->

<CENTER>
<A HREF="http://www.linuxgazette.com/">
<IMG ALT="LINUX GAZETTE" SRC="../gx/lglogo.png" 
	WIDTH="600" HEIGHT="124" border="0"></A> 
<BR>

<!-- *** BEGIN navbar *** -->
<IMG ALT="" SRC="../gx/navbar/left.jpg" WIDTH="14" HEIGHT="45" BORDER="0" ALIGN="bottom"><A HREF="mathew.html"><IMG ALT="[ Prev ]" SRC="../gx/navbar/prev.jpg" WIDTH="16" HEIGHT="45" BORDER="0" ALIGN="bottom"></A><A HREF="index.html"><IMG ALT="[ Table of Contents ]" SRC="../gx/navbar/toc.jpg" WIDTH="220" HEIGHT="45" BORDER="0" ALIGN="bottom" ></A><A HREF="../index.html"><IMG ALT="[ Front Page ]" SRC="../gx/navbar/frontpage.jpg" WIDTH="137" HEIGHT="45" BORDER="0" ALIGN="bottom"></A><A HREF="http://www.linuxgazette.com/cgi-bin/talkback/all.py?site=LG&article=http://www.linuxgazette.com/issue69/nielsen.html"><IMG ALT="[ Talkback ]" SRC="../gx/navbar/talkback.jpg" WIDTH="121" HEIGHT="45" BORDER="0" ALIGN="bottom"  ></A><A HREF="../faq/index.html"><IMG ALT="[ FAQ ]" SRC="./../gx/navbar/faq.jpg"WIDTH="62" HEIGHT="45" BORDER="0" ALIGN="bottom"></A><A HREF="okopnik.html"><IMG ALT="[ Next ]" SRC="../gx/navbar/next.jpg" WIDTH="15" HEIGHT="45" BORDER="0" ALIGN="bottom"  ></A><IMG ALT="" SRC="../gx/navbar/right.jpg" WIDTH="15" HEIGHT="45" ALIGN="bottom">
<!-- *** END navbar *** -->
<P>
</CENTER>

<!--endcut ============================================================-->

<H4 ALIGN="center">
"Linux Gazette...<I>making Linux just a little more fun!</I>"
</H4>

<P> <HR> <P> 
<!--===================================================================-->

<center>
<H1><font color="maroon">Combining Perl and PostgreSQL, Part 2: Procedures with PL/pgSQL</font></H1>
<H4>By <a href="mailto:articles@gnujobs.com">Mark Nielsen</a></H4>
</center>
<P> <HR> <P>  

<!-- END header -->




<p>
<ol>
<li>
<a href="#Introduction">Introduction</a></li>
<li><a href="#perl">Perl script to create tables, procedures, 
backup tables, and sequences. 
</a></li>
<li><a href="#execute">Executing the Perl script</a></li>
<li><a href="#consider">Considerations to explore.</a></li>
<li><a href="#Conclusion">Conclusion</a></li>
<li><a href="#REF">References</a></li>
</ol>

<h3><a NAME="Introduction"></a>Introduction</h3>
After dealing with installing PostgreSQL, Perl, and embedding Perl in
PostgreSQL, I wanted a standard way to create tables, sequences, 
stored procedures, and backup tables. Perhaps other people have nice GUI
solutions to do this, but I haven't seen any. I would like it if someone
would work with me to create a GUI interface to achieve what I am doing here. 
My goals are:
<ol>
<li> Always backup data no matter what happens. </li>
<li> Always use stored procedures to insert, update, delete, copy, or
  to do anything that changes data on the tables. One should even create
  stored procedures to select data. </li>
<li> Have a Perl script create tables, sequences, backups tables, and the
  stored procedures to manipulate the data. </li>
<li>Have the stored procedures clean data using Perl. </li> 
<li>Backup data if someone runs the Perl script on a live system.</li>
<li> There should be an active column in the table so that you can specify
active or inactive rows in the table. We create a view which views 
active rows of a table. </li>
<li> Stored procedures should record date created and date last updated. 
</li>
<li>All rows have a unique id. Even if we choose not to use them, 
they will still have them. It is not always good to use oid to get
unique rows. </li>
<li> Be able to delete inactive rows 
with a purge procedure. The delete procedure
just makes it inactive. Also, unpurge data with the latest purged
data for a unique id. This
is cool.</li> 
<li> All negative numbers returned from pl/sql procedures are considered
failures. All positive numbers (including 0) are considered to be
successes in the fact nothing errored out. They are either 0, in which
nothing happened, or something greater than 0 which indicates the
number of items affected or a id number. </li>
</ol>
My future goals include:
<ol>
<li> Creating a GUI interface. Preferrably one that is not dependent on GNOME
or KDE libraries but Python. You can create Python binaries easily, so I 
would prefer Python/TK.</li>
<li>Allow the GUI design to make changes to live tables by either:
  <ul>
  <li>Making updates that really happen with full effects. Some changes
   con't allow all options (at least in the past). </li>
  <li>Creating a new table, and dumping all the data from the old table
  into the new one while locking the old table.  </li>
  </ul>
</li>
<li>Record all database changes to review history.</li>
</ol>

<h3><a NAME="perl"></a>Perl script to create tables, procedures,
backup tables, and sequences.</h3>
Here is the Perl script I use. You can also get a copy here
<a href="misc/nielsen/Create_Functions.pl.txt">Create_Functions.pl.txt</a>.
<pre>
#!/usr/bin/perl

#              Create Functions for Perl/PostgreSQL version 0.1

#                       Copyright 2001, Mark Nielsen
#                            All rights reserved.
#    This Copyright notice was copied and modified from the Perl 
#    Copyright notice. 
#    This program is free software; you can redistribute it and/or modify
#    it under the terms of either:

#        a) the GNU General Public License as published by the Free
#        Software Foundation; either version 1, or (at your option) any
#        later version, or

#        b) the "Artistic License" which comes with this Kit.

#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See either
#    the GNU General Public License or the Artistic License for more details.

#    You should have received a copy of the Artistic License with this
#    Kit, in the file named "Artistic".  If not, I'll be glad to provide one.

#    You should also have received a copy of the GNU General Public License
#   along with this program in the file named "Copying". If not, write to the 
#   Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 
#    02111-1307, USA or visit their web page on the internet at
#    http://www.gnu.org/copyleft/gpl.html.

use strict;

  ### We want to define some variables WHICH YOU SHOULD CHANGE FOR YOUR
  ### OWN COMPUTER. 
my $Home = "/tmp/testdir";
my $File = "$Home/Tables.txt";
my $Template = "$Home/Generic.fun";
my $Custom = "$Home/Custom.sql";
my $Database = "testdatabase";
 
#------------------------------------------------------------------------

my @List = @ARGV;

  ## Let us create the two directories we need if they are not there. 
if (!(-e "$Home/Tables")) {system "mkdir -p $Home/Tables"}
if (!(-e "$Home/Backups")) {system "mkdir -p $Home/Backups"}

  ### Open up the template for the functions and the file that contains
  ### the info to create the tables. 
open(FILE,$Template); my @Template = &lt;FILE&gt;; close FILE;
open(FILE,$File); my @File = &lt;FILE&gt;; close FILE;
open(FILE,$Custom); my @Custom = &lt;FILE&gt;; close FILE;

  ### Filter out lines that have no numbers or letters. 
@File = grep($_ =~ /[a-z0-9]/i, @File);
  ### Get rid of any line which contains a #
@File = grep(!($_ =~ /\#/), @File);
  ### Get rid of the newline. 
grep(chomp $_, @File);
  ### Get rid of tabs and replace with spaces. . 
grep($_ =~ s/\t/ /g, @File);
  ### Convert all multiple spaces to one.  
grep($_ =~ s/  +/ /g, @File);
  ### Next two lines get rid of spaces and front and end.  
grep($_ =~ s/^ //g, @File);
grep($_ =~ s/ $//g, @File);
  ### Delete any commas at the end, we will put them back on later. 
grep($_ =~ s/\,$//g, @File);

my $Tables = {};
my $TableName = "";
  ### For each line in the file, either make a new array for the table, 
  ### or store the lines in the array for a table.  
foreach my $Line (@File)  
  {
  my $Junk = "";
    ### If the line starts with "TABLENAME" then create a new array. 
  if ($Line =~ /^TABLENAME/) 
    {
    ($Junk,$TableName, $Junk) = split(/ /,$Line);
       ### This creates the aray for the table. 
    $Tables-&gt;{$TableName} = [];
    }
  else 
    {
       ### Storing lines for the table. 
    push (@{$Tables-&gt;{$TableName}}, $Line) ;
    }
  }

    ### If we listed specific tables, then only do those. 
  if (@List) 
    {
    foreach my $TableName (sort keys %$Tables)
      { if (!(grep($_ eq $TableName, @List))) {delete $Tables-&gt;{$TableName};} }
    }

  ### Get the keys of the reference to an array $Tables
  ### and get the data for that array, create our file, and then use the file. 
foreach my $TableName (sort keys %$Tables) 
  {
  my @Temp = @{$Tables-&gt;{$TableName}};

  my $Backup_Columns = "";  my $Backup_Values = ""; my $Update_Fields = "";
  my $Field_Copy_Values = "";  my $FieldTypes = "";
  my $CleanVariables = ""; my $RemakeVariables = ""; 
    ### The two tables are different in one respect, the backup table 
    ### does not require uniqueness and it doesn't use a sequence.  
  my $Table = qq($TableName\_id int4 NOT NULL UNIQUE DEFAULT nextval('$TableName\_sequence'),
    date_updated  timestamp NOT NULL default CURRENT_TIMESTAMP,
    date_created  timestamp NOT NULL default CURRENT_TIMESTAMP,
    active int2 CHECK (active in (0,1)) DEFAULT 0,
);
    ## I should allow null for the id instead of 0, but since the sequence
    ### starts at 1, I use 0 as null. I hate nulls.  
  my $Table_Backup = qq(backup_id int4 NOT NULL UNIQUE DEFAULT nextval('$TableName\_sequence_backup'), 
    $TableName\_id int4 NOT NULL DEFAULT 0,
    date_updated  timestamp NOT NULL default CURRENT_TIMESTAMP,
    date_created  timestamp NOT NULL default CURRENT_TIMESTAMP,
    active int2 CHECK (active in (0,1)) DEFAULT 0,
    );

  print "Creating functions for table '$TableName'\n";
  my $No = 1;
    ### For each line for this table do this. 
    ### We want to create a few variables that are going to be placed into
    ### the template.  
  foreach my $Line (@Temp) 
    {
    $Table .= "$Line,\n";
    $Table_Backup .= "$Line,\n";
    my ($Name,$Type,$Ext) = split(/ /,$Line,3);
      ### The backup columns
    $Backup_Columns .= ", $Name"; 
      ### The update fields
    $No++; $Update_Fields .= ", $Name = var_$No"; 
      ### Backup values
    $Backup_Values .= ", record_backup.$Name";
      ### Now the fields when we copy stuff in the cyop function. 
    $Field_Copy_Values .= ", clean_text(record2.$Name)";
      ### Now the field types for the update function. 
    $FieldTypes .= ", $Type";
      ### We need to define the variables for the updating function.
    $CleanVariables .= "          var_$No $Type;\n";
      ### We need to define the type, I only check for text and int4 for now. 
    my $Temp = "\$$No";  
    if ($Type eq "int4") {$Temp = "clean_numeric($Temp)";}
    elsif  ($Type eq "text") {$Temp = "clean_text($Temp)";}
      ### Now we need to set the variables. 
    $RemakeVariables .= "         var_$No := $Temp;\n";

     ### We also need to add the function to clean out he variables before
     ### they are submitted. 
    }
   ### Record how many rows we had. Make a line for the update command for
   ### testing. 
  my $Number_Of_Rows = $No;
  my $Update_Test = "1";
  for (my $i = 1; $i &lt; $Number_Of_Rows - 1; $i++) {$Update_Test .= ",$i";}

    ### We need to chop off the last comma. 
  chomp $Table; chop $Table; chomp $Table_Backup; chop $Table_Backup;
    ### Now let us setup dropping and creating of the table and backup table. 
  my $Tables = qq(drop table $TableName;\ncreate table $TableName (\n$Table\n);); 
  $Tables .= "drop table $TableName\_backup;\n";
  $Tables .= "create table $TableName\_backup (\n$Table_Backup, error_code text NOT NULL DEFAULT ''\n);\n";
    ### Let us create a view for active stuff in our table. 
  $Tables .= "drop view $TableName\_active;\n";
  $Tables .= "create view $TableName\_active as select * from $TableName
        where active = 1;\n";
    ### Create a view for inactive or deleted items. 
  $Tables .= "drop view $TableName\_deleted;\n";
  $Tables .= "create view $TableName\_deleted as select * from $TableName
        where active = 0;\n";
    ### Create a view for a list of unique backup ids. 
  $Tables .= "drop view $TableName\_backup_ids;\n";
  $Tables .= "create view $TableName\_backup_ids as 
           select distinct $TableName\_id from $TableName\_backup;\n";
    ### Create a list of purged data (lastest data per id).  
  $Tables .= "drop view $TableName\_purged;\n";
  $Tables .= "create view $TableName\_purged as
   select * from $TableName\_backup where oid = ANY (
     select max(oid) from $TableName\_backup where $TableName\_id = ANY
        (
        select distinct $TableName\_id from $TableName\_backup
          where $TableName\_backup.error_code = 'purge'
           and NOT $TableName\_id = ANY (select $TableName\_id from $TableName)
        )
        group by $TableName\_id
     )
    ;\n";

     ### I use grep commands to search and replace stuff for arrays.
     ### I could use map, but I like greps.  
  my @Temp = @Template;
     ### now add the custom sql commands. 
  push (@Temp,@Custom);

  grep($_ =~ s/TABLENAME/$TableName/g, @Temp);
  grep($_ =~ s/BACKUPCOLUMNS/$Backup_Columns/g, @Temp);
  grep($_ =~ s/BACKUPVALUES/$Backup_Values/g, @Temp);
  grep($_ =~ s/UPDATEFIELDS/$Update_Fields/g, @Temp);
  grep($_ =~ s/COPYFIELDS/$Field_Copy_Values/g, @Temp);
  grep($_ =~ s/FIELDS/$FieldTypes/g, @Temp);
  grep($_ =~ s/HOME/$Home/g, @Temp);
  grep($_ =~ s/CLEANVARIABLES/$CleanVariables/g, @Temp);
  grep($_ =~ s/REMAKEVARIABLES/$RemakeVariables/g, @Temp);

    ### Now move the stuff from the array @Temp to @Template_Copy.
  my @Template_Copy = @Temp;

    ### Now we save the file. We won't delete it (unless you run this script
    ### again) so that we can figure out what was done.  
  open(FILE,"&gt;$Home/Tables/$TableName\.table_functions");
    ### Create the sequence for the table . 
  print FILE "drop sequence $TableName\_sequence;\n";
  print FILE "create sequence $TableName\_sequence;\n";
  print FILE "drop sequence $TableName\_sequence_backup;\n";
  print FILE "create sequence $TableName\_sequence_backup;\n";
    ### Print out the table and backup table.   
  print FILE $Tables;
    ### Print out the 4 functions, insert, delete, update, and copy. 
  foreach my $Temp (@Template_Copy) {print FILE "$Temp";} 

  close FILE;

    ### Before we execute, let us backup the table in case some novice 
    ### executes this on a live server. 
  my $Backup_File = "$Home/Backups/$TableName\_0.backup";
  my $No = 0;
  while (-e $Backup_File)
    {$No++; $Backup_File = "$Home/Backups/$TableName\_$No\.backup";} 
    ### Now we have the filename to store the backup, execute it. 
  system ("pg_dump -t $TableName -f $Backup_File $Database");
  
  ### Uncomment this option if you want to see what is in the file. 
##  system ("cat $Home/Tables/$TableName\.table_functions");

    ### Drop table and functions, create table and functions and backup table. 
  system ("psql -d $Database -c '\\i $Home/Tables/$TableName\.table_functions'");
  print "Check the file\n $Home/Tables/$TableName\.table_functions.\n";

  } 



</pre>
Rename the perl script "Create_Functions.pl.txt". Here are the things
needed to get it to work:
<ul>
<li>You must change the options above the dotted line. </li>
<li> You must have PostgreSQL installed with Perl embedded in it. 
<li>Enter the command "psql template1". Then type "create database
testdatabase;" or whatever you named your database. Press enter. If you have
errors along the way, you haven't set up the permissions yet. Login in as
root, then execute "su -l postgres". Then type "createuser" and press
enter. This will create a user in your postgresql database. Enter in the
username and give the username full privledges. Then try again 
with your normal account. </li> 
</ul>

<h3><a NAME="execute"></a>Executing the Perl script </h3>
You will need some more files. The <a href="misc/nielsen/Tables.txt">Tables.txt</a> file.  
<pre>

TABLENAME contact
question_id int4 NOT NULL DEFAULT 0
company_name text NOT NULL default ''
first  text NOT NULL default ''
middle text NOT NULL default ''
last text NOT NULL default ''
email  text NOT NULL default ''
work_phone text NOT NULL default ''
home_phone text NOT NULL default ''
address_1 text NOT NULL default '',
address_2 text NOT NULL default ''
city text NOT NULL default ''
state text NOT NULL default ''
zip text NOT NULL default ''

TABLENAME account
username text NOT NULL DEFAULT '',
password text not NULL DEFAULT '',

TABLENAME contact_lists
account_id int4 not null default 0,
contact_id int4 not null default 0,
</pre>
You can use my file as an example, but I suggest to modify it for your
own needs. It is simulated to make three tables. One containing userame
and passwords, and the other associating a username to a list of
contacts.  Another file you will need is <a href="misc/nielsen/Generic.fun">Generic.fun</a>
<pre>
---              Generic Functions for Perl/Postgresql version 0.1

---                       Copyright 2001, Mark Nielsen
---                            All rights reserved.
---    This Copyright notice was copied and modified from the Perl 
---    Copyright notice. 
---    This program is free software; you can redistribute it and/or modify
---    it under the terms of either:

---        a) the GNU General Public License as published by the Free
---        Software Foundation; either version 1, or (at your option) any
---        later version, or

---        b) the "Artistic License" which comes with this Kit.

---    This program is distributed in the hope that it will be useful,
---    but WITHOUT ANY WARRANTY; without even the implied warranty of
---    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See either
---    the GNU General Public License or the Artistic License for more details.

---    You should have received a copy of the Artistic License with this
---    Kit, in the file named "Artistic".  If not, I'll be glad to provide one.

---    You should also have received a copy of the GNU General Public License
---   along with this program in the file named "Copying". If not, write to the 
---   Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 
---    02111-1307, USA or visit their web page on the internet at
---    http://www.gnu.org/copyleft/gpl.html.

-- create a method to unpurge just one item.  
-- create a method to purge one item. 
--  \i HOME/TABLENAME.table
---------------------------------------------------------------------

drop function sql_TABLENAME_insert ();
CREATE FUNCTION sql_TABLENAME_insert () RETURNS int4 AS '
DECLARE
    record1 record;  oid1 int4; id int4 :=0; record_backup RECORD;
BEGIN
   insert into TABLENAME (date_updated, date_created, active)
        values (CURRENT_TIMESTAMP,CURRENT_TIMESTAMP, 1);
     -- Get the unique oid of the row just inserted. 
   GET DIAGNOSTICS oid1 = RESULT_OID;
     -- Get the TABLENAME id. 
   FOR record1 IN SELECT TABLENAME_id FROM TABLENAME where oid = oid1
      LOOP
      id := record1.TABLENAME_id;
   END LOOP;
   
     -- If id is NULL, insert failed or something is wrong.
   IF id is NULL THEN return (-1); END IF;
     -- It should also be greater than 0, otherwise something is wrong.
   IF id &lt; 1 THEN return (-2); END IF;

      -- Now backup the data. 
    FOR record_backup IN SELECT * FROM TABLENAME where TABLENAME_id = id
       LOOP
       insert into TABLENAME_backup (TABLENAME_id, date_updated, date_created, 
           active, error_code) 
         values (id, record_backup.date_updated, record_backup.date_created,
            record_backup.active, ''insert'');
    END LOOP;

     -- Everything has passed, return id as TABLENAME_id.
   return (id);
END;
' LANGUAGE 'plpgsql';
---------------------------------------------------------------------

drop function sql_TABLENAME_delete (int4);
CREATE FUNCTION sql_TABLENAME_delete (int4) RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record1 RECORD; 
    record_backup RECORD;
    return_int4 int4 :=0;

BEGIN
     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id &lt; 1 THEN return -1; END IF;

     -- If we find the id, set active = 0. 
   FOR record1 IN SELECT TABLENAME_id FROM TABLENAME 
          where TABLENAME_id = id
      LOOP
      update TABLENAME set active=0, date_updated = CURRENT_TIMESTAMP
           where TABLENAME_id = id;  
      GET DIAGNOSTICS return_int4 = ROW_COUNT;       
      id_exists := 1;
   END LOOP;
      
     -- If we did not find the id, abort and return -2.  
   IF id_exists = 0 THEN return (-2); END IF;

   FOR record_backup IN SELECT * FROM TABLENAME where TABLENAME_id = id
      LOOP
      insert into TABLENAME_backup (TABLENAME_id, date_updated, date_created,
          active BACKUPCOLUMNS ,error_code)
           values (record_backup.TABLENAME_id, record_backup.date_updated, 
             record_backup.date_updated, record_backup.active
             BACKUPVALUES , ''delete''
      );
   END LOOP;

     -- If id_exists == 0, Return error.
     -- It means it never existed. 
   IF id_exists = 0 THEN return (-1); END IF;

     -- We got this far, it must be true, return ROW_COUNT.   
   return (return_int4);
END;
' LANGUAGE 'plpgsql';

---------------------------------------------------------------------
drop function sql_TABLENAME_update (int4 FIELDS);
CREATE FUNCTION sql_TABLENAME_update  (int4 FIELDS) 
  RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record_update RECORD; record_backup RECORD;
    return_int4 int4 :=0;
    CLEANVARIABLES
BEGIN
    REMAKEVARIABLES
     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id &lt; 1 THEN return -1; END IF;

   FOR record_update IN SELECT TABLENAME_id FROM TABLENAME
         where TABLENAME_id = id
      LOOP
      id_exists := 1;
   END LOOP;

   IF id_exists = 0 THEN return (-2); END IF;

   update TABLENAME set date_updated = CURRENT_TIMESTAMP
      UPDATEFIELDS 
        where TABLENAME_id = id;
   GET DIAGNOSTICS return_int4 = ROW_COUNT;

   FOR record_backup IN SELECT * FROM TABLENAME where TABLENAME_id = id
      LOOP
     insert into TABLENAME_backup (TABLENAME_id,
         date_updated, date_created, active
         BACKUPCOLUMNS, error_code)
       values (record_update.TABLENAME_id, record_backup.date_updated,
         record_backup.date_updated, record_backup.active
         BACKUPVALUES, ''update''
      );
   END LOOP;

     -- We got this far, it must be true, return ROW_COUNT.   
   return (return_int4);
END;
' LANGUAGE 'plpgsql';
---------------------------------------------------------------------

drop function sql_TABLENAME_copy (int4);
CREATE FUNCTION sql_TABLENAME_copy (int4) 
  RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record1 RECORD; record2 RECORD; record3 RECORD;    
    return_int4 int4 := 0;
    id_new int4 := 0;
    TABLENAME_new int4 :=0;
BEGIN
     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id &lt; 1 THEN return -1; END IF;

   FOR record1 IN SELECT TABLENAME_id FROM TABLENAME where TABLENAME_id = id
      LOOP
      id_exists := 1;
   END LOOP;
   IF id_exists = 0 THEN return (-2); END IF;

     --- Get the new id
   FOR record1 IN SELECT sql_TABLENAME_insert() as TABLENAME_insert
      LOOP
      TABLENAME_new := record1.TABLENAME_insert;
   END LOOP;
     -- If the TABLENAME_new is not greater than 0, return error.
   IF TABLENAME_new &lt; 1 THEN return -3; END IF;

   FOR record2 IN SELECT * FROM TABLENAME where TABLENAME_id = id
      LOOP

     FOR record1 IN SELECT sql_TABLENAME_update(TABLENAME_new COPYFIELDS)
        as TABLENAME_insert
      LOOP
        -- execute some arbitrary command just to get it to pass. 
      id_exists := 1;
     END LOOP;
   END LOOP;

     -- We got this far, it must be true, return new id.   
   return (TABLENAME_new);
END;
' LANGUAGE 'plpgsql';

------------------------------------------------------------------
drop function sql_TABLENAME_purge ();
CREATE FUNCTION sql_TABLENAME_purge () RETURNS int4 AS '
DECLARE
    record_backup RECORD; oid1 int4 := 0;
    return_int4 int4 :=0;
    deleted int4 := 0;
    delete_count int4 :=0;
    delete_id int4;

BEGIN 

     -- Now delete one by one. 
   FOR record_backup IN SELECT * FROM TABLENAME where active = 0
      LOOP
         -- Record the id we want to delete. 
      delete_id = record_backup.TABLENAME_id;

      insert into TABLENAME_backup (TABLENAME_id, date_updated, date_created,
          active BACKUPCOLUMNS ,error_code)
           values (record_backup.TABLENAME_id, record_backup.date_updated, 
             record_backup.date_updated, record_backup.active
             BACKUPVALUES , ''purge''
          );

        -- Get the unique oid of the row just inserted. 
      GET DIAGNOSTICS oid1 = RESULT_OID;

        -- If oid1 less than 1, return -1
      IF oid1 &lt; 1 THEN return (-2); END IF;
        -- Now delete this from the main table.   
      delete from TABLENAME where TABLENAME_id = delete_id;

        -- Get row count of row just deleted, should be 1. 
      GET DIAGNOSTICS deleted = ROW_COUNT;
        -- If deleted less than 1, return -3
      IF deleted &lt; 1 THEN return (-3); END IF;
      delete_count := delete_count + 1;

    END LOOP;

     -- We got this far, it must be true, return the number of ones we had.  
   return (delete_count);
END;
' LANGUAGE 'plpgsql';

------------------------------------------------------------------
drop function sql_TABLENAME_purgeone (int4);
CREATE FUNCTION sql_TABLENAME_purgeone (int4) RETURNS int4 AS '
DECLARE
    record_backup RECORD; oid1 int4 := 0;
    record1 RECORD;
    return_int4 int4 :=0;
    deleted int4 := 0;
    delete_count int4 :=0;
    delete_id int4;
    purged_no int4 := 0;

BEGIN

    delete_id := $1;
        -- If purged_id less than 1, return -4
    IF delete_id &lt; 1 THEN return (-4); END IF;

   FOR record1 IN SELECT * FROM TABLENAME 
      where active = 0 and TABLENAME_id = delete_id 
      LOOP
      purged_no := purged_no + 1;
   END LOOP;

        -- If purged_no less than 1, return -1
   IF purged_no &lt; 1 THEN return (-1); END IF;

     -- Now delete one by one.
   FOR record_backup IN SELECT * FROM TABLENAME where TABLENAME_id = delete_id
      LOOP

      insert into TABLENAME_backup (TABLENAME_id, date_updated, date_created,
          active BACKUPCOLUMNS ,error_code)
           values (record_backup.TABLENAME_id, record_backup.date_updated,
             record_backup.date_updated, record_backup.active
             BACKUPVALUES , ''purgeone''
          );

        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;

        -- If oid1 less than 1, return -2
      IF oid1 &lt; 1 THEN return (-2); END IF;
        -- Now delete this from the main table.
      delete from TABLENAME where TABLENAME_id = delete_id;

        -- Get row count of row just deleted, should be 1.
      GET DIAGNOSTICS deleted = ROW_COUNT;
        -- If deleted less than 1, return -3
      IF deleted &lt; 1 THEN return (-3); END IF;
      delete_count := delete_count + 1;

    END LOOP;

     -- We got this far, it must be true, return the number of ones we had.
   return (delete_count);
END;
' LANGUAGE 'plpgsql';

------------------------------------------------------------------------
drop function sql_TABLENAME_unpurge ();
CREATE FUNCTION sql_TABLENAME_unpurge () RETURNS int2 AS '
DECLARE
    record1 RECORD;
    record2 RECORD; 
    record_backup RECORD;
    purged_id int4 := 0;
    purge_count int4 :=0;
    timestamp1 timestamp;
    purged_no int4 := 0;
    oid1 int4 := 0;
    oid_found int4 := 0;
    highest_oid int4 := 0;

BEGIN

     -- Now get the unique ids that were purged. 
   FOR record1 IN select distinct TABLENAME_id from TABLENAME_backup 
       where TABLENAME_backup.error_code = ''purge''
          and NOT TABLENAME_id = ANY (select TABLENAME_id from TABLENAME)
      LOOP

      purged_id := record1.TABLENAME_id;
      timestamp1 := CURRENT_TIMESTAMP;
      purged_no := purged_no + 1;
      oid_found := 0;
      highest_oid := 0;

        -- Now we have the unique id, find its latest date. 

      FOR record2 IN select max(oid) from TABLENAME_backup 
          where TABLENAME_id = purged_id and error_code = ''purge''
        LOOP 
          -- record we got the date and also record the highest date.
        oid_found := 1; 
        highest_oid := record2.max;
      END LOOP;
 
         -- If the oid_found is 0, return error. 
      IF oid_found = 0 THEN return (-3); END IF;

        -- Now we have the latest date, get the values and insert them. 
      FOR record_backup IN select * from TABLENAME_backup 
          where oid = highest_oid
        LOOP 

      insert into TABLENAME_backup (TABLENAME_id, date_updated, date_created,
          active BACKUPCOLUMNS ,error_code)
           values (purged_id, record_backup.date_updated, 
             timestamp1, record_backup.active
             BACKUPVALUES , ''unpurge''
          );

        -- Get the unique oid of the row just inserted. 
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -1
      IF oid1 &lt; 1 THEN return (-1); END IF;

      insert into TABLENAME (TABLENAME_id, date_updated, date_created,
          active BACKUPCOLUMNS)
           values (purged_id, timestamp1,
             timestamp1, record_backup.active
             BACKUPVALUES );
        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -2
      IF oid1 &lt; 1 THEN return (-2); END IF;

      END LOOP;

   END LOOP;

     -- We got this far, it must be true, return how many were affected.  
   return (purged_no);
END;
' LANGUAGE 'plpgsql';

---------------------------------------------------------------------
drop function sql_TABLENAME_unpurgeone (int4);
CREATE FUNCTION sql_TABLENAME_unpurgeone (int4) RETURNS int2 AS '
DECLARE
    record_id int4;
    record1 RECORD;
    record2 RECORD;
    record_backup RECORD;
    return_int4 int4 :=0;
    purged_id int4 := 0;
    purge_count int4 :=0;
    timestamp1 timestamp;
    purged_no int4 := 0;
    oid1 int4 := 0;
    oid_found int4 := 0;
    highest_oid int4 := 0;

BEGIN

      purged_id := $1;
        -- If purged_id less than 1, return -1
      IF purged_id &lt; 1 THEN return (-1); END IF;
        --- Get the current timestamp.
      timestamp1 := CURRENT_TIMESTAMP;

   FOR record1 IN select distinct TABLENAME_id from TABLENAME_backup
       where TABLENAME_backup.error_code = ''purge''
          and NOT TABLENAME_id = ANY (select TABLENAME_id from TABLENAME)
          and TABLENAME_id = purged_id
      LOOP
      purged_no := purged_no + 1;

   END LOOP;

        -- If purged_no less than 1, return -1
   IF purged_no &lt; 1 THEN return (-3); END IF;

        -- Now find the highest oid.  
   FOR record2 IN select max(oid) from TABLENAME_backup
          where TABLENAME_id = purged_id and error_code = ''purge''
        LOOP
          -- record we got the date and also record the highest date.
        oid_found := 1;
        highest_oid := record2.max;
    END LOOP;

         -- If the oid_found is 0, return error.
    IF oid_found = 0 THEN return (-4); END IF;

        -- Now get the data and restore it. 
    FOR record_backup IN select * from TABLENAME_backup 
          where oid  = highest_oid
        LOOP 
        -- Insert into backup that it was unpurged. 
      insert into TABLENAME_backup (TABLENAME_id, date_updated, date_created,
          active BACKUPCOLUMNS ,error_code)
           values (purged_id, timestamp1, 
             record_backup.date_created, record_backup.active
             BACKUPVALUES , ''unpurgeone''
          );

        -- Get the unique oid of the row just inserted. 
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -1
      IF oid1 &lt; 1 THEN return (-1); END IF;
        -- Insert into live table. 
      insert into TABLENAME (TABLENAME_id, date_updated, date_created,
          active BACKUPCOLUMNS)
           values (record_backup.TABLENAME_id, timestamp1,
             record_backup.date_updated, record_backup.active
             BACKUPVALUES );
        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -2
      IF oid1 &lt; 1 THEN return (-2); END IF;

      END LOOP;

     -- We got this far, it must be true, return how many were affected (1).  
   return (purged_no);
END;
' LANGUAGE 'plpgsql';





</pre>

and lastly <a href="misc/nielsen/Custom.sql">Custom.sql</a>. 
<pre>
---          Custom Sample SQL for Perl/PostgreSQL version 0.1

---                       Copyright 2001, Mark Nielsen
---                            All rights reserved.
---    This Copyright notice was copied and modified from the Perl 
---    Copyright notice. 
---    This program is free software; you can redistribute it and/or modify
---    it under the terms of either:

---        a) the GNU General Public License as published by the Free
---        Software Foundation; either version 1, or (at your option) any
---        later version, or

---        b) the "Artistic License" which comes with this Kit.

---    This program is distributed in the hope that it will be useful,
---    but WITHOUT ANY WARRANTY; without even the implied warranty of
---    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See either
---    the GNU General Public License or the Artistic License for more details.

---    You should have received a copy of the Artistic License with this
---    Kit, in the file named "Artistic".  If not, I'll be glad to provide one.

---    You should also have received a copy of the GNU General Public License
---   along with this program in the file named "Copying". If not, write to the 
---   Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 
---    02111-1307, USA or visit their web page on the internet at
---    http://www.gnu.org/copyleft/gpl.html.

drop function clean_text (text);
CREATE FUNCTION  clean_text (text) RETURNS text AS '
  my $Text = shift;
    # Get rid of whitespace in front. 
  $Text =~ s/^\\s+//;
    # Get rid of whitespace at end. 
  $Text =~ s/\\s+$//;
    # Get rid of anything not text.
  $Text =~ s/[^ a-z0-9\\/\\`\\~\\!\\@\\#\\$\\%\\^\\&\\*\\(\\)\\-\\_\\=\\+\\\\\\|\[\\{\\]\\}\\;\\:\\''\\"\\,\\&lt;\\.\\&gt;\\?\\t\\n]//gi;
    # Replace all multiple whitespace with one space. 
  $Text =~ s/\\s+/ /g;
  return $Text;
' LANGUAGE 'plperl';
 -- Just to show you what this function cleans up. 
select clean_text ('       ,./&lt;&gt;?aaa aa      !@#$%^&*()_+| ');

drop function clean_alpha (text);
CREATE FUNCTION  clean_alpha (text) RETURNS text AS '
  my $Text = shift;
  $Text =~ s/[^a-z0-9_]//gi;
  return $Text;
' LANGUAGE 'plperl';
 -- Just to show you what this function cleans up. 
select clean_alpha ('       ,./&lt;&gt;?aaa aa      !@#$%^&*()_+| ');

drop function clean_numeric (text);
CREATE FUNCTION  clean_numeric (text) RETURNS int4 AS '
  my $Text = shift;
  $Text =~ s/[^0-9]//gi;
  return $Text;
' LANGUAGE 'plperl';
 -- Just to show you what this function cleans up.
select clean_numeric ('       ,./&lt;&gt;?aaa aa      !@#$%^&*()_+| ');

drop function clean_numeric (int4);
CREATE FUNCTION  clean_numeric (int4) RETURNS int4 AS '
  my $Text = shift;
  $Text =~ s/[^0-9]//gi;
  return $Text;
' LANGUAGE 'plperl';
 -- Just do show you what this function cleans up.
select clean_numeric (1111);



</pre>

<p>
After you save the perl script, execute "chmod 755 Create_Functions.pl"
and then "./Create_Functions.pl". That should do it. 
<p>
If you have installed PostgreSQL and Perl correctly, and you have setup
the database and your account has permissions to that database, then everything
should have worked fine.  

<h3><a NAME="consider"></a>Considerations to explore.</h3>
I would like to test TCL, Python, and other languages as well for
stored procedures. If you are using MySQL, and I don't believe it has
stored procedures, you may want to consider PostgreSQL if you like the
style I mentioned. A nice GUI application to create tables and 
make changes to tables would be nice. Lastly, examples of how to
connect to the database server to use these stored procedures (using Perl, 
Python, PHP, TCL, C, etc) would be nice.  

<h3><a NAME="Conclusion"></a>Conclusion</h3>
The combination of PostgreSQL and Perl rocks. I can use Perl for three things, 
stored procedures, to setup my database, and to make Perl modules for
Apache that connect to the PostgreSQL database. Similar stuff can be
accomplished with other programming languages like Python, TCL, and others.
I want to try Python at some point when it gets out of beta
for PostgreSQL.
<p>
All database servers should use procedures exclusively for changing data. 
You could even argue that you should make custom stored procedures
for selecting data as well. The reason why this is so important is because
the web programmer (or other type of programmer) doesn't have to know 
anything about how to manipulate the data. They just submit variables
to procedures. This lets the web programmer use any programming language
he/she wants to without changing the behaviour of the database. The database
and how you use it becomes abstract. 
<p>
One stupid thing my perl script does is execute the custom sql code
for each table. This s very bad. I will have to go back and fix it later. 
You may want to test my stuff out with these commands:
<pre>
select sql_account_insert();
select sql_account_delete(1);
select sql_account_insert();
select sql_account_update(2,'mark','nielsen');
select sql_account_purge();
select sql_account_unpurge();
select * from account_backup;
select sql_account_delete(2);
select sql_account_insert();
select sql_account_update(1,'john','nielsen');
select sql_account_purge();
select * from account_backup;
</pre> 

<h3>
<a NAME="REF"></a>References</h3>

<ol>
<li><a href="../issue67/nielsen.html">
My Previous PostgreSQL article</a>.
<li>
If this article
changes, it will be available at
<a href="http://www.gnujobs.com/Articles/22/Perl_PostgreSQL2.html">
http://www.gnujobs.com/Articles/22/Perl_PostgreSQL2.html</a></li>
</ol>







<!-- *** BEGIN bio *** -->
<SPACER TYPE="vertical" SIZE="30">
<p> 
<h4><img align=bottom alt="" src="../gx/note.gif">Mark Nielsen</h4>
<EM>Mark works as an independent consultant donating time to causes like
GNUJobs.com, writing articles, writing free software, and working
as a volunteer at <a href="http://www.eastmont.net">eastmont.net</a>.</EM>

<!-- *** END bio *** -->

<!-- *** BEGIN copyright *** -->
<P> <hr> <!-- P --> 
<H5 ALIGN=center>

Copyright &copy; 2001, Mark Nielsen.<BR>
Copying license <A HREF="../copying.html">http://www.linuxgazette.com/copying.html</A><BR> 
Published in Issue 69 of <i>Linux Gazette</i>, August 2001</H5>
<!-- *** END copyright *** -->

<!--startcut ==========================================================-->
<HR><P>
<CENTER>
<!-- *** BEGIN navbar *** -->
<IMG ALT="" SRC="../gx/navbar/left.jpg" WIDTH="14" HEIGHT="45" BORDER="0" ALIGN="bottom"><A HREF="mathew.html"><IMG ALT="[ Prev ]" SRC="../gx/navbar/prev.jpg" WIDTH="16" HEIGHT="45" BORDER="0" ALIGN="bottom"></A><A HREF="index.html"><IMG ALT="[ Table of Contents ]" SRC="../gx/navbar/toc.jpg" WIDTH="220" HEIGHT="45" BORDER="0" ALIGN="bottom" ></A><A HREF="../index.html"><IMG ALT="[ Front Page ]" SRC="../gx/navbar/frontpage.jpg" WIDTH="137" HEIGHT="45" BORDER="0" ALIGN="bottom"></A><A HREF="http://www.linuxgazette.com/cgi-bin/talkback/all.py?site=LG&article=http://www.linuxgazette.com/issue69/nielsen.html"><IMG ALT="[ Talkback ]" SRC="../gx/navbar/talkback.jpg" WIDTH="121" HEIGHT="45" BORDER="0" ALIGN="bottom"  ></A><A HREF="../faq/index.html"><IMG ALT="[ FAQ ]" SRC="./../gx/navbar/faq.jpg"WIDTH="62" HEIGHT="45" BORDER="0" ALIGN="bottom"></A><A HREF="okopnik.html"><IMG ALT="[ Next ]" SRC="../gx/navbar/next.jpg" WIDTH="15" HEIGHT="45" BORDER="0" ALIGN="bottom"  ></A><IMG ALT="" SRC="../gx/navbar/right.jpg" WIDTH="15" HEIGHT="45" ALIGN="bottom">
<!-- *** END navbar *** -->
</CENTER>
</BODY></HTML>
<!--endcut ============================================================-->