Difference between revisions of "API:EPrints/Database"

From EPrints Documentation
Jump to: navigation, search
Line 71: Line 71:
 
====new====
 
====new====
  
<source lang="perl">$db = EPrints::Database-&gt;new( $repo )</source>
+
<source lang="perl">$db = EPrints::Database->new( $repo )
 +
 
 +
</source>
 
Create a connection to the database.
 
Create a connection to the database.
  
Line 81: Line 83:
 
====create====
 
====create====
  
<source lang="perl">$db = $db-&gt;create( $username, $password )</source>
+
<source lang="perl">$db = $db->create( $username, $password )
 +
 
 +
</source>
 
Create and connect to a new database using super user account $username and $password.
 
Create and connect to a new database using super user account $username and $password.
  
Line 91: Line 95:
 
====connect====
 
====connect====
  
<source lang="perl">$ok = $db-&gt;connect</source>
+
<source lang="perl">$ok = $db->connect
 +
 
 +
</source>
 
Connects to the database.  
 
Connects to the database.  
  
Line 101: Line 107:
 
====disconnect====
 
====disconnect====
  
<source lang="perl">$db-&gt;disconnect</source>
+
<source lang="perl">$db->disconnect
 +
 
 +
</source>
 
Disconnects from the EPrints database.
 
Disconnects from the EPrints database.
  
Line 111: Line 119:
 
====set_debug====
 
====set_debug====
  
<source lang="perl">$db-&gt;set_debug( $boolean )</source>
+
<source lang="perl">$db->set_debug( $boolean )
 +
 
 +
</source>
 
Set the SQL debug mode to true or false.
 
Set the SQL debug mode to true or false.
  
Line 121: Line 131:
 
====set_version====
 
====set_version====
  
<source lang="perl">$db-&gt;set_version( $versionid );</source>
+
<source lang="perl">$db->set_version( $versionid );
 +
 
 +
</source>
 
Set the version id table in the SQL database to the given value (used by the upgrade script).
 
Set the version id table in the SQL database to the given value (used by the upgrade script).
  
Line 131: Line 143:
 
====get_version====
 
====get_version====
  
<source lang="perl">$version = $db-&gt;get_version</source>
+
<source lang="perl">$version = $db->get_version
 +
 
 +
</source>
 
Returns the current database schema version.
 
Returns the current database schema version.
  
Line 141: Line 155:
 
====is_latest_version====
 
====is_latest_version====
  
<source lang="perl">$boolean = $db-&gt;is_latest_version</source>
+
<source lang="perl">$boolean = $db->is_latest_version
 +
 
 +
</source>
 
Return true if the database schema is in the correct configuration for this version of eprints. Otherwise false.
 
Return true if the database schema is in the correct configuration for this version of eprints. Otherwise false.
  
Line 151: Line 167:
 
====get_server_version====
 
====get_server_version====
  
<source lang="perl">$version = $db-&gt;get_server_version</source>
+
<source lang="perl">$version = $db->get_server_version
 +
 
 +
</source>
 
Return the database server version.
 
Return the database server version.
  
Line 161: Line 179:
 
====get_default_charset====
 
====get_default_charset====
  
<source lang="perl">$charset = $db-&gt;get_default_charset( LANGUAGE )</source>
+
<source lang="perl">$charset = $db->get_default_charset( LANGUAGE )
 +
 
 +
</source>
 
Return the character set to use for LANGUAGE.
 
Return the character set to use for LANGUAGE.
  
Line 173: Line 193:
 
====get_default_collation====
 
====get_default_collation====
  
<source lang="perl">$collation = $db-&gt;get_default_collation( LANGUAGE )</source>
+
<source lang="perl">$collation = $db->get_default_collation( LANGUAGE )
 +
 
 +
</source>
 
Return the collation to use for LANGUAGE.
 
Return the collation to use for LANGUAGE.
  
Line 185: Line 207:
 
====get_driver_name====
 
====get_driver_name====
  
<source lang="perl">$driver = $db-&gt;get_driver_name</source>
+
<source lang="perl">$driver = $db->get_driver_name
 +
 
 +
</source>
 
Return the database driver name.
 
Return the database driver name.
  
Line 195: Line 219:
 
====error====
 
====error====
  
<source lang="perl">$errstr = $db-&gt;error</source>
+
<source lang="perl">$errstr = $db->error
 +
 
 +
</source>
 
Return a string describing the last SQL error.
 
Return a string describing the last SQL error.
  
Line 205: Line 231:
 
====retry_error====
 
====retry_error====
  
<source lang="perl">$bool = $db-&gt;retry_error</source>
+
<source lang="perl">$bool = $db->retry_error
 +
 
 +
</source>
 
Returns true if the current error is a retry error.
 
Returns true if the current error is a retry error.
  
Line 215: Line 243:
 
====duplicate_error====
 
====duplicate_error====
  
<source lang="perl">$bool = $db-&gt;duplicate_error</source>
+
<source lang="perl">$bool = $db->duplicate_error
 +
 
 +
</source>
 
Returns true if the current error is a PRIMARY KEY or UNIQUE error.
 
Returns true if the current error is a PRIMARY KEY or UNIQUE error.
  
Line 225: Line 255:
 
====begin====
 
====begin====
  
<source lang="perl">$db-&gt;begin</source>
+
<source lang="perl">$db->begin
 +
 
 +
</source>
 
