[class] Database Access

Gepost door Sjaak Ringens op 05-12-2010 23:47.

Eindelijk weer eens een wat langer script van mij!

Ik weet dat er al aardig wat zijn, maar ik heb toch een eigen database class gemaakt, maar dan voor PostgreSQL. Een uitbreiding voor MySQL komt hierna ( moet natuurlijk niet te moeilijk zijn als de opzet goed is )

De opzet is eenvoudig, ik heb een mappenstructuur:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- sr
   |-- Database
       |~ Exception.php
       |
       |-- Connection
       |   |~ Base.php
       |   |~ Interface.php
       |
       |-- MySQL
       |   |~ Connection.php (niet meegeleverd)
       |   |~ Result.php (niet meegeleverd)       
       |
       |-- PostgreSQL
       |   |~ Connection.php
       |   |~ Result.php
       |
       |-- Result
           |~ Base.php
           |~ Interface.php

Door deze structuur te gebruiken is het makkelijker __autoload( ) te gebruiken om de classen aan te roepen.
Wil ik een soort database toevoegen, maak ik een mapje aan met de naam van de database, en plaats hierin de 2 benodigde bestanden.

Verder kun je met deze classes queries uitvoeren (lijkt me wel handig...), transacties en savepoints beheren, resultaten ophalen etc. All the works.

Ik heb de code in een zipje geplaatst aangezien ik niet meerdere bestanden kan uploaden!

( @mods: moet ik de code ook nog eens extra in dit bericht plaatsen?)

Reacties stel ik op prijs!

Bestanden van dit script

Database/Connection/Base.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
<?php

abstract class sr_Database_Connection_Base
{
    /***
    * The connection to the database.
    * @var         $rConnection
    * @access    protected
    * @type     resource
    */    
    protected $rConnection;

    /***
    * Holds the current connection status.
    * @var         $bIsConnected
    * @access    protected
    * @type     boolean
    */    
    protected $bIsConnected = false;

    /***
    * Holds the current transaction status.
    * @var         $bInTransaction
    * @access    protected    
    * @type     boolean
    */    
    protected $bInTransaction = false;
    
    /***
    * Start a transaction if it's not open yet.
    * @access    public
    * @return    boolean                        True if the transaction was started, false otherwise.
    */        
    public function StartTransaction( )
    {
        if( !$this->bIsConnected )
        {
            throw new sr_Database_Exception( 'Can not start transaction, connection is not open.', sr_Database_Exception::CONNECTION_NOT_OPEN );
        }
        
        if( $this->bInTransaction )
        {
            throw new sr_Database_Exception( 'Can not start transaction. Transaction is already open.', sr_Database_Exception::TRANSACTION_ALREADY_OPEN );
        }
        
        return $this->Execute( 'BEGIN;' );    
    }

    /***
    * Commit the current transaction.
    * @access    public
    * @return    boolean                        True if the transaction was committed, false otherwise.
    */            
    public function Commit( )
    {
        if( !$this->bIsConnected )
        {
            throw new sr_Database_Exception( 'Can not commit transaction, connection is not open.', sr_Database_Exception::CONNECTION_NOT_OPEN );
        }
        
        if( !$this->bInTransaction )
        {
            throw new sr_Database_Exception( 'Can not commit transaction. Transaction is not open.', sr_Database_Exception::TRANSACTION_NOT_OPEN );
        }
        
        return $this->Execute( 'COMMIT;' );
    }
    
    /***
    * Rollback the current transaction.
    * @access    public
    * @return    boolean                        True if the transaction was rolled back, false otherwise.
    */    
    public function Rollback( )
    {
        if( !$this->bIsConnected )
        {
            throw new sr_Database_Exception( 'Can not roll back transaction, connection is not open.', sr_Database_Exception::CONNECTION_NOT_OPEN );
        }
        
        if( !$this->bInTransaction )
        {
            throw new sr_Database_Exception( 'Can not roll back transaction. Transaction is not open.', sr_Database_Exception::TRANSACTION_NOT_OPEN );
        }
        
        return $this->Execute( 'ROLLBACK;' );
    }
    
