DBILayer.pm 9.47 KB
Newer Older
Nigel Kukard's avatar
Nigel Kukard committed
1
# Database independent layer module
Nigel Kukard's avatar
Nigel Kukard committed
2
# Copyright (C) 2009-2014, 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
31
32
33
34
35
36
37
38
39
40

use strict;
use warnings;


use DBI;



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


## @internal
Nigel Kukard's avatar
Nigel Kukard committed
41
# @fn _error($err)
Nigel Kukard's avatar
Nigel Kukard committed
42
43
44
# This function is used to set the last error for this class
#
# @param err Error message
Nigel Kukard's avatar
Nigel Kukard committed
45
sub _error
Nigel Kukard's avatar
Nigel Kukard committed
46
47
48
49
50
51
52
53
54
{
	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
55
56


Nigel Kukard's avatar
Nigel Kukard committed
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
## @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
73

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

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

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

	# Return error
	return $err;
}



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


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

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

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

	return $dbh;
}


Nigel Kukard's avatar
Nigel Kukard committed
124

Nigel Kukard's avatar
Nigel Kukard committed
125
126
127
## @member new($dsn,$username,$password)
# Class constructor
#
128
129
130
131
132
133
134
135
136
# @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
137
138
139
140
#
# @return Constructed object, undef on error
sub new
{
141
142
143
144
145
	my ($class,$settings) = @_;


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

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

		_dbh => undef,
		_error => undef,

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

		_table_prefix => "",

		_in_transaction => undef,
	};

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

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

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

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

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

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

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

221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
	# 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
243
244
245
246
	return 0;
}


Nigel Kukard's avatar
Nigel Kukard committed
247

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

	return $self->{_type};
}


Nigel Kukard's avatar
Nigel Kukard committed
260

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


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

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

	return 0;

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


Nigel Kukard's avatar
Nigel Kukard committed
292

Nigel Kukard's avatar
Nigel Kukard committed
293
294
295
296
297
298
299
300
301
302
303
## @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
304
	if ($self->_check()) {
Nigel Kukard's avatar
Nigel Kukard committed
305
		return;
Nigel Kukard's avatar
Nigel Kukard committed
306
	}
Nigel Kukard's avatar
Nigel Kukard committed
307
308
309

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

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

	return $sth;
}


Nigel Kukard's avatar
Nigel Kukard committed
325

Nigel Kukard's avatar
Nigel Kukard committed
326
327
328
329
330
331
332
333
334
335
336
## @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
337
	if ($self->_check()) {
Nigel Kukard's avatar
Nigel Kukard committed
338
		return;
Nigel Kukard's avatar
Nigel Kukard committed
339
	}
Nigel Kukard's avatar
Nigel Kukard committed
340

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

	return $sth;
}


Nigel Kukard's avatar
Nigel Kukard committed
352

Nigel Kukard's avatar
Nigel Kukard committed
353
354
355
356
357
358
359
360
361
362
363
364
## @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
365
	if ($self->_check()) {
Nigel Kukard's avatar
Nigel Kukard committed
366
		return;
Nigel Kukard's avatar
Nigel Kukard committed
367
368
	}

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

	return $res;
}


Nigel Kukard's avatar
Nigel Kukard committed
380

Nigel Kukard's avatar
Nigel Kukard committed
381
382
383
384
385
386
387
388
389
## @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
390
	if ($self->_check()) {
Nigel Kukard's avatar
Nigel Kukard committed
391
		return;
Nigel Kukard's avatar
Nigel Kukard committed
392
	}
Nigel Kukard's avatar
Nigel Kukard committed
393

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

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

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

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

	return $res;
}


Nigel Kukard's avatar
Nigel Kukard committed
417

Nigel Kukard's avatar
Nigel Kukard committed
418
419
420
421
422
423
424
425
426
## @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
427
	if ($self->_check()) {
Nigel Kukard's avatar
Nigel Kukard committed
428
		return;
Nigel Kukard's avatar
Nigel Kukard committed
429
430
	}

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

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

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

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

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

	return $res;
}


Nigel Kukard's avatar
Nigel Kukard committed
458

Nigel Kukard's avatar
Nigel Kukard committed
459
460
461
462
463
464
465
466
467
## @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
468
	if ($self->_check()) {
Nigel Kukard's avatar
Nigel Kukard committed
469
		$self->{'_in_transaction'}--;
Nigel Kukard's avatar
Nigel Kukard committed
470
		return;
Nigel Kukard's avatar
Nigel Kukard committed
471
472
	}

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

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

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

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

	return $res;
}


Nigel Kukard's avatar
Nigel Kukard committed
496

Nigel Kukard's avatar
Nigel Kukard committed
497
498
499
500
501
502
503
504
505
506
## @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
507
	return $self->{'_dbh'}->quote($variable);
Nigel Kukard's avatar
Nigel Kukard committed
508
509
510
}


Nigel Kukard's avatar
Nigel Kukard committed
511

Nigel Kukard's avatar
Nigel Kukard committed
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
## @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
527

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

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



1;
# vim: ts=4