books - git.Jacekk.net

Jacek Kowalski

2017-08-28 adfb2d2afab2b017db7006fcb7808d66aa2ef411

commit \| author \| age
175a52	1	<?php
JK	2	class sql {
	3	static $db;
	4	static $queries = 0;
	5
381239	6	static $PDO;
JK	7
175a52	8	static function connect() {
JK	9	}
	10
	11	static function query($q) {
381239	12	if(!self::$PDO) {
JK	13	self::$PDO = PDOO::Singleton();
175a52	14	}
JK	15
	16	self::$queries++;
	17
381239	18	return self::$PDO->query($q);
175a52	19	}
JK	20
	21	static function fetchonea($q) {
381239	22	return $q->fetch(PDO::FETCH_ASSOC);
175a52	23	}
JK	24
	25	static function fetchone($q) {
381239	26	return $q->fetch(PDO::FETCH_ASSOC);
175a52	27	}
JK	28
	29	static function fetch($q) {
381239	30	return $q->fetchAll();
175a52	31	}
JK	32
	33	static function increment_id() {
381239	34	if(!self::$PDO) {
JK	35	self::$PDO = PDOO::Singleton();
	36	}
	37
	38	return self::$PDO->lastInsertId();
175a52	39	}
JK	40
381239	41	static function affected($q = NULL) {
JK	42	if($q === NULL) {
	43	return 'nieznana';
	44	}
	45
	46	return $q->rowCount();
175a52	47	}
JK	48
	49	static function escape($q) {
381239	50	if(!self::$PDO) {
JK	51	self::$PDO = PDOO::Singleton();
175a52	52	}
JK	53
381239	54	return self::$PDO->quote($q);
175a52	55	}
JK	56
	57	static function close() {
	58	mysql_close(self::$db);
	59	}
	60	}
	61
	62	class db2 {
	63	// FALSE - Allows WHERE queries other than "is equal"
	64	// LIKE, MATCH ... AGAINST, !=, >, <, IS NULL ...
	65	static $SAFE_MODE_WHERE = FALSE;
	66
	67	// FALSE - Allows SELECT *
	68	static $SAFE_MODE_SELECT = FALSE;
	69
	70	// FALSE - Skipping empty fields in INSERTs
	71	static $SAFE_MODE_INSERT = FALSE;
	72
	73	// FALSE - Empty field is stated as NULL
	74	static $SAFE_MODE_UPDATE = FALSE;
	75
	76	// FALSE - Allows JOINs
	77	static $SAFE_MODE_TABLE = FALSE;
	78
	79	// FALSE - Allow `table`.`key` using table.key
	80	static $SAFE_MODE_KEY = FALSE;
	81
	82	// Do htmlspecialchars() on db2::get results?
	83	static $ESCAPE_DATA = TRUE;
	84
	85	static $revelance = FALSE;
	86
	87	static function revelance() {
	88	return (bool)self::$revelance;
	89	}
	90
	91	static function __combine_insert($keys) {
	92	foreach($keys as $key => $value) {
	93	if(empty($key)) {
	94	continue;
	95	}
	96
	97	if(!self::$SAFE_MODE_INSERT AND $value==='') {
	98	continue;
	99	}
	100
381239	101	$a[] = '`'.$key.'`';
JK	102	$b[] = sql::escape($value);
175a52	103	}
JK	104
	105	return '('.implode(', ', $a).') VALUES ('.implode(', ', $b).')';
	106	}
	107
	108	static function __combine_update($keys) {
	109	foreach($keys as $key => $value) {
	110	if(empty($key)) {
	111	continue;
	112	}
	113
	114	if(!self::$SAFE_MODE_UPDATE AND $value==='') {
	115	$value = NULL;
	116	}
	117	if(is_null($value)) {
85c65e	118	$a[] = '`'.$key.'`=NULL';
175a52	119	}
JK	120	else
	121	{
381239	122	$a[] = '`'.$key.'`='.sql::escape($value);
175a52	123	}
JK	124	}
	125
	126	return implode(', ', $a);
	127	}
	128
	129	static function __combine_select($keys, $revelance=FALSE) {
	130	if(!self::$SAFE_MODE_SELECT && $keys=='*') {
	131	$a[] = '*';
	132	}
	133	else
	134	{
	135	if(!is_array($keys)) {
	136	$keys = array($keys);
	137	}
	138
	139	foreach($keys as $v) {
	140	$a[] = self::__combine_keyn($v);
	141	if($one) break;
	142	}
	143
	144	}
	145
	146	if($revelance && self::$revelance) {
	147	$a[] = self::$revelance;
	148	}
	149
	150	return implode(', ', $a);
	151	}
	152
	153	static function __combine_where($keys, $revelance=FALSE) {
	154	self::$revelance = FALSE;
	155
	156	$implode = ' AND ';
	157
	158	if(!is_array($keys) OR empty($keys)) {
	159	return '';
	160	}
	161
	162	if(self::$SAFE_MODE_WHERE) {
	163	foreach($keys as $key => $value) {
	164	if(is_null($value)) {
	165	$a[] = self::__combine_keyn($key).' IS NULL';
	166	}
	167	else
	168	{
381239	169	$a[] = self::__combine_keyn($key).'='.sql::escape($value);
175a52	170	}
JK	171	}
	172	}
	173	else
	174	{
	175	$a = array();
	176	foreach($keys as $key => $v) {
	177	if(!is_array($v)) {
	178	$v = array($v);
	179	}
	180
	181	foreach($v as $value) {
	182	if($key === 'OR') {
	183	$implode = ' OR ';
	184	}
	185	elseif(substr($key, -1)=='!' AND is_null($value) OR $value==='') {
	186	$a[] = self::__combine_keyn(substr($key, 0, -1)).' IS NOT NULL';
	187	}
	188	elseif(is_null($value) OR $value==='') {
	189	$a[] = self::__combine_keyn($key).' IS NULL';
	190	}
	191	elseif(substr($key, -1)=='!') {
381239	192	$a[] = self::__combine_keyn(substr($key, 0, -1)).'!='.sql::escape($value);
175a52	193	}
JK	194	elseif($key=='^') {
	195	$a[] = 'MAX('.self::__combine_keyn($value).')';
	196	}
	197	elseif(substr($key, -2)=='~~') {
381239	198	$temp = 'MATCH ('.self::__combine_keyn(substr($key, 0, -2)).') AGAINST ('.sql::escape($value).')';
175a52	199	if($revelance) {
JK	200	self::$revelance = $temp.' AS `revelance`';
	201	}
	202
	203	$a[] = $temp;
	204	}
	205	elseif(substr($key, -1)=='~') {
381239	206	$a[] = self::__combine_keyn(substr($key, 0, -1)).' LIKE '.sql::escape($value);
175a52	207	}
JK	208	elseif(substr($key, -2)=='>=') {
381239	209	$a[] = self::__combine_keyn(substr($key, 0, -2)).'>='.sql::escape($value);
175a52	210	}
JK	211	elseif(substr($key, -2)=='<=') {
381239	212	$a[] = self::__combine_keyn(substr($key, 0, -2)).'<='.sql::escape($value);
175a52	213	}
JK	214	elseif(substr($key, -1)=='>') {
381239	215	$a[] = self::__combine_keyn(substr($key, 0, -1)).'>'.sql::escape($value);
175a52	216	}
JK	217	elseif(substr($key, -1)=='<') {
381239	218	$a[] = self::__combine_keyn(substr($key, 0, -1)).'<'.sql::escape($value);
175a52	219	}
JK	220	else
	221	{
381239	222	$a[] = self::__combine_keyn($key).'='.sql::escape($value);
175a52	223	}
JK	224	}
	225	}
	226	}
	227
	228	return ' WHERE '.implode($implode, $a).$addon;
	229	}
	230
	231	static function __combine_limit($limit, $stop) {
	232	if(!ctype_digit($limit) AND !is_int($limit)) {
	233	return '';
	234	}
	235	elseif(!ctype_digit($stop) AND !is_int($stop)) {
	236	return ' LIMIT '.$limit;
	237	}
	238	else
	239	{
	240	return ' LIMIT '.$limit.', '.$stop;
	241	}
	242	}
	243
	244	static function __combine_order($keys, $revelance=FALSE) {
	245	if(empty($keys)) {
	246	return '';
	247	}
	248
	249	if(!is_array($keys)) {
	250	$keys = array($keys => 'ASC');
	251	}
	252
	253	foreach($keys as $key => $value) {
	254	$bvalue = strtoupper($value);
	255	if($bvalue != 'ASC' AND $bvalue != 'DESC') {
	256	$key = $value;
	257	$value = 'ASC';
	258	}
	259	else
	260	{
	261	$value = $bvalue;
	262	}
	263
	264	if((!$revelance OR !self::$revelance) AND $key=='revelance') {
	265	continue;
	266	}
	267
	268	$a[] = self::__combine_keyn($key).' '.$value;
	269	}
	270
	271	return ' ORDER BY '.implode(', ', $a);
	272	}
	273
	274	private static function __combine_tablelist($key, $value) {
	275	$as = FALSE;
	276	if(!is_int($key)) {
	277	$as = $value;
	278	$value = $key;
	279	}
381239	280	return '`'.$value.'`'.($as ? ' AS `'.$as.'`' : '');
175a52	281	}
JK	282
	283	static function __combine_keyn($key) {
	284	if(!self::$SAFE_MODE_KEY AND strpos($key, '.')!==FALSE) {
	285	$key = explode('.', $key, 2);
381239	286	return '`'.$key[0].'`.`'.$key[1].'`';
175a52	287	}
JK	288
381239	289	return '`'.$key.'`';
175a52	290	}
JK	291
	292	static function __combine_table($table) {
	293	if(!is_array($table) OR self::$SAFE_MODE_TABLE) {
381239	294	return '`'.$table.'` ';
175a52	295	}
JK	296	else
	297	{
	298	$ret = array();
	299	foreach($table as $key => $value) {
	300	if(empty($value)) {
	301	continue;
	302	}
	303
	304	if(!is_array($value)) {
	305	$ret[] = self::__combine_tablelist($key, $value);
	306	}
	307	else
	308	{
	309	switch(strtoupper($value[0])) {
	310	case 'L':
	311	$sub = 'LEFT JOIN ';
	312	break;
	313	case 'R':
	314	$sub = 'RIGHT JOIN ';
	315	break;
	316	case 'J':
	317	$sub = 'JOIN ';
	318	break;
	319	case 'S':
	320	$sub = 'STRAIGHT_JOIN ';
	321	break;
	322	default:
	323	continue 2;
	324	break;
	325	}
	326
	327	if(!is_array($value[1])) {
	328	$value[1] = array($value[1]);
	329	}
	330	foreach($value[1] as $k => $v) {
	331	$sub1[] = self::__combine_tablelist($k, $v);
	332	}
	333	$sub .= implode(', ', $sub1).' ';
	334
	335	if(strtoupper($value[2])=='USING') {
	336	$sub .= 'USING ('.self::__combine_select($value[3]).')';
	337	}
	338	elseif(strtoupper($value[2])=='ON') {
	339	$sub .= 'ON '.substr(self::__combine_where($value[3]), 7);
	340	}
	341
	342	if($value[4]) {
	343	$sub .= ' HAVING '.self::__combine_where($value[4]);
	344
	345	}
	346
	347	$ret[] = $sub;
	348	}
	349	}
	350
	351	return implode(' ', $ret).' ';
	352	}
	353	}
	354
	355	static function escape_data($data) {
	356	if(self::$ESCAPE_DATA) {
	357	if(is_array($data)) {
	358	foreach($data as &$value) {
	359	$value = self::escape_data($value);
	360	}
	361	}
	362	elseif(is_string($data)) {
	363	$data = htmlspecialchars($data);
	364	}
	365	}
	366
	367	return $data;
	368	}
	369
	370	static function get($table, $keys, $where=NULL, $order=NULL, $limit=NULL, $stop=NULL, $revelance=FALSE) {
	371	$where = self::__combine_where($where, $revelance);
	372	$keys = self::__combine_select($keys, $revelance);
	373
	374	$q = sql::query('SELECT '.$keys.' FROM '.self::__combine_table($table).$where.self::__combine_order($order, $revelance).self::__combine_limit($limit, $stop));
	375	return self::escape_data(sql::fetch($q));
	376	}
	377
	378	static function getone($table, $keys, $where=NULL, $order=NULL, $limit=NULL, $stop=NULL, $revelance=FALSE) {
	379	$where = self::__combine_where($where, $revelance);
	380	$keys = self::__combine_select($keys, $revelance);
	381
	382	$q = sql::query('SELECT '.$keys.' FROM '.self::__combine_table($table).$where.self::__combine_order($order, $revelance).self::__combine_limit($limit, $stop));
	383	return self::escape_data(sql::fetchone($q));
	384	}
	385
	386	static function num($table, $key, $where=NULL) {
	387	$q = sql::query('SELECT COUNT('.self::__combine_select($key, $one).') AS `num` FROM '.self::__combine_table($table).self::__combine_where($where));
	388	$r = sql::fetchone($q);
	389	return $r['num'];
	390	}
	391
	392	static function add($table, $keys) {
381239	393	sql::query('INSERT INTO `'.$table.'` '.self::__combine_insert($keys));
175a52	394	return sql::affected();
JK	395	}
	396
	397	static function edit($table, $keys, $where=NULL, $order=NULL, $limit=NULL, $stop=NULL) {
381239	398	sql::query('UPDATE `'.$table.'` SET '.self::__combine_update($keys).self::__combine_where($where).self::__combine_order($order).self::__combine_limit($limit, $stop));
175a52	399	return sql::affected();
JK	400	}
	401
	402	static function del($table, $where=NULL, $order=NULL, $limit=NULL, $stop=NULL) {
381239	403	sql::query('DELETE FROM `'.$table.'`'.self::__combine_where($where).self::__combine_order($order).self::__combine_limit($limit, $stop));
175a52	404	return sql::affected();
JK	405	}
	406
	407	static function last_id() {
	408	return sql::increment_id();
	409	}
	410	}
532779	411	?>