Difference between revisions of "API:EPrints/Database"

From EPrints Documentation
Jump to: navigation, search
Line 6: Line 6:
 
<!-- Pod2Wiki=_private_ --><!-- Pod2Wiki=head_name -->
 
<!-- 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%; '>
 +
<span style='display:none'>User Comments</span>
 +
<!-- Edit below this comment -->
 +
 +
 +
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=head_synopsis -->
 +
==SYNOPSIS==
 +
  $db = $repo-&gt;database
 +
  $sth = $db-&gt;prepare("SELECT 'hello, world!'");
 +
  $sth-&gt;execute;
 +
  print $sth-&gt;fetchrow_arrayref-&gt;[0], "\n";
 +
 
 
<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%; '>
 
<span style='display:none'>User Comments</span>
 
<span style='display:none'>User Comments</span>
Line 19: Line 33:
 
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 34: Line 46:
 
<!-- Pod2Wiki=head_cross_database_support -->
 
<!-- 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%; '>
 +
<span style='display:none'>User Comments</span>
 +
<!-- Edit below this comment -->
 +
 
 +
 
 +
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- 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:
 +
 
 +
  my $Q_value = $db-&gt;quote_value( "Hello, World!" );
 +
  $db-&gt;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.
  
 
<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 54: Line 80:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_build_connection_string -->
+
<!-- Pod2Wiki=head_database -->
===build_connection_string===
+
===Database===
 +
<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_new -->
 +
====new====
  
  $dbstr = EPrints::Database::build_connection_string( %params )
+
  $db = EPrints::Database-&gt;new( $repo )
Build the string to use to connect to the database via DBI. %params  must contain dbname, and may also contain dbport, dbhost and dbsock.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 68: Line 103:
 
</div>
 
</div>
 
<!-- Pod2Wiki=item_create -->
 
<!-- Pod2Wiki=item_create -->
===create===
+
====create====
  
 
  $db = $db-&gt;create( $username, $password )
 
  $db = $db-&gt;create( $username, $password )
Line 80: Line 115:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_new -->
+
<!-- Pod2Wiki=item_connect -->
===new===
+
====connect====
 +
 
 +
$ok = $db-&gt;connect
 +
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%; '>
 +
<span style='display:none'>User Comments</span>
 +
<!-- Edit below this comment -->
 +
 
 +
 
 +
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=item_disconnect -->
 +
====disconnect====
 +
 
 +
$db-&gt;disconnect
 +
Disconnects from the EPrints database.
 +
 
 +
<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_set_debug -->
 +
====set_debug====
 +
 
 +
$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%; '>
 +
<span style='display:none'>User Comments</span>
 +
<!-- Edit below this comment -->
 +
 
 +
 
 +
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=item_set_version -->
 +
====set_version====
  
  $db = EPrints::Database-&gt;new( $session )
+
  $db-&gt;set_version( $versionid );
Create a connection to the database.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 93: Line 167:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_connect -->
+
<!-- Pod2Wiki=item_get_version -->
===connect===
+
====get_version====
  
  $foo = $db-&gt;connect
+
  $version = $db-&gt;get_version
Connects to the database.  
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 106: Line 180:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_disconnect -->
+
<!-- Pod2Wiki=item_is_latest_version -->
===disconnect===
+
====is_latest_version====
  
  $foo = $db-&gt;disconnect
+
  $boolean = $db-&gt;is_latest_version
Disconnects from the EPrints database. Should always be done before any script exits.
+
Return true if the database schema is in the correct configuration for this version 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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 119: Line 193:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_error -->
+
<!-- Pod2Wiki=item_get_server_version -->
===error===
+
====get_server_version====
  
  $errstr = $db-&gt;error
+
  $version = $db-&gt;get_server_version
Return a string describing the last SQL error.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 132: Line 206:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_begin -->
+
<!-- Pod2Wiki=item_get_default_charset -->
===begin===
+
====get_default_charset====
 +
 
 +
$charset = $db-&gt;get_default_charset( LANGUAGE )
 +
Return the character set to use for LANGUAGE.
  
$db-&gt;begin
+
Returns undef if character sets are unsupported.
Begin a transaction.
 
  
 
<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 145: Line 221:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_commit -->
+
<!-- Pod2Wiki=item_get_default_collation -->
===commit===
+
====get_default_collation====
 +
 
 +
$collation = $db-&gt;get_default_collation( LANGUAGE )
 +
Return the collation to use for LANGUAGE.
  
$db-&gt;commit
+
Returns undef if collation is unsupported.
Commit the previous 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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 158: Line 236:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_rollback -->
+
<!-- Pod2Wiki=item_get_driver_name -->
===rollback===
+
====get_driver_name====
  
  $db-&gt;rollback
+
  $driver = $db-&gt;get_driver_name
Rollback the partially completed transaction.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 171: Line 249:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_create_archive_tables -->
+
<!-- Pod2Wiki=item_error -->
===create_archive_tables===
+
====error====
  
  $success = $db-&gt;create_archive_tables
+
  $errstr = $db-&gt;error
Create all the SQL tables for each dataset.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 184: Line 262:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_drop_archive_tables -->
+
<!-- Pod2Wiki=item_retry_error -->
===drop_archive_tables===
+
====retry_error====
  
  $db-&gt;drop_archive_tables()
+
  $bool = $db-&gt;retry_error
Destroy all tables used by eprints in the database.
+
Returns true if the current error is a retry error.
  
 
<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 197: Line 275:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_create_dataset_tables -->
+
<!-- Pod2Wiki=item_duplicate_error -->
===create_dataset_tables===
+
====duplicate_error====
  
  $success = $db-&gt;create_dataset_tables( $dataset )
+
  $bool = $db-&gt;duplicate_error
Create all the SQL tables for a single dataset.
+
Returns true if the current error is a PRIMARY KEY or UNIQUE error.
  
 
<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 210: Line 288:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_drop_dataset_tables -->
+
<!-- Pod2Wiki=item_begin -->
===drop_dataset_tables===
+
====begin====
  
  $db-&gt;drop_dataset_tables( $dataset )
+
  $db-&gt;begin
