· 7 years ago · Dec 28, 2018, 07:26 PM
1 /**
2 * massUpdateTables is for updating/inserting a bunch of tables/columns
3 * It will automatically figure out how to place columns and makes large migrations easier
4 * to do and also to read.
5 * @param array $array
6 * Description: Should be indexed by the table name,
7 * with an array of columns and comma seperated from data type/extra.
8 * ------------------------------------------------------------------
9 * Data_type: Would be VARCHAR/BIGINT/etc.. values are not required to be paired with a data type either
10 * Extra: Could be something like PRIMARY KEY for example
11 * @example array: [
12 * 'calendar_event_categories' => [
13 * 'source_class, VARCHAR(255)',
14 * 'source_id, BIGINT',
15 * ],
16 * 'calendar_events' => [
17 * 'start_timestamp, TIMESTAMP',
18 * 'end_timestamp, TIMESTAMP',
19 * ]
20 * ]
21 * @return [void] [Either adds a column/creates the table with your columns]
22 */
23 public static function massModify($array) {
24 $updateData = createUpdateArray($array);
25
26 foreach($updateData as $table => $columns) {
27 if(Database::tableExists($table)) {
28 foreach($columns as $column => $type) {
29 $this->massUpdateAddColumn($table, $column, $type);
30 }
31 } else {
32 $this->massUpdateCreateTable($table, $columns);
33 }
34 }
35 }
36
37 private static function createUpdateArray($array) {
38 $retval = [];
39
40 foreach($array as $table => $columns) {
41 foreach($columns as $column) {
42 // Split the insert row using comma seperation for each section
43 $column_split = explode(',', $column);
44
45 // If the splits lower than 2 that means only a column was entered
46 if(count($column_split) < 2) {
47 throw new Exception("Date type not formated correctly");
48 } else {
49 $column_name = $column_split[0];
50 }
51
52 // Split the data type from the value
53 $data_type_split = explode('(', $column_split[1]);
54
55 // Some data types dont need a value, so only add it if a value exists
56 if(count($data_type_split) == 2) {
57 $tmp_set = [
58 'data_type' => $data_type_split[0],
59 'value' => rtrim($data_type_split[1], ')') // Left ( is removed during explode
60 ];
61 } else {
62 $tmp_set['data_type'] = $data_type_split[0];
63 }
64
65 // Get whatever remainder strings are left and put their spaces back together.
66 if(count($column_split) > 2) {
67 $extra = '';
68 foreach($column_split as $index => $splits) {
69 if($index == 0 || $index == 1) {
70 continue;
71 }
72
73 $extra .= " {$splits}";
74 }
75
76 // Every loop added a space so this removes the beggining space
77 $tmp_set['extra'] = ltrim($extra, ' ');
78 }
79
80 // Section all the data for return
81 $retval[$table][$column_name] = $tmp_set;
82
83 // unset all the values otherwise next look if value is not set for example it
84 // will use value from the last loop
85 unset($tmp_set);
86 unset($data_type_split);
87 unset($column_split);
88 }
89 }
90
91 return $retval;
92 }
93
94 private static function massUpdateAddColumn($table, $column, $type) {
95 if(Database::columnExists($table, $column)) {
96 if($type['extra']) {
97 throw new Exception("Only a column and data type is supported for adding columns to a table");
98 } else {
99 if($type['value']) {
100 Database::createColumn($table, $column, $type['data_type'], $type['value']);
101 } else {
102 Database::createColumn($table, $column, $type['data_type']);
103 }
104 }
105 }
106 }
107
108 private static function massUpdateCreateTable($table, $columns) {
109 $sql = "CREATE TABLE {$table} (";
110
111 // Build the insert columns
112 foreach($columns as $column => $type) {
113 if($type['value']) {
114 $sql .= "{$column} {$type['data_type']}({$type['value']})";
115 } else {
116 $sql .= "{$column} {$type['data_type']}";
117 }
118
119 if($type['extra']) {
120 $sql .= " {$type['extra']}";
121 }
122
123 // Add the comma seperation
124 $sql .= ', ';
125 }
126
127 $sql .= ")";
128
129 Database::query($sql);
130 }
131}