BIT320 Texts

Main BIT320 Texts Page

October 11, 2005

A Guide to SQL, Chapter 2

The key point is to establish good SQL techniques early and practice them.

Much of the "concept" stuff at the beginning of this chapter you will already be familiar with.

Our book covers multiple database systems: Oracle, Access, and MySQL. You only need to be concerned with the stuff covering Oracle. The basics of SQL are the same across all three, but the details of interacting with them are different.

The stuff under "Entities, Attributes, and Relationships" should be very familiar to you. Look how far you've already come this semester!
p27: Notice the name of the field rep.rep_number. I would have named the field (using the first four rules from the document Defining a database's tables and fields) as rep.num. Read this other document to help you understand my reasoning. BTW, I didn't use number as the field name because it is a reserved word in SQL (like table, order, integer, etc.). You have to be careful about these; they can trip you up sometimes.
Database creation: Read through this description, and look at the create command on the next page at the same time to ensure that you can read it.
p33, "SQL Commands": It is very important that you establish good formatting habits when typing in your SQL statements. It will pay off many times over when you're looking for errors in your SQL statements.
p33: Pay attention: You can safely ignore all this command buffer and editing commands stuff. You are going to use jedit (or whatever you choose) to create and maintain command files instead of using the command buffer. If you want to learn all this command buffer stuff, you can. Don't let me stop you. But I think it's much easier to use jedit.
p36-40: Instead of following their instructions, use jedit to create a command file, call it ex1.sql, and save it in the SQL directory on your account on elab-linux4. In your ssh window at the Oracle prompt, type @ex1 and press enter. This should execute (if you typed in the command correctly) and tell you that the table has been created. If it gives you an error message, use jedit to edit the file, save it again, and execute it again. Repeat until the command file executes without error.
p41, end of page: Also, notice how easy it would be to re-create the table after you've dropped it if you put the create statement in a command file (which you did, I might point out). You simply execute the file again! No need to re-type (and subsequently debug) the command. Yahoo!
p42, table 2.13: I almost never use char(n) except for something like state codes (since they have a fixed length of 2 characters). For all other character data I use varchar(n). I also don't use decimal(p,q). I use integer for whole numbers and number for numbers that can have a decimal part. Why? Because I haven't figured out what is gained by specifying (p,q) instead of simply letting Oracle figure it out. Everything has worked so far, so why change a good thing?
p43, top of page: On the next page you're going to get to a Question and Answer. Please try to answer these questions before you read the answer, not just this one, but all of them. These are generally pretty good and emphasize points that I think are important.
p44, Example 2: Use jedit to create a file called ex2.sql. Execute it in sqlplus by typing @ex2.
p45, Example 3 and the following 4 figures: You don't have to do all this. Open another window in jedit. Copy the information from the ex2.sql. Paste this information into the new window twice. Save this new window as ex3.sql. Modify the commands in the new window to reflect the proper data. Save the file again. In the ssh window execute this command in sqlplus. (You should know how to do this by now.)

I find this way of working much more intuitive than the whole command buffer thing.
p48, Example 4: Guess what? Create a command file called ex4.sql, save it, execute it.
p50, top: To interact with sqlplus and to enter commands that you're not worried about having to edit or run again, simply type the commands at the SQL> prompt. Do so here. FYI: You can enter select * from sales_rep; all on one line — as before, formatting doesn't matter.
p56: The delete command is very powerful. Be very careful. If you enter delete from sales_rep; with the semi-colon at the end and then press return, Oracle will gladly delete all the data from your table! No problem! But that might not be what you want. You might have wanted to enter a where clause (as shown in the middle of p56) and simply pressed the wrong key. Oops! As I said, be careful when using this command.
p56, "Saving SQL Commands": You're actually going to do what's in this section, but not how the author says (surprise!). You're going to use the pico text editor. Pico is the text editor used by the pine email program so you should be familiar with it. It can be useful to know how to use when you don't have jedit installed on the machine you're working on or when you're simply making a quick change to a file you haven't opened in jedit.

To use pico, type
edit cre_cust
at the SQL> prompt. This will open pico and show a blank file (or the contents of cre_cust if it has already been created). Type in the create statement. Type ctrl-O to write the information to the elab-linux4 disk without quitting pico. Type ctrl-X to save the information and exit out of pico. You're returned to the sqlplus prompt. As before, type @cre_cust to execute this command. If you have errors in the file, type edit cre_cust to open the file again, make the necessary changes, exit out of pico, and run the file again.
p58-62: Create command files for each of the figures on these pages. Execute them in order. Don't execute one until you've successfully executed all the previous ones. You'll have to watch very carefully for typos, and will have to go through many cycles of correcting, editing, and re-running until you get these right.
p67, Exercises, Note: It mentions a SPOOL file. This is nice, but generally won't be necessary for a while. Why? Because in the SSH and Telnet document, you went through a process that set up the terminal program so that you can scroll back up to 2000 lines in the display. So, much of what you did is available simply by scrolling up in the window. Later, when you are working with more tables and more data, you will want to use this spool stuff. For now in these simple exercises, it's probably not worth it.
p68, Premiere Exercises: You should have already done #1. Do numbers 2, 3, 4, 5, & 6. Why? Because the pain and suffering you go through here will help you out when you're doing your project. Also, this data and these tables are used throughout the rest of the book.
p68, Henry Books: As I've already stated: you don't have to do the Henry Books exercises.

Bud last edited this October 11, 2005 | Permalink | Comments (0)

October 13, 2005

A Guide to SQL, Chapter 3

We start querying, the way to get useful information from your database.

In this chapter you'll begin to see some of the power of SQL and Oracle. You're not going to see any foreign key or purely "relational" stuff yet - that'll come in the next chapter. However, never fear, this chapter will make you think a bit. In this chapter you begin the process of learning how to write the SQL select statements that we talked about toward the end of E-R modeling.

Before you start 1: Make sure you complete the exercises in chapter 2 before starting this chapter. You will be querying against the tables you create in those exercises so you won't have anything to do if you haven't created the tables and put data into them.
Before you start 2: In these few steps you're going to change the settings of sqlplus so that it prints the results of queries across a wider line. Currently, it is set to wrap output to a new line after 79 characters; we're going to change it so that it wraps after 128 characters.
1. Use the SSH Secure Shell to log in to elab-linux4 and change to the SQL directory
2. Maximize the SSH window
3. Type pico login.sql
4. Change the third line to set linesize 128
5. Save the file and exit pico
6. Start sqlplus
7. Type select * from customer
8. The results of this query should be around 100 characters and should not wrap to a new line. If so, then you successfully completed the above steps. If not, contact me.
Examples: You should type these into sqlplus as you go. Pay attention to what you're typing to try to get a feel for how SQL select statements are structured.
p78, after Note after Example 4: Yes, this is true - but there are two ways around it.
1. Convert the values in the field to all upper case when comparing it to your target string. For example, you would type in the following instead of what's in Figure 3.4:
```
select num from customer where upper(last) = 'ADAMS';
```
  This ensures that, no matter how you enter the data (e.g., 'adams', 'ADAMS', 'Adams'), the select statement will retrieve the proper records.
2. Type the values in as all upper case to begin with. You can then use Oracle functions to format the output as initial-caps (e.g., "Adams" instead of "ADAMS").
p82, Figure 3.15: Notice the heading of the 4^th column of the output. This is sorta ugly. If you want to make the meaning of this computation more clear, you can rename this column. Change the select statement to the following:
```
select customer_number, last, first, 
      (credit_limit - balance) "AVAILABLE CREDIT"
  from customer
  where credit_limit >= 1500;
```
Look at the output. Now it doesn't even appear that the last column is any different than the other columns. Unless you wrote the query, you would never know that the last column was computed and not simply retrieved.
p84, note: This is quite true, no doubt. You won't be using any large databases in this class. By "large" the author means one with millions of rows, not dozens or hundreds.
p89, 2nd note: This is a really good point. When using a numeric field, you should be very careful about leaving it blank when you might really mean to put a zero in it. The "average" example that the author talks about in this paragraph is a good question to ask yourself: "If an average is calculated on this field, do I want this row to be ignored (then put a NULL in the field) or do I want it to count as zero (then put a zero in the field).
p91, Figure 3.29: Make sure you understand this answer and why the previous answer is wrong.
p91, Nesting Queries: This is where queries can get to be really really hairy. Hang on. These will become much clearer with practice.
p100, exercises: Do all these exercises for Premiere Products. If you forget the name of a column, type DESC tableName (e.g., desc order_line), and Oracle will return the names of each field in that table. If you can't figure out how to do an exercise, ask someone sitting around you if you're in the lab or post a message to the remix site if you're not.

For each exercise you should create a separate command file. Call it ex3-XX, where XX is the number of the exercise. To review: you type edit ex3-xx to create or edit the file, and @ex3-xx to run the file. Of course, feel free to just use jedit
1. In question 8, be sure re-name the calculated field "ON_HAND_VALUE".
2. For question 9, I tried the following:
```
select part_num, description, (on_hand * price) as on_hand_value
from part
where on_hand_value > 7500;
```
  Oracle doesn't like this. It appears that the re-naming of the column (in the first line) doesn't have any affect in the where clause. So I had to type
```
select part_num, description, (on_hand * price) as on_hand_value
from part
where (on_hand * price) > 7500;
				
```
  This works just fine.

Bud last edited this October 13, 2005 | Permalink | Comments (0)

A Guide to SQL, Chapter 4

We dive into multitable queries.

This is the chapter in which you get to explore the "relational" part of relational databases. This material is critical for the test and the project.

p110, Using Exists: This is useful information. There is an important concept introduced on p111, correlated subqueries. Understanding this concept will stand you in good stead when you are working on the project. One good feature of subqueries is that they allow you to break up the logic of a more complicated query. I typically think of queries in parts. Step 1 "Find this", then Step 2 "Use the thing found in Step 1 to help with finding a new thing".
p112–113, Figures 4.8 & 4.9: I would formulate the query as in 4.8, not 4.9. Both are good but I think in the way shown in 4.8.
p114, Figure 4.10: You should be able to write this query - if not now, then soon. It's really not that complicated, just so long as you put it together in pieces. You would write the select, from, and where clauses first. You would execute this statement to ensure that it works. Then you would add the order by clause since that's not very complicated. Execute it to see if it works. Then you would add the group by clause and test it. Finally, you would add the having clause and test it. Then it works and is a fairly complicated query, but it's not so bad to write if you approach it in pieces.
p114, Using an Alias: I use aliases all the time. Nothing exciting about them, just useful.
p115, Example 9: You should read this example very carefully. Focus on the "Question and Answer" on page 117 also.
p115, Example 10: Remind yourself that you need to build this type of long query piece by piece, as described in my note for Figure 4.10. Don't try to do it all at once. And, if you had an ER diagram for this problem, you would be able to trace a path through the diagram; you could then use this path to determine how to connect the primary and foreign keys.
p120, Figure 4.16: This is exactly the type of query in which you should use aliases when constructing it. It would make it much shorter, less cluttered looking, easier to read.
p121, beginning after the Q&A: Well, what do you know! This is exactly the point I've been trying to make. Must be a coincidence!
p121, Set operations: Be sure that you know the differences among the three set commands. Be doubly sure that you follow the examples and figures in this section. (BTW: for me, this is where I really begin to get the idea that SQL is pretty cool and is a pretty powerful way of interacting with a database. The next section only reinforces these feelings.)
p134-135, Exercises (Premiere Products)Do them all. Every single one of them. You actually don't have to. Only if you want your project grade to be halfway decent. (A little prof humor there). This is really important and powerful stuff that you should be (or get) comfortable with.
Cross-product queries. One thing not mentioned is what happens if you include columns from two tables but do not link the two tables in the where clause of the query. Try it like this:
```
select description, customer_num
      from part, customer
      where customer_nubmer = '462';
```
Why do you think this result happened? How is oracle matching customer with part? Note, this example is trivial. It is easy to make this sort of error in complicated queries. My advice, in small databases (like the one in the project) where you know what the data is, you can easily check the query results with what you know the answer should be. Do this.
p134, Exercises:
1. Ex 1: Begin to get in the habit of using aliases in any query that joins more than one table. Also, use the alias name with every field name throughout the query. Why? Because it's the right thing to do and you should trust your professor. Need more than that? Because if you add another table to the query after its initial formulation, it's very possible that a field from the new table might have the same name as a field from a table already in the query. Which field? Don't know. You'll have to go through all the fields in all the tables to find the ones with the same names. Don't want to do that? Then use aliases like I said. It's saved me countless hours. Further, in my opinion, queries that use aliases correctly and well are easier to read than those that don't.
2. Ex 4 and 5: Do these two queries produce the same answer for you? If not, why? What could you do to get them to produce the same answer?
3. Ex 8: As a starting point for this exercise, you should copy your answer from the previous exercise. To do this, type
```
cp ex4-07.sql ex4-08.sql
```
  Then edit ex4-08.

Bud last edited this October 13, 2005 | Permalink | Comments (0)

October 20, 2005

A Guide to SQL, Chapter 6

Views and integrity constraints are the meat of this chapter.

This is the chapter in which all that you have learned about SQL and database design comes together, especially concerning the topics of views and foreign keys. Pay close attention to the discussion of these topics. Views are unbelievably important to a database administrator, and and understanding of them are probably the defining characteristic that differentiates between your random database user and a professional.

p160, 4th paragraph, sentence beginning "To illustrate": This is actually quite a realistic example. Think of Juan as being the product manager for housewares. He wouldn't care about any information in the database other than hardware, so the best thing to do is to make the database appear to contain only hardware information.
p160-170, examples 1-5: I can't emphasize how much just how important these examples and the concepts presented in these examples are. As I was reading them I kept writing down "important," "important," etc. Not so helpful. It's sorta like reading a chapter and highlighting the whole thing. But that's how this section is. Read these pages carefully.
p164, Examples 3-4: This is a very common strategy behind the creation of a view. Find a fairly common select statement that is going to be executed a lot, and define a view for it.
p165-9: Make sure you know and understand the benefits of using views.
p170-173: Contrary to what the book implies, you can grant and revoke various privileges with your accounts. However, the commands provided in the book are all wrong for Oracle, perhaps because the authors did not test them assuming you would not try them. Below are corrected versions in example order. The main issues are that some of the commands the authors list have to have the arguments in a different order. Also, as a simple user, you can only grant privileges on one table at a time. Note, that my grants are to an account, fpgstu4, my student account. To test this, you might want to grant privileges to one of your friends and see if they can indeed access the tables as you intended.
```
grant select on rep to fpgstu4;
grant insert on part to fpgstu4;
grant update (last, first, street) on customer to fpgstu4;
grant delete on order_line to fpgstu4;
grant select on part to public;
grant index on rep to fpgstu4;
grant alter on customer to fpgstu4;
grant all on rep to fpgstu4;
```
Here are the scripts that revoke these privileges:
```
revoke select on rep from fpgstu4;
revoke insert on part from fpgstu4;
revoke update on customer from fpgstu4;
revoke delete on order_line from fpgstu4;
revoke select on part from public;
revoke index on rep from fpgstu4;
revoke alter on customer from fpgstu4;
revoke all on rep from fpgstu;
```
p173: The main place we use indexes (I hate that word; better to use indices but no one seems to use it in the database world) is on fields that are a foreign key and not part of the primary key. You should index all of these fields. After that it's simply a matter of creating an index for a field if users tend to search on it a lot.
p178, Unique Indexes: For what type of relationships (if any) are unique indexes required?
p178, System Catalog: The system catalog is a real big deal in big-time databases. There are dozens of tables of information available about the database. One of a database professional's key resources and responsibilities are related to understanding and properly retrieving data from the system catalog.
p179, Figure 6.18: This is the query that executes when you type @list_tables.
p180, Figure 6.20: You are not the database administrator so you do not have the dba_tab_columns table available to you. You can execute this query against the user_tab_columns table provided you drop the part of the where clause that refers to the owner. Why? Because the user_tab_columns table only contains information about tables that you own, so the owner of each of these tables would be the same, so it does not store this information. The other tables they list can also be accessed by substituting user_ for dba_ and making the appropriate changes to the query as just outlined.
p181, Integrity in SQL: Finally, in the last section of this book that we're going to cover, we get to one of my favorite topics.
p184, check constraint in middle of page: When I see a constraint like this, I wonder why another table wasn't created for item classes, with one field for the primary key (e.g., AP, HW, SG, etc.) and another for the name of the class (e.g., housewares, etc.). I'm not saying that you have to have a separate table for this information. I'm simply saying that I would raise a red flag and investigate why you decided not to create a separate entity type for this information.

Bud last edited this October 20, 2005 | Permalink | Comments (0)

A Guide to SQL, Chapter 5

All about how to alter tables.

In this chapter you're not going to learn anything that's difficult - like all this query stuff - but you are going to learn some really useful commands that will help you maintain and build a database.

Notes on "Commit and Rollback"

Read this section of the book before you read this note.

This is unbelievably useful and comforting. Think about it: you can make any change you want to the data to your database knowing - knowing - that if you need to undo them, then it won't be a problem.

But, Houston, we have a problem. Actually, two. First, we have completely overridden this capability during the first two sessions of using Oracle.

If you haven't started sqlplus yet, do so.

Look at the notes that appear in the ssh window just above the Oracle8i prompt.

Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production
With the Partitioning option
JServer Release 9.0.1.0.0 - Production

Pico is the editor.
AutoCommit is on.
Login.sql loaded.

Specifically, notice the second-to-last line. This says that autocommit is on. What this means is that the commit/rollback feature is disabled. Every change to the data is automatically committed to the database. Once a change is committed to the database, it cannot be rolled back.

To turn autocommit off, do the following:

Type edit login
On the third-to-last line, which says set autocommit on, change it so that it is set to off
Change the prompt on the next line to off as well
Save the file
Exit back to the Oracle prompt
Exit out of sqlplus
Start up sqlplus again
Notice the change in the prompt when sqlplus starts

Of course, if you want to change commit so that it is back on, I think you can probably figure it out.

Above I mentioned that we have two problems, but I've only mentioned one. The second is that autocommit is now off. That means that if you work at Oracle for 24 hours straight, and then lose the connection to Oracle or exit Oracle without typing commit, then you will lose all the work that you've done.

And that can really stink.

That's not a bug, that's a feature. So, if you decide to work with autocommit off, then you need to remember to type commit before you exit Oracle.

p142, third sentence: The author says when necessary include a where clause. When necessary!?! Holy smokes! If you don't include the where clause, then the update command will change every row in the table. There are very specific instances when you don't want a where clause, but these are the exception rather than the rule. This is exactly the kind of mistake for which rollback exists.
p144, after the Note: To be clear about this, understand that rollback will rollback the effects of every and all commands entered since the last time changes to the database were committed (up to the beginning of your working session). So, if you enter all the statements in this chapter up to the exercises, you can enter rollback after every example or just once right before the exercises. In either case, the effect is that the database will not have changed.
p147, after the Q&A: And you enter rollback to get all your data back.
p150, Figure 5.12: Notice that the update command has no where clause. This is one of the places where this clause is not used. You have added a column to every row in the database and you want to ensure that every row has a value; thus, you use the update command without a where clause.
Exercises: Do the exercises for Premiere Products. Do not commit these changes to the database; i.e., type rollback after each and every exercise.

Bud last edited this October 20, 2005 | Permalink | Comments (0)

November 16, 2005

XSLT for Dummies — Chapter 3

Some preliminaries explaining XSLT

This chapter is an important overview of how stylesheets work. In particular, it provides important details about how XSLT views XML documents and how it chooses templates to apply. The advantage of using something like XSLT is that it is a general approach that is being built into most programming languages (e.g., .NET, Java, Perl, PHP). Therefore, if you learn XSLT, you are learning a generally applicable approach.

Pages 32-36: This is a good reference for what we have done so far with stylesheets. In particular, the table on Page 32 provides a good summary of the elments that control stylesheet processing and output.
Pages 36-38: Remember when I told you about the xsl: prefix. The section on namespaces gives the details of how that works.
Pages 38-40: To use the basic features of XSLT, you can get by without understanding how XSLT represents documents. However, even in this class, we need a better understanding of how XSLT views the xml source and output it creates. These five pages provide a good introduction to XSLT's tree view of documents. We've been using the terminology. Now, you can see it graphically.

Bud last edited this November 16, 2005 | Permalink | Comments (0)

XSLT for Dummies — Chapter 4

We finally get into doing XSLT and making it work for ourselves.

This chapter focuses on templates and template rules. These are the bread and butter of the transformations we have been talking about in class. I went through from page 50 on and created stylesheets for each of the examples. You should run these interactively as you read the material. You will note, on occasion, that the output shown in the book is sometimes just a subset of the output you get when you apply the stylesheet to the xml file. In these cases, the book has removed the extra output to reduce clutter. However, you should ask yourselves why the extra output occurs.

The examples are available from the class web site as XSLTfdCh04.zip under In Class for the day this chapter is assigned. You should transfer them to elab-linux4 and unzip them in a directory, for instance XSLTfdCh04 under webapps. To apply a stylesheet to an xml file, you should type oraxsl file.xml stylesheet.xsl where file.xml is the name of the xml file and stylesheet.xsl is the name of the stylesheet.

Here's how I made the stylesheets. Each time there was an xsl:template or xsl:styleheet example on a page, I constructed a stylesheet to illustrate it. The stylesheets are named according to the pattern basic.p??-#.xsl where ?? indicates the page number and # indicates which example it was on that page. Pages had up to four examples. So, for instance, to apply the stylesheet basic.p50-1.xsl to tv.xml, you would type oraxsl tv.xml basic.p50-1.xsl at the command line in the directory where you have both files on elab-linux4.

If you interactively do the examples as you go, you will gain a very good working knowledge of XSLT. Follows notes on specific elements:

Page 54: Why does basic.p54-2.xsl produce the output it produces when applied to miniscore.xml? Why do I not just get output for the film elemen? Hint: When is this template applied in processing? Are any default rules being applied (described fully on pages 58-62)?
Page 55: Why does basic.p55-2.xsl produce the output it produces when applied to miniscore.xml? Why do I not just get output for the composer element? Hint: This is just like the question above.
Page 56: Why doesn't basic.p56-1.xsl produce extra output (i.e., the text value of the nodes that do not have specific template rules) when applied to miniscore.xml? Hint: What element is being matched by the template rule? If a template rule matchtes a node, it takes over processing for that node and all of its children. If xsl:apply-templates is not part of the matching template, the default rules will not apply.
Pages 58-60: Here the built-in template rules are described in detail.
Page 61: Pay close attention to these matching rules. The author does not discuss modes as we have in class. All a mode does is say that a template rule belongs to a set. When you apply template rules of a particular mode, you are just saying to apply nodes from a particular set. Templates with a mode attribute only match when their mode is the one requested in xsl:apply templates.
Page 63: Finally, some discussion of named templates. Note how the author uses them; very similar to what we have seen in class.

Bud last edited this November 16, 2005 | Permalink | Comments (0)

November 21, 2005

XSLT for Dummies — Chapter 5

We are introduced to XPath, the standard that makes matching in XSLT go.

This chapter focuses on XPath. XPath is used all-over in XSLT. It is a method for locating nodes and attributes based on their position in the XML document. This part of the XSLT specification is becoming widely adopted and is very widely used, even for non-XML applications. If you take XPath step-by-step, it is not so bad. If it seems a little overwhelming (which it can the first time through), focus on the child, attribute, self, and sibling axes. They are the ones we will use the most.

As with Chapter 4, I went through from page 73 on and created stylesheets for each of the examples (the author does not provide them until Chapter 6). You should run these interactively as you read the material. Unlike the last chapter, I tried to remove extraneous output in these examples. I did so by providing empty template rules for elements I did not want to copy to the output. This deleted both the element and its children. Why? As XSLT processes the source document, it starts at the root and then descends down each branch of the tree until it runs out of branches. Every time, it encounters a node that matches a template rule, it passes of processing of that node and its children to the template rule. Therefore, if, as is the case with empty template rules, the template does not specify any output, the node and its children are effectively deleted.

The examples I created are available from the class web site as XSLTfdCh05.zip under In Class for the day this chapter is assigned. You should transfer them to elab-linux4 and unzip them in a directory, for instance XSLTfdCh05 under webapps. To apply a stylesheet to an xml file, you should type oraxsl file.xml stylesheet.xsl where file.xml is the name of the xml file and stylesheet.xsl is the name of the stylesheet.

If you interactively do the examples as you go, you will gain a very good working knowledge of XSLT. Follows notes on specific elements:

Page 73: Note that my stylesheet has a few extra elements in it. They are:
```
<xsl:output omit-xml-declaration="yes" indent="yes"/>
<xsl:strip-space elements="*"/>
```
at the head of the document and
```
<xsl:text>&#xa;</xsl:text>
```
in the middle of the document. We have seen the xsl:output element before. The xsl:strip-space element says to delete any extraneous spaces, tabs, or carriage returns from the output. It helps make the formatting look nice. The code 
 in the xsl:text element outputs a carriage return. All of these little pieces were added just to make things look better.

Bud last edited this November 20, 2005 | Permalink | Comments (0)

November 28, 2005

XSLT for Dummies — Chapter 6

You should be putting it all together in this chapter.

This chapter brings together the following elements:

The XSLT algorithm for applying template rules to the source xml document (Chapter 3).
Template rules themselves (Chapter 4).
XPath for determining which elements in the source document that template rules apply to (Chapter 5).

These elements should be coming together for you also. In particular, you should understand how XSLT applies templates to the source document. This chapter makes extensive use of empty templates to suppress processing of parts of the source document. It also uses templates that match specific elements to perform special processing on those while using the identity transformation (XSQL1) to copy all of the other nodes in the source document. You are in measure now to understand how these strategies work. Study them here.

The author created examples, but some of them are slightly broken or miss-labeled. I also added some top level elements to the stylesheets to control output. I placed my repaired stylesheets in XSLTfdCh06.zip under In Class for the day this chapter is assigned. You should transfer them to elab-linux4 and unzip them in a directory, for instance XSLTfdCh06 under webapps. To apply a stylesheet to an xml file, you should type oraxsl file.xml stylesheet.xsl where file.xml is the name of the xml file and stylesheet.xsl is the name of the stylesheet.

If you interactively do the examples as you go, you will gain a very good working knowledge of XSLT. Follows notes on specific elements:

Page 94: You should have copies of coffee.xml and coffee-light.xml in front of you. Now examing the template rule on this page. Which template rule is matched first? Why? How does that template rule control processing so that only prices are processed? How does the second template rule make it so that only price data is output? Here are my answers. The second template rule is matched first. In processing the source document, coffee.xml, the xslt parser (or processor) encounters coffees as the root element. It lools for a template rule that can match it. Well, there is a template rule that has "coffees" exactly as its match attribute. That is as specific as you can get, so it matches, and the rule is applied. The matched template rule has one xsl instruction in it xsl:apply-templates. This instruciton says to select all nodes on the XPath region/coffee/price and find templates that match them. Well, there is one template rule that can match these elements and it has a match attribute of "price". That rule says to apply-templates to all of the child nodes of price. Price's only child is its value. There are no specific templates that match this node, so the value is output based on the default template rule.
Why aren't all the other elements also processed by the default template rules? When the root element coffees was matched by a template, processing for the whole document passed to that template. That template said to only select elements matching the XPath expression contained in its select attribute. Therefore, nothing else was processed.
Read and re-read this explanation if you do not get it. If it is still murky, ask me.
Page 96: How does changing xsl:apply-templates to xsl:copy-of alter the processing of the first template compared with the first template in the stylesheet on Page 94? Note that xsl:copy-of is very similar to the Identity Transformation I showed you in XSQL1. The difference is that the xsl:copy-of works all at once while the Identity Transformation individually matches all nodes in the source tree and copies them. With the Identity Transformation, you can alter how individual nodes or groups of nodes are processed by using specific template rules while still copying everything that does not match those rules. That is not possible with xsl:copy-of.
Page 100: Behold the identity transformation and just the case I was talking about. The author wants to do something special with the coffee element and copy all the rest. Questions you should be able to answer: 1) Which template is applied first? 2) When is the template that matches coffee applied? 3) Which template matches when the "coffee" template calls xsl:apply-templates?
Page 107: The stylesheet on this page is called coffee-remove_attr.xsl.
Page 108: This stylesheet is called coffee-remove_attr2.xsl. How does the template that matches region avoid matching attributes? Hint, what subset of nodes is specified by the xsl:apply-templates' select attribute in this template? How can you tell that attributes are not part of the subset?
Page 109: The stylesheet I supplied adds a root element so that you can use it with coffee.xml.
Page 110: There is a typo in this template. The xsl:value-of that occurs in the bestwith literal result element should select "bestwith" and not "taste".
Page 113: Why doesn't this stylesheet copy the region element to the output? Hint: Pay careful attention to the match attributes on the template rules.
Page 116: Note the empty template rules. You should be able to explain how they suppress the copying of the source elements they match to the output. What would happen if these empty template rules were not here?

