Created
May 17, 2011 18:53
-
-
Save steamraven/977106 to your computer and use it in GitHub Desktop.
Filez 2.1 compatibility with postgresql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
diff --git a/app/models/DbTable/File.php b/app/models/DbTable/File.php | |
index 92b4a56..259db75 100644 | |
--- a/app/models/DbTable/File.php | |
+++ b/app/models/DbTable/File.php | |
@@ -111,7 +111,7 @@ class App_Model_DbTable_File extends Fz_Db_Table_Abstract { | |
public function findByOwnerOrderByUploadDateDesc ($user) { | |
$sql = 'SELECT * FROM '.$this->getTableName () | |
.' WHERE created_by=:id ' | |
- .' AND available_until >= CURRENT_DATE() ' | |
+ .' AND available_until >= CURRENT_DATE ' | |
.' ORDER BY created_at DESC'; | |
return $this->findBySql ($sql, array (':id' => $user->id)); | |
} | |
@@ -121,7 +121,7 @@ class App_Model_DbTable_File extends Fz_Db_Table_Abstract { | |
*/ | |
public function deleteExpiredFiles () { | |
$select = 'SELECT * FROM '.$this->getTableName (); | |
- $where = ' WHERE available_until<CURRENT_DATE()'; | |
+ $where = ' WHERE available_until<CURRENT_DATE'; | |
foreach ($this->findBySql ($select.$where) as $file) { | |
if ($file->deleteFromDisk () === true) { | |
fz_log ('Deleted file "'.$file->getOnDiskLocation ().'"', | |
@@ -143,8 +143,10 @@ class App_Model_DbTable_File extends Fz_Db_Table_Abstract { | |
*/ | |
public function findFilesToBeDeleted ($days = 2) { | |
$sql = 'SELECT * FROM '.$this->getTableName () | |
- .' WHERE available_until BETWEEN CURRENT_DATE() ' | |
- .'AND DATE_ADD(CURRENT_DATE(), INTERVAL '.$days.' DAY) ' | |
- .'AND del_notif_sent=0 AND notify_uploader=1'; | |
+ .' WHERE (available_until BETWEEN CURRENT_DATE ' | |
+ //.'AND DATE_ADD(CURRENT_DATE, INTERVAL '.$days.' DAY) ' | |
+ .'AND CURRENT_DATE + ' . $days .') ' | |
+ .'AND del_notif_sent=false AND notify_uploader=true'; | |
return $this->findBySql ($sql); | |
} | |
@@ -158,10 +158,10 @@ class App_Model_DbTable_File extends Fz_Db_Table_Abstract { | |
*/ | |
public function getTotalDiskSpaceByUser ($user) { | |
$result = option ('db_conn') | |
- ->prepare ('SELECT sum(file_size) FROM `' | |
+ ->prepare ('SELECT sum(file_size) FROM ' | |
.$this->getTableName () | |
- .'` WHERE created_by = ?' | |
- .' AND available_until >= CURRENT_DATE() '); | |
+ .' WHERE created_by = ?' | |
+ .' AND available_until >= CURRENT_DATE '); | |
$result->execute (array ($user->id)); | |
return (float) $result->fetchColumn (); | |
} | |
@@ -222,9 +222,9 @@ class App_Model_DbTable_File extends Fz_Db_Table_Abstract { | |
*/ | |
public function getTotalDiskSpace () { | |
$result = option ('db_conn') | |
- ->prepare ('SELECT sum(file_size) FROM `' | |
+ ->prepare ('SELECT sum(file_size) FROM ' | |
.$this->getTableName () | |
- .'` WHERE available_until >= CURRENT_DATE() '); | |
+ .' WHERE available_until >= CURRENT_DATE '); | |
$result->execute (); | |
return $this->getReadableSize ($result->fetchColumn ()); | |
} | |
diff --git a/app/models/DbTable/Info.php b/app/models/DbTable/Info.php | |
index 510a7c4..96fbab6 100644 | |
--- a/app/models/DbTable/Info.php | |
+++ b/app/models/DbTable/Info.php | |
@@ -31,7 +31,7 @@ class App_Model_DbTable_Info extends Fz_Db_Table_Abstract { | |
*/ | |
public function update ($key, $value) { | |
$db = Fz_Db::getConnection(); | |
- $sql = 'UPDATE `'.$this->getTableName ().'` SET `value` = :value WHERE `fz_info`.`key` = :key '; | |
+ $sql = 'UPDATE '.$this->getTableName ().' SET value = :value WHERE fz_info.key = :key '; | |
$stmt = $db->prepare ($sql); | |
return $stmt->execute (array ( | |
':key' => $key, | |
@@ -47,7 +47,7 @@ class App_Model_DbTable_Info extends Fz_Db_Table_Abstract { | |
*/ | |
public function insert ($key, $value) { | |
$db = Fz_Db::getConnection(); | |
- $sql = 'INSERT INTO `'.$this->getTableName ().'` (`key`, `value`) VALUES (:value, :key)'; | |
+ $sql = 'INSERT INTO '.$this->getTableName ().' (key, value) VALUES (:value, :key)'; | |
$stmt = $db->prepare ($sql); | |
return $stmt->execute (array ( | |
':key' => $key, | |
@@ -63,7 +63,7 @@ class App_Model_DbTable_Info extends Fz_Db_Table_Abstract { | |
*/ | |
public function get ($key) { | |
$db = Fz_Db::getConnection(); | |
- $sql = 'SELECT `value` FROM `'.$this->getTableName ().'` WHERE `fz_info`.`key` = ?'; | |
+ $sql = 'SELECT value FROM '.$this->getTableName ().' WHERE fz_info.key = ?'; | |
$stmt = $db->prepare ($sql); | |
$stmt->execute (array ($key)); | |
diff --git a/config/db/schema.sql b/config/db/schema.sql | |
index 2bbb96b..eb818d4 100644 | |
--- a/config/db/schema.sql | |
+++ b/config/db/schema.sql | |
@@ -1,40 +1,47 @@ | |
SET NAMES 'utf8'; | |
-CREATE TABLE IF NOT EXISTS `fz_file` ( | |
- `id` BIGINT UNSIGNED NOT NULL, | |
- `del_notif_sent` BOOLEAN DEFAULT 0, | |
- `file_name` varchar(100) NOT NULL, | |
- `file_size` INTEGER DEFAULT 0, | |
- `available_from` DATE NOT NULL, | |
- `available_until` DATE NOT NULL, | |
- `comment` varchar(200), | |
- `download_count` INTEGER DEFAULT 0, | |
- `notify_uploader` BOOLEAN DEFAULT 0, | |
- `created_by` INTEGER NOT NULL, | |
- `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
- `extends_count` INTEGER DEFAULT '0', | |
- `password` varchar(40) DEFAULT NULL, | |
- INDEX (`created_by`), | |
- FOREIGN KEY (`created_by`) REFERENCES fz_user(id), | |
- UNIQUE KEY `id` (`id`) | |
-) DEFAULT CHARSET=utf8; | |
- | |
-CREATE TABLE `fz_info` ( | |
- `key` VARCHAR( 30 ) NOT NULL , | |
- `value` VARCHAR( 50 ) NOT NULL , | |
- PRIMARY KEY ( `key` ) | |
-) DEFAULT CHARSET=utf8; | |
- | |
-CREATE TABLE `fz_user` ( | |
- `id` SERIAL NOT NULL, | |
- `username` VARCHAR(30) NOT NULL, | |
- `password` VARCHAR(40) NOT NULL, | |
- `salt` VARCHAR(40), | |
- `firstname` VARCHAR(50) NOT NULL, | |
- `lastname` VARCHAR(50) NOT NULL, | |
- `email` VARCHAR(50) NOT NULL, | |
- `is_admin` BOOLEAN DEFAULT 0, | |
- `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP | |
-) ENGINE = MYISAM ; | |
- | |
-INSERT INTO `fz_info` (`key`, `value`) VALUES ('cron_freq', NOW()); | |
-INSERT INTO `fz_info` (`key`, `value`) VALUES ('db_version', '2.1.0-2'); | |
+ | |
+CREATE TABLE "fz_user" ( | |
+ "id" SERIAL NOT NULL, | |
+ "username" VARCHAR(30) NOT NULL, | |
+ "password" VARCHAR(40) NOT NULL, | |
+ "salt" VARCHAR(40), | |
+ "firstname" VARCHAR(50) NOT NULL, | |
+ "lastname" VARCHAR(50) NOT NULL, | |
+ "email" VARCHAR(50) NOT NULL, | |
+ "is_admin" BOOLEAN DEFAULT false, | |
+ "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
+ PRIMARY KEY ("id") | |
+) ; | |
+ | |
+ | |
+CREATE TABLE "fz_file" ( | |
+ "id" NUMERIC(20) NOT NULL, | |
+ "del_notif_sent" BOOLEAN DEFAULT false, | |
+ "file_name" varchar(100) NOT NULL, | |
+ "file_size" INTEGER DEFAULT 0, | |
+ "available_from" DATE NOT NULL, | |
+ "available_until" DATE NOT NULL, | |
+ "comment" varchar(200), | |
+ "download_count" INTEGER DEFAULT 0, | |
+ "notify_uploader" BOOLEAN DEFAULT false, | |
+ "created_by" INTEGER NOT NULL, | |
+ "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
+ "extends_count" INTEGER DEFAULT '0', | |
+ "password" varchar(40) DEFAULT NULL, | |
+ FOREIGN KEY ("created_by") REFERENCES fz_user(id), | |
+ PRIMARY KEY ("id") | |
+) ; | |
+ | |
+CREATE INDEX "created_by" on "fz_file" ("created_by"); | |
+ | |
+CREATE TABLE "fz_info" ( | |
+ "key" VARCHAR( 30 ) NOT NULL , | |
+ "value" VARCHAR( 50 ) NOT NULL , | |
+ PRIMARY KEY ( "key" ) | |
+) ; | |
+ | |
+ | |
+ | |
+ | |
+INSERT INTO "fz_info" ("key", "value") VALUES ('cron_freq', NOW()); | |
+INSERT INTO "fz_info" ("key", "value") VALUES ('db_version', '2.1.0-2'); | |
diff --git a/lib/Fz/Db/Schema.php b/lib/Fz/Db/Schema.php | |
index f5c98fa..902e938 100644 | |
--- a/lib/Fz/Db/Schema.php | |
+++ b/lib/Fz/Db/Schema.php | |
@@ -43,7 +43,7 @@ class Fz_Db_Schema { | |
// Update filez version | |
if (! empty ($sql)) | |
- $sql .= 'UPDATE `fz_info` SET `value`=\''.$matches[1].'\' WHERE `key`=\'db_version\''; | |
+ $sql .= 'UPDATE fz_info SET value=\''.$matches[1].'\' WHERE key=\'db_version\''; | |
} | |
if (! empty ($sql)) | |
diff --git a/lib/Fz/Db/Table/Abstract.php b/lib/Fz/Db/Table/Abstract.php | |
index 2963931..090bd9d 100644 | |
--- a/lib/Fz/Db/Table/Abstract.php | |
+++ b/lib/Fz/Db/Table/Abstract.php | |
@@ -95,7 +95,7 @@ abstract class Fz_Db_Table_Abstract { | |
*/ | |
public function rowExists ($id) { | |
$db = option ('db_conn'); | |
- $sql = 'SELECT id FROM `'.$this->getTableName ().'` WHERE id = ?'; | |
+ $sql = 'SELECT id FROM '.$this->getTableName ().' WHERE id = ?'; | |
$stmt = $db->prepare ($sql); | |
$stmt->execute (array ($id)); | |
diff --git a/lib/Fz/Db/Table/Row/Abstract.php b/lib/Fz/Db/Table/Row/Abstract.php | |
index 91ad5cb..a9dd593 100644 | |
--- a/lib/Fz/Db/Table/Row/Abstract.php | |
+++ b/lib/Fz/Db/Table/Row/Abstract.php | |
@@ -168,7 +168,7 @@ abstract class Fz_Db_Table_Row_Abstract { | |
return $this; | |
array_walk ($this->_sqlModifiers, array ('Fz_Db','nameEqSql')); | |
- $sql = "UPDATE `$table` SET " . | |
+ $sql = "UPDATE $table SET " . | |
implode (', ', array_merge (array_map (array ('Fz_Db','nameEqColonName'),$unmodifiedColumns), $this->_sqlModifiers)) . | |
' WHERE id = :id'; | |
@@ -195,7 +195,7 @@ abstract class Fz_Db_Table_Row_Abstract { | |
$unmodifiedColumns = array_diff ($columnsName, $sqlModifiersColumnsName); | |
$sql = | |
- "INSERT INTO `$table` (" . | |
+ "INSERT INTO $table (" . | |
implode (', ', array_merge ($unmodifiedColumns, $sqlModifiersColumnsName)) . // reorder columns | |
') VALUES (' . | |
implode (', ', array_merge (array_map (array ('Fz_Db','addColon'), $unmodifiedColumns), $this->_sqlModifiers)) . ')'; | |
@@ -215,7 +215,7 @@ abstract class Fz_Db_Table_Row_Abstract { | |
public function delete () { | |
$db = option ('db_conn'); | |
if ($this->_exists === false) return; | |
- $stmt = $db->prepare ('DELETE FROM `'.$this->getTableName ().'` WHERE id = ?'); | |
+ $stmt = $db->prepare ('DELETE FROM '.$this->getTableName ().' WHERE id = ?'); | |
$stmt->execute (array ($this->id)); | |
} | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Corrected date adding in Files::findFilesToBeDeleted