· 8 years ago · Jan 02, 2018, 09:14 AM
1#!/usr/local/bin/perl
2
3use strict;
4use warnings;
5
6use DBI;
7
8package DatabaseConnection;
9
10my $dbh;
11
12# Creating a new connection
13sub connect {
14 my $class = shift;
15 my $self = {
16 _databaseSourceName => shift,
17 _username => shift,
18 _password => shift,
19 };
20
21 # using RaiseError to handle errors and automatically dies instead of
22 # writing "or die" explicitly in the code.
23 my %attr = ( PrintError => 0,
24 RaiseError => 1 );
25
26 $dbh = DBI->connect($self->{_databaseSourceName}, $self->{_username}, $self->{_password}, \%attr);
27
28 bless $self, $class;
29 return $self;
30}
31
32# Generic command execution
33sub execCommand {
34 my $class = shift;
35
36 # First parameter is the DBH
37 # Second one is the SQL Command
38 my $self = {_sqlCommand => shift };
39
40 # Preparing SQL Query
41 my $sth = $dbh->prepare($self->{_sqlCommand});
42
43 # Executing and Finishing the Query
44 $sth->execute();
45
46 my $all_rows = $dbh->selectall_arrayref($sth);
47 my @all_rows = @{ $all_rows }; # Dereference to create the array
48
49
50 $sth->finish();
51
52 return @all_rows;
53}
54
55# Create a table using provided parameters
56sub createTable {
57 my $class = shift;
58
59 # Second one is the SQL Command
60 my $self = {_tableName => shift,
61 _parameters => shift };
62
63 my $sth = $dbh->prepare("CREATE TABLE IF NOT EXISTS $self->{_tableName} ( $self->{_parameters} );");
64
65 # Executing and Finishing the Query
66 $sth->execute();
67 $sth->finish();
68
69
70 return $self;
71}
72
73# Dropping a table if exists
74sub dropTable {
75 my $class = shift;
76
77 # Second one is the SQL Command
78 my $self = { _tableName => shift };
79
80
81 my $sth = $dbh->prepare("DROP TABLE IF EXISTS $self->{_tableName};");
82
83 # Executing and Finishing the Query
84 $sth->execute();
85 $sth->finish();
86
87 return $self;
88}
89
90# Inserting data into a table
91# INSERT INTO table_name VALUES (value1, value2, ...);
92sub insert {
93 my $class = shift;
94
95 my $self = { _tableName => shift,};
96
97 my @values = @_;
98
99 foreach (@values) {
100 if (!$_) {
101 $_ = "' '"; # If there is undef value
102 } else {
103 $_ = "'" . $_ . "'";
104 }
105 }
106
107 # Constructing the values in appropriate form to be passed to the SQL query
108
109 @values = join(", ",@values);
110 #print @values, "\n\n";
111 my $sth = $dbh->prepare("INSERT INTO $self->{_tableName} VALUES (@values);");
112
113 # Executing and Finishing the Query
114 $sth->execute();
115 $sth->finish();
116
117 return $self;
118
119}
120
121# Retrieve data from database
122# SELECT * FROM table_name WHERE options;
123sub retrieve {
124 my $class = shift;
125
126 my $self = {_tableName => shift,
127 _conditions => shift};
128 my $sth;
129 if (!$self->{_conditions} ) {
130 # Selecting all rows from a single table
131 $sth = $dbh->prepare("SELECT * from $self->{_tableName}; ");
132 } else {
133 # Selecting specific rows based on the given conditions
134 $sth = $dbh->prepare("SELECT * from $self->{_tableName} WHERE $self->{_conditions}; ");
135 }
136
137
138 # Executing and Finishing the Query
139 $sth->execute();
140
141
142 my $all_rows = $dbh->selectall_arrayref($sth);
143 my @all_rows = @{ $all_rows }; # Dereference to create the array
144
145
146 $sth->finish();
147
148 return @all_rows;
149
150}
151
152
153
154
155# Disconnecting from MySQL database
156sub disconnect {
157 my $class = shift;
158 my $self = { };
159 $dbh->disconnect();
160
161 return $self;
162}
163
164
165# Return list of available tables in the database
166sub getTables {
167 my $class = shift;
168 my $self = { };
169
170 return $dbh->tables();
171}
172
173
1741;