1 <?php
2 /**
3 * @package Joomla.Libraries
4 * @subpackage Schema
5 *
6 * @copyright Copyright (C) 2005 - 2017 Open Source Matters, Inc. All rights reserved.
7 * @license GNU General Public License version 2 or later; see LICENSE.txt
8 */
9
10 defined('JPATH_PLATFORM') or die;
11
12 /**
13 * Checks the database schema against one SQL Server DDL query to see if it has been run.
14 *
15 * @since 2.5
16 */
17 class JSchemaChangeitemSqlsrv extends JSchemaChangeitem
18 {
19 /**
20 * Checks a DDL query to see if it is a known type
21 * If yes, build a check query to see if the DDL has been run on the database.
22 * If successful, the $msgElements, $queryType, $checkStatus and $checkQuery fields are populated.
23 * The $msgElements contains the text to create the user message.
24 * The $checkQuery contains the SQL query to check whether the schema change has
25 * been run against the current database. The $queryType contains the type of
26 * DDL query that was run (for example, CREATE_TABLE, ADD_COLUMN, CHANGE_COLUMN_TYPE, ADD_INDEX).
27 * The $checkStatus field is set to zero if the query is created
28 *
29 * If not successful, $checkQuery is empty and , and $checkStatus is -1.
30 * For example, this will happen if the current line is a non-DDL statement.
31 *
32 * @return void
33 *
34 * @since 2.5
35 */
36 protected function buildCheckQuery()
37 {
38 // Initialize fields in case we can't create a check query
39 $this->checkStatus = -1; // change status to skipped
40 $result = null;
41
42 // Remove any newlines
43 $this->updateQuery = str_replace("\n", '', $this->updateQuery);
44
45 // Fix up extra spaces around () and in general
46 $find = array('#((\s*)\(\s*([^)\s]+)\s*)(\))#', '#(\s)(\s*)#');
47 $replace = array('($3)', '$1');
48 $updateQuery = preg_replace($find, $replace, $this->updateQuery);
49 $wordArray = explode(' ', $updateQuery);
50
51 // First, make sure we have an array of at least 6 elements
52 // if not, we can't make a check query for this one
53 if (count($wordArray) < 6)
54 {
55 // Done with method
56 return;
57 }
58
59 // We can only make check queries for alter table and create table queries
60 $command = strtoupper($wordArray[0] . ' ' . $wordArray[1]);
61
62 if ($command === 'ALTER TABLE')
63 {
64 $alterCommand = strtoupper($wordArray[3] . ' ' . $wordArray[4]);
65
66 if ($alterCommand === 'ADD')
67 {
68 $result = 'SELECT * FROM INFORMATION_SCHEMA.Columns ' . $wordArray[2] . ' WHERE COLUMN_NAME = ' . $this->fixQuote($wordArray[5]);
69 $this->queryType = 'ADD';
70 $this->msgElements = array($this->fixQuote($wordArray[2]), $this->fixQuote($wordArray[5]));
71 }
72 elseif ($alterCommand === 'CREATE INDEX')
73 {
74 $index = $this->fixQuote(substr($wordArray[5], 0, strpos($wordArray[5], '(')));
75 $result = 'SELECT * FROM SYS.INDEXES ' . $wordArray[2] . ' WHERE name = ' . $index;
76 $this->queryType = 'CREATE INDEX';
77 $this->msgElements = array($this->fixQuote($wordArray[2]), $index);
78 }
79 elseif (strtoupper($wordArray[3]) === 'MODIFY' || strtoupper($wordArray[3]) === 'CHANGE')
80 {
81 $result = 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ' . $this->fixQuote($wordArray[2]);
82 $this->queryType = 'ALTER COLUMN COLUMN_NAME =' . $this->fixQuote($wordArray[4]);
83 $this->msgElements = array($this->fixQuote($wordArray[2]), $this->fixQuote($wordArray[4]));
84 }
85 }
86
87 if ($command === 'CREATE TABLE')
88 {
89 $table = $wordArray[2];
90 $result = 'SELECT * FROM sys.TABLES WHERE NAME = ' . $this->fixQuote($table);
91 $this->queryType = 'CREATE_TABLE';
92 $this->msgElements = array($this->fixQuote($table));
93 }
94
95 // Set fields based on results
96 if ($this->checkQuery = $result)
97 {
98 // Unchecked status
99 $this->checkStatus = 0;
100 }
101 else
102 {
103 // Skipped
104 $this->checkStatus = -1;
105 }
106 }
107
108 /**
109 * Fix up integer. Fixes problem with MySQL integer descriptions.
110 * If you change a column to "integer unsigned" it shows
111 * as "int(10) unsigned" in the check query.
112 *
113 * @param string $type1 the column type
114 * @param string $type2 the column attributes
115 *
116 * @return string The original or changed column type.
117 *
118 * @since 2.5
119 */
120 private function fixInteger($type1, $type2)
121 {
122 $result = $type1;
123
124 if (strtolower($type1) === 'integer' && strtolower(substr($type2, 0, 8)) === 'unsigned')
125 {
126 $result = 'int';
127 }
128
129 return $result;
130 }
131
132 /**
133 * Fixes up a string for inclusion in a query.
134 * Replaces name quote character with normal quote for literal.
135 * Drops trailing semicolon. Injects the database prefix.
136 *
137 * @param string $string The input string to be cleaned up.
138 *
139 * @return string The modified string.
140 *
141 * @since 2.5
142 */
143 private function fixQuote($string)
144 {
145 $string = str_replace('[', '', $string);
146 $string = str_replace(']', '', $string);
147 $string = str_replace('`', '', $string);
148 $string = str_replace(';', '', $string);
149 $string = str_replace('#__', $this->db->getPrefix(), $string);
150
151 return $this->db->quote($string);
152 }
153 }
154