DBILayer.pm 9.47 KB
Newer Older
Nigel Kukard's avatar
Nigel Kukard committed
1
# Database independent layer module
2
# Copyright (C) 2009-2017, AllWorldIT
Nigel Kukard's avatar
Nigel Kukard committed
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# Copyright (C) 2008, LinuxRulz
# Copyright (C) 2005-2007 Nigel Kukard  <nkukard@lbsd.net>
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program 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 General Public License for more details.
#
# You should have received a copy of the GNU General Public License along
# with this program; if not, write to the Free Software Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.




Nigel Kukard's avatar
Nigel Kukard committed
23
## @class AWITPT::DB::DBILayer
Nigel Kukard's avatar
Nigel Kukard committed
24
25
# Database independant layer module. This module encapsulates the DBI
# module and provides us with some tweaked functionality
Nigel Kukard's avatar
Nigel Kukard committed
26
package AWITPT::DB::DBILayer;
Nigel Kukard's avatar
Nigel Kukard committed
27
28
29
30

use strict;
use warnings;

31
our $VERSION = 1.01;
Nigel Kukard's avatar
Nigel Kukard committed
32
33
34
35
36
37
38
39
40
41

use DBI;



# Our current error message
my $error = "";


## @internal
Nigel Kukard's avatar
Nigel Kukard committed
42
# @fn _error($err)
Nigel Kukard's avatar
Nigel Kukard committed
43
44
45
# This function is used to set the last error for this class
#
# @param err Error message
Nigel Kukard's avatar
Nigel Kukard committed
46
sub _error
Nigel Kukard's avatar
Nigel Kukard committed
47
48
49
50
51
52
53
54
55
{
	my $err = shift;
	my ($package,$filename,$line) = caller;
	my (undef,undef,undef,$subroutine) = caller(1);

	# Set error
	$error = "$subroutine($line): $err";
}

Nigel Kukard's avatar
Nigel Kukard committed
56
57


Nigel Kukard's avatar
Nigel Kukard committed
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
## @fn internalError
# Return current module error message
#
# @return Last module error message
sub internalError
{
	my $err = $error;

	# Reset error
	$error = "";

	# Return error
	return $err;
}


Nigel Kukard's avatar
Nigel Kukard committed
74

75
## @member error
Nigel Kukard's avatar
Nigel Kukard committed
76
77
78
# Return current object error message
#
# @return Current object error message
79
sub error
Nigel Kukard's avatar
Nigel Kukard committed
80
81
82
{
	my $self = shift;

Nigel Kukard's avatar
Nigel Kukard committed
83
	my $err = $self->{'_error'};
Nigel Kukard's avatar
Nigel Kukard committed
84
85

	# Reset error
Nigel Kukard's avatar
Nigel Kukard committed
86
	$self->{'_error'} = "";
Nigel Kukard's avatar
Nigel Kukard committed
87
88
89
90
91
92
93
94

	# Return error
	return $err;
}



## @fn Init($server,$server_name)
95
# Initialize a class and return a dbilayer object
Nigel Kukard's avatar
Nigel Kukard committed
96
97
98
99
100
101
102
#
# @param server Server object
# @param server_name Name of server
#
# @return dbilayer object, undef on error
sub Init
{
103
	my ($server,$server_name) = @_;
Nigel Kukard's avatar
Nigel Kukard committed
104
105
106


	if (!defined($server)) {
Nigel Kukard's avatar
Nigel Kukard committed
107
		_error("Server object undefined");
Nigel Kukard's avatar
Nigel Kukard committed
108
		return;
Nigel Kukard's avatar
Nigel Kukard committed
109
110
	}
	if (!defined($server_name)) {
Nigel Kukard's avatar
Nigel Kukard committed
111
		_error("Server name undefined");
Nigel Kukard's avatar
Nigel Kukard committed
112
		return;
Nigel Kukard's avatar
Nigel Kukard committed
113
114
115
116
117
	}

	my $dbconfig = $server->{$server_name}->{'database'};

	# Check if we created
Nigel Kukard's avatar
Nigel Kukard committed
118
	my $dbh = AWITPT::DB::DBILayer->new($dbconfig);
Nigel Kukard's avatar
Nigel Kukard committed
119
	return if (!defined($dbh));
Nigel Kukard's avatar
Nigel Kukard committed
120
121
122
123
124

	return $dbh;
}


Nigel Kukard's avatar
Nigel Kukard committed
125

