Connecting to the PhotoStation's database

Questions regaring MySQL and PHP may go here
Forum rules
We've moved! Head over to Synology Community (community.synology.com) to meet up with our team and other Synology enthusiasts!
Pokersut
Student
Student
Posts: 77
Joined: Thu Sep 20, 2007 10:02 am

Connecting to the PhotoStation's database

Unread post by Pokersut » Fri Jan 15, 2010 12:05 pm

Hi.

Is it possible to tap directly into PhotoStation's database from a php script?

What I want is to make a page (stored locally in the 'web' folder) that connects to the database and selects (and displays), say, 100 random pictures from all the photos that are stored in the 'photo' folder.

I have made the 'photo' folder accessible to php scripts using the WebShare Enable application, and I have searched the PhotoStation source code in /usr/syno/synoman/phpsrc/photo/include, but I can't find the code that connects to the database and fetches and displays images.

Any point in the right direction will be greatly appreciated!

Mikkel
Main unit
DS-413 * Always latest DSM (including Betas) * 1 x Seagate ST4000DM000 + 1 x Western Digital WD40EFRX + 1 x Seagate ST3000DM001 + 1 x Western Digital WD30EZRX

Backup unit
DS-213+ * Always latest DSM (including Betas) * 2 x Western Digital WD30EZRX

jahlives
Enlightened
Enlightened
Posts: 415
Joined: Thu Nov 29, 2007 10:01 am

Re: Connecting to the PhotoStation's database

Unread post by jahlives » Fri Jan 15, 2010 12:19 pm

The information for Photostation is stored in the PSQL database (postgres). php should have postgres support built in

Pokersut
Student
Student
Posts: 77
Joined: Thu Sep 20, 2007 10:02 am

Re: Connecting to the PhotoStation's database

Unread post by Pokersut » Fri Jan 15, 2010 12:23 pm

jahlives wrote:The information for Photostation is stored in the PSQL database (postgres). php should have postgres support built in
Okay, but if PhotoStation can access the database, postgres support must be present, right? If not, I imagine that you can somehow modify the php installation to include postgres support?
Main unit
DS-413 * Always latest DSM (including Betas) * 1 x Seagate ST4000DM000 + 1 x Western Digital WD40EFRX + 1 x Seagate ST3000DM001 + 1 x Western Digital WD30EZRX

Backup unit
DS-213+ * Always latest DSM (including Betas) * 2 x Western Digital WD30EZRX

jahlives
Enlightened
Enlightened
Posts: 415
Joined: Thu Nov 29, 2007 10:01 am

Re: Connecting to the PhotoStation's database

Unread post by jahlives » Fri Jan 15, 2010 1:17 pm

If postgres is not enabled in php then a re-compilation of the php source with postgres Support is necessary. But I guess the default php from Synology should include postgres support. Otherwise the Photostation would not work.
An alternative could be to install php from ipkg. But again imho postgress support should be included in Syno Default php

Pokersut
Student
Student
Posts: 77
Joined: Thu Sep 20, 2007 10:02 am

Re: Connecting to the PhotoStation's database

Unread post by Pokersut » Fri Jan 15, 2010 1:20 pm

jahlives wrote:If postgres is not enabled in php then a re-compilation of the php source with postgres Support is necessary. But I guess the default php from Synology should include postgres support. Otherwise the Photostation would not work.
An alternative could be to install php from ipkg. But again imho postgress support should be included in Syno Default php
Yes, it must be.

But I still need the php code to connect to the database and extract photos - anyone?
Main unit
DS-413 * Always latest DSM (including Betas) * 1 x Seagate ST4000DM000 + 1 x Western Digital WD40EFRX + 1 x Seagate ST3000DM001 + 1 x Western Digital WD30EZRX

Backup unit
DS-213+ * Always latest DSM (including Betas) * 2 x Western Digital WD30EZRX

jahlives
Enlightened
Enlightened
Posts: 415
Joined: Thu Nov 29, 2007 10:01 am

Re: Connecting to the PhotoStation's database

Unread post by jahlives » Fri Jan 15, 2010 1:22 pm

