Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
SPOPS::SQLInterface(3)User Contributed Perl DocumentatioSPOPS::SQLInterface(3)

NAME
       SPOPS::SQLInterface - Generic routines for DBI database interaction

SYNOPSIS
	# Make this class a parent of my class

	package	My::DBIStuff;
	use SPOPS::SQLInterface;
	@My::DBIStuff::ISA = qw( SPOPS::SQLInterface );

	# You should also be able to use it directly, but you
	# need to pass in a database object with every request

	use SPOPS::SQLInterface;
	my $dbc	= 'SPOPS::SQLInterface';
	my $db = DBI->connect( ... ) ||	die $DBI::errstr;
	my $rows = $dbc->db_select({ select => [ qw/ uid first_name last_name /	],
				     from   => [ 'users' ],
				     where  => 'first_name = ? or last_name = ?',
				     value  => [ 'fozzie', "th'	bear" ],
				     db	    => $db });
	foreach	my $row	( @{ $results }	) {
	  print	"User ID $row->[0] is $row->[1]	$row->[2]\n";
	}

DESCRIPTION
       You are meant to	inherit	from this class, although you can use it as a
       standalone SQL abstraction tool as well,	as long	as you pass the
       database	handle into every routine you call.

DATABASE METHODS
       Relatively simple methods to do the select, update, delete and insert
       statements, with	the right values and table names being passed in.

       All parameters are passed in via	named values, such as:

	$t->db_select({	select => [ 'this', 'that' ],
			from   => [ 'mytable' ]	});

       VERY IMPORTANT

       The subclass that uses these methods must either	pass in	a DBI database
       handle via a named parameter (db) or make it available through a	method
       of the class called 'global_datasource_handle'.