Nigel Kukard's avatar
Nigel Kukard committed
126
127
128
## @member new($dsn,$username,$password)
# Class constructor
#
129
130
131
132
133
134
135
136
137
# @param settings Database settings hashref
# @li DSN Data source name
# @li Username Username to use
# @li Password Password to use
# @li TablePrefix Table prefix
# @li IgnoreTransactions Flag to ignore transactions
# @li SQLiteJournalMode SQLite: set journal mode
# @li SQLiteCacheSize SQLite: set cache size
# @li SQLiteSynchronous SQLite: set synchronous mode
Nigel Kukard's avatar
Nigel Kukard committed
138
139
140
141
#
# @return Constructed object, undef on error
sub new
{
142
143
144
145
146
	my ($class,$settings) = @_;


	# Check if we were given settings
	if (!defined($settings)) {
Nigel Kukard's avatar
Nigel Kukard committed
147
		_error("No database settings given");
148
	}
Nigel Kukard's avatar
Nigel Kukard committed
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166

	# Iternals
	my $self = {
		_type => undef,

		_dbh => undef,
		_error => undef,

		_dsn => undef,
		_username => undef,
		_password => undef,

		_table_prefix => "",

		_in_transaction => undef,
	};

	# Set database parameters
167
168
169
170
	if (defined($settings->{'DSN'})) {
		$self->{_dsn} = $settings->{'DSN'};
		$self->{_username} = $settings->{'Username'};
		$self->{_password} = $settings->{'Password'};
Nigel Kukard's avatar
Nigel Kukard committed
171
		$self->{'_table_prefix'} = $settings->{'TablePrefix'} || "";
172

Nigel Kukard's avatar
Nigel Kukard committed
173
		$self->{'transactions_ignore'} = $settings->{'IgnoreTransactions'};
174
175
176
177
178

		$self->{'sqlite_journal_mode'} = $settings->{'SQLiteJournalMode'};
		$self->{'sqlite_cache_size'} = $settings->{'SQLiteCacheSize'};
		$self->{'sqlite_synchronous'} = $settings->{'SQLiteSynchronous'};

Nigel Kukard's avatar
Nigel Kukard committed
179
	} else {
Nigel Kukard's avatar
Nigel Kukard committed
180
		_error("No DSN provided");
Nigel Kukard's avatar
Nigel Kukard committed
181
		return;
Nigel Kukard's avatar
Nigel Kukard committed
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
	}

	# Try grab database type
	$self->{_dsn} =~ /^DBI:([^:]+):/i;
	$self->{_type} = (defined($1) && $1 ne "") ? lc($1) : "unknown";

	# Create...
	bless $self, $class;
	return $self;
}



## @member connect(@params)
# Return connection to database
#
# @param params DBI parameters
#
# @return 0 on success, < 0 on error
sub connect
{
	my $self = shift;


Nigel Kukard's avatar
Nigel Kukard committed
206
	$self->{'_dbh'} = DBI->connect($self->{_dsn}, $self->{_username}, $self->{_password}, {
Nigel Kukard's avatar
Nigel Kukard committed
207
208
			'AutoCommit' => 1,
			'PrintError' => 0,
209
			'RaiseError' => 0,
Nigel Kukard's avatar
Nigel Kukard committed
210
211
212
213
			'FetchHashKeyName' => 'NAME_lc'
	});

	# Connect to database if we have to, check if we ok
Nigel Kukard's avatar
Nigel Kukard committed
214
215
	if (!$self->{'_dbh'}) {
		$self->{'_error'} = "Error connecting to database: $DBI::errstr";
Nigel Kukard's avatar
Nigel Kukard committed
216
217
218
219
		return -1;
	}

	# Apon connect we are not in a transaction
Nigel Kukard's avatar
Nigel Kukard committed
220
	$self->{'_in_transaction'} = 0;
Nigel Kukard's avatar
Nigel Kukard committed
221

222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
	# Check for SQLite options
	if ($self->{_type} eq "sqlite") {
		# Check for journal mode
		if (defined($self->{'sqlite_journal_mode'})) {
			if (!$self->do("PRAGMA journal_mode = ".$self->{'sqlite_journal_mode'})) {
				return -1;
			}
		}
		# Check for cache size
		if (defined($self->{'sqlite_cache_size'})) {
			if (!$self->do("PRAGMA cache_size = -".$self->{'sqlite_cache_size'})) {
				return -1;
			};
		}
		# Check for synchronous setting
		if (defined($self->{'sqlite_synchronous'})) {
			if (!$self->do("PRAGMA synchronous = ".$self->{'sqlite_synchronous'})) {
				return -1;
			}
		}
	}

Nigel Kukard's avatar
Nigel Kukard committed
244
245
246
247
	return 0;
}


