Tuesday, November 3, 2009

Limiting Database Resources




















Limiting Database Resources


Whether intentional or malicious, a computer’s resources can be monopolized without much effort. Generally, when this is done maliciously, it is known as a denial of service (DoS) attack.


DoS attacks are easy to implement and hard to defend against. The defense challenge arises from the fact that there are numerous ways to trigger such attacks. The result is simple: exhaust computing resources to the point that the database can no longer provide adequate service. Fortunately, there are some actions you can take in the database to mitigate the risk of DoS attacks.




Resource Limits


In addition to the password profile capabilities, Oracle supports the use of resource profiles to limit the use of precious database resources. Resource limits help ensure that the application or user doesn’t intentionally, or inadvertently, take over the database and system’s resources. You can view the various resources that can be managed as well as their values by querying the DBA_PROFILES view:


sec_mgr@KNOX10g> SELECT resource_name, LIMIT  
2 FROM dba_profiles
3 WHERE PROFILE = 'DEFAULT'
4 AND resource_type = 'KERNEL';

RESOURCE_NAME LIMIT
-------------------------- --------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED

9 rows selected.

Notice that the default values are all set to unlimited. A best practice is to actually define as many of these values as possible. Some general guidelines on the parameters are as follows:




  • Set the SESSIONS_PER_USER to the size of your application server connection pool. If you aren’t using a connection pool (or have no idea what that means), then set the value to something reasonable. You should consider that an application may lock or a computer may freeze with the connection open, so a value of one may be too restrictive.






    Tip 


    It’s possible to create a denial-of-service attack by utilizing the CREATE SESSION privilege and connecting to the database over and over until the database server exhausts all memory. Setting this parameter helps to ensure this will not happen.





  • IDLE_TIME can be set to help ensure that users don’t leave a connected terminal in the database while they step out for a lunch break. If their machine is left unlocked, then someone can simply walk up and start accessing the user’s data without having to worry about breaking passwords or subverting privileges. This value is more applicable to client-server applications than to web applications; if the latter is using a connection pool, the server shouldn’t disconnect the pooled connections.




  • CPU_PER_CALL is a hard parameter to guess, but it helps to ensure the availability of the database. Often CPU monopolization occurs not by a malicious user, but by a bad programmer who inadvertently sends the database into a recursive loop (I’m speaking from experience here)!




Refer to the “Create Profile” section in the Oracle Database SQL Reference 10g document for specific definitions on all the settings. Setting the profile parameters to the logical and correct values may take a few tries before the best values are selected. Start with a least privilege mentality by setting the values very conservatively. If you find that you need to relax a few privileges for legitimate reasons, do so only after you have determined the values need to be relaxed.


A best practice is to create a profile for each application or class of users in the database. This includes administrators at all levels.




















Styling the Links











 < Day Day Up > 





Styling the Links



Having set up the rest of the page, we're ready to tackle our navigation. We can see it in Figure 7.4, sitting there as an unordered list wedged between the title and main content. Frankly, it's rather ugly, and it's up to us to fix it.



Starting the Styles



There are two ways to take a list and make it into a horizontal set of links. One is to make the list items inline, and the other is to float them. The two approaches both have benefits and drawbacks, but one of the major benefits of the floating approach is that it lets us create graphically beautiful buttons that work in modern browsers�as well as Internet Explorer!



Let's consider the markup for the navigation elements, shown in Listing 7.1.



Listing 7.1. The Navigation Markup






<ul id="nav">

<li><a href="/webdev/">Web Development</a></li>

<li><a href="/design/">Design</a></li>

<li><a href="/photos/">Photo Editing</a></li>

<li id="current"><a href="/newmed/">New Media</a></li>

<li><a href="/render/">3-D Rendering</a></li>

<li><a href="/server/">Server Tech</a></li>

</ul>



That's it�nothing else is needed. We'll make use of the id value current later in the project, but for now we don't need it.



To make this technique work, we'll first make sure that the unordered list has no padding or margins. This will make it act, more or less, the same way a div would.





#main {font-size: small; color: #AAA; background: #FFF;

margin: 0; padding: 2.5% 12.5%;}

#nav {margin: 0; padding: 0;}

</style>



Now we can get to work on the list items themselves. We're going to float all of them to the left so that they all line up horizontally, with the first link the furthest to the left. We'll also explicitly remove the bullets from the list items for Explorer's sake.



Biting the Bullets

In theory, a floated list item shouldn't generate a marker (bullet), but Explorer doesn't seem to agree. Therefore, always be sure to explicitly set list-style to none when using this technique.






#nav {margin: 0; padding: 0;}

#nav li {list-style: none; float: left; margin: 0;}

</style>



There are 6 navigation links, so we could make them all the same size by dividing 100 by 6. That yields 16 2/3, which means they could all have a width of 16.6667%. That would make things rather fragile, though�a single rounding error could upset the whole design, and it leaves no room to add margins or borders to the list items (which we'll want to do soon). Let's play things safe and make the list items 15% wide. We'll add in dotted red borders so that we can see how they lay out, as illustrated by Figure 7.5.





#nav li {list-style: none; float: left; width: 15%;

border: 1px dotted red;}



Figure 7.5. The raw beginnings of the navigation tabs.




The tabs are now lined up more or less how we want, but there's a problem there on the left. The "Web Development" tab not only is wrapping to two lines, but it's caused some of the main text to flow around it.



Text Wrapping

If you're working in a browser window that's much wider than 800 pixels, you may not see text wrapping in your project file. Try shrinking the window, at least temporarily.




This is happening because we floated all the list items, of course. Normal-flow text (like that in the main content div) flows around floated elements (like the list items). What might not be so obvious is that the unordered list that contains those list items has zero height. If we were to give it a background color, it wouldn't appear on the screen. So the top of the main content div is actually lined up with the top of the navigation links, and the white background visible behind the links is actually the background of the content div.



This may seem a little odd, but it's definitely what's supposed to happen. Since we don't actually want it to happen, we'll have to push the content div below the floated list items. This is, as it happens, very easily accomplished: We'll just clear the div below the floats.





#main {font-size: small; color: #AAA; background: #FFF;

margin: 0; padding: 2.5% 12.5%; clear: left;}



Now the top border edge of the div will be placed just below the bottom margin edge of any leftward floats that come before the div in the document�for example, the list items. This means that the body background will be visible between the h1 and the content div, as we can see in Figure 7.6, but that's all right. We'll actually use that to our advantage later on.



Figure 7.6. Pushing the contents clear below the floated list items.




Refining the Tab Look



The preceding change solved the problem of the main content flowing around the links, but it didn't do anything to stop the "Web Development" text from wrapping to two lines. The fix for that is simple enough.





#nav li {list-style: none; float: left; width: 15%;

white-space: nowrap;

border: 1px dotted red;}



This is a lot like the old HTML nowrap attribute for table cells, except in CSS you can stop the wrapping of any element's content.



Before we go any further, we need to consider how this text is going to be used. Our intent is to place it over images that will make the tabs look lovely. Thus, it's important for the text to be of a size that's appropriate to the context in which it will appear (the tabs). Since images are sized in pixels, we'll do the same for the text. In fact, both the font's size and the height of the line can be set in pixels.





#nav li {list-style: none; float: left; margin: 0; width: 15%;

font-size: 10px; line-height: 20px; white-space: nowrap;

border: 1px dotted red;}



The next step to consider is the links themselves, which are sitting inside the floated list items. We'll strip off the underlines and add a solid border so that we can see how the links are laid out inside the list items (see Figure 7.7).





#nav li {list-style: none; float: left; margin: 0; width: 15%;

font-size: 10px; line-height: 20px; white-space: nowrap;

border: 1px dotted red;}

#nav a {text-decoration: none; border: 1px solid;}

</style>



Figure 7.7. Examining the links in relation to the list items.




As Figure 7.7 shows us, the links are smaller boxes inside the floated list items. It would be better if we could get those links to fill out the list items or at least as much of them as possible.



Let's take a moment to consider the situation. Each list item is floated and thus generates a block-level box, much like that created by a div. In fact, to elements inside the list item, they see only that they're inside a block box. Only elements outside the float react to it by wrapping around the element. The link is just a link, generating an inline box. So to get an inline link to fill out a block-level list item, we can just make the links generate block boxes.





#nav a {display: block;

text-decoration: none; border: 1px solid;}



Note that we have not changed the nature of the links themselves. The a elements are still inline elements. What's happened is that the CSS is getting them to generate block boxes. This is a subtle but crucial difference. If CSS changed the elements themselves to be block level, document validation could easily break down. This isn't what happens, however, as CSS is about affecting presentation, not altering document structure.



Speaking of presentation, the links in question look a little weak with normal-weight text. Let's boldface the links' text to make it stand out a little better.





#nav a {display: block;

text-decoration: none; font-weight: bold;

border: 1px solid;}