    /***
    * Create a savepoint in the transaction
    * @access    public
    * @param    string $psName                The name of the savepoint.
    * @return    boolean                        True if the transaction was rolled back, false otherwise.
    */    
    public function CreateSavepoint( $psName )
    {
        if( !ctype_alpha( (string)$psName ) )
        {
            throw new sr_Database_Exception( 'Parameter for CreateSavepoint must contain only alphabetic characters.', sr_Database_Exception::WRONG_DATATYPE );
        }
        
        if( !$this->bIsConnected )
        {
            throw new sr_Database_Exception( 'Can not create savepoint, connection is not open.', sr_Database_Exception::CONNECTION_NOT_OPEN );
        }
        
        if( !$this->bInTransaction )
        {
            throw new sr_Database_Exception( 'Can not create savepoint. Transaction is not open.', sr_Database_Exception::TRANSACTION_NOT_OPEN );
        }
        
        return $this->Execute( 'SAVEPOINT ' . $psName . ';' );
    }
    
    /***
    * Roll the transaction back to a savepoint.
    * @access    public
    * @param    string $psSavepoint            The name of the savepoint to rollback to.
    * @return    boolean                        True if the transaction was rolled back to the savepoint, false otherwise.
    */    
    public function RollbackToSavepoint( $psSavepoint )
    {
        if( !ctype_alpha( (string)$psSavepoint ) )
        {
            throw new sr_Database_Exception( 'Parameter for CreateSavepoint must contain only alphabetic characters.', sr_Database_Exception::WRONG_DATATYPE );
        }
        
        if( !$this->bIsConnected )
        {
            throw new sr_Database_Exception( 'Can not roll back transaction, connection is not open.', sr_Database_Exception::CONNECTION_NOT_OPEN );
        }
        
        if( !$this->bInTransaction )
        {
            throw new sr_Database_Exception( 'Can not roll back transaction. Transaction is not open.', sr_Database_Exception::TRANSACTION_NOT_OPEN );
        }
        
        return $this->Execute( 'ROLLBACK ' . $psSavepoint . ';' );
    }

    /***
    * Remove the stored savepoint.
    * @access    public
    * @param    string $psSavepoint            The name of the savepoint to remove.
    * @return    boolean                        True if the savepoint was released, false otherwise.
    */    
    public function ReleaseSavepoint( $psSavepoint )
    {
        if( !ctype_alpha( (string)$psSavepoint ) )
        {
            throw new sr_Database_Exception( 'Parameter for CreateSavepoint must contain only alphabetic characters.', sr_Database_Exception::WRONG_DATATYPE );
        }
        
        if( !$this->bIsConnected )
        {
            throw new sr_Database_Exception( 'Can not release savepoint, connection is not open.', sr_Database_Exception::CONNECTION_NOT_OPEN );
        }

        return $this->Execute( 'RELEASE ' . $psSavepoint . ';' );
    }
        
}

?>

Database/Connection/Interface.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php

interface sr_Database_Connection_Interface
{
    public function __construct( Array $paConnectionData );
    public function __destruct( );
    public function OpenConnection( Array $paConnectionData, $pbForceNew = false );
    public function CloseConnection( );
    public function Execute( $psQuery );
    public function Query( $psQuery );
    public function QueryParams( $psQuery, Array $paParams );
    public function Escape( $psParam );
    public function EscapeBytea( $psParam );    
    public function GetNextSequence( $psSequence );
    public function StartTransaction( );
    public function Commit( );
    public function Rollback( );
    public function CreateSavepoint( $psName );
    public function RollbackToSavepoint( $psSavepoint );
    public function ReleaseSavepoint( $psSavepoint );    
    public function GetLastError( );
}

?>

Database/Exception.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php

