<?xml version='1.0' ?>
<!DOCTYPE book PUBLIC "-//OASIS//DTD DocBook XML V4.4//EN"
               "http://www.oasis-open.org/docbook/xml/4.4/docbookx.dtd" [
<!ENTITY % myvars SYSTEM "../variables.ent">
%myvars;
]>
<sect1 id="eng-standards-plsql" xreflabel="PL/SQL Standards">
<title>PL/SQL Standards</title>

 
<authorblurb>
<para>
By Richard Li and Yon Feldman
</para>
</authorblurb>

<para>
  Like any other part of the OpenACS, PL/SQL (or pl/pgsql) code must be
  maintainable and professional. This means that it must be consistent and
  therefore must abide by certain standards. The standards will ensure that
  our product will be useful long after the current people building and
  maintaining it are around. Following are some standards and guidelines
  that will help us achieve this goal:
</para>

<sect2 id="eng-standards-plsql-general">
<title>General</title>

  <orderedlist>
   <listitem><para>
    All PL/SQL code must be well documented. We must write code that
    is maintainable by others, this is especially true in our case
    because we are building an open source toolkit than anyone can
    download and browse the source code. So document like you are
    trying to impress your "Introduction to Programming" professor or
    TA.
   </para></listitem>
   
   <listitem><para>
    It is important to be consistent throughout an application as much
    as is possible given the nature of team development. This means
    carrying style and other conventions suchs as naming within an
    application, not just within one file.
   </para></listitem>
  </orderedlist>

</sect2>

<sect2 id="eng-standards-plsql-code">
<title>Code</title>

  <orderedlist>
   <listitem><para>
    Encapsulation of related fuctionality is key to maintainability
    and upgradeability of our software. Try to bundle your code into
    <ulink url="http://oradoc.photo.net/ora816/appdev.816/a77069/08_packs.htm#4376">packages</ulink>
    whenever possible. This will make upgrading, bug fixing, and
    customizing, among other things, a possibility.
   </para></listitem>
   
   <listitem><para>
    When creating functions or procedures use the following template,
    it demonstrates most of the guidelines set forth in this document
    that correspond to functions and procedures:
    </para>     

<programlisting>
 
        create or replace procedure|function &lt;proc_or_func_name&gt; (
                 &lt;param_1&gt;    in|out|inout &lt;datatype&gt;,
                 &lt;param_2&gt;    in|out|inout &lt;datatype&gt;,
                 ...
                 &lt;param_n&gt;    in|out|inout &lt;datatype&gt;
        )
        [return &lt;datatype&gt;]
        is
                &lt;local_var_1&gt;    &lt;datatype&gt;
                &lt;local_var_2&gt;    &lt;datatype&gt;
                ...
                &lt;local_var_n&gt;    &lt;datatype&gt;
        begin
                ...
        end &lt;proc_or_func_name&gt;;
        /
        show errors
     
</programlisting>

   </listitem>
   
   <listitem><para>
    Always use <computeroutput>create or replace procedure|function
    &lt;proc_or_func_name&gt;</computeroutput>. It makes reloading packages much
    easier and painless to someone who is upgrading or fixing a bug.
   </para></listitem>
   
   <listitem><para>
    Always qualify <computeroutput>end</computeroutput> statements, i.e., the
    <computeroutput>end</computeroutput> statement for a package should be <computeroutput>end
    &lt;package_name&gt;;</computeroutput>, not just <computeroutput>end;</computeroutput>; same
    goes for procedures, functions, package bodies, and triggers.
   </para></listitem>
   
   <listitem><para>
    Always use the "show errors" SQL*Plus command after each PL/SQL
    block. It will help you debug when there are compilation errors in
    your PL/SQL code.
   </para></listitem>
   
   <listitem><para>
    Name parameters as simply as possible, i.e., use the column name
    if the parameter corresponds to a table column. We're deprecating
    the v_* and *_in syntax in favor of named parameters notation:
    </para>     

<programlisting>

      <computeroutput>
        acs_user.create(first_names =&gt; 'Jane', last_name =&gt; 'Doe', etc.)
      </computeroutput>
        instead of
      <computeroutput>
        acs_user.create(first_names_in =&gt; 'Jane', last_name_in =&gt; 'Doe', etc.)
      </computeroutput>
     
</programlisting>

<para>
    To achieve this we must fully qualify arguements passed into
    procedures or functions when using them inside a SQL
    statement. This will get rid of any ambiguities in your code,
    i.e. it will tell the parser when you want the value of the column
    and when you want the value from the local variable. Here is an
    example:
</para>

<programlisting>

        create or replace package body mypackage 
            .
            .
            procedure myproc(party_id in parties.party_id%TYPE) is begin
                .
                .
                delete
                  from parties
                 where party_id = myproc.party_id;
                .
                .
            end myproc;
            .
            .
        end mypackage;
        /
        show errors
     
</programlisting>

   </listitem>
   
   <listitem><para>
    Explicitly designate each parameter as "in," "out," or "inout."
   </para></listitem>
   
   <listitem><para>
    Each parameter should be on its own line, with a tab after the
    parameter name, then in/out/inout, then a space, and finally the
    datatype.
   </para></listitem>
   
   <listitem><para>
    Use %TYPE and %ROWTYPE whenever possible.
   </para></listitem>
   
   <listitem><para>
    Use 't' and 'f' for booleans, not the PL/SQL "boolean" datatype
    because it can't be used in SQL queries.
   </para></listitem>

   <listitem><para>
    All <computeroutput>new</computeroutput> functions (e.g., <computeroutput>acs_object.new,
    party.new,</computeroutput> etc.) should optionally accept an ID:
    </para>     

<programlisting>

      <computeroutput>
        create or replace package acs_object
        as
            function new (
                object_id       in acs_objects.object_id%TYPE default null,
                object_type     in acs_objects.object_type%TYPE default 'acs_object',
	        creation_date   in acs_objects.creation_date%TYPE default sysdate,
                creation_user   in acs_objects.creation_user%TYPE default null,
                creation_ip     in acs_objects.creation_ip%TYPE default null,
                context_id      in acs_objects.context_id%TYPE default null
           ) return acs_objects.object_id%TYPE;
     </computeroutput>
    
</programlisting>

<para>
   takes the optional argument <computeroutput>object_id</computeroutput>. Do this to
   allow people to use the same API call when they are doing double
   click protection, that is, tehy have already gotten an
   <computeroutput>object_id</computeroutput> and now they want to create the object with
   that <computeroutput>object_id</computeroutput>.
   </para></listitem>
  </orderedlist>

 </sect2>

<sect2 id="eng-standards-style">
<title>Style</title>

<para>
  Some general style guidelines to follow for the purpose of
  consistency across applications.
</para>
  

  <orderedlist>
   <listitem><para>
    Standard indentation is 4 spaces. Our PL/SQL code is not only
    viewable in the SQL files but also through our SQL and PL/SQL
    browsers. This means that we should try to make it as consistent
    as possible to all source code readers.
   </para></listitem>
   
   <listitem><para>
    Lowercase everything, with the exception of %TYPE and %ROWTYPE.
   </para></listitem>
   
  </orderedlist>

<para><phrase role="cvstag">($Id: plsql.xml,v 1.6 2006/07/17 05:38:37 torbenb Exp $)</phrase></para>

 </sect2>

</sect1>
