Re: [GENERAL] Use PSQLFS for photo storage
Steven Lembark wrote: I would like to use PSQLFS(http://www.edlsystems.com/psqlfs/) to store 100 GB of images in PostgreSQL. Once they are in there I can deal with them. My main purpose is to use rsync to get the files into the database. Is there a better way to load 20,000 plus files reliably into Postgres? Don't: put them into a reasonably-organized filesystem and store the paths. This is exactly what I do not want to do. I do not want to deal with the file system at all except for initial import. I want to manage the photos entirely with SQL. I use dir names of date-venue-descr and basenames of the dir-image number (e.g., 2009.01.12-foo-bar/123-4567.nef). You'll probably find that moving the data into a viewer using a path will be simpler than pumping the raw data through postgres anyway. Please explain. What does moving the data into a viewer using a path mean. At that point you could create COPY statements that look like a pg_dump output to load the stuff the first time. Please elaborate.
Re: [GENERAL] Use PSQLFS for photo storage
On Tue, 2009-01-13 at 18:22 -0600, Jason Long wrote:
Never used Python or Perl. I use primarily Java. I was thinking of
doing something like
INSERT INTO pictures (filename,data) VALUES
('filename','/path/to/my/image/img0009.jpg');
But, this syntax doesn't seem to be supported.
Maybe I can use a custom C function to get the contents of the file. Then do
something like
INSERT INTO pictures (filename,data) VALUES
('/path/to/my/image/img0009.jpg',getBinaryFileContents('/path/to/my/image/img0009.jpg'));
Is there some postgres contrib for something like this?
Simple java stub test program attached. Modify to meet your needs. See
file header for URL of original example.
/**
* A demo program to show how jdbc works with postgresql
* Nick Fankhauser 10/25/01
* ni...@ontko.com or n...@fankhausers.com
* This program may be freely copied and modified
* Please keep this header intact on unmodified versions
* The rest of the documentation that came with this demo program
* may be found at http://www.fankhausers.com/postgresql/jdbc
*/
import java.sql.*; // All we need for JDBC
import java.text.*;
import java.io.*;
public class HelloPostgresql
{
Connection db;// A connection to the database
Statementsql; // Our statement to run queries with
DatabaseMetaData dbmd; // This is basically info the driver delivers
// about the DB it just connected to. I use
// it to get the DB version to confirm the
// connection in this example.
public HelloPostgresql(String argv[])
throws ClassNotFoundException, SQLException
{
String database = argv[0];
String username = argv[1];
String password = argv[2];
String imagefilepath = argv[3];
//System.out.println(img fp: +imagefilepath+\n);
Class.forName(org.postgresql.Driver); //load the driver
db = DriverManager.getConnection(jdbc:postgresql:+database,
username,
password); //connect to the db
dbmd = db.getMetaData(); //get MetaData to confirm connection
System.out.println(Connection to +dbmd.getDatabaseProductName()+ +
dbmd.getDatabaseProductVersion()+ successful.\n);
sql = db.createStatement(); //create a statement that we can use later
String sqlText = create table jdbc_demo_bytea (imgname text, img bytea);
PreparedStatement ps = db.prepareStatement(sqlText);
System.out.println(Executing this command: +sqlText+\n);
sql.executeUpdate(sqlText);
File file = new File(imagefilepath);
//System.out.println(filepath: +file.getPath()+\n);
String dupimagefilepath = new String(file.getPath() + _Duplicate);
//System.out.println(dup img fp: +dupimagefilepath+\n);
try {
FileInputStream fis = new FileInputStream(file);
long startts = System.currentTimeMillis();
ps = db.prepareStatement(INSERT INTO jdbc_demo_bytea VALUES (?, ?));
ps.setString(1, file.getName());
ps.setBinaryStream(2, fis, (int)file.length());
int rows_inserted = ps.executeUpdate();
//System.out.println(inserted: +rows_inserted+\n);
ps.close();
long stopts = System.currentTimeMillis();
System.out.println(Store time = + (stopts - startts));
fis.close();
}
catch ( Exception e)
{
System.out.println(e.toString());
}
//try {
// Thread.currentThread().sleep(1000);
long startts = System.currentTimeMillis();
ps = db.prepareStatement(SELECT img FROM jdbc_demo_bytea WHERE imgname = ?);
ps.setString(1, imagefilepath);
ResultSet rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
//System.out.println(fp : + rsmd.getColumnName(1) +\n);
//System.out.println(#columns : +rsmd.getColumnCount()+\n);
while (rs.next()) {
//System.out.println(got next\n);
//byte[] imgBytes = rs.getBytes(1);
byte[] imgBytes = new byte[4000];
imgBytes = rs.getBytes(1);
long stopts = System.currentTimeMillis();
System.out.println(Retreive time = + (stopts - startts));
try {
File dupfile = new File(dupimagefilepath );
//System.out.println(write it : +imgBytes.length+\n);
OutputStream fos = new FileOutputStream (dupfile ) ;
fos.write(imgBytes);
fos.close();
fos = null;
}
catch ( Exception e)
{
System.out.println(e.toString());
}
}
rs.close();
//}
// catch(InterruptedException ie){
//If this thread was intrrupted by nother thread
//}
ps.close();
sqlText = drop table jdbc_demo_bytea;
System.out.println(Executing this command: +sqlText+\n);
Re: [GENERAL] Use PSQLFS for photo storage
Reid Thompson wrote:
On Tue, 2009-01-13 at 18:22 -0600, Jason Long wrote:
Never used Python or Perl. I use primarily Java. I was thinking of
doing something like
INSERT INTO pictures (filename,data) VALUES
('filename','/path/to/my/image/img0009.jpg');
But, this syntax doesn't seem to be supported.
Maybe I can use a custom C function to get the contents of the file. Then do
something like
INSERT INTO pictures (filename,data) VALUES
('/path/to/my/image/img0009.jpg',getBinaryFileContents('/path/to/my/image/img0009.jpg'));
Is there some postgres contrib for something like this?
Simple java stub test program attached. Modify to meet your needs. See
file header for URL of original example.
Thanks for the sample. I will be using Hibernate with will make my code
much cleaner. I will post an example here when done. I never use
straight JDBC.
Re: [GENERAL] Use PSQLFS for photo storage
On Tuesday 13 January 2009, Jason Long mailing.l...@supernovasoftware.com wrote: I would like to use PSQLFS(http://www.edlsystems.com/psqlfs/) to store 100 GB of images in PostgreSQL. Once they are in there I can deal with them. My main purpose is to use rsync to get the files into the database. Is there a better way to load 20,000 plus files reliably into Postgres? A perl script using either bytea fields or the lo_ interface via DBD::Pg would work well. -- Alan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Use PSQLFS for photo storage
bytea was what I was going for. *Does anyone have a script they would share for this purpose? * If not I will probably use Java because this is what I am familiar with. The web app I will write for managing my photos will be written in Java. I want to be able to categorize, label, search, my photos. Alan Hodgson wrote: On Tuesday 13 January 2009, Jason Long mailing.l...@supernovasoftware.com wrote: I would like to use PSQLFS(http://www.edlsystems.com/psqlfs/) to store 100 GB of images in PostgreSQL. Once they are in there I can deal with them. My main purpose is to use rsync to get the files into the database. Is there a better way to load 20,000 plus files reliably into Postgres? A perl script using either bytea fields or the lo_ interface via DBD::Pg would work well.
Re: [GENERAL] Use PSQLFS for photo storage
On Jan 13, 2009, at 10:34 AM, Jason Long wrote: I would like to use PSQLFS(http://www.edlsystems.com/psqlfs/) to store 100 GB of images in PostgreSQL. Once they are in there I can deal with them. My main purpose is to use rsync to get the files into the database. Is there a better way to load 20,000 plus files reliably into Postgres? If it's a filesystem then you'd get the files into the system by copying them there. You wouldn't want to touch the database manually (that'd be like touching the raw disk device on a real filesystem). Conversely, it's just a filesystem. There's not really any use to putting a filesystem on top of a database on top of a filesystem other than the (significant) hack value. In other words, you probably don't really want to do this. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Use PSQLFS for photo storage
Steve Atkins wrote: On Jan 13, 2009, at 10:34 AM, Jason Long wrote: I would like to use PSQLFS(http://www.edlsystems.com/psqlfs/) to store 100 GB of images in PostgreSQL. Once they are in there I can deal with them. My main purpose is to use rsync to get the files into the database. Is there a better way to load 20,000 plus files reliably into Postgres? If it's a filesystem then you'd get the files into the system by copying them there. You wouldn't want to touch the database manually (that'd be like touching the raw disk device on a real filesystem). Conversely, it's just a filesystem. There's not really any use to putting a filesystem on top of a database on top of a filesystem other than the (significant) hack value. In other words, you probably don't really want to do this. Cheers, Steve I just want an easy way to load the files into the DB and their original path they were loaded from. Is possible through SQL to load a file into a bytea column? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Use PSQLFS for photo storage
On Tue, Jan 13, 2009 at 03:28:18PM -0600, Jason Long wrote: Steve Atkins wrote: On Jan 13, 2009, at 10:34 AM, Jason Long wrote: I would like to use PSQLFS(http://www.edlsystems.com/psqlfs/) to store 100 GB of images in PostgreSQL. Is there a better way to load 20,000 plus files reliably into Postgres? That would imply that they're around 5MB on average? If they're all under, say, 20MB (or maybe even much more) you should be able to handle it by doing the most naive things possible. I just want an easy way to load the files into the DB and their original path they were loaded from. Is possible through SQL to load a file into a bytea column? You'd need to generate the SQL somehow; if you know python it's probably a pretty easy 20 or 30 lines of code to get this working. psycopg seems to be the recommend way of accessing PG with python and you basically want to be doing something like: import psycopg2; filename = myimage.jpeg conn = psycopg2.connect(); conn.cursor().execute( INSERT INTO pictures (filename,data) VALUES (%s,%s);, [filename,psycopg2.Binary(open(filename,rb).read())]); conn.commit(); This seems to do the right thing for me, and obviously needs to be put into a loop of some sort. But it'll hopefully get you started. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Use PSQLFS for photo storage
Sam Mason wrote:
On Tue, Jan 13, 2009 at 03:28:18PM -0600, Jason Long wrote:
Steve Atkins wrote:
On Jan 13, 2009, at 10:34 AM, Jason Long wrote:
I would like to use PSQLFS(http://www.edlsystems.com/psqlfs/)
to store 100 GB of images in PostgreSQL.
Is there a better way to load 20,000 plus files reliably into Postgres?
That would imply that they're around 5MB on average? If they're all
under, say, 20MB (or maybe even much more) you should be able to handle
it by doing the most naive things possible.
*This is correct. They are all around 5 MB.*
I just want an easy way to load the files into the DB and their original
path they were loaded from.
Is possible through SQL to load a file into a bytea column?
You'd need to generate the SQL somehow; if you know python it's probably
a pretty easy 20 or 30 lines of code to get this working. psycopg seems
to be the recommend way of accessing PG with python and you basically
want to be doing something like:
import psycopg2;
filename = myimage.jpeg
conn = psycopg2.connect();
conn.cursor().execute(
INSERT INTO pictures (filename,data) VALUES (%s,%s);,
[filename,psycopg2.Binary(open(filename,rb).read())]);
conn.commit();
This seems to do the right thing for me, and obviously needs to be put
into a loop of some sort. But it'll hopefully get you started.
Sam
*Never used Python or Perl. I use primarily Java. I was thinking of
doing something like
*
*INSERT INTO pictures (filename,data) VALUES
('filename','/path/to/my/image/img0009.jpg');
But, this syntax doesn't seem to be supported.
Maybe I can use a custom C function to get the contents of the file. Then do
something like
***INSERT INTO pictures (filename,data) VALUES
('**/path/to/my/image/img0009.jpg**',getBinaryFileContents('/path/to/my/image/img0009.jpg'));
Is there some postgres contrib for something like this?**
Re: [GENERAL] Use PSQLFS for photo storage
On Tue, Jan 13, 2009 at 06:22:34PM -0600, Jason Long wrote:
Sam Mason wrote:
You'd need to generate the SQL somehow; if you know python it's probably
a pretty easy 20 or 30 lines of code to get this working.
*Never used Python or Perl. I use primarily Java. I was thinking of
doing something like
*INSERT INTO pictures (filename,data) VALUES
('filename','/path/to/my/image/img0009.jpg');
If you're OK with using large objects, instead of byteas, you can use
the lo_import function. You'd do something like:
CREATE TABLE pics (
path TEXT PRIMARY KEY,
data OID
);
INSERT INTO pics (path,data)
SELECT path, lo_import(path)
FROM (VALUES ('/path/to/my/image/img0009.jpg')) x(path);
This assumes that the files are accessable to the database server (i.e.
the paths are relative to the server daemon, not the psql command line
or whatever JDBC app you're driving this from).
Does that help any more?
Sam
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Use PSQLFS for photo storage
Sam Mason wrote:
On Tue, Jan 13, 2009 at 06:22:34PM -0600, Jason Long wrote:
Sam Mason wrote:
You'd need to generate the SQL somehow; if you know python it's probably
a pretty easy 20 or 30 lines of code to get this working.
*Never used Python or Perl. I use primarily Java. I was thinking of
doing something like
*INSERT INTO pictures (filename,data) VALUES
('filename','/path/to/my/image/img0009.jpg');
If you're OK with using large objects, instead of byteas, you can use
the lo_import function. You'd do something like:
CREATE TABLE pics (
path TEXT PRIMARY KEY,
data OID
);
INSERT INTO pics (path,data)
SELECT path, lo_import(path)
FROM (VALUES ('/path/to/my/image/img0009.jpg')) x(path);
This assumes that the files are accessable to the database server (i.e.
the paths are relative to the server daemon, not the psql command line
or whatever JDBC app you're driving this from).
Does that help any more?
Sam
*They are on the server. I would rather use bytea. Is it possible to
import them as large objects and then use SQL to convert them to bytea?*
Re: [GENERAL] Use PSQLFS for photo storage
On Tue, Jan 13, 2009 at 06:43:06PM -0600, Jason Long wrote: Sam Mason wrote: If you're OK with using large objects, instead of byteas, you can use the lo_import function. *They are on the server. I would rather use bytea. Is it possible to import them as large objects and then use SQL to convert them to bytea?* You can, but it's pretty backward. If you Java you'd probably be better off using it to slurp in the file and do the insert there. If you really want to use large objects to go to byteas have a look through the client side API[1] and then at the functions available to call from SQL by doing \df from psql as they mirror the client calls very closely. Be aware that you'll be creating a lot more garbage that PG will have to clean out, so importing many gigabytes of data this way will take much longer. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Use PSQLFS for photo storage
On Wed, Jan 14, 2009 at 12:56:42AM +, Sam Mason wrote: If you Java you'd probably be better off using it Hum, it's getting late. That should be If you *know* Java! Bed time for me I think! Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Use PSQLFS for photo storage
Sam Mason wrote: On Wed, Jan 14, 2009 at 12:56:42AM +, Sam Mason wrote: If you Java you'd probably be better off using it Hum, it's getting late. That should be If you *know* Java! Bed time for me I think! Sam Thanks for the advice. I will probably go with Java. In the inventory system I developed I am already storing documents via bytea. I will probably do the same with these images. I will be dealing with them via hibernate so I guess I should import them the same way. I was hoping for a already developed function that could import an entire directory structure recursively. I can do this easily enough in Java.
