From 38123974efb3fab9a5dc103e1adb45f8ed928ead Mon Sep 17 00:00:00 2001
From: Jacek Kowalski <Jacek@jacekk.info>
Date: Tue, 12 Feb 2013 13:31:23 +0000
Subject: [PATCH] 1. Zmiana modułu do połączeń z MySQL z mysql na pdo_mysql. 2. Rozdzielenie tabeli pozycz na dwie: pozycz i pozycz_historia. 3. Wydzielenie pokazywania historii wypożyczeń do klasy gotowe. 4. Zmiany w wielu miejscach w kodzie związane z tabelą pozycz. 5. Poprawki w pliku test.php w związku z w/w zmianami. 6. Dodanie pliku UPGRADE z instrukcją aktualizacji.
---
biblioteka.sql | 13 ++
CHANGELOG | 6 +
includes/gotowe.php | 20 ++++
borrow_history.php | 39 -------
includes/db2.php | 113 ++++++++--------------
includes/ksiazki.php | 16 +--
UPGRADE | 26 +++++
test.php | 11 +
includes/PDOO.php | 24 ++++
includes/pozycz.php | 18 +-
10 files changed, 154 insertions(+), 132 deletions(-)
diff --git a/CHANGELOG b/CHANGELOG
index 55a2bb9..92301af 100644
--- a/CHANGELOG
+++ b/CHANGELOG
@@ -2,6 +2,12 @@
1.4 Stable
- Przeniesienie ustawień skryptu do osobnego pliku (includes/config.php)
+- Zmiana modułu do połączeń z MySQL z mysql na pdo_mysql
+- Rozdzielenie tabeli pozycz na dwie: pozycz i pozycz_historia
+- Wydzielenie pokazywania historii wypożyczeń do klasy gotowe
+- Zmiany w wielu miejscach w kodzie związane z tabelą pozycz
+- Poprawki w pliku test.php w związku z w/w zmianami
+- Dodanie pliku UPGRADE z instrukcją aktualizacji
1.3 Stable
- Poprawienie okładek - getimagesize() zwraca IMAGETYPE_XXX, a nie IMG_XXX
diff --git a/UPGRADE b/UPGRADE
new file mode 100644
index 0000000..298d2f4
--- /dev/null
+++ b/UPGRADE
@@ -0,0 +1,26 @@
+=================================================
+ Aktualizacja wersji 1.3 Stable i wcześniejszych
+=================================================
+
+- zapisz następujące informacje: dane połączenia z bazą danych,
+ klucz API LibraryThing
+- wgraj wszystkie zmienione pliki,
+- w pliku /includes/config.php podaj zapisane wcześniej dane,
+- wykonaj w bazie danych poniższe zapytania:
+
+CREATE TABLE `pozycz_historia` (
+ `id` mediumint(8) unsigned NOT NULL,
+ `kto` varchar(255) COLLATE utf8_polish_ci NOT NULL,
+ `od` int(11) NOT NULL,
+ `do` int(11) NOT NULL,
+ PRIMARY KEY (`id`,`od`)
+);
+
+INSERT INTO `pozycz_historia` (`id`, `kto`, `od`, `do`)
+ SELECT `id`, `kto`, `od`, `do` FROM `pozycz` WHERE `do` IS NOT NULL;
+DELETE FROM `pozycz` WHERE `do` IS NOT NULL;
+
+ALTER TABLE `pozycz` DROP INDEX `do`;
+ALTER TABLE `pozycz` DROP COLUMN `do`;
+
+ALTER TABLE `pozycz` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`);
diff --git a/biblioteka.sql b/biblioteka.sql
index 9ae75c4..c36324e 100644
--- a/biblioteka.sql
+++ b/biblioteka.sql
@@ -10,9 +10,9 @@
`ISBN` decimal(13,0) unsigned DEFAULT NULL,
`ISSN` decimal(13,0) unsigned DEFAULT NULL,
`regal` char(5) COLLATE utf8_polish_ci DEFAULT NULL,
- `polka` tinyint(4) DEFAULT NULL,
- `rzad` tinyint(4) DEFAULT NULL,
- `wycofana` char(1) COLLATE utf8_polish_ci NOT NULL DEFAULT '0',
+ `polka` decimal(2,0) unsigned DEFAULT NULL,
+ `rzad` decimal(2,0) unsigned DEFAULT NULL,
+ `wycofana` enum('0','1') CHARACTER SET ascii NOT NULL DEFAULT '0',
`powod` varchar(255) COLLATE utf8_polish_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ISBN` (`ISBN`),
@@ -27,6 +27,13 @@
`id` mediumint(8) unsigned NOT NULL,
`kto` varchar(255) COLLATE utf8_polish_ci NOT NULL,
`od` int(11) NOT NULL,
+ PRIMARY KEY (`id`,`od`)
+);
+
+CREATE TABLE `pozycz_historia` (
+ `id` mediumint(8) unsigned NOT NULL,
+ `kto` varchar(255) COLLATE utf8_polish_ci NOT NULL,
+ `od` int(11) NOT NULL,
`do` int(11) DEFAULT NULL,
PRIMARY KEY (`id`,`od`),
KEY `do` (`do`)
diff --git a/borrow_history.php b/borrow_history.php
index 3913c10..caa8f34 100644
--- a/borrow_history.php
+++ b/borrow_history.php
@@ -6,43 +6,8 @@
$title = 'Historia wypożyczeń książki';
include('./design/top.php');
-$nastrone = 5;
-
-if(ctype_digit($_GET['strona'])) {
- $strona = $nastrone*($strona-1);
-}
-else
-{
- $strona = 0;
-}
-
gotowe::informacje($_GET['kod']);
-
-$num = db2::num('pozycz', '*', array('id' => $_GET['kod']));
-$dane = db2::get('pozycz', '*', array('id' => $_GET['kod']), array('do' => 'DESC'), $strona, $nastrone);
-
-echo '
-
-<table id="bhist">
-<tr> <th>Pożyczający</th> <th>Od</th> <th>Do</th> </tr>
-';
-
-foreach($dane as $o) {
- echo '<tr> <td>'.$o['kto'].'</td> <td>'.date('Y-m-d H:i:s', $o['od']).'</td> <td>'.($o['do'] ? date('Y-m-d H:i:s', $o['do']) : '').'</td> </tr>'."\n";
-}
-
-echo '</table>
-
-<p>';
-
-for($i=0; $i<$num; $i+=$nastrone) {
- $str = ($i/$nastrone)+1;
- if($i>=$strona && $i<$strona+$nastrone)
- echo '<b> [ '.$str.' ] </b> ';
- else
- echo '<a href="borrow_history.php?kod='.$_GET['kod'].'&strona='.$str.'"> [ '.$str.' ] </a> ';
-}
-
+gotowe::historia($_GET['kod']);
include('./design/bottom.php');
-?>
+?>
\ No newline at end of file
diff --git a/includes/PDOO.php b/includes/PDOO.php
new file mode 100644
index 0000000..ecd70fa
--- /dev/null
+++ b/includes/PDOO.php
@@ -0,0 +1,24 @@
+<?php
+if(!extension_loaded('pdo')) {
+ throw new Exception('Brak rozszerzenia PDO. Skrypt nie będzie działał.');
+}
+
+if(!extension_loaded('pdo_mysql')) {
+ throw new Exception('Brak rozszerzenia PDO MySQL. Skrypt nie będzie działał.');
+}
+
+class PDOO {
+ private static $PDO;
+
+ static function Singleton() {
+ if(self::$PDO === NULL) {
+ self::$PDO = new PDO('mysql:dbname='.config::$db_base.';host='.config::$db_host,
+ config::$db_user, config::$db_pass);
+ self::$PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
+ self::$PDO->query('SET NAMES utf8');
+ }
+
+ return self::$PDO;
+ }
+}
+?>
\ No newline at end of file
diff --git a/includes/db2.php b/includes/db2.php
index 600e8fe..0b9c5cf 100644
--- a/includes/db2.php
+++ b/includes/db2.php
@@ -1,86 +1,57 @@
<?php
-if(!extension_loaded('mysql')) {
- error::add('Brak rozszerzenia MySQL. Skrypt nie będzie działał.');
-}
-
class sql {
static $db;
static $queries = 0;
+ static $PDO;
+
static function connect() {
- self::$db = @mysql_connect(config::$db_host, config::$db_user, config::$db_pass);
- if(!self::$db) {
- error::add(mysql_error());
- }
-
- self::query('SET CHARACTER SET \'UTF8\'');
- self::query('SET NAMES \'UTF8\'');
-
- self::$queries = 0;
-
- if(!@mysql_select_db(config::$db_base)) {
- error::add(mysql_error());
- }
}
static function query($q) {
- if(!self::$db) {
- self::connect();
+ if(!self::$PDO) {
+ self::$PDO = PDOO::Singleton();
}
-
- if(!@mysql_ping(self::$db)) {
- self::connect();
- }
-
- $r = @mysql_query($q, self::$db);
self::$queries++;
- if($r===FALSE) {
- error::add(mysql_error().' '.$q);
- }
-
- return $r;
+ return self::$PDO->query($q);
}
static function fetchonea($q) {
- return mysql_fetch_assoc($q);
+ return $q->fetch(PDO::FETCH_ASSOC);
}
static function fetchone($q) {
- return mysql_fetch_array($q);
+ return $q->fetch(PDO::FETCH_ASSOC);
}
static function fetch($q) {
- while ($entry = mysql_fetch_array($q)) {
- $r[] = $entry;
- }
-
- if(!$r) {
- $r = array();
- }
-
- return $r;
+ return $q->fetchAll();
}
static function increment_id() {
- return mysql_insert_id(self::$db);
+ if(!self::$PDO) {
+ self::$PDO = PDOO::Singleton();
+ }
+
+ return self::$PDO->lastInsertId();
}
- static function affected() {
- return mysql_affected_rows(self::$db);
+ static function affected($q = NULL) {
+ if($q === NULL) {
+ return 'nieznana';
+ }
+
+ return $q->rowCount();
}
static function escape($q) {
- if(!self::$db) {
- self::connect();
+ if(!self::$PDO) {
+ self::$PDO = PDOO::Singleton();
}
- if(!@mysql_ping(self::$db)) {
- self::connect();
- }
-
- return mysql_real_escape_string($q, self::$db);
+ return self::$PDO->quote($q);
}
static function close() {
@@ -127,8 +98,8 @@
continue;
}
- $a[] = '`'.sql::escape($key).'`';
- $b[] = '\''.sql::escape($value).'\'';
+ $a[] = '`'.$key.'`';
+ $b[] = sql::escape($value);
}
return '('.implode(', ', $a).') VALUES ('.implode(', ', $b).')';
@@ -144,11 +115,11 @@
$value = NULL;
}
if(is_null($value)) {
- $a[] = '`'.sql::escape($key).'`=NULL';
+ $a[] = '`'.$key.'` IS NULL';
}
else
{
- $a[] = '`'.sql::escape($key).'`=\''.sql::escape($value).'\'';
+ $a[] = '`'.$key.'`='.sql::escape($value);
}
}
@@ -195,7 +166,7 @@
}
else
{
- $a[] = self::__combine_keyn($key).'=\''.sql::escape($value).'\'';
+ $a[] = self::__combine_keyn($key).'='.sql::escape($value);
}
}
}
@@ -218,13 +189,13 @@
$a[] = self::__combine_keyn($key).' IS NULL';
}
elseif(substr($key, -1)=='!') {
- $a[] = self::__combine_keyn(substr($key, 0, -1)).'!=\''.sql::escape($value).'\'';
+ $a[] = self::__combine_keyn(substr($key, 0, -1)).'!='.sql::escape($value);
}
elseif($key=='^') {
$a[] = 'MAX('.self::__combine_keyn($value).')';
}
elseif(substr($key, -2)=='~~') {
- $temp = 'MATCH ('.self::__combine_keyn(substr($key, 0, -2)).') AGAINST (\''.sql::escape($value).'\')';
+ $temp = 'MATCH ('.self::__combine_keyn(substr($key, 0, -2)).') AGAINST ('.sql::escape($value).')';
if($revelance) {
self::$revelance = $temp.' AS `revelance`';
}
@@ -232,23 +203,23 @@
$a[] = $temp;
}
elseif(substr($key, -1)=='~') {
- $a[] = self::__combine_keyn(substr($key, 0, -1)).' LIKE \''.sql::escape($value).'\'';
+ $a[] = self::__combine_keyn(substr($key, 0, -1)).' LIKE '.sql::escape($value);
}
elseif(substr($key, -2)=='>=') {
- $a[] = self::__combine_keyn(substr($key, 0, -2)).'>=\''.sql::escape($value).'\'';
+ $a[] = self::__combine_keyn(substr($key, 0, -2)).'>='.sql::escape($value);
}
elseif(substr($key, -2)=='<=') {
- $a[] = self::__combine_keyn(substr($key, 0, -2)).'<=\''.sql::escape($value).'\'';
+ $a[] = self::__combine_keyn(substr($key, 0, -2)).'<='.sql::escape($value);
}
elseif(substr($key, -1)=='>') {
- $a[] = self::__combine_keyn(substr($key, 0, -1)).'>\''.sql::escape($value).'\'';
+ $a[] = self::__combine_keyn(substr($key, 0, -1)).'>'.sql::escape($value);
}
elseif(substr($key, -1)=='<') {
- $a[] = self::__combine_keyn(substr($key, 0, -1)).'<\''.sql::escape($value).'\'';
+ $a[] = self::__combine_keyn(substr($key, 0, -1)).'<'.sql::escape($value);
}
else
{
- $a[] = self::__combine_keyn($key).'=\''.sql::escape($value).'\'';
+ $a[] = self::__combine_keyn($key).'='.sql::escape($value);
}
}
}
@@ -306,21 +277,21 @@
$as = $value;
$value = $key;
}
- return '`'.sql::escape($value).'`'.($as ? ' AS `'.sql::escape($as).'`' : '');
+ return '`'.$value.'`'.($as ? ' AS `'.$as.'`' : '');
}
static function __combine_keyn($key) {
if(!self::$SAFE_MODE_KEY AND strpos($key, '.')!==FALSE) {
$key = explode('.', $key, 2);
- return '`'.sql::escape($key[0]).'`.`'.sql::escape($key[1]).'`';
+ return '`'.$key[0].'`.`'.$key[1].'`';
}
- return '`'.sql::escape($key).'`';
+ return '`'.$key.'`';
}
static function __combine_table($table) {
if(!is_array($table) OR self::$SAFE_MODE_TABLE) {
- return '`'.sql::escape($table).'` ';
+ return '`'.$table.'` ';
}
else
{
@@ -419,17 +390,17 @@
}
static function add($table, $keys) {
- sql::query('INSERT INTO `'.sql::escape($table).'` '.self::__combine_insert($keys));
+ sql::query('INSERT INTO `'.$table.'` '.self::__combine_insert($keys));
return sql::affected();
}
static function edit($table, $keys, $where=NULL, $order=NULL, $limit=NULL, $stop=NULL) {
- sql::query('UPDATE `'.sql::escape($table).'` SET '.self::__combine_update($keys).self::__combine_where($where).self::__combine_order($order).self::__combine_limit($limit, $stop));
+ sql::query('UPDATE `'.$table.'` SET '.self::__combine_update($keys).self::__combine_where($where).self::__combine_order($order).self::__combine_limit($limit, $stop));
return sql::affected();
}
static function del($table, $where=NULL, $order=NULL, $limit=NULL, $stop=NULL) {
- sql::query('DELETE FROM `'.sql::escape($table).'`'.self::__combine_where($where).self::__combine_order($order).self::__combine_limit($limit, $stop));
+ sql::query('DELETE FROM `'.$table.'`'.self::__combine_where($where).self::__combine_order($order).self::__combine_limit($limit, $stop));
return sql::affected();
}
diff --git a/includes/gotowe.php b/includes/gotowe.php
index 5b66686..b81299c 100644
--- a/includes/gotowe.php
+++ b/includes/gotowe.php
@@ -112,6 +112,26 @@
return $ord;
}
+ static function historia($kod) {
+ $st = PDOO::Singleton()->prepare(
+ 'SELECT * FROM pozycz_historia WHERE id=?'."\n"
+ .'UNION'."\n"
+ .'SELECT *, \'\' AS do FROM pozycz WHERE id=? ORDER BY od ASC'
+ );
+ $st->execute(array($kod, $kod));
+ $dane = $st->fetchAll();
+
+ $info = '<table id="bhist">
+<tr> <th>Pożyczający</th> <th>Od</th> <th>Do</th> </tr>
+';
+
+ foreach($dane as $o) {
+ $info .= '<tr> <td>'.htmlspecialchars($o['kto']).'</td> <td>'.date('Y-m-d H:i:s', $o['od']).'</td> <td>'.($o['do'] ? date('Y-m-d H:i:s', $o['do']) : '').'</td> </tr>'."\n";
+ }
+
+ echo $info.'</table>';
+ }
+
static function informacje($kod, $dane=NULL) {
if(is_null($dane)) {
$dane = ksiazki::szukaj_KOD($kod);
diff --git a/includes/ksiazki.php b/includes/ksiazki.php
index 051fb28..2ed9d61 100644
--- a/includes/ksiazki.php
+++ b/includes/ksiazki.php
@@ -13,10 +13,6 @@
}
static function cache_add($kod, &$dane) {
- if($dane['od2']) {
- $dane['od'] = $dane['od2'];
- unset($dane['od2']);
- }
self::$cache[(int)$kod] = $dane;
}
@@ -37,7 +33,7 @@
}
static function cache_update($kod) {
- $dane = db2::escape_data(sql::fetchone(sql::query('SELECT *, (SELECT MAX(`od`) FROM `pozycz` WHERE `pozycz`.`id`=`ksiazki`.`id`) as `od2`, (SELECT `do` FROM `pozycz` WHERE `pozycz`.`id`=`ksiazki`.`id` AND `od`=`od2`) as `do`, (SELECT `kto` FROM `pozycz` WHERE `pozycz`.`id`=`ksiazki`.`id` AND `od`=`od2`) as `kto` FROM `ksiazki` WHERE `id`=\''.sql::escape($kod).'\'')));
+ $dane = db2::escape_data(sql::fetchone(sql::query('SELECT `ksiazki`.*, `pozycz`.`od`, `pozycz`.`kto` FROM `ksiazki` LEFT OUTER JOIN `pozycz` ON `pozycz`.`id`=`ksiazki`.`id` WHERE `ksiazki`.`id`='.sql::escape($kod))));
self::cache_add($kod, $dane);
}
}
@@ -233,25 +229,25 @@
else
{
if($dane['do']) {
- $num = db2::num('pozycz', 'id', array('do' => NULL));
- if($num==0) {
+ $num = db2::num('pozycz', 'id');
+ if($num == 0) {
$ret = array();
}
else
{
- $ret = db2::get(array('pozycz', array('J', 'ksiazki', 'USING', 'id')), '*', array('do' => NULL), $order, $start, $limit);
+ $ret = db2::get(array('pozycz', array('J', 'ksiazki', 'USING', 'id')), '*', NULL, $order, $start, $limit);
}
}
else
{
$num = db2::num('ksiazki', 'id', $where);
- if($num==0) {
+ if($num == 0) {
$ret = array();
}
else
{
$where = db2::__combine_where($where, TRUE);
- $ret = db2::escape_data(sql::fetch(sql::query('SELECT *, (SELECT MAX(`od`) FROM `pozycz` WHERE `pozycz`.`id`=`ksiazki`.`id`) as `od2`, (SELECT `do` FROM `pozycz` WHERE `pozycz`.`id`=`ksiazki`.`id` AND `od`=`od2`) as `do`, (SELECT `kto` FROM `pozycz` WHERE `pozycz`.`id`=`ksiazki`.`id` AND `od`=`od2`) as `kto`'.(db2::revelance() ? ', '.db2::$revelance : '').' FROM `ksiazki`'.$where.db2::__combine_order($order, TRUE).db2::__combine_limit($start, $limit))));
+ $ret = db2::escape_data(sql::fetch(sql::query('SELECT `ksiazki`.*, `pozycz`.`od`, `pozycz`.`kto` FROM `ksiazki` LEFT OUTER JOIN `pozycz` ON `pozycz`.`id`=`ksiazki`.`id`'.(db2::revelance() ? ', '.db2::$revelance : '').' '.$where.db2::__combine_order($order, TRUE).db2::__combine_limit($start, $limit))));
}
}
diff --git a/includes/pozycz.php b/includes/pozycz.php
index 1d9fb30..6adb5b5 100644
--- a/includes/pozycz.php
+++ b/includes/pozycz.php
@@ -14,19 +14,19 @@
error::add('Książka nie jest wypożyczona!');
}
- db2::edit('pozycz', array('do' => time()), array('id' => $kod, 'do' => NULL));
- ksiazki::cache_clear($kod);
+ $st = PDOO::Singleton()->prepare('INSERT INTO pozycz_historia (id, kto, od, do)
+ SELECT id, kto, od, ? FROM pozycz WHERE id=?');
+ $st->execute(array(time(), $kod));
+
+ $st = PDOO::Singleton()->prepare('DELETE FROM pozycz WHERE id=?');
+ $st->execute(array($kod));
+
+ ksiazki::cache_update($kod);
}
static function pozyczona($kod) {
$ksiazka = ksiazki::szukaj_KOD($kod);
- if($ksiazka['do']!==NULL OR $ksiazka['od']===NULL) {
- return FALSE;
- }
- else
- {
- return $ksiazka['kto'];
- }
+ return $ksiazka['od'] != NULL;
}
}
?>
\ No newline at end of file
diff --git a/test.php b/test.php
index c84bbcc..815f15f 100644
--- a/test.php
+++ b/test.php
@@ -32,8 +32,15 @@
<tr> <th class="head" colspan="2">Baza danych</th> </tr>
<tr> <th>Ustawienia bazy<br />(/includes/config.php)</th> <td><?php echo NT; ?></td> </tr>
-<tr> <th>Rozszerzenie MySQL</th> <td><?php
-if(!extension_loaded('mysql')) {
+<tr> <th>Rozszerzenie PDO</th> <td><?php
+if(!extension_loaded('pdo')) {
+ echo FAIL;
+ $mysql = FALSE;
+}
+else echo OK;
+?></td> </tr>
+<tr> <th>Rozszerzenie PDO MySQL</th> <td><?php
+if(!extension_loaded('pdo_mysql')) {
echo FAIL;
$mysql = FALSE;
}
--
Gitblit v1.9.1