[Nottingham] REading filesystem into MySQL query

[Duncan John Fyfe]

On Thu, 2005-10-13 at 09:43 +0100, Moses O'Hara wrote:
> Hi all, 
> 
> long time no spot. 
> 
> I've got a filesystem and simply want to recursively read the file names, sizes and locations within that hierarchy and store each result into a MySQL database for poking at later. 
> 
> I had spoken to someone earlier this year about it and he spoke about using stored procedures by which time no amount of GPS wizardry was going to help me work out where the hell I was.
> 
> This is probably really simple but my SQL knowledge ends at SELECT. 
> 
> Any help appreciated. 
> 

Does MySQL support  SQL 'COPY' 
eg. COPY mytable  FROM 'flist' WITH DELIMITER '|' NULL AS '\N';

It is quicker to do big one off copies with this than with many INSERT.

eg.
	(shell)$ find $SEARCH_ROOT -printf '%f|%h|%b' > flist
	(mysql)$ COPY mytable FROM '/probably/need/full/path/see/what/the/mysql/docs/say/flist' WITH DELIMITER '|' NULL AS '\N';

man(1) find.  There are other options you may wish to use too such as:
       -noleaf (if it is a non UNIX filesystem)
     , -xdev   (stick to one filesystem)

and remember you probably want to avoid /dev , /proc , /sys and maybe /tmp.

Have fun,
Duncan
-- 
Duncan John Fyfe <djf at star.le.ac.uk>