There is a application for postgres like phpmyadmin (http://phppgadmin.sourceforge.net/) for mysql. With that you could see the structure of the photostation tables and create your php code to read the necessary information von the DB

Pokersut
Student
Student
Posts: 77
Joined: Thu Sep 20, 2007 10:02 am

Re: Connecting to the PhotoStation's database

Unread post by Pokersut » Fri Jan 15, 2010 1:44 pm

jahlives wrote:There is a application for postgres like phpmyadmin (http://phppgadmin.sourceforge.net/) for mysql. With that you could see the structure of the photostation tables and create your php code to read the necessary information von the DB
Okay, I got it, and installed it in the web folder - but what should I use to log in ...? I have tried with username 'root' and the normal password (the one I use for the admin account and the MySQL database), but I get "Login forbidden".
Main unit
DS-413 * Always latest DSM (including Betas) * 1 x Seagate ST4000DM000 + 1 x Western Digital WD40EFRX + 1 x Seagate ST3000DM001 + 1 x Western Digital WD30EZRX

Backup unit
DS-213+ * Always latest DSM (including Betas) * 2 x Western Digital WD30EZRX

goetz
Knowledgeable
Knowledgeable
Posts: 336
Joined: Wed Mar 18, 2009 10:05 pm
Location: Berlin, Germany

Re: Connecting to the PhotoStation's database

Unread post by goetz » Fri Jan 15, 2010 2:35 pm

Hi,
try this from german Wiki.

Regards Goetz
DS-209+II / DSM 3.1-1742 /2x 2TB Seagate Constellation ES
DS-107+ / DSM 3.1-1613 / Samsung HD103UI
DS-106 / DSM 3.1-1742 (from 108j) / Hitachi HDP72505
eTrayZ / Samsung HD103SI

Pokersut
Student
Student
Posts: 77
Joined: Thu Sep 20, 2007 10:02 am

Re: Connecting to the PhotoStation's database

Unread post by Pokersut » Sat Jan 16, 2010 12:52 am

goetz wrote:Hi,
try this from german Wiki.
It works! The trick was to move the phpPgAdmin installation to the phpsrc directory and set up an Alias - I can now log in with my admin account credentials.

Thanks!
Main unit
DS-413 * Always latest DSM (including Betas) * 1 x Seagate ST4000DM000 + 1 x Western Digital WD40EFRX + 1 x Seagate ST3000DM001 + 1 x Western Digital WD30EZRX

Backup unit
DS-213+ * Always latest DSM (including Betas) * 2 x Western Digital WD30EZRX

bca_hbi
Trainee
Trainee
Posts: 16
Joined: Wed Feb 25, 2009 2:38 pm

Re: Connecting to the PhotoStation's database

Unread post by bca_hbi » Sun Jan 17, 2010 5:06 pm

What I want is to make a page (stored locally in the 'web' folder) that connects to the database and selects (and displays), say, 100 random pictures from all the photos that are stored in the 'photo' folder.
If you succeed in this then I'am really interested in the code and steps how you have set this up.

I'am already trying for months to have random pictures being displayed on my SPF-83V photoframe via RSS feeds. I tried installing Gallery2 on my Synology to have more RSS feed options (e.g. random) but then I have to upload all my photos to this Gallery2 webserver as well because I still want to keep Photostation up and running. With a Photostation database of 120.000 items this is no option. With a webpage displaying random images I would make a big step forward.

Erik
DS207+ (2x 750GB) DSM 3.1-1613
DS210+ (2x 2TB Seagate ST2000DL003-9VT1) DSM 4.0-2197, external Seagate BlackArmor WS110 2TB drive connected via eSATA

Pokersut
Student
Student
Posts: 77
Joined: Thu Sep 20, 2007 10:02 am

Re: Connecting to the PhotoStation's database

Unread post by Pokersut » Sun Jan 17, 2010 11:34 pm

bca_hbi wrote:I'am already trying for months to have random pictures being displayed on my SPF-83V photoframe via RSS feeds. I tried installing Gallery2 on my Synology to have more RSS feed options (e.g. random) but then I have to upload all my photos to this Gallery2 webserver as well because I still want to keep Photostation up and running. With a Photostation database of 120.000 items this is no option. With a webpage displaying random images I would make a big step forward.
That's funny - I have an SPF-83V as well!

Anyway, the trick to get this working is two things:

1) Install phpPgAdmin in the folder /usr/syno/synoman/phpsrc/phppgadmin/
2) Make an alias that redirects all requests to a specific subdir under the web folder to the phpPgAdmin folder. You do this by adding a line like this one to the file httpd-autoindex.conf-user in /usr/syno/apache/conf/extra/:

Code: Select all

Alias /phppgadmin/ "/usr/syno/synoman/phpsrc/phppgadmin/"
Now you can go to http://<your NAS IP address>/phppgadmin/, and it will already have access to the database.

I haven't had much time to play around with it, but the code I have so far displays one random picture - should be easy to modify it to show more:

Code: Select all

<?php  
header ('Content-Type: image/jpeg');
$db_handle = pg_connect("dbname=photo user=admin");  

if (!$db_handle) {   
echo "Connection failed!";
die();
} else {
$query = "SELECT path FROM photo_image ORDER BY random() LIMIT 1";  
$result = pg_exec($db_handle, $query);  

if ($result) {        
	for ($row = 0; $row < pg_numrows($result); $row++) {
	$values = pg_fetch_row($result, $row);
		for ($col = 0; $col < count($values); $col++) {
		$image = file_get_contents($values[$col]);
		echo $image;
		}
	}
pg_freeresult($result);  
pg_close($db_handle);  
}
}
?>
IMPORTANT: You MUST place this file in the same folder as the pgpPgAdmin installation (/usr/syno/synoman/phpsrc/phppgadmin/), or it won't work ... Still, because you have set up the alias, you can access the file via the subdir in the web folder, so if you call the file 'photos.php', you can access it at http://<your NAS IP address>/phppgadmin/photos.php

If you understand German, I suggest you check out the wiki article that is linked to above - it is really good.

Happy coding!

Mikkel
Main unit
DS-413 * Always latest DSM (including Betas) * 1 x Seagate ST4000DM000 + 1 x Western Digital WD40EFRX + 1 x Seagate ST3000DM001 + 1 x Western Digital WD30EZRX

Backup unit
DS-213+ * Always latest DSM (including Betas) * 2 x Western Digital WD30EZRX

bca_hbi
Trainee
Trainee
Posts: 16
Joined: Wed Feb 25, 2009 2:38 pm

Re: Connecting to the PhotoStation's database

Unread post by bca_hbi » Mon Jan 18, 2010 10:54 pm

Hi Mikkel,

Thank you very much for this clear description. I followed all steps and with some additional help of the German wiki I was able to get your piece of code up and running.

So now I only have to add some kind of rss-feed which is accepted by my SPF-83V photoframe.
Or do you already have plans to build this yourself since you have apperently the same photoframe. If yes, is it then allowed to wait for this a few more days :D

Erik
DS207+ (2x 750GB) DSM 3.1-1613
DS210+ (2x 2TB Seagate ST2000DL003-9VT1) DSM 4.0-2197, external Seagate BlackArmor WS110 2TB drive connected via eSATA

Pokersut
Student
Student
Posts: 77
Joined: Thu Sep 20, 2007 10:02 am

Re: Connecting to the PhotoStation's database

Unread post by Pokersut » Tue Jan 19, 2010 7:48 pm

bca_hbi wrote:So now I only have to add some kind of rss-feed which is accepted by my SPF-83V photoframe.
Or do you already have plans to build this yourself since you have apperently the same photoframe. If yes, is it then allowed to wait for this a few more days :D
I do have plans to make an rss feed, but honestly I don't know when I will have the time. I will post whatever I end up with here, I don't know when it will be.

If you decide to try it on your own, do share your results! I have previously tried to make my own rss feed by looking at the ones that are available from within PhotoStation, but that was before I had gained access to the database, so I never got very far, because I didn't know all the internal database values: A typical link to a photo in the rss feed will look something like this

Code: Select all