Drop all the SQL tables for a single dataset.
+
Begin a transaction.
  
 
<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 223: Line 301:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_create_dataset_index_tables -->
+
<!-- Pod2Wiki=item_commit -->
===create_dataset_index_tables===
+
====commit====
  
  $success = $db-&gt;create_dataset_index_tables( $dataset )
+
  $db-&gt;commit
Create all the index tables for a single dataset.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 236: Line 314:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_create_dataset_ordervalues_tables -->
+
<!-- Pod2Wiki=item_rollback -->
===create_dataset_ordervalues_tables===
+
====rollback====
  
  $success = $db-&gt;create_dataset_ordervalues_tables( $dataset )
+
  $db-&gt;rollback
Create all the ordervalues tables for a single dataset.
+
Roll-back the current transaction.
  
 
<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 250: Line 328:
 
</div>
 
</div>
 
<!-- Pod2Wiki=item_type_info -->
 
<!-- Pod2Wiki=item_type_info -->
===type_info===
+
====type_info====
  
 
  $type_info = $db-&gt;type_info( DATA_TYPE )
 
  $type_info = $db-&gt;type_info( DATA_TYPE )
Line 263: Line 341:
 
</div>
 
</div>
 
<!-- Pod2Wiki=item_get_column_type -->
 
<!-- Pod2Wiki=item_get_column_type -->
===get_column_type===
+
====get_column_type====
  
 
  $real_type = $db-&gt;get_column_type( NAME, TYPE, NOT_NULL, [ LENGTH/PRECISION ], [ SCALE ], %opts )
 
  $real_type = $db-&gt;get_column_type( NAME, TYPE, NOT_NULL, [ LENGTH/PRECISION ], [ SCALE ], %opts )
Line 306: Line 384:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_create_table -->
+
<!-- Pod2Wiki=head_schema_manipulation -->
===create_table===
+
===Schema Manipulation===
 +
<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 -->
  
$success = $db-&gt;create_table( $tablename, $dataset, $setkey, @fields );
 
Create the tables used to store metadata for this dataset: the main table and any required for multiple or mulitlang fields.
 
  
The first $setkey number of fields are used for a primary key.
+
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=item_create_archive_tables -->
 +
====create_archive_tables====
 +
 
 +
$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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 321: Line 406:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_has_sequence -->
+
<!-- Pod2Wiki=item_drop_archive_tables -->
===has_sequence===
+
====drop_archive_tables====
  
  $boolean = $db-&gt;has_sequence( $name )
+
  $db-&gt;drop_archive_tables()
Return true if a sequence of the given name exists in the database.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 334: Line 419:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_create_sequence -->
+
<!-- Pod2Wiki=item_create_version_table -->
===create_sequence===
+
====create_version_table====
  
  $success = $db-&gt;create_sequence( $seq_name )
+
  $db-&gt;create_version_table
Creates a new sequence object initialised to zero.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 347: Line 432:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_drop_sequence -->
+
<!-- Pod2Wiki=item_drop_version_table -->
===drop_sequence===
+
====drop_version_table====
  
  $success = $db-&gt;drop_sequence( $seq_name )
+
  $db-&gt;drop_version_table
Deletes a sequence object.
+
Drop the version table.
  
 
<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 360: Line 445:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_drop_column -->
+
<!-- Pod2Wiki=item_create_dataset_tables -->
===drop_column===
+
====create_dataset_tables====
  
  $success = $db-&gt;drop_column( $table, $column )
+
  $success = $db-&gt;create_dataset_tables( $dataset )
Drops a column from a table.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 373: Line 458:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_get_primary_key -->
+
<!-- Pod2Wiki=item_drop_dataset_tables -->
===get_primary_key===
+
====drop_dataset_tables====
  
  @columns = $db-&gt;get_primary_key( $tablename )
+
  $db-&gt;drop_dataset_tables( $dataset )
Returns the list of column names that comprise the primary key for $tablename.
+
Drop all the SQL tables for a single dataset.
 
 
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 388: Line 471:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_create_index -->
+
<!-- Pod2Wiki=item_create_dataset_index_tables -->
===create_index===
+
====create_dataset_index_tables====
  
  $success = $db-&gt;create_index( $tablename, @columns )
+
  $success = $db-&gt;create_dataset_index_tables( $dataset )
Creates an index over @columns for $tablename. Returns true on success.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 401: Line 484:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_create_unique_index -->
+
<!-- Pod2Wiki=item_create_dataset_ordervalues_tables -->
===create_unique_index===
+
====create_dataset_ordervalues_tables====
  
  $success = $db-&gt;create_unique_index( $tablename, @columns )
+
  $success = $db-&gt;create_dataset_ordervalues_tables( $dataset )
Creates a unique index over @columns for $tablename. Returns true on success.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 414: Line 497:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item__update -->
+
<!-- Pod2Wiki=item_has_dataset -->
===_update===
+
====has_dataset====
 +
 
 +
$db-&gt;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().
 +
 
 +
<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 -->
  
$rows = $db-&gt;_update( $tablename, $keycols, $keyvals, $columns, @values )
 
UPDATES $tablename where $keycols equals $keyvals and returns the number of rows affected.
 
  
Note! If no rows are affected the result is still 'true', see DBI's execute() method.
+
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=item_has_field -->
 +
====has_field====
  
This method is internal.
+
$db-&gt;has_field( $dataset, $field )
 +
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 431: Line 525:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item__update_quoted -->
+
<!-- Pod2Wiki=item_add_field -->
===_update_quoted===
+
====add_field====
  
  $success = $db-&gt;_update_quoted( $tablename, $keycols, $keyvals, $columns, @qvalues )
+
  $db-&gt;add_field( $dataset, $field [, $force ] )
UPDATES $tablename where $keycols equals $keyvals. Won't quote $keyvals or @qvalues before use - use this method with care!
+
Add $field to $dataset's tables.
  
This method is internal.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 446: Line 540:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_insert -->
+
<!-- Pod2Wiki=item_remove_field -->
===insert===
+
====remove_field====
  
  $success = $db-&gt;insert( $table, $columns, @values )