Begin a transaction.
 
Begin a transaction.
  
Line 235: Line 267:
 
====commit====
 
====commit====
  
<source lang="perl">$db-&gt;commit</source>
+
<source lang="perl">$db->commit
 +
 
 +
</source>
 
Commit the previously begun transaction.
 
Commit the previously begun transaction.
  
Line 245: Line 279:
 
====rollback====
 
====rollback====
  
<source lang="perl">$db-&gt;rollback</source>
+
<source lang="perl">$db->rollback
 +
 
 +
</source>
 
Roll-back the current transaction.
 
Roll-back the current transaction.
  
Line 255: Line 291:
 
====type_info====
 
====type_info====
  
<source lang="perl">$type_info = $db-&gt;type_info( DATA_TYPE )</source>
+
<source lang="perl">$type_info = $db->type_info( DATA_TYPE )
 +
 
 +
</source>
 
See {{API:PodLink|file=DBI|package_name=DBI|section=type_info|text=DBI/type_info}}.
 
See {{API:PodLink|file=DBI|package_name=DBI|section=type_info|text=DBI/type_info}}.
  
Line 265: Line 303:
 
====get_column_type====
 
====get_column_type====
  
<source lang="perl">$real_type = $db-&gt;get_column_type( NAME, TYPE, NOT_NULL, [ LENGTH/PRECISION ], [ SCALE ], %opts )</source>
+
<source lang="perl">$real_type = $db->get_column_type( NAME, TYPE, NOT_NULL, [ LENGTH/PRECISION ], [ SCALE ], %opts )
 +
 
 +
</source>
 
Returns a SQL column definition for NAME of type TYPE, usually something like:
 
Returns a SQL column definition for NAME of type TYPE, usually something like:
  
Line 312: Line 352:
 
====create_archive_tables====
 
====create_archive_tables====
  
<source lang="perl">$success = $db-&gt;create_archive_tables</source>
+
<source lang="perl">$success = $db->create_archive_tables
 +
 
 +
</source>
 
Create all the SQL tables for each dataset.
 
Create all the SQL tables for each dataset.
  
Line 322: Line 364:
 
====drop_archive_tables====
 
====drop_archive_tables====
  
<source lang="perl">$db-&gt;drop_archive_tables()</source>
+
<source lang="perl">$db->drop_archive_tables()
 +
 
 +
</source>
 
Destroy all tables used by eprints in the database.
 
Destroy all tables used by eprints in the database.
  
Line 332: Line 376:
 
====create_version_table====
 
====create_version_table====
  
<source lang="perl">$db-&gt;create_version_table</source>
+
<source lang="perl">$db->create_version_table
 +
 
 +
</source>
 
Make the version table (and set the only value to be the current version of eprints).
 
Make the version table (and set the only value to be the current version of eprints).
  
Line 342: Line 388:
 
====drop_version_table====
 
====drop_version_table====
  
<source lang="perl">$db-&gt;drop_version_table</source>
+
<source lang="perl">$db->drop_version_table
 +
 
 +
</source>
 
Drop the version table.
 
Drop the version table.
  
Line 352: Line 400:
 
====create_dataset_tables====
 
====create_dataset_tables====
  
<source lang="perl">$success = $db-&gt;create_dataset_tables( $dataset )</source>
+
<source lang="perl">$success = $db->create_dataset_tables( $dataset )
 +
 
 +
</source>
 
Create all the SQL tables for a single dataset.
 
Create all the SQL tables for a single dataset.
  
Line 362: Line 412:
 
====drop_dataset_tables====
 
====drop_dataset_tables====
  
<source lang="perl">$db-&gt;drop_dataset_tables( $dataset )</source>
+
<source lang="perl">$db->drop_dataset_tables( $dataset )
 +
 
 +
</source>
 
Drop all the SQL tables for a single dataset.
 
Drop all the SQL tables for a single dataset.
  
Line 372: Line 424:
 
====create_dataset_index_tables====
 
====create_dataset_index_tables====
  
<source lang="perl">$success = $db-&gt;create_dataset_index_tables( $dataset )</source>
+
<source lang="perl">$success = $db->create_dataset_index_tables( $dataset )
 +
 
 +
</source>
 
Create all the index tables for a single dataset.
 
Create all the index tables for a single dataset.
  
Line 382: Line 436:
 
====create_dataset_ordervalues_tables====
 
====create_dataset_ordervalues_tables====
  
<source lang="perl">$success = $db-&gt;create_dataset_ordervalues_tables( $dataset )</source>
+
<source lang="perl">$success = $db->create_dataset_ordervalues_tables( $dataset )
 +
 
 +
</source>
 
Create all the ordervalues tables for a single dataset.
 
Create all the ordervalues tables for a single dataset.
  
Line 392: Line 448:
 
====has_dataset====
 
====has_dataset====
  
<source lang="perl">$db-&gt;has_dataset( $dataset )</source>
+
<source lang="perl">$db->has_dataset( $dataset )
 +
 
 +
</source>
 
Returns true if $dataset exists in the database or has no database tables.
 
Returns true if $dataset exists in the database or has no database tables.
  
Line 404: Line 462:
 
====has_field====
 
====has_field====
  
<source lang="perl">$db-&gt;has_field( $dataset, $field )</source>
+
<source lang="perl">$db->has_field( $dataset, $field )
 +
 
 +
</source>
 
Returns true if $field is in the database for $dataset.
 
