Class :: DBI
ROM: Joe Jiang out: China Perl Association FPC (Foundation of Perlchina) Original: Class :: DBI Author: Tony Bowden Original: http://www.perl.com/pub/a/2002/11/11/11 27 / classdbi.html Table: November 27, 2002Perlchina reminds you: Please protect the author's copyright and safeize the author's crystallization.
Recently, several articles on Perl.com (including Phasebook design patterns) discussed the issues of Perl code and database dealing. Terrence Brannon's DBIX :: Recordset One article attempts to display the database is more simple and easy to maintain. This article is to use Class :: DBI to make this effort further.
Class :: DBI reward lazy and simple. The goal is to make simple database operations almost no programming while making difficulties. For many simple database applications, it allows us to write SQL at all, and on the other hand it doesn't force you to use a complex data structure to represent a complex query. If you really need the function or expressive ability of the original SQL, it will give you the way.
The easiest way to understand Class :: DBI is to use it to create an example program. In this article I have to do a tool to analyze my phone bill.
Data :: bt :: Phonebill (available in CPAN Download) gives us a way to download a telephone bill from the BT website. With this module and some of the most recent call accounts, we can use the database to store the details for analysis.
The basic concept of Class :: DBI is that each table in the database has a corresponding class. Although each class can be connected (database) related things, it is best to have a class to encapsulate these things. So we have to build a database and create a base class for the application:
Package my :: phonebill :: dbi;
Use base 'clas :: dbi';
__Package __-> set_db ('main', 'dbi: mysql: phonebill', 'u / n', 'p / w');
1;
We just inherit the Class :: DBI and use the 'set_db' method to create a database connection. At present, this is what we need to do in this class. Here we start building a table for storing call information:
Create Table Call
Callid Mediumint Unsigned Not Null Primary Key Auto_Increment,
Number varchar (20) Not null,
Destination varchar (255) Not null,
Calldate Date Not Null,
CallTime Time Not Null,
Type varchar (50) Not null,
Duration Smallint Unsigned Not Null,
Cost float (8, 1)
);
For this, we have to build the corresponding classes:
Package my :: phonebill :: call;
Use base 'my :: phonebill :: dbi';
__Package __-> Table ('Call');
__Package __-> columns (all => QW / CallID Number Destination CallDate CallTime Type Duration Cost /)
1;
We inherit the connection information from the base class and declare the table we have to use and the columns it contains. Now we have to start filled the data inside the table.
We have established a simple script called "Populate_Phone_Bill": #! / Usr / bin / perl
Use Data :: bt :: phonebill;
Use my :: phonebill :: call;
MY $ file = shift or die "Need a phone bill file";
MY $ BILL = DATA:: BT :: Phonebill-> New ($ file) or die "can't parse bill";
While (MY $ CALL = $ BILL-> Next_CALL) {
My :: Phonebill :: Call-> Create ({
Number => $ call-> number,
Calldate => $ call-> date,
CallTime => $ call-> time,
Destination => $ call-> destination,
Duration => $ call-> DURATION,
TYPE => $ call-> type,
Cost => $ call-> COST,
});
}
CREATE () calls to execute SQL to the INSERT line for each row. Because we are using Class :: DBI and set the primary key for auto_increment, we do not need to provide a value for that column. For databases that support sequences, we can also remind Class :: DBI which sequence needs to use to provide the primary key to provide the next unique value.
Now we already have a table filled with call data, then start querying the data. Below you will write a simple script to report call records with a specific number.
#! / usr / bin / perl
Use my :: phonebill :: call;
MY $ Number = Shift or Die "Usage: $ 0
"
My @calls = my :: phonebill :: Call-> search (number => $ number);
MY $ TOTAL_COST = 0;
Foreach My $ Call (@calls) {
$ TOTAL_COST = $ CALL-> COST;
Printf "% S% S -% D Secs,% .1f pence / n",
$ CALL-> Calldate, $ CALL-> CallTime, $ CALL-> DURATION, $ CALL-> COST
}
Printf "Total:% D Calls,% D Pence / N", Scalar @calls, $ TOTAL_COST
Here I saw Class :: DBI provided us with a 'search' method for us. We provide a pair of pairs of columns / values to get all the eligible records. Each record is an instance of a Call object, and each instance also has an access method corresponding to the column name. (This is a method of adjusting the value, we can use to modify the record, but we only care about the report)
After having this script, if we want to see a few times, you can run this command several times.
> Perl Calls_to 123
2002-09-17 11:06:00 - 5 SECS, 8.5 PENCE
2002-10-19 21:20:00 - 8 SECS, 8.5 PENCE
Total: 2 Calls, 17 Pence
Similarly, if we want to see all the calls for a day, you can write a 'calls_on' script: #! / Usr / bin / perl
Use my :: phonebill :: call;
MY $ DATE = Shift or Die "Usage: $ 0
"
My @calls = my :: phonebill :: Call-> search (calldate => $ date);
MY $ TOTAL_COST = 0;
Foreach My $ Call (@calls) {
$ TOTAL_COST = $ CALL-> COST;
Printf "% s)% S -% D second,% .1f pence / n",
$ call-> CallTime, $ CALL-> Number, $ CALL-> DURATION, $ CALL-> COST
}
Printf "Total:% D Calls,% D Pence / N", Scalar @calls, $ TOTAL_COST
Run this command results:
> Perl Calls_on 2002-10-19
...
18:36:00) 028 9024 4222 - 41 SECS, 4.2 PENCE
21:20:00) 123 - 8 SECS, 8.5 PENCE
...
Total: 7 Calls, 92 Pence
Just as before we guarantee that we can access the database without writing SQL. Although there is no very complicated thing, this small example can make our lives easier.
Building a phone call in the past, I always have a good memory for the phone number. But Nokia, Ericsson these companies are conspirating me. The embedded phone inside my mobile phone makes me reduce my memory capabilities for 10/11 digits. Now I have seen the output of 'calls_on', I have not known what "028 9024 4222" is what. Now we need a phone with contact information to explain the numbers in these reports.
The first step is to do more cleaning points for our information. We will move both numbers and calls to the "Recipient" table and add a name. The word "destination" does not have a good expression and the number of relationships, not to say the relationship between calls, so we have to rename it "location".
CREATE TABLE Recipient
Recipid Mediumint Unsigned Not Null Primary Key Auto_Increment,
Number varchar (20) Not null,
Location varchar (255) Not null,
Name varchar (255),
Key (Number)
);
Then we build a table corresponding to the table:
Package my :: phonebill :: russient;
Use base 'my :: phonebill :: dbi';
__Package __-> Table ('Recipient');
__Package __-> columns (all => QW / Recipid Number location name);
1;
You also need to modify the definition of the Call table:
Create Table Call
Callid Mediumint Unsigned Not Null Primary Key Auto_Increment,
Recipient Mediumint Unsigned Not Null,
Calldate Date Not Null,
CallTime Time Not Null, Type Varchar (50) Not Null,
Duration Smallint Unsigned Not Null,
Cost float (8, 1),
Key (Recipient)
);
And the corresponding class:
Package my :: phonebill :: call; user base 'my :: phonebill :: dbi'; __package __-> Table ('call'); __package __-> columns (all => Qw / callid Recipient CallDate CallTime Type Duration Cost /) ; 1;
Below we want to modify the script that populates the database:
#! / usr / bin / perl
Use Data :: bt :: phonebill;
Use my :: phonebill :: call;
Use my :: phonebill :: russient;
MY $ file = shift or die "Need a phone bill file";
MY $ BILL = DATA:: BT :: Phonebill-> New ($ file) or die "can't parse bill";
While (MY $ CALL = $ BILL-> Next_CALL) {
MY $ Recipient = My :: Phonebill :: Recipient-> Find_OR_CREATE ({
Number => $ call-> number,
Location => $ call-> destination,
});
My :: Phonebill :: Call-> Create ({
Recipient => $ regipient-> id,
Calldate => $ call-> date,
CallTime => $ call-> time,
Duration => $ call-> DURATION,
TYPE => $ call-> type,
Cost => $ call-> COST,
});
}
This time we have to do is to build Recipient so that you can establish a link to its link from the CALL. But we don't have to build a new Recipient for each call. If we have already called someone, there will be a record. So we use find_or_create to retrieve the entries that may already exist or create a new entry.
After retorning the data in the table, we return to the report script.
Our calls_on scripts will now fail because we can't directly get the 'Number' of the call. Below we have to modify:
#! / usr / bin / perl
Use my :: phonebill :: call;
MY $ DATE = Shift or Die "Usage: $ 0
"
My @calls = my :: phonebill :: Call-> search (calldate => $ date);
MY $ TOTAL_COST = 0;
Foreach My $ Call (@calls) {
$ TOTAL_COST = $ CALL-> COST;
Printf "% s)% S -% D second,% .1f pence / n",
$ call-> CallTime, $ CALL-> Recipient, $ CALL-> DURATION, $ CALL-> Cost;}
Printf "Total:% D Calls,% D Pence / N", Scalar @calls, $ TOTAL_COST
But this script runs different from our expectations:
> Perl Calls_on 2002-10-19
...
18:36:00) 67 - 41 SECS, 4.2 PENCE
21:20:00) 47 - 8 SECS, 8.5 PENCE
...
Total: 7 Calls, 92 Pence
We got the ID in the Recipient table instead of the phone number, the ID is just a self-increasing value.
In order to make this value a meaningful value, we add the following line in the CALL class:
__Package __-> HAS_A (Recipient => 'My :: Phonebill :: Recipient');
This tells that the Recipient method is not a simple output of a value but transforms that value into an instance of a Recipient class.
Of course, Calls_on is still incorrect:
> Perl Calls_on 2002-10-19
...
18:36:00) My :: Phonebill :: Recipient = Hash (0x835b6b8) - 41 SECS, 4.2 PENCE
21:20:00) My :: phonebill :: Recipient = Hash (0x835a210) - 8 SECS, 8.5 PENCE
...
Total: 7 Calls, 92 Pence
But now you can do a small modification:
Printf "% s)% S -% D second,% .1f pence / n",
$ call-> Calltime, $ call-> Recipient-> Number, $ call-> duration, $ call-> cost;
Everything is all perfect now:
> Perl Calls_on 2002-10-19
...
18:36:00) 028 9024 4222 - 41 SECS, 4.2 PENCE
21:20:00) 123 - 8 SECS, 8.5 PENCE
...
Total: 7 Calls, 92 Pence
Calls_to scripts require more techniques because the start now is Recipent instead of call.
So we change the search for:
MY ($ Recipient) = my :: phonebill :: Recipient-> search (Number => $ Number)
OR DIE "No Calls to $ Number / N";
Then we need to get all the calls to the Recipient. In order to achieve this, we need to declare the relationship between Recipient and Call. Unlike the HAS_A relationship established in the CALL class, the Recipient table does not store information related to each call record of the CALL table. In this case, we have to add HAS_MANY to the Recipient class.
__Package __-> HAS_MANY (Calls => 'my :: phonebill :: call');
This creates a new way called Calls for the Recipient object instance, calling it will return all Call objects related to the Recipient foreign key.
This has been found in the Calls_to script, as long as we do this:
MY @calls = $ recipient-> calls;
This script can now work as before: #! / Usr / bin / perl
Use my :: phonebill :: russient;
MY $ Number = Shift or Die "Usage: $ 0
"
MY ($ Recipient) = my :: phonebill :: Recipient-> search (Number => $ Number)
OR DIE "No Calls to $ Number / N";
MY @calls = $ recipient-> calls;
MY $ TOTAL_COST = 0;
Foreach My $ Call (@calls) {
$ TOTAL_COST = $ CALL-> COST;
Printf "% S% S -% D Secs,% .1f pence / n",
$ CALL-> Calldate, $ CALL-> CallTime, $ CALL-> DURATION, $ CALL-> COST
}
Printf "Total:% D Calls,% D Pence / N", Scalar @calls, $ TOTAL_COST
Output is also an oldest:
> Perl Calls_to 123
2002-09-17 11:06:00 - 5 SECS, 8.5 PENCE
2002-10-19 21:20:00 - 8 SECS, 8.5 PENCE
Total: 2 Calls, 17 Pence
Below we write a script to set the name for a phone number in the address book:
#! / usr / bin / perl
Use my :: phonebill :: russient;
MY ($ Number, $ Name) = @argv;
DIE "USAGE $ 0
/ N "Unless $ Number and $ Name;
MY $ Recip = My :: Phonebill :: Recipient-> Find_or_create ({Number => $ Number});
MY $ OLD_NAME = $ RECIP-> Name;
$ Recip-> Name ($ name);
$ Recip-> Commit;
IF ($ OLD_NAME) {
Print "OK. $ Number Changed from $ OLD_NAME To $ Name / N";
} else {
Print "OK. $ Number IS $ Name / N";
}
This allows us to establish a relationship between numbers and names:
> Perl add_phone_number 123 "speaking clock"
OK. 123 is speaking clock
Now as long as small changes can make the calls_on script to output our familiar name:
Printf "% s)% S -% D second,% .1f pence / n",
$ call-> calltime, $ call-> Recipient-> name || $ call-> Recipient-> Number,
$ CALL-> DURATION, $ call-> cost;
> Perl Calls_on 2002-10-19
...
18:36:00) 028 9024 4222 - 41 SECS, 4.2 PENCE
21:20:00) Speaking Clock - 8 SECS, 8.5 Pence
...
Total: 7 Calls, 92 Pence
To allow the calls_to script to accept name or number parameters at the same time, we can do this: MY $ Recipient = my :: phonebill :: Recipient-> search (name => $ number)
|| My :: Phonebill :: Recipient-> Search (Number => $ Number)
|| DIE "No Calls to $ Number / N";
However, a name may correspond to multiple numbers. Because we call the Search method in the scalar environment instead of the list environment, we get an Iterator instead of each Recipient object. We need to traverse each subject to complete the work:
MY @calls;
While (My $ Recip = $ Recipient-> Next) {
Push @Calls, $ Recip-> Calls;
}
(Print the small function of each number leaves the reader to practice.)
> Perl Calls_to "Speaking Clock"
2002-09-17 11:06:00 - 5 SECS, 8.5 PENCE
2002-10-19 21:20:00 - 8 SECS, 8.5 PENCE
Total: 2 Calls, 17 Pence
Collaboration with other modules Sometimes we need to store data that other modules need to be used in the database. For example, we need to have a different type of date, we prefer to use the Date :: Simple type object. Class :: DBI also makes this goal easy to implement.
We still declare this relationship with HAS_A in the Call class:
__Package __-> HAS_A (Recipient => 'My :: Phonebill :: Recipient');
__Package __-> HAS_A (CallDate => 'Date :: Simple');
This way we get CallDate, it is automatically expanded to date :: Simple object. This way we can design a more beautiful format for the output of Calls_to:
Printf "% S% S -% D Secs,% .1f pence / n",
$ call-> calldate-> format ("% D% b"), $ call-> calltime,
$ CALL-> DURATION, $ call-> cost;
> Perl Calls_to "Speaking Clock"
17 Sep 11:06:00 - 5 SECS, 8.5 Pence
19 Oct 21:20:00 - 8 SECS, 8.5 Pence
Total: 2 Calls, 17 Pence
Class :: DBI assumes any non-Class :: DBI class to expand through the New method, compressed by Stringification. Because Date :: Simple does support this, we don't need to do more. If this is not the case, for example, you want to use the Time :: Piece class instead :: Simple class, you have to tell Class :: DBI how to expand and compress data between memory values and databases.
__Package __-> HAS_A (CallDate => 'Time :: Piece',
Inflate => sub {time :: piece-> strptime (shift, "% y-% m-% d")}
DEFLATE => 'YMD'
);
Compact Time :: Piece objects into the ISO date type of MySQL is very easy: You just want to call the ymd () method of the class. This way we sequence it into a string. It is troublesome to decompress, which needs to call a strPtime () method with two parameters. This way we have to use a function reference. This way we can tell Strptime to analyze the date string. Using Time :: Piece instead :: time requires our code to change the output section:
Printf "% S% S -% D Secs,% .1f pence / n",
$ call-> calldate-> strftime ("% D% B"), $ call-> calketime,
$ CALL-> DURATION, $ call-> cost;
The most common number BT gives us a service that sets 10 most common numbers and saves call charges. This makes it necessary to see the most money on those numbers. We assume that there is no need to analyze only one phone number. We only care about the number of people who spend more than one call.
As mentioned earlier, Class :: DBI is not trying to express any SQL with grammar, so some data cannot be obtained directly. We still use the simplest way.
First we add a method to the Recipient class to tell us how much we spend talking to this person:
Use list :: util 'sum';
SUB TOTAL_SPEND {
MY $ SELF = Shift;
Return Sum Map $ _-> COST, $ Self-> Calls;
}
Then we can write a TOP_TEN script:
#! / usr / bin / perl
Use my :: phonebill :: russient;
My @recipients = my :: phonebill :: Recipient-> Retrieve_all;
My @regulars = grep $ _-> calls> 1, @Recipients;
My @Sorted = Sort {$ b-> Total_spend <=> $ A-> Total_Spend} @regulars;
Foreach My $ Recip (@sorted [0 .. 9]) {
Printf "% S -% D calls =% D whped / N",
$ Recip-> name || $ rececon-> Number,
Scalar $ Recip-> Calls,
$ Recip-> Total_Spend;
}
This is a very slow method, especially if there are more than 50 call records in the database. The main overhead is that we always use the return value of the method to make a comparison value. Use Schwartzian Transform to replace sorting significantly improved performance:
My @Sorted = Map $ _-> [0],
Sort {$ b -> [1] <= $ a -> [1]}
Map [$ _, $ _-> TOTAL_SPEND], @REGULARS;
Before the content of the database is significantly increased, this method is much more, especially if you don't run this script often.
But this is not enough, we can use SQL directly. It should be, you will lose other performance when optimized for speed, in which losses in this example may be portability. Now our example uses mysql, we will add Mysql supported in Recipient.pm:
__Package __-> set_sql (top_ten => QQ {
Select Recipient.Recipid,
Sum (Call.cost) AS SPEND,
Count (Call.callid) AS Calls
From Recipient, Call
WHERE RECIPIENT.Recipid = CALL.Recipient
Group by Recipient.Recipid
Having Calls> 1
Order by spend desc
Limit 10
});
Then we can build a method that returns a lot of objects:
SUB top_ten {
MY $ Class = Shift;
MY $ sth = $ class-> sql_top_ten;
$ sth-> execute;
Return $ Class-> Sth_to_Objects ($ STH);
}
Any SQL set with SET_SQL can be used to take out the DBI statement handle that compiles the good execution. So we retrieve Top_ten with my $ st = $ class-> sql_top_ten.
We can do this and call those traditional DBI orders such as Fetchrow_Array, etc., can also be lazy. Since the column of our query output is the primary key of Recipient, we can feed the results to sth_to_Objects, this Class :: DBI's underlying method allows the query to return to the object instance list.
This way, our script is simpler:
Foreach My $ Recip (My :: Phonebill :: Recipient-> Top_ten) {
Printf "% S -% D calls =% D whped / N",
$ Recip-> name || $ rececon-> Number,
Scalar $ Recip-> Calls,
$ Recip-> Total_Spend;
}
As shown above, Class :: DBI makes the usual database programming is not worth mentioning (not writing SQL code). But when you really need it, you can easily write the SQL you need and execute.
