1: <?php
  2: 
  3: namespace RedBeanPHP\QueryWriter;
  4: use RedBeanPHP\QueryWriter\AQueryWriter as AQueryWriter;
  5: use RedBeanPHP\QueryWriter as QueryWriter;
  6: use RedBeanPHP\Adapter\DBAdapter as DBAdapter;
  7: use RedBeanPHP\Adapter as Adapter;
  8: use RedBeanPHP\RedException\SQL as SQLException;
  9: 
 10: /**
 11:  * RedBeanPHP CUBRID Writer.
 12:  * This is a QueryWriter class for RedBeanPHP.
 13:  * This QueryWriter provides support for the CUBRID database platform.
 14:  *
 15:  * @file    RedBeanPHP/QueryWriter/CUBRID.php
 16:  * @author  Gabor de Mooij and the RedBeanPHP Community
 17:  * @license BSD/GPLv2
 18:  *
 19:  * @copyright
 20:  * (c) copyright G.J.G.T. (Gabor) de Mooij and the RedBeanPHP Community.
 21:  * This source file is subject to the BSD/GPLv2 License that is bundled
 22:  * with this source code in the file license.txt.
 23:  */
 24: class CUBRID extends AQueryWriter implements QueryWriter
 25: {
 26:     /**
 27:      * Data types
 28:      */
 29:     const C_DATATYPE_INTEGER          = 0;
 30:     const C_DATATYPE_DOUBLE           = 1;
 31:     const C_DATATYPE_STRING           = 2;
 32:     const C_DATATYPE_SPECIAL_DATE     = 80;
 33:     const C_DATATYPE_SPECIAL_DATETIME = 81;
 34:     const C_DATATYPE_SPECIFIED        = 99;
 35: 
 36:     /**
 37:      * @var DBAdapter
 38:      */
 39:     protected $adapter;
 40: 
 41:     /**
 42:      * @var string
 43:      */
 44:     protected $quoteCharacter = '`';
 45: 
 46:     /**
 47:      * This method adds a foreign key from type and field to
 48:      * target type and target field.
 49:      * The foreign key is created without an action. On delete/update
 50:      * no action will be triggered. The FK is only used to allow database
 51:      * tools to generate pretty diagrams and to make it easy to add actions
 52:      * later on.
 53:      * This methods accepts a type and infers the corresponding table name.
 54:      *
 55:      * @param  string $type        type that will have a foreign key field
 56:      * @param  string $targetType  points to this type
 57:      * @param  string $property       field that contains the foreign key value
 58:      * @param  string $targetProperty field where the fk points to
 59:      * @param  bool $isDep
 60:      *
 61:      * @return bool
 62:      */
 63:     protected function buildFK( $type, $targetType, $property, $targetProperty, $isDep = FALSE )
 64:     {
 65:         $table           = $this->esc( $type );
 66:         $tableNoQ        = $this->esc( $type, TRUE );
 67:         $targetTable     = $this->esc( $targetType );
 68:         $targetTableNoQ  = $this->esc( $targetType, TRUE );
 69:         $column          = $this->esc( $property );
 70:         $columnNoQ       = $this->esc( $property, TRUE );
 71:         $targetColumn    = $this->esc( $targetProperty );
 72:         if ( !is_null( $this->getForeignKeyForTypeProperty( $tableNoQ, $columnNoQ ) ) ) return FALSE;
 73:         $needsToDropFK   = FALSE;
 74:         $casc = ( $isDep ? 'CASCADE' : 'SET NULL' );
 75:         $sql  = "ALTER TABLE $table ADD CONSTRAINT FOREIGN KEY($column) REFERENCES $targetTable($targetColumn) ON DELETE $casc ";
 76:         try {
 77:             $this->adapter->exec( $sql );
 78:         } catch( SQLException $e ) {
 79:             return FALSE;
 80:         }
 81:         return TRUE;
 82:     }
 83: 
 84:     /**
 85:      * @see AQueryWriter::getKeyMapForType
 86:      */
 87:     protected function getKeyMapForType( $type  )
 88:     {
 89:         $sqlCode = $this->adapter->get("SHOW CREATE TABLE `{$type}`");
 90:         if (!isset($sqlCode[0])) return array();
 91:         $matches = array();
 92:         preg_match_all( '/CONSTRAINT\s+\[([\w_]+)\]\s+FOREIGN\s+KEY\s+\(\[([\w_]+)\]\)\s+REFERENCES\s+\[([\w_]+)\](\s+ON\s+DELETE\s+(CASCADE|SET\sNULL|RESTRICT|NO\sACTION)\s+ON\s+UPDATE\s+(SET\sNULL|RESTRICT|NO\sACTION))?/', $sqlCode[0]['CREATE TABLE'], $matches );
 93:         $list = array();
 94:         if (!isset($matches[0])) return $list;
 95:         $max = count($matches[0]);
 96:         for($i = 0; $i < $max; $i++) {
 97:             $label = $this->makeFKLabel( $matches[2][$i], $matches[3][$i], 'id' );
 98:             $list[ $label ] = array(
 99:                 'name' => $matches[1][$i],
100:                 'from' => $matches[2][$i],
101:                 'table' => $matches[3][$i],
102:                 'to' => 'id',
103:                 'on_update' => $matches[6][$i],
104:                 'on_delete' => $matches[5][$i]
105:             );
106:         }
107:         return $list;
108:     }
109: 
110:     /**
111:      * Constructor
112:      *
113:      * @param Adapter $adapter Database Adapter
114:      */
115:     public function __construct( Adapter $adapter )
116:     {
117:         $this->typeno_sqltype = array(
118:             CUBRID::C_DATATYPE_INTEGER          => ' INTEGER ',
119:             CUBRID::C_DATATYPE_DOUBLE           => ' DOUBLE ',
120:             CUBRID::C_DATATYPE_STRING           => ' STRING ',
121:             CUBRID::C_DATATYPE_SPECIAL_DATE     => ' DATE ',
122:             CUBRID::C_DATATYPE_SPECIAL_DATETIME => ' DATETIME ',
123:         );
124: 
125:         $this->sqltype_typeno = array();
126: 
127:         foreach ( $this->typeno_sqltype as $k => $v ) {
128:             $this->sqltype_typeno[trim( ( $v ) )] = $k;
129:         }
130: 
131:         $this->sqltype_typeno['STRING(1073741823)'] = self::C_DATATYPE_STRING;
132: 
133:         $this->adapter = $adapter;
134:     }
135: 
136:     /**
137:      * This method returns the datatype to be used for primary key IDS and
138:      * foreign keys. Returns one if the data type constants.
139:      *
140:      * @return integer
141:      */
142:     public function getTypeForID()
143:     {
144:         return self::C_DATATYPE_INTEGER;
145:     }
146: 
147:     /**
148:      * @see QueryWriter::getTables
149:      */
150:     public function getTables()
151:     {
152:         $rows = $this->adapter->getCol( "SELECT class_name FROM db_class WHERE is_system_class = 'NO';" );
153: 
154:         return $rows;
155:     }
156: 
157:     /**
158:      * @see QueryWriter::createTable
159:      */
160:     public function createTable( $table )
161:     {
162:         $sql  = 'CREATE TABLE '
163:             . $this->esc( $table )
164:             . ' ("id" integer AUTO_INCREMENT, CONSTRAINT "pk_'
165:             . $this->esc( $table, TRUE )
166:             . '_id" PRIMARY KEY("id"))';
167: 
168:         $this->adapter->exec( $sql );
169:     }
170: 
171:     /**
172:      * @see QueryWriter::getColumns
173:      */
174:     public function getColumns( $table )
175:     {
176:         $table = $this->esc( $table );
177: 
178:         $columnsRaw = $this->adapter->get( "SHOW COLUMNS FROM $table" );
179: 
180:         $columns = array();
181:         foreach ( $columnsRaw as $r ) {
182:             $columns[$r['Field']] = $r['Type'];
183:         }
184: 
185:         return $columns;
186:     }
187: 
188:     /**
189:      * @see QueryWriter::scanType
190:      */
191:     public function scanType( $value, $flagSpecial = FALSE )
192:     {
193:         $this->svalue = $value;
194: 
195:         if ( is_null( $value ) ) {
196:             return self::C_DATATYPE_INTEGER;
197:         }
198: 
199:         if ( $flagSpecial ) {
200:             if ( preg_match( '/^\d{4}\-\d\d-\d\d$/', $value ) ) {
201:                 return self::C_DATATYPE_SPECIAL_DATE;
202:             }
203:             if ( preg_match( '/^\d{4}\-\d\d-\d\d\s\d\d:\d\d:\d\d$/', $value ) ) {
204:                 return self::C_DATATYPE_SPECIAL_DATETIME;
205:             }
206:         }
207: 
208:         $value = strval( $value );
209: 
210:         if ( !$this->startsWithZeros( $value ) ) {
211:             if ( is_numeric( $value ) && ( floor( $value ) == $value ) && $value >= -2147483647 && $value <= 2147483647 ) {
212:                 return self::C_DATATYPE_INTEGER;
213:             }
214:             if ( is_numeric( $value ) ) {
215:                 return self::C_DATATYPE_DOUBLE;
216:             }
217:         }
218: 
219:         return self::C_DATATYPE_STRING;
220:     }
221: 
222:     /**
223:      * @see QueryWriter::code
224:      */
225:     public function code( $typedescription, $includeSpecials = FALSE )
226:     {
227:         $r = ( ( isset( $this->sqltype_typeno[$typedescription] ) ) ? $this->sqltype_typeno[$typedescription] : self::C_DATATYPE_SPECIFIED );
228: 
229:         if ( $includeSpecials ) {
230:             return $r;
231:         }
232: 
233:         if ( $r >= QueryWriter::C_DATATYPE_RANGE_SPECIAL ) {
234:             return self::C_DATATYPE_SPECIFIED;
235:         }
236: 
237:         return $r;
238:     }
239: 
240:     /**
241:      * @see QueryWriter::addColumn
242:      */
243:     public function addColumn( $type, $column, $field )
244:     {
245:         $table  = $type;
246:         $type   = $field;
247: 
248:         $table  = $this->esc( $table );
249:         $column = $this->esc( $column );
250: 
251:         $type   = array_key_exists( $type, $this->typeno_sqltype ) ? $this->typeno_sqltype[$type] : '';
252: 
253:         $this->adapter->exec( "ALTER TABLE $table ADD COLUMN $column $type " );
254:     }
255: 
256:     /**
257:      * @see QueryWriter::addUniqueIndex
258:      */
259:     public function addUniqueConstraint( $type, $properties )
260:     {
261:         $tableNoQ = $this->esc( $type, TRUE );
262:         $columns = array();
263:         foreach( $properties as $key => $column ) $columns[$key] = $this->esc( $column );
264:         $table = $this->esc( $type );
265:         sort( $columns ); // else we get multiple indexes due to order-effects
266:         $name = 'UQ_' . sha1( implode( ',', $columns ) );
267:         $sql = "ALTER TABLE $table ADD CONSTRAINT UNIQUE $name (" . implode( ',', $columns ) . ")";
268:         try {
269:             $this->adapter->exec( $sql );
270:         } catch( SQLException $e ) {
271:             return FALSE;
272:         }
273:         return TRUE;
274:     }
275: 
276:     /**
277:      * @see QueryWriter::sqlStateIn
278:      */
279:     public function sqlStateIn( $state, $list )
280:     {
281:         return ( $state == 'HY000' ) ? ( count( array_diff( array(
282:                 QueryWriter::C_SQLSTATE_INTEGRITY_CONSTRAINT_VIOLATION,
283:                 QueryWriter::C_SQLSTATE_NO_SUCH_COLUMN,
284:                 QueryWriter::C_SQLSTATE_NO_SUCH_TABLE
285:             ), $list ) ) !== 3 ) : FALSE;
286:     }
287: 
288:     /**
289:      * @see QueryWriter::addIndex
290:      */
291:     public function addIndex( $type, $name, $column )
292:     {
293:         try {
294:             $table  = $this->esc( $type );
295:             $name   = preg_replace( '/\W/', '', $name );
296:             $column = $this->esc( $column );
297:             $this->adapter->exec( "CREATE INDEX $name ON $table ($column) " );
298:             return TRUE;
299:         } catch ( SQLException $e ) {
300:             return FALSE;
301:         }
302:     }
303: 
304:     /**
305:      * @see QueryWriter::addFK
306:      */
307:     public function addFK( $type, $targetType, $property, $targetProperty, $isDependent = FALSE )
308:     {
309:         return $this->buildFK( $type, $targetType, $property, $targetProperty, $isDependent );
310:     }
311: 
312:     /**
313:      * @see QueryWriter::wipeAll
314:      */
315:     public function wipeAll()
316:     {
317:         foreach ( $this->getTables() as $t ) {
318:             foreach ( $this->getKeyMapForType( $t ) as $k ) {
319:                 $this->adapter->exec( "ALTER TABLE \"$t\" DROP FOREIGN KEY \"{$k['name']}\"" );
320:             }
321:         }
322:         foreach ( $this->getTables() as $t ) {
323:             $this->adapter->exec( "DROP TABLE \"$t\"" );
324:         }
325:     }
326: 
327:     /**
328:      * @see QueryWriter::esc
329:      */
330:     public function esc( $dbStructure, $noQuotes = FALSE )
331:     {
332:         return parent::esc( strtolower( $dbStructure ), $noQuotes );
333:     }
334: 
335:     /**
336:      * @see QueryWriter::inferFetchType
337:      */
338:     public function inferFetchType( $type, $property )
339:     {
340:         $table = $this->esc( $type, TRUE );
341:         $field = $this->esc( $property, TRUE ) . '_id';
342:         $keys = $this->getKeyMapForType( $table );
343: 
344:         foreach( $keys as $key ) {
345:             if (
346:                 $key['from'] === $field
347:             ) return $key['table'];
348:         }
349:         return NULL;
350:     }
351: }
352: