#!/usr/bin/perl -w

=head1 NAME
recursive.filestats.sqlite_skel.pl [options]

=head1 SYNOPSIS

  % recursive.filestats.sqlite_skel.pl
            --startdir=/path/to/start/dir
			--user="username"  (not needed for SQLite)
			--pass="password"  (not needed for SQLite)
			--db="Database_name" (database filename for SQLite)
			--maxfiles=#  (max # of files to process)

=head1 DESCRIPTION

recursive.filestats.sqlite_skel.pl is an example perl script to
show how to use the Perl DBI on a data that exists on every
Linux machine - the files.  It stores info in 4 tables on various
parameters that make up a file's data - it's size, location,
how many inodes it takes, last modified, etc.  Once the program
runs, you can query those tables with the sqlite commandline
program or browse it with the SQLite browser (both available in
the Ubuntu applications tree.

In order to run this program run the following command in a Ubuntu-based
Linux distribution to install all the necessary applications & libs:

sudo apt-get install sqlite3 libdbd-sqlite3-perl sqlitebrowser \
libgetopt-mixed-perl libdbi-perl

=head1 COMMAND-LINE OPTIONS
--startdir=/path/to/start/dir - the top of the dir tree that you
                                want to use as input
--user="username" - needed for a 'real' database server like
                    PostgreSQL(PG) or MySQL but not SQLite)