http://192.168.1.100/photo/photo_one.php?dir=42696c6c6564657220667261206469676974616c6b616d6572612f323030392f303420617072696c&name=44534346343236332e6a7067
and you need access to the database to know the values of 'dir' and 'name'. Now that I have access, I will give it another try.

Mikkel
Main unit
DS-413 * Always latest DSM (including Betas) * 1 x Seagate ST4000DM000 + 1 x Western Digital WD40EFRX + 1 x Seagate ST3000DM001 + 1 x Western Digital WD30EZRX

Backup unit
DS-213+ * Always latest DSM (including Betas) * 2 x Western Digital WD30EZRX

mpirretti
I'm New!
I'm New!
Posts: 2
Joined: Sat Mar 05, 2011 11:37 pm

Re: Connecting to the PhotoStation's database

Unread post by mpirretti » Sat Mar 05, 2011 11:52 pm

I realize that this post is about a year old. However, I followed the directions above and managed to get local access to the postgres server.

I also figured out how to extend this access beyond just the local host. Since I am running this on a home NAT, I set it up to allow access from any machine on my local network. This way I can run a web server (e.g. Glassfish) on another machine and use the synology machine as a pure database (e.g. with full access to the photo database from the web server).

You will need to edit the postgres authentication config file:

Code: Select all

vi /usr/syno/pgsql/etc/pg_hba.conf
Go to the end of the file. You should see several entries such as:

Code: Select all

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
##host    all         all         ::1/128               md5
Add another entry to allow remote access. For me, my local network is 192.168.1.*, so I added the following:

Code: Select all

host    all         all         192.168.1.0/24        trust
Once you do this, you may as well restart postgres:

Code: Select all

/usr/syno/etc/rc.d/S20pgsql.sh restart
At this point you can directly access the postgres databases from a remote machine on your local network.

bca_hbi
Trainee
Trainee
Posts: 16
Joined: Wed Feb 25, 2009 2:38 pm

Re: Connecting to the PhotoStation's database

Unread post by bca_hbi » Mon Mar 14, 2011 8:55 pm

mpirretti wrote:This way I can run a web server (e.g. Glassfish) on another machine and use the synology machine as a pure database (e.g. with full access to the photo database from the web server).
This sounds great but I have no idea about the possibilities of Glassfish. Is it easy to make an RSS feed of random images taken from the photo database?

Last year I only extended the original code of Pokersut a bit. I now have the ability to define two parameters to specify the range of random photos to retrieve.
I have all my photos located in a directory structure like '/volume1/photo/Event folders/<year>'.
By using for example the URL "http://<IP-address>:5002/phppgadmin/photo.php?year=2010&name=Vacation" I will get back a random photo from the year 2010 with in the filename the word 'Vacation' (none case sensitive).

Unfortunately I still did not spent enough time to make an RSS feed of it but if you like you can have it.

Code: Select all

<?php  
header ('Content-Type: image/jpeg');

$base_path = '/volume1/photo/Event folders/';

# Parse parameters 
$arr = parse_url($_SERVER['REQUEST_URI']); 
$parameters = $arr["query"];
parse_str($parameters, $data);
$extend_path = $data['year'] . '%';
$name = $data['name'] . '%';
   
$db_handle = pg_connect("dbname=photo user=admin");  
if (!$db_handle) {   
	echo "Connection failed!";
	die();
} else {
	$query = "SELECT path,name FROM photo_image WHERE path LIKE '$base_path$extend_path' AND upper(name) LIKE upper('%$name%') ORDER BY random() LIMIT 1";  
	$result = pg_exec($db_handle, $query);  

	if ($result) {        
		for ($row = 0; $row < pg_numrows($result); $row++) {
			$values = pg_fetch_row($result, $row);
			for ($col = 0; $col < count($values); $col++) {
				$image = file_get_contents($values[$col]);
				echo $image;
			}
		}
		pg_freeresult($result);  
		pg_close($db_handle);  
	} 
}
?>
DS207+ (2x 750GB) DSM 3.1-1613
DS210+ (2x 2TB Seagate ST2000DL003-9VT1) DSM 4.0-2197, external Seagate BlackArmor WS110 2TB drive connected via eSATA

Locked

Return to “MySQL/PHP Mods”