API:EPrints/Database - Revision history

Pod2wiki at 21:20, 4 March 2022

2022-03-04T21:20:38Z

← Older revision		Revision as of 21:20, 4 March 2022
Line 1:		Line 1:
	<!-- Pod2Wiki=_preamble_		<!-- Pod2Wiki=_preamble_
	This page has been automatically generated from the EPrints 3.4 source. Any wiki changes made between the 'Pod2Wiki=*' and 'Edit below this comment' comments will be lost.		This page has been automatically generated from the EPrints 3.4 source. Any wiki changes made between the 'Pod2Wiki=*' and 'Edit below this comment' comments will be lost.
	-->{{API}}{{Pod2Wiki}}{{API:Source\|file=EPrints/Database.pm\|package_name=EPrints::Database}}[[Category:API~~\|DATABASE]][[Category:API:EPrints/Database~~\|DATABASE]]<div><!-- Edit below this comment -->		-->{{API}}{{Pod2Wiki}}{{API:Source\|file=EPrints/Database.pm\|package_name=EPrints::Database}}[[Category:API\|DATABASE]]<div><!-- Edit below this comment -->


Line 1,747:		Line 1,747:

	$message = $db->save_user_message( $userid, $m_type, $dom_m_data )		$message = $db->save_user_message( $userid, $m_type, $dom_m_data )
	Save user message provided in XML DOM object <tt>$dom_m_data</tt> as a sanitized string in a [[API:EPrints:DataObj/Message\|EPrints:DataObj::Message]] using <tt>$m_type</tt> to define the message type and <tt>$userid</tt> for the ID of the user whose message it is.		Save user message provided in XML DOM object <tt>$dom_m_data</tt> as a sanitized string in a [[API:EPrints/DataObj/Message\|EPrints::DataObj::Message]] using <tt>$m_type</tt> to define the message type and <tt>$userid</tt> for the ID of the user whose message it is.

	<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>		<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>

Pod2wiki at 23:41, 11 January 2022

2022-01-11T23:41:46Z

← Older revision		Revision as of 23:41, 11 January 2022
Line 1:		Line 1:
	<!-- Pod2Wiki=_preamble_		<!-- Pod2Wiki=_preamble_
	This page has been automatically generated from the EPrints 3.4 source. Any wiki changes made between the 'Pod2Wiki=*' and 'Edit below this comment' comments will be lost.		This page has been automatically generated from the EPrints 3.4 source. Any wiki changes made between the 'Pod2Wiki=*' and 'Edit below this comment' comments will be lost.
	-->{{API}}{{Pod2Wiki}}{{API:Source\|file=EPrints/Database.pm\|package_name=EPrints::Database}}[[Category:API\|DATABASE]]<div><!-- Edit below this comment -->		-->{{API}}{{Pod2Wiki}}{{API:Source\|file=EPrints/Database.pm\|package_name=EPrints::Database}}[[Category:API\|DATABASE]][[Category:API:EPrints/Database\|DATABASE]]<div><!-- Edit below this comment -->

Pod2wiki at 23:40, 11 January 2022

2022-01-11T23:40:19Z

← Older revision		Revision as of 23:40, 11 January 2022
Line 1:		Line 1:
	<!-- Pod2Wiki=_preamble_		<!-- Pod2Wiki=_preamble_
	This page has been automatically generated from the EPrints 3.4 source. Any wiki changes made between the 'Pod2Wiki=*' and 'Edit below this comment' comments will be lost.		This page has been automatically generated from the EPrints 3.4 source. Any wiki changes made between the 'Pod2Wiki=*' and 'Edit below this comment' comments will be lost.
	-->{{API}}{{Pod2Wiki}}{{API:Source\|file=EPrints/Database.pm\|package_name=EPrints::Database}}[[Category:API~~\|DATABASE]][[Category:API:EPrints/Database~~\|DATABASE]]<div><!-- Edit below this comment -->		-->{{API}}{{Pod2Wiki}}{{API:Source\|file=EPrints/Database.pm\|package_name=EPrints::Database}}[[Category:API\|DATABASE]]<div><!-- Edit below this comment -->


