31 'bignumber' =>
'bigint',
33 'varchar' =>
'nvarchar',
37 'date' =>
'nvarchar(14)',
75 $errors = print_r(sqlsrv_errors(),
true);
76 $this->
setError(-1,
'database connect fail' . PHP_EOL . $errors);
102 if(version_compare(PHP_VERSION,
"5.4.0",
"<") && get_magic_quotes_gpc())
104 $string = stripslashes(str_replace(
"\\",
"\\\\", $string));
120 if(!$transactionLevel)
122 if(sqlsrv_begin_transaction(
$connection) ===
false)
143 $point = $transactionLevel - 1;
179 if(count($this->param))
181 foreach($this->param as $k => $o)
183 if($o->isColumnName())
187 if($o->getType() ==
'number')
189 $value = $o->getUnescapedValue();
192 $_param = array_merge($_param, $value);
196 $_param[] = $o->getUnescapedValue();
201 $value = $o->getUnescapedValue();
204 foreach($value as $v)
206 $_param[] = array($v, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STRING(
'utf-8'));
211 $_param[] = array($value, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STRING(
'utf-8'));
235 $result = sqlsrv_execute($stmt);
241 $this->
setError(print_r(sqlsrv_errors(),
true));
244 $this->param = array();
262 foreach($_param as $key => $value)
266 $value_copy = $value;
267 $value_arg = array();
268 $value_arg[] = &$value_copy[0];
269 $value_arg[] = $value_copy[1];
270 $value_arg[] = $value_copy[2];
276 $copy[$key] = $value_arg;
277 $args[$i++] = &$copy[$key];
296 $c = sqlsrv_num_fields(
$result);
303 $m = sqlsrv_field_metadata(
$result);
306 for($i = 0; $i < $c; $i++)
308 $row->{$m[$i][
'Name']} = sqlsrv_get_field(
$result, $i, SQLSRV_PHPTYPE_STRING(
'utf-8'));
310 if($arrayIndexEndValue)
312 $output[$arrayIndexEndValue--] = $row;
322 if(isset($arrayIndexEndValue))
341 $query = sprintf(
"insert into %ssequence (seq) values (ident_incr('%ssequence'))", $this->prefix, $this->prefix);
342 $this->_query($query);
344 $query = sprintf(
"select ident_current('%ssequence')+1 as sequence", $this->prefix);
345 $result = $this->_query($query);
346 $tmp = $this->_fetch($result);
349 return $tmp->sequence;
359 $query = sprintf(
"select name from sysobjects where name = '%s%s' and xtype='U'", $this->prefix, $this->addQuotes($target_name));
360 $result = $this->_query($query);
361 $tmp = $this->_fetch($result);
380 function addColumn($table_name, $column_name, $type =
'number', $size =
'', $default = null, $notnull =
false)
382 if($this->isColumnExists($table_name, $column_name))
386 $type = $this->column_type[$type];
387 if(strtoupper($type) ==
'INTEGER')
392 $query = sprintf(
"alter table %s%s add \"%s\" ", $this->prefix, $table_name, $column_name);
395 $query .= sprintf(
" %s(%s) ", $type, $size);
399 $query .= sprintf(
" %s ", $type);
404 $query .= sprintf(
" default '%s' ", $default);
408 $query .=
" not null ";
411 return $this->_query($query);
422 if(!$this->isColumnExists($table_name, $column_name))
426 $query = sprintf(
"alter table %s%s drop column \"%s\" ", $this->prefix, $table_name, $column_name);
427 $this->_query($query);
438 $query = sprintf(
"select syscolumns.name as name from syscolumns, sysobjects where sysobjects.name = '%s%s' and sysobjects.id = syscolumns.id and syscolumns.name = '%s'", $this->prefix, $table_name, $column_name);
439 $result = $this->_query($query);
444 $tmp = $this->_fetch($result);
462 function addIndex($table_name, $index_name, $target_columns, $is_unique =
false)
464 if($this->isIndexExists($table_name, $index_name))
468 if(!is_array($target_columns))
470 $target_columns = array($target_columns);
473 $query = sprintf(
"create %s index %s on %s%s (%s)", $is_unique ?
'unique' :
'', $index_name, $this->prefix, $table_name, implode(
',', $target_columns));
474 $this->_query($query);
484 function dropIndex($table_name, $index_name, $is_unique =
false)
486 if(!$this->isIndexExists($table_name, $index_name))
490 $query = sprintf(
"drop index %s%s.%s", $this->prefix, $table_name, $index_name);
491 $this->_query($query);
502 $query = sprintf(
"select sysindexes.name as name from sysindexes, sysobjects where sysobjects.name = '%s%s' and sysobjects.id = sysindexes.id and sysindexes.name = '%s'", $this->prefix, $table_name, $index_name);
504 $result = $this->_query($query);
509 $tmp = $this->_fetch($result);
525 return $this->_createTable($xml_doc);
535 if(!file_exists($file_name))
541 return $this->_createTable($buff);
557 $xml_obj = $oXml->parse($xml_doc);
559 $table_name = $xml_obj->table->attrs->name;
560 if($this->isTableExists($table_name))
565 if($table_name ==
'sequence')
567 $table_name = $this->prefix . $table_name;
568 $query = sprintf(
'create table %s ( sequence int identity(1,1), seq int )', $table_name);
569 return $this->_query($query);
573 $table_name = $this->prefix . $table_name;
575 if(!is_array($xml_obj->table->column))
577 $columns[] = $xml_obj->table->column;
581 $columns = $xml_obj->table->column;
584 $primary_list = array();
585 $unique_list = array();
586 $index_list = array();
588 $typeList = array(
'number' => 1,
'text' => 1);
589 foreach($columns as $column)
591 $name = $column->attrs->name;
592 $type = $column->attrs->type;
593 $size = $column->attrs->size;
594 $notnull = $column->attrs->notnull;
595 $primary_key = $column->attrs->primary_key;
596 $index = $column->attrs->index;
597 $unique = $column->attrs->unique;
598 $default = $column->attrs->default;
599 $auto_increment = $column->attrs->auto_increment;
601 $column_schema[] = sprintf(
'[%s] %s%s %s %s %s', $name, $this->column_type[$type], !isset($typeList[$type]) && $size ?
'(' . $size .
')' :
'', isset($default) ?
"default '" . $default .
"'" :
'', $notnull ?
'not null' :
'null', $auto_increment ?
'identity(1,1)' :
'');
605 $primary_list[] = $name;
609 $unique_list[$unique][] = $name;
613 $index_list[$index][] = $name;
617 if(count($primary_list))
619 $column_schema[] = sprintf(
"primary key (%s)",
'"' . implode($primary_list,
'","') .
'"');
622 $schema = sprintf(
'create table [%s] (%s%s)', $this->addQuotes($table_name),
"\n", implode($column_schema,
",\n"));
623 $output = $this->_query($schema);
629 if(count($unique_list))
631 foreach($unique_list as $key => $val)
633 $query = sprintf(
"create unique index %s on %s (%s);", $key, $table_name,
'[' . implode(
'],[', $val) .
']');
634 $this->_query($query);
638 if(count($index_list))
640 foreach($index_list as $key => $val)
642 $query = sprintf(
"create index %s on %s (%s);", $key, $table_name,
'[' . implode(
'],[', $val) .
']');
643 $this->_query($query);
658 $query = $this->getInsertSql($queryObject,
false);
659 $this->param = $queryObject->getArguments();
660 return $this->_query($query);
670 $query = $this->getUpdateSql($queryObject,
false);
671 $this->param = $queryObject->getArguments();
672 return $this->_query($query);
682 function getUpdateSql($query, $with_values =
true, $with_priority =
false)
684 $columnsList = $query->getUpdateString($with_values);
685 if($columnsList ==
'')
690 $from = $query->getFromString($with_values);
696 $tables = $query->getTables();
698 foreach($tables as $table)
700 $alias_list .= $table->getAlias();
702 implode(
',', explode(
' ', $alias_list));
704 $where = $query->getWhereString($with_values);
707 $where =
' WHERE ' . $where;
710 $priority = $with_priority ? $query->getPriority() :
'';
712 return "UPDATE $priority $alias_list SET $columnsList FROM " . $from . $where;
722 $query = $this->getDeleteSql($queryObject,
false);
723 $this->param = $queryObject->getArguments();
724 return $this->_query($query);
735 $with_values =
false;
742 $limitQueryPart = $query->getLimit();
745 $limitCount = $limitQueryPart->getLimit();
747 if($limitCount !=
'')
749 $limit =
'SELECT TOP ' . $limitCount;
752 $select = $query->getSelectString($with_values);
759 $select = $limit .
' ' . $select;
763 $select =
'SELECT ' . $select;
766 $from = $query->getFromString($with_values);
771 $from =
' FROM ' . $from;
773 $where = $query->getWhereString($with_values);
776 $where =
' WHERE ' . $where;
779 $groupBy = $query->getGroupByString();
782 $groupBy =
' GROUP BY ' . $groupBy;
785 $orderBy = $query->getOrderByString();
788 $orderBy =
' ORDER BY ' . $orderBy;
791 if($limitCount !=
'' && $query->limit->start > 0)
793 $order = $query->getOrder();
794 $first_columns = array();
795 foreach($order as $val)
797 $tmpColumnName = $val->getPureColumnName();
798 $first_columns[] = sprintf(
'%s(%s) as %s', $val->getPureSortOrder()==
'asc'?
'max':
'min', $tmpColumnName, $tmpColumnName);
799 $first_sub_columns[] = $tmpColumnName;
802 $first_query = sprintf(
"select %s from (select top %d %s %s %s %s %s) xet", implode(
',',$first_columns), $query->limit->start, implode(
',',$first_sub_columns), $from, $where, $groupBy, $orderBy);
803 $this->param = $query->getArguments();
804 $result = $this->__query($first_query, $connection);
805 $tmp = $this->_fetch($result);
808 foreach($order as $k => $v)
811 if(get_class($v->sort_order) ==
'SortArgument')
813 $sort_order = $v->sort_order->value;
818 $sort_order = $v->sort_order;
821 $sub_cond[] = sprintf(
"%s %s '%s'", $v->getPureColumnName(), $sort_order==
'asc'?
'>':
'<', $tmp->{$v->getPureColumnName()});
826 $sub_condition =
' WHERE ( '.implode(
' and ',$sub_cond).
' )';
830 $sub_condition =
' and ( '.implode(
' and ',$sub_cond).
' )';
833 return $select .
' ' . $from .
' ' . $where .$sub_condition.
' ' . $groupBy .
' ' . $orderBy;
846 $query = $this->getSelectSql($queryObject,
true, $connection);
848 if(strpos($query,
"substr"))
850 $query = str_replace(
"substr",
"substring", $query);
854 $this->param = $queryObject->getArguments();
856 $query .= (__DEBUG_QUERY__ & 1 &&
$output->query_id) ? sprintf(
' ' . $this->comment_syntax, $this->query_id) :
'';
857 $result = $this->_query($query, $connection);
861 return $this->queryError($queryObject);
865 return $this->queryPageLimit($queryObject, $result, $connection);
876 return new DBParser(
"[",
"]", $this->prefix);
886 $limit = $queryObject->getLimit();
887 if($limit && $limit->isPageHandler())
890 $buff->total_count = 0;
891 $buff->total_page = 0;
893 $buff->data = array();
894 $buff->page_navigation =
new PageHandler(0, 1, 1, 10);
912 $limit = $queryObject->getLimit();
913 if($limit && $limit->isPageHandler())
916 $temp_where = $queryObject->getWhereString(
true,
false);
917 $count_query = sprintf(
'select count(*) as "count" %s %s',
'FROM ' . $queryObject->getFromString(), ($temp_where ===
'' ?
'' :
' WHERE ' . $temp_where));
920 $temp_select = $queryObject->getSelectString(
true);
921 $uses_distinct = stripos($temp_select,
"distinct") !==
false;
922 $uses_groupby = $queryObject->getGroupByString() !=
'';
923 if($uses_distinct || $uses_groupby)
925 $count_query = sprintf(
'select %s %s %s %s'
927 ,
'FROM ' . $queryObject->getFromString(
true)
928 , ($temp_where ===
'' ?
'' :
' WHERE ' . $temp_where)
929 , ($uses_groupby ?
' GROUP BY ' . $queryObject->getGroupByString() :
'')
933 $count_query = sprintf(
'select count(*) as "count" from (%s) xet', $count_query);
936 $count_query .= (__DEBUG_QUERY__ & 1 && $queryObject->queryID) ? sprintf(
' ' . $this->comment_syntax, $this->query_id) :
'';
937 $this->param = $queryObject->getArguments();
938 $result_count = $this->_query($count_query, $connection);
939 $count_output = $this->_fetch($result_count);
940 $total_count = (int) $count_output->count;
942 $list_count = $limit->list_count->getValue();
947 $page_count = $limit->page_count->getValue();
952 $page = $limit->page->getValue();
953 if(!$page || $page < 1)
960 $total_page = (int) (($total_count - 1) / $list_count) + 1;
968 if($page > $total_page)
973 $buff->total_count = $total_count;
974 $buff->total_page = $total_page;
976 $buff->data = array();
977 $buff->page_navigation =
new PageHandler($total_count, $total_page, $page, $page_count);
980 if($queryObject->usesClickCount())
982 $update_query = $this->getClickCountQuery($queryObject);
983 $this->_executeUpdateAct($update_query);
987 $start_count = ($page - 1) * $list_count;
988 $this->param = $queryObject->getArguments();
989 $virtual_no = $total_count - $start_count;
990 $data = $this->_fetch($result, $virtual_no);
993 $buff->total_count = $total_count;
994 $buff->total_page = $total_page;
997 $buff->page_navigation =
new PageHandler($total_count, $total_page, $page, $page_count);
1001 $data = $this->_fetch($result);
1003 $buff->data = $data;
setError($errno=0, $errstr= 'success')
__query($query, $connection)
_executeUpdateAct($queryObject)
isTableExists($target_name)
createTableByXmlFile($file_name)
_executeSelectAct($queryObject, $connection=null)
isConnected($type= 'master', $indx=0)
addColumn($table_name, $column_name, $type= 'number', $size= '', $default=null, $notnull=false)
addIndex($table_name, $index_name, $target_columns, $is_unique=false)
queryPageLimit($queryObject, $result, $connection)
__construct($auto_connect=TRUE)
_executeDeleteAct($queryObject)
_executeInsertAct($queryObject)
_rollback($transactionLevel=0)
isIndexExists($table_name, $index_name)
createTableByXml($xml_doc)
_begin($transactionLevel=0)
getSelectSql($query, $with_values=TRUE, $connection=NULL)
dropIndex($table_name, $index_name, $is_unique=false)
_connect($type= 'master', $indx=0)
_getConnection($type= 'master', $indx=NULL)
_query($query, $connection=NULL)
getUpdateSql($query, $with_values=true, $with_priority=false)
_fetch($result, $arrayIndexEndValue=NULL)
_getParametersByReference($_param)
dropColumn($table_name, $column_name)
isColumnExists($table_name, $column_name)