Zikula Community

• **unknown user**
  
  

  • Rank: Registered User
  • Registered: Mar 16, 2002
  • Last visit: Nov 17, 2004
  • Posts: 4

  Posted: Oct 19, 2004 - 01:29 PM

  I'm still new at PostNuke Module development but am getting the hang of it.
  
  I did notice something rather redundant and possibly a painful issue when developing. Your tables are declared in the pntables.php and created and dropped in pninit.php. However the code between the two files can become difficult to maintain. Your modules database schema is in 3 or more places(Modulename_pntables, Modulename_init, Modulename_delete) if you use the example module given.
  
  I got around this by creating a function in pntables.php that returns a multidimensional array that has the table name as key then an array of fields which are arrays name, type, length, autoincrement, primary key,
  

  Code

  function Modulename_pntableinfo()
  {
  $columns[]=array('name'=>'id','auto'=>1,'primary'=>1,'type'=>'bigint','length'=>20);    $columns[]=array('name'=>'name','type'=>'varchar','length'=>20);
  $tables['tablename1']=$columns;
  
  $columns[]=array('name'=>'id','auto'=>1,'primary'=>1,'type'=>'bigint','length'=>20);
  $columns[]=array('name'=>'name','type'=>'varchar','length'=>20);
  $tables['tablename2']=$columns;
  
  return $tables;
  }

  
  Then Modulename_pntables reads this array and adds pnConfigGetVar('prefix') and the module name and returns valid a pntable array for the rest of the system.
  
  My init and deletet functions for the use the results of the above function to create and delete the tables.
  
  The benefit is that you can modify your table structure in 1 place instead of 3.
  
  It doesn't support Modulename_update though. (maybe add a version item in the field array?)
  
  Does anyone know of a better way than the example or what I've written?
  
  Thanks!
  
  Leigh

  • 
  •  
• rgasch
  
  

  • Rank: Team Member
  • Registered: Jan 05, 2003
  • Last visit: May 28, 2010
  • Posts: 775

  Posted: Oct 19, 2004 - 08:52 PM

  I think your approach makes sense and would be an improvement to PN (from memory, I think Xaraya has done something very similar to this).
  
  I would prefer if PN switched to such an approach, however I don't think this will happen any time soon. Perhaps you could post your hack as a patch under the NOC...
  
  Greetings/Thanks
  --> R

  • 
  •  
• markwest
  
  

  • Rank: Team Member
  • Registered: Mar 18, 2002
  • Last visit: Oct 21, 2009
  • Posts: 6606

  Posted: Oct 19, 2004 - 09:43 PM

  You don't actually have to have the full table information in pntables.php now so it can be left out if you'd prefer. All pntables.php need contain is the detection of the prefix for the relevant table. Some changes have already happened in .8x cvs to implement more streamlined management of tables - in our case via the use of ADODB's data dictionary libary.
  
  -Mark
  
  --
  Visit My homepage and Zikula themes.

  • 
  •  
• **unknown user**
  
  

  • Rank: Registered User
  • Registered: Mar 16, 2002
  • Last visit: Nov 17, 2004
  • Posts: 4

  Posted: Oct 20, 2004 - 02:38 AM

  It's really rough not ready for prime time and i don't have the time to fix it up for public use (no GPL screaming right now) I need to put in input validation error handling and other checks and balances
  
  It was easier to do this when starting so I could check the INIT and delete of the tables and quickly add or remove fields.
  
  pntables.php
  

  Code

  function Example_pntables()
  {
       // Initialize table array
      $pntables = array();
      // Retrieve Table Information
      $tables = private_Example_pntableinfo();
      // Get Prefix for PN installation
      $pfx = pnConfigGetVar('prefix') . '_';
      // Get Module Name
      $mod_prefix = 'Example';
      // Loop through all of our tables and columns
      while (list($tablename, $table) = each($tables)) {
          // Get the Table Name array key
          $tablename = $mod_prefix . '_' . $tablename;
          // Get the Table Columns Array Key
          $tablenamec = $tablename . "_column";
          // Set add the table name to the array (i.e. nuke_Example_table)
          $pntables[$tablename] = $pfx . $tablename;
          // Get the columns for the table
          while (list($ky, $column) = each($table)) {
              // Fetch the Column Name
              $columnname = $column['name'];
              // Set the Column Name
              $pntables[$tablenamec][$columnname] = $pntables[$tablename] . ".$column[name]";
          }
      }
      // Return the complete array.
      return $pntables;
  }
  function private_Example_pntableinfo()
  {
      // Initialize table array
      $tables = array();
      // Initialize column array
      $columns = array();
      $columns[] = array('name' => 'example_id', 'auto' => 1, 'primary' => 1, 'type' => 'bigint', 'length' => 20);
      $columns[] = array('name' => 'title', 'type' => 'varchar', 'length' => 20);
      $columns[] = array('name' => 'content', 'type' => 'text');
      $columns[] = array('name' => 'updated', 'type' => 'timestamp', 'length' => 14);// I LIKE timestamps....
      $columns[] = array('name' => 'created', 'type' => 'timestamp', 'length' => 14);// Handy little buggers...
      // Add table info to array
      $tables['example'] = $columns;
  
  // Rinse and repeat for other tables...
  
  
      return $tables;
  }

  
  pninit.php
  

  Code

  function Example_init() {
          $dbconn = &pnDBGetConn(true);
          // get the tables
          $tables = private_Example_pntableinfo();
          $sql = '';
          $pfx = pnConfigGetVar('prefix') . '_';
          $mod_prefix = 'Example';
          while (list($tablename, $tableinfo) = each($tables)) {
              $sql = "CREATE TABLE $pfx$mod_prefix" . "_$tablename(";
              while (list($kky, $column) = each($tableinfo)) {
                  // Set the column name
                  $sql .= "$column[name] ";
                  /**
                  * Future use to more accuratly set up fields (i.e. Floats need decimal places, varchar need length)
                  */
                  switch ($column['type']) {
                      default:
                      // Set column type
                      $sql .= "$column[type]";
                      // Set Column Length if there is one
                      if ($column['length']) {
                          $sql .= "($column[length])";
                      }
                      // There's really no NULL attribute checking...
                      $sql .= " NOT NULL";
                      if ($column['auto']) {
                          $sql .= " auto_increment";
                      }
                  }
                  // Add the field to the primary key
                  if ($column['primary'] == 1) {
                      if (strlen($primary_key) > 1)$primary_key .= ", ";
                      $primary_key .= $column[name];
                  }
                  // yes it's shady but I always have a a primary key
                  $sql .= ", ";
              }
              // $sql=substr($sql,0,strlen($sql)-2) ;
              $sql .= " PRIMARY KEY($primary_key))";
              $primary_key = '';
              // All right setup the table...
              $dbconn->execute($sql);
              if (!$dbconn->ErrorNo() == 0) {
                  // OOOPS something went wrong try and clean up
                  Example_delete();
                  return false;
              }
              unset($sql);
          }
          return 'op';
      }
      function Example_delete() {
          // get the tables
          $tables = Example_pntables();
          $dbconn = &pnDBGetConn(true);
          // Loop through the tables
          while (list($table, $tablename) = each($tables)) {
              // drop them like babies
              $dbconn->Execute("drop table $tablename");
          }
          // We don't care what happend really we don't
          return true;
      }

  
  Is it pretty? Nope but hey it works....
  
  Leigh

  • 
  •  
•  
•  

Users on-line

• mesteele101

This list is based on users active over the last 60 minutes.