Nigel Kukard's avatar
Nigel Kukard committed
248

Nigel Kukard's avatar
Nigel Kukard committed
249
250
251
252
253
254
255
256
257
258
259
260
## @member type
# Return database type
#
# @return Database type string
sub type
{
	my $self = shift;

	return $self->{_type};
}


Nigel Kukard's avatar
Nigel Kukard committed
261

Nigel Kukard's avatar
Nigel Kukard committed
262
263
264
265
266
267
268
## @member _check
# Check database connection and reconnect if we lost the connection
sub _check
{
	my $self = shift;


Nigel Kukard's avatar
Nigel Kukard committed
269
	# DB is disconnected if _dbh is not defined
Nigel Kukard's avatar
Nigel Kukard committed
270
	if (!defined($self->{'_dbh'})) {
Nigel Kukard's avatar
Nigel Kukard committed
271
272
273
274
		goto RECONNECT;
	}

	# Try ping
Nigel Kukard's avatar
Nigel Kukard committed
275
	if (!$self->{'_dbh'}->ping()) {
Nigel Kukard's avatar
Nigel Kukard committed
276
		# If we not in a transaction try connect
Nigel Kukard's avatar
Nigel Kukard committed
277
		if ($self->{'_in_transaction'} == 0) {
Nigel Kukard's avatar
Nigel Kukard committed
278
			# Disconnect & reconnect
Nigel Kukard's avatar
Nigel Kukard committed
279
			$self->{'_dbh'}->disconnect();
Nigel Kukard's avatar
Nigel Kukard committed
280
			goto RECONNECT;
Nigel Kukard's avatar
Nigel Kukard committed
281
		}
Nigel Kukard's avatar
Nigel Kukard committed
282
		$self->{'_error'} = "Cannot reconnect to DB while inside transaction";
Nigel Kukard's avatar
Nigel Kukard committed
283
		return -1;
Nigel Kukard's avatar
Nigel Kukard committed
284
	}
Nigel Kukard's avatar
Nigel Kukard committed
285
286
287
288
289

	return 0;

RECONNECT:
	return $self->connect();
Nigel Kukard's avatar
Nigel Kukard committed
290
291
292
}


Nigel Kukard's avatar
Nigel Kukard committed
293

Nigel Kukard's avatar
Nigel Kukard committed
294
295
296
297
298
299
300
301
302
303
304
## @member select($query)
# Return database selection results...
#
# @param query SQL query
#
# @return DBI statement handle object, undef on error
sub select
{
	my ($self,$query,@params) = @_;


Nigel Kukard's avatar
Nigel Kukard committed
305
	if ($self->_check()) {
Nigel Kukard's avatar
Nigel Kukard committed
306
		return;
Nigel Kukard's avatar
Nigel Kukard committed
307
	}
Nigel Kukard's avatar
Nigel Kukard committed
308
309
310

	# Prepare query
	my $sth;
Nigel Kukard's avatar
Nigel Kukard committed
311
312
	if (!($sth = $self->{'_dbh'}->prepare($query))) {
		$self->{'_error'} = $self->{'_dbh'}->errstr;
Nigel Kukard's avatar
Nigel Kukard committed
313
		return;
Nigel Kukard's avatar
Nigel Kukard committed
314
315
316
317
	}

	# Check for execution error
	if (!$sth->execute(@params)) {
Nigel Kukard's avatar
Nigel Kukard committed
318
		$self->{'_error'} = $self->{'_dbh'}->errstr;
Nigel Kukard's avatar
Nigel Kukard committed
319
		return;
Nigel Kukard's avatar
Nigel Kukard committed
320
321
322
323
324
325
	}

	return $sth;
}


Nigel Kukard's avatar
Nigel Kukard committed
326

