App-PhotoDB
view release on metacpan or search on metacpan
docs/SCHEMA.pod view on Meta::CPAN
+-----------------+-------------+------------------------------------------------------------------------------+
| COLUMN_NAME | COLUMN_TYPE | COLUMN_COMMENT |
+-----------------+-------------+------------------------------------------------------------------------------+
| archive_id | int(11) | Unique ID of this archive |
| archive_type_id | int(11) | ID of this type of archive |
| name | varchar(45) | Name of this archive |
| max_width | int(11) | Maximum width of media that this archive can store |
| max_height | int(11) | Maximum height of media that this archive can store |
| location | varchar(45) | Location of this archive |
| storage | varchar(45) | The type of storage used for this archive, e.g. box, folder, ringbinder, etc |
| sealed | tinyint(1) | Whether or not this archive is sealed (closed to new additions) |
+-----------------+-------------+------------------------------------------------------------------------------+
=head2 ARCHIVE_TYPE
Table to list the different types of archive available for materials
+-----------------+-------------+------------------------------+
| COLUMN_NAME | COLUMN_TYPE | COLUMN_COMMENT |
+-----------------+-------------+------------------------------+
lib/App/PhotoDB/funcs.pm view on Meta::CPAN
# Dump data for debugging
print "\n\nThis is what I will update into $table where:\n" unless $silent;
print Dump($where) unless $silent;
print Dump($data) unless $silent;
print "\n$rowcount records will be updated\n" unless $silent;
print "\n" unless $silent;
# Build query
my $sql = SQL::Abstract->new;
my($stmt, @bind) = $sql->update($table, $data, $where);
# Final confirmation
unless ($silent) {
if (!&prompt({default=>'yes', prompt=>'Proceed?', type=>'boolean'})) {
print "Aborted!\n";
return;
}
}
# Execute query
my $sth = $db->prepare($stmt);
my $rows = $sth->execute(@bind);
$rows = &unsci($rows);
print "Updated $rows rows\n" unless $silent;
&logger({db=>$db, type=>'EDIT', message=>"$table $rows rows"}) if $log;
return $rows;
}
# Delete an existing record in any table
=head2 deleterecord
lib/App/PhotoDB/funcs.pm view on Meta::CPAN
# Work out affected rows
my $rowcount = &lookupval({db=>$db, col=>'count(*)', table=>$table, where=>$where});
# Dump data for debugging
print "\n\nI will delete from $table where:\n" unless $silent;
print Dump($where) unless $silent;
print "$rowcount records will be deleted\n" unless $silent;
# Build query
my $sql = SQL::Abstract->new;
my($stmt, @bind) = $sql->delete($table, $where);
# Final confirmation
unless ($silent) {
if (!&prompt({default=>'yes', prompt=>'Proceed?', type=>'boolean'})) {
print "Aborted!\n";
return;
}
}
# Execute query
my $sth = $db->prepare($stmt);
my $rows = $sth->execute(@bind);
$rows = &unsci($rows);
print "Deleted $rows rows\n" unless $silent;
&logger({db=>$db, type=>'DELETE', message=>"$table $rows rows"}) if $log;
return $rows;
}
=head2 newrecord
Insert a record into any table
lib/App/PhotoDB/funcs.pm view on Meta::CPAN
# Delete empty strings from data hash
$data = &thin($data);
# Dump data for debugging
print "\n\nThis is what I will insert into $table:\n" unless $silent;
print Dump($data) unless $silent;
print "\n" unless $silent;
# Build query
my $sql = SQL::Abstract->new;
my($stmt, @bind) = $sql->insert($table, $data);
# Final confirmation
unless ($silent) {
if (!&prompt({default=>'yes', prompt=>'Proceed?', type=>'boolean'})) {
print "Aborted!\n";
return;
}
}
# Execute query
my $sth = $db->prepare($stmt);
$sth->execute(@bind);
# Display inserted row
my $insertedrow = $sth->{mysql_insertid};
print "Inserted $table $insertedrow\n" unless $silent;
&logger({db=>$db, type=>'ADD', message=>"$table #$insertedrow"}) if $log;
return $insertedrow;
}
=head2 notimplemented
lib/App/PhotoDB/funcs.pm view on Meta::CPAN
my $char = $href->{char} // '+'; # character to use to signal that you want to enter a new row
my ($sth, $rows);
if ($query) {
# Use the manual query
$sth = $db->prepare($query) or die "Couldn't prepare statement: " . $db->errstr;
$rows = $sth->execute();
} elsif ($table && $cols && $where) {
# Use SQL::Abstract
my $sql = SQL::Abstract->new;
my($stmt, @bind) = $sql->select($table, $cols, $where);
$sth = $db->prepare($stmt);
$rows = $sth->execute(@bind);
} else {
die "Must pass in either query OR table, cols, where\n";
}
# No point in proceeding if there are no valid options to choose from
if ($rows == 0) {
print "No valid $keyword options to choose from\n";
if ($inserthandler && &prompt({prompt=>"Add a new $keyword?", type=>'boolean', default=>'no'})) {
# add a new entry
my $id = $inserthandler->({db=>$db});
lib/App/PhotoDB/funcs.pm view on Meta::CPAN
my $cols = $href->{cols} // ('id, opt'); # Part of the SQL::Abstract tuple
my $where = $href->{where} // {}; # Part of the SQL::Abstract tuple
my $order = $href->{order}; # Part of the SQL::Abstract tuple
print "Now showing $msg\n";
my ($sth, $rows);
if ($table && $cols && $where) {
# Use SQL::Abstract
my $sql = SQL::Abstract->new;
my($stmt, @bind) = $sql->select($table, $cols, $where, $order);
$sth = $db->prepare($stmt);
$rows = $sth->execute(@bind);
$rows = &unsci($rows);
} else {
print "Must pass in table, cols, where\n";
return;
}
while (my $ref = $sth->fetchrow_hashref) {
print "\t$ref->{id}\t$ref->{opt}\n";
}
return $rows;
lib/App/PhotoDB/funcs.pm view on Meta::CPAN
my $cols = $href->{cols} // '*'; # Part of the SQL::Abstract tuple
my $where = $href->{where} // {}; # Part of the SQL::Abstract tuple
my ($sth, $rows);
if ($query) {
$sth = $db->prepare($query) or die "Couldn't prepare statement: " . $db->errstr;
$rows = $sth->execute();
} elsif ($table && $cols && $where) {
# Use SQL::Abstract
my $sql = SQL::Abstract->new;
my($stmt, @bind) = $sql->select($table, $cols, $where);
$sth = $db->prepare($stmt);
$rows = $sth->execute(@bind);
} else {
print "Must pass in either query OR table, cols, where\n";
return;
}
my @array;
while (my $ref = $sth->fetchrow_hashref) {
$ref = &thin($ref);
push(@array, $ref);
}
lib/App/PhotoDB/funcs.pm view on Meta::CPAN
my $where = $href->{where} // {}; # Part of the SQL::Abstract tuple
my ($sth, $rows);
if ($query) {
# Use the manual query
$sth = $db->prepare($query) or die "Couldn't prepare statement: " . $db->errstr;
$rows = $sth->execute();
} elsif ($table && $col && $where) {
# Use SQL::Abstract
my $sql = SQL::Abstract->new;
my($stmt, @bind) = $sql->select($table, $col, $where);
$sth = $db->prepare($stmt);
$rows = $sth->execute(@bind);
} else {
print "Must pass in either query OR table, col, where\n";
return;
}
my $row = $sth->fetchrow_array();
return $row;
}
lib/App/PhotoDB/funcs.pm view on Meta::CPAN
my $db = $href->{db}; # DB handle
my $table = $href->{table}; # Part of the SQL::Abstract tuple
my $col = $href->{col}; # Part of the SQL::Abstract tuple
my $where = $href->{where} // {}; # Part of the SQL::Abstract tuple
my ($sth, $rows);
if ($table && $col && $where) {
# Use SQL::Abstract
my $sql = SQL::Abstract->new;
my($stmt, @bind) = $sql->select($table, $col, $where);
$sth = $db->prepare($stmt);
$rows = $sth->execute(@bind);
} else {
print "Must pass in table, col, where\n";
return;
}
my @list;
while (my @row = $sth->fetchrow_array()) {
push(@list, $row[0]);
}
return \@list;
lib/App/PhotoDB/funcs.pm view on Meta::CPAN
sub unsetdisplaylens {
my $href = shift;
my $db = $href->{db};
my %where;
$where{camera_id} = $href->{camera_id};
$where{display_lens} = $href->{lens_id};
my $thinwhere = &thin(\%where);
# Build query
my $sql = SQL::Abstract->new;
my($stmt, @bind) = $sql->update('CAMERA', {display_lens => undef}, $thinwhere);
# Execute query
my $sth = $db->prepare($stmt);
return $sth->execute(@bind);
}
=head2 welcome
Print a friendly welcome message
=head4 Usage
&welcome;
lib/App/PhotoDB/funcs.pm view on Meta::CPAN
'FocalLengthIn35mmFormat',
'LensSerialNumber',
'SerialNumber',
'LensMake',
'Copyright',
'UserComment',
);
# This is the query that fetches (and calculates) values from the DB that we want to write as EXIF tags
my $sql = SQL::Abstract->new;
my($stmt, @bind) = $sql->select('exifdata', '*', $where);
# Prepare and execute the SQL
my $sth = $db->prepare($stmt) or die "Couldn't prepare statement: " . $db->errstr;
my $rows = $sth->execute(@bind);
$rows = &unsci($rows);
# Get confirmation
if ($rows == 0) {
print "No scans be will tagged\n";
return;
}
return unless &prompt({prompt=>"This will review and potentially update the tags of $rows scans. Proceed?", type=>'boolean'});
# Set some globals
lib/App/PhotoDB/funcs.pm view on Meta::CPAN
sub tabulate {
my $href = shift;
my $db = $href->{db};
my $view = $href->{view};
my $cols = $href->{cols} // '*';
my $where = $href->{where} // {};
# Use SQL::Abstract
my $sql = SQL::Abstract->new;
my($stmt, @bind) = $sql->select($view, $cols, $where);
my $sth = $db->prepare($stmt);
my $rows = $sth->execute(@bind);
my $returnedcols = $sth->{'NAME'};
my @array;
my $table = Text::TabularDisplay->new(@$returnedcols);
while (my @row = $sth->fetchrow) {
$table->add(@row);
}
# print "$choices[$action]{'desc'}\n";
print $table->render;
print "\n";
lib/App/PhotoDB/handlers.pm view on Meta::CPAN
# Add a new physical archive for prints or films
sub archive_add {
my $href = shift;
my $db = $href->{db};
my %data;
$data{archive_type_id} = $href->{archive_type_id} // &listchoices({db=>$db, cols=>['archive_type_id as id', 'archive_type as opt'], table=>'ARCHIVE_TYPE'});
$data{name} = $href->{name} // &prompt({prompt=>'What is the name of this archive?'});
$data{max_width} = $href->{max_width} // &prompt({prompt=>'What is the maximum width of media that this archive can accept (if applicable)?'});
$data{max_height} = $href->{max_height} // &prompt({prompt=>'What is the maximum height of media that this archive can accept (if applicable)?'});
$data{location} = $href->{location} // &prompt({prompt=>'What is the location of this archive?'});
$data{storage} = $href->{storage} // &prompt({prompt=>'What is the storage type of this archive? (e.g. box, folder, ringbinder, etc)'});
$data{sealed} = $href->{sealed} // &prompt({default=>'no', prompt=>'Is this archive sealed (closed to new additions)?', type=>'boolean'});
return &newrecord({db=>$db, data=>\%data, table=>'ARCHIVE'});
}
# Bulk-add multiple films to an archive
sub archive_films {
my $href = shift;
my $db = $href->{db};
my %data;
my $minfilm = $href->{minfilm} // &prompt({prompt=>'What is the lowest film ID in the range?', type=>'integer'});
lib/App/PhotoDB/handlers.pm view on Meta::CPAN
# Read in cmdline args
my $href = shift;
my $db = $href->{db};
my $film_id = $href->{film_id} // &film_choose({db=>$db});
# Make sure basepath is valid
my $basepath = &basepath;
# Find matching scans
my $sql = SQL::Abstract->new;
my($stmt, @bind) = $sql->select('scans_negs', '*', {film_id=>$film_id});
my $sth = $db->prepare($stmt) or die "Couldn't prepare statement: " . $db->errstr;
my $rows = $sth->execute(@bind);
$rows = &unsci($rows);
return if ($rows == 0);
# Loop through our result set
while (my $ref = $sth->fetchrow_hashref()) {
# First check the path is defined in MySQL
if (defined($ref->{'filename'})) {
# Now make sure the path actually exists on the system
if (-e "$basepath/$ref->{'directory'}/$ref->{'filename'}") {
migrations/000-base-schema.sql view on Meta::CPAN
DROP TABLE IF EXISTS `ARCHIVE`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `ARCHIVE` (
`archive_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of this archive',
`archive_type_id` int(11) DEFAULT NULL COMMENT 'ID of this type of archive',
`name` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of this archive',
`max_width` int(11) DEFAULT NULL COMMENT 'Maximum width of media that this archive can store',
`max_height` int(11) DEFAULT NULL COMMENT 'Maximum height of media that this archive can store',
`location` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Location of this archive',
`storage` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'The type of storage used for this archive, e.g. box, folder, ringbinder, etc',
`sealed` tinyint(1) DEFAULT '0' COMMENT 'Whether or not this archive is sealed (closed to new additions)',
PRIMARY KEY (`archive_id`),
KEY `fk_ARCHIVE_3_idx` (`archive_type_id`),
CONSTRAINT `fk_ARCHIVE_3` FOREIGN KEY (`archive_type_id`) REFERENCES `ARCHIVE_TYPE` (`archive_type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to list all archives that exist for storing physical media';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `LIGHT_METER`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `LIGHT_METER` (
( run in 0.878 second using v1.01-cache-2.11-cpan-2398b32b56e )