Difference between revisions of "API:EPrints/Database"

From EPrints Documentation
Jump to: navigation, search
 
(27 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
<!-- Pod2Wiki=_preamble_  
 
<!-- Pod2Wiki=_preamble_  
 
This page has been automatically generated from the EPrints 3.2 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.2 source. Any wiki changes made between the 'Pod2Wiki=*' and 'Edit below this comment' comments will be lost.
  -->
+
  -->{{API}}{{Pod2Wiki}}{{API:Source|file=perl_lib/EPrints/Database.pm|package_name=EPrints::Database}}[[Category:API|DATABASE]][[Category:API:EPrints/Database|DATABASE]]<div><!-- Edit below this comment -->
__NOTOC__
+
{{API}}{{Pod2Wiki}}{{API:Source|file=EPrints/Database.pm|package_name=EPrints::Database}}[[Category:API|Database]]<div><!-- Edit below this comment -->
+
  
  
<!-- Pod2Wiki=head_name --></div>
+
<!-- Pod2Wiki=_private_ --><!-- Pod2Wiki=head_name -->
 
==NAME==
 
==NAME==
'''EPrints::Database''' - a connection to the SQL database for an eprints session.
+
'''EPrints::Database''' - a connection to the SQL database for an eprints session
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
 
<!-- Edit below this comment -->
 
<!-- Edit below this comment -->
  
  
<!-- Pod2Wiki=head_description --></div>
+
<!-- Pod2Wiki= -->
 +
<!-- Pod2Wiki=head_synopsis -->
 +
==SYNOPSIS==
 +
<source lang="perl">$db = $repo->database
 +
$sth = $db->prepare("SELECT 'hello, world!'");
 +
$sth->execute;
 +
print $sth->fetchrow_arrayref->[0], "\n";</source>
 +
 
 +
<!-- Edit below this comment -->
 +
 
 +
 
 +
<!-- Pod2Wiki= -->
 +
<!-- Pod2Wiki=head_description -->
 
==DESCRIPTION==
 
==DESCRIPTION==
 
EPrints Database Access Module
 
EPrints Database Access Module
  
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 is performed via this module to 1) provide cross-database support and 2) improve security through enforcing proper quoting of SQL values.
  
The database object is created automatically when you start a new eprints session. To get a handle on it use:
+
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.
  
$db = $session-&gt;get_database
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
 
<!-- Edit below this comment -->
 
<!-- Edit below this comment -->
  
  
<!-- Pod2Wiki=head_cross_database_support --></div>
+
<!-- Pod2Wiki= -->
 +
<!-- Pod2Wiki=head_cross_database_support -->
 
===Cross-database Support===
 
