Podstrony
|
[ Pobierz całość w formacie PDF ]
move beyond simple queries you ll find it essential to have a reasonable understanding ofGROUP BY,
HAVINGand the aggregate options. If your database supports sub-queries, you ll find that you re able
to push more work on to it rather than having to do it in your code. FinallyUNIONand outer joins are
very handy things to understand.
The Cheetah book covers SQL more fully on pages 58 - 75.
Chapter summary
" TheSELECTstatement allows us to pull data out from the database.
" We can limit the amount of data we get out from aSELECTstatement by using conditional
selections.
" We can join tables to gain further information in ourSELECTstatements.
" To order data we can use theORDER BYinstruction.
" TheCREATEstatement allows us to create tables to store our data within.
" TheINSERTstatement allows us to add data to a table.
" TheUPDATEstatement allows us to change parts of our data in a row.
" TheDELETEstatement allows us to delete rows from the database that we don t need any longer.
Perl Training Australia (http://perltraining.com.au/) 109
Appendix B. Introduction to SQL
110 Perl Training Australia (http://perltraining.com.au/)
Appendix C. Remote connections and
persistence
In this chapter...
In this chapter we will discuss connecting to databases remotely, setting access controls on remote
connections and using encrypted connections. We will also cover persistent connections to databases
by caching database handles and the use ofApache::DBI.
Establishing a remote connection
It is not always possible, or even desirable, to have your programs run on the same machine as the
database application which you reference. In these cases you need some way to connect to the remote
server. Many databases (and therefore their database drivers) support remote connection natively. For
the other databases you need another strategy. TheDBD::Proxydriver can be used to connect to any
remote database and provides extra features such as encryption, compression and access controls.
Connecting with the database driver
Many databases, like MySQL support remote connections natively. To use this feature you pass the
hostname and port to the database driver through theDSNin your call to theconnectmethod.
my $database = "dbiX";
my $hostname = "example.perltraining.com.au";
my $port = 3306;
my $dsn = "dbi:mysql:database=$database;".
"host=$host;port=$port";
my $dbh = DBI->connect($dsn, $username, $password, { AutoCommit => 1 })
or die "Failed to connect to database: $DBI::errstr";
The order of the elements after the driver in theDSNdoesn t matter. The port number can be omitted
if the database server is running on its default port.
To discover whether your database driver can handle remote connections natively read its
documentation at perldoc DBD::drivername. For example the driver documentation for the
mysqldriver can be found at perldoc DBD::mysql.
Using DBD::Proxy
The Cheetah book covers theDBD::Proxyin more detail on pages 178 - 186.
Perl Training Australia (http://perltraining.com.au/) 111
Appendix C. Remote connections and persistence
Before connecting to a remote database withDBD::Proxyyou must ensure that a Proxy server is
running on the remote machine. Connecting to a remote database withDBD::Proxyis very similar to
connecting remotely via a database driver. The biggest difference is that you have to provide adsn
option in yourDSN. This is theDSNthat the Proxy server will use to connect to your database on the
remote server.
If your connection to the database would look like the following if your program was running on the
machine locally:
my $database = "dbiX";
my $dsn = "dbi:mysql:database=$database";
my $dbh = DBI->connect($dsn, $username, $password, { AutoCommit => 1 })
or die "Failed to connect to database: $DBI::errstr";
then your call toconnectto the remote server should look like this:
my $database = "dbiX";
my $hostname = "example.perltraining.com.au";
my $port = 3306;
my $host_dsn = "dbi:mysql:database=$database";
my $dsn = "dbi:Proxy:hostname=$hostname;port=$port;".
"dsn=$host_dsn";
my $dbh = DBI->connect($dsn, $username, $password, { AutoCommit => 1 })
or die "Failed to connect to database: $DBI::errstr";
To find out more aboutDBD::Proxyread its documentation at perldoc DBD::Proxy.
Exercise
DBD::Proxycan be used to connect to local proxy servers. Your instructor will inform you of the
hostname and port to provide in your connection.
1. Connect to the proxy and run a simple select command.
Running the proxy server
TheDBD::Proxydriver requires a DBI Proxy server to be running on the remote machine. This
Proxy server can be created by using theDBI::ProxyServermodule. This module runs as a daemon
on the machine with the database application and is implemented as aRPC::PlServerapplication.
DBI::ProxyServerprovides a driver programdbiproxywhich can be used as is, or modified to meet
your needs.
DBI::ProxyServertakes a number of configuration options and must be given a value for
localport. We will discuss some of these options in greater detail in the following sections.
To learn more aboutDBI::ProxyServerread its documentation at perldoc DBI::ProxyServer.
112 Perl Training Australia (http://perltraining.com.au/)
Appendix C. Remote connections and persistence
Access control
By careful choice of configuration options forDBI::ProxyServeryou can restrict which machines
can connect to your Proxy server, which users have access and even what SQL statements can be
executed. This configuration must be specified in the configuration file. The name of the
configuration file should be passed todbiproxyon startup.
This access control goes in the theclientslist. This list is an array of hashes with each hash
covering one set of permissions. The array is searched from top to bottom until access is either
permitted or denied.
The permission hashes have four primary keys:
1.mask: a regular expression which is matched against the hostname or IP address of the remote
client
2.accept: whether to allow or deny access if these criteria are met
3.users: a list of usernames to verify
4.sql: a hash of which SQL statements are covered by this criteria.
Any of themask,usersandsqlkeys may be omitted. A missingmaskimplies all hosts, a missing
userslist implies all users, a missingsqlhash implies all SQL.
clients => [
{
mask => ^example\.perltraining\.com\.au$ ,
accept => 1,
users => [ dbi1 , dbi2 , dbi3 , dbi4 , dbi5 , dbi6 ],
sql => {
select_all_staff => SELECT * FROM Staff ,
select_all_phone => SELECT * FROM StaffPhone ,
select_all => SELECT *
FROM Staff s,
Projects proj,
StaffPhone ph
WHERE s.StaffID = proj.StaffID AND
s.StaffID = ph.StaffID ,
select_staff_wage => SELECT *
FROM Staff s,
Projects proj,
WHERE s.StaffID = proj.StaffID AND
s.StaffID = ? ,
}
},
{
mask => perltraining\.com\.au$ ,
accept => 1,
users => [ pjf , jarich ],
},
{
accept => 0,
}
],
The above access list allowspjfandjarichto connect from any machine in the
perltraining.com.aunetwork and execute any SQL statement. Usersdbi1through todbi6are only
allowed if they connect fromexample.perltraining.com.auand may only run the four select
statements specified. Finally we specify that any situation not covered by the previous controls is not
accepted.
Perl Training Australia (http://perltraining.com.au/) 113
Appendix C. Remote connections and persistence
By specifying the SQL allowed to be executed we remove the need for the client to craft the SQL
statements. Instead the client must call these SQL statements by the name we ve given them in the
sqlhash. For example:
my $sth = $dbh->prepare("select_staff_wage");
$sth->execute($staffid);
Exercise
Your instructor will inform you of your username and password for this exercise.
1. Modify your previous program to use your new username and password. Try one of the SQL
[ Pobierz całość w formacie PDF ]
zanotowane.pldoc.pisz.plpdf.pisz.plkskarol.keep.pl
|