Returns true if $field is in the database for $dataset.
  
Line 414: Line 474:
 
====add_field====
 
====add_field====
  
<source lang="perl">$db-&gt;add_field( $dataset, $field [, $force ] )</source>
+
<source lang="perl">$db->add_field( $dataset, $field [, $force ] )
 +
 
 +
</source>
 
Add $field to $dataset's tables.
 
Add $field to $dataset's tables.
  
Line 426: Line 488:
 
====remove_field====
 
====remove_field====
  
<source lang="perl">$db-&gt;remove_field( $dataset, $field )</source>
+
<source lang="perl">$db->remove_field( $dataset, $field )
 +
 
 +
</source>
 
Remove $field from $dataset's tables.
 
Remove $field from $dataset's tables.
  
Line 436: Line 500:
 
====rename_field====
 
====rename_field====
  
<source lang="perl">$ok = $db-&gt;rename_field( $dataset, $field, $old_name )</source>
+
<source lang="perl">$ok = $db->rename_field( $dataset, $field, $old_name )
 +
 
 +
</source>
 
Rename a $field in the database from it's old name $old_name.
 
Rename a $field in the database from it's old name $old_name.
  
Line 448: Line 514:
 
====create_table====
 
====create_table====
  
<source lang="perl">$success = $db-&gt;create_table( $tablename, $setkey, @fields );</source>
+
<source lang="perl">$success = $db->create_table( $tablename, $setkey, @fields );
 +
 
 +
</source>
 
Creates a new table $tablename based on @fields.
 
Creates a new table $tablename based on @fields.
  
Line 460: Line 528:
 
====create_foreign_key====
 
====create_foreign_key====
  
<source lang="perl">$ok = $db-&gt;create_foreign_key( $main_table, $aux_table, $key_field )</source>
+
<source lang="perl">$ok = $db->create_foreign_key( $main_table, $aux_table, $key_field )
 +
 
 +
</source>
 
Create a foreign key relationship between $main_table and $aux_table using the $key_field.
 
Create a foreign key relationship between $main_table and $aux_table using the $key_field.
  
Line 472: Line 542:
 
====create_counters====
 
====create_counters====
  
<source lang="perl">$success = $db-&gt;create_counters</source>
+
<source lang="perl">$success = $db->create_counters
 +
 
 +
</source>
 
Create the counters used to store the highest current id of eprints, users etc.
 
Create the counters used to store the highest current id of eprints, users etc.
  
Line 482: Line 554:
 
====has_counter====
 
====has_counter====
  
<source lang="perl">$success = $db-&gt;has_counter( $counter )</source>
+
<source lang="perl">$success = $db->has_counter( $counter )
 +
 
 +
</source>
 
Returns true if $counter exists.
 
Returns true if $counter exists.
  
Line 492: Line 566:
 
====create_counter====
 
====create_counter====
  
<source lang="perl">$success = $db-&gt;create_counter( $name )</source>
+
<source lang="perl">$success = $db->create_counter( $name )
 +
 
 +
</source>
 
Create and initialise to zero a new counter called $name.
 
Create and initialise to zero a new counter called $name.
  
Line 502: Line 578:
 
====remove_counters====
 
====remove_counters====
  
<source lang="perl">$success = $db-&gt;remove_counters</source>
+
<source lang="perl">$success = $db->remove_counters
 +
 
 +
</source>
 
Destroy all counters.
 
Destroy all counters.
  
Line 512: Line 590:
 
====drop_counter====
 
====drop_counter====
  
<source lang="perl">$success = $db-&gt;drop_counter( $name )</source>
+
<source lang="perl">$success = $db->drop_counter( $name )
 +
 
 +
</source>
 
Destroy the counter named $name.
 
Destroy the counter named $name.
  
Line 522: Line 602:
 
====counter_current====
 
====counter_current====
  
<source lang="perl">$n = $db-&gt;counter_current( $counter )</source>
+
<source lang="perl">$n = $db->counter_current( $counter )
 +
 
 +
</source>
 
Return the value of the previous counter_next on $counter.
 
Return the value of the previous counter_next on $counter.
  
Line 532: Line 614:
 
====counter_next====
 
====counter_next====
  
<source lang="perl">$n = $db-&gt;counter_next( $counter )</source>
+
<source lang="perl">$n = $db->counter_next( $counter )
 +
 
 +
</source>
 
Return the next unused value for the named counter. Returns undef if  the counter doesn't exist.
 
Return the next unused value for the named counter. Returns undef if  the counter doesn't exist.
  
Line 542: Line 626:
 
====counter_minimum====
 
====counter_minimum====
  
<source lang="perl">$db-&gt;counter_minimum( $counter, $value )</source>
+
<source lang="perl">$db->counter_minimum( $counter, $value )
 +
 
 +
</source>
 
Ensure that the counter is set no lower than $value. This is used when importing eprints which may not be in scrict sequence.
 
Ensure that the counter is set no lower than $value. This is used when importing eprints which may not be in scrict sequence.
  
Line 552: Line 638:
 
====counter_reset====
 
====counter_reset====
  
<source lang="perl">$db-&gt;counter_reset( $counter )</source>
+
<source lang="perl">$db->counter_reset( $counter )
 +
 
 +
</source>
 
Reset the counter. Use with caution.
 
Reset the counter. Use with caution.
  
Line 562: Line 650:
 