Bud last edited this November 28, 2005 | Permalink | Comments (0)

XSLT for Dummies — Chapter 7

This is just sort of catching up for a few conditional expressions.

This chapter illustrates the use of conditional and looping statements. The author makes the point that most of the time you can just write a new template rule instead of using one of these constructs. That is true, and the create-a-template strategy is preferrable in XSLT. You are taking advantage of its built-in processing algorithm to take care of conditions.

However, there are times when using a conditional statement is required. Recall customerHTMLnoRows.xsl from XSQL2. Our strategy there was to call a named template to create a table. That way, we gained a re-usable piece of code that was not tied to the specific way we named our elements in a particular document. However, we needed a way to figure out when to call the named template. That's where the conditional xsl:choose came in to play.

The author created examples, but some of them are slightly broken or miss-labeled. I also added some top level elements to the stylesheets to control output. I placed my repaired stylesheets in XSLTfdCh07.zip under In Class for the day this chapter is assigned. You should transfer them to elab-linux4 and unzip them in a directory, for instance XSLTfdCh07 under webapps. To apply a stylesheet to an xml file, you should type oraxsl file.xml stylesheet.xsl where file.xml is the name of the xml file and stylesheet.xsl is the name of the stylesheet.

If you interactively do the examples as you go, you will gain a very good working knowledge of XSLT. Follows notes on specific elements:

