PageRenderTime 53ms CodeModel.GetById 21ms RepoModel.GetById 1ms app.codeStats 0ms

/lib/yadal/class.MSSQL.php

https://github.com/reshadf/Library
PHP | 582 lines | 354 code | 51 blank | 177 comment | 19 complexity | 66e363a856180134a5a8e5acab6422bf MD5 | raw file
Possible License(s): LGPL-2.1
  1. <?php
  2. /**
  3. * Yadal interface for the MSSQL (Microsoft SQL Server) Database type
  4. *
  5. * @package Yadal
  6. */
  7. /**
  8. * class MSSQL
  9. *
  10. * Yadal - Yet Another Database Abstraction Layer
  11. * Microsoft SQL Server (MSSQL) class
  12. *
  13. * @author Teye Heimans
  14. * @package Yadal
  15. */
  16. class MSSQL extends Yadal
  17. {
  18. /**
  19. * MSSQL::MSSQL()
  20. *
  21. * Constructor
  22. *
  23. * @author Teye Heimans
  24. */
  25. function MSSQL( $db )
  26. {
  27. $this->Yadal( $db );
  28. $this->_nameQuote = array('[',']');
  29. }
  30. /**
  31. * MSSQL::connect()
  32. *
  33. * Make a connection with the database and
  34. * select the database.
  35. *
  36. * @param string $servername: the server to connect to
  37. * @param string $username: the username which should be used to login
  38. * @param string $password: the password which should be used to login
  39. * @return resource: The connection resource or false on failure
  40. * @access public
  41. * @author Teye Heimans
  42. */
  43. function connect( $servername = '', $username = '', $password = '' )
  44. {
  45. // try to connect
  46. $this->_conn = mssql_connect( $servername, $username, $password );
  47. if( ! $this->_conn )
  48. {
  49. return false;
  50. }
  51. // select the database
  52. if( mssql_select_db( $this->_db, $this->_conn ) )
  53. {
  54. $this->_isConnected = true;
  55. // return the connection resource
  56. return $this->_conn;
  57. }
  58. return false;
  59. }
  60. /**
  61. * MSSQL::close()
  62. *
  63. * Close the connection
  64. *
  65. * @return bool
  66. * @access public
  67. * @author Teye Heimans
  68. */
  69. function close()
  70. {
  71. if( $this->_isConnected )
  72. {
  73. $this->_isConnected = false;
  74. return mssql_close( $this->_conn );
  75. }
  76. }
  77. /**
  78. * MSSQL::query()
  79. *
  80. * Execute the query
  81. *
  82. * @param string $query: the query
  83. * @return resource
  84. * @access public
  85. * @author Teye Heimans
  86. */
  87. function query( $query )
  88. {
  89. $this->_lastQuery = $query;
  90. // execute the query
  91. $sql = mssql_query( $query );
  92. return $sql;
  93. }
  94. /**
  95. * MSSQL::result()
  96. *
  97. * Return a specific result of a sql resource
  98. *
  99. * @param resource $result: The sql result where you want to get a result from
  100. * @param int $row: The row where you want a result from
  101. * @param string $field: The field which result you want
  102. * @return string
  103. * @access public
  104. * @author Teye Heimans
  105. */
  106. function result( $result, $row = 0, $field = null )
  107. {
  108. return mssql_result( $result, $row, $field);
  109. }
  110. /**
  111. * MSSQL::getInsertId()
  112. *
  113. * Get the id of the last inserted record
  114. *
  115. * @param string $table: the table which last inserted id should be returned from
  116. * @return int
  117. * @access public
  118. * @author Teye Heimans
  119. */
  120. function getInsertId( $table )
  121. {
  122. $sql = mssql_query( "SELECT IDENT_CURRENT('".$table."')" );
  123. if( $sql )
  124. {
  125. list($id) = mssql_fetch_row($sql);
  126. return $id;
  127. }
  128. else
  129. {
  130. trigger_error(
  131. "Could not fetch the last inserted id for the table '".$table."'.\n".
  132. "Query: ".$this->getLastQuery()."\n".
  133. "Error: ".$this->getError(),
  134. E_USER_WARNING
  135. );
  136. return false;
  137. }
  138. }
  139. /**
  140. * MSSQL::getError()
  141. *
  142. * Return the last error
  143. *
  144. * @return string
  145. * @access public
  146. * @author Teye Heimans
  147. */
  148. function getError()
  149. {
  150. $error = mssql_get_last_message();
  151. if ($error == '')
  152. {
  153. $error = "General Error (The MS-SQL interface did not return a detailed error message).";
  154. }
  155. return $error;
  156. }
  157. /**
  158. * MSSQL::recordCount()
  159. *
  160. * Return the number of records found by the query
  161. *
  162. * @param resource $sql: the sql resource which we should count
  163. * @return int
  164. * @access public
  165. * @author Teye Heimans
  166. */
  167. function recordCount( $sql)
  168. {
  169. return mssql_num_rows( $sql );
  170. }
  171. /**
  172. * MSSQL::getRecord()
  173. *
  174. * Fetch a record in assoc mode and return it
  175. *
  176. * @return assoc array or false when there are no records left
  177. * @access public
  178. * @author Teye Heimans
  179. */
  180. function getRecord( $sql )
  181. {
  182. return mssql_fetch_assoc( $sql );
  183. }
  184. /**
  185. * MSSQL::getFieldNames()
  186. *
  187. * Return the field names of the table
  188. *
  189. * @param string $table: if a table is given, this one is used. otherwise the default is used
  190. * @return array
  191. * @access public
  192. * @author Teye Heimans
  193. */
  194. function getFieldNames( $table )
  195. {
  196. $t = strtolower($table);
  197. // return the data from the cache if it exists
  198. if( isset( $this->_cache['fields'][$t] ) )
  199. {
  200. return $this->_cache['fields'][$t];
  201. }
  202. // get the field names
  203. $sql = $this->query("
  204. SELECT column_name fld
  205. FROM information_schema.columns
  206. WHERE table_name = '".$table."'
  207. ORDER BY ordinal_position"
  208. );
  209. // query failed ?
  210. if( ! $sql )
  211. {
  212. trigger_error(
  213. "Could not fetch fieldnames of the table '".$table."'.\n".
  214. "Query: ".$this->getLastQuery()."\n".
  215. "Error: ".$this->getError(),
  216. E_USER_WARNING
  217. );
  218. return false;
  219. }
  220. // save the fields in an array and return it
  221. $result = array();
  222. while ( $row = $this->getRecord( $sql) )
  223. {
  224. $result[] = $row['fld'];
  225. }
  226. mssql_free_result($sql);
  227. // save the result in the cache
  228. $this->_cache['fields'][$t] = $result;
  229. return $result;
  230. }
  231. /**
  232. * MSSQL::getTables()
  233. *
  234. * Return the tables from the database
  235. *
  236. * @param bool $showViews: should we also return the views ? (default true )
  237. * @return array
  238. * @access public
  239. * @author Teye Heimans
  240. */
  241. function getTables($showViews = true)
  242. {
  243. // return the data from the cache if it exists
  244. if( isset( $this->_cache['tables'] ) )
  245. {
  246. return $this->_cache['tables'];
  247. }
  248. $sql = $this->query("
  249. SELECT name
  250. FROM sysobjects
  251. WHERE type='U' ".($showViews ? " OR type='V' ":"")." AND
  252. (name not in
  253. ('sysallocations','syscolumns','syscomments','sysdepends',
  254. 'sysfilegroups','sysfiles','sysfiles1','sysforeignkeys','dtproperties',
  255. 'sysfulltextcatalogs','sysindexes','sysindexkeys','sysmembers',
  256. 'sysobjects','syspermissions','sysprotects','sysreferences',
  257. 'systypes','sysusers','sysalternates','sysconstraints',
  258. 'syssegments','REFERENTIAL_CONSTRAINTS','CHECK_CONSTRAINTS',
  259. 'CONSTRAINT_TABLE_USAGE','CONSTRAINT_COLUMN_USAGE','VIEWS',
  260. 'VIEW_TABLE_USAGE','VIEW_COLUMN_USAGE','SCHEMATA','TABLES',
  261. 'TABLE_CONSTRAINTS','TABLE_PRIVILEGES','COLUMNS',
  262. 'COLUMN_DOMAIN_USAGE','COLUMN_PRIVILEGES','DOMAINS',
  263. 'DOMAIN_CONSTRAINTS','KEY_COLUMN_USAGE'))"
  264. );
  265. // query failed ?
  266. if( !$sql )
  267. {
  268. trigger_error(
  269. "Could not retrieve the tables from the database!\n".
  270. "Query: ".$this->getLastQuery()."\n".
  271. "Error: ".$this->getError(),
  272. E_USER_WARNING
  273. );
  274. return false;
  275. }
  276. // save the table names in an array and return them
  277. $result = array();
  278. $num = $this->recordCount( $sql );
  279. for( $i = 0; $i < $num; $i++ )
  280. {
  281. $result[] = $this->result( $sql, $i);
  282. }
  283. // save the result in the cache
  284. $this->_cache['tables'] = $result;
  285. return $result;
  286. }
  287. /**
  288. * MSSQL::getNotNullFields()
  289. *
  290. * Retrieve the fields that can not contain NULL
  291. *
  292. * @param string $table: The table which fields we should retrieve
  293. * @return array
  294. * @access public
  295. * @author Teye Heimans
  296. */
  297. function getNotNullFields ( $table )
  298. {
  299. $t = strtolower($table);
  300. // return the data from the cache if it exists
  301. if( isset( $this->_cache['notnull'][$t] ) )
  302. {
  303. return $this->_cache['notnull'][$t];
  304. }
  305. // get the not null fields
  306. $sql = $this->query("
  307. SELECT
  308. column_name fld
  309. FROM
  310. information_schema.columns
  311. WHERE
  312. table_name = '".$table."' AND
  313. is_nullable = 'No'"
  314. );
  315. // query failed ?
  316. if( ! $sql )
  317. {
  318. trigger_error(
  319. "Could not fetch the not nullable fields of the table '".$table."'.\n".
  320. "Query: ".$this->getLastQuery()."\n".
  321. "Error: ".$this->getError(),
  322. E_USER_WARNING
  323. );
  324. return false;
  325. }
  326. // save the fields in an array and return it
  327. $result = array();
  328. while ( $row = $this->getRecord( $sql) )
  329. {
  330. $result[] = $row['fld'];
  331. }
  332. mssql_free_result($sql);
  333. // save the result in the cache
  334. $this->_cache['notnull'][$t] = $result;
  335. return $result;
  336. }
  337. /**
  338. * MSSQL::getFieldTypes()
  339. *
  340. * Retrieve the field types of the given table
  341. *
  342. * @param string $table: The table where we should fetch the fields and their types from
  343. * @return array
  344. * @access public
  345. * @author Teye Heimans
  346. */
  347. function getFieldTypes( $table )
  348. {
  349. $t = strtolower($table);
  350. // return the data from the cache if it exists
  351. if( isset( $this->_cache['fieldtypes'][$t] ) )
  352. {
  353. return $this->_cache['fieldtypes'][$t];
  354. }
  355. // get the meta data
  356. $sql = $this->query("
  357. SELECT
  358. c.name fld,
  359. t.name type,
  360. c.length
  361. FROM syscolumns c
  362. JOIN systypes t ON t.xusertype = c.xusertype
  363. JOIN sysobjects o ON o.id = c.id
  364. WHERE o.name='".$table."'"
  365. );
  366. // query failed ?
  367. if( !$sql )
  368. {
  369. trigger_error(
  370. "Could not fetch the meta data of the columns for table '".$table."'.\n".
  371. "Query: ".$this->getLastQuery()."\n".
  372. "Error: ".$this->getError(),
  373. E_USER_WARNING
  374. );
  375. return false;
  376. }
  377. // save the result in an array
  378. // TODO: load the default values in the 3rd place in the array
  379. $result = array();
  380. while( $row = $this->getRecord( $sql ) )
  381. {
  382. $result[ $row['fld'] ] = array(
  383. $row['type'],
  384. $row['length'],
  385. null // default value
  386. );
  387. }
  388. // save the result in the cache
  389. $this->_cache['fieldtypes'][$t] = $result;
  390. return $result;
  391. }
  392. /**
  393. * MSSQL::escapeString()
  394. *
  395. * Escape the string we are going to save from dangerous characters
  396. *
  397. * @param string $string
  398. * @return string
  399. * @access public
  400. * @author Teye Heimans
  401. */
  402. function escapeString( $string )
  403. {
  404. return preg_replace("'","''",$string);
  405. }
  406. /**
  407. * MSSQL::getPrKeys()
  408. *
  409. * Fetch the keys from the table
  410. *
  411. * @param string $table: the table where we should fetch the keys from
  412. * @return array of the keys which are found
  413. * @access public
  414. * @author Teye Heimans
  415. */
  416. function getPrKeys( $table )
  417. {
  418. $t = strtolower($table);
  419. // return the data from the cache if it exists
  420. if( isset( $this->_cache['keys'][$t] ) )
  421. {
  422. return $this->_cache['keys'][$t];
  423. }
  424. // get the primary keys
  425. $sql = $this->query("
  426. SELECT
  427. k.column_name fld
  428. FROM
  429. information_schema.key_column_usage k,
  430. information_schema.table_constraints tc
  431. WHERE
  432. tc.constraint_name = k.constraint_name AND
  433. tc.constraint_type = 'PRIMARY KEY' AND
  434. k.table_name = '".$table."'"
  435. );
  436. // query failed ?
  437. if( ! $sql )
  438. {
  439. trigger_error(
  440. "Could not fetch the primary keys for the table '".$table."'.\n".
  441. "Query: ".$this->getLastQuery()."\n".
  442. "Error: ".$this->getError(),
  443. E_USER_WARNING
  444. );
  445. return false;
  446. }
  447. // get the fields, put them into an array and return them
  448. $result = array();
  449. while( $row = $this->getRecord( $sql ) )
  450. {
  451. $result[] = $row['fld'];
  452. }
  453. mssql_free_result($sql);
  454. // save the result in the cache
  455. $this->_cache['keys'][$t] = $result;
  456. return $result;
  457. }
  458. /**
  459. * MSSQL::getUniqueFields()
  460. *
  461. * Fetch the unique fields from the table
  462. *
  463. * @param string $table: the table where we should fetch the unique fields from
  464. * @return array of the keys which are found
  465. * @access public
  466. * @author Teye Heimans
  467. */
  468. function getUniqueFields( $table )
  469. {
  470. $t = strtolower( $table );
  471. // return the data from the cache if it exists
  472. if( isset( $this->_cache['unique'][$t] ) )
  473. {
  474. return $this->_cache['unique'][$t];
  475. }
  476. // fetch the unique fields
  477. $sql = $this->query("
  478. SELECT
  479. t1.constraint_name con,
  480. t2.column_name fld
  481. FROM
  482. information_schema.table_constraints t1,
  483. information_schema.constraint_column_usage t2
  484. WHERE
  485. t1.table_name = t2.table_name AND
  486. t1.constraint_name = t2.constraint_name AND
  487. (t1.constraint_type = 'UNIQUE' OR
  488. t1.constraint_type = 'PRIMARY KEY') AND
  489. t1.table_name = '".$table."'"
  490. );
  491. // query failed ?
  492. if( ! $sql )
  493. {
  494. trigger_error(
  495. "Could not fetch the unique fields for the table '".$table."'.\n".
  496. "Query: ".$this->getLastQuery()."\n".
  497. "Error: ".$this->getError(),
  498. E_USER_WARNING
  499. );
  500. return false;
  501. }
  502. // put the unique fields into an array and return them
  503. $result = array();
  504. while( $row = $this->getRecord( $sql ) )
  505. {
  506. $result[$row['con']][] = $row['fld'];
  507. }
  508. mssql_free_result($sql);
  509. // save the result in the cache
  510. $this->_cache['unique'][$t] = $result;
  511. return $result;
  512. }
  513. }
  514. ?>