====next_doc_pos====
 
====next_doc_pos====
  
<source lang="perl">$n = $db-&gt;next_doc_pos( $eprintid )</source>
+
<source lang="perl">$n = $db->next_doc_pos( $eprintid )
 +
 
 +
</source>
 
Return the next unused document pos for the given eprintid.
 
Return the next unused document pos for the given eprintid.
  
Line 578: Line 668:
 
====do====
 
====do====
  
<source lang="perl">$success = $db-&gt;do( $sql )</source>
+
<source lang="perl">$success = $db->do( $sql )
 +
 
 +
</source>
 
Execute the given SQL.
 
Execute the given SQL.
  
Line 588: Line 680:
 
====prepare====
 
====prepare====
  
<source lang="perl">$sth = $db-&gt;prepare( $sql )</source>
+
<source lang="perl">$sth = $db->prepare( $sql )
 +
 
 +
</source>
 
Prepare the SQL statement $sql for execution.
 
Prepare the SQL statement $sql for execution.
  
Line 603: Line 697:
 
====prepare_select====
 
====prepare_select====
  
<source lang="perl">$sth = $db-&gt;prepare_select( $sql [, %options ] )</source>
+
<source lang="perl">$sth = $db->prepare_select( $sql [, %options ] )
 +
 
 +
</source>
 
Prepare a SELECT statement $sql for execution.
 
Prepare a SELECT statement $sql for execution.
  
Line 622: Line 718:
 
====has_sequence====
 
====has_sequence====
  
<source lang="perl">$boolean = $db-&gt;has_sequence( $name )</source>
+
<source lang="perl">$boolean = $db->has_sequence( $name )
 +
 
 +
</source>
 
Return true if a sequence of the given name exists in the database.
 
Return true if a sequence of the given name exists in the database.
  
Line 632: Line 730:
 
====create_sequence====
 
====create_sequence====
  
<source lang="perl">$success = $db-&gt;create_sequence( $seq_name )</source>
+
<source lang="perl">$success = $db->create_sequence( $seq_name )
 +
 
 +
</source>
 
Creates a new sequence object initialised to zero.
 
Creates a new sequence object initialised to zero.
  
Line 642: Line 742:
 
====drop_sequence====
 
====drop_sequence====
  
<source lang="perl">$success = $db-&gt;drop_sequence( $seq_name )</source>
+
<source lang="perl">$success = $db->drop_sequence( $seq_name )
 +
 
 +
</source>
 
Deletes a sequence object.
 
Deletes a sequence object.
  
Line 652: Line 754:
 
====drop_column====
 
====drop_column====
  
<source lang="perl">$success = $db-&gt;drop_column( $table, $column )</source>
+
<source lang="perl">$success = $db->drop_column( $table, $column )
 +
 
 +
</source>
 
Drops a column from a table.
 
Drops a column from a table.
  
Line 662: Line 766:
 
====get_primary_key====
 
====get_primary_key====
  
<source lang="perl">@columns = $db-&gt;get_primary_key( $tablename )</source>
+
<source lang="perl">@columns = $db->get_primary_key( $tablename )
 +
 
 +
</source>
 
Returns the list of column names that comprise the primary key for $tablename.
 
Returns the list of column names that comprise the primary key for $tablename.
  
Line 674: Line 780:
 
====create_primary_key====
 
====create_primary_key====
  
<source lang="perl">$db-&gt;create_primary_key( $tablename, @cols )</source>
+
<source lang="perl">$db->create_primary_key( $tablename, @cols )
 +
 
 +
</source>
 
Create a PRIMARY KEY on $tablename over @cols.
 
Create a PRIMARY KEY on $tablename over @cols.
  
Line 684: Line 792:
 
====create_index====
 
====create_index====
  
<source lang="perl">$success = $db-&gt;create_index( $tablename, @columns )</source>
+
<source lang="perl">$success = $db->create_index( $tablename, @columns )
 +
 
 +
</source>
 
Creates an index over @columns for $tablename. Returns true on success.
 
Creates an index over @columns for $tablename. Returns true on success.
  
Line 694: Line 804:
 
====create_unique_index====
 
====create_unique_index====
  
<source lang="perl">$success = $db-&gt;create_unique_index( $tablename, @columns )</source>
+
<source lang="perl">$success = $db->create_unique_index( $tablename, @columns )
 +
 
 +
</source>
 
Creates a unique index over @columns for $tablename. Returns true on success.
 
Creates a unique index over @columns for $tablename. Returns true on success.
  
Line 704: Line 816:
 
====index_name====
 
====index_name====
  
<source lang="perl">$name = $db-&gt;index_name( $table, @columns )</source>
+
<source lang="perl">$name = $db->index_name( $table, @columns )
 +
 
 +
</source>
 
Returns the name of the first index that starts with @columns on the $table table.
 
Returns the name of the first index that starts with @columns on the $table table.
  
Line 716: Line 830:
 
====_update====
 
====_update====
  
<source lang="perl">$rows = $db-&gt;_update( $tablename, $keycols, $keyvals, $columns, @values )</source>
+
<source lang="perl">$rows = $db->_update( $tablename, $keycols, $keyvals, $columns, @values )
 +
 
 +
</source>
 
UPDATES $tablename where $keycols equals $keyvals and returns the number of rows affected.
 
UPDATES $tablename where $keycols equals $keyvals and returns the number of rows affected.
  