class sr_Database_Exception extends Exception
{
    const CONNECTION_FAILED = 100;
    const CONNECTION_NOT_OPEN = 101;
    const CONNECTION_ALREADY_OPEN = 102;
    const TRANSACTION_NOT_OPEN = 110;
    const TRANSACTION_ALREADY_OPEN = 111;
    const TRANSACTION_STILL_OPEN = 112;
    const QUERY_FAILED = 120;
    const WRONG_DATATYPE = 200;
    
    public function __toString( )
    {
        return '<div style="border: 1px solid red;">
                    An Exception was thrown with the message: <br />' . $this->getMessage( ) . '<br /><br /> 
                    Trace: <pre>' . print_r( $this->getTrace( ), true ) . '</pre>' . 
                '</div>';
                    
    }
}

?>

Database/MySQL/Connection.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
<?php

class sr_Database_MySQL_Connection extends sr_Database_Connection_Base implements sr_Database_Connection_Interface
{
    /***
    * Constructor, opens a new database connection with the given connection data.
    * @access    public
    * @param    array $paConnectionData        The data needed for connecting to the database.
    * @return    object                        A new PostgreSQLDatabaseConnection object.
    */
    public function __construct( Array $paConnectionData )
    {
        $this->rDatabaseConnection = @mysql_connect( $paConnectionData['sHost'] . ':' . $paConnectionData['iPort'], $paConnectionData['sUser'], $paConnectionData['sPassword'] );
        
        if( !is_resource( $this->rDatabaseConnection ) )
            throw new sr_Database_Exception( 'Connecting to the database failed.', sr_Database_Exception::CONNECTION_FAILED );
            
        if( !mysql_select_db( $paConnectionData['sDatabase'], $this->rDatabaseConnection ) )
            throw new sr_Database_Exception( 'Can not connect. Unknown database name.', sr_Database_Exception::CONNECTION_FAILED );
        
        $this->bIsConnected = true;
    }
    
    /***
    * Close the connection before cleanup.
    * @access    public
    * @return    void
    */
    public function __destruct( )
    {
        $this->CloseConnection( );
    }

    /***
    * Open a connection to the database (or open a new connection)
    * @access    public
    * @param    array $paConnectionData        The data needed for connecting to the database.
    * @param    boolean    $pbForceNew            Set to true if a new connection must be made, although a connection is already open.
    * @return    boolean                        True if the connection could be established, false otherwise.
    */
    public function OpenConnection( Array $paConnectionData, $pbForceNew = false )
    {
        if( $this->bIsConnected && !$pbForceNew )
            throw new sr_Database_Exception( 'The connection was already open.', sr_Database_Exception::CONNECTION_ALREADY_OPEN );

        $this->rDatabaseConnection = @mysql_connect( $paConnectionData['sHost'] . ':' . $paConnectionData['iPort'], $paConnectionData['sUser'], $paConnectionData['sPassword'] );
        mysql_select_db( $paConnectionData['sDatabase'], $this->rDatabaseConnection );
        
        if( !mysql_select_db( $paConnectionData['sDatabase'], $this->rDatabaseConnection ) )
            throw new sr_Database_Exception( 'Can not connect. Unknown database name.', sr_Database_Exception::CONNECTION_FAILED );        
        
        if( is_resource( $this->rDatabaseConnection ) )
        {
            $this->bIsConnected = true;
            return true;
        }
        else
        {
            $this->bIsConnected = false;
            return false;
        }
    }

    /***
    * Close the current database connection.
    * @access    public
    * @return    boolean                        Returns true on success or false on failure.
    */    
    public function CloseConnection( )
    {
        if( !$this->bIsConnected )
            throw new sr_Database_Exception( 'There was no open connection to close', sr_Database_Exception::CONNECTION_NOT_OPEN );
        
        return @mysql_close( $this->rDatabaseConnection );
    }

