Odyssey
2017071700_tron.php
1 <?php
2 /* Notes:
3  * 1. Anything complicated needs to be done with SQL.
4  * 2. Always have a check to know if the migration needs to occur (up or down).
5  * 3. Use up() and down(), not change(), because of the SQL
6  */
7 use Phinx\Migration\AbstractMigration;
8 
9 class Tron extends AbstractMigration
10 {
11 
12  /* This is the process for moving forward. We are re-creating the table so if there is
13  * existing data will need to create a temporary table, copy the data, drop the old table,
14  * rename the temporary table.
15  */
16  public function up()
17  {
18  // create each table if it doesn't exist
19  $name = "cutronab";
20  $tableExists = $this->hasTable( $name );
21 
22  if ( !$tableExists ) {
23  // announce it
24  print "Creating table $name\n";
25 
26  // create the SQL for the new table
27  $sql = "CREATE TABLE cutronab (
28  runslot character varying(10) NOT NULL,
29  cu character(10) NOT NULL,
30  accountnumber character(12) NOT NULL,
31  accounttype character(25) NOT NULL,
32  certnumber integer NOT NULL,
33  deposittype character(1) NOT NULL,
34  description character varying(255) NOT NULL,
35  amount numeric(11,2) NOT NULL,
36  ytdinterest numeric(11,2) NOT NULL,
37  lastyrinterest numeric(11,2),
38  available numeric(11,2),
39  micraccount character(17)
40  )";
41 
42  // create the table
43  $rows = $this->query( $sql );
44 
45  // create the index
46  $sql = "CREATE UNIQUE INDEX cutronab_idx01 ON cutronab USING btree (runslot, cu, accountnumber, accounttype, certnumber)";
47  $rows = $this->query( $sql );
48  }
49 
50  $name = "cutronah";
51  $tableExists = $this->hasTable( $name );
52 
53  if ( !$tableExists ) {
54  // announce it
55  print "Creating table $name\n";
56 
57  // create the SQL for the new table
58  $sql = "CREATE TABLE cutronah (
59  runslot character varying(10) NOT NULL,
60  cu character(10) NOT NULL,
61  accountnumber character(12) NOT NULL,
62  accounttype character(25) NOT NULL,
63  certnumber integer NOT NULL,
64  tracenumber character(16) NOT NULL,
65  checknumber character(6),
66  date timestamp with time zone NOT NULL,
67  amount numeric(11,2) NOT NULL,
68  description character varying(255),
69  balance numeric(11,2) NOT NULL,
70  sortkey character(16)
71  )";
72 
73  // create the table
74  $rows = $this->query( $sql );
75 
76  // create the index
77  $sql = "CREATE UNIQUE INDEX cutronah_idx01 ON cutronah USING btree (runslot, cu, accountnumber, accounttype, certnumber, tracenumber);";
78  $rows = $this->query( $sql );
79  }
80 
81  $name = "cutronlb";
82  $tableExists = $this->hasTable( $name );
83 
84  if ( !$tableExists ) {
85  // announce it
86  print "Creating table $name\n";
87 
88  // create the SQL for the new table
89  $sql = "CREATE TABLE cutronlb (
90  runslot character varying(10) NOT NULL,
91  cu character(10) NOT NULL,
92  accountnumber character(12) NOT NULL,
93  loannumber character(25) NOT NULL,
94  currentbalance numeric(11,2) NOT NULL,
95  payoff numeric(11,2) NOT NULL,
96  paymentamount numeric(11,2) NOT NULL,
97  nextduedate timestamp with time zone,
98  description character varying(255),
99  interestrate numeric,
100  creditlimit numeric(11,2),
101  ytdinterest numeric(11,2),
102  lastyrinterest numeric(11,2),
103  misc1 character varying(256),
104  type character(2),
105  lastpaymentdate timestamp with time zone
106  )";
107 
108  // create the table
109  $rows = $this->query( $sql );
110 
111  // create the index
112  $sql = "CREATE UNIQUE INDEX cutronlb_idx01 ON cutronlb USING btree (runslot, cu, accountnumber, loannumber)";
113  $rows = $this->query( $sql );
114  }
115 
116  $name = "cutronlh";
117  $tableExists = $this->hasTable( $name );
118 
119  if ( !$tableExists ) {
120  // announce it
121  print "Creating table $name\n";
122 
123  // create the SQL for the new table
124  $sql = "CREATE TABLE cutronlh (
125  runslot character varying(10) NOT NULL,
126  cu character(10) NOT NULL,
127  accountnumber character(12) NOT NULL,
128  loannumber character(25) NOT NULL,
129  tracenumber character(16) NOT NULL,
130  date timestamp with time zone NOT NULL,
131  principleamount numeric(11,2) NOT NULL,
132  interestamount numeric(11,2) NOT NULL,
133  description character varying(255),
134  balance numeric(11,2),
135  sortkey character(16)
136  )";
137 
138  // create the table
139  $rows = $this->query( $sql );
140 
141  // create the index
142  $sql = "CREATE UNIQUE INDEX cutronlh_idx01 ON cutronlh USING btree (runslot, cu, accountnumber, loannumber, tracenumber)";
143  $rows = $this->query( $sql );
144  }
145 
146  $name = "cutronus";
147  $tableExists = $this->hasTable( $name );
148 
149  if ( !$tableExists ) {
150  // announce it
151  print "Creating table $name\n";
152 
153  // create the SQL for the new table
154  $sql = "CREATE TABLE cutronus (
155  runslot character varying(10) NOT NULL,
156  cu character(10),
157  user_id integer NOT NULL,
158  shuffle double precision,
159  sched_id integer NOT NULL,
160  PRIMARY KEY (runslot, cu, user_id, sched_id)
161  )";
162 
163  // create the table
164  $rows = $this->query( $sql );
165  }
166  }
167 
168  /* To execute this you need to run the following command:
169  * php vendor/bin/phinx rollback (using correct path to phinx)
170  * Or, to roll back to a specific time:
171  * php vendor/bin/phinx rollback -t 20151123211207
172  */
173  public function down()
174  {
175  $sql = "DROP TABLE IF EXISTS cutronab";
176  $rows = $this->query( $sql );
177  $sql = "DROP TABLE IF EXISTS cutronah";
178  $rows = $this->query( $sql );
179  $sql = "DROP TABLE IF EXISTS cutronlb";
180  $rows = $this->query( $sql );
181  $sql = "DROP TABLE IF EXISTS cutronlh";
182  $rows = $this->query( $sql );
183  $sql = "DROP TABLE IF EXISTS cutronus";
184  $rows = $this->query( $sql );
185  }
186 }
Generated by   doxygen 1.8.15