Nigel Kukard's avatar
Nigel Kukard committed
327
328
329
330
331
332
333
334
335
336
337
## @member do($command)
# Perform a command
#
# @param command Command to execute
#
# @return DBI statement handle object, undef on error
sub do
{
	my ($self,$command,@params) = @_;


Nigel Kukard's avatar
Nigel Kukard committed
338
	if ($self->_check()) {
Nigel Kukard's avatar
Nigel Kukard committed
339
		return;
Nigel Kukard's avatar
Nigel Kukard committed
340
	}
Nigel Kukard's avatar
Nigel Kukard committed
341

Nigel Kukard's avatar
Nigel Kukard committed
342
	# Do the query
Nigel Kukard's avatar
Nigel Kukard committed
343
	my $sth;
Nigel Kukard's avatar
Nigel Kukard committed
344
345
	if (!($sth = $self->{'_dbh'}->do($command,undef,@params))) {
		$self->{'_error'} = $self->{'_dbh'}->errstr;
Nigel Kukard's avatar
Nigel Kukard committed
346
		return;
Nigel Kukard's avatar
Nigel Kukard committed
347
348
349
350
351
352
	}

	return $sth;
}


Nigel Kukard's avatar
Nigel Kukard committed
353

Nigel Kukard's avatar
Nigel Kukard committed
354
355
356
357
358
359
360
361
362
363
364
365
## @method lastInsertID($table,$column)
# Function to get last insert id
#
# @param table Table last entry was inserted into
# @param column Column we want the last value for
#
# @return Last inserted ID, undef on error
sub lastInsertID
{
	my ($self,$table,$column) = @_;


Nigel Kukard's avatar
Nigel Kukard committed
366
	if ($self->_check()) {
Nigel Kukard's avatar
Nigel Kukard committed
367
		return;
Nigel Kukard's avatar
Nigel Kukard committed
368
369
	}

Nigel Kukard's avatar
Nigel Kukard committed
370
371
	# Get last insert id
	my $res;
Nigel Kukard's avatar
Nigel Kukard committed
372
373
	if (!($res = $self->{'_dbh'}->last_insert_id(undef,undef,$table,$column))) {
		$self->{'_error'} = $self->{'_dbh'}->errstr;
Nigel Kukard's avatar
Nigel Kukard committed
374
		return;
Nigel Kukard's avatar
Nigel Kukard committed
375
376
377
378
379
380
	}

	return $res;
}


Nigel Kukard's avatar
Nigel Kukard committed
381

Nigel Kukard's avatar
Nigel Kukard committed
382
383
384
385
386
387
388
389
390
## @method begin
# Function to begin a transaction
#
# @return 1 on success, undef on error
sub begin
{
	my $self = shift;


Nigel Kukard's avatar
Nigel Kukard committed
391
	if ($self->_check()) {
Nigel Kukard's avatar
Nigel Kukard committed
392
		return;
Nigel Kukard's avatar
Nigel Kukard committed
393
	}
Nigel Kukard's avatar
Nigel Kukard committed
394

Nigel Kukard's avatar
Nigel Kukard committed
395
	$self->{'_in_transaction'}++;
Nigel Kukard's avatar
Nigel Kukard committed
396
397

	# Don't really start transaction if we more than 1 deep
Nigel Kukard's avatar
Nigel Kukard committed
398
	if ($self->{'_in_transaction'} > 1) {
Nigel Kukard's avatar
Nigel Kukard committed
399
400
401
		return 1;
	}

402
	# Check if we need to ignore transactions
Nigel Kukard's avatar
Nigel Kukard committed
403
	if ($self->{'transactions_ignore'}) {
404
405
406
		return 1;
	}

Nigel Kukard's avatar
Nigel Kukard committed
407
408
	# Begin
	my $res;
Nigel Kukard's avatar
Nigel Kukard committed
409
410
	if (!($res = $self->{'_dbh'}->begin_work())) {
		$self->{'_error'} = $self->{'_dbh'}->errstr;
Nigel Kukard's avatar
Nigel Kukard committed
411
		return;
Nigel Kukard's avatar
Nigel Kukard committed
412
413
414
415
416
417
	}

	return $res;
}


Nigel Kukard's avatar
Nigel Kukard committed
418