    /***
    * Execute a query and return only if the query was successfull or not.
    * @access    public
    * @param    string    $psQuery            The query that will be executed.
    * @return    boolean                        True if the query was successfull, false otherwise.
    */
    public function Execute( $psQuery )
    {
        if( !$this->bIsConnected )
            throw new sr_Database_Exception( 'Can not execute query on a closed connection', sr_Database_Exception::CONNECTION_NOT_OPEN  );
            
        return is_resource( mysql_query( $psQuery, $this->rDatabaseConnection ) );
    }
    
    /***
    * Execute a query and return a result object.
    * @access    public
    * @param    string    $psQuery            The query that will be executed.
    * @return    object                        The database result object for handling the result.
    */        
    public function Query( $psQuery )
    {
        if( !$this->bIsConnected )
            throw new sr_Database_Exception( 'Can not execute query on a closed connection', sr_Database_Exception::CONNECTION_NOT_OPEN  );
            
        if( false === $rResult = mysql_query( $psQuery, $this->rDatabaseConnection ) )
            throw new sr_Database_Exception( 'Query failed.', sr_Database_Exception::QUERY_FAILED );
        
        return new sr_Database_MySQL_Result( $rResult );
    }

    /***
    * Execute a query with parameters and return a result object.
    * @access    public
    * @param    string    $psQuery            The query that will be executed.
    * @param    array    $paParams            The parameters for the query.
    * @return    object                        The database result object for handling the result.
    */        
    public function QueryParams( $psQuery, Array $paParams )
    {
        //if( !$this->bIsConnected )
        //    throw new sr_Database_Exception( 'Can not execute query on a closed connection', sr_Database_Exception::CONNECTION_NOT_OPEN  );
            
        //if( !is_resource( $rResult = @pg_query_params( $this->rDatabaseConnection, $psQuery, $paParams ) ) )
        //    throw new sr_Database_Exception( 'Query failed.', sr_Database_Exception::QUERY_FAILED );
        
        //return new sr_Database_MySQL_Result( $rResult );
    }
    
    /***
    * Escape a parameter for save executing in a query.
    * @access    public
    * @param    string    $psParam            The parameter thats needs escaping.
    * @return    string                        The escaped parameter.
    */        
    public function Escape( $psParam )
    {
        return mysql_real_escape_string( $psParam, $this->rDatabaseConnection );
    }

    /***
    * Escape a bytea parameter for save executing in a query.
    * @access    public
    * @param    binary    $psParam            The parameter thats needs escaping.
    * @return    string                        The escaped parameter.
    */        
    public function EscapeBytea( $psParam )
    {
        
    }

    /***
    * prepare a query before executing.
    * @access    public
    * @param    string    $psStatementName    The name of the prepared statement.
    * @param    string    $psQuery            The query to prepare.
    * @return    object                        The result object.
    */        
    public function Prepare( $psStatementName, $psQuery )
    {
        if( !$this->bIsConnected )
            throw new sr_Database_Exception( 'Can not prepare statement on a closed connection', sr_Database_Exception::CONNECTION_NOT_OPEN  );
            
        //if( !is_resource( $rResult = pg_prepare( $this->rDatabaseConnection, $psStatementName, $psQuery ) ) )
        //    throw new sr_Database_Exception( 'Can not prepare statement.', sr_Database_Exception::QUERY_FAILED );

        return new sr_Database_MySQL_Result( $rResource );
    }
    
}
    
?>

Database/MySQL/Result.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
<?php

class sr_Database_MySQL_Result implements sr_Database_Result_Interface
{
    /***
    * Create a new PostgreSQLDatabaseResult object.
    * @access    public
    * @param    resource $prResultset    The result resource of the executed query.
    * @return    object                    A new PostgreSQLDatabaseResult object.
    */
    public function __construct( $prResultset )
    {
        $this->rResultset = $prResultset;
        $this->iRowCount = ( false != @mysql_num_rows( $prResultset ) ) ? @mysql_num_rows( $prResultset ) : @mysql_affected_rows( $prResult ) ;
    }
    
