bind results from the CPAN

bind

App-PhotoDB

view release on metacpan or search on metacpan


    +-----------------+-------------+------------------------------------------------------------------------------+
    | 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 1.846 second using v1.01-cache-2.11-cpan-2398b32b56e )