Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
systems:yii2:migrations [2018/04/18 12:29]
smayr 		systems:yii2:migrations [2018/04/19 10:07] (current)
smayr [New Notation]
Line 55: Line 55:
                  
         foreach($targetTables as $curTable) {         foreach($targetTables as $curTable) {
 +            echo "Checking table: {$curTable}\n";
             if (in_array($curTable, $allTables)) {             if (in_array($curTable, $allTables)) {
                 //if ($curTable == 'company_address')                 //if ($curTable == 'company_address')
Line 266: Line 267:
 class m160601_220000_skeleton extends Migration class m160601_220000_skeleton extends Migration
 { {
 +    private $useExtendedUserProfile = false;  // set it to your needs
 +    
     public function up()     public function up()
     {     {
Line 271: Line 274:
         $this->create_table_entry();         $this->create_table_entry();
         $this->create_table_product_hash();         $this->create_table_product_hash();
 +
 +        if ($this->useExtendedUserProfile) {
 +            $this->alter_user_table();
 +        }
     }     }
  
Line 276: Line 283:
     {     {
         $this->drop_tables();         $this->drop_tables();
 +        
         return true;         return true;
     }     }
Line 287: Line 295:
         // Available Tables: must be listed in reverse creation order,         // Available Tables: must be listed in reverse creation order,
         // and dropping tables with foreign keys first.         // and dropping tables with foreign keys first.
-        $targetTables= ["product_hash", "entry"];+        //$targetTables= ["product_hash", "entry"]; 
 +        $targetTables= [ 
 +            $this->db->schema->getRawTableName(app\models\ProductHash::tableName()), 
 +            $this->db->schema->getRawTableName(app\models\Entry::tableName()), 
 +        ];
                  
         echo "\nAvailable Tables: \n- ".implode("\n- ", $allTables)."\n";         echo "\nAvailable Tables: \n- ".implode("\n- ", $allTables)."\n";
Line 295: Line 307:
             echo "Checking table: {$curTable}\n";             echo "Checking table: {$curTable}\n";
             if (in_array($curTable, $allTables)) {             if (in_array($curTable, $allTables)) {
-                if ($curTable == 'entry') { +                if ($curTable == $this->db->schema->getRawTableName(app\models\Entry::tableName())) {  // entry 
-                    //$this->dropForeignKey('FK_product_id', $curTable);+                    //$this->dropForeignKey('FK_name', $curTable); 
 +                } 
 +                if ($curTable == $this->db->schema->getRawTableName(app\models\ProductHash::tableName())) {  // product_hash 
 +                    //$this->dropForeignKey('FK_name', $curTable);
                 }                 }
                 $this->truncateTable($curTable);   // delete all records                 $this->truncateTable($curTable);   // delete all records
                 $this->dropTable($curTable);       // drop table                 $this->dropTable($curTable);       // drop table
 +            }
 +            
 +            if ($this->useExtendedUserProfile) {
 +                // Restore any original table changes
 +                $this->restore_user_table();
             }             }
         }         }
 +    }
 +    
 +    private function alter_user_table()
 +    {
 +        $this->addColumn('{{%user}}', 'address1', $this->string());
 +        $this->addColumn('{{%user}}', 'address2', $this->string());
 +        $this->addColumn('{{%user}}', 'city', $this->string());
 +        $this->addColumn('{{%user}}', 'state_prov', $this->string());
 +        $this->addColumn('{{%user}}', 'postal_code', $this->string());
 +        $this->addColumn('{{%user}}', 'country', $this->string());
 +        // $this->addColumn('{{%user}}', 'company_name', $this->string());
 +        // $this->addColumn('{{%user}}', 'organization_name', $this->string());
 +        // $this->addColumn('{{%user}}', 'school_name', $this->string());
 +        // $this->addColumn('{{%user}}', 'job_title', $this->string());
 +        $this->addColumn('{{%user}}', 'receive_newsletter', $this->tinyInteger()->notNull()->defaultValue(1));
 +    }
 +    
 +    private function restore_user_table()
 +    {
 +        $this->dropColumn('{{%user}}', 'address1');
 +        $this->dropColumn('{{%user}}', 'address2');
 +        $this->dropColumn('{{%user}}', 'city');
 +        $this->dropColumn('{{%user}}', 'state_prov');
 +        $this->dropColumn('{{%user}}', 'postal_code');
 +        $this->dropColumn('{{%user}}', 'country');
 +        $this->dropColumn('{{%user}}', 'company_name');
 +        $this->dropColumn('{{%user}}', 'job_title');
 +        $this->dropColumn('{{%user}}', 'receive_newsletter');
     }     }
          
Line 329: Line 377:
         // AUTO_INCREMENT=1;         // AUTO_INCREMENT=1;
                  
-        $this->createTable('entry', [+        $this->createTable('{{%entry}}', [
             'id'                => $this->primaryKey(),             'id'                => $this->primaryKey(),
             'serial_number'     => $this->string(50)->defaultValue('0'),             'serial_number'     => $this->string(50)->defaultValue('0'),
Line 343: Line 391:
                  
         // insert data as a row         // insert data as a row
-        $this->insert('entry', [+        $this->insert('{{%entry}}', [
             "serial_number"     => "15169630",              "serial_number"     => "15169630", 
             "order_number"      => "0001",              "order_number"      => "0001", 
Line 376: Line 424:
         // AUTO_INCREMENT=1;         // AUTO_INCREMENT=1;
                  
-        $this->createTable('product_hash', [+        $this->createTable('{{%product_hash}}', [
             'id'              => $this->primaryKey(),             'id'              => $this->primaryKey(),
             'program_name'    => $this->text()->notNull(),             'program_name'    => $this->text()->notNull(),
Line 385: Line 433:
                  
         // insert data as a row         // insert data as a row
-        //$this->insert('entry', [+        //$this->insert('{{%product_hash}}', [
         //    "program_name"    => "ProductITE1",          //    "program_name"    => "ProductITE1", 
         //    "program_value"   => "10",          //    "program_value"   => "10", 
Line 394: Line 442:
         // table name, column names, column values         // table name, column names, column values
         $db = Yii::$app->getDb();         $db = Yii::$app->getDb();
-        $db->createCommand()->batchInsert('product', +        $db->createCommand()->batchInsert('{{%product_hash}}', 
             ['id', 'program_name', 'program_value', 'program_version', 'hash'],              ['id', 'program_name', 'program_value', 'program_version', 'hash'], 
             [             [
Line 431: Line 479:
 Method 1: Method 1:
 <code php> <code php>
-$this->createTable('customer', [+$this->createTable('{{%customer}}', [
     'id'           => Schema::TYPE_PK,     'id'           => Schema::TYPE_PK,
     'contact'      => Schema::TYPE_STRING   . ' NULL DEFAULT NULL',     'contact'      => Schema::TYPE_STRING   . ' NULL DEFAULT NULL',
Line 445: Line 493:
 Method 2: Method 2:
 <code php> <code php>
-$this->createTable('customer', [+$this->createTable('{{%customer}}', [
     'id'         => $this->primaryKey(),     'id'         => $this->primaryKey(),
     'contact'    => $this->string(),     'contact'    => $this->string(),
Line 528: Line 576:
 // USAGE: batchInsert(table_name, column_names, column_values) // USAGE: batchInsert(table_name, column_names, column_values)
 $db = Yii::$app->getDb(); $db = Yii::$app->getDb();
-$db->createCommand()->batchInsert('{{%product}}', +$db->createCommand()->batchInsert('{{%product_hash}}', 
     ['id', 'program_name', 'program_value', 'program_version', 'hash'],      ['id', 'program_name', 'program_value', 'program_version', 'hash'], 
     [     [
Line 542: Line 590:
 // Add reference_code to all records // Add reference_code to all records
 foreach((new Query)->from('price')->each() as $price) { foreach((new Query)->from('price')->each() as $price) {
-    $this->update('price', ['reference_code' => $price->item_code], ['id' => $price->id]);+    $this->update('{{%price}}', ['reference_code' => $price->item_code], ['id' => $price->id]);
 } }
 </code> </code>
Line 551: Line 599:
 foreach((new Query)->from('price')->each() as $price) { foreach((new Query)->from('price')->each() as $price) {
   // DELETE price WHERE reference_code = 'partUnknown', id = :price_id   // DELETE price WHERE reference_code = 'partUnknown', id = :price_id
-  $this->delete('price', ['reference_code' => 'partUnknown', 'id' => $price->id]);+  $this->delete('{{%price}}', ['reference_code' => 'partUnknown', 'id' => $price->id]);
 } }
 </code> </code>
Line 557: Line 605:
  
 === Create SQL View === === Create SQL View ===
 +Instead of viewing all entry records, you can create a view with a subset of them.  For example, if we want all the entry records for brand 'Acme', we would create the 'acme_entry' view as follows:
 <code php> <code php>
 private function create_view_acme_entry() private function create_view_acme_entry()
systems/yii2/migrations.1524068995.txt.gz · Last modified: 2018/04/18 12:29 by smayr
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International


  • Show page
  • Old revisions
  • Backlinks
  • Recent Changes
  • Media Manager
  • Sitemap