    /***
    * Remove all data.
    * @access    public
    * @return    void
    */    
    public function __destruct( )
    {
        @mysql_free_result( $this->rResultset );
    }

    /***
    * Get the next row in the resultset.
    * @access    public
    * @return    mixed                    An array containing the row, or false if all rows are already fetched.
    */    
    public function Fetch( )
    {
        $this->iResultPointer++;
        return @mysql_fetch_assoc( $this->rResultset );
    }
    
    /***
    * Get all the rows in the resultset.
    * @access    public
    * @return    Array                    An multidimensional array containing all the rows.
    */    
    public function FetchAll( )
    {
        $aRows = array( );
        while( $aRecord = @mysql_fetch_assoc( $this->rResultset ) )
        {
            $aRows[] = $aRecord;
        }
        return $aRows;
    }
    
    /***
    * Set the internal row offset for the result resource
    * @access    public
    * @param    integer $piOffset        The new offset for the result pointer.
    * @return    boolean                    True if the internet pointer could be set.
    */
    public function DataSeek( $piOffset )
    {
        if( !ctype_digit( (string)$piOffset ) )
            throw new DataTypeException( 'Parameter $piOffset must be of type integer, ' . get_type( $piOffset ) . ' given.', DataTypeException::WRONG_DATATYPE);
        
        $this->iResultPointer = $piOffset;
        if( mysql_data_seek( $this->rResultset, $piOffset ) )
        {
            $this->iResultPointer = $piOffset;
            return true;
        }
        else
        {
            return false;
        }
    }

    /***
    * Frees the memory and data associated with the result resource.
    * @access    public
    * @return    boolean                    True if the result could be freed, false otherwise.
    */    
    public function FreeResult( )
    {
        return @mysql_free_result( $this->rResultset );
    }
}

?>

Database/PostgreSQL/Connection.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
<?php

class sr_Database_PostgreSQL_Connection extends sr_Database_Connection_Base implements sr_Database_Connection_Interface
{
    /***
    * Constructor, opens a new database connection with the given connection data.
    * @access    public
    * @param    array $paConnectionData        The data needed for connecting to the database.
    * @return    object
    */
    public function __construct( Array $paConnectionData )
    {
        if( false === $this->rConnection = @pg_connect( 'host=' . $paConnectionData['sHost'] . ' user=' . $paConnectionData['sUser'] . ' port=' . $paConnectionData['iPort'] . ' password=' . $paConnectionData['sPassword'] . ' dbname=' . $paConnectionData['sDatabase'] ) )
        {
            throw new sr_Database_Exception( 'Could not connect to the database.', sr_Database_Exception::CONNECTION_FAILED );
        }
        
        $this->bIsConnected = true;
    }
    
    /***
    * Close the connection before cleanup.
    * @access    public
    * @return    void
    */
    public function __destruct( )
    {
        if( $this->bInTransaction )
        {
            # Do we really need to throw this Exception?!?
            throw new sr_Database_Exception( 'Connection was closed before ending a transaction.', sr_Database_Exception::TRANSACTION_STILL_OPEN );
        }
        
        $this->CloseConnection( );
    }

    /***
    * Open a connection to the database (or open a new connection).
    * @access    public
    * @param    array $paConnectionData        The data needed for connecting to the database.
    * @param    boolean    $pbForceNew            Set to true for forcing a new connection.
    * @return    void
    */
    public function OpenConnection( Array $paConnectionData, $pbForceNew = false )
    {
        if( !is_boolean( $pbForceNew ) )
        {
            throw new sr_Database_Exception( 'Parameter $pbForceNew of function OpenConnection must be of type boolean, ' . get_type( $pbForceNew ) . ' given.', sr_Database_Exception::WRONG_DATATYPE );
        }

        if( $this->bIsConnected && !$pbForceNew )
        {
            throw new sr_Database_Exception( 'The connection was already open.', sr_Database_Exception::CONNECTION_ALREADY_OPEN );
        }
        
        if( false === $this->rConnection = @pg_connect( 'host=' . $paConnectionData['sHost'] . ' user=' . $paConnectionData['sUser'] . ' port=' . $paConnectionData['iPort'] . ' password=' . $paConnectionData['sPassword'] . ' dbname=' . $paConnectionData['sDatabase'] ) )
        {
            throw new sr_Database_Exception( 'Could not connect to the database.', sr_Database_Exception::CONNECTION_FAILED );
        }

        $this->bIsConnected = true;
    }

