Difference between revisions of "API:EPrints/Database/Pg"

From EPrints Documentation
Jump to: navigation, search
 
(3 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
<!-- Pod2Wiki=_preamble_  
 
<!-- Pod2Wiki=_preamble_  
−
This page has been automatically generated from the EPrints 3.2 source. Any wiki changes made between the 'Pod2Wiki=*' and 'Edit below this comment' comments will be lost.
+
This page has been automatically generated from the EPrints 3.4 source. Any wiki changes made between the 'Pod2Wiki=*' and 'Edit below this comment' comments will be lost.
−
  -->{{API}}{{Pod2Wiki}}{{API:Source|file=EPrints/Database/Pg.pm|package_name=EPrints::Database::Pg}}[[Category:API|PG]][[Category:API:EPrints/Database|PG]][[Category:API:EPrints/Database/Pg|PG]]<div><!-- Edit below this comment -->
+
  -->{{API}}{{Pod2Wiki}}{{API:Source|file=EPrints/Database/Pg.pm|package_name=EPrints::Database::Pg}}[[Category:API|PG]][[Category:API:EPrints/Database|PG]]<div><!-- Edit below this comment -->
  
  
Line 17: Line 17:
 
<!-- Pod2Wiki=head_description -->
 
<!-- Pod2Wiki=head_description -->
 
==DESCRIPTION==
 
==DESCRIPTION==
 +
PostgreSQL database wrapper.
 +
 
<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 24: Line 26:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
−
<!-- Pod2Wiki=head_todo -->
+
<!-- Pod2Wiki=head_synopsis -->
−
===TODO===
+
===Synopsis===
 +
    $c-&gt;{dbdriver} = 'Pg';
 +
    # $c-&gt;{dbhost} = 'localhost';
 +
    $c-&gt;{dbname} = 'myrepo';
 +
    $c-&gt;{dbuser} = 'bob';
 +
    $c-&gt;{dbpass} = 'asecret';
 +
    $c-&gt;{dbschema} = 'eprints';
 +
 
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<span style='display:none'>User Comments</span>
 
<span style='display:none'>User Comments</span>
Line 33: Line 42:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
−
<!-- Pod2Wiki=item_epadmin_create -->
+
<!-- Pod2Wiki=head_postgresql_specific_annoyances -->
−
====epadmin create====
+
===PostgreSQL-specific Annoyances===
 +
The {{API:PodLink|file=DBD/Pg|package_name=DBD::Pg|section=|text=DBD::Pg}} <tt>SQL_VARCHAR</tt> type is mapped to <tt>TEXT</tt> instead of  <tt>VARCHAR(n)</tt>.
  
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 43: Line 53:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
−
<!-- Pod2Wiki=item_index_name -->
+
<!-- Pod2Wiki=head_constants -->
−
====$name = $db-&gt;index_name( $table, @columns )====
+
==CONSTANTS==
 +
See [[API:EPrints/Database#CONSTANTS|EPrints::Database]].
  
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 53: Line 64:
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 
</div>
 
</div>
−
<!-- Pod2Wiki=head_postgresql_specific_annoyances -->
+
<!-- Pod2Wiki=head_instance_variables -->
−
===PostgreSQL-specific Annoyances===
+
==INSTANCE VARIABLES==
−
The {{API:PodLink|file=DBD/Pg|package_name=DBD::Pg|section=|text=DBD::Pg}} SQL_VARCHAR type is mapped to text instead of varchar(n).
+
See [[API:EPrints/Database#INSTANCE_VARIABLES|EPrints::Database]].
  
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
Line 66: Line 77:
 
<!-- Pod2Wiki=head_methods -->
 
<!-- Pod2Wiki=head_methods -->
 
==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%; '>
 +
<span style='display:none'>User Comments</span>
 +
<!-- Edit below this comment -->
 +
 +
 +
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=item_connect -->
 +
===connect===
 +
 +
$db-&gt;connect
 +
Connects to the database.
 +
 +
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 +
<span style='display:none'>User Comments</span>
 +
<!-- Edit below this comment -->
 +
 +
 +
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=item_type_info -->
 +
===type_info===
 +
 +
$type_info = $db-&gt;type_info( $data_type )
 +
See {{API:PodLink|file=DBI|package_name=DBI|section=type_info|text=DBI/type_info}}.
 +
 +
Uses <tt>SMALLINT</tt> with column size 3 for <tt>SQL_TINYINT</tt>.
 +
 +
Uses <tt>VARCHAR</tt> with column size 255 for <tt>SQL_VARCHAR</tt> rather than <tt>TEXT</tt> which is the default for {{API:PodLink|file=DBD:Pg|package_name=DBD:Pg|section=|text=DBD:Pg}}.
 +
 +
Uses <tt>TEXT</tt> with column size 2^31 for <tt>SQL_LONGVARCHAR</tt> and <tt>SQL_CLOB</tt>.
 +
 +
Uses <tt>BYTEA</tt> with column size 2^31 for <tt>SQL_LONGVARBINARY</tt>.
 +
 +
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 +
<span style='display:none'>User Comments</span>
 +
<!-- Edit below this comment -->
 +
 +
 +
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=item_create -->
 +
===create===
 +
 +
$db = $db-&gt;create( $username, $password )
 +
Create and connect to a new database using user account <tt>$username</tt>  and <tt>$password</tt>.
 +
 +
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 +
<span style='display:none'>User Comments</span>
 +
<!-- Edit below this comment -->
 +
 +
 +
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=item_get_column_type -->
 +
===get_column_type===
 +
 +
$real_type = $db-&gt;get_column_type( $name, $type, $not_null, [ $length ] )
 +
Returns a SQL column definition for <tt>$name</tt> of type <tt>$type</tt>. If <tt>$not_null</tt> is <tt>true</tt> the column will be set to <tt>NOT NULL</tt>. For column types that require a length use <tt>$length</tt>.
 +
 +
<tt>$type</tt> 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: <tt>SQL_VARCHAR(n)</tt>, <tt>SQL_LONGVARCHAR</tt>.
 +
 +
Binary data: <tt>SQL_VARBINARY(n)</tt>, <tt>SQL_LONGVARBINARY</tt>.
 +
 +
Integer data: <tt>SQL_TINYINT</tt>, <tt>SQL_SMALLINT</tt>, <tt>SQL_INTEGER</tt>,
 +
 +
Floating-point data: <tt>SQL_REAL</tt>, <tt>SQL_DOUBLE</tt>.
 +
 +
Time data: <tt>SQL_DATE</tt>, <tt>SQL_TIME</tt>.
 +
 +
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 +
<span style='display:none'>User Comments</span>
 +
<!-- Edit below this comment -->
 +
 +
 +
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=item_has_table -->
 +
===has_table===
 +
 +
$boolean = $db-&gt;has_table( $table )
 +
Returns boolean dependent on whether the named <tt>$table</tt> exists in the database.
 +
 +
For PostGres <tt>column_info()</tt> under {{API:PodLink|file=DBD/Pg|package_name=DBD::Pg|section=|text=DBD::Pg}} returns reserved  identifiers in quotes, so instead we'll query the  <tt>information_schema</tt>.
 +
 +
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 +
<span style='display:none'>User Comments</span>
 +
<!-- Edit below this comment -->
 +
 +
 +
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=item_has_column -->
 +
===has_column===
 +
 +
$boolean = $db-&gt;has_column( $table, $column )
 +
Return <tt>true</tt> if the named database <tt>$table</tt> has the named <tt>$column</tt>.
 +
 +
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 +
<span style='display:none'>User Comments</span>
 +
<!-- Edit below this comment -->
 +
 +
 +
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=item_has_sequence -->
 +
===has_sequence===
 +
 +
$boolean = $db-&gt;has_sequence( $name )
 +
Return <tt>true</tt> if a sequence of the given $&lt;name&gt; exists in the database.
 +
 +
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 +
<span style='display:none'>User Comments</span>
 +
<!-- Edit below this comment -->
 +
 +
 +
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=item_get_tables -->
 +
===get_tables===
 +
 +
@tables = $db-&gt;get_tables
 +
Return a list of all the tables in the database.
 +
 +
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 +
<span style='display:none'>User Comments</span>
 +
<!-- Edit below this comment -->
 +
 +
 +
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=item_counter_current -->
 +
===counter_current===
 +
 +
$n = $db-&gt;counter_current( $counter )
 +
Return the value of the previous counter_next on <tt>$counter</tt>.
 +
 +
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 +
<span style='display:none'>User Comments</span>
 +
<!-- Edit below this comment -->
 +
 +
 +
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=item_counter_next -->
 +
===counter_next===
 +
 +
$n = $db-&gt;counter_next( $counter )
 +
Return the next unused value for the named <tt>$counter</tt>. Returns <tt>undef</tt> if the <tt>$counter</tt> doesn't exist.
 +
 +
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 +
<span style='display:none'>User Comments</span>
 +
<!-- Edit below this comment -->
 +
 +
 +
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=item_quote_binary -->
 +
===quote_binary===
 +
 +
$str = $db-&gt;quote_binary( $bytes )
 +
PostgreSQL requires transforms of binary data to work correctly.
 +
 +
This sets <tt>pg_type</tt> to {{API:PodLink|file=DBD/Pg/Pg_BYTEA|package_name=DBD::Pg::Pg_BYTEA|section=|text=DBD::Pg::Pg_BYTEA}} for returned <tt>$bytes</tt>.
 +
 +
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 +
<span style='display:none'>User Comments</span>
 +
<!-- Edit below this comment -->
 +
 +
 +
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=item_prepare_regexp -->
 +
===prepare_regexp===
 +
 +
$sql = $db-&gt;prepare_regexp( $col, $value )
 +
PostgreSQL use the syntax:
 +
 +
$col ~* $value
 +
 
 +
For the quoted regexp <tt>$value</tt> and the quoted column <tt>$col</tt>.
 +
 +
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 +
<span style='display:none'>User Comments</span>
 +
<!-- Edit below this comment -->
 +
 +
 +
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=item_index_name -->
 +
===index_name===
 +
 +
$name = $db-&gt;index_name( $table, @cols )
 +
Should return the name of the first index that starts with named columns <tt>@cols</tt> in the named <tt>$table</tt>. However, this is not supported by PostgreSQL so always returns <tt>1</tt>.
 +
 +
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 +
<span style='display:none'>User Comments</span>
 +
<!-- Edit below this comment -->
 +
 +
 +
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=item_sql_like -->
 +
===sql_like===
 +
 +
$sql = $db-&gt;sql_LIKE()
 +
Returns the syntactic glue to use when making a case-insensitive <tt>ILIKE</tt>.
 +
 +
<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_see_also -->
 +
==SEE ALSO==
 +
[[API:EPrints/Database|EPrints::Database]]
 +
 
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
 
<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 75: Line 311:
 
<!-- Pod2Wiki=head_copyright -->
 
<!-- Pod2Wiki=head_copyright -->
 
==COPYRIGHT==
 
==COPYRIGHT==
 +
{{API:Copyright}}
 
<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>

Latest revision as of 23:55, 11 January 2022

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

API: Core API

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


NAME

EPrints::Database::Pg - custom database methods for PostgreSQL DB

User Comments


DESCRIPTION

PostgreSQL database wrapper.

User Comments


Synopsis

   $c->{dbdriver} = 'Pg';
   # $c->{dbhost} = 'localhost';
   $c->{dbname} = 'myrepo';
   $c->{dbuser} = 'bob';
   $c->{dbpass} = 'asecret';
   $c->{dbschema} = 'eprints';

User Comments


PostgreSQL-specific Annoyances

The DBD::Pg SQL_VARCHAR type is mapped to TEXT instead of VARCHAR(n).

User Comments


CONSTANTS

See EPrints::Database.

User Comments


INSTANCE VARIABLES

See EPrints::Database.

User Comments


METHODS

User Comments


connect

$db->connect

Connects to the database.

User Comments


type_info

$type_info = $db->type_info( $data_type )

See DBI/type_info.

Uses SMALLINT with column size 3 for SQL_TINYINT.

Uses VARCHAR with column size 255 for SQL_VARCHAR rather than TEXT which is the default for DBD:Pg.

Uses TEXT with column size 2^31 for SQL_LONGVARCHAR and SQL_CLOB.

Uses BYTEA with column size 2^31 for SQL_LONGVARBINARY.

User Comments


create

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

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

User Comments


get_column_type

$real_type = $db->get_column_type( $name, $type, $not_null, [ $length ] )

Returns a SQL column definition for $name of type $type. If $not_null is true the column will be set to NOT NULL. For column types that require a length use $length.

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

Floating-point data: SQL_REAL, SQL_DOUBLE.

Time data: SQL_DATE, SQL_TIME.

User Comments


has_table

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

Returns boolean dependent on whether the named $table exists in the database.

For PostGres column_info() under DBD::Pg returns reserved identifiers in quotes, so instead we'll query the information_schema.

User Comments


has_column

$boolean = $db->has_column( $table, $column )

Return true if the named database $table has the named $column.

User Comments


has_sequence

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

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

User Comments


get_tables

@tables = $db->get_tables

Return a list of all the tables in the database.

User Comments


counter_current

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

Return the value of the previous counter_next on $counter.

User Comments


counter_next

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

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

User Comments


quote_binary

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

PostgreSQL requires transforms of binary data to work correctly.

This sets pg_type to DBD::Pg::Pg_BYTEA for returned $bytes.

User Comments


prepare_regexp

$sql = $db->prepare_regexp( $col, $value )

PostgreSQL use the syntax: 

$col ~* $value

For the quoted regexp $value and the quoted column $col.

User Comments


index_name

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

Should return the name of the first index that starts with named columns @cols in the named $table. However, this is not supported by PostgreSQL so always returns 1.

User Comments


sql_like

$sql = $db->sql_LIKE()

Returns the syntactic glue to use when making a case-insensitive ILIKE.

User Comments


SEE ALSO

EPrints::Database

User Comments


COPYRIGHT

© Copyright 2000-2024 University of Southampton.

EPrints 3.4 is supplied by EPrints Services.

http://www.eprints.org/eprints-3.4/

LICENSE

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

EPrints 3.4 and this file are released under the terms of the GNU Lesser General Public License version 3 as published by the Free Software Foundation unless otherwise stated.

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

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

User Comments


Retrieved from ‘https://wiki.eprints.org/w/index.php?title=API:EPrints/Database/Pg&oldid=14220’