PHP Standards

1. CORE does use class autoloading, but does not currently use namespaces or underscores to define directory paths. File names should still match class names and be case-sensitive - e.g., class Foo should be defined in a file named Foo.php.
2. PSR-1 says Files SHOULD either declare symbols (classes, functions, constants, etc.) or cause side-effects (e.g. generate output, change .ini settings, etc.) but SHOULD NOT do both. CORE files should not cause side effects at all when include()ed; side effects should only occur when files are directly executed. This aids in class autodiscovery. Typically one of these constructs used:

   if (dirname(__FILE__) == dirname($_SERVER['PHP_SELF'])) {
       // means file is being executed
       // side effects code goes here
   }
   
   // OR EQUIVALENTLY
   
   FannieDispatch::conditionalExec();
       

SQL Standards

1. Naming Things:
   • New tables and views should be named in StudlyCaps. Names should be plural unless there is a specific logical reason for a singular name.
   • Column names should be in camelCase.
   • All tables should contain an incrementing integer column named tableNameID. The camelCase rule applies and these columns should be singular unless, again, there is a specific logical reason for using plural. For example, a table named Members would have increment column memberID.
   • All tables should contain a primary key. The primary key may be the incrementing integer column but does not have to be if another column has a more important uniqueness contraint. Single column primary keys are preferred
   • Foreign key columns should exactly match the column name in the relevant table.
   • Exceptions:
     • New columns added to an existing table should follow the naming style present in that table. If existing columns are lowercase_underscore, new columns should be the same. If there is no rhyme or reason to the existing column names, use camelCase.
     • Abbreviations and acronyms are generally capitalized - e.g., memberID instead of memberId - but if they occur in the middle of a name use whatever capitalization pattern is most readable.
     • Multiple foreign keys to the same column can be named in any reasonably logical manner.
     • If a new table has a foreign key to an older table where the older table does not have proper camelCase column names, the new table should use a camelCase name rather than an exactly matching name. For example, NewTable.departmentID corresponds to departments.dept_no rather than continuing to spread misformatted names through the schema.
2. Writing Queries:
   • SQL reserved words and function names should be CAPITALIZED.
   • SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY lines should all be on new lines at the same indent level.
   • At most one column or conditional may be included on the same line as SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY.
   • Additional columns, conditionals, and joins should be indented on further level with one column, conditional, or join per line.
   • JOIN and ON should go on the same line.
   • Very long column, conditional, or join lines should be split and indented so as to be readable.
   • Commas go on the end of lines
   • ANDs and ORs go at the beginning of lines
   • Example queries:

     SELECT d.dept_no,
         d.dept_name,
         COUNT(p.upc) AS numProducts
     FROM products AS p
         LEFT JOIN departments AS d ON p.department=d.dept_name
     WHERE p.upc LIKE '0000000%'
         AND d.department < 500
     GROUP BY d.dept_no,
         d.dept_name
     HAVING COUNT(p.upc) <> 0
     ORDER BY d.dept_no,
         d.dept_name
     
     SELECT
         d.dept_no,
         d.dept_name,
         COUNT(p.upc) AS numProducts
     FROM 
         products AS p
         LEFT JOIN departments AS d ON p.department=d.dept_name
     WHERE 
         p.upc LIKE '0000000%'
         AND d.department < 500
     GROUP BY 
         d.dept_no,
         d.dept_name
     HAVING 
         COUNT(p.upc) <> 0
     ORDER BY 
         d.dept_no,
         d.dept_name
         

   • User input should be handled via prepared statements in lane code. User input must be handled via prepared statements in Fannie code. Typically question marks are used instead of named placeholders.
   • Queries should specify column names on SELECT and INSERT queries rather than assuming a particular column count and order.
   • Results should be accessed by column name rather than numeric index
   • Reasonable effort should be made to avoid using syntax only supported by one particular dialect of SQL. The SQLManager class provides helper methods for generating appropriate per-dialect syntax for many common situations.