Line 730: Line 846:
 
====insert====
 
====insert====
  
<source lang="perl">$success = $db-&gt;insert( $table, $columns, @values )</source>
+
<source lang="perl">$success = $db->insert( $table, $columns, @values )
 +
 
 +
</source>
 
Inserts values into the table $table. If $columns is defined it will be used as a list of columns to insert into. @values is a list of arrays containing values to insert.
 
Inserts values into the table $table. If $columns is defined it will be used as a list of columns to insert into. @values is a list of arrays containing values to insert.
  
Line 742: Line 860:
 
====insert_quoted====
 
====insert_quoted====
  
<source lang="perl">$success = $db-&gt;insert_quoted( $table, $columns, @qvalues )</source>
+
<source lang="perl">$success = $db->insert_quoted( $table, $columns, @qvalues )
 +
 
 +
</source>
 
Inserts values into the table $table. If $columns is defined it will be used as a list of columns to insert into. @qvalues is a list of arrays containing values to insert.
 
Inserts values into the table $table. If $columns is defined it will be used as a list of columns to insert into. @qvalues is a list of arrays containing values to insert.
  
Line 754: Line 874:
 
====delete_from====
 
====delete_from====
  
<source lang="perl">$success = $db-&gt;delete_from( $table, $columns, @values )</source>
+
<source lang="perl">$success = $db->delete_from( $table, $columns, @values )
 +
 
 +
</source>
 
Perform a SQL DELETE FROM $table using $columns to build a where clause. @values is a list of array references of values in the same order as $columns.
 
Perform a SQL DELETE FROM $table using $columns to build a where clause. @values is a list of array references of values in the same order as $columns.
  
Line 766: Line 888:
 
====count_table====
 
====count_table====
  
<source lang="perl">$n = $db-&gt;count_table( $tablename )</source>
+
<source lang="perl">$n = $db->count_table( $tablename )
 +
 
 +
</source>
 
Return the number of rows in the specified SQL table.
 
Return the number of rows in the specified SQL table.
  
Line 776: Line 900:
 
====has_table====
 
====has_table====
  
<source lang="perl">$boolean = $db-&gt;has_table( $tablename )</source>
+
<source lang="perl">$boolean = $db->has_table( $tablename )
 +
 
 +
</source>
 
Return true if a table of the given name exists in the database.
 
Return true if a table of the given name exists in the database.
  
Line 786: Line 912:
 
====has_column====
 
====has_column====
  
<source lang="perl">$boolean = $db-&gt;has_column( $tablename, $columnname )</source>
+
<source lang="perl">$boolean = $db->has_column( $tablename, $columnname )
 +
 
 +
</source>
 
Return true if the a table of the given name has a column named $columnname in the database.
 
Return true if the a table of the given name has a column named $columnname in the database.
  
Line 796: Line 924:
 
====drop_table====
 
====drop_table====
  
<source lang="perl">$db-&gt;drop_table( $tablename [, $tablename2 ] )</source>
+
<source lang="perl">$db->drop_table( $tablename [, $tablename2 ] )
 +
 
 +
</source>
 
Delete the named table(s). Use with caution!
 
Delete the named table(s). Use with caution!
  
Line 806: Line 936:
 
====clear_table====
 
====clear_table====
  
<source lang="perl">$db-&gt;clear_table( $tablename )</source>
+
<source lang="perl">$db->clear_table( $tablename )
 +
 
 +
</source>
 
Clears all records from the given table, use with caution!
 
Clears all records from the given table, use with caution!
  
Line 816: Line 948:
 
====rename_table====
 
====rename_table====
  
<source lang="perl">$db-&gt;rename_table( $tablename, $newtablename )</source>
+
<source lang="perl">$db->rename_table( $tablename, $newtablename )
 +
 
 +
</source>
 
Renames the table from the old name to the new one.
 
Renames the table from the old name to the new one.
  
Line 826: Line 960:
 
====swap_table====
 
====swap_table====
  
<source lang="perl">$db-&gt;swap_table( $table_a, $table_b )</source>
+
<source lang="perl">$db->swap_table( $table_a, $table_b )
 +
 
 +
</source>
 
Swap table a and table b.  
 
Swap table a and table b.  
  
Line 836: Line 972:
 
====get_tables====
 
====get_tables====
  
<source lang="perl">@tables = $db-&gt;get_tables</source>
+
<source lang="perl">@tables = $db->get_tables
 +
 
 +
</source>
 
Return a list of all the tables in the database.
 
Return a list of all the tables in the database.
  
Line 852: Line 990:
 
====prep_int====
 
====prep_int====
  
<source lang="perl">$mungedvalue = EPrints::Database::prep_int( $value )</source>
+
<source lang="perl">$mungedvalue = EPrints::Database::prep_int( $value )
 +
 
 +
</source>
 
Escape a numerical value for SQL. undef becomes NULL. Anything else becomes a number (zero if needed).
 
Escape a numerical value for SQL. undef becomes NULL. Anything else becomes a number (zero if needed).
  
Line 862: Line 1,002:
 
====prep_value====
 
====prep_value====
  
<source lang="perl">$mungedvalue = EPrints::Database::prep_value( $value )</source>
+
<source lang="perl">$mungedvalue = EPrints::Database::prep_value( $value )
 +
 
 +
</source>
 
Escape a value for SQL. Modify value such that " becomes \" and \  becomes \\ and ' becomes \'
 