+
  $db-&gt;remove_field( $dataset, $field )
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.
+
Remove $field from $dataset's tables.
 
 
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 461: Line 553:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_insert_quoted -->
+
<!-- Pod2Wiki=item_rename_field -->
===insert_quoted===
+
====rename_field====
  
  $success = $db-&gt;insert_quoted( $table, $columns, @qvalues )
+
  $ok = $db-&gt;rename_field( $dataset, $field, $old_name )
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.
+
Rename a $field in the database from it's old name $old_name.
  
Values will NOT be quoted before insertion - care must be exercised!
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 476: Line 568:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_delete_from -->
+
<!-- Pod2Wiki=item_create_table -->
===delete_from===
+
====create_table====
  
  $success = $db-&gt;delete_from( $table, $columns, @values )
+
  $success = $db-&gt;create_table( $tablename, $setkey, @fields );
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.
+
Creates a new table $tablename based on @fields.
  
If you want to clear a table completely use clear_table().
+
The first $setkey number of fields are used for a primary key.
  
 
<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 491: Line 583:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_add_record -->
+
<!-- Pod2Wiki=item_create_foreign_key -->
===add_record===
+
====create_foreign_key====
  
  $success = $db-&gt;add_record( $dataset, $data )
+
  $ok = $db-&gt;create_foreign_key( $main_table, $aux_table, $key_field )
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.
+
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.
  
 
<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 504: Line 598:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_prep_int -->
+
<!-- Pod2Wiki=item_create_counters -->
===prep_int===
+
====create_counters====
  
  $mungedvalue = EPrints::Database::prep_int( $value )
+
  $success = $db-&gt;create_counters
Escape a numerical value for SQL. undef becomes NULL. Anything else becomes a number (zero if needed).
+
Create the counters used to store the highest current id of eprints, users etc.
  
 
<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 517: Line 611:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_prep_value -->
+
<!-- Pod2Wiki=item_has_counter -->
===prep_value===
+
====has_counter====
  
  $mungedvalue = EPrints::Database::prep_value( $value )
+
  $success = $db-&gt;has_counter( $counter )
Escape a value for SQL. Modify value such that " becomes \" and \  becomes \\ and ' becomes \'
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 530: Line 624:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_prep_like_value -->
+
<!-- Pod2Wiki=item_create_counter -->
===prep_like_value===
+
====create_counter====
  
  $mungedvalue = EPrints::Database::prep_like_value( $value )
+
  $success = $db-&gt;create_counter( $name )
Escape an value for an SQL like field. In addition to ' " and \ also  escapes % and _
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 543: Line 637:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_quote_value -->
+
<!-- Pod2Wiki=item_remove_counters -->
===quote_value===
+
====remove_counters====
  
  $str = $db-&gt;quote_value( $value )
+
  $success = $db-&gt;remove_counters
Return a quoted value. To quote a 'like' value you should do:
+
Destroy all counters.
  
my $str = $database-&gt;quote_value( EPrints::Database::prep_like_value( $foo ) . '%' );
 
 
 
 
<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%; '>
 
<span style='display:none'>User Comments</span>
 
<span style='display:none'>User Comments</span>
Line 558: Line 650:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_quote_int -->
+
<!-- Pod2Wiki=item_drop_counter -->
===quote_int===
+
====drop_counter====
  
  $str = $db-&gt;quote_int( $value )
+
  $success = $db-&gt;drop_counter( $name )
Return a quoted integer value
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 571: Line 663:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_quote_binary -->
+
<!-- Pod2Wiki=item_counter_current -->
===quote_binary===
+
====counter_current====
  
  $str = $db-&gt;quote_binary( $bytes )
+
  $n = $db-&gt;counter_current( $counter )
