API:EPrints/Database
EPrints 3 Reference: Directory Structure - Metadata Fields - Repository Configuration - XML Config Files - XML Export Format - EPrints data structure - Core API - Data Objects
Latest Source Code (3.4, 3.3) | Revision Log | Before editing this page please read Pod2Wiki
Contents
- 1 NAME
- 2 DESCRIPTION
- 3 CONSTANTS
- 4 INSTANCE VARIABLES
- 5 METHODS
- 5.1 Database
- 5.1.1 new
- 5.1.2 create
- 5.1.3 build_connection_string
- 5.1.4 connect
- 5.1.5 disconnect
- 5.1.6 set_debug
- 5.1.7 set_version
- 5.1.8 get_version
- 5.1.9 is_latest_version
- 5.1.10 get_server_version
- 5.1.11 get_default_charset
- 5.1.12 get_default_collation
- 5.1.13 get_driver_name
- 5.1.14 error
- 5.1.15 retry_error
- 5.1.16 duplicate_error
- 5.1.17 begin
- 5.1.18 commit
- 5.1.19 rollback
- 5.1.20 type_info
- 5.1.21 get_column_type
- 5.2 Basic SQL Operations
- 5.3 Quoting
- 5.4 Counters
- 5.5 Dataset Data
- 5.6 Searching, caching and object retrieval
- 5.6.1 cache_exp
- 5.6.2 cache
- 5.6.3 cache_table
- 5.6.4 cache_userid
- 5.6.5 get_cachemap
- 5.6.6 search
- 5.6.7 drop_cache
- 5.6.8 from_cache
- 5.6.9 drop_orphan_cache_tables
- 5.6.10 get_single
- 5.6.11 get_all
- 5.6.12 get_cache_ids
- 5.6.13 get_dataobjs
- 5.6.14 get_values
- 5.6.15 sort_values
- 5.6.16 get_ids_by_field_values
- 5.6.17 dequeue_events
- 5.6.18 ci_lookup
- 5.7 Password Validation and Secret Fields
- 5.8 Database Schema Manipulation
- 5.8.1 has_sequence
- 5.8.2 create_sequence
- 5.8.3 drop_sequence
- 5.8.4 has_column
- 5.8.5 drop_column
- 5.8.6 get_primary_key
- 5.8.7 index_name
- 5.8.8 get_index_ids
- 5.8.9 create_index
- 5.8.10 create_unique_index
- 5.8.11 create_foreign_key
- 5.8.12 get_tables
- 5.8.13 has_table
- 5.8.14 create_table
- 5.8.15 drop_table
- 5.8.16 rename_table
- 5.8.17 swap_table
- 5.9 EPrints Schema Manipulation
- 5.9.1 create_archive_tables
- 5.9.2 drop_archive_tables
- 5.9.3 create_version_table
- 5.9.4 drop_version_table
- 5.9.5 has_dataset
- 5.9.6 has_dataset_index_tables
- 5.9.7 create_dataset_tables
- 5.9.8 drop_dataset_tables
- 5.9.9 create_dataset_index_tables
- 5.9.10 create_dataset_ordervalues_tables
- 5.9.11 has_field
- 5.9.12 add_field
- 5.9.13 remove_field
- 5.9.14 rename_field
- 5.9.15 create_counters
- 5.9.16 has_counter
- 5.9.17 create_counter
- 5.9.18 remove_counters
- 5.9.19 drop_counter
- 5.10 User Messages
- 5.1 Database
- 6 SEE ALSO
- 7 COPYRIGHT
NAME
EPrints::Database - a connection to the SQL database for an eprints session.
DESCRIPTION
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.
In most use-cases it should not be necessary to use the database module directly. Instead you should use EPrints::DataSet or EPrints::MetaField accessor methods to access objects and field values respectively.
Cross-database Support
Any use of SQL statements must use quote_identifier to quote database tables and columns and quote_value to quote values. The only exception to this are the EPrints::Database::* modules which provide database-driver specific extensions.
Quoting SQL Values
By convention variables that contain already quoted values are prefixed with Q_ so they can be easily recognised when used in string interpolation:
my $Q_value = $db->quote_value( "Hello, World!" );
$db->do("SELECT $Q_value");
Where possible you should avoid quoting values yourself, instead use a method that accepts unquoted values which will (safely) do the work for you.
CONSTANTS
All the SQL_ column types defined by Perl module DBI and the following:
SQL_NULL
A column value is undefined.
SQL_NOT_NULL
A column value is defined.
INSTANCE VARIABLES
$self->{session}
The EPrints::Session which is associated with this database connection.
$self->{debug}
If true then SQL is logged.
$self->{dbh}
The handle on the actual database connection.
METHODS
Database
new
$db = EPrints::Database->new( $repo, [ %opts ] )
Create a connection to the database.
Options:
db_connect - Boolean. Also connect to the database (default: true).
create
$db = $db->create( $username, $password )
Create and connect to a new database using user account $username and $password.
build_connection_string
$dbstr = EPrints::Database::build_connection_string( %params )
Build the string to use to connect to the database via DBI.
Parameters:
dbname - Database name (REQUIRED). dbdriver - Database driver (e.g. mysql, Oracle, pgsql, default: mysql). dbhost - Database host. Assumes localhost if unset. dbport - Port to connect to database host. Assumes default for driver if unset. dbsock - Socket file to connect to database through.
connect
$db->connect()
Connects to the database.
disconnect
$db->disconnect()
Disconnects from the EPrints database. Should always be done before any script exits.
set_debug
$db->set_debug( $boolean )
Set the SQL debug mode to true or false.
set_version
$db->set_version( $versionid );
Set the version id table in the SQL database to the given versionid (used by the upgrade script).
get_version
$version = $db->get_version
Returns the current database schema version.
is_latest_version
$boolean = $db->is_latest_version
Return true if the SQL tables are in the correct configuration for this edition of eprints. Otherwise, false.
get_server_version
$version = $db->get_server_version
Return the database server version.
get_default_charset
$charset = $db->get_default_charset
Return the character set to use.
Returns undef if character sets are unsupported.
get_default_collation
$collation = $db->get_default_collation( $lang )
Return the collation to use for language $lang.
Returns undef if collation is unsupported.
get_driver_name
$driver = $db->get_driver_name
Return the database driver name.
error
$errstr = $db->error()
Return a string describing the last SQL error.
retry_error
$boolean = $db->retry_error()
Returns a boolean for whether the database error is a retry error.
duplicate_error
$boolean = $db->duplicate_error()
Returns a boolean for whether the database error is a duplicate error.
begin
$db->begin()
Begin a transaction.
commit
$db->commit()
Commit the previously begun transaction.
rollback
$db->rollback()
Rollback the partially completed transaction.
type_info
$type_info = $db->type_info( $data_type )
See DBI/type_info.
get_column_type
$real_type = $db->get_column_type( $name, $data_type, $not_null, [ $length, $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 to NOT NULL.
$length 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.
$data_type is the SQL type. The types are constants defined by this module, to import them use:
use EPrints::Database qw( :sql_types );
Supported types (n = requires $length argument):
Character data: SQL_VARCHAR(n), SQL_LONGVARCHAR, SQL_CLOB.
Binary data: SQL_VARBINARY(n), SQL_LONGVARBINARY.
Integer data: SQL_TINYINT, SQL_SMALLINT, SQL_INTEGER, SQL_BIGINT.
Floating-point data: SQL_REAL, SQL_DOUBLE.
Time data: SQL_DATE, SQL_TIME.
The actual column types used will be database-specific.
Basic SQL Operations
do
$success = $db->do( $sql )
Execute the given $sql.
prepare
$sth = $db->prepare( $sql )
Prepare the given $sql and return a handle on it.
Use the execute method on the returned DBI handle to execute the SQL:
my $sth = $db->prepare_select( "SELECT 'Hello, World'" ); $sth->execute;
prepare_select
$sth = $db->prepare_select( $sql, [ %options ] )
Prepare a SELECT statement $sql and return a handle to it. After preparing a statement use execute() to execute it.
Returns a DBI statement handle.
The LIMIT SQL keyword is not universally supported, to specify this use the limit option.
Options:
limit - limit the number of rows returned offset - return limit number of rows after offset
execute
$success = $db->execute( $sth, $sql )
Execute the SQL prepared earlier in the $sth. $sql is only required for debugging purposes.
update
$success = $db->update( $dataset, $data, $changed )
Updates a EPrints::DataObj from $dataset with the given $data. The primary key field (e.g. eprintid) value must be included.
Updates the ordervalues if the $dataset is ordered.
_update
$rows = $db->_update( $tablename, $keycols, $keyvals, $columns, @values )
Updates $columns in $tablename with @values where $keycols equals $keyvals and returns the number of rows affected.
N.B. If no rows are affected, the result is still true, see DBI's execute() method.
This is an internal method.
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. These will be quoted before insertion.
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. These will NOT be quoted before insertion - care must be exercised!
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().
count_table
$n = $db->count_table( $tablename )
Return the number of rows in the specified SQL table with $tablename.
clear_table
$db->clear_table( $tablename )
Clears all records from the given table with $tablename. Use with caution!
Quoting
prep_int
$mungedvalue = EPrints::Database::prep_int( $value )
Escape numerical $value for an SQL statement. undef becomes NULL. Anything else becomes a number (zero if needed).
prep_value
$mungedvalue = EPrints::Database::prep_value( $value )
Escape $value for an SQL statement. Modify value such that " becomes \" and \ becomes \\ and ' becomes \'.
prep_like_value
$mungedvalue = EPrints::Database::prep_like_value( $value )
Escape $value for an SQL LIKE clause. In addition to ' " and \ also escapes % and _.
quote_value
$str = $db->quote_value( $value )
Return a quoted version of $value. To quote a LIKE value you should use:
$db->quote_value( EPrints::Database::prep_like_value( $foo ) . '%' );
quote_int
$str = $db->quote_int( $value )
Return a quoted integer for $value.
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 $bytes containing null bytes or back-slashes before being passed on quote_value.
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 $value for $field before it's inserted.
quote_identifier
$str = $db->quote_identifier( @parts )
Quote a database identifier (e.g. table names). Multiple @parts will be joined by dots (.).
prepare_regexp
$sql = $db->prepare_regexp( $col, $value )
The syntax used for regular expressions varies across databases. This method takes two quoted string and returns a SQL expression that will apply the quoted regexp $value to the quoted column $col.
sql_as
$sql = $db->sql_AS()
Returns the syntactic glue to use when aliasing. SQL 92 databases will happily use AS but some databases (Oracle) will not accept it.
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.
Counters
counter_current
$n = $db->counter_current( $counter )
Return the value of the previous counter_next on $counter.
counter_next
$n = $db->counter_next( $counter )
Return the next unused value for the named $counter. Returns undef if the $counter doesn't exist.
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.
counter_reset
$db->counter_reset( $counter )
Reset the $counter. Use with caution.
next_doc_pos
$n = $db->next_doc_pos( $eprintid )
Return the next unused document position for the given $eprintid.
Dataset Data
exists
$boolean = $db->exists( $dataset, $id )
Return true if there exists an EPrints::DataObj from the $dataset with its primary key set to $id. Otherwise, return false.
add_record
$success = $db->add_record( $dataset, $data )
Add the given $data as a new record in the given $dataset. $data is a reference to a hash containing values structured for a record in the that $dataset.
remove
$success = $db->remove( $dataset, $id )
Attempts to remove the EPrints::DataObj with the primary key $id from the specified $dataset.
Searching, caching and object retrieval
cache_exp
$searchexp = $db->cache_exp( $cacheid )
Return the serialised search of a the cached search with $cacheid. Return undef if the $cacheid is invalid or expired.
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.
cache_table
$tablename = $db->cache_table( $id )
Return the name of the SQL table used to store the cache with $id.
cache_userid
$userid = $db->cache_userid( $id )
Returns the userid associated with the cache with $id if that cache exists.
get_cachemap
$cachemap = $db->get_cachemap( $id )
Return the cachemap with $<id>.
search
$ids = $db->search( $keyfield, $tables, $conditions, [ $main_table_alias ] )
Return a reference to an array of $keyfield IDs - the results of the search specified by $conditions across the tables specified in the $tables hash where keys are tables aliases and values are table names.
If no $main_table_alias is specified then M is assumed.
drop_cache
$db->drop_cache( $id )
Remove the cached search with the given $id.
from_cache
$items = $db->from_cache( $dataset, $cacheid, [ $offset, $count, $justids ] )
Return a reference to an array containing all the items from the given $dataset that have IDs in the cache specified by $cacheid. 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 a reference to an array of the record IDs and not the objects themselves.
drop_orphan_cache_tables
$c = $db->drop_orphan_cache_tables
Drop tables called cacheXXX where XXX is an integer. Returns the number of cache tables dropped.
get_single
$obj = $db->get_single( $dataset, $id )
Return a single <EPrints::DataObj> from the given $dataset with primary key set to $id.
get_all
$items = $db->get_all( $dataset )
Returns a reference to an array with all the <EPrints::DataObj>s from the given $dataset.
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.
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.
get_values
$values = $db->get_values( $field, $dataset )
Return a reference to an array of all the distinct values of the EPrints::MetaField $field for the $dataset specified.
sort_values
$values = $db->sort_values( $field, $values, [ $langid ] )
Sorts and returns the list of $values using the database.
$field is used to get the order value for each value. $langid (or $session->get_langid if unset) is used to determine the database collation to use when sorting the resulting order values.
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 specified $datasets's $field value IDs and the values are references to arrays of IDs.
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 in the queue.
ci_lookup
$value = $db->ci_lookup( $field, $value )
This is a hacky method to support case-insensitive lookup for usernames, emails, etc. It returns the actual case-sensitive version of $value if there is a case-insensitive match for the $field.
Password Validation and Secret Fields
valid_login
$db->valid_login( $username, $password )
Returns whether the clear-text $password matches the stored crypted password for the $username.
secret_matches
$db->secret_matches( $dataobj, $fieldname, $token [, $callback ] )
Returns whether the clear-text $token matches the stored crypted field with $fieldname for the $dataobj according to the $callback function.
If not set, $callback defaults to EPrints::Utils#crypt_equals.
is_secret_set
$boolean = $db->is_secret_set( $dataobj, $fieldname )
Returns a boolean for whether the secret $fieldname for $dataobj has a value set.
Database Schema Manipulation
has_sequence
$boolean = $db->has_sequence( $name )
Returns true if a sequence of the given $name exists in the database. Otherwise, returns false.
create_sequence
$success = $db->create_sequence( $name )
Creates a new sequence object with $name and initialises to zero.
drop_sequence
$success = $db->drop_sequence( $name )
Deletes a sequence object with $name.
has_column
$boolean = $db->has_column( $table, $column )
Return true if the named $table has the named $column in the database.
drop_column
$success = $db->drop_column( $table, $column )
Drops the named $column from the named $table.
get_primary_key
@columns = $db->get_primary_key( $tablename )
Returns a list of column names that comprise the primary key for the $tablename.
Returns an empty list if no primary key exists.
index_name
$name = $db->index_name( $table, @cols )
Returns the name of the first index that starts with named columns @cols in the named $table.
Returns undef if no index exists.
get_index_ids
$ids = $db->get_index_ids( $table, $condition )
Return a reference to an array of the distinct primary keys from the named SQL $table which match the specified $condition.
create_index
$success = $db->create_index( $table, @columns )
Creates an index over @columns for named $table. Returns true on success, false otherwise.
create_unique_index
$success = $db->create_unique_index( $tablename, @columns )
Creates a unique index over @columns for named $table. Returns true on success, false otherwise.
create_foreign_key
$ok = $db->create_foreign_key( $main_table, $table, $key_field )
Create a foreign key relationship between named $main_table and named $table using $key_field.
This will cause records in $table to be deleted if the equivalent record is deleted from $main_table.
get_tables
@tables = $db->get_tables( [ $dbname ] )
Returns a list of all the tables in the database.
$dbname specifies a particular database name of current connection has access to more than one database.
has_table
$boolean = $db->has_table( $tablename )
Returns boolean dependent on whether a table of the $tablename exists in the database.
create_table
$success = $db->create_table( $tablename, $setkey, @fields );
Creates a new table with $tablename based on @fields.
The first $setkey number of fields are used for its primary key.
drop_table
$db->drop_table( @tables )
Delete the named @tables. Use with caution!
rename_table
$db->rename_table( $table_from, $table_to )
Renames the table named $table_from to $table_to.
swap_table
$db->swap_table( $table_a, $table_b )
Renames table named $table_a to $table_b and vice-versa.
EPrints Schema Manipulation
create_archive_tables
$success = $db->create_archive_tables()
Create all the SQL tables for all datasets.
drop_archive_tables
$db->drop_archive_tables()
Destroy all tables used by EPrints in the database.
create_version_table
$db->create_version_table
Make the version table (and set the only value to be the current version of EPrints).
drop_version_table
$db->drop_version_table
Drop the version table.
has_dataset
$db->has_dataset( $dataset )
Returns true if $dataset exists in the database and has all expected tables including ordervalues and index tables.
This does not check that all fields are configured - see </has_field>.
has_dataset_index_tables
$db->has_dataset_index_tables( $dataset )
Returns true if index tables for $dataset exists of if this is not indexable.
create_dataset_tables
$success = $db->create_dataset_tables( $dataset )
Creates all the SQL tables for the specified $dataset.
drop_dataset_tables
$db->drop_dataset_tables( $dataset )
Drops all the SQL tables for the specified $dataset.
create_dataset_index_tables
$success = $db->create_dataset_index_tables( $dataset )
Creates all the index tables for the specified $dataset.
create_dataset_ordervalues_tables
$success = $db->create_dataset_ordervalues_tables( $dataset )
Creates all the ordervalues tables for the specified $dataset.
has_field
$db->has_field( $dataset, $field )
Returns true if $field is in the database for $dataset.
add_field
$db->add_field( $dataset, $field, [ $force ] )
Add $field to $dataset's tables.
If $force is true modify/replace an existing column. Use with care!
remove_field
$db->remove_field( $dataset, $field )
Remove $field from $dataset's tables.
rename_field
$ok = $db->rename_field( $dataset, $field, $old_name )
Rename the $field in the $dataset from its $old_name.
Returns true if the $field is successfully renamed.
create_counters
$success = $db->create_counters
Create the counters used to store the highest current ID of eprints, users, etc.
has_counter
$success = $db->has_counter( $counter )
Returns true if $counter exists.
create_counter
$success = $db->create_counter( $name )
Create and initialise to zero a new counter with $name.
remove_counters
$success = $db->remove_counters
Destroy all counters.
drop_counter
$success = $db->drop_counter( $name )
Destroy the counter named $name.
User Messages
save_user_message
$message = $db->save_user_message( $userid, $m_type, $dom_m_data )
Save user message provided in XML DOM object $dom_m_data as a sanitized string in a EPrints::DataObj::Message using $m_type to define the message type and $userid for the ID of the user whose message it is.
get_user_messages
@messages = $db->get_user_messages( $userid, %opts )
Get the messages for a user with ID $userid and clear messages if $opt{clear} is set.
clear_user_messages
$db->clear_user_messages( $userid )
Clear all messages for user with ID $userid.
SEE ALSO
To access database-stored objects use the methods provided by the following modules: EPrints::Repository, EPrints::DataSet.
COPYRIGHT
© Copyright 2000-2024 University of Southampton.
EPrints 3.4 is supplied by EPrints Services.
http://www.eprints.org/eprints-3.4/
LICENSE
This file is part of EPrints 3.4 http://www.eprints.org/.
EPrints 3.4 and this file are released under the terms of the GNU Lesser General Public License version 3 as published by the Free Software Foundation unless otherwise stated.
EPrints 3.4 is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details.
You should have received a copy of the GNU Lesser General Public License along with EPrints 3.4. If not, see http://www.gnu.org/licenses/.