Escape a value for SQL. Modify value such that " becomes \" and \  becomes \\ and ' becomes \'
  
Line 872: Line 1,014:
 
====prep_like_value====
 
====prep_like_value====
  
<source lang="perl">$mungedvalue = EPrints::Database::prep_like_value( $value )</source>
+
<source lang="perl">$mungedvalue = EPrints::Database::prep_like_value( $value )
 +
 
 +
</source>
 
Escape an value for an SQL like field. In addition to ' " and \ also  escapes % and _
 
Escape an value for an SQL like field. In addition to ' " and \ also  escapes % and _
  
Line 882: Line 1,026:
 
====quote_value====
 
====quote_value====
  
<source lang="perl">$str = $db-&gt;quote_value( $value )</source>
+
<source lang="perl">$str = $db->quote_value( $value )
 +
 
 +
</source>
 
Return a quoted value. To quote a 'like' value you should do:
 
Return a quoted value. To quote a 'like' value you should do:
  
Line 894: Line 1,040:
 
====quote_int====
 
====quote_int====
  
<source lang="perl">$str = $db-&gt;quote_int( $value )</source>
+
<source lang="perl">$str = $db->quote_int( $value )
 +
 
 +
</source>
 
Return a quoted integer value
 
Return a quoted integer value
  
Line 904: Line 1,052:
 
====quote_binary====
 
====quote_binary====
  
<source lang="perl">$str = $db-&gt;quote_binary( $bytes )</source>
+
<source lang="perl">$str = $db->quote_binary( $bytes )
 +
 
 +
</source>
 
Some databases (Oracle/PostgreSQL) require transforms of binary data to work correctly.
 
Some databases (Oracle/PostgreSQL) require transforms of binary data to work correctly.
  
Line 916: Line 1,066:
 
====quote_ordervalue====
 
====quote_ordervalue====
  
<source lang="perl">$str = $db-&gt;quote_ordervalue( $field, $value )</source>
+
<source lang="perl">$str = $db->quote_ordervalue( $field, $value )
 +
 
 +
</source>
 
Some databases (Oracle) can't order by CLOBS so need special treatment when creating the ordervalues tables. This method allows any fixing-up required for string data before it's inserted.
 
Some databases (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.
  
Line 926: Line 1,078:
 
====quote_identifier====
 
====quote_identifier====
  
<source lang="perl">$str = $db-&gt;quote_identifier( @parts )</source>
+
<source lang="perl">$str = $db->quote_identifier( @parts )
 +
 
 +
</source>
 
Quote a database identifier (e.g. table names). Multiple @parts will be joined by dot.
 
Quote a database identifier (e.g. table names). Multiple @parts will be joined by dot.
  
Line 936: Line 1,090:
 
====prepare_regexp====
 
====prepare_regexp====
  
<source lang="perl">$sql = $db-&gt;prepare_regexp( $quoted_column, $quoted_value )</source>
+
<source lang="perl">$sql = $db->prepare_regexp( $quoted_column, $quoted_value )
 +
 
 +
</source>
 
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).
 
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).
  
Line 946: Line 1,102:
 
====sql_as====
 
====sql_as====
  
<source lang="perl">$sql = $db-&gt;sql_AS()</source>
+
<source lang="perl">$sql = $db->sql_AS()
 +
 
 +
</source>
 
Returns the syntactic glue to use when aliasing. SQL 92 DBs will happilly use " AS " but some DBs (Oracle!) won't accept it.
 
Returns the syntactic glue to use when aliasing. SQL 92 DBs will happilly use " AS " but some DBs (Oracle!) won't accept it.
  
Line 956: Line 1,114:
 
====sql_like====
 
====sql_like====
  
<source lang="perl">$sql = $db-&gt;sql_LIKE()</source>
+
<source lang="perl">$sql = $db->sql_LIKE()
 +
 
 +
</source>
 
Returns the syntactic glue to use when making a case-insensitive LIKE. PostgreSQL requires "ILIKE" while everything else uses "LIKE" and the column collation.
 
Returns the syntactic glue to use when making a case-insensitive LIKE. PostgreSQL requires "ILIKE" while everything else uses "LIKE" and the column collation.
  
Line 972: Line 1,132:
 
====insert_data====
 
====insert_data====
  
<source lang="perl">$success = $db-&gt;insert_data( $dataset, $data )</source>
+
<source lang="perl">$success = $db->insert_data( $dataset, $data )
 +
 
 +
</source>
 
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.
 
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.
  
Line 986: Line 1,148:
 
====update_data====
 
====update_data====
  
<source lang="perl">$success = $db-&gt;update_data( $dataset, $data, $changed )</source>
+
<source lang="perl">$success = $db->update_data( $dataset, $data, $changed )
 +
 
 +
</source>
 
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.
 
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.
  
Line 1,000: Line 1,164:
 
====exists====
 
====exists====
  
<source lang="perl">$boolean = $db-&gt;exists( $dataset, $id )</source>
+
<source lang="perl">$boolean = $db->exists( $dataset, $id )
 +
 
 +
</source>
 
Return true if a record with the given primary key exists in the dataset, otherwise false.
 
Return true if a record with the given primary key exists in the dataset, otherwise false.
  
Line 1,010: Line 1,176:
 
====remove====
 
====remove====
  