    /***
    * Close the current database connection.
    * @access    public
    * @return    boolean
    */    
    public function CloseConnection( )
    {
        if( !$this->bIsConnected )
        {
            # Do we really need to throw this Exception?!?
            throw new sr_Database_Exception( 'There was no open connection to close', sr_Database_Exception::CONNECTION_NOT_OPEN );
        }
        
        return @pg_close( $this->rConnection );
    }

    /***
    * Execute a query and return only if the query was successfull or not.
    * @access    public
    * @param    string    $psQuery            The query that will be executed.
    * @return    boolean                        True if the query was successfull, false otherwise.
    */
    public function Execute( $psQuery )
    {
        if( !is_string( $psQuery ) )
        {
            throw new sr_Database_Exception( 'Parameter for function Execute must be of type string, ' . get_type( $psQuery ) . ' given.', sr_Database_Exception::WRONG_DATATYPE );
        }
        
        if( !$this->bIsConnected )
        {
            throw new sr_Database_Exception( 'Can not execute query on a closed connection', sr_Database_Exception::CONNECTION_NOT_OPEN  );
        }
        
        return ( false !== @pg_query( $this->rConnection, $psQuery ) );
    }
    
    /***
    * Execute a query and return a result object.
    * @access    public
    * @param    string    $psQuery            The query that will be executed.
    * @return    object                        The database result object for handling the result.
    */        
    public function Query( $psQuery )
    {
        if( !is_string( $psQuery ) )
        {
            throw new sr_Database_Exception( 'Parameter for function Query must be of type string, ' . get_type( $psQuery ) . ' given.', sr_Database_Exception::WRONG_DATATYPE );
        }
        
        if( !$this->bIsConnected )
        {
            throw new sr_Database_Exception( 'Can not execute query on a closed connection', sr_Database_Exception::CONNECTION_NOT_OPEN  );
        }
        
        if( false === $rResult = @pg_query( $this->rConnection, $psQuery ) )
        {
            throw new sr_Database_Exception( 'Query failed.', sr_Database_Exception::QUERY_FAILED );
        }
        
        return new sr_Database_PostgreSQL_Result( $rResult );
    }

    /***
    * Execute a query with parameters and return a result object.
    * @access    public
    * @param    string    $psQuery            The query that will be executed.
    * @param    array    $paParams            The parameters for the query.
    * @return    object                        The database result object for handling the result.
    */        
    public function QueryParams( $psQuery, Array $paParams )
    {
        if( !is_string( $psQuery ) )
        {
            throw new sr_Database_Exception( 'Parameter for function QueryParams must be of type string, ' . get_type( $psQuery ) . ' given.', sr_Database_Exception::WRONG_DATATYPE );
        }
        
        if( !$this->bIsConnected )
        {
            throw new sr_Database_Exception( 'Can not execute query on a closed connection', sr_Database_Exception::CONNECTION_NOT_OPEN  );
        }
        
        if( false === $rResult = @pg_query_params( $this->rConnection, $psQuery, $paParams ) )
        {
            throw new sr_Database_Exception( 'Query failed.', sr_Database_Exception::QUERY_FAILED );
        }
        
        return new sr_Database_PostgreSQL_Result( $rResult );
    }
    
