first attempt at image attachments!

3 files changed, 311 insertions, 9 deletions

diff --git a/lib/database.php b/lib/database.php
index 5774d95..4e5e0cd 100644
--- a/lib/database.php
+++ b/lib/database.php
@@ -15,11 +15,12 @@ try {
 // first time setup
 if($config['db_version'] == 0) {
 	try {
-		$db->exec("CREATE TABLE IF NOT EXISTS `posts` (
-			`id` integer PRIMARY KEY NOT NULL,
+		$db->exec("PRAGMA `user_version` = 1;
+			CREATE TABLE IF NOT EXISTS `posts` (
+			`id` INTEGER PRIMARY KEY NOT NULL,
 			`post_content` TEXT,
 			`post_timestamp` INTEGER
-		); PRAGMA `user_version` = 1;");
+		);");
 		$config['db_version'] = 1;
 	} catch(PDOException $e) {
 		print 'Exception : '.$e->getMessage();
@@ -30,7 +31,7 @@ if($config['db_version'] == 0) {
 // upgrade database to v2
 if($config['db_version'] == 1) {
 	try {
-		$db->exec("PRAGMA user_version = 2;
+		$db->exec("PRAGMA `user_version` = 2;
 			ALTER TABLE `posts` ADD `post_thread` INTEGER;
 			ALTER TABLE `posts` ADD `post_edited` INTEGER;
 			ALTER TABLE `posts` ADD `post_deleted` INTEGER;
@@ -45,7 +46,7 @@ if($config['db_version'] == 1) {
 // upgrade database to v3
 if($config['db_version'] == 2) {
 	try {
-		$db->exec("PRAGMA user_version = 3;
+		$db->exec("PRAGMA `user_version` = 3;
 			ALTER TABLE `posts` ADD `post_guid` TEXT;
 		");
 		$config['db_version'] = 3;
@@ -55,5 +56,42 @@ if($config['db_version'] == 2) {
 	}
 }
 
+// upgrade database to v4
+if($config['db_version'] == 3) {
+	try {
+		$db->exec("PRAGMA `user_version` = 4;
+				CREATE TABLE `files` (
+				`id` INTEGER PRIMARY KEY NOT NULL,
+				`file_filename` TEXT NOT NULL,
+				`file_extension` TEXT,
+				`file_original` TEXT NOT NULL,
+				`file_mime_type` TEXT,
+				`file_size` INTEGER,
+				`file_hash` TEXT UNIQUE,
+				`file_hash_algo` TEXT,
+				`file_meta` TEXT DEFAULT '{}',
+				`file_dir` TEXT,
+				`file_subdir` TEXT,
+				`file_timestamp` INTEGER,
+				`file_deleted` INTEGER
+			);
+			CREATE TABLE `file_to_post` (
+				`file_id` INTEGER NOT NULL,
+				`post_id` INTEGER NOT NULL,
+				`deleted` INTEGER,
+				UNIQUE(`file_id`, `post_id`) ON CONFLICT IGNORE
+			);
+			CREATE INDEX `posts_timestamp` ON posts (`post_timestamp`);
+			CREATE INDEX `files_original` ON files (`file_original`);
+			CREATE INDEX `link_deleted` ON file_to_post (`deleted`);
+			CREATE UNIQUE INDEX `files_hashes` ON files (`file_hash`);
+		");
+		$config['db_version'] = 4;
+	} catch(PDOException $e) {
+		print 'Exception : '.$e->getMessage();
+		die('cannot upgrade database table to v4!');
+	}
+}
+
 // debug: get a list of post table columns
 // var_dump($db->query("PRAGMA table_info(`posts`)")->fetchAll(PDO::FETCH_COLUMN, 1));
diff --git a/lib/functions.php b/lib/functions.php
index 7046eb5..e606230 100644
--- a/lib/functions.php
+++ b/lib/functions.php
@@ -98,6 +98,33 @@ function db_select_post($id=0) {
 	return (!empty($row)) ? $row : false;
 }
 
+function db_get_attached_files($post_id, $include_deleted=false) {
+	global $db;
+	if(empty($db)) return false;
+
+	$rows = [];
+
+	if($include_deleted) {
+		$sql = 'SELECT f.* FROM files f LEFT JOIN file_to_post p WHERE f.id = p.file_id AND p.post_id = :post_id ORDER BY f.file_timestamp ASC';
+	} else {
+		$sql = 'SELECT f.* FROM files f LEFT JOIN file_to_post p WHERE f.id = p.file_id AND p.post_id = :post_id AND p.deleted IS NULL ORDER BY f.file_timestamp ASC';
+	}
+
+	try {
+		$statement = $db->prepare($sql);
+		$statement->bindValue(':post_id', $post_id, PDO::PARAM_INT);
+		
+		$statement->execute();
+
+		$rows = $statement->fetchAll(PDO::FETCH_ASSOC);
+	} catch(PDOException $e) {
+		// print 'Exception : '.$e->getMessage();
+		return false;
+	}
+
+	return (!empty($rows)) ? $rows : false;
+}
+
 function db_select_posts($from, $amount=10, $sort='desc', $offset=0) {
 	global $db;
 	if(empty($db)) return false;
@@ -126,6 +153,225 @@ function db_posts_count() {
 	return (int) $row['posts_count'];
 }
 
+function convert_files_array($input_array) {
+	$file_array = [];
+	$file_count = count($input_array['name']);
+	$file_keys = array_keys($input_array);
+
+	for ($i=0; $i<$file_count; $i++) {
+		foreach ($file_keys as $key) {
+			$file_array[$i][$key] = $input_array[$key][$i];
+		}
+	}
+
+	return $file_array;
+}
+
+function attach_uploaded_files($files=[], $post_id=null) {
+	if(empty($files['tmp_name'][0])) return false;
+	
+	$files = convert_files_array($files);
+	//var_dump($files);exit();
+
+	foreach($files as $file) {
+
+		if (!isset($file['error']) || is_array($file['error'])) {
+			// invalid parameters
+			// var_dump('bad file info');exit();
+			continue; // skip this file
+		}
+
+		if($file['size'] > 20000000) {
+			// Exceeded filesize limit.
+			// var_dump('invalid file size');exit();
+			continue;
+		}
+
+		$mime = mime_content_type($file['tmp_name']);
+		if (false === $ext = array_search(
+			$mime,
+			array(
+				'jpg' => 'image/jpeg',
+				'png' => 'image/png',
+				'gif' => 'image/gif',
+				'avif' => 'image/avif',
+				'webp' => 'image/webp',
+				// todo: video
+				'txt' => 'text/plain',
+				'md' => 'text/markdown',
+			),
+			true
+		)) {
+			// Invalid file format.
+			// var_dump('invalid format');exit();
+			continue;
+		}
+
+		save_file($file['name'], $ext, $file['tmp_name'], $post_id, $mime);
+	}
+}
+
+function detatch_files($file_ids=[], $post_id=null) {
+	global $db;
+	if(empty($db)) return false;
+	if(empty($file_ids)) return false;
+	if(!$post_id) return false;
+
+	$file_id = null;
+
+	try {
+		$statement = $db->prepare('UPDATE file_to_post SET deleted = :delete_time WHERE file_id = :file_id AND post_id = :post_id');
+
+		$statement->bindParam(':file_id', $file_id, PDO::PARAM_INT);
+		$statement->bindParam(':post_id', $post_id, PDO::PARAM_INT);
+		$statement->bindValue(':delete_time', time(), PDO::PARAM_INT);
+
+		foreach ($file_ids as $id) {
+			$file_id = $id;
+			$statement->execute();
+		}
+
+	} catch(PDOException $e) {
+		// print 'Exception : '.$e->getMessage();
+		return false;
+	}
+
+	return true;
+}
+
+function db_select_file($query, $method='id') {
+	global $db;
+	if(empty($db)) return false;
+	if($id === 0) return false;
+
+	switch ($method) {
+		case 'hash':
+			$statement = $db->prepare('SELECT * FROM files WHERE file_hash = :q LIMIT 1');
+			$statement->bindValue(':q', $query, PDO::PARAM_STR);
+			break;
+		case 'filename':
+			$statement = $db->prepare('SELECT * FROM files WHERE file_filename = :q LIMIT 1');
+			$statement->bindValue(':q', $query, PDO::PARAM_STR);
+			break;
+		default:
+			$statement = $db->prepare('SELECT * FROM files WHERE id = :q LIMIT 1');
+			$statement->bindValue(':q', $query, PDO::PARAM_INT);
+			break;
+	}
+	
+	$statement->execute();
+	$row = $statement->fetch(PDO::FETCH_ASSOC);
+
+	return (!empty($row)) ? $row : false;
+}
+
+function db_link_file($file_id, $post_id) {
+	global $db;
+	if(empty($db)) return false;
+
+	try {
+		$statement = $db->prepare('INSERT INTO file_to_post (file_id, post_id) VALUES (:file_id, :post_id)');
+
+		$statement->bindValue(':file_id', $file_id, PDO::PARAM_INT);
+		$statement->bindValue(':post_id', $post_id, PDO::PARAM_INT);
+
+		$statement->execute();
+	} catch(PDOException $e) {
+		// print 'Exception : '.$e->getMessage();
+		return false;
+	}
+
+	return true;
+}
+
+function save_file($filename, $extension, $tmp_file, $post_id, $mime='') {
+	global $db;
+	if(empty($db)) return false;
+
+	$files_dir = ROOT.DS.'files';
+	$hash_algo = 'sha1';
+
+	$insert = [
+		'file_extension' => $extension,
+		'file_original' => $filename,
+		'file_mime_type' => $mime,
+		'file_size' => filesize($tmp_file),
+		'file_hash' => hash_file($hash_algo, $tmp_file),
+		'file_hash_algo' => $hash_algo,
+		'file_meta' => '{}',
+		'file_dir' => date('Y'),
+		'file_subdir' => date('m'),
+		'file_timestamp' => time()
+	];
+
+	if(!is_dir($files_dir.DS.$insert['file_dir'])) {
+		mkdir($files_dir.DS.$insert['file_dir'], 0755);
+	}
+
+	if(!is_dir($files_dir.DS.$insert['file_dir'].DS.$insert['file_subdir'])) {
+		mkdir($files_dir.DS.$insert['file_dir'].DS.$insert['file_subdir'], 0755);
+	}
+
+	$insert['file_filename'] = $post_id . '-' . substr($insert['file_hash'], 0, 7);
+	$path = $files_dir.DS.$insert['file_dir'].DS.$insert['file_subdir'];
+
+	if(move_uploaded_file($tmp_file, $path.DS.$insert['file_filename'] .'.'. $insert['file_extension'])) {
+		// add to database
+
+		// check if file exists already
+		$existing = db_select_file($insert['file_hash'], 'hash');
+
+		if(!empty($existing)) {
+			// just link existing file
+			if(db_link_file($existing['id'], $post_id)) {
+				return $existing['id'];
+			} else {
+				return false;
+			}
+		} else {
+			// insert new
+			try {
+				$statement = $db->prepare('INSERT INTO files (file_filename, file_extension, file_original, file_mime_type, file_size, file_hash, file_hash_algo, file_meta, file_dir, file_subdir, file_timestamp) VALUES (:file_filename, :file_extension, :file_original, :file_mime_type, :file_size, :file_hash, :file_hash_algo, :file_meta, :file_dir, :file_subdir, :file_timestamp)');
+
+				$statement->bindValue(':file_filename', $insert['file_filename'], PDO::PARAM_STR);
+				$statement->bindValue(':file_extension', $insert['file_extension'], PDO::PARAM_STR);
+				$statement->bindValue(':file_original', $insert['file_original'], PDO::PARAM_STR);
+				$statement->bindValue(':file_mime_type', $insert['file_mime_type'], PDO::PARAM_STR);
+				$statement->bindValue(':file_size', $insert['file_size'], PDO::PARAM_INT);
+				$statement->bindValue(':file_hash', $insert['file_hash'], PDO::PARAM_STR);
+				$statement->bindValue(':file_hash_algo', $insert['file_hash_algo'], PDO::PARAM_STR);
+				$statement->bindValue(':file_meta', $insert['file_meta'], PDO::PARAM_STR);
+				$statement->bindValue(':file_dir', $insert['file_dir'], PDO::PARAM_STR);
+				$statement->bindValue(':file_subdir', $insert['file_subdir'], PDO::PARAM_STR);
+				$statement->bindValue(':file_timestamp', $insert['file_timestamp'], PDO::PARAM_INT);
+
+				$statement->execute();
+
+				// todo: check this?
+				db_link_file($db->lastInsertId(), $post_id);
+
+				return $db->lastInsertId();
+			} catch(PDOException $e) {
+				print 'Exception : '.$e->getMessage();
+				return false;
+			}
+		}
+	}
+
+	return false;
+}
+
+function get_file_path($file) {
+	$url = '';
+
+	$url .= 'files/';
+	$url .= $file['file_dir'] . '/';
+	$url .= $file['file_subdir'] . '/';
+	$url .= $file['file_filename'] . '.' . $file['file_extension'];
+
+	return $url;
+}
+
 /* function that pings the official micro.blog endpoint for feed refreshes */
 function ping_microblog() {
 	global $config;
@@ -178,13 +424,33 @@ function rebuild_json_feed($posts=[]) {
 
 	foreach($posts as $post) {
 
+		$attachments = db_get_attached_files($post['id']);
+		$post_attachments = [];
+		if(!empty($attachments)) {
+			foreach ($attachments as $a) {
+				$post_attachments[] = [
+					'url' => $config['url'] .'/'. get_file_path($a),
+					'mime_type' => $a['file_mime_type'],
+					'size_in_bytes' => $a['file_size']
+				];
+			}
+		}
+
+		$post_images = array_filter($post_attachments, function($v) {
+			return strpos($v['mime_type'], 'image') === 0;
+		});
+
 		$feed['items'][] = array(
 			'id' => ($post['post_guid'] ? 'urn:uuid:'.$post['post_guid'] : $config['url'].'/'.$post['id']),
 			'url' => $config['url'].'/'.$post['id'],
 			'title' => '',
 			'content_html' => $post['post_content'],
-			'date_published' => gmdate('Y-m-d\TH:i:s\Z', $post['post_timestamp'])
+			'date_published' => gmdate('Y-m-d\TH:i:s\Z', $post['post_timestamp']),
+			'image' => !empty($post_images) ? $post_images[0]['url'] : '',
+			'attachments' => $post_attachments
 		);
+
+		
 	}
 
 	if(file_put_contents($filename, json_encode($feed, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES))) {
diff --git a/lib/xmlrpc.php b/lib/xmlrpc.php
index 51e1be2..985d0f1 100644
--- a/lib/xmlrpc.php
+++ b/lib/xmlrpc.php
@@ -3,11 +3,9 @@
 $request_xml = file_get_contents("php://input");
 
 // check prerequisites
-if(!function_exists('xmlrpc_server_create')) { exit('No XML-RPC support detected!'); }
+if(!$config['xmlrpc']) { exit('No XML-RPC support detected!'); }
 if(empty($request_xml)) { exit('XML-RPC server accepts POST requests only.'); }
 
-// load config
-require_once(__DIR__.DIRECTORY_SEPARATOR.'..'.DIRECTORY_SEPARATOR.'config.php');
 $logfile = ROOT.DS.'log.txt';
 
 if(!function_exists('str_starts_with')) {