<source lang="perl">$success = $db-&gt;remove( $dataset, $id )</source>
+
<source lang="perl">$success = $db->remove( $dataset, $id )
 +
 
 +
</source>
 
Remove the record, index terms and order value with the key field value $id from the specified dataset.
 
Remove the record, index terms and order value with the key field value $id from the specified dataset.
  
Line 1,028: Line 1,196:
 
====cache_exp====
 
====cache_exp====
  
<source lang="perl">$searchexp = $db-&gt;cache_exp( $cacheid )</source>
+
<source lang="perl">$searchexp = $db->cache_exp( $cacheid )
 +
 
 +
</source>
 
Return the serialised Search of a the cached search with id $cacheid. Return undef if the id is invalid or expired.
 
Return the serialised Search of a the cached search with id $cacheid. Return undef if the id is invalid or expired.
  
Line 1,038: Line 1,208:
 
====cache====
 
====cache====
  
<source lang="perl">$cacheid = $db-&gt;cache( $searchexp, $dataset, $srctable, [$order], [$list] )</source>
+
<source lang="perl">$cacheid = $db->cache( $searchexp, $dataset, $srctable, [$order], [$list] )
 +
 
 +
</source>
 
Create a cache of the specified search expression from the SQL table $srctable.
 
Create a cache of the specified search expression from the SQL table $srctable.
  
Line 1,054: Line 1,226:
 
====cache_table====
 
====cache_table====
  
<source lang="perl">$tablename = $db-&gt;cache_table( $id )</source>
+
<source lang="perl">$tablename = $db->cache_table( $id )
 +
 
 +
</source>
 
Return the SQL table used to store the cache with id $id.
 
Return the SQL table used to store the cache with id $id.
  
Line 1,064: Line 1,238:
 
====search====
 
====search====
  
<source lang="perl">$ids = $db-&gt;search( $keyfield, $tables, $conditions, [$main_table_alias] )</source>
+
<source lang="perl">$ids = $db->search( $keyfield, $tables, $conditions, [$main_table_alias] )
 +
 
 +
</source>
 
Return a reference to an array of ids - the results of the search specified by $conditions accross the tables specified in the $tables hash where keys are tables aliases and values are table names.  
 
Return a reference to an array of ids - the results of the search specified by $conditions accross the tables specified in the $tables hash where keys are tables aliases and values are table names.  
  
Line 1,076: Line 1,252:
 
====drop_cache====
 
====drop_cache====
  
<source lang="perl">$db-&gt;drop_cache( $id )</source>
+
<source lang="perl">$db->drop_cache( $id )
 +
 
 +
</source>
 
Remove the cached search with the given id.
 
Remove the cached search with the given id.
  
Line 1,086: Line 1,264:
 
====from_cache====
 
====from_cache====
  
<source lang="perl">$foo = $db-&gt;from_cache( $dataset, $cacheid, [$offset], [$count], [$justids] )</source>
+
<source lang="perl">$foo = $db->from_cache( $dataset, $cacheid, [$offset], [$count], [$justids] )
 +
 
 +
</source>
 
Return a reference to an array containing all the items from the given dataset that have id's in the specified cache. The cache may be  specified either by id or serialised search expression.  
 
Return a reference to an array containing all the items from the given dataset that have id's in the specified cache. The cache may be  specified either by id or serialised search expression.  
  
Line 1,100: Line 1,280:
 
====drop_orphan_cache_tables====
 
====drop_orphan_cache_tables====
  
<source lang="perl">$c = $db-&gt;drop_orphan_cache_tables</source>
+
<source lang="perl">$c = $db->drop_orphan_cache_tables
 +
 
 +
</source>
 
Drop tables called "cacheXXX" where XXX is an integer. Returns the number of tables dropped.
 
Drop tables called "cacheXXX" where XXX is an integer. Returns the number of tables dropped.
  
Line 1,110: Line 1,292:
 
====get_single====
 
====get_single====
  
<source lang="perl">$obj = $db-&gt;get_single( $dataset, $id )</source>
+
<source lang="perl">$obj = $db->get_single( $dataset, $id )
 +
 
 +
</source>
 
Return a single item from the given dataset. The one with the specified id.
 
Return a single item from the given dataset. The one with the specified id.
  
Line 1,120: Line 1,304:
 
====get_all====
 
====get_all====
  
<source lang="perl">$items = $db-&gt;get_all( $dataset )</source>
+
<source lang="perl">$items = $db->get_all( $dataset )
 +
 
 +
</source>
 
Returns a reference to an array with all the items from the given dataset.
 
Returns a reference to an array with all the items from the given dataset.
  
Line 1,130: Line 1,316:
 
====get_cache_ids====
 
====get_cache_ids====
  
<source lang="perl">@ids = $db-&gt;get_cache_ids( $dataset, $cachemap, $offset, $count )</source>
+
<source lang="perl">@ids = $db->get_cache_ids( $dataset, $cachemap, $offset, $count )
 +
 
 +
</source>
 
Returns a list of $count ids from $cache_id starting at $offset and in the order in the cachemap.
 
Returns a list of $count ids from $cache_id starting at $offset and in the order in the cachemap.
  
Line 1,140: Line 1,328:
 
====get_dataobjs====
 
====get_dataobjs====
  
<source lang="perl">@dataobjs = $db-&gt;get_dataobjs( $dataset [, $id [, $id ] ] )</source>
+
<source lang="perl">@dataobjs = $db->get_dataobjs( $dataset [, $id [, $id ] ] )
 +
 
 +