    /***
    * Escape a parameter for save executing in a query.
    * @access    public
    * @param    string    $psParam            The parameter thats needs escaping.
    * @return    string                        The escaped parameter.
    */        
    public function Escape( $psParam )
    {
        return @pg_escape_string( $this->rConnection, $psParam );
    }

    /***
    * Escape a bytea parameter for save executing in a query.
    * @access    public
    * @param    binary    $psParam            The parameter thats needs escaping.
    * @return    string                        The escaped parameter.
    */        
    public function EscapeBytea( $psParam )
    {
        return @pg_escape_bytea( $this->rConnection, $psParam );
    }

    /***
    * Get the next value from a named sequence.
    * @access    public
    * @param    string    $psSequence            The name of the prepared statement.
    * @return    integer                        The next value in the sequence.
    */        
    public function GetNextSequence( $psSequence )
    {
        if( !ctype_alpha( (string)$psSequence ) )
        {
            throw new sr_Database_Exception( 'Parameter for GetNextSequence must contain only alphabetic characters.', sr_Database_Exception::WRONG_DATATYPE );
        }
        
        $sGetNextSequence = 'SELECT nextval( $1 ) AS nextval';
        if( !is_object( $oResult = $this->QueryParams( $sGetNextSequence, array( $psSequence ) ) ) )
        {
            throw new sr_Database_Exception( 'Could not retrieve next value for "' . $psSequence . '"', sr_Database_Exception::QUERY_FAILED );
        }
        
        $aRecord = $oResult->Fetch( );
        return $aRecord['nextval'];
    }
    
    /***
    * Get the last error message from the database.
    * @access    public
    * @return    mixed                        Boolean false on failure, string with the last error message on success.
    */
    public function GetLastError( )
    {
        return @pg_last_error( );
    }
}
    
?>

Database/PostgreSQL/Result.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
<?php

class sr_Database_PostgreSQL_Result extends sr_Database_Result_Base implements sr_Database_Result_Interface
{
    /***
    * Constructor.
    * @access    public
    * @param    resource $prResultset        The result resource of the executed query.
    * @return    object                        A new PostgreSQLDatabaseResult object.
    */
    public function __construct( $prResultset )
    {
        $this->rResultset = $prResultset;
        $this->iRowCount = ( -1 == @pg_num_rows( $prResultset ) ) ? @pg_affected_rows( $prResult ) : @pg_num_rows( $prResultset );
    }
    
    /***
    * Remove all data before cleanup.
    * @access    public
    * @return    void
    */    
    public function __destruct( )
    {
        @pg_free_result( $this->rResultset );
    }

    /***
    * Get the next row in the resultset.
    * @access    public
    * @return    mixed                        An array containing the row, or false if all rows are already fetched.
    */    
    public function Fetch( )
    {
        return @pg_fetch_assoc( $this->rResultset, $this->iResultPointer++ );
    }
    
    /***
    * Get all the rows in the resultset.
    * @access    public
    * @return    Array                        An multidimensional array containing all the rows.
    */    
    public function FetchAll( )
    {
        return @pg_fetch_all( $this->rResultset );
    }
    
    /***
    * Set the internal row offset for the result resource
    * @access    public
    * @param    integer $piOffset            The new offset for the result pointer.
    * @return    boolean                        True if the internal pointer could be set.
    */
    public function DataSeek( $piOffset )
    {
        if( !ctype_digit( (string)$piOffset ) )
            throw new sr_Database_Exception( 'Parameter for function DataSeek must be a digit, ' . get_type( $piOffset ) . ' given.', sr_Database_Exception::WRONG_DATATYPE );
        
        if( @pg_result_seek( $this->rResultset, $piOffset ) )
        {
            $this->iResultPointer = $piOffset;
            return true;
        }
        else
        {
            return false;
        }
    }

    /***
    * Frees the memory and data associated with the result resource.
    * @access    public
    * @return    boolean                        True if the result could be freed, false otherwise.
    */    
    public function FreeResult( )
    {
        return @pg_free_result( $this->rResultset );
    }
}