We can see the result of these two changes in Figure 7.8. Note how the borders of the links are right against the inside of the dotted red borders we set on the list items.



Figure 7.8. Boldfaced links generating block boxes.




This is how it will appear in most browsers, anyway. The major exception is IE5/Mac, which incorrectly makes the links as wide as the unordered list instead of as wide as the list items. We definitely don't want that, so a solution is needed, and it turns out that the solution is to float the links themselves inside the floated list items.





#nav a {display: block; float: left;

text-decoration: none; font-weight: bold;

border: 1px solid;



This fixes the layout problem for IE5/Mac, but we don't really want to float the links in other browsers. What we need is a way to turn the floating back off for any browser that isn't IE5/Mac. Fortunately, Doug Bowman gave us a way when he came up with the Sliding Doors technique: There's a parsing bug that affects IE5/Mac only, and it can be used to hide the unfloating rule from IE5/Mac.





#nav a {display: block; float: left;

text-decoration: none; font-weight: bold;

border: 1px solid;

/* Commented Backslash Hack hides rule from IE5-Mac \*/

#nav a {float: none;} /* End IE5-Mac hack */

</style>



How It Works

The key to this hack is the backward slash (\) that comes right before the asterisk at the end of the first comment. This causes IE/Mac to think the comment hasn't ended yet, and so it thinks the next rule is part of a comment.




Thus, all browsers float the links (due to float: left), and then all browsers except for IE5/Mac defloat the links (thanks to float: none). This restores layout consistency in IE5/Mac, and we can move on.



Before we go on to use the full capabilities of the Sliding Doors technique, let's look at a more basic way to style the links by turning them into gray "raised" buttons. The first step is to color the foreground (text) and background of the links in varying shades of gray.





#nav a {display: block; float: left;

text-decoration: none; font-weight: bold;

border: 1px solid;

background: #CCC; color: #333;}



Now we just need to create a raised effect. There's already a solid border in place, and we could change the solid to outset. The problem is that there's no control over how the border colors are modified to create the outset effect. One browser might just make the top and left borders white and the bottom and right borders black, while another could use more subtle shades. In fact, there is very little cross-browser consistency in outset shading (as well as inset, ridge, and groove shading). So instead we'll leave the border solid and declare our own shading by setting the color of each border side.





#nav a {display: block; float: left;

text-decoration: none; font-weight: bold;

border: 1px solid; border-color: #FFF #333 #333 #FFF;

background: #CCC; color: #333;}



It's time to remove the dotted red border, so we'll do that as well, with the result shown in Figure 7.9.





#nav li {list-style: none; float: left; margin: 0; width: 15%;

font-size: 10px; line-height: 20px; white-space: nowrap;}



Figure 7.9. The links begin to look like buttons.




If we ever want to lower the contrast between the "highlight" and "shadow" edges, or introduce some subtle coloration, we need only alter the border-color values.



Adding Texture



We've made some pretty good progress so far, but there's more to be done. The biggest visual problem at the moment is that the text of the links is hard up against the left border, which doesn't look too attractive. We could add some left padding to the links to shove the text over, and in fact we'll do so in just a bit.



Since we're going to move the text over anyway, why don't we move it over enough to put an image into each link? That way, we could provide a little more visual texture to the links. Take, for example, the image in Figure 7.10, which is shown at 1600% magnification.



Figure 7.10. A small image to add some texture to the links' backgrounds.




The large white and black pixels are visible, and the rest of the image is transparent (represented by the gray-and-white checkerboard pattern). Thus, placing this image into the links will allow the gray background color to "shine through" the transparent portions of the background image.



We want this image to appear only once, inset just a bit from the left edge of the link and vertically centered, so we write the following:





#nav a {display: block; float: left;

text-decoration: none; font-weight: bold;

border: 1px solid; border-color: #FFF #333 #333 #FFF;

background: #CCC url(tab-bumps.gif) 2px 50% no-repeat;

color: #333;}



If we left things as they are now, the bumpy image would appear behind the text of the links. We'll fix that by adding some padding to the link; the image will appear within the padding, but the text will not. Since the image is 14 pixels wide and is inset a bit, we'll add 20 pixels of padding to the left sides of the links, with the result shown in Figure 7.11.





#nav a {display: block; float: left; padding: 0 0 0 20px;

text-decoration: none; font-weight: bold;

border: 1px solid; border-color: #FFF #333 #333 #FFF;

background: #CCC url(tab-bumps.gif) 2px 50% no-repeat;

color: #333;}



Figure 7.11. The links gain some texture via a background image.




It looks great except for one thing: The text of the "Web Development" link has been shoved over far enough that it's being cut off. For the moment, we're going to have to ignore this, although we will see a way around it later in the project.



Text Handling

If you're working in a browser window that's much wider than 800 pixels, you may not see the text get cut off in your project file. Try shrinking the window, at least temporarily. Also, IE/Win will not truncate the text but will stretch the list items to fit around the text. That sounds like a good idea until you realize that it makes it more likely the buttons will wrap to multiple lines.




Hovering and Finishing



Now that we have these nice buttons, let's add a hover effect to the links. Perhaps the simplest hover effect is to "invert" the colors of a link, and that's what we'll do here. We'll swap the foreground and background colors while we change the border colors to be the inverse of their unhovered state.





#nav a {float: none;} /* End IE5-Mac hack */

#nav a:hover {background-color: #333; color: #CCC;

border-color: #000 #CCC #CCC #000;}

</style>



The last touch to this phase of the project is to add a gray border to the top of the main content div. This will "box in" the links, visually placing them into a stripe that contains them alone, as we can see in Figure 7.12.





#main {font-size: small; color: #AAA; background: #FFF;

margin: 0; padding: 2.5% 12.5%; clear: left;

border-top: 1px solid gray;}



Figure 7.12. Hover styles and a border on the main content finish the look.




The style sheet we've created so far is shown in Listing 7.2.



Listing 7.2. The Styles So Far






html, body {margin: 0; padding: 0;

color: #000; background: #EEF;

font-family: Verdana, Arial, sans-serif;}

h1 {color: #FFF; background: rgb(0%,56%,84%);

font: bold 200%/1em Arial, Verdana, sans-serif;

padding: 1em 1em 0; margin: 0;

border: 1px solid rgb(0%,31%,46%);

border-width: 2px 0;}

#main {font-size: small; color: #AAA; background: #FFF;

margin: 0; padding: 2.5% 12.5%; clear: left;

border-top: 1px solid gray;}

#nav {margin: 0; padding: 0;}

#nav li {list-style: none; float: left; margin: 0; width: 15%;

font-size: 10px; line-height: 20px; white-space: nowrap;}

#nav a {display: block; float: left; padding: 0 0 0 20px;

text-decoration: none; font-weight: bold;

border: 1px solid; border-color: #FFF #333 #333 #FFF;

background: #CCC url(tab-bumps.gif) 2px 50% no-repeat;

color: #333;}

/* Commented Backslash Hack hides rule from IE5-Mac \*/

#nav a {float: none;} /* End IE5-Mac hack */

#nav a:hover {background-color: #333; color: #CCC;

border-color: #000 #CCC #CCC #000;}



Cut Off Text and Shrunken Hotspots



This is an appropriate point at which to address two potential problems with the styles we've written so far. These are:



  • If a link's text is too long, it sticks out of the link box and can be cut off by other content. We first saw this in Figure 7.11.

  • In IE/Win, the "hotspot" (clickable region) for each link is confined to the actual content and does not fill out the whole list item box.



Let's consider the first problem first. The text is cut off because we prevented the text from wrapping with white-space: nowrap and gave the list items an explicit width of 15%. Thus, the list items have to each be 15% as wide as their parent element (the ul element), regardless of whether or not their content actually fits into that space. The same would be true of any explicit width, whether set in percentages, pixels, ems, or any other length measure.



Excession

The text is actually flowing out of the list item and would be visible outside the list item if not for the other floats, which are overwriting the overflowing text. Thus, the text is not actually cut off, although it appears to be in our project files. To see what's really happening, try temporarily commenting out all but the first link in the navigation area.




We could allow the text to wrap by removing the white-space declaration, but then some links might have wrapped text whereas others would not, resulting in uneven heights for the links. This is definitely not the desired effect.



Now let's consider the second problem, the resolution of which will also help us solve the first problem. In IE/Win, a bug prevents it from considering the entirety of a block-box link as "clickable." Unless some kind of explicit width is given, only the content area of the link is treated as clickable. We could set the links to have a width of 100%, except then we couldn't add any padding to the links without making them stick out of the list items (because width defines the width of the content area plus any padding, borders, and margins that are added to it). In that case, we couldn't add images to the link backgrounds, which is too limiting.



In grappling with these issues, Doug Bowman came up with a solution that exploits a different bug in IE/Win to trick it into considering the whole link as clickable. It turns out that if you give the links a tiny width value, IE/Win will automatically (and incorrectly) expand the value to include all of the content, plus it will suddenly consider the padding clickable. This is fairly strange, but it works. Thus:





#nav a {display: block; float: left; padding: 0 0 0 20px;

text-decoration: none; font-weight: bold;

border: 1px solid; border-color: #FFF #333 #333 #FFF;

background: #CCC url(tab-bumps.gif) 2px 50% no-repeat;

color: #333;

width: .1em;}



The problem is that more conformant browsers will take that value seriously and make the link content area exactly a tenth of an em wide, with the text overflowing that thin sliver. So we need to undo the damage for these more advanced browsers by showing them a rule that IE/Win doesn't see. In this case, we rely on the fact that IE/Win doesn't understand child selectors, and we add a rule that sets the width back to auto for more advanced browsers.





#nav a {display: block; float: left; padding: 0 0 0 20px;

text-decoration: none; font-weight: bold;

border: 1px solid; border-color: #FFF #333 #333 #FFF;

background: #CCC url(tab-bumps.gif) 2px 50% no-repeat;

color: #333;

width: .1em;}

html>body #nav a {width: auto;} /* fixes IE6 hack */

/* Commented Backslash Hack hides rule from IE5-Mac \*/



So all browsers will see the width as .1em, and those that understand child selectors will also see width: auto and use it to override the .1em. Either way, the width of the link's content will equal the width of the text content, thanks to the way floats tend to shrink-wrap their contents, and the whole link will become clickable, as shown in Figure 7.13.



Figure 7.13. Shrink-wrapping the links to their contents.




Uh-oh�what happened? Exactly what was described: The content area of the links became as wide as the contents. The list items, however, are still 15% wide, so the links are no longer filling out the list items.



At least, that's what happens in some browsers. (Figure 7.13 was taken in IE5/Mac, and IE/Win should look similar.) In others, the links will still fill out the list items. Clearly, this isn't much better than before, when our only problem was that one of the links had text sticking out of the button.



We can bring everything back to a kind of harmony by letting the list items also shrink themselves to fit their contents. To do this, we need the list items to have auto width, which we can accomplish by simply removing the width: 15% from the #nav li rule.





#nav li {list-style: none; float: left;

font-size: 10px; line-height: 20px; white-space: nowrap;}



Now, for every button, the link will shrink around its content, and the list item will shrink around the link. This gives us the situation shown in Figure 7.14.



Figure 7.14. Shrink-wrapping both the list items and the links.




So what have we learned in this interlude?



  • That it's difficult to get fully clickable links in IE/Win when their parents are set to an explicit width

  • That links set to an explicit width risk having the contents of the links overflow

  • That shrink-wrapping links and the list items that contain them solves both problems, once you work your way around some IE bugs



So the reasonable conclusion is that, in general, it's better to have autosized links than explicitly sized links. In fact, this is the approach used in the original Sliding Doors technique: tabs that autosize themselves to their contents.