</source>
 
Retrieves the records in $dataset with the given $id(s). If an $id doesn't exist in the database it will be ignored.
 
Retrieves the records in $dataset with the given $id(s). If an $id doesn't exist in the database it will be ignored.
  
Line 1,150: Line 1,340:
 
====get_values====
 
====get_values====
  
<source lang="perl">$foo = $db-&gt;get_values( $field, $dataset )</source>
+
<source lang="perl">$foo = $db->get_values( $field, $dataset )
 +
 
 +
</source>
 
Return a reference to an array of all the distinct values of the  [[API:EPrints/MetaField|EPrints::MetaField]] specified.
 
Return a reference to an array of all the distinct values of the  [[API:EPrints/MetaField|EPrints::MetaField]] specified.
  
Line 1,160: Line 1,352:
 
====get_ids_by_field_values====
 
====get_ids_by_field_values====
  
<source lang="perl">$ids = $db-&gt;get_ids_by_field_values( $field, $dataset [ %opts ] )</source>
+
<source lang="perl">$ids = $db->get_ids_by_field_values( $field, $dataset [ %opts ] )
 +
 
 +
</source>
 
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.
 
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.
  
Line 1,170: Line 1,364:
 
====dequeue_events====
 
====dequeue_events====
  
<source lang="perl">@events = $db-&gt;dequeue_events( $n )</source>
+
<source lang="perl">@events = $db->dequeue_events( $n )
 +
 
 +
</source>
 
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.
 
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.
  
Line 1,186: Line 1,382:
 
====valid_login====
 
====valid_login====
  
<source lang="perl">$real_username = $db-&gt;valid_login( $username, $password )</source>
+
<source lang="perl">$real_username = $db->valid_login( $username, $password )
 +
 
 +
</source>
 
Tests whether the clear-text $password matches the stored encrypted password for $username.
 
Tests whether the clear-text $password matches the stored encrypted password for $username.
  

Revision as of 08:59, 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.3, 3.2) | Revision Log | Before editing this page please read Pod2Wiki


Contents

NAME

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


SYNOPSIS

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


DESCRIPTION

EPrints Database Access Module

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

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


Cross-database Support

Any use of SQL statements must use quote_identifier to quote database tables and columns and quote_value to quote values. The only exception to this are the EPrints::Database::* modules which provide database-driver specific extensions.


Quoting SQL Values

By convention variables that contain already quoted values are prefixed with 'Q_' so they can be easily recognised when used in string interpolation:

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

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


METHODS

Database

new

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

Create a connection to the database.


create

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

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


connect

$ok = $db->connect

Connects to the database.


disconnect

$db->disconnect

Disconnects from the EPrints database.


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 value (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 database schema is in the correct configuration for this version 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( LANGUAGE )

Return the character set to use for LANGUAGE.

Returns undef if character sets are unsupported.


get_default_collation

$collation = $db->get_default_collation( LANGUAGE )

Return the collation to use for LANGUAGE.

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

$bool = $db->retry_error

Returns true if the current error is a retry error.


duplicate_error

$bool = $db->duplicate_error

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


begin

$db->begin

Begin a transaction.


commit

$db->commit

Commit the previously begun transaction.


rollback

$db->rollback

Roll-back the current transaction.


type_info

$type_info = $db->type_info( DATA_TYPE )

See DBI/type_info.


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.


Schema Manipulation

create_archive_tables

$success = $db->create_archive_tables

Create all the SQL tables for each dataset.


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.


create_dataset_tables

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

Create all the SQL tables for a single dataset.


drop_dataset_tables

$db->drop_dataset_tables( $dataset )

Drop all the SQL tables for a single dataset.


create_dataset_index_tables

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

Create all the index tables for a single dataset.


create_dataset_ordervalues_tables

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

Create all the ordervalues tables for a single dataset.


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


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 will 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 a $field in the database from it's old name $old_name.

Returns true if the field was successfully renamed.


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.


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.


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


remove_counters

$success = $db->remove_counters

Destroy all counters.


drop_counter

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

Destroy the counter named $name.


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 pos for the given eprintid.


SQL utility methods

do

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

Execute the given SQL.


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;
 


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
 


has_sequence

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

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


create_sequence

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

Creates a new sequence object initialised to zero.


drop_sequence

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

Deletes a sequence object.


drop_column

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

Drops a column from a table.


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.


create_primary_key

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

Create a PRIMARY KEY on $tablename over @cols.


create_index

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

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


create_unique_index

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

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


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.


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


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.


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!


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.


has_table

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

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


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.


drop_table

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

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


clear_table

$db->clear_table( $tablename )

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


rename_table

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

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


swap_table

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

Swap table a and table b.


get_tables

@tables = $db->get_tables

Return a list of all the tables in the database.


Quoting Values

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


prep_value

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

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


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 _


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


quote_int

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

Return a quoted integer 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 containing nul 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 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 dot.


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


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.


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.


Dataset Data

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.


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.


exists

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

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


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.


Searching, Caching and Retrieval

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.


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 SQL table used to store the cache with id $id.


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.


drop_cache

$db->drop_cache( $id )

Remove the cached search with the given id.


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.


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.


get_single

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

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


get_all

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

Returns a reference to an array with all the items 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

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

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


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.


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.


Password Validation

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.


SEE ALSO

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


COPYRIGHT