--pass="password" - ditto
--db="Database_name" - the name of the database (a file in SQLite;
                       a database in MySQL or PG
--maxfiles=# - max # of files to process before stopping in case
               you direct it to an unexpectedly huge tree.


=head1 AUTHOR
Harry Mangalam <hjm@tacgi.com>, <harry.mangalam@uci.edu>
Copyright (c) 2008 UC Irvine

=cut



use File::Find; # Perl module to recurse thru dirs
use DBI; # Perl module to provide uniform database access to multiple databases
use DBD::SQLite; # Perl DBI module to call SQLite routines
#use DBD::Pg; # if use PostgreSQL, need this
use Getopt::Long; # Perl module for entering options on the commandline

# next line declares all variables are to be declared prior to use or with the prefix
# 'my' if using them as local variables.
use strict;

# 'use vars' to declare global variables.  Local variable can still be declared with the
# 'my' declaration ie:
# my $hjmvar = 98; # declares $hjmvar to be a local variable with value 98.  $hjmvar is
# undeclared outside of the local scope
use vars qw( $startdir $user $pass $dbname $maxfiles $table $ino $nlink $rdev
$blksize $blocks $content @directories $readsize $size $tmp $sth $dbh $pwd
$filenum $mode $uid $gid $filetype $atime $mtime $ctime $dev
);

# following structure is how you create options for the commandline (requires Getopt::Long)
# the names in the left column are the option names; the '=s, =i' are teh type declarations
# '=s' means that the input is tested for being a string
# '=i' means input is tested for being an integer
# '=f' means it's a float.
# the column on the right '\$startdir' is the variable that the value is carried by
&GetOptions("startdir=s"      => \$startdir,  # where to root the recursion
            "user=s"          => \$user,  # username for the DB connection(*)
            "pass=s"          => \$pass,      # password for the DB connection(*)
            "dbname=s"        => \$dbname,    # name for the DB
            "maxfiles=i"      => \$maxfiles   # max # of files to process
);

# (*) for SQLite, we don't really need a username or password as it's not a secure
# server DB, but rather a local file that contains the DB.  If you were going to
# use a server-based DB like PostgreSQL or mySQL, you'd need not only the username
# and password but a set of vars that defined the server name, username, password,
# port#, and perhaps other things to make the program client match the server
# requirements.  This is one reason why SQLite is so useful.

# set up defaults if they're not set by options
if (! defined  $user) { $user = "hjm";}
if (! defined  $dbname)   { $dbname = "filestats";}
if (! defined  $pass)     { $pass = "hjm";}
if (! defined  $table)    { $table = "info";}
if (! defined  $startdir) { $startdir = "./";} # careful where  you start...
if (! defined  $maxfiles) { $maxfiles = 200;} # set default to a low #

# set up the variables we'll need.
my ($sth, $dbh, $pwd); # Statement handle, DB handle, pass
my $filenum = 0; # define this file counter as an integer
my $mode = 0;
my $uid = 0;
my $gid = 0;
my $filetype = "ASCII";  # to define this var as a string
my $atime = my $mtime = my $ctime = 0; # another way of declaring and initializing vars
my $dev = $ino = $nlink= $rdev= $size= $size= $blksize= $blocks= $blocks = 0; #ditto
sub statstats(); # declaring our custom subroutine (defined at the bottom)


# Here's where we start to interact with the user with a simple question.  The template
# is: print out the question, wait for the answer, and process it, usually using some
# kind of regex processing.

# in the following query, the final [A] should indicate the common-sense default so
# if the user accidentally hits the <Enter> key without reading carefully, you don't
# get a catastrophic result
print "Start a [N]ew DB or [A]dd to the existing one? [A]\n";
# the following line takes whatever the user types and submits by hitting <Enter>
$tmp = <STDIN>;
# next line demonstrates perl's regex handling - it means "remove all the whitespace"
# literally: substitute one or more whitespace chars (/\s+/)(meaning tabs or spaces) and
# replace with nothing (//). The '/' character is being used here as a delimiter.
$tmp =~ s/\s+//g;

if ($tmp =~ /n/i){ # if there's an N or n in the response ('i' indicates ignore case)
	#   $filenum = 0;
	# Since we want to start a new DB, 1st we have to drop any existing DB of the same name.
	if (-e $dbname) { # if a file named $dbname exists, figger out what it is...
		# backtics are a Perlish way of exec'g a system command and capturing the output
		my $filetype = `file $dbname`;
		# following lines codes a crude check to make sure we're not deleting a useful file.
		if ($filetype =~ /SQLite/ || $filetype =~ /empty/) { # if it looks like it's an SQLite DB..
			print STDERR "WARN: There's an SQLite DB (or empty file) called $dbname in the way.\nAre you sure you want to delete it? [N]";
			$tmp = <STDIN>; $tmp =~ s/\s+//g;
			if ($tmp =~ /n/i || $tmp eq "") { die "OK - you fix it and restart\n"; }
			else {
				system "rm $dbname"; # ..delete it
				print STDERR "NOTICE: OK - it's deleted.  Continuing...\n";
			}
		} else { # It's not an SQLite DB, so deal with it outside of this program
			die "There's a file named $dbname that's not an SQLite DB - please deal with it and then restart.";
		}
	}
	# next line opens the DB handle; we pass in the name that we defined above and also use
	# the vars $user and $pass since we want to maintain the format with other DBs.
	# For SQLite, we could use blanks in their place, but the perl lib will execute very slowly
	# try it.
	# Note that this just establishes communication to an empty database; there's no tables
	# & certainly no data in it yet.
	$dbh = DBI->connect("dbi:SQLite:dbname=$dbname", $user, $pass, {AutoCommit => 0}) or die "can't connect to filestats";

	# below, we use the $dbh to set up statements to create the tables we'll use in the database
	# note that the '$dbh->do' format executes the statement immediately. Note that this
	# table structure is arbitrary and doesn't really do much that a flat file couldn't do
	# but it does allow the data to be queried.

	# table "location"
	$dbh->do( "CREATE TABLE location (
		pk         integer PRIMARY KEY,
		device     integer,
		inode      integer,
		nlinks     integer,
		rdev       integer,
		isize      integer,
		fsize      float,
		blksize    integer,
		iblocks    integer,
		fblocks    float,
		filename   varchar(80),
		dirpath    varchar(128)
	);");

	# table "ownership"
	$dbh->do( "CREATE TABLE ownership (
		fk_lo      integer REFERENCES location,
		mode       integer,
		uid        integer,
		gid        integer,
		filename   varchar(80)
	);");

	# table "content"
	$dbh->do( "CREATE TABLE content(
		fk_lo      integer REFERENCES location,
		filename   varchar(80),
		filetype   varchar(32),
		content    varchar(256)
	);");

	#table "timestamp"
	$dbh->do( "CREATE TABLE timestamp (
		fk_lo      integer REFERENCES location,
		atime      integer,
		imtime     integer,
		fmtime     float,
		ctime      integer,
		filename   varchar(80)
	);");

	# when printing statements that you don't want to go into the output stream, print to
	# STDERR as below.  While the statement is interleaved when the output goes to the console,
	# it will be separated if you redirect the output to a file (>file) or to a pipe (|).
	# also, it's good form to use the prefix (NOTICE: below) to indicate what kind of info
	# is being communicated. You might use WARN: to indicate something is odd, but surmountable
	# and ERROR! if something is very wrong.
	print STDERR "NOTICE: Setting PRAGMA default_synchronous = OFF\n";

	# next line uses $dbh to tell the DB to be asynnchronous; it tends to make it run
	# faster at a slight increase in risk of data loss.
	$dbh->do( "PRAGMA default_synchronous = OFF");


} else { # here's where we went if we want to ADD to the existing DB
   print STDERR "NOTICE: OK - continuing to add data to existing DB\n";
   # next line connects to the DB using the same vars as above.
   $dbh = DBI->connect("dbi:SQLite:dbname=$dbname", $user, $pass,{AutoCommit => 1}) or
          die "can't connect to filestats";
   # next lines prepare and execute an SQL 'select' statement on the DB to get the index
   # value of the last statement (where to insert additional statements)
   $sth =  $dbh->prepare(" select max(pk) from location;");
   $sth->execute;
   $filenum = $sth->fetchrow_array;
   print STDERR "\nNOTICE: primary key starts at: $filenum\n";
   }

print STDERR "Hit a key to start loading data, starting at $startdir .\n";
$tmp = <STDIN>;

# $startdir needs to be in an array context for the find function below.
@directories = ("$startdir"); # now it's a single element in array @directories
# now use the find() function from File::Find module to recurse down the
# $startdir to grab all the files, feeding them to the statstats function
# defined immediately below.
find(\&statstats, @directories);

sub statstats() {
	# statstats() takes the info from a stat() call, extracts some interesting bits
	# and then puts them into SQLite DB tables.
	my ($filename) = $_; # statstats() is called iteratively with a file name (=$_)
	chomp($pwd = `pwd`); # get the current dir & chew off the '\n' with chomp()
	# Perl supplies a huge list of the single-letter tests used below
	# view them with "perldoc -f '-f' " form a shell, if perldoc has been installed)
   if (-f $filename) {  # is the file a plain file (as opposed to a directory)?
      ($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size,$atime,$mtime,
             $ctime,$blksize,$blocks) = stat($_); # then call stat() on it.

      if (-T $filename) {  # if it's plain text
         $filetype = "ASCII";
         if ($size > 128) { $readsize = 128;}
         else { $readsize = $size;}
         open (FH, $filename) or die "can't open $filename - permissions?\n";
         if ($readsize > 0) { read FH, $content, $readsize;}
         else { $content = "ZERO LENGTH";}
      } else { $filetype = $content = "BINARY";}
   } else {
		print "\n\n$filename not a file\n";
   }
   # so now bung it into the DB; note the lining up to variables to values
   # for (relatively easy viewing.  DON'T be afraid to use up space for
   # easier comprehension of such mappings!!

	# the following 'my' prefix declares the variable local to this block
	# in this case, it's the global namespace and should be declared above, but
	# it's often used this way to 'bring a variable into protected existance when you want it
	# the following lines insert the data extracted about each file into the respective tables.
	my $rows = $dbh->do( "INSERT INTO location
	(pk,      device, inode, nlinks, rdev, isize, fsize,  blksize, iblocks,  fblocks , filename,    dirpath) VALUES (
	$filenum, $dev,  $ino,  $nlink, $rdev, $size, $size, $blksize, $blocks,  $blocks, '$filename', '$pwd' )" );

	$rows = $dbh->do( "INSERT INTO timestamp (
	fk_lo,    atime,  imtime, fmtime, ctime,    filename) VALUES (
  	$filenum, $atime, $mtime, $mtime, $ctime, '$filename' )" );

	# the quote() function 'protects' quoting embedded in the string ie:
	# this string's embedded "quoting" might be really mess'd by dif't /quoting/ styles
	my $correctedcontent = $dbh->quote($content);

	$rows = $dbh->do( "INSERT INTO content (
	fk_lo,      filename,   filetype,    content) VALUES (
	$filenum, '$filename', '$filetype', $correctedcontent)" );

	print "[$filenum] [$filename] [$size] \n"; # print out a running tally of the files

	$rows = $dbh->do( "INSERT INTO ownership (
	fk_lo,     mode,  uid,  gid,   filename) VALUES (
	$filenum, $mode, $uid, $gid, '$filename')" );

	$filenum++; # increment the file count
	if ($filenum > $maxfiles) { # if we've exceeded the max count, commit the db, disconnect, and die.
		$dbh->commit();
		$dbh->disconnect();
		die "we're done.\n";
	}
}