There are other reasons why the technique uses shrink-wrapping, as we're about to find out.













     < Day Day Up > 



    Viewing Graphics Files









    Viewing Graphics Files


    Images can only be displayed by applications that can interpret the information in a graphics file. The application must understand the specific format the graphic file is stored in. An application may understand .jpg files, but not .bmp files.


    Konqueror can display most graphics formats. Konqueror can display thumbnails of the graphics files when it displays the files in a directory. Thumbnails are small versions of the image. To configure Konqueror to display thumbnails, click View and select Preview to see the submenu shown on the right. Select Show Previews. Check or uncheck the types of previews you want to see. For graphics thumbnails, check Pictures and Images.



    With previews on, thumbnails of most graphics files display, but not all. For instance, thumbnails of .bmp files and .tif files are not shown. However, this doesn't mean that Konqueror can't render the graphics format. If you double-click the icon for a .bmp or .tif file, the image displays in the Konqueror window.


    Graphics file extensions are associated with applications, as described in Chapter 9. To see which application is associated with a particular file type, highlight a file with the extension, right-click, and select Edit File Type. You can see, and change, the application that opens the file with the given extension.


    Konqueror can interpret most graphics file types, but not all. For instance, Konqueror can't interpret .svg files, a graphics file type that uses XML. If you double-click an .svg file icon, it opens in the application associated with the .svg file type, usually sodipodi.









      10.14 Exporting Query Results from MySQL




      I l@ve RuBoard










      10.14 Exporting Query Results from MySQL




      10.14.1 Problem



      You want to export the result of a query
      from MySQL into a file or another program.





      10.14.2 Solution



      Use the SELECT ...
      INTO OUTFILE statement or
      redirect the output of the mysql program.





      10.14.3 Discussion



      MySQL provides a SELECT ...
      INTO OUTFILE statement that
      exports a query result directly into a file on the server host.
      Another way to export a query, if you want to capture the result on
      the client host instead, is to redirect the output of the
      mysql program. These methods have different
      strengths and weaknesses, so you should get to know them both and
      apply whichever one best suits a given situation.





      10.14.4 Exporting with the SELECT ... INTO OUTFILE Statement



      The syntax for this statement combines a regular
      SELECT with INTO
      OUTFILE filename at the
      end. The default output format is the same as for
      LOAD DATA, so the following
      statement exports the passwd table into
      /tmp/passwd.txt as a tab-delimited,
      linefeed-terminated file:



      mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/passwd.txt';


      You can change the output format using options similar to those used
      with LOAD DATA that indicate
      how to quote and delimit columns and records. To export the
      passwd table in CSV format with CRLF-terminated
      lines, use this statement:



      mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/passwd.txt'
      -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
      -> LINES TERMINATED BY '\r\n';


      SELECT ... INTO
      OUTFILE has the following properties:




      • The output file is created directly by the MySQL server, so the
        filename should indicate where you want the file to be written on the
        server host. There is no LOCAL version of the
        statement analogous to the LOCAL version of
        LOAD DATA.


      • You must have the MySQL FILE privilege to execute
        the SELECT ... INTO statement.


      • The output file must not already exist. This prevents MySQL from
        clobbering files that may be important.


      • You should have a login account on the server host or some way to
        retrieve the file from that host. Otherwise,
        SELECT ... INTO
        OUTFILE likely will be of no value to you.


      • Under Unix, the file is created world readable and is owned by the
        MySQL server. This means that although you'll be
        able to read the file, you may not be able to delete it.





      10.14.5 Using the mysql Client to Export Data



      Because
      SELECT ... INTO
      OUTFILE writes the datafile on the server host,
      you cannot use it unless your MySQL account has the
      FILE privilege. To export data into a local file,
      you must use some other strategy. If all you require is tab-delimited
      output, you can do a "poor-man's
      export" by executing a SELECT
      statement with the mysql program and redirecting
      the output to a file. That way you can write query results into a
      file on your local host without the FILE
      privilege. Here's an example that exports the login
      name and command interpreter columns from the
      passwd table created earlier in this chapter:



      % mysql -e "SELECT account, shell FROM passwd" -N cookbook > shells.txt


      The
      -e option specifies the query to execute,
      and
      -N tells MySQL not to write the row of
      column names that normally precedes query output. The latter option
      was added in MySQL 3.22.20; if your version is older than that, you
      can achieve the same end by telling
      mysql to be "really
      silent" with the -ss option
      instead:



      % mysql -e "SELECT account, shell FROM passwd" -ss cookbook > shells.txt


      Note that NULL values are written as the string
      "NULL". Some sort of postprocessing
      may be necessary to convert them, depending on what you want to do
      with the output file.



      It's possible to produce output in formats other
      than tab-delimited by sending the query result into a post-processing
      filter that converts tabs to something else. For example, to use hash
      marks as delimiters, convert all tabs to #
      characters (TAB indicates where you type a
      tab character in the command):



      % mysql -N -e " your query here "  db_name  | sed -e "s/ TAB /#/g" >  output_file 


      You can also use tr for this purpose, though the
      syntax may vary for different implementations of this utility. The
      command looks like this for Mac OS X or RedHat Linux:



      % mysql -N -e " your query here "  db_name  | tr "\t" "#" >  output_file 


      The mysql commands just shown use
      -N or -ss to suppress column labels
      from appearing in the output. Under some circumstances, it may be
      useful to include the labels. (For example, they may be useful when
      importing the file later.) If so, omit the label-suppression option
      from the command. In this respect, exporting query results with
      mysql is more flexible than
      SELECT ... INTO
      OUTFILE because the latter cannot produce output
      that includes column labels.





      10.14.6 See Also



      Another way to export query results to a file on the client host is
      by using the mysql_to_text.pl utility described
      in Recipe 10.18. That program has options that allow
      you to specify the output format explicitly. To export a query result
      as an Excel spreadsheet or for use with FileMaker Pro, see Recipes
      Recipe 10.40 and Recipe 10.41.










        I l@ve RuBoard



        1.4. Downloading Eclipse











         < Day Day Up > 







        1.4. Downloading Eclipse



        To download the Eclipse IDE, go to http://www.eclipse.org. Click on "downloads" and then select the most recent stable or release version of the Eclipse SDK for your platform. If prompted for a mirror site, pick the one located closest to you. If that one is slow or unavailable, simply return to the download page and try a different mirror, or try the main site.




        Tip: You may see other download packages such as Runtime, JDT, and RCP on the download page. You don't need those. Just get the one package called Eclipse SDK.














           < Day Day Up > 



          Chapter 13. Development Tools











          Chapter 13. Development Tools




          In this chapter


          • GNU Debugger (GDB) page 310

          • Data Display Debugger page 317

          • cbrowser/cscope page 319

          • Tracing and Profiling Tools page 321

          • Binary Utilities page 340

          • Miscellaneous Binary Utilities page 346

          • Chapter Summary page 349


          A typical embedded Linux distribution includes many useful tools. Some are complex and require a great deal of proficiency to master. Others are simple and have been all but ignored by developers of embedded systems. Some tools might require customization for a particular environment. Many will run "right out of the box" and provide the developer with useful information without much effort. This chapter presents a cross-section of the most important (and frequently neglected) tools available to the embedded Linux engineer.


          It is impossible to provide complete details on the tools and utilities presented in this chapter. That would take an entire book by itself! Rather than provide a complete reference, our goal is to provide an introduction on the basic usage of each one. You are encouraged to pursue additional study on these and other important development tools. The man page (or other documentation) for each tool is a great place to start.


          The GNU Debugger (GDB) is introduced first, followed by a brief look at the Data Display Debugger, a graphical front end for GDB. Next we introduce a series of utilities designed to give the developer a look at the behavior of programs and the system as a whole. These include strace, ltrace, top, and ps, often overlooked by inexperienced Linux developers. We then present some crash dump and memory-analysis tools. The chapter concludes by introducing some of the more useful binary utilities.















          CRITICAL SKILL 3.10 Manage Privileges for Database Users


          Team Fly 


          Page 115



          CRITICAL SKILL 3.10
          Manage Privileges for Database Users



          Creating a user in Oracle has accomplished the first part of user setup and that is authentication. We have a user ID and password and have authorized this user to use an Oracle database. Once the user logs in, however, they will not be able to do very much because they will not have privileges that allow them to access any objects. This leads us to the second step of setting up a user: authorization. In order to authorize a user to perform their tasks, we need to grant access.



          Grant Authority



          You now need to give permission to the user to do things in Oracle. Actions like accessing a table or executing a procedure or running a utility require you to ''grant" the authority to that user. When you perform a grant, you can specify four things:



          The user that is being granted the authority.



          The object that is being granted. Examples of these are a table, procedure, or role.



          The type of access being granted, such as select, insert, update, or delete on a table, or execute on a procedure, function, or package.



          Whether this user has authority to then grant the same authority to other users. By default, they do not, but this can be added by using the With Grant option.



          Here are two examples that grant a user "NEWUSER" access to a table and then to a package.




          GRANT SELECT ON "TABLE_NAME" TO "NEWUSER" WITH GRANT OPTION;
          GRANT INSERT ON "TABLE_NAME" TO "NEWUSER" WITH GRANT OPTION;
          GRANT EXECUTE ON "PROCEDURE_NAME" TO "NEWUSER"


          Types of Grants


          There are two types of grants that can be given to a user: system privileges and object privileges.



          System privileges are predefined Oracle privileges granting authority to overall system objects rather than individual ones. The ability to perform a create tablespace, alter system, and back up any table are just a few examples of some system-level privileges that can be granted to a user.



          Object privileges are a lower-level authority where a named object is granted to a user. So, the ability to perform an operation on a particular



          Team Fly 

          Generating XML from a DataTable











           < Day Day Up > 





          Generating XML from a DataTable



          One question I receive quite often regarding serializing ADO.NET objects to XML format is how to generate XML from a standalone DataTable, or one that is not included in a parent DataSet object. If you look up the GetXml or WriteXml method in online help, you'll find that while both methods are implemented by the DataSet class, neither is implemented by the DataTable class. This is a bit confusing at first because, as you've seen, calling and using these methods against a DataSet containing multiple DataTable objects does result in each table being processed. After exchanging a couple of e-mails with friends on the .NET development team, I've found that the DataSet does define methods to serialize a specified DataTable, but that these methods are not exposed. However, this limitation is very easy to circumvent with the following generic function�WriteDataTableToXml�which serializes a specified DataTable object to XML.





          void WriteDataTableToXml(

          DataTable* table,

          String* filename,

          XmlWriteMode mode)

          {

          #pragma push_macro("new")

          #undef new

          try

          {

          // Create a temporary dataset

          DataSet* dataset = new DataSet();



          // Does table belong to a dataset already?

          if (NULL == table->DataSet)

          {

          // Add it to the temporary DataSet

          dataset->Tables->Add(table);

          }

          else

          {

          // Make a copy of the DataTable and

          // add it to the temporary DataSet

          dataset->Tables->Add(table->Copy());

          }



          dataset->WriteXml(filename, mode);

          }

          catch(Exception* e)

          {

          Console::WriteLine(e->Message);

          }

          #pragma pop_macro("new")

          }



          The WriteDataTableToXml function takes three parameters�a DataTable object, a String that's used as a file name, and an XmlWriteMode enumeration value. After the function constructs an empty DataSet, it checks to see if the DataTable already belongs to a DataSet by inspecting the DataTable::DataSet property. This is done due to the restriction that a single instance of a DataTable cannot simultaneously belong to more than one DataSet. If the DataTable does not belong to a DataSet, it is added to the temporary DataSet. If the table does belong to a DataSet, then a temporary copy of the DataTable is made and added to the temporary DataSet. Finally, the DataSet::WriteXml is called, passing it the specified file name and XmlWriteNode parameter values. The following test function programmatically constructs a DataTable, defining its columns and adding a couple of rows of test data before calling the WriteDataTableToXml function.





          void TestWriteDataTableToXml()

          {

          #pragma push_macro("new")

          #undef new

          try

          {

          // Define a DataTable

          DataTable* table = new DataTable(S"Players");



          // Define the table's columns

          table->Columns->Add(S"FirstName", __typeof(String));

          table->Columns->Add(S"LastName", __typeof(String));



          DataRow* newRow;

          Object* values[] = new Object*[table->Columns->Count];



          // Add test rows to the DataTable object

          values[0] = S"Yao";

          values[1] = S"Ming";

          newRow = table->Rows->Add(values);



          values[0] = S"Steve";

          values[1] = S"Francis";

          newRow = table->Rows->Add(values);



          WriteDataTableToXml(table,

          S"Players.xml",

          XmlWriteMode::WriteSchema);

          }

          catch(Exception* e)

          {

          Console::WriteLine(e->Message);

          }

          #pragma pop_macro("new")

          }



          As the following output indicates, our little helper function now enables us to serialize DataTable objects with or without the accompanying schema information.





          <?xml version="1.0" standalone="yes"?>

          <NewDataSet>

          <xs:schema id="NewDataSet"

          xmlns=""

          xmlns:xs="http://www.w3.org/2001/XMLSchema"

          xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

          <xs:element name="NewDataSet" msdata:IsDataSet="true">

          <xs:complexType>

          <xs:choice maxOccurs="unbounded">

          <xs:element name="Players">

          <xs:complexType>

          <xs:sequence>

          <xs:element name="FirstName"

          type="xs:string" minOccurs="0" />

          <xs:element name="LastName"

          type="xs:string" minOccurs="0" />

          </xs:sequence>

          </xs:complexType>

          </xs:element>

          </xs:choice>

          </xs:complexType>

          </xs:element>

          </xs:schema>

          <Players>

          <FirstName>Yao</FirstName>

          <LastName>Ming</LastName>

          </Players>

          <Players>

          <FirstName>Steve</FirstName>

          <LastName>Francis</LastName>

          </Players>

          </NewDataSet>













             < Day Day Up > 



            Chapter 15. InnoDB Tables











             < Day Day Up > 







            Chapter 15. InnoDB Tables





            This chapter describes the InnoDB table type that is supported by the InnoDB storage engine. It covers the following topics:



            • The InnoDB tablespace and log files

            • InnoDB support for transactions

            • Optimizations that apply to working with InnoDB tables

            • How the InnoDB storage engine handles locking

            • Backup and recovery options for InnoDB tables

            • How to check and repair InnoDB tables

            • InnoDB maintenance issues, such as how to configure the tablespace



            Questions on the material in this chapter make up approximately 10% of the exam.



            The InnoDB storage engine is one of the table handlers that MySQL provides. InnoDB was introduced to MySQL during the 3.23 release series and is a standard feature in binary distributions as of MySQL 4. It has several notable features:



            • InnoDB supports transactions, with commit and rollback. It provides full ACID (atomicity, consistency, isolation, durability) compliance. Multi-versioning is used to isolate transactions from one another. If the MySQL server or the server host crashes, InnoDB performs automatic crash recovery.

            • InnoDB manages contention for tables using multi-versioning and row-level locking. This provides good query concurrency even when clients are performing a mix of reads and writes.

            • InnoDB supports foreign keys and referential integrity, including cascaded deletes and updates.

            • Each InnoDB table is represented on disk by an .frm format file in the database directory, along with data and index information stored in a tablespace that is shared among InnoDB tables. The tablespace is stored in machine-independent format. It is implemented such that table sizes can exceed the maximum file size allowed by the filesystem.



            To create an InnoDB table, nothing more is necessary than to add a TYPE option to the end of the CREATE TABLE statement:










            CREATE TABLE t (i INT) TYPE = InnoDB;




            To convert an existing table from some other type to InnoDB, use ALTER TABLE:










            ALTER TABLE t TYPE = InnoDB;


















               < Day Day Up > 



              Section 17.3. Using Stored Programs in ASP.NET










              17.3. Using Stored Programs in ASP.NET







              In the final section of this chapter, let's put our newly acquired Connector/Net and stored program skills to work to create a simple ASP.NET application.


              The stored procedure we will use is shown in Example 17-41. It takes as an (optional) argument a database name, and it reports on the objects within that database, along with a list of users currently connected to the server, server status variables, server configuration variables, and a list of databases contained within the server. It contains one OUT parameter that reports the server version.


              Example 17-41. Stored procedure for our ASP.NET example




              CREATE PROCEDURE sp_mysql_info
              (in_database VARCHAR(60),
              OUT server_version VARCHAR(100))
              READS SQL DATA
              BEGIN

              DECLARE db_count INT;

              SELECT @@version
              INTO server_version;

              SELECT 'Current processes active in server' as table_header;
              SHOW full processlist;


              SELECT 'Databases in server' as table_header;

              SHOW databases;

              SELECT 'Configuration variables set in server' as table_header;
              SHOW global variables;
              SELECT 'Status variables in server' as table_header;
              SHOW global status;


              SELECT COUNT(*)
              INTO db_count
              FROM information_schema.schemata s
              WHERE schema_name=in_database;
              IF (db_count=1) THEN
              SELECT CONCAT('Tables in database ',in_database) as table_header;
              SELECT table_name
              FROM information_schema.tables
              WHERE table_schema=in_database;
              END IF;

              END$$



              The number and type of result sets is unpredictable, since a list of database objects is generated only if a database matching the stored procedure's first parameter is found on the server.


              Prior to every major result set, the stored procedure generates a one-row "title" as a result set. This "title" result set is identified by the column title table_header.


              First, we need to create an ASP.NET form to retrieve the information we need to connect to the MySQL server and to obtain the parameters we need to call the stored procedure.


              Creating the input form in Visual Studio is fairly straightforward. We create TextBox controls to retrieve our input parameters, as shown in Figure 17-3.



              Figure 17-3. ASP.NET form



              Notice that in addition to the standard TextBox controls, we also added Literal and PlaceHolder controls. These controls allow us to insert dynamic content when the stored procedure is executed.


              Next, we add the code that controls the database interaction. All of our database interaction logic is contained within the method associated with the Submit button. This logic is shown in Example 17-42.


              Example 17-42. Database access logic for our ASP.NET page




              1 void FindButton_Click(object sender, EventArgs e)
              2 {
              3 //Arrays of grids and literals for our output.
              4 System.Web.UI.WebControls.DataGrid[] DataGrids;
              5 DataGrids = new System.Web.UI.WebControls.DataGrid[20];
              6 System.Web.UI.WebControls.Literal[] Literals;
              7 Literals = new System.Web.UI.WebControls.Literal[20];
              8
              9
              10 String myConnectionString = "Database=" + tDatabase.Text +
              11 " ;Host=" + tHost.Text +
              12 ";UserName=" + tUsername.Text+ ";Password=" + tPassword.Text;
              13
              14
              15 MySqlConnection myConnection = new MySqlConnection( );
              16 myConnection.ConnectionString = myConnectionString;
              17
              18 try
              19 {
              20 myConnection.Open( );
              21 MySqlCommand SpCmd = new MySqlCommand("sp_mysql_info", myConnection);
              22 SpCmd.CommandType = CommandType.StoredProcedure;
              23 MySqlParameter InDbParm = SpCmd.Parameters.Add(
              24 "in_database",MySqlDbType.String);
              25 InDbParm.Value = tDatabase.Text;
              26 MySqlParameter OutMyVersion = SpCmd.Parameters.Add(
              27 "server_version", MySqlDbType.String);
              28 OutMyVersion.Direction = ParameterDirection.Output;
              29
              30 MySqlDataAdapter MyAdapter = new MySqlDataAdapter(SpCmd);
              31 MyAdapter.SelectCommand = SpCmd;
              32 DataSet SpDataSet = new DataSet( );
              33 MyAdapter.Fill(SpDataSet);
              34
              35 ReportHeaderl.Text = "<h1>MySQL Server status and statistics</h1>" +
              36 "<b>Host:</b>"+tHost.Text+"<br>"+
              37 " <b>Port:</b> "+tPort.Text+"<br>"+
              38 "<b>Version:</b>"+OutMyVersion.Value+"<br>";
              39
              40 int grid_no = 0;
              41 int heading_no=0;
              42 foreach (DataTable SpTable in SpDataSet.Tables) {
              43 if (SpTable.Columns[0].ColumnName == "table_header")
              44 {
              45 Literals[heading_no]=new Literal( );
              46 Literals[heading_no].Text="<h2>"+ SpTable.Rows[0][0]+"</h2>";
              47 PlaceHolder.Controls.Add(Literals[heading_no]);
              48 heading_no++;
              49 }
              50 else
              51 {
              52 DataGrids[grid_no] = new DataGrid( );
              53 DataGrids[grid_no].DataSource = SpTable;
              54 DataGrids[grid_no].DataBind( );
              55 DataGrids[grid_no].BorderWidth = 1;
              56 DataGrids[grid_no].HeaderStyle.BackColor =
              57 System.Drawing.Color.Silver;
              58 PlaceHolder.Controls.Add(DataGrids[grid_no]);
              59 grid_no++;
              60 }
              61 }
              62
              63
              64
              65 }
              66 catch (MySqlException MyException)
              67 {
              68 Response.Write("Connection error: MySQL code: " + MyException.Number
              69 + " " + MyException.Message);
              70 }
              71
              72
              73 }



              There is quite a bit of code in this example, but the basic principles are fairly simple:


              • We connect to MySQL using the connection information given.

              • We call the stored procedure, passing the database name as an input parameter.

              • We cycle through the result sets in the stored procedure. If the result set is a one-line, one-column "title" for a subsequent result set, we store an HTML header into a literal control and add this to the Placeholder control we placed on the HTML form earlier.

              • If the result set is not a "title" result set, we bind the result set to a DataGrid control and add that to the Placeholder.

              • When all of the result sets have been processed, we retrieve the output parameter (MySQL version) and display this and other information in the Literal control we placed on the ASP.NET form earlier.


              Let's examine this code in a bit more detail:


              Line(s)

              Explanation

              4-7

              Create an array of DataGrid and Literal controls. DataGrids are data-bound controls similar to HTML tables. Literals are controls in which we can insert regular HTML arguments. Later in the code, we will populate the controls in these arrays with data from the stored procedure output and insert the resulting controls into the Placeholder control on the ASPX page.

              1020

              Construct a MySqlConnection string using the parameters provided in the input form and then establish a connection. The final connection call is embedded within a TRy/catch block so that we will handle any errors that might occur when attempting to connect.

              2128

              Set up the stored procedure for execution. Both input and output parameters are defined.

              3031

              Create a MySqlDataAdpator associated with the stored procedure.

              2333

              Create a DataSet, and use the MySqlDataAdapter to populate the DataSet. This effectively executes the stored procedure and populates the DataSet with all the result sets from that stored procedure call.

              3538

              Now that we have retrieved all of the result sets, we can access the value of the output parameter. Consequently, we can populate the Literal control with HTML to generate the first part of our report, which provides identity information for the MySQL server.

              4261

              Generate the bulk of the report, which is based on the result sets generated from the stored procedure. This loop iterates through the DataTables contained within the DataSet.

              4349

              If the first column within the table is called table_header, then this is a heading row, so we create a Literal containing an H2 HTML header containing the text of the row.

              5060

              If the result set is not a heading, then we need to create an HTML table to represent the output. We use the ASP.NET DataGrid control, which is a data-bound table control. Line 53 attaches the DataGrid to the current DataTable. Lines 5557 format the DataGrid. Finally on line 58 we add the DataGrid to the PlaceHolder control on the ASP.NET page.



              Figure 17-4 shows some of the output generated by our ASP.NET application. The ASP.NET code can render virtually any output that might be returned by the stored procedure, so if we want to add a new set of output to the procedure, we do not need to modify the ASP.NET code.












              7.8 Dividing a Summary into Subgroups




              I l@ve RuBoard










              7.8 Dividing a Summary into Subgroups




              7.8.1 Problem



              You want to calculate a summary for each
              subgroup of a set of rows, not an overall summary value.





              7.8.2 Solution



              Use a GROUP BY clause to arrange
              rows into groups.





              7.8.3 Discussion



              The summary queries shown so far calculate summary values over all
              rows in the result set. For example, the following query determines
              the number of daily driving records in the
              driver_log table, and thus the total number of
              days that drivers were on the road:



              mysql> SELECT COUNT(*) FROM driver_log;
              +----------+
              | COUNT(*) |
              +----------+
              | 10 |
              +----------+


              But sometimes it's desirable to break a set of rows
              into subgroups and summarize each group. This is done by using
              aggregate functions in conjunction
              with a GROUP BY clause. To
              determine the number of days driven by each driver, group the rows by
              driver name, count how many rows there are for each name, and display
              the names with the counts:



              mysql> SELECT name, COUNT(name) FROM driver_log GROUP BY name;
              +-------+-------------+
              | name | COUNT(name) |
              +-------+-------------+
              | Ben | 3 |
              | Henry | 5 |
              | Suzi | 2 |
              +-------+-------------+


              That query summarizes the same column used for grouping
              (name), but that's not always
              necessary. Suppose you want a quick characterization of the
              driver_log table, showing for each person listed
              in it the total number of miles driven and the average number of
              miles per day. In this case, you still use the
              name column to place the rows in groups, but the
              summary functions operate on the miles values:



              mysql> SELECT name,
              -> SUM(miles) AS 'total miles',
              -> AVG(miles) AS 'miles per day'
              -> FROM driver_log GROUP BY name;
              +-------+-------------+---------------+
              | name | total miles | miles per day |
              +-------+-------------+---------------+
              | Ben | 362 | 120.6667 |
              | Henry | 911 | 182.2000 |
              | Suzi | 893 | 446.5000 |
              +-------+-------------+---------------+


              Use as many grouping columns as necessary to achieve as fine-grained
              a summary as you require. The following query produces a coarse
              summary showing how many messages were sent by each message sender
              listed in the mail table:



              mysql> SELECT srcuser, COUNT(*) FROM mail
              -> GROUP BY srcuser;
              +---------+----------+
              | srcuser | COUNT(*) |
              +---------+----------+
              | barb | 3 |
              | gene | 6 |
              | phil | 5 |
              | tricia | 2 |
              +---------+----------+


              To be more specific and find out how many messages each sender sent
              from each host, use two grouping columns. This produces a result with
              nested groups (groups within groups):



              mysql> SELECT srcuser, srchost, COUNT(*) FROM mail
              -> GROUP BY srcuser, srchost;
              +---------+---------+----------+
              | srcuser | srchost | COUNT(*) |
              +---------+---------+----------+
              | barb | saturn | 2 |
              | barb | venus | 1 |
              | gene | mars | 2 |
              | gene | saturn | 2 |
              | gene | venus | 2 |
              | phil | mars | 3 |
              | phil | venus | 2 |
              | tricia | mars | 1 |
              | tricia | saturn | 1 |
              +---------+---------+----------+



              Getting Distinct Values Without Using DISTINCT



              If you use GROUP
              BY without selecting the value of any aggregate
              functions, you achieve the same effect as DISTINCT
              without using DISTINCT explicitly:



              mysql> SELECT name FROM driver_log GROUP BY name;
              +-------+
              | name |
              +-------+
              | Ben |
              | Henry |
              | Suzi |
              +-------+


              Normally with this kind of query you'd select a
              summary value (for example, by invoking
              COUNT(name) to count the instances of each name),
              but it's legal not to. The net effect is to produce
              a list of the unique grouped values. I prefer to use
              DISTINCT, because it makes the point of the query
              more obvious. (Internally, MySQL actually maps the
              DISTINCT form of the query onto the
              GROUP BY form.)




              The preceding examples in this section have used COUNT(
              )
              , SUM( ) and AVG( )
              for per-group summaries. You can use MIN( ) or
              MAX( ), too. With a GROUP
              BY clause, they will tell you the smallest or
              largest value per group. The following query groups
              mail table rows by message sender, displaying for
              each one the size of the largest message sent and the date of the
              most recent message:



              mysql> SELECT srcuser, MAX(size), MAX(t) FROM mail GROUP BY srcuser;
              +---------+-----------+---------------------+
              | srcuser | MAX(size) | MAX(t) |
              +---------+-----------+---------------------+
              | barb | 98151 | 2001-05-14 14:42:21 |
              | gene | 998532 | 2001-05-19 22:21:51 |
              | phil | 10294 | 2001-05-17 12:49:23 |
              | tricia | 2394482 | 2001-05-14 17:03:01 |
              +---------+-----------+---------------------+


              You can group by multiple
              columns and display a maximum for each combination of values in those
              columns. This query finds the size of the largest message sent
              between each pair of sender and recipient values listed in the
              mail table:



              mysql> SELECT srcuser, dstuser, MAX(size) FROM mail GROUP BY srcuser, dstuser;
              +---------+---------+-----------+
              | srcuser | dstuser | MAX(size) |
              +---------+---------+-----------+
              | barb | barb | 98151 |
              | barb | tricia | 58274 |
              | gene | barb | 2291 |
              | gene | gene | 23992 |
              | gene | tricia | 998532 |
              | phil | barb | 10294 |
              | phil | phil | 1048 |
              | phil | tricia | 5781 |
              | tricia | gene | 194925 |
              | tricia | phil | 2394482 |
              +---------+---------+-----------+


              When using aggregate functions to produce per-group summary values,
              watch out for the following trap. Suppose you want to know the
              longest trip per driver in the driver_log table.
              That's produced by this query:



              mysql> SELECT name, MAX(miles) AS 'longest trip'
              -> FROM driver_log GROUP BY name;
              +-------+--------------+
              | name | longest trip |
              +-------+--------------+
              | Ben | 152 |
              | Henry | 300 |
              | Suzi | 502 |
              +-------+--------------+


              But what if you also want to show the date on which each
              driver's longest trip occurred? Can you just add
              trav_date to the output column list? Sorry, that
              won't work:



              mysql> SELECT name, trav_date, MAX(miles) AS 'longest trip'
              -> FROM driver_log GROUP BY name;
              +-------+------------+--------------+
              | name | trav_date | longest trip |
              +-------+------------+--------------+
              | Ben | 2001-11-30 | 152 |
              | Henry | 2001-11-29 | 300 |
              | Suzi | 2001-11-29 | 502 |
              +-------+------------+--------------+


              The query does produce a result, but if you compare it to the full
              table (shown below), you'll see that although the
              dates for Ben and Henry are correct, the date for Suzi is not:




              +--------+-------+------------+-------+
              | rec_id | name | trav_date | miles |
              +--------+-------+------------+-------+
              | 1 | Ben | 2001-11-30 | 152 | <-- Ben's longest trip
              | 2 | Suzi | 2001-11-29 | 391 |
              | 3 | Henry | 2001-11-29 | 300 | <-- Henry's longest trip
              | 4 | Henry | 2001-11-27 | 96 |
              | 5 | Ben | 2001-11-29 | 131 |
              | 6 | Henry | 2001-11-26 | 115 |
              | 7 | Suzi | 2001-12-02 | 502 | <-- Suzi's longest trip
              | 8 | Henry | 2001-12-01 | 197 |
              | 9 | Ben | 2001-12-02 | 79 |
              | 10 | Henry | 2001-11-30 | 203 |
              +--------+-------+------------+-------+



              So what's going on? Why does the summary query
              produce incorrect results? This happens because when you include a
              GROUP BY clause in a query, the
              only values you can select are the grouped columns or the summary
              values calculated from them. If you display additional columns,
              they're not tied to the grouped columns and the
              values displayed for them are indeterminate. (For the query just
              shown, it appears that MySQL may simply be picking the first date for
              each driver, whether or not it matches the driver's
              maximum mileage value.)



              The general solution to the problem of displaying contents of rows
              associated with minimum or maximum group values involves a join. The
              technique is described in Chapter 12. If you
              don't want to read ahead, or you
              don't want to use another table, consider using the
              MAX-CONCAT
              trick described earlier. It produces the correct result, although the
              query is fairly ugly:



              mysql> SELECT name,
              -> SUBSTRING(MAX(CONCAT(LPAD(miles,3,' '), trav_date)),4) AS date,
              -> LEFT(MAX(CONCAT(LPAD(miles,3,' '), trav_date)),3) AS 'longest trip'
              -> FROM driver_log GROUP BY name;
              +-------+------------+--------------+
              | name | date | longest trip |
              +-------+------------+--------------+
              | Ben | 2001-11-30 | 152 |
              | Henry | 2001-11-29 | 300 |
              | Suzi | 2001-12-02 | 502 |
              +-------+------------+--------------+









                I l@ve RuBoard



                Log4J in a Web Application





























                Chapter 18 -
                Log4J
                byVivek Chopra, Ben Galbraithet al.
                Wrox Press 2003































                Log4J in a Web Application


                Now that we have the basics of Log4J we shall move on to using it in a Java web application. The developer of the application would usually add the logging statements before it is handed to the administrator. However, it is useful to know the syntax and usage of the different options available. We'll start off by using the simple example again, but this time we'll use it from a JSP page in a web application called logging. First though, we need to configure the Logger.




                Configuring a Logger in a Web Application


                As we saw earlier, the easiest way to configure a Logger was to use a configuration file. When using Log4J in a web application it is a good idea to set up a Logger for the entire web application at startup. This can be achieved by using an initialization servlet that loads the configuration file into the web application.



                The Initialization Servlet


                Here is the initialization servlet:




                package wrox;

                import javax.servlet.ServletException;

                import javax.servlet.http.HttpServlet;
                import javax.servlet.http.HttpServletRequest;
                import javax.servlet.http.HttpServletResponse;

                import java.io.PrintWriter;
                import java.io.IOException;

                import org.apache.log4j.PropertyConfigurator;

                public class Log4JInitServlet extends HttpServlet {



                The application-wide configuration happens in the init() method. This method is called as the servlet is first loaded by Tomcat, making the Logger available from startup:




                // Initialize the servlet by loading the Log4J properties file
                public void init() throws ServletException {


                The properties file will be stored in the $CATALINA_HOME/webapps/logging/WEB-INF/classes directory of our web application. We need to get the path to the properties file before we can load it, so we get the full path to the web application on the filesystem and then append the name of the file. In this case, the name of the file comes from the properties initialization parameter specified in web.xml. This allows us to specify the location of the properties file outside of the application's code:




                // First we get the fully qualified path to the web application
                String path = getServletContext().getRealPath("/");
                String properties = path + getInitParameter("properties");


                The final step is to call the configure () method to make the Logger available for all the other files in the application:




                // Next we set the properties for all the servlets and JSP
                // pages in this web application
                PropertyConfigurator.configure(properties);
                }


                The final methods are required to be in any servlet:




                // The doPost() method forwards all requests to the doGet() method
                public void doPost(HttpServletRequest req, HttpServletResponse resp) throws
                ServletException, IOException {
                doGet(req, resp);
                }

                // The doGet() method informs users of the purpose of this servlet
                public void doGet(HttpServletRequest req, HttpServletResponse resp) throws
                ServletException, IOException {
                PrintWriter out = resp.getWriter();

                out.println("Initialization servlet for Log4J");
                }

                public void destroy() {}
                }


                Compile this class and place it in $CATALINA_HOME/webapps/logging/WEB-INF/classes/wrox.





                web.xml


                Next we need a web.xml file for our web application so that we can specify the location of our properties file:





                <?xml version="1.0" encoding="ISO-8859-1"?>

                <!DOCTYPE web-app
                PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
                "http://java.sun.com/dtd/web-app_2_3.dtd">

                <web-app>
                <servlet>
                <servlet-name>log4Jinit</servlet-name>
                <servlet-class>wrox.Log4JInitServlet</servlet-class>
                <init-param>
                <param-name>properties</param-name>
                <param-value>WEB-INF/classes/log4j.properties</param-value>
                </init-param>
                <load-on-startup>1</load-on-startup>
                </servlet>
                </web-app>


                The <load-on-startup> element loads the servlet when Tomcat is started. This is exactly what we want as we need the Logger to be available to the web application as soon as Tomcat starts.








                Logging To Files


                In this section we will start with a simple file example, then show the following formats that are made available to Log4J:





                • HTMLFormat





                • PatternFormat





                Simple File


                The configuration file for this first example is very similar to the one we saw before. However, this time we'll log to the $CATALINA_HOME/logs directory:




                # A simple logger
                log4j.logger.wrox.simple=DEBUG, simple

                # This simple example will log to a file
                log4j.appender.simple=org.apache.log4j.FileAppender
                log4j.appender.simple.File=<CATALINA_HOME>/logs/log4jsimple.log

                # We will use a simple layout for this example
                log4j.appender.simple.layout=org.apache.log4j.SimpleLayout


                Note that Log4j does not consider <CATALINA_HOME> to be a shortcut; it is merely a neat piece of notation for us. Be sure and provide the full path in your files.


                Save this file as log4j.properties in logging/WEB-INF/classes.



                Finally, we need a JSP page to use the Logger (logsimple.jsp):




                <%@ page import="org.apache.log4j.Logger" %>
                <html>
                <head><title>Logging with Log4J</title></head>

                <%
                Logger logger = Logger.getLogger("wrox.simple");

                logger.debug("A debug message");
                logger.info("An info message");
                logger.warn("A warn message");
                logger.error("An error message");
                logger.fatal("A fatal message");
                %>

                <body>
                <h1>Logging completed</h1>
                </body>
                <html>


                Place this in $CATALINA_HOME/webapps/logging. Now, point your browser at http://localhost:8080/logging/logsimple.jsp. You should see the confirmation message. This should have set off the Logger and written the logging messages to $CATALINA_HOME/logs/log4jsimple.log.





                HTML


                Now we will look at a different kind of Layout, the HTMLLayout. This Layout formats the logging information into HTML tables that can be customized to fit most needs. We shall send the logging information to an HTML file in the logging web application so that we can view it online. However, it would be possible to put this file under a password-protected context, such as admin in Tomcat 4.1.3 onwards.


                Here is the configuration file:




                # An HTML logger
                log4j.logger.wrox.html=DEBUG, html

                # This simple example will log to an HTML file
                log4j.appender.html=org.apache.log4j.FileAppender
                log4j.appender.html.File=<CATALINA_HOME>/webapps/logging/log.html

                # This line stops additional log sessions being appended to the end
                # of the file
                log4j.appender.html.Append=false

                # We will use an HTML Layout for this example
                log4j.appender.html.layout=org.apache.log4j.HTMLLayout

                # This line shows where in the servlet/JSP page the logging
                # statement is placed
                log4j.appender.html.layout.LocationInfo=true

                # The Title parameter sets the HTML <title> tag for the HTML page
                log4j.appender.html.layout.Title=Wrox Log



                The JSP page for this example, loghtml.jsp, has only one change:




                <%@ page import="org.apache.log4j.Logger" %>
                <html>
                <head><title>Logging with Log4J</title></head>

                <%
                Logger logger = Logger.getLogger("wrox.html");

                logger.debug("A debug message");
                logger.info("An info message");
                logger.warn("A warn message");
                logger.error("An error message");
                logger.fatal("A fatal message");
                %>

                <body>
                <h1>Logging completed</h1>
                </body>
                <html>


                Access this page at http://localhost:8080/logging/loghtml.jsp and then view http://localhost:8080/logging/log.html. You should see something similar to the following:








                Pattern


                The final Layout that we will look at is the PatternLayout. This Layout allows us to specify a custom format for the logging messages. The format can be configured in a number of ways with various format modifiers and conversion characters playing their part. We saw one example earlier, but here is the example we will be using in this section:




                %-5p : %m %d{dd-MM-yy HH:mm:ss}%n



                We have already seen the %m and %n conversion characters, as well as a different version of the %d character. This time we have specified a custom format for the date between the {} markers. The initial %-5p section can be further broken down into the -5 and %p blocks. %p displays a string representation of the priority (a throwback to earlier versions of Log4J when a Priority did the same job as a Level), that is, 'DEBUG', 'WARN', and so on. -5 is a format modifier that says 'right pad with spaces if the priority is less than 5 characters long'.




                A full list of the format modifiers and conversion characters is available in the Log4J documentation: http://jakarta.apache.org/log4j/docs/api/org/apache/log4j/PatternLayout.html.



                We specify this pattern for our file as follows:




                # A pattern logger
                log4j.logger.wrox.pattern=DEBUG, pattern

                # This simple example will log to a custom format file
                log4j.appender.pattern=org.apache.log4j.FileAppender
                log4j.appender.pattern.File=<CATALINA_HOME>/logs/log4jpattern.log

                # We will use a custom layout for this example
                log4j.appender.pattern.layout=org.apache.log4j.PatternLayout
                # Here we set our custom pattern
                log4j.appender.pattern.layout.ConversionPattern=%-5p : %m %d{dd-MM-yy HH:mm:ss}%n


                The only line that has changed since our last JSP page is:




                Logger logger = Logger.getLogger("wrox.pattern");


                Point your browser to http://localhost:8080/logger/logpattern.jsp and then view $CATALINA_HOME/logs/log4jpattern.log. The log entries should look something like this:




                DEBUG : A debug message 06-08-02 01:54:21
                INFO : An info message 06-08-02 01:54:21
                WARN : A warn message 06-08-02 01:54:21
                ERROR : An error message 06-08-02 01:54:21
                FATAL : A fatal message 06-08-02 01:54:21








                Logging To the Console


                Log4J allows us to log to other destinations other than files. In this section we shall send logging messages to the console. This allows an application to instantly notify the administrator of any problems that occur. To log to the console we need to attach a ConsoleAppender to our Logger:




                # A console Logger
                log4j.logger.wrox.console=DEBUG, console

                # This simple example will log to the console
                log4j.appender.console=org.apache.log4j.ConsoleAppender
                # Set the target to Sysytem.out
                log4j.appender.console.Target=System.out

                # We will use the simple format for the console example
                log4j.appender.console.layout=org.apache.log4j.SimpleLayout


                The Target parameter must be one of System.out (the default) or System.err.ConsoleAppenders accept the same Layouts as FileAppenders: in this case we are using a SimpleLayout.


                Again, our JSP page (logconsole.jsp) only needs one change:




                Logger logger = Logger.getLogger("wrox.console");


                Accessing http://localhost:8080/logging/logconsole.jsp will cause the log messages to be logged to Tomcat's console:









                Logging To Multiple Destinations


                One of the more useful features of Log4J is the ability to log to several destinations at once. This can allow us to send serious error messages to a location where they will be instantly noticed, for example the console, while other more routine messages can be sent to a regular logging file.


                We shall look at the following examples in this section:




                • Logging FATAL events to the console and all events to a file




                • Logging FATAL events to the console and all events to Unix's syslog system Logger




                • Logging FATAL events to the console and FATAL and ERROR level events to WinNT's Event Logger





                Console and a File


                Our first example will log all FATAL level events to the console so that an administrator will be instantly alerted to any serious problems with an application. Also, we still need a record of other events (and a permanent record of FATAL events) so we will log all events to a file.



                First we assign two Appenders to the wrox.multifile Logger: fatalconsole and errorfile. We also set the default logging level:




                # A multi-destination logger
                # FATAL errors will go to the console
                # All errors will go to a file
                log4j.logger.wrox.multifile=DEBUG, fatalconsole, errorfile


                Now we set the fatalconsole Appender's type to ConsoleAppender, set the target to System.out, and set the logging level to FATAL, using the Threshold parameter of the Appender:




                # All fatal messages for this example go to the console
                log4j.appender.fatalconsole=org.apache.log4j.ConsoleAppender
                log4j.appender.fatalconsole.Target=System.out
                log4j.appender.fatalconsole.Threshold=FATAL


                Next we set up the file where all log messages will be sent. We also set the logging level to DEBUG:




                # All messages go to a file
                log4j.appender.errorfile=org.apache.log4j.FileAppender
                log4j.appender.errorfile.File=<CATALINA_HOME>/logs/log4jerrors.log
                log4j.appender.errorfile.Threshold=DEBUG

                # We will use simple layouts for both the console and the file
                log4j.appender.fatalconsole.layout=org.apache.log4j.SimpleLayout
                log4j.appender.errorfile.layout=org.apache.log4j.SimpleLayout


                Now all we need to do to set up this example is change the same line of code in our JSP page (fatalconsole.jsp):




                Logger logger = Logger.getLogger("wrox.multifile");


                The logging method calls remain the same. Now each time a logging message is logged by the wrox.multifile Logger it is compared to the level of each Appender and only sent to its destination if it is less than or equal to this level.


                Now if you point a browser at http://localhost:8080/logging/fatalconsole.jsp you should see the following in the Tomcat console:






                Now look at $CATALINA_HOME/logs/log4jerrors.jsp:




                DEBUG - A debug message
                INFO - An info message
                WARN - A warn message
                ERROR - An error message
                FATAL - A fatal message


                As you can see, Log4J has only sent FATAL level messages to the console, but has sent all messages to the regular logging file.





                Console and Syslog


                The Unix syslog program is an integral part of all Unix systems. It was designed with two important functions in mind: liberating programmers from having to write log files and putting logging in the hands of the administrator.


                Log4J provides the SyslogAppender for working with syslog. It needs a bit more configuring than the other Appenders we have seen before, but it is still relatively straightforward. In this example, we will again send FATAL level events to the console, but this time we will send all events to syslog as user-level events:




                # A multi-destination logger
                # FATAL errors will go to the console
                # All errors will go to syslog
                log4j.logger.wrox.multisyslog=DEBUG, fatalconsolesys, syslog

                # All fatal messages for this example go to the console
                log4j.appender.fatalconsolesys=org.apache.log4j.ConsoleAppender
                log4j.appender.fatalconsolesys.Target=System.out
                log4j.appender.fatalconsolesys.Threshold=FATAL


                Here is the SyslogAppender configuration. After setting the syslog Appender type, we set its logging level. We then need to set the syslog facility using the SyslogAppender's Facility parameter. syslog uses facilities alongside a severity index to decide where the logging message should be sent, much like Log4J does with the logging level and Appender name. Many system functions have their own facility, for example sendmail has the mail facility and the kernel has the kern facility. In our example we will use the user facility that is designed for user-level processes.


                The final piece of configuration for a SyslogAppender is the SyslogHost parameter. This should specify the central logging host of your network, or localhost if you are logging to the local machine:




                # All messages go to syslog
                log4j.appender.syslog=org.apache.log4j.net.SyslogAppender
                log4j.appender.syslog.Threshold=DEBUG
                log4j.appender.syslog.Facility=USER
                log4j.appender.syslog.SyslogHost=localhost

                # We will use simple layouts for both the console and syslog
                log4j.appender.fatalconsolesys.layout=org.apache.log4j.SimpleLayout
                log4j.appender.syslog.layout=org.apache.log4j.SimpleLayout








                Important 

                Note that if syslog is running on a Linux box over a network, you will have to start it with the -r switch to enable network logging. This can be set in the /etc/init.d/syslog file. Run /etc/init.d/syslog restart to restart syslog.



                Again, there is only one change to the JSP file:




                Logger logger = Logger.getLogger("wrox.multisyslog");


                Point a browser at http://localhost:8080/logging/fatalsyslog.jsp and then examine the log file where syslog sends all user-level log messages. You should see the messages listed in syslog's format, which should look like the following:




                Aug 5 16:48:40 matthew user: DEBUG - A debug message





                Console and WinNT Event Logger


                Our final example will be logging messages to the console and WinNT's Event Logger. The Event Logger can be found under Start | Settings | Control Panel | Administrative Tools | Event Viewer. This is where WinNT logs its system messages. These messages include serious errors in applications like Internet Explorer, as well as less important messages such as print job completion messages. WinNT messages have error types that are similar in concept to Log4J Levels. For example, WinNT Errors correspond to Log4J's FATAL and ERROR levels.


                In this example, we will again send only FATAL errors to the console, but this time only FATAL and ERROR messages will be sent to the Event Logger:




                # A multi-destination logger
                # FATAL errors will go to the console
                # FATAL and ERROR will go to the NT Event Logger
                log4j.logger.wrox.multiwinnt=DEBUG, winntconsole, winntlogger

                # All fatal messages for this example go to the console
                log4j.appender.winntconsole=org.apache.log4j.ConsoleAppender
                log4j.appender.winntconsole.Target=System.out
                log4j.appender.winntconsole.Threshold=FATAL


                Here is where we configure the NTEventLogAppender that allows us to write to the Event Logger:




                # All fatal and error messages go to the WinNT logger
                log4j.appender.winntlogger=org.apache.log4j.nt.NTEventLogAppender
                log4j.appender.winntlogger.Threshold=ERROR


                The Event Logger displays the source of the message in its window. By default, Log4J messages are given the name 'log4j'. This isn't very useful if we have a number of applications using Log4J, so we will add our own source name:




                # We will set the Source parameter so we know which application
                # this message came from
                log4j.appender.winntlogger.Source=Wrox Logging



                The Event Logger can take the same Layouts as other Layouts, but only SimpleLayout and PatternLayout make sense in this situation (as we shall see when we examine the log message):




                # We will use a simple layout for both the console and the Event Logger
                log4j.appender.winntconsole.layout=org.apache.log4j.SimpleLayout
                log4j.appender.winntlogger.layout=org.apache.log4j.SimpleLayout


                Here's that line of code again:




                Logger logger = Logger.getLogger("wrox.multiwinnt");


                Viewing the page http://localhost:8080/logging/fatalwinnt.jsp will send the usual FATAL message to the console as well as an ERROR message and a FATAL message to the Event Logger:





                Both messages are logged as WinNT errors, but are given different categories. Double-clicking on a log message will show its properties:






                The Description box explains why the SimpleLayout and PatternLayout are the only sensible options for this kind of Appender: putting HTML tables into this box would make it almost unreadable.