Nigel Kukard's avatar
Nigel Kukard committed
419
420
421
422
423
424
425
426
427
## @method commit
# Function to commit a transaction
#
# @return DBI layer result, or 1 on deep transaction commit
sub commit
{
	my $self = shift;


Nigel Kukard's avatar
Nigel Kukard committed
428
	if ($self->_check()) {
Nigel Kukard's avatar
Nigel Kukard committed
429
		return;
Nigel Kukard's avatar
Nigel Kukard committed
430
431
	}

Nigel Kukard's avatar
Nigel Kukard committed
432
	# Reduce level
Nigel Kukard's avatar
Nigel Kukard committed
433
	$self->{'_in_transaction'}--;
Nigel Kukard's avatar
Nigel Kukard committed
434
435

	# If we not at top level, return success
Nigel Kukard's avatar
Nigel Kukard committed
436
	if ($self->{'_in_transaction'} > 0) {
Nigel Kukard's avatar
Nigel Kukard committed
437
438
439
440
		return 1;
	}

	# Reset transaction depth to 0
Nigel Kukard's avatar
Nigel Kukard committed
441
	$self->{'_in_transaction'} = 0;
Nigel Kukard's avatar
Nigel Kukard committed
442

443
	# Check if we need to ignore transactions
Nigel Kukard's avatar
Nigel Kukard committed
444
	if ($self->{'transactions_ignore'}) {
445
446
447
		return 1;
	}

Nigel Kukard's avatar
Nigel Kukard committed
448
449
	# Commit
	my $res;
Nigel Kukard's avatar
Nigel Kukard committed
450
451
	if (!($res = $self->{'_dbh'}->commit())) {
		$self->{'_error'} = $self->{'_dbh'}->errstr;
Nigel Kukard's avatar
Nigel Kukard committed
452
		return;
Nigel Kukard's avatar
Nigel Kukard committed
453
454
455
456
457
458
	}

	return $res;
}


Nigel Kukard's avatar
Nigel Kukard committed
459

Nigel Kukard's avatar
Nigel Kukard committed
460
461
462
463
464
465
466
467
468
## @method rollback
# Function to rollback a transaction
#
# @return DBI layer result or 1 on deep transaction
sub rollback
{
	my $self = shift;


Nigel Kukard's avatar
Nigel Kukard committed
469
	if ($self->_check()) {
Nigel Kukard's avatar
Nigel Kukard committed
470
		$self->{'_in_transaction'}--;
Nigel Kukard's avatar
Nigel Kukard committed
471
		return;
Nigel Kukard's avatar
Nigel Kukard committed
472
473
	}

Nigel Kukard's avatar
Nigel Kukard committed
474
	# If we at top level, return success
Nigel Kukard's avatar
Nigel Kukard committed
475
	if ($self->{'_in_transaction'} < 1) {
Nigel Kukard's avatar
Nigel Kukard committed
476
477
478
		return 1;
	}

Nigel Kukard's avatar
Nigel Kukard committed
479
	$self->{'_in_transaction'} = 0;
Nigel Kukard's avatar
Nigel Kukard committed
480

481
	# Check if we need to ignore transactions
Nigel Kukard's avatar
Nigel Kukard committed
482
	if ($self->{'transactions_ignore'}) {
483
484
485
		return 1;
	}

Nigel Kukard's avatar
Nigel Kukard committed
486
487
	# Rollback
	my $res;
Nigel Kukard's avatar
Nigel Kukard committed
488
489
	if (!($res = $self->{'_dbh'}->rollback())) {
		$self->{'_error'} = $self->{'_dbh'}->errstr;
Nigel Kukard's avatar
Nigel Kukard committed
490
		return;
Nigel Kukard's avatar
Nigel Kukard committed
491
492
493
494
495
496
	}

	return $res;
}


Nigel Kukard's avatar
Nigel Kukard committed
497

Nigel Kukard's avatar
Nigel Kukard committed
498
499
500
501
502
503
504
505
506
507
## @method quote($variable)
# Function to quote a database variable
#
# @param variable Variable to quote
#
# @return Quoted variable
sub quote
{
	my ($self,$variable) = @_;

Nigel Kukard's avatar
Nigel Kukard committed
508
	return $self->{'_dbh'}->quote($variable);
Nigel Kukard's avatar
Nigel Kukard committed
509
510
511
}


Nigel Kukard's avatar
Nigel Kukard committed
512

Nigel Kukard's avatar
Nigel Kukard committed
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
## @method free($sth)
# Function to cleanup DB query
#
# @param sth DBI statement handle
sub free
{
	my ($self,$sth) = @_;


	if ($sth) {
		$sth->finish();
	}
}


Nigel Kukard's avatar
Nigel Kukard committed
528

Nigel Kukard's avatar
Nigel Kukard committed
529
530
531
532
533
# Function to return the table prefix
sub table_prefix
{
	my $self = shift;

Nigel Kukard's avatar
Nigel Kukard committed
534
	return $self->{'_table_prefix'};
Nigel Kukard's avatar
Nigel Kukard committed
535
536
537
538
539
}



1;