Pages 126-127: There are two things here. First, you can test if nodes exist exist by writing an XPath expression. If the expresesion results in a non-empty node set (i.e., there are nodes that match the XPath expression), then the condition is true. Second, you can perform comparisons between the value of a node and some standard. If the comparison is true, the condition is true (this is how we usually think of conditional statements).
Page 131: xsl:choose allows you to use multiple conditions and allows for an otherwise possibility. xsl:if only allows the testing of one condition.
Page 135: We used xsl:for-each in the template rule CreateSimpleTable in simpleTable.xsl. xsl:for-each allows you to process a number of nodes within a template and then perhaps process those nodes differently again within the same template. For instance, we created a header row for our table using the first row of database results. We then re-processed all of the rows of database results, including the first row, to create the data rows.

Bud last edited this November 28, 2005 | Permalink | Comments (0)

SQL, XML, & Ice Cream

A remixable web learning project at Michigan's Ross School of Business

BIT320 Texts

A Guide to SQL, Chapter 2

A Guide to SQL, Chapter 3

A Guide to SQL, Chapter 4

A Guide to SQL, Chapter 6

A Guide to SQL, Chapter 5

Notes on "Commit and Rollback"

XSLT for Dummies — Chapter 3

XSLT for Dummies — Chapter 4

XSLT for Dummies — Chapter 5

XSLT for Dummies — Chapter 6

XSLT for Dummies — Chapter 7

Search

Browse By Month

Browse By Topic

Subscribe

Contact