===Cross-database Support===
Any use of SQL must use quote_identifier to quote database tables and columns. The only exception to this are the 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 [[API:EPrints/Database#quote_value|quote_value]] to quote values. The only exception to this are the EPrints::Database::* modules which provide database-driver specific extensions.
  
Variables that are database quoted are prefixed with 'Q_'.
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
 
<!-- Edit below this comment -->
 
<!-- Edit below this comment -->
  
  
<!-- Pod2Wiki=item_build_connection_string --></div>
+
<!-- Pod2Wiki= -->
====$dbstr = EPrints::Database::build_connection_string( %params )====
+
<!-- Pod2Wiki=head_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:
  
Build the string to use to connect to the database via DBI. %params must contain dbname, and may also contain dbport, dbhost and dbsock.
+
<pre> my $Q_value = $db-&gt;quote_value( "Hello, World!" );
 +
  $db-&gt;do("SELECT $Q_value");</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.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
 
<!-- Edit below this comment -->
 
<!-- Edit below this comment -->
  
  
<!-- Pod2Wiki=item_create --></div>
+
<!-- Pod2Wiki= -->
====$db = $db-&gt;create( $username, $password )====
+
<!-- Pod2Wiki=head_methods -->
 
+
==METHODS==
Create and connect to a new database using super user account $username and $password.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
 
<!-- Edit below this comment -->
 
<!-- Edit below this comment -->
  
  
<!-- Pod2Wiki=item_new --></div>
+
<!-- Pod2Wiki= -->
====$db = EPrints::Database-&gt;new( $session )====
+
<!-- Pod2Wiki=head_database -->
 
+
===Database===
 +
$db = EPrints::Database-&gt;new( $repo )
 
Create a connection to the database.
 
Create a connection to the database.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $db = $db-&gt;create( $username, $password )
<h4><span style='display:none'>User Comments</span></h4>
+
Create and connect to a new database using super user account $username and $password.
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_connect --></div>
+
====$foo = $db-&gt;connect====
+
  
 +
$ok = $db-&gt;connect
 
Connects to the database.  
 
Connects to the database.  
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $db-&gt;disconnect
<h4><span style='display:none'>User Comments</span></h4>
+
Disconnects from the EPrints database.
<!-- Edit below this comment -->
+
  
 +
$db-&gt;set_debug( $boolean )
 +
Set the SQL debug mode to true or false.
  
<!-- Pod2Wiki=item_disconnect --></div>
+
$db-&gt;set_version( $versionid );
====$foo = $db-&gt;disconnect====
+
Set the version id table in the SQL database to the given value (used by the upgrade script).
  
Disconnects from the EPrints database. Should always be done before any script exits.
+
$version = $db-&gt;get_version
 +
Returns the current database schema version.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
$boolean = $db-&gt;is_latest_version
<h4><span style='display:none'>User Comments</span></h4>
+
Return true if the database schema is in the correct configuration for this version of eprints. Otherwise false.
<!-- Edit below this comment -->
+
  
 +
$version = $db-&gt;get_server_version
 +
Return the database server version.
  
<!-- Pod2Wiki=item_error --></div>
+
$charset = $db-&gt;get_default_charset( LANGUAGE )
====$errstr = $db-&gt;error====
+
Return the character set to use for LANGUAGE.
  
Return a string describing the last SQL error.
+
Returns undef if character sets are unsupported.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
$collation = $db-&gt;get_default_collation( LANGUAGE )
<h4><span style='display:none'>User Comments</span></h4>
+
Return the collation to use for LANGUAGE.
<!-- Edit below this comment -->
+
  
 +
Returns undef if collation is unsupported.
  
<!-- Pod2Wiki=item_begin --></div>
+
$driver = $db-&gt;get_driver_name
====$db-&gt;begin====
+
Return the database driver name.
  
Begin a transaction.
+
$errstr = $db-&gt;error
 +
Return a string describing the last SQL error.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
$bool = $db-&gt;retry_error
<h4><span style='display:none'>User Comments</span></h4>
+
Returns true if the current error is a retry error.
<!-- Edit below this comment -->
+
  
 +
$bool = $db-&gt;duplicate_error
 +
Returns true if the current error is a PRIMARY KEY or UNIQUE error.
  
<!-- Pod2Wiki=item_commit --></div>
+
$db-&gt;begin
====$db-&gt;commit====
+
Begin a transaction.
  
Commit the previous begun transaction.
+
$db-&gt;commit
 +
Commit the previously begun transaction.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $db-&gt;rollback
<h4><span style='display:none'>User Comments</span></h4>
+
Roll-back the current transaction.
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_rollback --></div>
+
====$db-&gt;rollback====
+
 
+
Rollback the partially completed transaction.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_create_archive_tables --></div>
+
====$success = $db-&gt;create_archive_tables====
+
 
+
Create all the SQL tables for each dataset.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_drop_archive_tables --></div>
+
====$db-&gt;drop_archive_tables()====
+
 
+
Destroy all tables used by eprints in the database.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_create_dataset_tables --></div>
+
====$success = $db-&gt;create_dataset_tables( $dataset )====
+
 
+
Create all the SQL tables for a single dataset.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_drop_dataset_tables --></div>
+
====$db-&gt;drop_dataset_tables( $dataset )====
+
 
+
Drop all the SQL tables for a single dataset.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_create_dataset_index_tables --></div>
+
====$success = $db-&gt;create_dataset_index_tables( $dataset )====
+
 
+
Create all the index tables for a single dataset.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_create_dataset_ordervalues_tables --></div>
+
====$success = $db-&gt;create_dataset_ordervalues_tables( $dataset )====
+
 
+
Create all the ordervalues tables for a single dataset.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_type_info --></div>
+
====$type_info = $db-&gt;type_info( DATA_TYPE )====
+
  
 +
$type_info = $db-&gt;type_info( DATA_TYPE )
 
See {{API:PodLink|file=DBI|package_name=DBI|section=type_info|text=DBI/type_info}}.
 
See {{API:PodLink|file=DBI|package_name=DBI|section=type_info|text=DBI/type_info}}.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
$real_type = $db-&gt;get_column_type( NAME, TYPE, NOT_NULL, [ LENGTH/PRECISION ], [ SCALE ], %opts )
<h4><span style='display:none'>User Comments</span></h4>
+
Returns a SQL column definition for NAME of type TYPE, usually something like:
<!-- Edit below this comment -->
+
  
 +
<pre>  $name $type($length,$scale) [ NOT NULL ]</pre>
  
<!-- Pod2Wiki=item_get_column_type --></div>
 
====$real_type = $db-&gt;get_column_type( NAME, TYPE, NOT_NULL, [ LENGTH/PRECISION ], [ SCALE ], %opts )====
 
 
Returns a SQL column definition for NAME of type TYPE, usually something like:
 
 
  $name $type($length,$scale) [ NOT NULL ]
 
 
 
 
If NOT_NULL is true column will be set "not null".
 
If NOT_NULL is true column will be set "not null".
  
Line 214: Line 136:
 
Other options available to refine the column definition:
 
Other options available to refine the column definition:
  
  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>
 
+
 
 
'''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:
  
  use EPrints::Database qw( :sql_types );
+
<pre>  use EPrints::Database qw( :sql_types );</pre>
 
+
 
 
Supported types (n = requires LENGTH argument):
 
Supported types (n = requires LENGTH argument):
  
Character data: SQL_VARCHAR(n), SQL_LONGVARCHAR.
+
Character data: SQL_VARCHAR(n), SQL_LONGVARCHAR, SQL_CLOB.
  
 
Binary data: SQL_VARBINARY(n), SQL_LONGVARBINARY.
 
Binary data: SQL_VARBINARY(n), SQL_LONGVARBINARY.
Line 237: Line 159:
 
The actual column types used will be database-specific.
 
The actual column types used will be database-specific.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
 
<!-- Edit below this comment -->
 
<!-- Edit below this comment -->
  
  
<!-- Pod2Wiki=item_create_table --></div>
+
<!-- Pod2Wiki= -->
====$success = $db-&gt;create_table( $tablename, $dataset, $setkey, @fields );====
+
<!-- Pod2Wiki=head_basic_sql_operations -->
 +
===Basic SQL operations===
 +
$success = $db-&gt;do( $sql )
 +
Execute the given SQL.
  
Create the tables used to store metadata for this dataset: the main table and any required for multiple or mulitlang fields.
+
$sth = $db-&gt;prepare( $sql )
 +
Prepare the SQL statement $sql for execution.
  
The first $setkey number of fields are used for a primary key.
+
Use the <code>execute</code> method on the returned {{API:PodLink|file=DBI|package_name=DBI|section=|text=DBI}} handle to execute the SQL:
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<pre>  my $sth = $db-&gt;prepare_select( "SELECT 'Hello, World'" );
<h4><span style='display:none'>User Comments</span></h4>
+
  $sth-&gt;execute;</pre>
<!-- Edit below this comment -->
+
  
 +
$sth = $db-&gt;prepare_select( $sql [, %options ] )
 +
Prepare a SELECT statement $sql for execution. This method provides cross-database support for getting a part of the result list based on an offset/limit.
  
<!-- Pod2Wiki=item_has_sequence --></div>
+
Returns a {{API:PodLink|file=DBI|package_name=DBI|section=|text=DBI}} statement handle.
====$boolean = $db-&gt;has_sequence( $name )====
+
  
Return true if a sequence of the given name exists in the database.
+
Options:
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<pre> limit - limit the number of rows returned
<h4><span style='display:none'>User Comments</span></h4>
+
  offset - return limit number of rows after offset</pre>
<!-- Edit below this comment -->
+
  
 +
$rows = $db-&gt;update( $tablename, $keycols, $keyvals, $columns, $values )
 +
UPDATEs $columns with the equivalent slot from $values where the rows in $tablename match the $keycols/$keyvals.
  
<!-- Pod2Wiki=item_create_sequence --></div>
+
Returns the number of rows affected or false on error, see {{API:PodLink|file=DBI|package_name=DBI|section=|text=DBI}}'s execute() method.
====$success = $db-&gt;create_sequence( $seq_name )====
+
  
Creates a new sequence object initialised to zero.
+
<pre>  $db-&gt;update(
 +
    'eprint',
 +
    ['eprintid'],
 +
    [23],
 +
    ['title','description'],
 +
    ['Hello, World!', undef]
 +
  );</pre>
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
$success = $db-&gt;update_quoted( $tablename, $keycols, $keyvals, $columns, $Q_values )
<h4><span style='display:none'>User Comments</span></h4>
+
UPDATEs $tablename where $keycols equals $keyvals.
<!-- Edit below this comment -->
+
  
 +
Returns the number of rows affected or false on error, see {{API:PodLink|file=DBI|package_name=DBI|section=|text=DBI}}'s execute() method.
  
<!-- Pod2Wiki=item_drop_sequence --></div>
+
  $success = $db-&gt;insert( $table, $columns, @values )
====$success = $db-&gt;drop_sequence( $seq_name )====
+
 
+
Deletes a sequence object.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_drop_column --></div>
+
====$success = $db-&gt;drop_column( $table, $column )====
+
 
+
Drops a column from a table.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_get_primary_key --></div>
+
====@columns = $db-&gt;get_primary_key( $tablename )====
+
 
+
Returns the list of column names that comprise the primary key for $tablename.
+
 
+
Returns empty list if no primary key exists.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_create_index --></div>
+
====$success = $db-&gt;create_index( $tablename, @columns )====
+
 
+
Creates an index over @columns for $tablename. Returns true on success.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_create_unique_index --></div>
+
====$success = $db-&gt;create_unique_index( $tablename, @columns )====
+
 
+
Creates a unique index over @columns for $tablename. Returns true on success.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item__update --></div>
+
====$success = $db-&gt;_update( $tablename, $keycols, $keyvals, $columns, @values )====
+
 
+
UPDATES $tablename where $keycols equals $keyvals.
+
 
+
This method is internal.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item__update_quoted --></div>
+
====$success = $db-&gt;_update_quoted( $tablename, $keycols, $keyvals, $columns, @qvalues )====
+
 
+
UPDATES $tablename where $keycols equals $keyvals. Won't quote $keyvals or @qvalues before use - use this method with care!
+
 
+
This method is internal.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_insert --></div>
+
====$success = $db-&gt;insert( $table, $columns, @values )====
+
 
+
 
Inserts values into the table $table. If $columns is defined it will be used as a list of columns to insert into. @values is a list of arrays containing values to insert.
 
Inserts values into the table $table. If $columns is defined it will be used as a list of columns to insert into. @values is a list of arrays containing values to insert.
  
 
Values will be quoted before insertion.
 
Values will be quoted before insertion.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $success = $db-&gt;insert_quoted( $table, $columns, @Q_values )
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_insert_quoted --></div>
+
====$success = $db-&gt;insert_quoted( $table, $columns, @qvalues )====
+
 
+
 
Inserts values into the table $table. If $columns is defined it will be used as a list of columns to insert into. @qvalues is a list of arrays containing values to insert.
 
Inserts values into the table $table. If $columns is defined it will be used as a list of columns to insert into. @qvalues is a list of arrays containing values to insert.
  
 
Values will NOT be quoted before insertion - care must be exercised!
 
Values will NOT be quoted before insertion - care must be exercised!
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $success = $db-&gt;delete_from( $table, $columns, @values )
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_delete_from --></div>
+
====$success = $db-&gt;delete_from( $table, $columns, @values )====
+
 
+
 
Perform a SQL DELETE FROM $table using $columns to build a where clause. @values is a list of array references of values in the same order as $columns.
 
Perform a SQL DELETE FROM $table using $columns to build a where clause. @values is a list of array references of values in the same order as $columns.
  
 
If you want to clear a table completely use clear_table().
 
If you want to clear a table completely use clear_table().
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
$n = $db-&gt;count_table( $tablename )
<h4><span style='display:none'>User Comments</span></h4>
+
Return the number of rows in the specified SQL table.
<!-- Edit below this comment -->
+
  
 +
$db-&gt;clear_table( $tablename )
 +
Clears all records from the given table, use with caution!
  
<!-- Pod2Wiki=item_add_record --></div>
 
====$success = $db-&gt;add_record( $dataset, $data )====
 
 
Add the given data as a new record in the given dataset. $data is a reference to a hash containing values structured for a record in the that dataset.
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
 
<!-- Edit below this comment -->
 
<!-- Edit below this comment -->
  
  
<!-- Pod2Wiki=item_prep_int --></div>
+
<!-- Pod2Wiki= -->
====$mungedvalue = EPrints::Database::prep_int( $value )====
+
<!-- Pod2Wiki=head_quoting -->
 
+
===Quoting===
 +
$mungedvalue = EPrints::Database::prep_int( $value )
 
Escape a numerical value for SQL. undef becomes NULL. Anything else becomes a number (zero if needed).
 
Escape a numerical value for SQL. undef becomes NULL. Anything else becomes a number (zero if needed).
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $mungedvalue = EPrints::Database::prep_value( $value )
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_prep_value --></div>
+
====$mungedvalue = EPrints::Database::prep_value( $value )====
+
 
+
 
Escape a value for SQL. Modify value such that " becomes \" and \  becomes \\ and ' becomes \'
 
Escape a value for SQL. Modify value such that " becomes \" and \  becomes \\ and ' becomes \'
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $mungedvalue = EPrints::Database::prep_like_value( $value )
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_prep_like_value --></div>
+
====$mungedvalue = EPrints::Database::prep_like_value( $value )====
+
 
+
 
Escape an value for an SQL like field. In addition to ' " and \ also  escapes % and _
 
Escape an value for an SQL like field. In addition to ' " and \ also  escapes % and _
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $str = $db-&gt;quote_value( $value )
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_quote_value --></div>
+
====$str = $db-&gt;quote_value( $value )====
+
 
+
 
Return a quoted value. To quote a 'like' value you should do:
 
Return a quoted value. To quote a 'like' value you should do:
  
my $str = $database-&gt;quote_value( EPrints::Database::prep_like_value( $foo ) . '%' );
+
<pre> my $str = $database-&gt;quote_value( EPrints::Database::prep_like_value( $foo ) . '%' );</pre>
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_quote_int --></div>
+
====$str = $db-&gt;quote_int( $value )====
+
  
 +
$str = $db-&gt;quote_int( $value )
 
Return a quoted integer value
 
Return a quoted integer value
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
$str = $db-&gt;quote_binary( $bytes )
<h4><span style='display:none'>User Comments</span></h4>
+
Some databases (Oracle/PostgreSQL) require transforms of binary data to work correctly.
<!-- Edit below this comment -->
+
  
 +
This method should be called on data containing nul bytes or back-slashes before being passed on [[API:EPrints/Database#quote_value|quote_value]].
  
<!-- Pod2Wiki=item_quote_binary --></div>
+
$str = $db-&gt;quote_ordervalue( $field, $value )
====$str = $db-&gt;quote_binary( $bytes )====
+
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 before it's inserted.
 
+
Some databases (PostgreSQL) require weird transforms of binary data to work correctly.
+
 
+
This method should be called on data containing nul bytes or back-slashes before being passed on {{API:PodLink|file=|package_name=|section=quote_value|text=/quote_value}}.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_quote_identifier --></div>
+
====$str = $db-&gt;quote_identifier( @parts )====
+
  
 +
$str = $db-&gt;quote_identifier( @parts )
 
Quote a database identifier (e.g. table names). Multiple @parts will be joined by dot.
 
Quote a database identifier (e.g. table names). Multiple @parts will be joined by dot.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
$sql = $db-&gt;prepare_regexp( $quoted_column, $quoted_value )
<h4><span style='display:none'>User Comments</span></h4>
+
The syntax used for regular expressions varies across databases. This method takes two '''quoted''' values and returns a SQL expression that will apply the regexp ($quoted_value) to the column ($quoted_column).
<!-- Edit below this comment -->
+
  
 +
$sql = $db-&gt;sql_AS()
 +
Returns the syntactic glue to use when aliasing. SQL 92 DBs will happilly use " AS " but some DBs (Oracle!) won't accept it.
  
<!-- Pod2Wiki=item_update --></div>
+
$sql = $db-&gt;sql_LIKE()
====$success = $db-&gt;update( $dataset, $data, $changed, $insert )====
+
Returns the syntactic glue to use when making a case-insensitive LIKE. PostgreSQL requires "ILIKE" while everything else uses "LIKE" and the column collation.
  
Updates a record in the database with the given $data. Obviously the value of the primary key must be set.
+
$sql = $db-&gt;sql_IS_SET( $qname )
 +
Return the SQL to test whether $qname is set. For most databases this will be:
  
This also updates the text indexes and the ordering keys.
+
<pre>  $qname is not null AND $qname != ''</pre>
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
$qname is a quoted identifier or value.
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_remove --></div>
+
====$success = $db-&gt;remove( $dataset, $id )====
+
 
+
Attempts to remove the record with the primary key $id from the  specified dataset.
+
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
 
<!-- Edit below this comment -->
 
<!-- Edit below this comment -->
  
  
<!-- Pod2Wiki=item_create_counters --></div>
+
<!-- Pod2Wiki= -->
====$success = $db-&gt;create_counters====
+
<!-- Pod2Wiki=head_counters -->
 +
===Counters===
 +
$n = $db-&gt;counter_current( $counter )
 +
Return the value of the previous counter_next on $counter.
  
Create the counters used to store the highest current id of eprints, users etc.
+
$n = $db-&gt;counter_next( $counter )
 +
Return the next unused value for the named counter. Returns undef if  the counter doesn't exist.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $db-&gt;counter_minimum( $counter, $value )
<h4><span style='display:none'>User Comments</span></h4>
+
Ensure that the counter is set no lower than $value. This is used when importing eprints which may not be in scrict sequence.
<!-- Edit below this comment -->
+
  
 +
$db-&gt;counter_reset( $counter )
 +
Reset the counter. Use with caution.
  
<!-- Pod2Wiki=item_has_counter --></div>
+
  $n = $db-&gt;next_doc_pos( $eprintid )
====$success = $db-&gt;has_counter( $counter )====
+
 
+
Returns true if $counter exists.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_create_counter --></div>
+
====$success = $db-&gt;create_counter( $name )====
+
 
+
Create and initialise to zero a new counter called $name.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_remove_counters --></div>
+
====$success = $db-&gt;remove_counters====
+
 
+
Destroy all counters.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_drop_counter --></div>
+
====$success = $db-&gt;drop_counter( $name )====
+
 
+
Destroy the counter named $name.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_next_doc_pos --></div>
+
====$n = $db-&gt;next_doc_pos( $eprintid )====
+
 
+
 
Return the next unused document pos for the given eprintid.
 
Return the next unused document pos for the given eprintid.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
 
<!-- Edit below this comment -->
 
<!-- Edit below this comment -->
  
  
<!-- Pod2Wiki=item_counter_current --></div>
+
<!-- Pod2Wiki= -->
====$n = $db-&gt;counter_current( $counter )====
+
<!-- Pod2Wiki=head_dataset_data -->
 +
===Dataset data===
 +
$boolean = $db-&gt;exists( $dataset, $id )
 +
Return true if a record with the given primary key exists in the dataset, otherwise false.
  
Return the value of the previous counter_next on $counter.
+
$success = $db-&gt;insert_data( $dataset, $data )
 +
Add the given data as a new record in the given dataset. $data is a reference to a hash containing values structured for a record in the that dataset.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
Returns undef if a record already exists with the key field value in $data.
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
  
 +
Inserts ordervalues if the dataset is [[API:EPrints/DataSet#ordered|ordered]].
  
<!-- Pod2Wiki=item_counter_next --></div>
+
$success = $db-&gt;update_data( $dataset, $data, $changed )
====$n = $db-&gt;counter_next( $counter )====
+
Updates a record in the given [[API:EPrints/DataSet|$dataset]]. $data is a copy of the entire record's data and $changed the values that have changed.
  
Return the next unused value for the named counter. Returns undef if  the counter doesn't exist.
+
If the record does not already exist or the key field is unset in $data no changes will be written.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
Updates ordervalues if the dataset is [[API:EPrints/DataSet#ordered|ordered]].
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
  
 +
$success = $db-&gt;remove( $dataset, $id )
 +
Remove the record, index terms and order value with the key field value $id from the specified dataset.
  
<!-- Pod2Wiki=item_counter_minimum --></div>
+
Returns true on success.
====$db-&gt;counter_minimum( $counter, $value )====
+
  
Ensure that the counter is set no lower than $value. This is used when importing eprints which may not be in scrict sequence.
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
 
<!-- Edit below this comment -->
 
<!-- Edit below this comment -->
  
  
<!-- Pod2Wiki=item_counter_reset --></div>
+
<!-- Pod2Wiki= -->
====$db-&gt;counter_reset( $counter )====
+
<!-- Pod2Wiki=head_searching_caching_and_object_retrieval -->
 
+
===Searching, caching and object retrieval===
Reset the counter. Use with caution.
+
$searchexp = $db-&gt;cache_exp( $cacheid )
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_cache_exp --></div>
+
====$searchexp = $db-&gt;cache_exp( $cacheid )====
+
 
+
 
Return the serialised Search of a the cached search with id $cacheid. Return undef if the id is invalid or expired.
 
Return the serialised Search of a the cached search with id $cacheid. Return undef if the id is invalid or expired.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $cacheid = $db-&gt;cache( $searchexp, $dataset, $srctable, [$order], [$list] )
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_cache --></div>
+
====$cacheid = $db-&gt;cache( $searchexp, $dataset, $srctable, [$order], [$list] )====
+
 
+
 
Create a cache of the specified search expression from the SQL table $srctable.
 
Create a cache of the specified search expression from the SQL table $srctable.
  
Line 613: Line 342:
 
If $srctable is set to "ALL" every matching record from $dataset is added to the cache, optionally ordered by $order.
 
If $srctable is set to "ALL" every matching record from $dataset is added to the cache, optionally ordered by $order.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $tablename = $db-&gt;cache_table( $id )
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_cache_table --></div>
+
====$tablename = $db-&gt;cache_table( $id )====
+
 
+
 
Return the SQL table used to store the cache with id $id.
 
Return the SQL table used to store the cache with id $id.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $ids = $db-&gt;search( $keyfield, $tables, $conditions, [$main_table_alias] )
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_get_index_ids --></div>
+
====$ids = $db-&gt;get_index_ids( $table, $condition )====
+
 
+
Return a reference to an array of the distinct primary keys from the given SQL table which match the specified condition.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_search --></div>
+
====$ids = $db-&gt;search( $keyfield, $tables, $conditions, [$main_table_alias] )====
+
 
+
 
Return a reference to an array of ids - the results of the search specified by $conditions accross the tables specified in the $tables hash where keys are tables aliases and values are table names.  
 
Return a reference to an array of ids - the results of the search specified by $conditions accross the tables specified in the $tables hash where keys are tables aliases and values are table names.  
  
 
If no table alias is passed then M is assumed.  
 
If no table alias is passed then M is assumed.  
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $db-&gt;drop_cache( $id )
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_drop_cache --></div>
+
====$db-&gt;drop_cache( $id )====
+
 
+
 
Remove the cached search with the given id.
 
Remove the cached search with the given id.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $foo = $db-&gt;from_cache( $dataset, $cacheid, [$offset], [$count], [$justids] )
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_count_table --></div>
+
====$n = $db-&gt;count_table( $tablename )====
+
 
+
Return the number of rows in the specified SQL table.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_from_cache --></div>
+
====$foo = $db-&gt;from_cache( $dataset, $cacheid, [$offset], [$count], [$justids] )====
+
 
+
 
Return a reference to an array containing all the items from the given dataset that have id's in the specified cache. The cache may be  specified either by id or serialised search expression.  
 
Return a reference to an array containing all the items from the given dataset that have id's in the specified cache. The cache may be  specified either by id or serialised search expression.  
  
Line 679: Line 360:
 
If $justids is true then it returns just an ref to an array of the record ids, not the objects.
 
If $justids is true then it returns just an ref to an array of the record ids, not the objects.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $c = $db-&gt;drop_orphan_cache_tables
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_drop_orphan_cache_tables --></div>
+
====$c = $db-&gt;drop_orphan_cache_tables====
+
 
+
 
Drop tables called "cacheXXX" where XXX is an integer. Returns the number of tables dropped.
 
Drop tables called "cacheXXX" where XXX is an integer. Returns the number of tables dropped.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $obj = $db-&gt;get_single( $dataset, $id )
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_get_single --></div>
+
====$obj = $db-&gt;get_single( $dataset, $id )====
+
 
+
 
Return a single item from the given dataset. The one with the specified id.
 
Return a single item from the given dataset. The one with the specified id.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $items = $db-&gt;get_all( $dataset )
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_get_all --></div>
+
====$items = $db-&gt;get_all( $dataset )====
+
 
+
 
Returns a reference to an array with all the items from the given dataset.
 
Returns a reference to an array with all the items from the given dataset.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  @ids = $db-&gt;get_cache_ids( $dataset, $cachemap, $offset, $count )
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_get_cache_ids --></div>
+
====@ids = $db-&gt;get_cache_ids( $dataset, $cachemap, $offset, $count )====
+
 
+
 
Returns a list of $count ids from $cache_id starting at $offset and in the order in the cachemap.
 
Returns a list of $count ids from $cache_id starting at $offset and in the order in the cachemap.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
@dataobjs = $db-&gt;get_dataobjs( $dataset [, $id [, $id ] ] )
<h4><span style='display:none'>User Comments</span></h4>
+
Retrieves the records in $dataset with the given $id(s). If an $id doesn't exist in the database it will be ignored.
<!-- Edit below this comment -->
+
  
 +
$foo = $db-&gt;get_values( $field, $dataset )
 +
Return a reference to an array of all the distinct values of the  [[API:EPrints/MetaField|EPrints::MetaField]] specified.
  
<!-- Pod2Wiki=item_get_dataobjs --></div>
+
$ids = $db-&gt;get_ids_by_field_values( $field, $dataset [ %opts ] )
====@dataobjs = $db-&gt;get_dataobjs( $dataset [, $id [, $id ] ] )====
+
Return a reference to a hash table where the keys are field value ids and the value is a reference to an array of ids.
  
Retrieves the records in $dataset with the given $id(s). If an $id doesn't exist in the database it will be ignored.
+
@events = $db-&gt;dequeue_events( $n )
 +
Attempt to dequeue upto $n events. May return between 0 and $n events depending on parallel processes and how many events are remaining on the queue.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
 
<!-- Edit below this comment -->
 
<!-- Edit below this comment -->
  
  
<!-- Pod2Wiki=item_get_values --></div>
+
<!-- Pod2Wiki= -->
====$foo = $db-&gt;get_values( $field, $dataset )====
+
<!-- Pod2Wiki=head_password_validation -->
 +
===Password Validation===
 +
$real_username = $db-&gt;valid_login( $username, $password )
 +
Tests whether the clear-text $password matches the stored encrypted password for $username.
  
Return a reference to an array of all the distinct values of the EPrints::MetaField specified.
+
Returns the user's real (case-sensitive) username or undef if the passwords don't match.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
 
<!-- Edit below this comment -->
 
<!-- Edit below this comment -->
  
  
<!-- Pod2Wiki=item_sort_values --></div>
+
<!-- Pod2Wiki= -->
====$values = $db-&gt;sort_values( $field, $values [, $langid ] )====
+
<!-- Pod2Wiki=head_database_schema_manipulation -->
 +
===Database schema manipulation===
 +
$boolean = $db-&gt;has_sequence( $name )
 +
Return true if a sequence of the given name exists in the database.
  
ALPHA!!! Liable to API change!!!
+
$success = $db-&gt;create_sequence( $seq_name )
 +
Creates a new sequence object initialised to zero.
  
Sorts and returns the list of $values using the database.
+
$success = $db-&gt;drop_sequence( $seq_name )
 +
Deletes a sequence object.
  
$field is used to get the order value for each value. $langid (or $session-&gt;get_langid if unset) is used to determine the database collation to use when sorting the resulting order values.
+
$boolean = $db-&gt;has_column( $tablename, $columnname )
 +
Return true if the a table of the given name has a column named $columnname in the database.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
$success = $db-&gt;drop_column( $table, $column )
<h4><span style='display:none'>User Comments</span></h4>
+
Drops a column from a table.
<!-- Edit below this comment -->
+
  
 +
@columns = $db-&gt;get_primary_key( $tablename )
 +
Returns the list of column names that comprise the primary key for $tablename.
  
<!-- Pod2Wiki=item_get_ids_by_field_values --></div>
+
Returns empty list if no primary key exists.
====$ids = $db-&gt;get_ids_by_field_values( $field, $dataset [ %opts ] )====
+
  
Return a reference to a hash table where the keys are field value ids and the value is a reference to an array of ids.
+
$db-&gt;create_primary_key( $tablename, @cols )
 +
Create a PRIMARY KEY on $tablename over @cols.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
$name = $db-&gt;index_name( $table, @columns )
<h4><span style='display:none'>User Comments</span></h4>
+
Returns the name of the first index that starts with @columns on the $table table.
<!-- Edit below this comment -->
+
  
 +
Returns undef if no index exists.
  
<!-- Pod2Wiki=item_do --></div>
+
$success = $db-&gt;create_index( $tablename, @columns )
====$success = $db-&gt;do( $sql )====
+
Creates an index over @columns for $tablename. Returns true on success.
  
Execute the given SQL.
+
$success = $db-&gt;create_unique_index( $tablename, @columns )
 +
Creates a unique index over @columns for $tablename. Returns true on success.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
$ok = $db-&gt;create_foreign_key( $main_table, $aux_table, $key_field )
<h4><span style='display:none'>User Comments</span></h4>
+
Create a foreign key relationship between $main_table and $aux_table using the $key_field.
<!-- Edit below this comment -->
+
  
 +
This will cause records in $aux_table to be deleted if the equivalent record is deleted from $main_table.
  
<!-- Pod2Wiki=item_prepare --></div>
+
@tables = $db-&gt;get_tables
====$sth = $db-&gt;prepare( $sql )====
+
Return a list of all the tables in the database.
  
Prepare the given $sql and return a handle on it.
+
$boolean = $db-&gt;has_table( $tablename )
 +
Return true if a table of the given name exists in the database.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
$success = $db-&gt;create_table( $tablename, $setkey, @fields );
<h4><span style='display:none'>User Comments</span></h4>
+
Creates a new table $tablename based on @fields.
<!-- Edit below this comment -->
+
  
 +
The first $setkey number of fields are used for a primary key.
  
<!-- Pod2Wiki=item_prepare_select --></div>
+
$db-&gt;drop_table( $tablename [, $tablename2 ] )
====$sth = $db-&gt;prepare_select( $sql [, %options ] )====
+
Delete the named table(s). Use with caution!
  
Prepare a SELECT statement $sql and return a handle to it. After preparing a statement use execute() to execute it.
+
$db-&gt;rename_table( $tablename, $newtablename )
 +
Renames the table from the old name to the new one.
  
The LIMIT SQL keyword is not universally supported, to specify a LIMIT you must use the '''limit''' option.
+
$db-&gt;swap_table( $table_a, $table_b )
 
+
Swap table a and table b.  
Options:
+
  
  limit - limit the number of rows returned
 
  offset - return '''limit''' number of rows after offset
 
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
 
<!-- Edit below this comment -->
 
<!-- Edit below this comment -->
  
  
<!-- Pod2Wiki=item_execute --></div>
+
<!-- Pod2Wiki= -->
====$success = $db-&gt;execute( $sth, $sql )====
+
<!-- Pod2Wiki=head_eprints_schema_manipulation -->
 +
===EPrints schema manipulation===
 +
$success = $db-&gt;create_archive_tables
 +
Create all the SQL tables for each dataset.
  
Execute the SQL prepared earlier. $sql is only passed in for debugging purposes.
+
$db-&gt;drop_archive_tables()
 +
Destroy all tables used by eprints in the database.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $db-&gt;create_version_table
<h4><span style='display:none'>User Comments</span></h4>
+
Make the version table (and set the only value to be the current version of eprints).
<!-- Edit below this comment -->
+
  
 +
$db-&gt;drop_version_table
 +
Drop the version table.
  
<!-- Pod2Wiki=item_has_dataset --></div>
+
$db-&gt;has_dataset( $dataset )
====$db-&gt;has_dataset( $dataset )====
+
 
+
 
Returns true if $dataset exists in the database or has no database tables.
 
Returns true if $dataset exists in the database or has no database tables.
  
 
This does not check that all fields are configured - see has_field().
 
This does not check that all fields are configured - see has_field().
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
$success = $db-&gt;create_dataset_tables( $dataset )
<h4><span style='display:none'>User Comments</span></h4>
+
Create all the SQL tables for a single dataset.
<!-- Edit below this comment -->
+
  
 +
Index tables are created in the dataset is indexable.
  
<!-- Pod2Wiki=item_has_field --></div>
+
Order values tables are created for each configured languaged if the dataset is ordered.
====$db-&gt;has_field( $dataset, $field )====
+
  
 +
$db-&gt;drop_dataset_tables( $dataset )
 +
Drop all the SQL tables for a single dataset.
 +
 +
$db-&gt;has_field( $dataset, $field )
 
Returns true if $field is in the database for $dataset.
 
Returns true if $field is in the database for $dataset.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $db-&gt;add_field( $dataset, $field [, $force ] )
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_add_field --></div>
+
====$db-&gt;add_field( $dataset, $field [, $force ] )====
+
 
+
 
Add $field to $dataset's tables.
 
Add $field to $dataset's tables.
  
 
If $force is true will modify/replace an existing column (use with care!).
 
If $force is true will modify/replace an existing column (use with care!).
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $db-&gt;remove_field( $dataset, $field )
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_remove_field --></div>
+
====$db-&gt;remove_field( $dataset, $field )====
+
 
+
 
Remove $field from $dataset's tables.
 
Remove $field from $dataset's tables.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $ok = $db-&gt;rename_field( $dataset, $field, $old_name )
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_rename_field --></div>
+
====$ok = $db-&gt;rename_field( $dataset, $field, $old_name )====
+
 
+
 
Rename a $field in the database from it's old name $old_name.
 
Rename a $field in the database from it's old name $old_name.
  
 
Returns true if the field was successfully renamed.
 
Returns true if the field was successfully renamed.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
$success = $db-&gt;create_counters
<h4><span style='display:none'>User Comments</span></h4>
+
Create the counters used to store the highest current id of eprints, users etc.
<!-- Edit below this comment -->
+
  
 +
$success = $db-&gt;has_counter( $counter )
 +
Returns true if $counter exists.
  
<!-- Pod2Wiki=item_exists --></div>
+
$success = $db-&gt;create_counter( $name )
====$boolean = $db-&gt;exists( $dataset, $id )====
+
Create and initialise to zero a new counter called $name.
  
Return true if a record with the given primary key exists in the dataset, otherwise false.
+
$success = $db-&gt;remove_counters
 +
Destroy all counters.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce; padding: 0em 1em 0em 1em; font-size: 80%; '>
+
  $success = $db-&gt;drop_counter( $name )
<h4><span style='display:none'>User Comments</span></h4>
+
Destroy the counter named $name.
<!-- Edit below this comment -->
+
 
+
 
+
<!-- Pod2Wiki=item_set_debug --></div>
+
====$db-&gt;set_debug( $boolean )====
+
 
+
Set the SQL debug mode to true or false.
+
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
 
<!-- Edit below this comment -->
 
<!-- Edit below this comment -->
  
  
<!-- Pod2Wiki=item_create_version_table --></div>
+
<!-- Pod2Wiki= -->
====$db-&gt;create_version_table====
+
<!-- Pod2Wiki=head_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]].
  
Make the version table (and set the only value to be the current version of eprints).
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
 
<!-- Edit below this comment -->
 
<!-- Edit below this comment -->
  
  
<!-- Pod2Wiki=item_drop_version_table --></div>
+
<!-- Pod2Wiki= -->
====$db-&gt;drop_version_table====
+
<!-- Pod2Wiki=head_copyright -->
 +
==COPYRIGHT==
 +
Copyright 2000-2011 University of Southampton.
  
Drop the version table.
+
This file is part of EPrints http://www.eprints.org/.
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
EPrints is free software: you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
<h4><span style='display:none'>User Comments</span></h4>
+
<!-- Edit below this comment -->
+
  
 +
EPrints is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License for more details.
  
<!-- Pod2Wiki=item_set_version --></div>
+
You should have received a copy of the GNU Lesser General Public License along with EPrints.  If not, see http://www.gnu.org/licenses/.
====$db-&gt;set_version( $versionid );====
+
  
Set the version id table in the SQL database to the given value (used by the upgrade script).
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
 
<!-- Edit below this comment -->
 
<!-- Edit below this comment -->
  
  
<!-- Pod2Wiki=item_has_table --></div>
+
<!-- Pod2Wiki= -->
====$boolean = $db-&gt;has_table( $tablename )====
+
<!-- Pod2Wiki=_postamble_ -->
 
+
Return true if a table of the given name exists in the database.
+
 
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<h4><span style='display:none'>User Comments</span></h4>
+
 
<!-- Edit below this comment -->
 
<!-- Edit below this comment -->
 
 
<!-- Pod2Wiki=item_has_column --></div>
 
====$boolean = $db-&gt;has_column( $tablename, $columnname )====
 
 
Return true if the a table of the given name has a column named $columnname in the database.
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
<!-- Edit below this comment -->
 
 
 
<!-- Pod2Wiki=item_drop_table --></div>
 
====$db-&gt;drop_table( $tablename )====
 
 
Delete the named table. Use with caution!
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
<!-- Edit below this comment -->
 
 
 
<!-- Pod2Wiki=item_clear_table --></div>
 
====$db-&gt;clear_table( $tablename )====
 
 
Clears all records from the given table, use with caution!
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
<!-- Edit below this comment -->
 
 
 
<!-- Pod2Wiki=item_rename_table --></div>
 
====$db-&gt;rename_table( $tablename, $newtablename )====
 
 
Renames the table from the old name to the new one.
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
<!-- Edit below this comment -->
 
 
 
<!-- Pod2Wiki=item_swap_table --></div>
 
====$db-&gt;swap_table( $table_a, $table_b )====
 
 
Swap table a and table b.
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
<!-- Edit below this comment -->
 
 
 
<!-- Pod2Wiki=item_get_tables --></div>
 
====@tables = $db-&gt;get_tables====
 
 
Return a list of all the tables in the database.
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
<!-- Edit below this comment -->
 
 
 
<!-- Pod2Wiki=item_get_version --></div>
 
====$version = $db-&gt;get_version====
 
 
Return the version of eprints which the database is compatable with or undef if unknown (before v2.1).
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
<!-- Edit below this comment -->
 
 
 
<!-- Pod2Wiki=item_is_latest_version --></div>
 
====$boolean = $db-&gt;is_latest_version====
 
 
Return true if the SQL tables are in the correct configuration for this edition of eprints. Otherwise false.
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
<!-- Edit below this comment -->
 
 
 
<!-- Pod2Wiki=item_valid_login --></div>
 
====$db-&gt;valid_login( $username, $password )====
 
 
Returns whether the clear-text $password matches the stored crypted password for $username.
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
<!-- Edit below this comment -->
 
 
 
<!-- Pod2Wiki=item_get_server_version --></div>
 
====$version = $db-&gt;get_server_version====
 
 
Return the database server version.
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
<!-- Edit below this comment -->
 
 
 
<!-- Pod2Wiki=item_get_default_charset --></div>
 
====$charset = $db-&gt;get_default_charset( LANGUAGE )====
 
 
Return the character set to use for LANGUAGE.
 
 
Returns undef if character sets are unsupported.
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
<!-- Edit below this comment -->
 
 
 
<!-- Pod2Wiki=item_get_default_collation --></div>
 
====$collation = $db-&gt;get_default_collation( LANGUAGE )====
 
 
Return the collation to use for LANGUAGE.
 
 
Returns undef if collation is unsupported.
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
<!-- Edit below this comment -->
 
 
 
<!-- Pod2Wiki=item_get_driver_name --></div>
 
====$driver = $db-&gt;get_driver_name====
 
 
Return the database driver name.
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
<!-- Edit below this comment -->
 
 
 
<!-- Pod2Wiki=for --></div>
 
INTERNAL<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
<!-- Edit below this comment -->
 
 
 
<!-- Pod2Wiki=item_dequeue_events --></div>
 
====@events = $db-&gt;dequeue_events( $n )====
 
 
Attempt to dequeue upto $n events. May return between 0 and $n events depending on parallel processes and how many events are remaining on the queue.
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
<!-- Edit below this comment -->
 
 
 
<!-- Pod2Wiki=item_prepare_regexp --></div>
 
====$sql = $db-&gt;prepare_regexp( $quoted_column, $quoted_value )====
 
 
The syntax used for regular expressions varies across databases. This method takes two '''quoted''' values and returns a SQL expression that will apply the regexp ($quoted_value) to the column ($quoted_column).
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<h4><span style='display:none'>User Comments</span></h4>
 
<!-- Edit below this comment -->
 
 
 
<!-- Pod2Wiki=_postamble_ --><!-- Edit below this comment -->
 

Latest revision as of 09:56, 22 January 2013

EPrints 3 Reference: Directory Structure - Metadata Fields - Repository Configuration - XML Config Files - XML Export Format - EPrints data structure - Core API - Data Objects


API: Core API

Latest Source Code (3.3, 3.2) | Revision Log | Before editing this page please read Pod2Wiki


NAME

EPrints::Database - a connection to the SQL database for an eprints session


SYNOPSIS

$db = $repo->database
$sth = $db->prepare("SELECT 'hello, world!'");
$sth->execute;
print $sth->fetchrow_arrayref->[0], "\n";


DESCRIPTION

EPrints Database Access Module

Provides access to the backend database. All database access is performed via this module to 1) provide cross-database support and 2) improve security through enforcing proper quoting of SQL values.

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


Cross-database Support

Any use of SQL statements must use 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.


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:

  my $Q_value = $db->quote_value( "Hello, World!" );
  $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.


METHODS

Database

$db = EPrints::Database->new( $repo )

Create a connection to the database.

$db = $db->create( $username, $password )

Create and connect to a new database using super user account $username and $password.

$ok = $db->connect

Connects to the database.

$db->disconnect

Disconnects from the EPrints database.

$db->set_debug( $boolean )

Set the SQL debug mode to true or false.

$db->set_version( $versionid );

Set the version id table in the SQL database to the given value (used by the upgrade script).

$version = $db->get_version

Returns the current database schema version.

$boolean = $db->is_latest_version

Return true if the database schema is in the correct configuration for this version of eprints. Otherwise false.

$version = $db->get_server_version

Return the database server version.

$charset = $db->get_default_charset( LANGUAGE )

Return the character set to use for LANGUAGE.

Returns undef if character sets are unsupported.

$collation = $db->get_default_collation( LANGUAGE )

Return the collation to use for LANGUAGE.

Returns undef if collation is unsupported.

$driver = $db->get_driver_name

Return the database driver name.

$errstr = $db->error

Return a string describing the last SQL error.

$bool = $db->retry_error

Returns true if the current error is a retry error.

$bool = $db->duplicate_error

Returns true if the current error is a PRIMARY KEY or UNIQUE error.

$db->begin

Begin a transaction.

$db->commit

Commit the previously begun transaction.

$db->rollback

Roll-back the current transaction.

$type_info = $db->type_info( DATA_TYPE )

See DBI/type_info.

$real_type = $db->get_column_type( NAME, TYPE, NOT_NULL, [ LENGTH/PRECISION ], [ SCALE ], %opts )

Returns a SQL column definition for NAME of type TYPE, usually something like:

  $name $type($length,$scale) [ 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).

Other options available to refine the column definition:

  langid - character set/collation to use
  sorted - whether this column will be used to order by

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:

  use EPrints::Database qw( :sql_types );

Supported types (n = requires LENGTH argument):

Character data: SQL_VARCHAR(n), SQL_LONGVARCHAR, SQL_CLOB.

Binary data: SQL_VARBINARY(n), SQL_LONGVARBINARY.

Integer data: SQL_TINYINT, SQL_SMALLINT, SQL_INTEGER, SQL_BIGINT.

Floating-point data: SQL_REAL, SQL_DOUBLE.

Time data: SQL_DATE, SQL_TIME.

The actual column types used will be database-specific.


Basic SQL operations

$success = $db->do( $sql )

Execute the given SQL.

$sth = $db->prepare( $sql )

Prepare the SQL statement $sql for execution.

Use the execute method on the returned DBI handle to execute the SQL:

  my $sth = $db->prepare_select( "SELECT 'Hello, World'" );
  $sth->execute;
$sth = $db->prepare_select( $sql [, %options ] )

Prepare a SELECT statement $sql for execution. This method provides cross-database support for getting a part of the result list based on an offset/limit.

Returns a DBI statement handle.

Options:

  limit - limit the number of rows returned
  offset - return limit number of rows after offset
$rows = $db->update( $tablename, $keycols, $keyvals, $columns, $values )

UPDATEs $columns with the equivalent slot from $values where the rows in $tablename match the $keycols/$keyvals.

Returns the number of rows affected or false on error, see DBI's execute() method.

  $db->update(
    'eprint',
    ['eprintid'],
    [23],
    ['title','description'],
    ['Hello, World!', undef]
  );
$success = $db->update_quoted( $tablename, $keycols, $keyvals, $columns, $Q_values )

UPDATEs $tablename where $keycols equals $keyvals.

Returns the number of rows affected or false on error, see DBI's execute() method.

$success = $db->insert( $table, $columns, @values )

Inserts values into the table $table. If $columns is defined it will be used as a list of columns to insert into. @values is a list of arrays containing values to insert.

Values will be quoted before insertion.

$success = $db->insert_quoted( $table, $columns, @Q_values )

Inserts values into the table $table. If $columns is defined it will be used as a list of columns to insert into. @qvalues is a list of arrays containing values to insert.

Values will NOT be quoted before insertion - care must be exercised!

$success = $db->delete_from( $table, $columns, @values )

Perform a SQL DELETE FROM $table using $columns to build a where clause. @values is a list of array references of values in the same order as $columns.

If you want to clear a table completely use clear_table().

$n = $db->count_table( $tablename )

Return the number of rows in the specified SQL table.

$db->clear_table( $tablename )

Clears all records from the given table, use with caution!


Quoting

$mungedvalue = EPrints::Database::prep_int( $value )

Escape a numerical value for SQL. undef becomes NULL. Anything else becomes a number (zero if needed).

$mungedvalue = EPrints::Database::prep_value( $value )

Escape a value for SQL. Modify value such that " becomes \" and \ becomes \\ and ' becomes \'

$mungedvalue = EPrints::Database::prep_like_value( $value )

Escape an value for an SQL like field. In addition to ' " and \ also escapes % and _

$str = $db->quote_value( $value )

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

 my $str = $database->quote_value( EPrints::Database::prep_like_value( $foo ) . '%' );
$str = $db->quote_int( $value )

Return a quoted integer value

$str = $db->quote_binary( $bytes )

Some databases (Oracle/PostgreSQL) require transforms of binary data to work correctly.

This method should be called on data containing nul bytes or back-slashes before being passed on quote_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 before it's inserted.

$str = $db->quote_identifier( @parts )

Quote a database identifier (e.g. table names). Multiple @parts will be joined by dot.

$sql = $db->prepare_regexp( $quoted_column, $quoted_value )

The syntax used for regular expressions varies across databases. This method takes two quoted values and returns a SQL expression that will apply the regexp ($quoted_value) to the column ($quoted_column).

$sql = $db->sql_AS()

Returns the syntactic glue to use when aliasing. SQL 92 DBs will happilly use " AS " but some DBs (Oracle!) won't accept it.

$sql = $db->sql_LIKE()

Returns the syntactic glue to use when making a case-insensitive LIKE. PostgreSQL requires "ILIKE" while everything else uses "LIKE" and the column collation.

$sql = $db->sql_IS_SET( $qname )

Return the SQL to test whether $qname is set. For most databases this will be:

  $qname is not null AND $qname != ''

$qname is a quoted identifier or value.


Counters

$n = $db->counter_current( $counter )

Return the value of the previous counter_next on $counter.

$n = $db->counter_next( $counter )

Return the next unused value for the named counter. Returns undef if the counter doesn't exist.

$db->counter_minimum( $counter, $value )

Ensure that the counter is set no lower than $value. This is used when importing eprints which may not be in scrict sequence.

$db->counter_reset( $counter )

Reset the counter. Use with caution.

$n = $db->next_doc_pos( $eprintid )

Return the next unused document pos for the given eprintid.


Dataset data

$boolean = $db->exists( $dataset, $id )

Return true if a record with the given primary key exists in the dataset, otherwise false.

$success = $db->insert_data( $dataset, $data )

Add the given data as a new record in the given dataset. $data is a reference to a hash containing values structured for a record in the that dataset.

Returns undef if a record already exists with the key field value in $data.

Inserts ordervalues if the dataset is ordered.

$success = $db->update_data( $dataset, $data, $changed )

Updates a record in the given $dataset. $data is a copy of the entire record's data and $changed the values that have changed.

If the record does not already exist or the key field is unset in $data no changes will be written.

Updates ordervalues if the dataset is ordered.

$success = $db->remove( $dataset, $id )

Remove the record, index terms and order value with the key field value $id from the specified dataset.

Returns true on success.


Searching, caching and object retrieval

$searchexp = $db->cache_exp( $cacheid )

Return the serialised Search of a the cached search with id $cacheid. Return undef if the id is invalid or expired.

$cacheid = $db->cache( $searchexp, $dataset, $srctable, [$order], [$list] )

Create a cache of the specified search expression from the SQL table $srctable.

If $order is set then the cache is ordered by the specified fields. For example "-year/title" orders by year (descending). Records with the same year are ordered by title.

If $srctable is set to "LIST" then order is ignored and the list of ids is taken from the array reference $list.

If $srctable is set to "ALL" every matching record from $dataset is added to the cache, optionally ordered by $order.

$tablename = $db->cache_table( $id )

Return the SQL table used to store the cache with id $id.

$ids = $db->search( $keyfield, $tables, $conditions, [$main_table_alias] )

Return a reference to an array of ids - the results of the search specified by $conditions accross the tables specified in the $tables hash where keys are tables aliases and values are table names.

If no table alias is passed then M is assumed.

$db->drop_cache( $id )

Remove the cached search with the given id.

$foo = $db->from_cache( $dataset, $cacheid, [$offset], [$count], [$justids] )

Return a reference to an array containing all the items from the given dataset that have id's in the specified cache. The cache may be specified either by id or serialised search expression.

$offset is an offset from the start of the cache and $count is the number of records to return.

If $justids is true then it returns just an ref to an array of the record ids, not the objects.

$c = $db->drop_orphan_cache_tables

Drop tables called "cacheXXX" where XXX is an integer. Returns the number of tables dropped.

$obj = $db->get_single( $dataset, $id )

Return a single item from the given dataset. The one with the specified id.

$items = $db->get_all( $dataset )

Returns a reference to an array with all the items from the given dataset.

@ids = $db->get_cache_ids( $dataset, $cachemap, $offset, $count )

Returns a list of $count ids from $cache_id starting at $offset and in the order in the cachemap.

@dataobjs = $db->get_dataobjs( $dataset [, $id [, $id ] ] )

Retrieves the records in $dataset with the given $id(s). If an $id doesn't exist in the database it will be ignored.

$foo = $db->get_values( $field, $dataset )

Return a reference to an array of all the distinct values of the EPrints::MetaField specified.

$ids = $db->get_ids_by_field_values( $field, $dataset [ %opts ] )

Return a reference to a hash table where the keys are field value ids and the value is a reference to an array of ids.

@events = $db->dequeue_events( $n )

Attempt to dequeue upto $n events. May return between 0 and $n events depending on parallel processes and how many events are remaining on the queue.


Password Validation

$real_username = $db->valid_login( $username, $password )

Tests whether the clear-text $password matches the stored encrypted password for $username.

Returns the user's real (case-sensitive) username or undef if the passwords don't match.


Database schema manipulation

$boolean = $db->has_sequence( $name )

Return true if a sequence of the given name exists in the database.

$success = $db->create_sequence( $seq_name )

Creates a new sequence object initialised to zero.

$success = $db->drop_sequence( $seq_name )

Deletes a sequence object.

$boolean = $db->has_column( $tablename, $columnname )

Return true if the a table of the given name has a column named $columnname in the database.

$success = $db->drop_column( $table, $column )

Drops a column from a table.

@columns = $db->get_primary_key( $tablename )

Returns the list of column names that comprise the primary key for $tablename.

Returns empty list if no primary key exists.

$db->create_primary_key( $tablename, @cols )

Create a PRIMARY KEY on $tablename over @cols.

$name = $db->index_name( $table, @columns )

Returns the name of the first index that starts with @columns on the $table table.

Returns undef if no index exists.

$success = $db->create_index( $tablename, @columns )

Creates an index over @columns for $tablename. Returns true on success.

$success = $db->create_unique_index( $tablename, @columns )

Creates a unique index over @columns for $tablename. Returns true on success.

$ok = $db->create_foreign_key( $main_table, $aux_table, $key_field )

Create a foreign key relationship between $main_table and $aux_table using the $key_field.

This will cause records in $aux_table to be deleted if the equivalent record is deleted from $main_table.

@tables = $db->get_tables

Return a list of all the tables in the database.

$boolean = $db->has_table( $tablename )

Return true if a table of the given name exists in the database.

$success = $db->create_table( $tablename, $setkey, @fields );

Creates a new table $tablename based on @fields.

The first $setkey number of fields are used for a primary key.

$db->drop_table( $tablename [, $tablename2 ] )

Delete the named table(s). Use with caution!

$db->rename_table( $tablename, $newtablename )

Renames the table from the old name to the new one.

$db->swap_table( $table_a, $table_b )

Swap table a and table b.


EPrints schema manipulation

$success = $db->create_archive_tables

Create all the SQL tables for each dataset.

$db->drop_archive_tables()

Destroy all tables used by eprints in the database.

$db->create_version_table

Make the version table (and set the only value to be the current version of eprints).

$db->drop_version_table

Drop the version table.

$db->has_dataset( $dataset )

Returns true if $dataset exists in the database or has no database tables.

This does not check that all fields are configured - see has_field().

$success = $db->create_dataset_tables( $dataset )

Create all the SQL tables for a single dataset.

Index tables are created in the dataset is indexable.

Order values tables are created for each configured languaged if the dataset is ordered.

$db->drop_dataset_tables( $dataset )

Drop all the SQL tables for a single dataset.

$db->has_field( $dataset, $field )

Returns true if $field is in the database for $dataset.

$db->add_field( $dataset, $field [, $force ] )

Add $field to $dataset's tables.

If $force is true will modify/replace an existing column (use with care!).

$db->remove_field( $dataset, $field )

Remove $field from $dataset's tables.

$ok = $db->rename_field( $dataset, $field, $old_name )

Rename a $field in the database from it's old name $old_name.

Returns true if the field was successfully renamed.

$success = $db->create_counters

Create the counters used to store the highest current id of eprints, users etc.

$success = $db->has_counter( $counter )

Returns true if $counter exists.

$success = $db->create_counter( $name )

Create and initialise to zero a new counter called $name.

$success = $db->remove_counters

Destroy all counters.

$success = $db->drop_counter( $name )

Destroy the counter named $name.


SEE ALSO

To access database-stored objects use the methods provided by the following modules: EPrints::Repository, EPrints::DataSet.


COPYRIGHT

Copyright 2000-2011 University of Southampton.

This file is part of EPrints http://www.eprints.org/.

EPrints is free software: you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

EPrints is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public License along with EPrints. If not, see http://www.gnu.org/licenses/.