METHODS
       There are very few methods in this class, but each one can do quite a
       bit.

   sql_quote( $value, $type, $db )
       Quotes a	value for insertion/update or selection	when bound parameters
       are inappropriate or unavailable. Returns 'NULL'	if $value is undef,
       otherwise calls "quote( $value, $type )"	on the DBI handle $db.

       Drivers that do not implement the two-argument form of "quote()"	should
       override	this method.

       Returns:	quoted $value appropriate to use in a SQL statement

   db_select( \%params )
       Executes	a SELECT. Return value depends on what you ask for. Many of
       the parameters are optional unless you pass in SQL to execute.

       Parameters:

       sql ($) (optional)

       Full statement to execute, although you may put '?' in the where	clause
       and pass	values for substitution. (No quoting hassles...)

       select (\@) (optional unless 'sql' defined)

       Fields to select

       select_modifier ($) (optional)

       Clause to insert	between	'SELECT' and fields (e.g., DISTINCT)

       from (\@	or $) (optional	unless 'sql' defined)

       List of tables to select	from. (You can pass a single tablename as a
       scalar if you wish.)

       order ($) (optional)

       Clause to order results by; if not given, the order depends entirely on
       the database.

       group ($) (optional)

       Clause to group results by (in a	'GROUP BY' clause). This is normally
       only done with 'COUNT(*)' and such features. See	your favorite SQL
       reference for more info.

       where ($) (optional unless 'sql'	defined)

       Clause to limit results.	Note that you can use '?' for field values but
       they will get quoted as if they were a SQL_VARCHAR type of value.

       return ($) (optional)

       What the	method should return. Potential	values are:

       o   'list': returns an arrayref of arrayrefs (default)

       o   'single': returns a single arrayref

       o   'hash': returns an arrayref of hashrefs

       o   'single-list': returns an arrayref with the first value of each
	   record as the element.

       o   'sth': Returns a DBI	statement handle that has been prepared	and
	   executed with the proper values. Use	this if	you are	executing a
	   query that may return a lot of rows but you only want to retrieve
	   values for some of the rows.

       value (\@) (optional unless you use '?' placeholders)

       List of values to bind, all as SQL_VARCHAR; they	must match order of
       '?' in the where	clause either passed in	or within the SQL statement
       passed in.

       Examples:

       Perl statement:

	$t->db_select( { select	=> [ qw/ first_name last_name /],
			 from	=> [ 'users' ],
			 where	=> 'last_name LIKE ?',
			 value	=> 'moo%' } );

       SQL statement:

	SELECT first_name, last_name
	  FROM users
	 WHERE last_name LIKE 'moo%'

       Returns:

	[ [ 'stephen', 'moore' ],
	  [ 'charles', 'mooron'	],
	  [ 'stacy', 'moonshine' ] ]

       Perl statement:

	$t->db_select( { select	=> [ qw/ u.username l.login_date / ],
			 from	=> [ 'users u',	'logins	l' ],
			 where	=> "l.login_date > '2000-04-18'	and u.uid = l.uid"
			 return	=> 'hash' } );

       SQL statement:

	SELECT u.username, l.login_date
	  FROM users u,	logins l
	 WHERE l.login_date > '2000-04-18' and u.uid = l.uid

       Returns:

	[ { username =>	'smoore',
	    login_date => '2000-05-01' },
	  { username =>	'cmooron',
	    login_date => '2000-04-19' },
	  { username =>	'smoonshine',
	    login_date => '2000-05-02' } ]

       Perl statement:

	$t->db_select( { select	=> [ qw/ login_name first_name last_name /],
			 from	=> [ 'users' ],
			 where	=> 'last_name LIKE ?',
			 value	=> 'moo%',
			 return	=> 'single-list' } );

       SQL statement:

	SELECT login_name, first_name, last_name
	  FROM users
	 WHERE last_name LIKE 'moo%'

       Returns:

	[ 'smoore',
	  'cmooron',
	  'smoonshine' ]

   db_insert( \%params )
       Create and execute an INSERT statement given the	parameters passed in.
       Return value is true is insert was successful --	the exact value	is
       whatever	is returned from the "execute()" statement handle call from
       your database. (See DBI and your	driver docs.)

       Parameters:

       sql ($) (optional)

       Full SQL	statement to run; you can still	pass in	values to quote/bind
       if you use '?' in the statement.

       table ($) (optional unless 'sql'	defined)

       Name of table to	insert into

       field (\@) (optional unless 'sql' defined)

       List of fieldnames to insert

       value (\@) (optional unless you use '?' placeholders)

       List of values, matching	up with	order of field list.

       no_quote	(\%) (optional)

       Fields that we should not quote

       return_sth ($) (optional)

       If true,	return the statement handle rather than	a status.

       Examples:

       Perl statement:

	$t->db_insert( { table => 'users',
			 field => [ qw/	username first_name last_name password / ],
			 value => [ 'cmw817', "Chris O'Winters"	] } );

       SQL statement:

	INSERT INTO users
	( username, first_name,	last_name, password )
	VALUES
	( 'cmw817', 'Chris', 'O''Winters', NULL	)

       Perl statement:

	my $sql	= qq/
	  INSERT INTO users ( username ) VALUES	( ? )
	/;

	foreach	my $username ( qw/ chuck stinky	jackson	/ ) {
	  $t->db_insert({ sql	=> $sql,
			  value	=> [ $username ] } );
	}

       SQL statements:

	INSERT INTO users ( username ) VALUES (	'chuck'	)
	INSERT INTO users ( username ) VALUES (	'stinky' )
	INSERT INTO users ( username ) VALUES (	'jackson' )

   db_update( \%params )
       Create and execute an UPDATE statement given the	parameters passed in.
       Return value is true is update was successful --	the exact value	is
       whatever	is returned from the "execute()" statement handle call from
       your database, which many times is the number of	rows affected by the
       update. (See DBI	and your driver	docs --	in particular, note that the
       return value from an UPDATE can vary depending on the database being
       used as well as the number of records actually updated versus those
       that matched the	criteria but were not updated because they already
       matched the value(s). In	particular, see	the discussion in DBD::mysql
       under 'mysql_client_found_rows'.)

       Parameters:

       sql ($) (optional)

       Full SQL	statement to run; note that you	can use	'?' for	values and
       pass in the raw values via the 'value' parameter, and they will be
       quoted as necessary.

       field (\@) (optional unless 'sql' defined)

       List of fieldnames we are updating

       value (\@) (optional unless you use '?' placeholders)

       List of values corresponding to the fields we are updating and to
       parameters in the WHERE clause.

       table ($) (optional unless 'sql'	defined)

       Name of table we	are updating

       where ($) (optional unless 'sql'	defined)

       Clause that specifies the rows we are updating

       no_quote	(\%) (optional)

       Specify fields not to quote

       Examples:

       Perl statement:

	$t->db_update( { field => [ qw/	first_name last_name / ],
			 value => [ 'Chris', "O'Donohue" ],
			 table => 'users',
			 where => 'user_id = 98172' } );

       SQL statement (assuming "'" gets	quoted as "''"):

	UPDATE users
	   SET first_name = 'Chris',
	       last_name = 'O''Donohue',
	 WHERE user_id = 98172

   db_delete( \%params )
       Removes the record indicated by \%params	from the database. Return
       value is	true is	delete was successful -- the exact value is whatever
       is returned from	the "execute()"	statement handle call from your
       database. (See DBI)

       Parameters:

       sql ($) (optional)

       Full SQL	statement to execute directly, although	you can	use '?'	for
       values and pass the actual values in via	the 'value' parameter.

       table ($) (optional unless 'sql'	defined)

       Name of table from which	we are removing	records.

       where ($) (optional unless 'sql'	defined)

       Specify the records we are removing. Be careful:	if you pass in the
       table but not the criteria, you will clear out your table! (Just	like
       real SQL...)

       value (\@) (optional unless you use '?' placeholders)

       List of values to bind to '?' that may be found either in the where
       clause passed in	or in the where	clause found in	the SQL	statement.

       Examples:

       Perl statement:

	$t->db_delete( { table => 'users', where => 'user_id = 98172' }	);

       SQL statement:

	DELETE FROM users
	 WHERE user_id = 98172

       Perl statement:

	$t->db_delete( { table => 'users', where => 'last_name LIKE ?',
			 value => [ 'moo%' ] } );

       SQL statement:

	DELETE FROM users
	 WHERE last_name LIKE 'moo%'

       Perl statement:

	$t->db_delete( { table => 'users' } );

       SQL statement:

	DELETE FROM users

       Oops, just cleared out the 'users' table. Be careful!

   db_discover_types( $table, \%params )
       Retrieve	field type information for $table. Normally we simply issue a
       dummy query to a	particular table to get	its schema -- field names and
       field types. We cache the information (in a SPOPS::DBI::TypeInfo
       object) and then	query it for the different field types as we need
       them.

       If a DBD	driver does not	support	the "{TYPE}" attribute of the DBI
       statement handle, you have to specify some simple types in your class
       configuration or	provide	them either in the call	to
       "db_discover_types()" or	in the object configuration using the key
       'dbi_type_info' as listed in the	parameters below. (See
       SPOPS::DBI::TypeInfo for	an enumeration of the the values to use.)

       Return a	SPOPS::DBI::TypeInfo object for	$table.

       Parameters:

       table ($)

       The name	of a particular	table. Note that this routine is not smart
       enough to distinguish between: users and	dbo.users even though they
       might refer to the same table in	the database. It is not	harmful	if you
       use the same name twice in this manner, the module just has to do a
       little extra work.

       Other parameters:

       db (object) (optional)
	   DBI database	handle.	(Optional only if you have a
	   "global_datasource_handle()"	class method defined.

       dbi_type_info (\%) (optional)
	   If your DBD driver cannot retrieve type information from the
	   database, you need to give this module a hint as to what type of
	   datatypes you will be working with. See "Fake Types"	in
	   SPOPS::DBI::TypeInfo	for the	types you can use.

       Example:

	 my $type_info = $class->db_discover_types( $class->table_name );
	 foreach my $field ( $type_info->get_fields ) {
	     print "$field is DBI type ", $type_info->get_type(	$field ), "\n";
	 }

ERROR HANDLING
       All errors encountered by this module throw a SPOPS::Exception object
       (in case	of a lack of required information) or, in most cases, a
       SPOPS::Exception::DBI object.

TO DO
       DBI binding conventions

       One of the things the DBI allows	you to do is prepare a statement once
       and then	execute	it many	times -- particularly useful for INSERTs and
       UPDATEs.	It would be nice to be able to do that.

       Datasource Names

       Be able to pass a name to 'global_datasource_handle' (and to pass in
       that name to the	relevant 'db_*'	calls).

BUGS
       None known.

SEE ALSO
       DBI

COPYRIGHT
       Copyright (c) 2001-2004 intes.net, inc..	All rights reserved.

       This library is free software; you can redistribute it and/or modify it
       under the same terms as Perl itself.

AUTHORS
       Chris Winters <chris@cwinters.com>

       See the SPOPS module for	the full author	list.

perl v5.32.1			  2004-06-02		SPOPS::SQLInterface(3)

NAME | SYNOPSIS | DESCRIPTION | DATABASE METHODS | METHODS | ERROR HANDLING | TO DO | BUGS | SEE ALSO | COPYRIGHT | AUTHORS

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=SPOPS::SQLInterface&sektion=3&manpath=FreeBSD+13.0-RELEASE+and+Ports>

home | help