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 10:54]
smayr [Create SQL Trigger] 		systems:yii2:migrations [2018/04/19 10:07] (current)
smayr [New Notation]
Line 8: Line 8:
  
 For example, to create a migration of customer data:   For example, to create a migration of customer data:  
-<code>$ yii migrate/create create_customer_table</code>+<code bash>$ yii migrate/create create_customer_table</code>
  
 This generates a file ''m150904_181255_create_customer_table.php'' in ''[src]/migrations''.   This generates a file ''m150904_181255_create_customer_table.php'' in ''[src]/migrations''.  
Line 42: Line 42:
         //return true;         //return true;
                  
-        $connection    = Yii::$app->db;                  // get connection +        $connection = Yii::$app->db;                  // get connection 
-        $dbSchema      = $connection->schema;            // or $connection->getSchema(); +        $dbSchema   = $connection->schema;            // or $connection->getSchema(); 
-        $allTableNames = $dbSchema->getTableNames();     // returns array of tbl schema's+        $allTables  = $dbSchema->getTableNames();     // returns array of tbl schema's
                  
-        // Available Tables: must be listed in reverse creation order +        // Available Tables: must be listed in reverse creation order, 
-        //$availTables = ["evaluation", "issue", "customer", "sales_history", ];   +        // and dropping tables with foreign keys first. 
-        $availTables = ["customer"];+        //$targetTables = ["evaluation", "issue", "customer", "sales_history", ];   
 +        $targetTables = ["customer"];
                  
-        foreach($availTables as $curTable) { +        echo "\nAvailable Tables: \n- ".implode("\n- ", $allTables)."\n"; 
-            if (in_array($curTable, $allTableNames)) {+        echo "\nTarget Tables: \n- ".implode("\n- ", $targetTables)."\n\n"; 
 +         
 +        foreach($targetTables as $curTable) { 
 +            echo "Checking table: {$curTable}\n"; 
 +            if (in_array($curTable, $allTables)) {
                 //if ($curTable == 'company_address')                 //if ($curTable == 'company_address')
                 //{                 //{
Line 262: 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 267: 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 272: Line 283:
     {     {
         $this->drop_tables();         $this->drop_tables();
 +        
         return true;         return true;
     }     }
Line 277: Line 289:
     public function drop_tables()     public function drop_tables()
     {     {
-        $connection    = Yii::$app->db;                  // get connection +        $connection = Yii::$app->db;                  // get connection 
-        $dbSchema      = $connection->schema;            // or $connection->getSchema(); +        $dbSchema   = $connection->schema;            // or $connection->getSchema(); 
-        $allTableNames = $dbSchema->getTableNames();     // returns array of tbl schema's+        $allTables  = $dbSchema->getTableNames();     // returns array of tbl schema's
                  
-        // Available Tables: must be listed in reverse creation order, and dropping tables with foreign keys first +        // Available Tables: must be listed in reverse creation order, 
-        $availTables = ["product_hash", "entry"];+        // and dropping tables with foreign keys first. 
 +        //$targetTables= ["product_hash", "entry"]; 
 +        $targetTables= [ 
 +            $this->db->schema->getRawTableName(app\models\ProductHash::tableName()), 
 +            $this->db->schema->getRawTableName(app\models\Entry::tableName()), 
 +        ];
                  
-        foreach($availTables as $curTable) { +        echo "\nAvailable Tables: \n- ".implode("\n- ", $allTables)."\n"; 
-            if (in_array($curTable, $allTableNames)) { +        echo "\nTarget Tables: \n- ".implode("\n- ", $targetTables)."\n\n"; 
-                if ($curTable == 'entry') { +         
-                    //$this->dropForeignKey('FK_product_id', $curTable);+        foreach($targetTables as $curTable) { 
 +            echo "Checking table: {$curTable}\n"; 
 +            if (in_array($curTable, $allTables)) { 
 +                if ($curTable == $this->db->schema->getRawTableName(app\models\Entry::tableName())) {  // entry 
 +                    //$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 320: 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 334: 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 367: 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 376: 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 385: 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 422: 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 436: 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 519: 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 533: 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 542: 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 548: 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.1524063279.txt.gz · Last modified: 2018/04/18 10:54 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