?>

Database/Result/Base.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<?php

abstract class sr_Database_Result_Base
{
    /***
    * The resultset of the executed query.
    * @var         $rResultset
    * @access    protected
    * @type     resource
    */    
    protected $rResultset;

    /***
    * The internal result pointer.
    * @var         $iResultPointer
    * @access    protected
    * @type     resource
    */    
    protected $iResultPointer = 0;
    
    /***
    * The amount of rows in this resultset.
    * @var         $iRowCount
    * @access    public
    * @type     resource
    */    
    public $iRowCount;    
}

?>

Database/Result/Interface.php

1
2
3
4
5
6
7
8
9
10
11
12
13
<?php

interface sr_Database_Result_Interface
{
    public function __construct( $prResultset );
    public function __destruct( );
    public function Fetch( );
    public function FetchAll( );
    public function DataSeek( $piOffset );
    public function FreeResult( );
}

?>

Commentaar

05-12-2010 23:50

Ik zie net dat het zipje netjes wordt uitgepakt zodat de code zichtbaar wordt! Handig gedaan!

Ik zie verder ook dat er al een oudere versie van mijn MySQL versie erbij zit, deze moet ik nog aanpassen naar de nieuwe versie...

@ Mods: Wanneer komt de functie om een script aan te passen erbij? Ik zie nog nergens de 'Bewerk script'-knop.

06-12-2010 09:54

Maak van je result class een SeekableIterator of een ItteratorAggregate.
Ook Countable is hier wel handig.

Dan kan je het result object gewoon als array gebruiken.

06-12-2010 10:22

/me gaat Googlen! Wat een termen zeg, heb ze alle 3 nog nooit gehoord :D

06-12-2010 11:07

Met dank aan Berry om mijn vorige commentaar te verwijderen i.v.m. het verknoeien van de layout.

Je kunt inderdaad er ook een SeekableIterator van maken (deze leek voor mij het meest logisch van de 3), maar ik vind dit zelf niet handig.

Ik heb graag duidelijkheid in wat ik doe, dus als ik met een database werk, wil ik ook graag functienamen hebben die mij laten zien dat ik met een database werk: Fetch(), FreeResult(), DataSeek() etc.
Daarnaast verplicht die iterator dat functienamen met een kleine letter beginnen, terwijl ik juist altijd hoofdletters gebruik! Dit werkt niet makkelijk aangezien je nu altijd moet gissen of het hoofd- of kleine letters moeten zijn!

Ik heb een Iterator ervoor gemaakt, maar deze kan ik niet plaatsen aangezien de layout dus vernield wordt...

Bedankt voor je toevoeging Lode, maar ik blijf toch lekker bij mijn eigen maniertje (A)

06-02-2011 17:21

Persoonlijk ben ik geen voorstander van het gebruiken van de resultset in het resultobject, omdat het geen voordeel oplevert. PHP haalt sowieso altijd de hele resultset van de database af, dus in feite is het efficienter om direct een fetch_all te doen en de resultset weg te gooien. Dat scheelt een hoop werk bij het apart fetchen van de records.

Waarom maak ik daar een punt van: in de praktijk vraag je een set records op omdat je ze allemaal nodig hebt. je haalt niet 100 records op omdat je er maar drie nodig hebt, het gebeurt gewoon niet.
Ditto met het seeken, ik seek nooit, ik reset de seekppointer niet eens. Als het nodig is om twee keer door een resultset te wandelen dan is er al iets mis met je ontwerp; als twee taken dezelfde data nodig hebben dan zou de ene taak de data door moeten geven aan de ander, niet er vannuit gaan dat de data in een resultset klopt.

Nieuwe Topics

Scripts

Wiki's

Vacatures

Nieuwsberichten

Software Updates

Inloggen wachtwoord vergeten? Aanmelden