Line 21:		Line 21:
	Provides access to the backend database. All database access done via this module, in the hope that the backend can be replaced as easily as possible.		Provides access to the backend database. All database access done via this module, in the hope that the backend can be replaced as easily as possible.

	In most use-cases it should not be necessary to use the database module directly. Instead you should use [[API:EPrints/DataSet\|EPrints::DataSet]] or [[API:EPrints/MetaField\|EPrints::MetaField]] accessor methods to access objects and field values respectively.		In most use-cases it should not be necessary to use the database module directly. Instead you should use [[API:EPrints/DataSet\|EPrints::DataSet]] or [[API:EPrints/MetaField\|EPrints::MetaField]] accessor methods to access objects and field values respectively.

	<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>		<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 32:		Line 32:
	<!-- Pod2Wiki=head_cross_database_support -->		<!-- Pod2Wiki=head_cross_database_support -->
	===Cross-database Support===		===Cross-database Support===
	Any use of SQL statements must use [[API:EPrints/Database#quote_identifier\|quote_identifier]] to quote database tables and columns and quote_value to quote values. The only exception to this are the EPrints::Database::* modules which provide database-driver specific extensions.		Any use of SQL statements must use [[API:EPrints/Database#quote_identifier\|quote_identifier]] to quote database tables and columns and quote_value to quote values. The only exception to this are the EPrints::Database::* modules which provide database-driver specific extensions.

	<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>		<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 43:		Line 43:
	<!-- Pod2Wiki=head_quoting_sql_values -->		<!-- Pod2Wiki=head_quoting_sql_values -->
	===Quoting SQL Values===		===Quoting SQL Values===
	By convention variables that contain already quoted values are prefixed with 'Q_' so they can be easily recognised when used in string interpolation:		By convention variables that contain already quoted values are prefixed with <tt>Q_</tt> so they can be easily recognised when used in string interpolation:

	my $Q_value = $db->quote_value( "Hello, World!" );		my $Q_value = $db->quote_value( "Hello, World!" );
	$db->do("SELECT $Q_value");		$db->do("SELECT $Q_value");

	Where possible you should avoid quoting values yourself, instead use a method that accepts unquoted values which will (safely) do the work for you.		Where possible you should avoid quoting values yourself, instead use a method that accepts unquoted values which will (safely) do the work for you.

	<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>		<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 59:		Line 59:
	<!-- Pod2Wiki=head_constants -->		<!-- Pod2Wiki=head_constants -->
	==CONSTANTS==		==CONSTANTS==
	~~Also, all~~ the <tt>SQL_</tt> column types defined by Perl module {{API:PodLink\|file=DBI\|package_name=DBI\|section=\|text=DBI}}.		All the <tt>SQL_</tt> column types defined by Perl module {{API:PodLink\|file=DBI\|package_name=DBI\|section=\|text=DBI}} and the following:

	<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>		<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 84:		Line 84:

	A column value is defined.		A column value is defined.

			<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
			<span style='display:none'>User Comments</span>
			<!-- Edit below this comment -->


			<!-- Pod2Wiki= -->
			</div>
			<!-- Pod2Wiki=head_instance_variables -->
			==INSTANCE VARIABLES==
			<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
			<span style='display:none'>User Comments</span>
			<!-- Edit below this comment -->


			<!-- Pod2Wiki= -->
			</div>
			<!-- Pod2Wiki=item_$self->{session} -->
			===$self->{session}===

			The [[API:EPrints/Session\|EPrints::Session]] which is associated with this database connection.

			<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
			<span style='display:none'>User Comments</span>
			<!-- Edit below this comment -->


			<!-- Pod2Wiki= -->
			</div>
			<!-- Pod2Wiki=item_$self->{debug} -->
			===$self->{debug}===

			If <tt>true</tt> then SQL is logged.

			<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
			<span style='display:none'>User Comments</span>
			<!-- Edit below this comment -->


			<!-- Pod2Wiki= -->
			</div>
			<!-- Pod2Wiki=item_$self->{dbh} -->
			===$self->{dbh}===

			The handle on the actual database connection.

	<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>		<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>

Pod2wiki at 21:59, 26 December 2021

2021-12-26T21:59:38Z

← Older revision		Revision as of 21:59, 26 December 2021
Line 1:		Line 1:
	<!-- Pod2Wiki=_preamble_		<!-- Pod2Wiki=_preamble_
	This page has been automatically generated from the EPrints 3.4 source. Any wiki changes made between the 'Pod2Wiki=*' and 'Edit below this comment' comments will be lost.		This page has been automatically generated from the EPrints 3.4 source. Any wiki changes made between the 'Pod2Wiki=*' and 'Edit below this comment' comments will be lost.
	-->{{API}}{{Pod2Wiki}}{{API:Source\|file=EPrints/Database.pm\|package_name=EPrints::Database}}[[Category:API\|DATABASE]]<div><!-- Edit below this comment -->		-->{{API}}{{Pod2Wiki}}{{API:Source\|file=EPrints/Database.pm\|package_name=EPrints::Database}}[[Category:API\|DATABASE]][[Category:API:EPrints/Database\|DATABASE]]<div><!-- Edit below this comment -->

Pod2wiki at 20:08, 25 December 2021

2021-12-25T20:08:40Z

← Older revision		Revision as of 20:08, 25 December 2021
Line 130:		Line 130:

	$db = $db->create( $username, $password )		$db = $db->create( $username, $password )
	Create and connect to a new database using ~~super~~ user account <tt>$username</tt> and <tt>$password</tt>.		Create and connect to a new database using user account <tt>$username</tt> and <tt>$password</tt>.

	<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>		<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 1,739:		Line 1,739:
	<!-- Pod2Wiki=head_see_also -->		<!-- Pod2Wiki=head_see_also -->
	==SEE ALSO==		==SEE ALSO==
	To access database-stored objects use the methods provided by the following modules: [[API:EPrints/Repository\|EPrints::Repository]], [[API:EPrints/DataSet\|EPrints::DataSet]].		To access database-stored objects use the methods provided by the following modules: [[API:EPrints/Repository\|EPrints::Repository]], [[API:EPrints/DataSet\|EPrints::DataSet]].

	<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>		<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>

Pod2wiki at 16:29, 25 December 2021

2021-12-25T16:29:19Z

← Older revision		Revision as of 16:29, 25 December 2021
Line 392:		Line 392:
	$name $type($length,$scale) [ NOT NULL ]		$name $type($length,$scale) [ NOT NULL ]

	If <tt>$not_null</tt> is <tt>true</tt> column will be set to <em>NOT NULL</em>.		If <tt>$not_null</tt> is <tt>true</tt> column will be set to <tt>NOT NULL</tt>.

	<tt>$length</tt> and <tt>$scale</tt> control the maximum lengths of character or decimal types (see below).		<tt>$length</tt> and <tt>$scale</tt> control the maximum lengths of character or decimal types (see below).
Line 476:		Line 476:
	Returns a {{API:PodLink\|file=DBI\|package_name=DBI\|section=\|text=DBI}} statement handle.		Returns a {{API:PodLink\|file=DBI\|package_name=DBI\|section=\|text=DBI}} statement handle.

	The <tt>LIMIT</tt> SQL keyword is not universally supported, to specify this use the <tt>limit</tt> option.		The <tt>LIMIT</tt> SQL keyword is not universally supported, to specify this use the <tt>limit</tt> option.

	Options:		Options:
Line 697:		Line 697:

	$str = $db->quote_ordervalue( $field, $value )		$str = $db->quote_ordervalue( $field, $value )
	Some databases (Oracle) can't order by ~~CLOBS~~ so need special treatment when creating the ordervalues tables. This method allows any fixing-up required for string data <tt>$value</tt> for <tt>$field</tt> before it's inserted.		Some databases (Oracle) can't order by <tt>CLOB</tt>s so need special treatment when creating the ordervalues tables. This method allows any fixing-up required for string data <tt>$value</tt> for <tt>$field</tt> before it's inserted.

	<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>		<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 722:		Line 722:
	====prepare_regexp====		====prepare_regexp====

	$sql = $db->prepare_regexp( $~~quoted_column~~, $~~quoted_value~~ )		$sql = $db->prepare_regexp( $col, $value )
	The syntax used for regular expressions varies across databases. This method takes two quoted string and returns a SQL expression that will apply the regexp <tt>$~~quoted_value~~</tt> to the column <tt>$~~quoted_column~~</tt>.		The syntax used for regular expressions varies across databases. This method takes two quoted string and returns a SQL expression that will apply the quoted regexp <tt>$value</tt> to the quoted column <tt>$col</tt>.

	<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>		<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 736:		Line 736:

	$sql = $db->sql_AS()		$sql = $db->sql_AS()
	Returns the syntactic glue to use when aliasing. SQL 92 databases will happily use <tt>AS</tt> but some databases (Oracle) will nt accept it.		Returns the syntactic glue to use when aliasing. SQL 92 databases will happily use <tt>AS</tt> but some databases (Oracle) will not accept it.

	<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>		<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>

Pod2wiki at 11:41, 25 December 2021

2021-12-25T11:41:30Z

Show changes

Tdb01r at 09:56, 22 January 2013

2013-01-22T09:56:11Z

Show changes

Tdb01r at 11:10, 2 March 2012

2012-03-02T11:10:31Z

← Older revision		Revision as of 11:10, 2 March 2012
Line 369:		Line 369:
	Prepare the SQL statement $sql for execution.		Prepare the SQL statement $sql for execution.

	Use the <tt>execute</tt> method on the returned {{API:PodLink\|file=DBI\|package_name=DBI\|section=\|text=DBI}} handle to execute the SQL:		Use the <code>execute</code> method on the returned {{API:PodLink\|file=DBI\|package_name=DBI\|section=\|text=DBI}} handle to execute the SQL:

	<pre> my $sth = $db->prepare_select( "SELECT 'Hello, World'" );		<pre> my $sth = $db->prepare_select( "SELECT 'Hello, World'" );

Tdb01r at 10:55, 2 March 2012

2012-03-02T10:55:51Z

← Older revision		Revision as of 10:55, 2 March 2012
Line 17:		Line 17:
	$sth = $db->prepare("SELECT 'hello, world!'");		$sth = $db->prepare("SELECT 'hello, world!'");
	$sth->execute;		$sth->execute;
	print $sth->fetchrow_arrayref->[0], "\n";		print $sth->fetchrow_arrayref->[0], "\n";</pre>
	</pre><!-- Edit below this comment -->
			<!-- Edit below this comment -->


Line 47:		Line 48:

	<pre> my $Q_value = $db->quote_value( "Hello, World!" );		<pre> my $Q_value = $db->quote_value( "Hello, World!" );
	$db->do("SELECT $Q_value");		$db->do("SELECT $Q_value");</pre>
	</pre>Where possible you should avoid quoting values yourself, instead use a method that accepts unquoted values which will (safely) do the work for you.
			Where possible you should avoid quoting values yourself, instead use a method that accepts unquoted values which will (safely) do the work for you.

	<!-- Edit below this comment -->		<!-- Edit below this comment -->
Line 306:		Line 308:
	Returns a SQL column definition for NAME of type TYPE, usually something like:		Returns a SQL column definition for NAME of type TYPE, usually something like:

	<pre> $name $type($length,$scale) [ NOT NULL ]		<pre> $name $type($length,$scale) [ NOT NULL ]</pre>
	</pre>If NOT_NULL is true column will be set "not null".
			If NOT_NULL is true column will be set "not null".

	LENGTH/PRECISION and SCALE control the maximum lengths of character or decimal types (see below).		LENGTH/PRECISION and SCALE control the maximum lengths of character or decimal types (see below).
Line 314:		Line 317:

	<pre> langid - character set/collation to use		<pre> langid - character set/collation to use
	sorted - whether this column will be used to order by		sorted - whether this column will be used to order by</pre>
	</pre>'''langid''' is mapped to real database values by the "dblanguages" configuration option. The database may not be able to order the request column type in which case, if '''sorted''' is true, the database may use a substitute column type.
			'''langid''' is mapped to real database values by the "dblanguages" configuration option. The database may not be able to order the request column type in which case, if '''sorted''' is true, the database may use a substitute column type.

	TYPE is the SQL type. The types are constants defined by this module, to import them use:		TYPE is the SQL type. The types are constants defined by this module, to import them use:

	<pre> use EPrints::Database qw( :sql_types );		<pre> use EPrints::Database qw( :sql_types );</pre>
	</pre>Supported types (n = requires LENGTH argument):
			Supported types (n = requires LENGTH argument):

	Character data: SQL_VARCHAR(n), SQL_LONGVARCHAR, SQL_CLOB.		Character data: SQL_VARCHAR(n), SQL_LONGVARCHAR, SQL_CLOB.
Line 367:		Line 372:

	<pre> my $sth = $db->prepare_select( "SELECT 'Hello, World'" );		<pre> my $sth = $db->prepare_select( "SELECT 'Hello, World'" );
	$sth->execute;		$sth->execute;</pre>
	</pre><!-- Edit below this comment -->
			<!-- Edit below this comment -->


Line 385:		Line 391:

	<pre> limit - limit the number of rows returned		<pre> limit - limit the number of rows returned
	offset - return limit number of rows after offset		offset - return limit number of rows after offset</pre>
	</pre><!-- Edit below this comment -->
			<!-- Edit below this comment -->


Line 406:		Line 413:
	['title','description'],		['title','description'],
	['Hello, World!', undef]		['Hello, World!', undef]
	);		);</pre>
	</pre><!-- Edit below this comment -->
			<!-- Edit below this comment -->


Line 541:		Line 549:
	Return a quoted value. To quote a 'like' value you should do:		Return a quoted value. To quote a 'like' value you should do:

	<pre> my $str = $database->quote_value( EPrints::Database::prep_like_value( $foo ) . '%' );		<pre> my $str = $database->quote_value( EPrints::Database::prep_like_value( $foo ) . '%' );</pre>
	</pre><!-- Edit below this comment -->
			<!-- Edit below this comment -->