view release on metacpan or  search on metacpan

lib/App/PhotoDB/funcs.pm  view on Meta::CPAN

=head2 runmigrations

Run database migrations

=head4 Usage

    &runmigrations($db);

=head4 Arguments

=item * C<$db> DB handle

=head4 Returns

Nothing

=cut

sub runmigrations {
	my $dbh = shift;
	use DB::SQL::Migrations;
	my $migrator = DB::SQL::Migrations->new(dbh=>$dbh, migrations_directory=>'migrations');

	print "Checking database schema... \n";

	# Creates migrations table if it doesn't exist
	$migrator->create_migrations_table();

	# Run migrations
	$migrator->apply();

	return;
}

# Update an existing record in any table

=head2 updaterecord

Update an existing record in any table

=head4 Usage

    my $rows = &updaterecord({db=>$db, data=>\%data, table=>'FILM', where=>{film_id=>$film_id}});

=head4 Arguments

=item * C<$db> DB handle

=item * C<$data> Hash of new values to update

=item * C<$table> Name of table to update

=item * C<$where> Where clause, formatted for SQL::Abstract

=item * C<$silent> Suppress output

=item * C<$log> Write an event to the database log. Defaults to C<1>.

=head4 Returns

The number of rows updated

=cut

sub updaterecord {
	# Pass in a hashref of arguments
	my $href = shift;

	# Unpack the hashref and set default values
	my $db = $href->{db};			# DB handle
	my $data = $href->{data};		# Hash of new values to update
	my $table = $href->{table};		# Name of table to update
	my $where = $href->{where};		# Where clause, formatted for SQL::Abstract
	my $silent = $href->{silent} // 0;	# Suppress output
	my $log = $href->{log} // 1;	    # Write event to log

	# Quit if we didn't get params
	die 'Must pass in $db' if !($db);
	die 'Must pass in $data' if !($data);
	die 'Must pass in $table' if !($table);
	die 'Must pass in $where' if !($where);

	# Delete empty strings from data hash
	$data = &thin($data);

	if (scalar(keys %$data) == 0) {
		print "Nothing to update\n";
		return 0;
	}

	# Work out affected rows
	my $rowcount = &lookupval({db=>$db, col=>'count(*)', table=>$table, where=>$where});

	# 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

Delete an existing record from any table

=head4 Usage

    my $rows = &deleterecord({db=>$db, table=>'FILM', where=>{film_id=>$film_id}});

=head4 Arguments

=item * C<$db> DB handle

=item * C<$table> Name of table to delete from

=item * C<$where> Where clause, formatted for SQL::Abstract

=item * C<$silent> Suppress output

=item * C<$log> Write an event to the database log. Defaults to C<1>.

=head4 Returns

The number of rows deleted

=cut

sub deleterecord {
	# Pass in a hashref of arguments
	my $href = shift;

	# Unpack the hashref and set default values
	my $db = $href->{db};		   # DB handle
	my $table = $href->{table};	     # Name of table to delete from
	my $where = $href->{where};	     # Where clause, formatted for SQL::Abstract
	my $silent = $href->{silent} // 0;      # Suppress output