Some databases (Oracle/PostgreSQL) require transforms of binary data to work correctly.
+
Return the value of the previous counter_next on $counter.
 
 
This method should be called on data containing nul bytes or back-slashes before being passed on [[API:EPrints/Database#quote_value|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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 586: Line 676:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_quote_ordervalue -->
+
<!-- Pod2Wiki=item_counter_next -->
===quote_ordervalue===
+
====counter_next====
  
  $str = $db-&gt;quote_ordervalue( $field, $value )
+
  $n = $db-&gt;counter_next( $counter )
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.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 599: Line 689:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_quote_identifier -->
+
<!-- Pod2Wiki=item_counter_minimum -->
===quote_identifier===
+
====counter_minimum====
  
  $str = $db-&gt;quote_identifier( @parts )
+
  $db-&gt;counter_minimum( $counter, $value )
Quote a database identifier (e.g. table names). Multiple @parts will be joined by dot.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 612: Line 702:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_update -->
+
<!-- Pod2Wiki=item_counter_reset -->
===update===
+
====counter_reset====
  
  $success = $db-&gt;update( $dataset, $data, $changed, $insert )
+
  $db-&gt;counter_reset( $counter )
Updates a record in the database with the given $data. Obviously the value of the primary key must be set.
+
Reset the counter. Use with caution.
 
 
This also updates the text indexes and the ordering keys.
 
  
 
<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 627: Line 715:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_remove -->
+
<!-- Pod2Wiki=item_next_doc_pos -->
===remove===
+
====next_doc_pos====
 +
 
 +
$n = $db-&gt;next_doc_pos( $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%; '>
 +
<span style='display:none'>User Comments</span>
 +
<!-- Edit below this comment -->
  
$success = $db-&gt;remove( $dataset, $id )
 
Attempts to remove the record with the primary key $id from the  specified dataset.
 
  
 +
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=head_sql_utility_methods -->
 +
===SQL utility methods===
 
<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%; '>
 
<span style='display:none'>User Comments</span>
 
<span style='display:none'>User Comments</span>
Line 640: Line 737:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_create_counters -->
+
<!-- Pod2Wiki=item_do -->
===create_counters===
+
====do====
  
  $success = $db-&gt;create_counters
+
  $success = $db-&gt;do( $sql )
Create the counters used to store the highest current id of eprints, users etc.
+
Execute the given SQL.
  
 
<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 653: Line 750:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_has_counter -->
+
<!-- Pod2Wiki=item_prepare -->
===has_counter===
+
====prepare====
 +
 
 +
$sth = $db-&gt;prepare( $sql )
 +
Prepare the SQL statement $sql for execution.
  
$success = $db-&gt;has_counter( $counter )
+
Use the <tt>execute</tt> method on the returned {{API:PodLink|file=DBI|package_name=DBI|section=|text=DBI}} handle to execute the SQL:
Returns true if $counter exists.
 
  
 +
  my $sth = $db-&gt;prepare_select( "SELECT 'Hello, World'" );
 +
  $sth-&gt;execute;
 +
 
 
<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%; '>
 
<span style='display:none'>User Comments</span>
 
<span style='display:none'>User Comments</span>
Line 666: Line 768:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_create_counter -->
+
<!-- Pod2Wiki=item_prepare_select -->
===create_counter===
+
====prepare_select====
 +
 
 +
$sth = $db-&gt;prepare_select( $sql [, %options ] )
 +
Prepare a SELECT statement $sql for execution.
 +
 
 +
Returns a {{API:PodLink|file=DBI|package_name=DBI|section=|text=DBI}} statement handle.
 +
 
 +
The LIMIT SQL keyword is not universally supported, to specify a LIMIT you must use the '''limit''' option.
  
$success = $db-&gt;create_counter( $name )
+
Options:
Create and initialise to zero a new counter called $name.
 
  
 +
  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%; '>
 
<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>
 
<span style='display:none'>User Comments</span>
Line 679: Line 790:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_remove_counters -->
+
<!-- Pod2Wiki=item_has_sequence -->
===remove_counters===
+
====has_sequence====
  
  $success = $db-&gt;remove_counters
+
  $boolean = $db-&gt;has_sequence( $name )
Destroy all counters.
+
Return true if a sequence 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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 692: Line 803:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_drop_counter -->
+
<!-- Pod2Wiki=item_create_sequence -->
===drop_counter===
+
====create_sequence====
  
  $success = $db-&gt;drop_counter( $name )
+
  $success = $db-&gt;create_sequence( $seq_name )
Destroy the counter named $name.
+
Creates a new sequence object initialised to zero.
  
 
<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 705: Line 816:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_next_doc_pos -->
+
<!-- Pod2Wiki=item_drop_sequence -->
===next_doc_pos===
+
====drop_sequence====
  
  $n = $db-&gt;next_doc_pos( $eprintid )
+
  $success = $db-&gt;drop_sequence( $seq_name )
Return the next unused document pos for the given eprintid.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 718: Line 829:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_counter_current -->
+
<!-- Pod2Wiki=item_drop_column -->
===counter_current===
+
====drop_column====
  
  $n = $db-&gt;counter_current( $counter )
+
  $success = $db-&gt;drop_column( $table, $column )
Return the value of the previous counter_next on $counter.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 731: Line 842:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_counter_next -->
+
<!-- Pod2Wiki=item_get_primary_key -->
===counter_next===
+
====get_primary_key====
 +
 
 +
@columns = $db-&gt;get_primary_key( $tablename )
 +
Returns the list of column names that comprise the primary key for $tablename.
  
$n = $db-&gt;counter_next( $counter )
+
Returns empty list if no primary key exists.
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 744: Line 857:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_counter_minimum -->
+
<!-- Pod2Wiki=item_create_primary_key -->
===counter_minimum===
+
====create_primary_key====
  
  $db-&gt;counter_minimum( $counter, $value )
+
  $db-&gt;create_primary_key( $tablename, @cols )
Ensure that the counter is set no lower than $value. This is used when importing eprints which may not be in scrict sequence.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 757: Line 870:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_counter_reset -->
+
<!-- Pod2Wiki=item_create_index -->
===counter_reset===
+
====create_index====
  
  $db-&gt;counter_reset( $counter )
+
  $success = $db-&gt;create_index( $tablename, @columns )
Reset the counter. Use with caution.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 770: Line 883:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_cache_exp -->
+
<!-- Pod2Wiki=item_create_unique_index -->
===cache_exp===
+
====create_unique_index====
  
  $searchexp = $db-&gt;cache_exp( $cacheid )
+
  $success = $db-&gt;create_unique_index( $tablename, @columns )
Return the serialised Search of a the cached search with id $cacheid. Return undef if the id is invalid or expired.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 783: Line 896:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_cache -->
+
<!-- Pod2Wiki=item_index_name -->
===cache===
+
====index_name====
  
  $cacheid = $db-&gt;cache( $searchexp, $dataset, $srctable, [$order], [$list] )
+
  $name = $db-&gt;index_name( $table, @columns )
Create a cache of the specified search expression from the SQL table $srctable.
+
Returns the name of the first index that starts with @columns on the $table table.
  
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.
+
Returns undef if no index exists.
 
 
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.
 
  
 
<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 802: Line 911:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_cache_table -->
+
<!-- Pod2Wiki=item__update -->
===cache_table===
+
====_update====
 +
 
 +
$rows = $db-&gt;_update( $tablename, $keycols, $keyvals, $columns, @values )
 +
UPDATES $tablename where $keycols equals $keyvals and returns the number of rows affected.
 +
 
 +
Note! If no rows are affected the result is still 'true', see DBI's execute() method.
  
$tablename = $db-&gt;cache_table( $id )
+
This method is internal.
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 815: Line 928:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_get_index_ids -->
+
<!-- Pod2Wiki=item_insert -->
===get_index_ids===
+
====insert====
  
  $ids = $db-&gt;get_index_ids( $table, $condition )
+
  $success = $db-&gt;insert( $table, $columns, @values )
Return a reference to an array of the distinct primary keys from the given SQL table which match the specified condition.
+
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.
  
 
<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 828: Line 943:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_search -->
+
<!-- Pod2Wiki=item_insert_quoted -->
===search===
+
====insert_quoted====
  
  $ids = $db-&gt;search( $keyfield, $tables, $conditions, [$main_table_alias] )
+
  $success = $db-&gt;insert_quoted( $table, $columns, @qvalues )
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.  
+
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.
  
If no table alias is passed then M is assumed.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 843: Line 958:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_drop_cache -->
+
<!-- Pod2Wiki=item_delete_from -->
===drop_cache===
+
====delete_from====
 +
 
 +
$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.
  
$db-&gt;drop_cache( $id )
+
If you want to clear a table completely use clear_table().
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 857: Line 974:
 
</div>
 
</div>
 
<!-- Pod2Wiki=item_count_table -->
 
<!-- Pod2Wiki=item_count_table -->
===count_table===
+
====count_table====
  
 
  $n = $db-&gt;count_table( $tablename )
 
  $n = $db-&gt;count_table( $tablename )
Line 869: Line 986:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_from_cache -->
+
<!-- Pod2Wiki=item_has_table -->
===from_cache===
+
====has_table====
  
  $foo = $db-&gt;from_cache( $dataset, $cacheid, [$offset], [$count], [$justids] )
+
  $boolean = $db-&gt;has_table( $tablename )
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 true if a table of the given name exists in the database.
 
 
$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.
 
  
 
<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 886: Line 999:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_drop_orphan_cache_tables -->
+
<!-- Pod2Wiki=item_has_column -->
===drop_orphan_cache_tables===
+
====has_column====
  
  $c = $db-&gt;drop_orphan_cache_tables
+
  $boolean = $db-&gt;has_column( $tablename, $columnname )
Drop tables called "cacheXXX" where XXX is an integer. Returns the number of tables dropped.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 899: Line 1,012:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_get_single -->
+
<!-- Pod2Wiki=item_drop_table -->
===get_single===
+
====drop_table====
  
  $obj = $db-&gt;get_single( $dataset, $id )
+
  $db-&gt;drop_table( $tablename [, $tablename2 ] )
Return a single item from the given dataset. The one with the specified id.
+
Delete the named table(s). 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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 912: Line 1,025:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_get_all -->
+
<!-- Pod2Wiki=item_clear_table -->
===get_all===
+
====clear_table====
  
  $items = $db-&gt;get_all( $dataset )
+
  $db-&gt;clear_table( $tablename )
Returns a reference to an array with all the items from the given dataset.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 925: Line 1,038:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_get_cache_ids -->
+
<!-- Pod2Wiki=item_rename_table -->
===get_cache_ids===
+
====rename_table====
  
  @ids = $db-&gt;get_cache_ids( $dataset, $cachemap, $offset, $count )
+
  $db-&gt;rename_table( $tablename, $newtablename )
Returns a list of $count ids from $cache_id starting at $offset and in the order in the cachemap.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 938: Line 1,051:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_get_dataobjs -->
+
<!-- Pod2Wiki=item_swap_table -->
===get_dataobjs===
+
====swap_table====
  
  @dataobjs = $db-&gt;get_dataobjs( $dataset [, $id [, $id ] ] )
+
  $db-&gt;swap_table( $table_a, $table_b )
Retrieves the records in $dataset with the given $id(s). If an $id doesn't exist in the database it will be ignored.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 951: Line 1,064:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_get_values -->
+
<!-- Pod2Wiki=item_get_tables -->
===get_values===
+
====get_tables====
  
  $foo = $db-&gt;get_values( $field, $dataset )
+
  @tables = $db-&gt;get_tables
Return a reference to an array of all the distinct values of the EPrints::MetaField specified.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 964: Line 1,077:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_sort_values -->
+
<!-- Pod2Wiki=head_quoting_values -->
===sort_values===
+
===Quoting Values===
 
 
$values = $db-&gt;sort_values( $field, $values [, $langid ] )
 
ALPHA!!! Liable to API change!!!
 
 
 
Sorts and returns the list of $values using the database.
 
 
 
$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.
 
 
 
 
<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%; '>
 
<span style='display:none'>User Comments</span>
 
<span style='display:none'>User Comments</span>
Line 981: Line 1,086:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_get_ids_by_field_values -->
+
<!-- Pod2Wiki=item_prep_int -->
===get_ids_by_field_values===
+
====prep_int====
  
  $ids = $db-&gt;get_ids_by_field_values( $field, $dataset [ %opts ] )
+
  $mungedvalue = EPrints::Database::prep_int( $value )
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.
+
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%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 994: Line 1,099:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_do -->
+
<!-- Pod2Wiki=item_prep_value -->
===do===
+
====prep_value====
  
  $success = $db-&gt;do( $sql )
+
  $mungedvalue = EPrints::Database::prep_value( $value )
Execute the given SQL.
+
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%; '>
 
<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,007: Line 1,112:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_prepare -->
+
<!-- Pod2Wiki=item_prep_like_value -->
===prepare===
+
====prep_like_value====
  
  $sth = $db-&gt;prepare( $sql )
+
  $mungedvalue = EPrints::Database::prep_like_value( $value )
Prepare the given $sql and return a handle on it.
+
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%; '>
 
<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,020: Line 1,125:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_prepare_select -->
+
<!-- Pod2Wiki=item_quote_value -->
===prepare_select===
+
====quote_value====
  
  $sth = $db-&gt;prepare_select( $sql [, %options ] )
+
  $str = $db-&gt;quote_value( $value )
Prepare a SELECT statement $sql and return a handle to it. After preparing a statement use execute() to execute it.
+
Return a quoted value. To quote a 'like' value you should do:
  
The LIMIT SQL keyword is not universally supported, to specify a LIMIT you must use the '''limit''' option.
+
my $str = $database-&gt;quote_value( EPrints::Database::prep_like_value( $foo ) . '%' );
 
 
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%; '>
 
<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,040: Line 1,140:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_execute -->
+
<!-- Pod2Wiki=item_quote_int -->
===execute===
+
====quote_int====
  
  $success = $db-&gt;execute( $sth, $sql )
+
  $str = $db-&gt;quote_int( $value )
Execute the SQL prepared earlier. $sql is only passed in for debugging purposes.
+
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%; '>
 
<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,053: Line 1,153:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_has_dataset -->
+
<!-- Pod2Wiki=item_quote_binary -->
===has_dataset===
+
====quote_binary====
  
  $db-&gt;has_dataset( $dataset )
+
  $str = $db-&gt;quote_binary( $bytes )
Returns true if $dataset exists in the database or has no database tables.
+
Some databases (Oracle/PostgreSQL) require transforms of binary data to work correctly.
  
This does not check that all fields are configured - see has_field().
+
This method should be called on data containing nul bytes or back-slashes before being passed on [[API:EPrints/Database#quote_value|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%; '>
 
<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,068: Line 1,168:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_has_field -->
+
<!-- Pod2Wiki=item_quote_ordervalue -->
===has_field===
+
====quote_ordervalue====
  
  $db-&gt;has_field( $dataset, $field )
+
  $str = $db-&gt;quote_ordervalue( $field, $value )
Returns true if $field is in the database for $dataset.
+
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.
  
 
<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,081: Line 1,181:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_add_field -->
+
<!-- Pod2Wiki=item_quote_identifier -->
===add_field===
+
====quote_identifier====
  
  $db-&gt;add_field( $dataset, $field [, $force ] )
+
  $str = $db-&gt;quote_identifier( @parts )
Add $field to $dataset's tables.
+
Quote a database identifier (e.g. table names). Multiple @parts will be joined by dot.
 
 
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%; '>
 
<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,096: Line 1,194:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_remove_field -->
+
<!-- Pod2Wiki=item_prepare_regexp -->
===remove_field===
+
====prepare_regexp====
  
  $db-&gt;remove_field( $dataset, $field )
+
  $sql = $db-&gt;prepare_regexp( $quoted_column, $quoted_value )
Remove $field from $dataset's tables.
+
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%; '>
 
<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,109: Line 1,207:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_rename_field -->
+
<!-- Pod2Wiki=item_sql_as -->
===rename_field===
+
====sql_as====
  
  $ok = $db-&gt;rename_field( $dataset, $field, $old_name )
+
  $sql = $db-&gt;sql_AS()
Rename a $field in the database from it's old name $old_name.
+
Returns the syntactic glue to use when aliasing. SQL 92 DBs will happilly use " AS " but some DBs (Oracle!) won't accept it.
 
 
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%; '>
 
<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,124: Line 1,220:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_exists -->
+
<!-- Pod2Wiki=item_sql_like -->
===exists===
+
====sql_like====
  
  $boolean = $db-&gt;exists( $dataset, $id )
+
  $sql = $db-&gt;sql_LIKE()
Return true if a record with the given primary key exists in the dataset, otherwise false.
+
Returns the syntactic glue to use when making a case-insensitive LIKE. PostgreSQL requires "ILIKE" while everything else uses "LIKE" and the column collation.
  
 
<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,137: Line 1,233:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_set_debug -->
+
<!-- Pod2Wiki=head_dataset_data -->
===set_debug===
+
===Dataset Data===
 
 
$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%; '>
 
<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>
 
<span style='display:none'>User Comments</span>
Line 1,150: Line 1,242:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_create_version_table -->
+
<!-- Pod2Wiki=item_insert_data -->
===create_version_table===
+
====insert_data====
 +
 
 +
$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.
 +
 
 +
Returns undef if a record already exists with the key field value in $data.
  
$db-&gt;create_version_table
+
Inserts ordervalues if the dataset is {{API:PodLink|file=EPrints/DataSet|package_name=EPrints::DataSet|section=ordered|text=ordered}}.
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%; '>
 
<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,163: Line 1,259:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_drop_version_table -->
+
<!-- Pod2Wiki=item_update_data -->
===drop_version_table===
+
====update_data====
 +
 
 +
$success = $db-&gt;update_data( $dataset, $data, $changed )
 +
Updates a record in the given {{API:PodLink|file=EPrints/DataSet|package_name=EPrints::DataSet|section=|text=$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.
  
$db-&gt;drop_version_table
+
Updates ordervalues if the dataset is {{API:PodLink|file=EPrints/DataSet|package_name=EPrints::DataSet|section=ordered|text=ordered}}.
Drop the version table.
 
  
 
<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,176: Line 1,276:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_set_version -->
+
<!-- Pod2Wiki=item_exists -->
===set_version===
+
====exists====
  
  $db-&gt;set_version( $versionid );
+
  $boolean = $db-&gt;exists( $dataset, $id )
Set the version id table in the SQL database to the given value (used by the upgrade script).
+
Return true if a record with the given primary key exists in the dataset, 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%; '>
 
<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,189: Line 1,289:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_has_table -->
+
<!-- Pod2Wiki=item_remove -->
===has_table===
+
====remove====
 +
 
 +
$success = $db-&gt;remove( $dataset, $id )
 +
Remove the record, index terms and order value with the key field value $id from the specified dataset.
  
$boolean = $db-&gt;has_table( $tablename )
+
Returns true on success.
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%; '>
 
<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,202: Line 1,304:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_has_column -->
+
<!-- Pod2Wiki=head_searching_caching_and_retrieval -->
===has_column===
+
===Searching, Caching and Retrieval===
 
 
$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%; '>
 
<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>
 
<span style='display:none'>User Comments</span>
Line 1,215: Line 1,313:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_index_name -->
+
<!-- Pod2Wiki=item_cache_exp -->
===index_name===
+
====cache_exp====
  
  $name = $db-&gt;index_name( $table, @columns )
+
  $searchexp = $db-&gt;cache_exp( $cacheid )
Returns the name of the first index that starts with @columns on the $table table.
+
Return the serialised Search of a the cached search with id $cacheid. Return undef if the id is invalid or expired.
 
 
Returns undef if no index exists.
 
  
 
<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,230: Line 1,326:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_drop_table -->
+
<!-- Pod2Wiki=item_cache -->
===drop_table===
+
====cache====
 +
 
 +
$cacheid = $db-&gt;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.
  
$db-&gt;drop_table( $tablename )
+
If $srctable is set to "ALL" every matching record from $dataset is added to the cache, optionally ordered by $order.
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%; '>
 
<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,243: Line 1,345:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_clear_table -->
+
<!-- Pod2Wiki=item_cache_table -->
===clear_table===
+
====cache_table====
  
  $db-&gt;clear_table( $tablename )
+
  $tablename = $db-&gt;cache_table( $id )
Clears all records from the given table, use with caution!
+
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%; '>
 
<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,256: Line 1,358:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_rename_table -->
+
<!-- Pod2Wiki=item_search -->
===rename_table===
+
====search====
 +
 
 +
$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.
  
$db-&gt;rename_table( $tablename, $newtablename )
+
If no table alias is passed then M is assumed.  
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%; '>
 
<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,269: Line 1,373:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_swap_table -->
+
<!-- Pod2Wiki=item_drop_cache -->
===swap_table===
+
====drop_cache====
  
  $db-&gt;swap_table( $table_a, $table_b )
+
  $db-&gt;drop_cache( $id )
Swap table a and table b.  
+
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%; '>
 
<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,282: Line 1,386:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_get_tables -->
+
<!-- Pod2Wiki=item_from_cache -->
===get_tables===
+
====from_cache====
 +
 
 +
$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.
 +
 
 +
$offset is an offset from the start of the cache and $count is the number of records to return.
  
@tables = $db-&gt;get_tables
+
If $justids is true then it returns just an ref to an array of the record ids, not the objects.
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%; '>
 
<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,295: Line 1,403:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_get_version -->
+
<!-- Pod2Wiki=item_drop_orphan_cache_tables -->
===get_version===
+
====drop_orphan_cache_tables====
  
  $version = $db-&gt;get_version
+
  $c = $db-&gt;drop_orphan_cache_tables
Return the version of eprints which the database is compatable with or undef if unknown (before v2.1).
+
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%; '>
 
<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,308: Line 1,416:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_is_latest_version -->
+
<!-- Pod2Wiki=item_get_single -->
===is_latest_version===
+
====get_single====
  
  $boolean = $db-&gt;is_latest_version
+
  $obj = $db-&gt;get_single( $dataset, $id )
Return true if the SQL tables are in the correct configuration for this edition of eprints. Otherwise false.
+
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%; '>
 
<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,321: Line 1,429:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_valid_login -->
+
<!-- Pod2Wiki=item_get_all -->
===valid_login===
+
====get_all====
  
  $db-&gt;valid_login( $username, $password )
+
  $items = $db-&gt;get_all( $dataset )
Returns whether the clear-text $password matches the stored crypted password for $username.
+
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%; '>
 
<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,334: Line 1,442:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_get_server_version -->
+
<!-- Pod2Wiki=item_get_cache_ids -->
===get_server_version===
+
====get_cache_ids====
  
  $version = $db-&gt;get_server_version
+
  @ids = $db-&gt;get_cache_ids( $dataset, $cachemap, $offset, $count )
Return the database server version.
+
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%; '>
 
<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,347: Line 1,455:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_get_default_charset -->
+
<!-- Pod2Wiki=item_get_dataobjs -->
===get_default_charset===
+
====get_dataobjs====
  
  $charset = $db-&gt;get_default_charset( LANGUAGE )
+
  @dataobjs = $db-&gt;get_dataobjs( $dataset [, $id [, $id ] ] )
Return the character set to use for LANGUAGE.
+
Retrieves the records in $dataset with the given $id(s). If an $id doesn't exist in the database it will be ignored.
 
 
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%; '>
 
<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,362: Line 1,468:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_get_default_collation -->
+
<!-- Pod2Wiki=item_get_values -->
===get_default_collation===
+
====get_values====
  
  $collation = $db-&gt;get_default_collation( LANGUAGE )
+
  $foo = $db-&gt;get_values( $field, $dataset )
Return the collation to use for LANGUAGE.
+
Return a reference to an array of all the distinct values of the  [[API:EPrints/MetaField|EPrints::MetaField]] specified.
 
 
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%; '>
 
<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,377: Line 1,481:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_get_driver_name -->
+
<!-- Pod2Wiki=item_get_ids_by_field_values -->
===get_driver_name===
+
====get_ids_by_field_values====
  
  $driver = $db-&gt;get_driver_name
+
  $ids = $db-&gt;get_ids_by_field_values( $field, $dataset [ %opts ] )
Return the database driver name.
+
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.
  
 
<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,391: Line 1,495:
 
</div>
 
</div>
 
<!-- Pod2Wiki=item_dequeue_events -->
 
<!-- Pod2Wiki=item_dequeue_events -->
===dequeue_events===
+
====dequeue_events====
  
 
  @events = $db-&gt;dequeue_events( $n )
 
  @events = $db-&gt;dequeue_events( $n )
Line 1,403: Line 1,507:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_prepare_regexp -->
+
<!-- Pod2Wiki=head_password_validation -->
===prepare_regexp===
+
===Password Validation===
 
 
$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%; '>
 
<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>
 
<span style='display:none'>User Comments</span>
Line 1,416: Line 1,516:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_sql_as -->
+
<!-- Pod2Wiki=item_valid_login -->
===sql_as===
+
====valid_login====
 +
 
 +
$real_username = $db-&gt;valid_login( $username, $password )
 +
Tests whether the clear-text $password matches the stored encrypted password for $username.
  
$sql = $db-&gt;sql_AS()
+
Returns the user's real (case-sensitive) username or undef if the passwords don't match.
Returns the syntactic glue to use when aliasing. SQL 92 DBs will happilly use " AS " but some DBs (Oracle!) won't 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%; '>
Line 1,429: Line 1,531:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
<!-- Pod2Wiki=item_sql_like -->
+
<!-- Pod2Wiki=head_see_also -->
===sql_like===
+
==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]].
$sql = $db-&gt;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.
 
  
 
<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%; '>

Revision as of 08:45, 2 March 2012

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.4, 3.3) | Revision Log | Before editing this page please read Pod2Wiki


Contents

NAME

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

User Comments


SYNOPSIS

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

User Comments


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.

User Comments


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.

User Comments


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.

User Comments


METHODS

User Comments


Database

User Comments


new

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

Create a connection to the database.

User Comments


create

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

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

User Comments


connect

$ok = $db->connect

Connects to the database.

User Comments


disconnect

$db->disconnect

Disconnects from the EPrints database.

User Comments


set_debug

$db->set_debug( $boolean )

Set the SQL debug mode to true or false.

User Comments


set_version

$db->set_version( $versionid );

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

User Comments


get_version

$version = $db->get_version

Returns the current database schema version.

User Comments


is_latest_version

$boolean = $db->is_latest_version

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

User Comments


get_server_version

$version = $db->get_server_version

Return the database server version.

User Comments


get_default_charset

$charset = $db->get_default_charset( LANGUAGE )

Return the character set to use for LANGUAGE.

Returns undef if character sets are unsupported.

User Comments


get_default_collation

$collation = $db->get_default_collation( LANGUAGE )

Return the collation to use for LANGUAGE.

Returns undef if collation is unsupported.

User Comments


get_driver_name

$driver = $db->get_driver_name

Return the database driver name.

User Comments


error

$errstr = $db->error

Return a string describing the last SQL error.

User Comments


retry_error

$bool = $db->retry_error

Returns true if the current error is a retry error.

User Comments


duplicate_error

$bool = $db->duplicate_error

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

User Comments


begin

$db->begin

Begin a transaction.

User Comments


commit

$db->commit

Commit the previously begun transaction.

User Comments


rollback

$db->rollback

Roll-back the current transaction.

User Comments


type_info

$type_info = $db->type_info( DATA_TYPE )

See DBI/type_info.

User Comments


get_column_type

$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.

User Comments


Schema Manipulation

User Comments


create_archive_tables

$success = $db->create_archive_tables

Create all the SQL tables for each dataset.

User Comments


drop_archive_tables

$db->drop_archive_tables()

Destroy all tables used by eprints in the database.

User Comments


create_version_table

$db->create_version_table

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

User Comments


drop_version_table

$db->drop_version_table

Drop the version table.

User Comments


create_dataset_tables

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

Create all the SQL tables for a single dataset.

User Comments


drop_dataset_tables

$db->drop_dataset_tables( $dataset )

Drop all the SQL tables for a single dataset.

User Comments


create_dataset_index_tables

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

Create all the index tables for a single dataset.

User Comments


create_dataset_ordervalues_tables

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

Create all the ordervalues tables for a single dataset.

User Comments


has_dataset

$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().

User Comments


has_field

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

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

User Comments


add_field

$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!).

User Comments


remove_field

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

Remove $field from $dataset's tables.

User Comments


rename_field

$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.

User Comments


create_table

$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.

User Comments


create_foreign_key

$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.

User Comments


create_counters

$success = $db->create_counters

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

User Comments


has_counter

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

Returns true if $counter exists.

User Comments


create_counter

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

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

User Comments


remove_counters

$success = $db->remove_counters

Destroy all counters.

User Comments


drop_counter

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

Destroy the counter named $name.

User Comments


counter_current

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

Return the value of the previous counter_next on $counter.

User Comments


counter_next

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

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

User Comments


counter_minimum

$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.

User Comments


counter_reset

$db->counter_reset( $counter )

Reset the counter. Use with caution.

User Comments


next_doc_pos

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

Return the next unused document pos for the given eprintid.

User Comments


SQL utility methods

User Comments


do

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

Execute the given SQL.

User Comments


prepare

$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;
 

User Comments


prepare_select

$sth = $db->prepare_select( $sql [, %options ] )

Prepare a SELECT statement $sql for execution.

Returns a DBI statement handle.

The LIMIT SQL keyword is not universally supported, to specify a LIMIT you must use the limit option.

Options:

 limit - limit the number of rows returned
 offset - return limit number of rows after offset
 

User Comments


has_sequence

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

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

User Comments


create_sequence

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

Creates a new sequence object initialised to zero.

User Comments


drop_sequence

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

Deletes a sequence object.

User Comments


drop_column

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

Drops a column from a table.

User Comments


get_primary_key

@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.

User Comments


create_primary_key

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

Create a PRIMARY KEY on $tablename over @cols.

User Comments


create_index

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

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

User Comments


create_unique_index

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

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

User Comments


index_name

$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.

User Comments


_update

$rows = $db->_update( $tablename, $keycols, $keyvals, $columns, @values )

UPDATES $tablename where $keycols equals $keyvals and returns the number of rows affected.

Note! If no rows are affected the result is still 'true', see DBI's execute() method.

This method is internal.

User Comments


insert

$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.

User Comments


insert_quoted

$success = $db->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.

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

User Comments


delete_from

$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().

User Comments


count_table

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

Return the number of rows in the specified SQL table.

User Comments


has_table

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

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

User Comments


has_column

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

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

User Comments


drop_table

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

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

User Comments


clear_table

$db->clear_table( $tablename )

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

User Comments


rename_table

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

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

User Comments


swap_table

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

Swap table a and table b.

User Comments


get_tables

@tables = $db->get_tables

Return a list of all the tables in the database.

User Comments


Quoting Values

User Comments


prep_int

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

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

User Comments


prep_value

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

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

User Comments


prep_like_value

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

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

User Comments


quote_value

$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 ) . '%' );
 

User Comments


quote_int

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

Return a quoted integer value

User Comments


quote_binary

$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.

User Comments


quote_ordervalue

$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.

User Comments


quote_identifier

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

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

User Comments


prepare_regexp

$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).

User Comments


sql_as

$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.

User Comments


sql_like

$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.

User Comments


Dataset Data

User Comments


insert_data

$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.

User Comments


update_data

$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.

User Comments


exists

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

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

User Comments


remove

$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.

User Comments


Searching, Caching and Retrieval

User Comments


cache_exp

$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.

User Comments


cache

$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.

User Comments


cache_table

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

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

User Comments


search

$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.

User Comments


drop_cache

$db->drop_cache( $id )

Remove the cached search with the given id.

User Comments


from_cache

$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.

User Comments


drop_orphan_cache_tables

$c = $db->drop_orphan_cache_tables

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

User Comments


get_single

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

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

User Comments


get_all

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

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

User Comments


get_cache_ids

@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.

User Comments


get_dataobjs

@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.

User Comments


get_values

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

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

User Comments


get_ids_by_field_values

$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.

User Comments


dequeue_events

@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.

User Comments


Password Validation

User Comments


valid_login

$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.

User Comments


SEE ALSO

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

User Comments


COPYRIGHT

User Comments