Difference between revisions of "API:EPrints/Database"
| Line 38: | Line 38: | ||
Variables that are database quoted are prefixed with 'Q_'. | 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_methods --> | ||
| + | ==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 46: | Line 55: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_build_connection_string --> | <!-- Pod2Wiki=item_build_connection_string --> | ||
| − | === | + | ===build_connection_string=== |
| + | $dbstr = EPrints::Database::build_connection_string( %params ) | ||
Build the string to use to connect to the database via DBI. %params must contain dbname, and may also contain dbport, dbhost and dbsock. | Build the string to use to connect to the database via DBI. %params must contain dbname, and may also contain dbport, dbhost and dbsock. | ||
| Line 58: | Line 68: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_create --> | <!-- Pod2Wiki=item_create --> | ||
| − | === | + | ===create=== |
| + | $db = $db->create( $username, $password ) | ||
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 70: | Line 81: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_new --> | <!-- Pod2Wiki=item_new --> | ||
| − | === | + | ===new=== |
| + | $db = EPrints::Database->new( $session ) | ||
Create a connection to the database. | Create a connection to the database. | ||
| Line 82: | Line 94: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_connect --> | <!-- Pod2Wiki=item_connect --> | ||
| − | === | + | ===connect=== |
| + | $foo = $db->connect | ||
Connects to the database. | Connects to the database. | ||
| Line 94: | Line 107: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_disconnect --> | <!-- Pod2Wiki=item_disconnect --> | ||
| − | === | + | ===disconnect=== |
| + | $foo = $db->disconnect | ||
Disconnects from the EPrints database. Should always be done before any script exits. | Disconnects from the EPrints database. Should always be done before any script exits. | ||
| Line 106: | Line 120: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_error --> | <!-- Pod2Wiki=item_error --> | ||
| − | === | + | ===error=== |
| + | $errstr = $db->error | ||
Return a string describing the last SQL error. | Return a string describing the last SQL error. | ||
| Line 118: | Line 133: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_begin --> | <!-- Pod2Wiki=item_begin --> | ||
| − | === | + | ===begin=== |
| + | $db->begin | ||
Begin a transaction. | Begin a transaction. | ||
| Line 130: | Line 146: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_commit --> | <!-- Pod2Wiki=item_commit --> | ||
| − | === | + | ===commit=== |
| + | $db->commit | ||
Commit the previous begun transaction. | Commit the previous begun transaction. | ||
| Line 142: | Line 159: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_rollback --> | <!-- Pod2Wiki=item_rollback --> | ||
| − | === | + | ===rollback=== |
| + | $db->rollback | ||
Rollback the partially completed transaction. | Rollback the partially completed transaction. | ||
| Line 154: | Line 172: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_create_archive_tables --> | <!-- Pod2Wiki=item_create_archive_tables --> | ||
| − | === | + | ===create_archive_tables=== |
| + | $success = $db->create_archive_tables | ||
Create all the SQL tables for each dataset. | Create all the SQL tables for each dataset. | ||
| Line 166: | Line 185: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_drop_archive_tables --> | <!-- Pod2Wiki=item_drop_archive_tables --> | ||
| − | === | + | ===drop_archive_tables=== |
| + | $db->drop_archive_tables() | ||
Destroy all tables used by eprints in the database. | Destroy all tables used by eprints in the database. | ||
| Line 178: | Line 198: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_create_dataset_tables --> | <!-- Pod2Wiki=item_create_dataset_tables --> | ||
| − | === | + | ===create_dataset_tables=== |
| + | $success = $db->create_dataset_tables( $dataset ) | ||
Create all the SQL tables for a single dataset. | Create all the SQL tables for a single dataset. | ||
| Line 190: | Line 211: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_drop_dataset_tables --> | <!-- Pod2Wiki=item_drop_dataset_tables --> | ||
| − | === | + | ===drop_dataset_tables=== |
| + | $db->drop_dataset_tables( $dataset ) | ||
Drop all the SQL tables for a single dataset. | Drop all the SQL tables for a single dataset. | ||
| Line 202: | Line 224: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_create_dataset_index_tables --> | <!-- Pod2Wiki=item_create_dataset_index_tables --> | ||
| − | === | + | ===create_dataset_index_tables=== |
| + | $success = $db->create_dataset_index_tables( $dataset ) | ||
Create all the index tables for a single dataset. | Create all the index tables for a single dataset. | ||
| Line 214: | Line 237: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_create_dataset_ordervalues_tables --> | <!-- Pod2Wiki=item_create_dataset_ordervalues_tables --> | ||
| − | === | + | ===create_dataset_ordervalues_tables=== |
| + | $success = $db->create_dataset_ordervalues_tables( $dataset ) | ||
Create all the ordervalues tables for a single dataset. | Create all the ordervalues tables for a single dataset. | ||
| Line 226: | Line 250: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_type_info --> | <!-- Pod2Wiki=item_type_info --> | ||
| − | === | + | ===type_info=== |
| + | $type_info = $db->type_info( DATA_TYPE ) | ||
See {{API:PodLink|file=DBI|package_name=DBI|section=type_info|text=DBI/type_info}}. | See {{API:PodLink|file=DBI|package_name=DBI|section=type_info|text=DBI/type_info}}. | ||
| Line 238: | Line 263: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_get_column_type --> | <!-- Pod2Wiki=item_get_column_type --> | ||
| − | === | + | ===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: | Returns a SQL column definition for NAME of type TYPE, usually something like: | ||
| Line 281: | Line 307: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_create_table --> | <!-- Pod2Wiki=item_create_table --> | ||
| − | === | + | ===create_table=== |
| + | $success = $db->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. | Create the tables used to store metadata for this dataset: the main table and any required for multiple or mulitlang fields. | ||
| Line 295: | Line 322: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_has_sequence --> | <!-- Pod2Wiki=item_has_sequence --> | ||
| − | === | + | ===has_sequence=== |
| + | $boolean = $db->has_sequence( $name ) | ||
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 307: | Line 335: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_create_sequence --> | <!-- Pod2Wiki=item_create_sequence --> | ||
| − | === | + | ===create_sequence=== |
| + | $success = $db->create_sequence( $seq_name ) | ||
Creates a new sequence object initialised to zero. | Creates a new sequence object initialised to zero. | ||
| Line 319: | Line 348: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_drop_sequence --> | <!-- Pod2Wiki=item_drop_sequence --> | ||
| − | === | + | ===drop_sequence=== |
| + | $success = $db->drop_sequence( $seq_name ) | ||
Deletes a sequence object. | Deletes a sequence object. | ||
| Line 331: | Line 361: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_drop_column --> | <!-- Pod2Wiki=item_drop_column --> | ||
| − | === | + | ===drop_column=== |
| + | $success = $db->drop_column( $table, $column ) | ||
Drops a column from a table. | Drops a column from a table. | ||
| Line 343: | Line 374: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_get_primary_key --> | <!-- Pod2Wiki=item_get_primary_key --> | ||
| − | === | + | ===get_primary_key=== |
| + | @columns = $db->get_primary_key( $tablename ) | ||
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 357: | Line 389: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_create_index --> | <!-- Pod2Wiki=item_create_index --> | ||
| − | === | + | ===create_index=== |
| + | $success = $db->create_index( $tablename, @columns ) | ||
Creates an index over @columns for $tablename. Returns true on success. | Creates an index over @columns for $tablename. Returns true on success. | ||
| Line 369: | Line 402: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_create_unique_index --> | <!-- Pod2Wiki=item_create_unique_index --> | ||
| − | === | + | ===create_unique_index=== |
| + | $success = $db->create_unique_index( $tablename, @columns ) | ||
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 381: | Line 415: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item__update --> | <!-- Pod2Wiki=item__update --> | ||
| − | === | + | ===_update=== |
| + | $success = $db->_update( $tablename, $keycols, $keyvals, $columns, @values ) | ||
UPDATES $tablename where $keycols equals $keyvals. | UPDATES $tablename where $keycols equals $keyvals. | ||
| Line 395: | Line 430: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item__update_quoted --> | <!-- Pod2Wiki=item__update_quoted --> | ||
| − | === | + | ===_update_quoted=== |
| + | $success = $db->_update_quoted( $tablename, $keycols, $keyvals, $columns, @qvalues ) | ||
UPDATES $tablename where $keycols equals $keyvals. Won't quote $keyvals or @qvalues before use - use this method with care! | UPDATES $tablename where $keycols equals $keyvals. Won't quote $keyvals or @qvalues before use - use this method with care! | ||
| Line 409: | Line 445: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_insert --> | <!-- Pod2Wiki=item_insert --> | ||
| − | === | + | ===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. | 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 423: | Line 460: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_insert_quoted --> | <!-- Pod2Wiki=item_insert_quoted --> | ||
| − | === | + | ===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. | 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 437: | Line 475: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_delete_from --> | <!-- Pod2Wiki=item_delete_from --> | ||
| − | === | + | ===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. | 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 451: | Line 490: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_add_record --> | <!-- Pod2Wiki=item_add_record --> | ||
| − | === | + | ===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. | 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 463: | Line 503: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_prep_int --> | <!-- Pod2Wiki=item_prep_int --> | ||
| − | === | + | ===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). | Escape a numerical value for SQL. undef becomes NULL. Anything else becomes a number (zero if needed). | ||
| Line 475: | Line 516: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_prep_value --> | <!-- Pod2Wiki=item_prep_value --> | ||
| − | === | + | ===prep_value=== |
| + | $mungedvalue = EPrints::Database::prep_value( $value ) | ||
Escape a value for SQL. Modify value such that " becomes \" and \ becomes \\ and ' becomes \' | Escape a value for SQL. Modify value such that " becomes \" and \ becomes \\ and ' becomes \' | ||
| Line 487: | Line 529: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_prep_like_value --> | <!-- Pod2Wiki=item_prep_like_value --> | ||
| − | === | + | ===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 _ | Escape an value for an SQL like field. In addition to ' " and \ also escapes % and _ | ||
| Line 499: | Line 542: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_quote_value --> | <!-- Pod2Wiki=item_quote_value --> | ||
| − | === | + | ===quote_value=== |
| + | $str = $db->quote_value( $value ) | ||
Return a quoted value. To quote a 'like' value you should do: | Return a quoted value. To quote a 'like' value you should do: | ||
| Line 513: | Line 557: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_quote_int --> | <!-- Pod2Wiki=item_quote_int --> | ||
| − | === | + | ===quote_int=== |
| + | $str = $db->quote_int( $value ) | ||
Return a quoted integer value | Return a quoted integer value | ||
| Line 525: | Line 570: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_quote_binary --> | <!-- Pod2Wiki=item_quote_binary --> | ||
| − | === | + | ===quote_binary=== |
| + | $str = $db->quote_binary( $bytes ) | ||
Some databases (PostgreSQL) require weird transforms of binary data to work correctly. | Some databases (PostgreSQL) require weird transforms of binary data to work correctly. | ||
| Line 539: | Line 585: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_quote_identifier --> | <!-- Pod2Wiki=item_quote_identifier --> | ||
| − | === | + | ===quote_identifier=== |
| + | $str = $db->quote_identifier( @parts ) | ||
Quote a database identifier (e.g. table names). Multiple @parts will be joined by dot. | Quote a database identifier (e.g. table names). Multiple @parts will be joined by dot. | ||
| Line 551: | Line 598: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_update --> | <!-- Pod2Wiki=item_update --> | ||
| − | === | + | ===update=== |
| + | $success = $db->update( $dataset, $data, $changed, $insert ) | ||
Updates a record in the database with the given $data. Obviously the value of the primary key must be set. | Updates a record in the database with the given $data. Obviously the value of the primary key must be set. | ||
| Line 565: | Line 613: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_remove --> | <!-- Pod2Wiki=item_remove --> | ||
| − | === | + | ===remove=== |
| + | $success = $db->remove( $dataset, $id ) | ||
Attempts to remove the record with the primary key $id from the specified dataset. | Attempts to remove the record with the primary key $id from the specified dataset. | ||
| Line 577: | Line 626: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_create_counters --> | <!-- Pod2Wiki=item_create_counters --> | ||
| − | === | + | ===create_counters=== |
| + | $success = $db->create_counters | ||
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 589: | Line 639: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_has_counter --> | <!-- Pod2Wiki=item_has_counter --> | ||
| − | === | + | ===has_counter=== |
| + | $success = $db->has_counter( $counter ) | ||
Returns true if $counter exists. | Returns true if $counter exists. | ||
| Line 601: | Line 652: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_create_counter --> | <!-- Pod2Wiki=item_create_counter --> | ||
| − | === | + | ===create_counter=== |
| + | $success = $db->create_counter( $name ) | ||
Create and initialise to zero a new counter called $name. | Create and initialise to zero a new counter called $name. | ||
| Line 613: | Line 665: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_remove_counters --> | <!-- Pod2Wiki=item_remove_counters --> | ||
| − | === | + | ===remove_counters=== |
| + | $success = $db->remove_counters | ||
Destroy all counters. | Destroy all counters. | ||
| Line 625: | Line 678: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_drop_counter --> | <!-- Pod2Wiki=item_drop_counter --> | ||
| − | === | + | ===drop_counter=== |
| + | $success = $db->drop_counter( $name ) | ||
Destroy the counter named $name. | Destroy the counter named $name. | ||
| Line 637: | Line 691: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_next_doc_pos --> | <!-- Pod2Wiki=item_next_doc_pos --> | ||
| − | === | + | ===next_doc_pos=== |
| + | $n = $db->next_doc_pos( $eprintid ) | ||
Return the next unused document pos for the given eprintid. | Return the next unused document pos for the given eprintid. | ||
| Line 649: | Line 704: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_counter_current --> | <!-- Pod2Wiki=item_counter_current --> | ||
| − | === | + | ===counter_current=== |
| + | $n = $db->counter_current( $counter ) | ||
Return the value of the previous counter_next on $counter. | Return the value of the previous counter_next on $counter. | ||
| Line 661: | Line 717: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_counter_next --> | <!-- Pod2Wiki=item_counter_next --> | ||
| − | === | + | ===counter_next=== |
| + | $n = $db->counter_next( $counter ) | ||
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 673: | Line 730: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_counter_minimum --> | <!-- Pod2Wiki=item_counter_minimum --> | ||
| − | === | + | ===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. | Ensure that the counter is set no lower than $value. This is used when importing eprints which may not be in scrict sequence. | ||
| Line 685: | Line 743: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_counter_reset --> | <!-- Pod2Wiki=item_counter_reset --> | ||
| − | === | + | ===counter_reset=== |
| + | $db->counter_reset( $counter ) | ||
Reset the counter. Use with caution. | Reset the counter. Use with caution. | ||
| Line 697: | Line 756: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_cache_exp --> | <!-- Pod2Wiki=item_cache_exp --> | ||
| − | === | + | ===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. | Return the serialised Search of a the cached search with id $cacheid. Return undef if the id is invalid or expired. | ||
| Line 709: | Line 769: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_cache --> | <!-- Pod2Wiki=item_cache --> | ||
| − | === | + | ===cache=== |
| + | $cacheid = $db->cache( $searchexp, $dataset, $srctable, [$order], [$list] ) | ||
Create a cache of the specified search expression from the SQL table $srctable. | Create a cache of the specified search expression from the SQL table $srctable. | ||
| Line 727: | Line 788: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_cache_table --> | <!-- Pod2Wiki=item_cache_table --> | ||
| − | === | + | ===cache_table=== |
| + | $tablename = $db->cache_table( $id ) | ||
Return the SQL table used to store the cache with id $id. | Return the SQL table used to store the cache with id $id. | ||
| Line 739: | Line 801: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_get_index_ids --> | <!-- Pod2Wiki=item_get_index_ids --> | ||
| − | === | + | ===get_index_ids=== |
| + | $ids = $db->get_index_ids( $table, $condition ) | ||
Return a reference to an array of the distinct primary keys from the given SQL table which match the specified condition. | Return a reference to an array of the distinct primary keys from the given SQL table which match the specified condition. | ||
| Line 751: | Line 814: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_search --> | <!-- Pod2Wiki=item_search --> | ||
| − | === | + | ===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. | 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 765: | Line 829: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_drop_cache --> | <!-- Pod2Wiki=item_drop_cache --> | ||
| − | === | + | ===drop_cache=== |
| + | $db->drop_cache( $id ) | ||
Remove the cached search with the given id. | Remove the cached search with the given id. | ||
| Line 777: | Line 842: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_count_table --> | <!-- Pod2Wiki=item_count_table --> | ||
| − | === | + | ===count_table=== |
| + | $n = $db->count_table( $tablename ) | ||
Return the number of rows in the specified SQL table. | Return the number of rows in the specified SQL table. | ||
| Line 789: | Line 855: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_from_cache --> | <!-- Pod2Wiki=item_from_cache --> | ||
| − | === | + | ===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. | 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 805: | Line 872: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_drop_orphan_cache_tables --> | <!-- Pod2Wiki=item_drop_orphan_cache_tables --> | ||
| − | === | + | ===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. | Drop tables called "cacheXXX" where XXX is an integer. Returns the number of tables dropped. | ||
| Line 817: | Line 885: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_get_single --> | <!-- Pod2Wiki=item_get_single --> | ||
| − | === | + | ===get_single=== |
| + | $obj = $db->get_single( $dataset, $id ) | ||
Return a single item from the given dataset. The one with the specified id. | Return a single item from the given dataset. The one with the specified id. | ||
| Line 829: | Line 898: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_get_all --> | <!-- Pod2Wiki=item_get_all --> | ||
| − | === | + | ===get_all=== |
| + | $items = $db->get_all( $dataset ) | ||
Returns a reference to an array with all the items from the given dataset. | Returns a reference to an array with all the items from the given dataset. | ||
| Line 841: | Line 911: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_get_cache_ids --> | <!-- Pod2Wiki=item_get_cache_ids --> | ||
| − | === | + | ===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. | Returns a list of $count ids from $cache_id starting at $offset and in the order in the cachemap. | ||
| Line 853: | Line 924: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_get_dataobjs --> | <!-- Pod2Wiki=item_get_dataobjs --> | ||
| − | === | + | ===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. | Retrieves the records in $dataset with the given $id(s). If an $id doesn't exist in the database it will be ignored. | ||
| Line 865: | Line 937: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_get_values --> | <!-- Pod2Wiki=item_get_values --> | ||
| − | === | + | ===get_values=== |
| + | $foo = $db->get_values( $field, $dataset ) | ||
Return a reference to an array of all the distinct values of the EPrints::MetaField specified. | Return a reference to an array of all the distinct values of the EPrints::MetaField specified. | ||
| Line 877: | Line 950: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_sort_values --> | <!-- Pod2Wiki=item_sort_values --> | ||
| − | === | + | ===sort_values=== |
| + | $values = $db->sort_values( $field, $values [, $langid ] ) | ||
ALPHA!!! Liable to API change!!! | ALPHA!!! Liable to API change!!! | ||
| Line 893: | Line 967: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_get_ids_by_field_values --> | <!-- Pod2Wiki=item_get_ids_by_field_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 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 905: | Line 980: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_do --> | <!-- Pod2Wiki=item_do --> | ||
| − | === | + | ===do=== |
| + | $success = $db->do( $sql ) | ||
Execute the given SQL. | Execute the given SQL. | ||
| Line 917: | Line 993: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_prepare --> | <!-- Pod2Wiki=item_prepare --> | ||
| − | === | + | ===prepare=== |
| + | $sth = $db->prepare( $sql ) | ||
Prepare the given $sql and return a handle on it. | Prepare the given $sql and return a handle on it. | ||
| Line 929: | Line 1,006: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_prepare_select --> | <!-- Pod2Wiki=item_prepare_select --> | ||
| − | === | + | ===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. | Prepare a SELECT statement $sql and return a handle to it. After preparing a statement use execute() to execute it. | ||
| Line 948: | Line 1,026: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_execute --> | <!-- Pod2Wiki=item_execute --> | ||
| − | === | + | ===execute=== |
| + | $success = $db->execute( $sth, $sql ) | ||
Execute the SQL prepared earlier. $sql is only passed in for debugging purposes. | Execute the SQL prepared earlier. $sql is only passed in for debugging purposes. | ||
| Line 960: | Line 1,039: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_has_dataset --> | <!-- Pod2Wiki=item_has_dataset --> | ||
| − | === | + | ===has_dataset=== |
| + | $db->has_dataset( $dataset ) | ||
Returns true if $dataset exists in the database or has no database tables. | Returns true if $dataset exists in the database or has no database tables. | ||
| Line 974: | Line 1,054: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_has_field --> | <!-- Pod2Wiki=item_has_field --> | ||
| − | === | + | ===has_field=== |
| + | $db->has_field( $dataset, $field ) | ||
Returns true if $field is in the database for $dataset. | Returns true if $field is in the database for $dataset. | ||
| Line 986: | Line 1,067: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_add_field --> | <!-- Pod2Wiki=item_add_field --> | ||
| − | === | + | ===add_field=== |
| + | $db->add_field( $dataset, $field [, $force ] ) | ||
Add $field to $dataset's tables. | Add $field to $dataset's tables. | ||
| Line 1,000: | Line 1,082: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_remove_field --> | <!-- Pod2Wiki=item_remove_field --> | ||
| − | === | + | ===remove_field=== |
| + | $db->remove_field( $dataset, $field ) | ||
Remove $field from $dataset's tables. | Remove $field from $dataset's tables. | ||
| Line 1,012: | Line 1,095: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_rename_field --> | <!-- Pod2Wiki=item_rename_field --> | ||
| − | === | + | ===rename_field=== |
| + | $ok = $db->rename_field( $dataset, $field, $old_name ) | ||
Rename a $field in the database from it's old name $old_name. | Rename a $field in the database from it's old name $old_name. | ||
| Line 1,026: | Line 1,110: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_exists --> | <!-- Pod2Wiki=item_exists --> | ||
| − | === | + | ===exists=== |
| + | $boolean = $db->exists( $dataset, $id ) | ||
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,038: | Line 1,123: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_set_debug --> | <!-- Pod2Wiki=item_set_debug --> | ||
| − | === | + | ===set_debug=== |
| + | $db->set_debug( $boolean ) | ||
Set the SQL debug mode to true or false. | Set the SQL debug mode to true or false. | ||
| Line 1,050: | Line 1,136: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_create_version_table --> | <!-- Pod2Wiki=item_create_version_table --> | ||
| − | === | + | ===create_version_table=== |
| + | $db->create_version_table | ||
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 1,062: | Line 1,149: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_drop_version_table --> | <!-- Pod2Wiki=item_drop_version_table --> | ||
| − | === | + | ===drop_version_table=== |
| + | $db->drop_version_table | ||
Drop the version table. | Drop the version table. | ||
| Line 1,074: | Line 1,162: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_set_version --> | <!-- Pod2Wiki=item_set_version --> | ||
| − | === | + | ===set_version=== |
| + | $db->set_version( $versionid ); | ||
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 1,086: | Line 1,175: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_has_table --> | <!-- Pod2Wiki=item_has_table --> | ||
| − | === | + | ===has_table=== |
| + | $boolean = $db->has_table( $tablename ) | ||
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 1,098: | Line 1,188: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_has_column --> | <!-- Pod2Wiki=item_has_column --> | ||
| − | === | + | ===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. | Return true if the a table of the given name has a column named $columnname in the database. | ||
| Line 1,110: | Line 1,201: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_index_name --> | <!-- Pod2Wiki=item_index_name --> | ||
| − | === | + | ===index_name=== |
| + | $name = $db->index_name( $table, @columns ) | ||
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 1,124: | Line 1,216: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_drop_table --> | <!-- Pod2Wiki=item_drop_table --> | ||
| − | === | + | ===drop_table=== |
| + | $db->drop_table( $tablename ) | ||
Delete the named table. Use with caution! | Delete the named table. Use with caution! | ||
| Line 1,136: | Line 1,229: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_clear_table --> | <!-- Pod2Wiki=item_clear_table --> | ||
| − | === | + | ===clear_table=== |
| + | $db->clear_table( $tablename ) | ||
Clears all records from the given table, use with caution! | Clears all records from the given table, use with caution! | ||
| Line 1,148: | Line 1,242: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_rename_table --> | <!-- Pod2Wiki=item_rename_table --> | ||
| − | === | + | ===rename_table=== |
| + | $db->rename_table( $tablename, $newtablename ) | ||
Renames the table from the old name to the new one. | Renames the table from the old name to the new one. | ||
| Line 1,160: | Line 1,255: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_swap_table --> | <!-- Pod2Wiki=item_swap_table --> | ||
| − | === | + | ===swap_table=== |
| + | $db->swap_table( $table_a, $table_b ) | ||
Swap table a and table b. | Swap table a and table b. | ||
| Line 1,172: | Line 1,268: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_get_tables --> | <!-- Pod2Wiki=item_get_tables --> | ||
| − | === | + | ===get_tables=== |
| + | @tables = $db->get_tables | ||
Return a list of all the tables in the database. | Return a list of all the tables in the database. | ||
| Line 1,184: | Line 1,281: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_get_version --> | <!-- Pod2Wiki=item_get_version --> | ||
| − | === | + | ===get_version=== |
| + | $version = $db->get_version | ||
Return the version of eprints which the database is compatable with or undef if unknown (before v2.1). | Return the version of eprints which the database is compatable with or undef if unknown (before v2.1). | ||
| Line 1,196: | Line 1,294: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_is_latest_version --> | <!-- Pod2Wiki=item_is_latest_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. | Return true if the SQL tables are in the correct configuration for this edition of eprints. Otherwise false. | ||
| Line 1,208: | Line 1,307: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_valid_login --> | <!-- Pod2Wiki=item_valid_login --> | ||
| − | === | + | ===valid_login=== |
| + | $db->valid_login( $username, $password ) | ||
Returns whether the clear-text $password matches the stored crypted password for $username. | Returns whether the clear-text $password matches the stored crypted password for $username. | ||
| Line 1,220: | Line 1,320: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_get_server_version --> | <!-- Pod2Wiki=item_get_server_version --> | ||
| − | === | + | ===get_server_version=== |
| + | $version = $db->get_server_version | ||
Return the database server version. | Return the database server version. | ||
| Line 1,232: | Line 1,333: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_get_default_charset --> | <!-- Pod2Wiki=item_get_default_charset --> | ||
| − | === | + | ===get_default_charset=== |
| + | $charset = $db->get_default_charset( LANGUAGE ) | ||
Return the character set to use for LANGUAGE. | Return the character set to use for LANGUAGE. | ||
| Line 1,246: | Line 1,348: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_get_default_collation --> | <!-- Pod2Wiki=item_get_default_collation --> | ||
| − | === | + | ===get_default_collation=== |
| + | $collation = $db->get_default_collation( LANGUAGE ) | ||
Return the collation to use for LANGUAGE. | Return the collation to use for LANGUAGE. | ||
| Line 1,260: | Line 1,363: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_get_driver_name --> | <!-- Pod2Wiki=item_get_driver_name --> | ||
| − | === | + | ===get_driver_name=== |
| + | $driver = $db->get_driver_name | ||
Return the database driver name. | Return the database driver name. | ||
| Line 1,272: | Line 1,376: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_dequeue_events --> | <!-- Pod2Wiki=item_dequeue_events --> | ||
| − | === | + | ===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. | 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,284: | Line 1,389: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_prepare_regexp --> | <!-- Pod2Wiki=item_prepare_regexp --> | ||
| − | === | + | ===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). | 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 1,296: | Line 1,402: | ||
</div> | </div> | ||
<!-- Pod2Wiki=item_alias_glue --> | <!-- Pod2Wiki=item_alias_glue --> | ||
| − | === | + | ===alias_glue=== |
| + | $glue = $db->alias_glue() | ||
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. | ||
Revision as of 13:23, 25 February 2010
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 METHODS
- 3.1 build_connection_string
- 3.2 create
- 3.3 new
- 3.4 connect
- 3.5 disconnect
- 3.6 error
- 3.7 begin
- 3.8 commit
- 3.9 rollback
- 3.10 create_archive_tables
- 3.11 drop_archive_tables
- 3.12 create_dataset_tables
- 3.13 drop_dataset_tables
- 3.14 create_dataset_index_tables
- 3.15 create_dataset_ordervalues_tables
- 3.16 type_info
- 3.17 get_column_type
- 3.18 create_table
- 3.19 has_sequence
- 3.20 create_sequence
- 3.21 drop_sequence
- 3.22 drop_column
- 3.23 get_primary_key
- 3.24 create_index
- 3.25 create_unique_index
- 3.26 _update
- 3.27 _update_quoted
- 3.28 insert
- 3.29 insert_quoted
- 3.30 delete_from
- 3.31 add_record
- 3.32 prep_int
- 3.33 prep_value
- 3.34 prep_like_value
- 3.35 quote_value
- 3.36 quote_int
- 3.37 quote_binary
- 3.38 quote_identifier
- 3.39 update
- 3.40 remove
- 3.41 create_counters
- 3.42 has_counter
- 3.43 create_counter
- 3.44 remove_counters
- 3.45 drop_counter
- 3.46 next_doc_pos
- 3.47 counter_current
- 3.48 counter_next
- 3.49 counter_minimum
- 3.50 counter_reset
- 3.51 cache_exp
- 3.52 cache
- 3.53 cache_table
- 3.54 get_index_ids
- 3.55 search
- 3.56 drop_cache
- 3.57 count_table
- 3.58 from_cache
- 3.59 drop_orphan_cache_tables
- 3.60 get_single
- 3.61 get_all
- 3.62 get_cache_ids
- 3.63 get_dataobjs
- 3.64 get_values
- 3.65 sort_values
- 3.66 get_ids_by_field_values
- 3.67 do
- 3.68 prepare
- 3.69 prepare_select
- 3.70 execute
- 3.71 has_dataset
- 3.72 has_field
- 3.73 add_field
- 3.74 remove_field
- 3.75 rename_field
- 3.76 exists
- 3.77 set_debug
- 3.78 create_version_table
- 3.79 drop_version_table
- 3.80 set_version
- 3.81 has_table
- 3.82 has_column
- 3.83 index_name
- 3.84 drop_table
- 3.85 clear_table
- 3.86 rename_table
- 3.87 swap_table
- 3.88 get_tables
- 3.89 get_version
- 3.90 is_latest_version
- 3.91 valid_login
- 3.92 get_server_version
- 3.93 get_default_charset
- 3.94 get_default_collation
- 3.95 get_driver_name
- 3.96 dequeue_events
- 3.97 prepare_regexp
- 3.98 alias_glue
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.
The database object is created automatically when you start a new eprints session. To get a handle on it use:
$db = $session->get_database
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.
Variables that are database quoted are prefixed with 'Q_'.
METHODS
build_connection_string
$dbstr = EPrints::Database::build_connection_string( %params )
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
$db = $db->create( $username, $password )
Create and connect to a new database using super user account $username and $password.
new
$db = EPrints::Database->new( $session )
Create a connection to the database.
connect
$foo = $db->connect
Connects to the database.
disconnect
$foo = $db->disconnect
Disconnects from the EPrints database. Should always be done before any script exits.
error
$errstr = $db->error
Return a string describing the last SQL error.
begin
$db->begin
Begin a transaction.
commit
$db->commit
Commit the previous begun transaction.
rollback
$db->rollback
Rollback the partially completed transaction.
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_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.
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.
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.
create_table
$success = $db->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.
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_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.
_update
$success = $db->_update( $tablename, $keycols, $keyvals, $columns, @values )
UPDATES $tablename where $keycols equals $keyvals.
This method is internal.
_update_quoted
$success = $db->_update_quoted( $tablename, $keycols, $keyvals, $columns, @qvalues )
UPDATES $tablename where $keycols equals $keyvals. Won't quote $keyvals or @qvalues before use - use this method with care!
This method is internal.
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().
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.
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 (PostgreSQL) require weird transforms of binary data to work correctly.
This method should be called on data containing nul bytes or back-slashes before being passed on /quote_value.
quote_identifier
$str = $db->quote_identifier( @parts )
Quote a database identifier (e.g. table names). Multiple @parts will be joined by dot.
update
$success = $db->update( $dataset, $data, $changed, $insert )
Updates a record in the database with the given $data. Obviously the value of the primary key must be set.
This also updates the text indexes and the ordering keys.
remove
$success = $db->remove( $dataset, $id )
Attempts to remove the record with the primary key $id from the specified dataset.
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.
next_doc_pos
$n = $db->next_doc_pos( $eprintid )
Return the next unused document pos for the given eprintid.
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.
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.
get_index_ids
$ids = $db->get_index_ids( $table, $condition )
Return a reference to an array of the distinct primary keys from the given SQL table which match the specified condition.
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.
count_table
$n = $db->count_table( $tablename )
Return the number of rows in the specified SQL table.
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.
sort_values
$values = $db->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->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 field value ids and the value is a reference to an array of ids.
do
$success = $db->do( $sql )
Execute the given SQL.
prepare
$sth = $db->prepare( $sql )
Prepare the given $sql and return a handle on it.
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.
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
execute
$success = $db->execute( $sth, $sql )
Execute the SQL prepared earlier. $sql is only passed in for debugging purposes.
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.
exists
$boolean = $db->exists( $dataset, $id )
Return true if a record with the given primary key exists in the dataset, otherwise false.
set_debug
$db->set_debug( $boolean )
Set the SQL debug mode to true or false.
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.
set_version
$db->set_version( $versionid );
Set the version id table in the SQL database to the given value (used by the upgrade script).
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.
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.
drop_table
$db->drop_table( $tablename )
Delete the named table. 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.
get_version
$version = $db->get_version
Return the version of eprints which the database is compatable with or undef if unknown (before v2.1).
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.
valid_login
$db->valid_login( $username, $password )
Returns whether the clear-text $password matches the stored crypted password for $username.
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.
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.
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).
alias_glue
$glue = $db->alias_glue()
Returns the syntactic glue to use when aliasing. SQL 92 DBs will happilly use " AS " but some DBs (Oracle!) won't accept it.
