Overview

Created by Gustaf Neumann, last modified by Malte Sussdorff 07 Aug 2007, at 04:18 PM

OpenACS: robust web development framework

Created by Anett Szabo, last modified by Anett Szabo 30 Jul 2007, at 01:58 PM

OpenACS: robust web development framework

Author: Rocael Hernández, Galileo University, Guatemala / OpenACS Core Team, roc@viaro.net
Author: Andrew Grumet, OpenACS Core Team, aegrumet@alum.mit.edu

Tcl/Tk 2005 Conference, Portland, Oregon

Abstract:

OpenACS is a full featured web development framework to create scalable applications
oriented to collaboration and online communities. Is in use by many big players such as
greenpeace.org or the e-learning platform of the MIT Sloan School of Management.
While the system is not trivial, here are explained some of the most interesting and still
relatively simple facilities that the framework provides. Everything from templating,
separating the code from the presentation, database interactions according to the
programming language, auto-documentation features, automated test engine,
internationalization, and many more are written in Tcl, which has shown to be extremely
powerful for writing the foundation logic and the application pages.

Advanced infrastructure

Created by Anett Szabo, last modified by Anett Szabo 30 Jul 2007, at 01:57 PM

3.1. Serving files: packages, instances, site-map, request processor

Like other Web environments OpenACS can serve familiar file types such as .html and
.gif files from a document root. The OpenACS standard document root is
$OACS_HOME/www. Put a file at $OACS_HOME/www/hello.html and it will appear
at http://yourserver.example.com/hello.html.
OpenACS can also run scripts that set up variables and display them in HTML-like
templates, and also embed templates within other templates via include and master/slave
tags. These topics are covered in the Template system section above. In the sections
below we explore OpenACS more advanced mechanisms for serving files.

3.1.1. Packages

OpenACS is modularized into a set of packages that can be found in the
$OACS_HOME/packages subdirectory. Each package may contain SQL scripts, Tcl
libraries and visible pages, as illustrated in the abbreviated directory layout below:

This example draws attention to the forums package, one of dozens of application
packages available for use with OpenACS. Other available packages include a Webbased
files storage system (which also is WebDAV-enabled), calendaring, blog
authoring, assessment, news aggregation, wikis, photo galleries, RSS support, XMLRPC,
SOAP support and many more. A full list of packages can be browsed at
http://cvs.openacs.org/cvs/openacs-4/packages/.
Packages are managed with the OpenACS package manager, which handles upgrades and
tracks versions, dependencies and files much like Linux package managers do.
A view of the Package Manager:

3.1.2. Site map, package instances, request processor

Each package can have its own document root that functions like the default document
root at $OACS_HOME/www. The document root for the forums package is located at
$OACS_HOME/packages/forums/www, as illustrated in the abbreviated directory layout
above.
Package document roots are mapped to visible URLs through a set of database tables,
configuration data, libraries and administration Web pages known collectively as the site
map. Using the site map we can map $OACS_HOME/packages/forums/www to, for
example, http://yourserver.example.com/tclers-forums/. But it gets more interesting,
because the site map allows for re-use of packages at multiple URLs. Hence we can host
a discussion forum for C programmers by adding a new site map entry that maps the
forums package to http://yourserver.example.com/cprogrammers-forums/.
These mappings are referred to in OpenACS-speak as “package instances”. As the
terminology hints, the mapping to /tclers-forums has distinct configuration data from the
mapping to /cprogrammers-forums. Hence the /tclers-forums instance might contain a
Tcl Forum, a Tk Forum and an AOLserver Forum, while the /cprogrammers-forums
instance contains a Small and Fast Forum and a Some Compilation Required Forum.
Because package instances are OpenACS objects, they can be have different permission
settings, so that some users may be able to read and post to the /tclers-forums but not the
/cprogrammers-forums, and vice-versa. The OpenACS object system will be covered in
more detail below.
Before doing that, let’s take a brief diversion into the mechanics of how files are served,
Requests for OpenACS Web pages pass through a Request Processor, which is a global
filter and set of Tcl procs that respond to every incoming URL reaching the server. The
following diagram summarizes the stages of the request processor assuming a URL
request like http://yourserver.example.com/notes/somepage.adp.
The stages are:
1. Search the Site Map, to map the URL to the appropriate physical directory in the
filesystem.
2. Authenticate the user.
3. Authorize the possible specific defined permissions that the site node might have.
4. Process the URL, search for the appropriate file and server it.

 
3.2. Object system and services

Deep in OpenACS’ design is the notion that one should be able to build common services
that are useful across the toolkit. Hence a commenting engine ought work equally well
for blog posts as it does for images in photo galleries. Furthermore, any sufficiently
interesting piece of data in the system ought to carry basic accounting information with it,
such as who created it and timestamps for creation and modification.
The OpenACS object system addresses these requirements by defining a central SQL
table called acs_objects and giving this table a column for each generic bit of
information. Most importantly, acs_objects has a primary key column, named
object_id. This primary key is the anchor on which all other information about an
object rests. If the object is a blog post, the post body might live in a separate table
blog_posts whose primary key, post_id, is a reference back to
acs_objects.object_id. If the object is an image, it might contain a binary field
containing the image bits or alternatively a text field pointing to the physical storage
location of the image file, and also an image_id primary key that is a reference back to
acs_objects.object_id. Since each blog post and each image has a row in
acs_objects, comments on either can be inserted into a table that contains an
on_what_object column that points back to the object_id.
Any data that participates in the OpenACS object system can tell us its title, what kind of
object it is, when it was created and by whom. It can also be assigned permissions,
commented on, categorized, have files attached to it, and benefit from any other objectlevel
services that we can dream up.
The OpenACS object system, site map and instances are the foundation of OpenACS.
More information about these can be found at the following URLs:
http://openacs.org/doc/openacs-5-1/request-processor.html
http://openacs.org/doc/openacs-5-1/subsites.html
http://openacs.org/doc/openacs-5-1/packages.html

 3.3. Developer Support

OpenACS provides a set of developer support tools to improve the development process,
debugging, testing, and searching of the API. These tools enhance many of the day to day
activities of the developers.
The functionalities that developer support provides are:
1. Time to serve a given request. Good for performance problem detection and
improvement.
2. Tracing database calls involved in a given request, where the involved queries
are located, what was the actual query executed and how long did it take to return
the data. Useful for improving queries that might be slowing your application
performance.
3. Tracing scripts involved in serving a given request. Showing the time taken to
perform a given script, its location, code, and error that it might bring. Especially
important for tuning applications performance.
4. ADP reveal, to show in the browser which part of the rendered html belongs to a
given script.
5. User Switching, as an admin account switch easily to another account to
reproduce and test actions, and then simply go back to the administrator account.
6. OpenACS Shell, which is a Tcl shell with all the API available within OpenACS,
in order to simplify the testing of small pieces of Tcl within your browser.
An screenshot of the ADP Reveal:
The Tracing Script view, information shown at the bottom of each server page:
OpenACS also provides an implicit mechanism to document any script or procedure that
you might create, and then display and search that scripts or procedures, its documented
information, expected inputs and outputs, and its code though a Web interface at your
own OpenACS installation, like yourserver.com/api-doc, have a look here:
http://openacs.org/api-doc/
Finally, using the package manager, the Web server can be instructed to reload Tcl
library files without a restart of the webserver, and keep watching them for subsequent
changes, which is quite useful for the developers.

 
3.5. Testing Framework

OpenACS provides a full featured testing framework to create, maintain and run
automated test in your applications. The main characteristics of it are:
- Define tests, as smoke, config, database, web, etc. in a per package basis. And
with the API provided by the test framework you have a UI to check results, and
log events when executing a test case.
- Test your application at the proc level, using specific Test API to define and
check if your procs are behaving as expected.
- Test your end-user web scripts using a third party tool, such as tclwebtest or
perl::mechanize, to test the end-user pages automatically simulating end user
navigation (clicks) through the application and finally check the outpus using the
Test API.
- Define procedures that many tests might call, in order to automate similar
activities that need to be done before running a test case.
- Rollback the generated data after executing a test case.
- Rollback code section, to perform actions that will rollback the actions done by
running a test case, specially designed for those situations where you cannot
encapsulate the database calls to rollback, like when you are calling the scripts
with an automated tool.
- Run tests by installation, package or by choosing an specific test case.

 
3.6. Internationalization

OpenACS provide a facility to internationalize its user interface texts (not the data stored)
to any desired language, right now OpenACS is translated to more than 20 languages.
The end users can change the language user interface by simply selecting it. Each
OpenACS package can be internationalized separately, maintaining a message keys
catalog, which consist in a specific XML DTD where each possible user interface
message is stored as a message key, and then translated to a given language. Although all
the data is stored in the xml file, everything is also stored in the database, for
performance reasons and to facilitate the edition of the message keys OpenACS provides
a simple UI for translation of message key. And exists an official translation server for
the community at: translate.openacs.org.

3.7. Callbacks

The callbacks are a simple method to execute procedures stored in each of the possible
installed packages that a given OpenACS installation might have. The objective is to give
the core applications to invoke in non-core packages that may or may not be installed, but
without cluttering core with code that belongs to other packages, making possible to have
independence among packages.
The architecture work as

as opposed to this architecture

Callback implementations would be declared like this:

 Where ad_proc is a wrapper of the normal TCL proc, which mainly gives autodocumentation
structure for any procedure.
Core uses tcl introspection to find which callbacks exist and invokes them. eg
foreach proc [info procs ::callback::module::op::impl::*] {
$proc $args
}
To invoke a callback you do this
callback [ -catch ] [ -impl impl ] callback [ args... ]
The callbacks is a great improvement in order to keep simple yet separated, coherent and
modular a web framework that constantly evolves and grows. The larger goal is to
promote reuse of standard pages and functions by removing the need to create perpackage
versions of these.

Domain level tools

Created by Anett Szabo, last modified by Anett Szabo 30 Jul 2007, at 01:40 PM

4.1. Content Repository

The Content Repository (CR) is a central service application that can be used by other
applications to manage its content. The developer must define and declare the
interactions with the content repository. The main features that the content repository are:
- CR is capable to store any kind of information (files, data, text).
- Define application specific data structures that will use the CR features and
services within the application context.
- Revision control, which means that every addition and subsequent changes of an
application is registered, so the versions are kept and can be roll-backed.
- Ability to handle hierarchies, and folder structures, and inherit its properties.
- Easily interact with the CR and its specific defined structures through a welldefined
API, in order to add data or present it.
- Also handles publish states for content that might need it.
- Identify content through a content type definition.
- Associate the content with external applications.
- Store specific templates to use when displaying specific content types.
- Create relations between content repository items or external database objects.
- Embedded search facility to automatically expose the content to the search engine
the system might be using.
The process for an application to use the content repository goes as:
1. Define the database structure your application needs that will use some of the CR
features.
2. Use the standardized API (to add, edit, delete, present) to create scripts for your
CR-enabled-application.
The CR logic is stored in database functions and triggers, but it has a Tcl API that glue all
the involved database calls and makes straightforward for the developers to create
applications that use the CR.

4.2. Results: Vertical Applications

Within OpenACS a vertical application means a set packages that interact together to
provide a specific domain set of functionalities.
The following are good examples of vertical applications completely running in
OpenACS and using Tcl as the programming language:

4.2.1. Project Manager

Full featured Project manager, consist in a set of functionalities that can be summarized
as: track tasks, estimates and actual progress for a project. It consist in more that 5
packages. Features that are part f it: project creation, customers management, task
assignments, logs, comments, create processes, rates, etc.

4.2.2. Assessment Tool

Extremely powerful application to create several types of assessments such as online selftests,
surveys, tests and gathering of information in general. The main features of this
application are: IMS-QTI support for import and export of assessments, branching,
sections, several types of questions and many more are part of it. More information at:
http://cvs.openacs.org/cvs/*checkout*/openacs-
4/packages/assessment/www/doc/index.html?rev=1.6

4.2.3. Communities of Practice

Communities of Practice within OpenACS are represented by a set of tools available
through all the website to interact with any kind of applications. The main objective is to
give the power to the end user describe in many forms any object at the system and make
relations among them. Those tools are:
- Rate
- Comment
- Categorize
- Link
- Search
And those tools can be used in all the applications that the end user might have available,
such as forums, file storage, etc. For example, you can categorize a given file, and then
link it to a forum thread, which you are also rating and commenting. Then a third user
will search for given topic, and the first result will be the file, and when he looks at the
file, he’ll see as well all its related objects, such as the forum thread.

4.2.4. .LRN and the E-LANE project

.LRN (pronounced dot-learn, www.dotlrn.org) is a full featured LMS with extra
community building capabilities, uses packages available for OpenACS and integrate
other vertical applications described here. Its focus is to help the creation of learning and
research communities, and not just server as an LMS.
The E-LANE Project (European-Latin America New Education, www.e-lane.org) is
using .LRN as its foundation technology to promote and demonstrate e-learning within
this geographical area, the demonstration is based on three fundamental factors: the
learning methodology, the content development, and the technological platform, the three
of them brought together to use it in real scenarios for further improvement of .LRN.
As a contribution to the .LRN community, E-LANE has developed among other
applications the user-tracking package, which is mainly focused to “Analyze users
behavior and syndicate it by object type”.
This package is basically using Tcl to create the UI and the administrative actions, and
glue them with awstats (http://awstats.sourceforge.net/), which in this case is the log
analyzer used to parse the AOLserver request logs, and those logs contains a set of
special keys written on each http request logged, which is the base to generate specific
request reports for a given user and/or object type.

Introduction

Created by Anett Szabo, last modified by Anett Szabo 30 Jul 2007, at 01:08 PM

 
The Open Architecture Community System (OpenACS) is a Web development
framework for building applications that support online communities.
OpenACS provides a robust infrastructure, building on top of the following standard
components: the Tcl programming language, a Postgres or Oracle database for storing the
application data, AOLserver for HTTP service and *nix or Windows operating systems.
Like other modern Web frameworks, OpenACS supports: templating for separating the
logic from the presentation, internationalization to present the user interface in the user’s
preferred language, a modular package system to create sub-applications, a role and
permissioning system, a content repository to store all manner of content and maintain
versioning.

SQL Tutorial

Created by Anett Szabo, last modified by Anett Szabo 26 Jul 2007, at 01:48 PM

What is SQL?

SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database. This tutorial will provide you with the instruction on the basics of each of these commands as well as allow you to put them to practice using the SQL Interpreter.

Table Basics

A relational database system contains one or more objects called tables. The data or information for the database are stored in these tables. Tables are uniquely identified by their names and are comprised of columns and rows. Columns contain the column name, data type, and any other attributes for the column. Rows contain the records or data for the columns. Here is a sample table called "weather".

city, state, high, and low are the columns. The rows contain the data for this table:

Selecting Data

The select statement is used to query the database and retrieve selected data that match the criteria that you specify. Here is the format of a simple select statement:

select "column1"
  [,"column2",etc] 
  from "tablename"
  [where "condition"];
  [] = optional

The column names that follow the select keyword determine which columns will be returned in the results. You can select as many column names that you'd like, or you can use a "*" to select all columns.

The table name that follows the keyword from specifies the table that will be queried to retrieve the desired results.

The where clause (optional) specifies which data values or rows will be returned or displayed, based on the criteria described after the keyword where.

Conditional selections used in the where clause:

The LIKE pattern matching operator can also be used in the conditional selection of the where clause. Like is a very powerful operator that allows you to select only rows that are "like" what you specify. The percent sign "%" can be used as a wild card to match any possible character that might appear before or after the characters specified. For example:

select first, last, city
   from empinfo
   where first LIKE 'Er%';

This SQL statement will match any first names that start with 'Er'. Strings must be in single quotes.

Or you can specify,

select first, last
   from empinfo
   where last LIKE '%s';

This statement will match any last names that end in a 's'.

select * from empinfo
   where first = 'Eric';

This will only select rows where the first name equals 'Eric' exactly.

What will be the result of the following sample SELECT statements?

select first, last, city from empinfo; 

select last, city, age from empinfo 
where age > 30;

select first, last, city, state from empinfo
       where first LIKE 'J%'; 

select * from empinfo;

select first, last, from empinfo
       where last LIKE '%s';  

 select first, last, age from empinfo
       where last LIKE '%illia%'; 

select * from empinfo where first = 'Eric'; 

Exercise 1.

Enter select statements to:

1. Display the first name and age for everyone that's in the table.
2. Display the first name, last name, and city for everyone that's not from Payson.
3. Display all columns for everyone that is over 40 years old.
4. Display the first and last names for everyone whose last name ends in an "ay".
5. Display all columns for everyone whose first name equals "Mary".
6. Display all columns for everyone whose first name contains "Mary".

Answers

Exercise 2.

items_ordered

Enter select statements to:

1. From the items_ordered table, select a list of all items purchased for customerid 10449. Display the customerid, item, and price for this customer.
2. Select all columns from the items_ordered table for whoever purchased a Tent.
3. Select the customerid, order_date, and item values from the items_ordered table for any items in the item column that start with the letter "S".
4. Select the distinct items in the items_ordered table. In other words, display a listing of each of the unique items from the items_ordered table.
5. Make up your own select statements and submit them.

Answers

Aggregate Functions

Aggregate functions are used to compute against a "returned column of numeric data" from your SELECT statement. They basically summarize the results of a particular column of selected data. We are covering these here since they are required by the next topic, "GROUP BY". Although they are required for the "GROUP BY" clause, these functions can be used without the "GROUP BY" clause. For example:

SELECT AVG(salary)
FROM employee;

This statement will return a single result which contains the average value of everything returned in the salary column from the employee table.

Another example:

SELECT AVG(salary)
    FROM employee;
       WHERE title = 'Programmer';

 This statement will return the average salary for all employees whose title is equal to 'Programmer'

 Example:

Example:

SELECT Count(*)
FROM employees;

This particular statement is slightly different from the other aggregate functions since there isn't a column supplied to the count function. This statement will return the number of rows in the employees table.

Exercise 3.

1. Select the maximum price of any item ordered in the items_ordered table ( see above ). Hint: Select the maximum price only.>
2. Select the average price of all of the items ordered that were purchased in the month of Dec.
3. What are the total number of rows in the items_ordered table?
4. For all of the tents that were ordered in the items_ordered table, what is the price of the lowest tent? Hint: Your query should return the price only.

Answers

GROUP BY clause

The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performed on the one or more columns. This can best be explained by an example:

GROUP BY clause syntax:

SELECT column1, 
SUM(column2)
FROM "list-of-tables"
GROUP BY "column-list";

Let's say you would like to retrieve a list of the highest paid salaries in each dept:

SELECT max(salary), dept
FROM employee 
GROUP BY dept;

This statement will select the maximum salary for the people in each unique department. Basically, the salary for the person who makes the most in each department will be displayed. Their, salary and their department will be returned.

What if I wanted to display their lastname too?

For example, take a look at the items_ordered table. Let's say you want to group everything of quantity 1 together, everything of quantity 2 together, everything of quantity 3 together, etc. If you would like to determine what the largest cost item is for each grouped quantity (all quantity 1's, all quantity 2's, all quantity 3's, etc.), you would enter:

SELECT quantity, max(price)
FROM items_ordered
GROUP BY quantity;

Enter the statement in above, and take a look at the results to see if it returned what you were expecting. Verify that the maximum price in each Quantity Group is really the maximum price.

Exercise 4.

1. How many people are in each unique state in the customers table? Select the state and display the number of people in each. Hint: count is used to count rows in a column, sum works on numeric data only.
2. From the items_ordered table, select the item, maximum price, and minimum price for each specific item in the table. Hint: The items will need to be broken up into separate groups.
3. How many orders did each customer make? Use the items_ordered table. Select the customerid, number of orders they made, and the sum of their orders. Click the Group By answers link below if you have any problems.

Answers

HAVING clause

The HAVING clause allows you to specify conditions on the rows for each group - in other words, which rows should be selected will be based on the conditions you specify. The HAVING clause should follow the GROUP BY clause if you are going to use it.

HAVING clause syntax:

SELECT column1, 
SUM(column2)
FROM "list-of-tables"
GROUP BY "column-list"
HAVING "condition";

HAVING can best be described by example. Let's say you have an employee table containing the employee's name, department, salary, and age. If you would like to select the average salary for each employee in each department, you could enter:

SELECT dept, avg(salary)
FROM employee
GROUP BY dept;

But, let's say that you want to ONLY calculate & display the average if their salary is over 20000:

SELECT dept, avg(salary)
FROM employee
GROUP BY dept
HAVING avg(salary) > 20000;

Exercise 5.

1. How many people are in each unique state in the customers table that have more than one person in the state? Select the state and display the number of how many people are in each if it's greater than 1.
2. From the items_ordered table, select the item, maximum price, and minimum price for each specific item in the table. Only display the results if the maximum price for one of the items is greater than 190.00.
3. How many orders did each customer make? Use the items_ordered table. Select the customerid, number of orders they made, and the sum of their orders if they purchased more than 1 item.

Answers

ORDER BY clause

ORDER BY is an optional clause which will allow you to display the results of your query in a sorted order (either ascending order or descending order) based on the columns that you specify to order by. 

 ORDER BY clause syntax:

SELECT column1, SUM(column2)
FROM "list-of-tables"
ORDER BY "column-list" [ASC | DESC];

[ ] = optional

This statement will select the employee_id, dept, name, age, and salary from the employee_info table where the dept equals 'Sales' and will list the results in Ascending (default) order based on their Salary.

ASC = Ascending Order - default
DESC = Descending Order 

For example:

SELECT employee_id, dept, name, age, salary
FROM employee_info
WHERE dept = 'Sales'ORDER BY salary;

If you would like to order based on multiple columns, you must seperate the columns with commas. For example:

SELECT employee_id, dept, name, age, salary
FROM employee_info
WHERE dept = 'Sales'
ORDER BY salary, age DESC;

Exercise 6.

1. Select the lastname, firstname, and city for all customers in the customers table. Display the results in Ascending Order based on the lastname.
2. Same thing as exercise #1, but display the results in Descending order.
3. Select the item and price for all of the items in the items_ordered table that the price is greater than 10.00. Display the results in Ascending order based on the price.

Answers

Combining conditions and Boolean Operators

The AND operator can be used to join two or more conditions in the WHERE clause. Both sides of the AND condition must be true in order for the condition to be met and for those rows to be displayed.

SELECT column1, 
SUM(column2)
FROM "list-of-tables"
WHERE "condition1" AND 
"condition2";

The OR operator can be used to join two or more conditions in the WHERE clause also. However, either side of the OR operator can be true and the condition will be met - hence, the rows will be displayed. With the OR operator, either side can be true or both sides can be true.

For example:

SELECT employeeid, firstname, lastname, title, salary
FROM employee_info
WHERE salary >= 50000.00 AND title = 'Programmer';

This statement will select the employeeid, firstname, lastname, title, and salary from the employee_info table where the salary is greater than or equal to 50000.00 AND the title is equal to 'Programmer'. Both of these conditions must be true in order for the rows to be returned in the query. If either is false, then it will not be displayed.

Although they are not required, you can use paranthesis around your conditional expressions to make it easier to read:

SELECT employeeid, firstname, lastname, title, salary
FROM employee_info
WHERE (salary >= 50000.00) AND (title = 'Programmer');

Another Example:

SELECT firstname, lastname, title, salary
FROM employee_info
WHERE (title = 'Sales') OR (title = 'Programmer');

This statement will select the firstname, lastname, title, and salary from the employee_info table where the title is either equal to 'Sales' OR the title is equal to 'Programmer'.

Exercise 7.

1. Select the customerid, order_date, and item from the items_ordered table for all items unless they are 'Snow Shoes' or if they are 'Ear Muffs'. Display the rows as long as they are not either of these two items.
2. Select the item and price of all items that start with the letters 'S', 'P', or 'F'.

Answers

IN and BETWEEN Conditional Operators

SELECT col1, SUM(col2)
FROM "list-of-tables"
WHERE col3 IN 
       (list-of-values);
SELECT col1, SUM(col2)
FROM "list-of-tables"
WHERE col3 BETWEEN value1 
AND value2;

SELECT employeeid, lastname, salary
FROM employee_info
WHERE lastname IN ('Hernandez', 'Jones', 'Roberts', 'Ruiz');

SELECT employeeid, lastname, salary
FROM employee_info
WHERE lastname = 'Hernandez' OR lastname = 'Jones' 
OR lastname = 'Roberts'OR lastname = 'Ruiz';

SELECT employeeid, age, lastname, salary
FROM employee_info
WHERE age BETWEEN 30 AND 40;

SELECT employeeid, age, lastname, salary
FROM employee_info
WHERE age >= 30 AND age <= 40;

SELECT round(salary), firstname
FROM employee_info

Table Joins, a must

SELECT "list-of-columns"
FROM table1,table2
WHERE "search-condition(s)"

Joins can be explained easier by demonstrating what would happen if you worked with one table only, and didn't have the ability to use "joins". This single table database is also sometimes referred to as a "flat table". Let's say you have a one-table database that is used to keep track of all of your customers and what they purchase from your store:

Everytime a new row is inserted into the table, all columns will be be updated, thus resulting in unnecessary "redundant data". For example, every time Wolfgang Schultz purchases something, the following rows will be inserted into the table:

An ideal database would have two tables:

1. One for keeping track of your customers
2. And the other to keep track of what they purchase:

"Customer_info" table:

"Purchases" table:

Now, whenever a purchase is made from a repeating customer, the 2nd table, "Purchases" only needs to be updated! We've just eliminated useless redundant data, that is, we've just normalized this database!

Notice how each of the tables have a common "cusomer_number" column. This column, which contains the unique customer number will be used to JOIN the two tables. Using the two new tables, let's say you would like to select the customer's name, and items they've purchased. Here is an example of a join statement to accomplish this:

SELECT customer_info.firstname, customer_info.lastname, purchases.item
FROM customer_info, purchases
WHERE customer_info.customer_number = purchases.customer_number;

This particular "Join" is known as an "Inner Join" or "Equijoin". This is the most common type of "Join" that you will see or use.

Notice that each of the colums are always preceeded with the table name and a period. This isn't always required, however, it IS good practice so that you wont confuse which colums go with what tables. It is required if the name column names are the same between the two tables. I recommend preceeding all of your columns with the table names when using joins.

Note: The syntax described above will work with most Database Systems -including the one with this tutorial. However, in the event that this doesn't work with yours, please check your specific database documentation.

Although the above will probably work, here is the ANSI SQL-92 syntax specification for an Inner Join using the preceding statement above that you might want to try:

SELECT customer_info.firstname, customer_info.lastname, purchases.item
FROM customer_info INNER JOIN purchases
ON customer_info.customer_number = purchases.customer_number;

SELECT customer_info.firstname, customer_info.lastname, purchases.item
FROM customer_info INNER JOIN purchases
ON customer_info.customer_number = purchases.customer_number;

Another example:

SELECT employee_info.employeeid, employee_info.lastname, employee_sales.comission
FROM employee_info, employee_sales
WHERE employee_info.employeeid = employee_sales.employeeid; 

This statement will select the employeeid, lastname (from the employee_info table), and the comission value (from the employee_sales table) for all of the rows where the employeeid in the employee_info table matches the employeeid in the employee_sales table.

Exercises 10. 

1. Write a query using a join to determine which items were ordered by each of the customers in the customers table. Select the customerid, firstname, lastname, order_date, item, and price for everything each customer purchased in the items_ordered table.
2. Repeat exercise #1, however display the results sorted by state in descending order.

Creating Tables

The create table statement is used to create a new table. Here is the format of a simple create table statement:

create table "tablename"
("column1" "data type",
 "column2" "data type",
 "column3" "data type");

Format of create table if you were to use optional constraints:

create table "tablename"
("column1" "data type" 
         [constraint],
 "column2" "data type" 
         [constraint],
 "column3" "data type" 
        [constraint]);
 [ ] = optional

Note: You may have as many columns as you'd like, and the constraints are optional.

Example:

create table employee
(first varchar(15),
 last varchar(20),
 age number(3),
 address varchar(30),
 city varchar(20),
 state varchar(20));

To create a new table, enter the keywords create table followed by the table name, followed by an open parenthesis, followed by the first column name, followed by the data type for that column, followed by any optional constraints, and followed by a closing parenthesis. It is important to make sure you use an open parenthesis before the beginning table, and a closing parenthesis after the end of the last column definition. Make sure you seperate each column definition with a comma. All SQL statements should end with a ";".

The table and column names must start with a letter and can be followed by letters, numbers, or underscores - not to exceed a total of 30 characters in length. Do not use any SQL reserved keywords as names for tables or column names (such as "select", "create", "insert", etc).

Data types specify what the type of data can be for that particular column. If a column called "Last_Name", is to be used to hold names, then that particular column should have a "varchar" (variable-length character) data type.

Here are the most common Data types:

What are constraints? When tables are created, it is common for one or more columns to have constraints associated with them. A constraint is basically a rule associated with a column that the data entered into that column must follow. For example, a "unique" constraint specifies that no two records can have the same value in a particular column. They must all be unique. The other two most popular constraints are "not null" which specifies that a column can't be left blank, and "primary key". A "primary key" constraint defines a unique identification of each record (or row) in a table. All of these and more will be covered in the future Advanced release of this Tutorial. Constraints can be entered in this SQL interpreter, however, they are not supported in this Intro to SQL tutorial & interpreter. They will be covered and supported in the future release of the Advanced SQL tutorial - that is, if "response" is good.

It's now time for you to design and create your own table. You will use this table throughout the rest of the tutorial. If you decide to change or redesign the table, you can either drop it and recreate it or you can create a completely different one. The SQL statement drop will be covered later.

Exercise 11. 

You have just started a new company. It is time to hire some employees. You will need to create a table that will contain the following information about your new employees: firstname, lastname, title, age, and salary. 

Answer

Inserting into a Table

The insert statement is used to insert or add a row of data into the table.

To insert records into a table, enter the key words insert into followed by the table name, followed by an open parenthesis, followed by a list of column names separated by commas, followed by a closing parenthesis, followed by the keyword values, followed by the list of values enclosed in parenthesis. The values that you enter will be held in the rows and they will match up with the column names that you specify. Strings should be enclosed in single quotes, and numbers should not.

insert into "tablename"
 (first_column,...last_column)
  values (first_value,...last_value);

In the example below, the column name first will match up with the value 'Luke', and the column name state will match up with the value 'Georgia'.

Example:

insert into employee
  (first, last, age, address, city, state)
  values ('Luke', 'Duke', 45, '2130 Boars Nest', 
          'Hazard Co', 'Georgia');

Note: All strings should be enclosed between single quotes: 'string'

Exercise 12. 

It is time to insert data into your new employee table.

Your first three employees are the following:

Jonie Weber, Secretary, 28, 19500.00
Potsy Weber, Programmer, 32, 45300.00
Dirk Smith, Programmer II, 45, 75020.00

Enter these employees into your table first, and then insert at least 5 more of your own list of employees in the table.

After they're inserted into the table, enter select statements to:

1. Select all columns for everyone in your employee table.
2. Select all columns for everyone with a salary over 30000.
3. Select first and last names for everyone that's under 30 years old.
4. Select first name, last name, and salary for anyone with "Programmer" in their title.
5. Select all columns for everyone whose last name contains "ebe".
6. Select the first name for everyone whose first name equals "Potsy".
7. Select all columns for everyone over 80 years old.
8. Select all columns for everyone whose last name ends in "ith".

Answers

Updating Records

The update statement is used to update or change records that match a specified criteria. This is accomplished by carefully constructing a where clause.

update "tablename"
set "columnname" = 
    "newvalue"
 [,"nextcolumn" = 
   "newvalue2"...]
where "columnname" 
  OPERATOR "value" 
 [and|or "column" 
  OPERATOR "value"];

 [] = optional

 [The above example was line wrapped for better viewing on this Web page.]

Examples:

update phone_book
  set area_code = 623
  where prefix = 979;

update phone_book
  set last_name = 'Smith', prefix=555, suffix=9292
  where last_name = 'Jones';

update employee
  set age = age+1
  where first_name='Mary' and last_name='Williams';

Exercise 13.

After each update, issue a select statement to verify your changes.

1. Jonie Weber just got married to Bob Williams. She has requested that her last name be updated to Weber-Williams.
2. Dirk Smith's birthday is today, add 1 to his age.
3. All secretaries are now called "Administrative Assistant". Update all titles accordingly.
4. Everyone that's making under 30000 are to receive a 3500 a year raise.
5. Everyone that's making over 33500 are to receive a 4500 a year raise.
6. All "Programmer II" titles are now promoted to "Programmer III".
7. All "Programmer" titles are now promoted to "Programmer II".

Answers

Deleting Records

The delete statement is used to delete records or rows from the table.

delete from "tablename"

where "columnname" 
  OPERATOR "value" 
[and|or "column" 
  OPERATOR "value"];

[ ] = optional

[The above example was line wrapped for better viewing on this Web page.]

Examples:

delete from employee;

 Note: if you leave off the where clause, all records will be deleted!

delete from employee
  where lastname = 'May';

delete from employee
  where firstname = 'Mike' or firstname = 'Eric';

To delete an entire record/row from a table, enter "delete from" followed by the table name, followed by the where clause which contains the conditions to delete. If you leave off the where clause, all records will be deleted.

Exercise 14.

(Use the select statement to verify your deletes):

1. Jonie Weber-Williams just quit, remove her record from the table.
2. It's time for budget cuts. Remove all employees who are making over 70000 dollars.

Answers

Drop a Table

The drop table command is used to delete a table and all rows in the table.

To delete an entire table including all of its rows, issue the drop table command followed by the tablename. drop table is different from deleting all of the records in the table. Deleting all of the records in the table leaves the table including column and constraint information. Dropping the table removes the table definition as well as all of its rows.

drop table "tablename"

Example:

drop table myemployees_ts0211;

Exercise 15.

Drop your employee table.

---

based on http://sqlcourse2.com/ 

Simple queries

Created by Anett Szabo, last modified by Anett Szabo 26 Jul 2007, at 12:11 PM

If you start up SQL*Plus, you can start browsing around immediately with the SELECT statement. You don't even need to define a table; Oracle provides the built-in dual table for times when you're interested in a constant or a function:

SQL> select 'Hello World' from dual;

'HELLOWORLD
-----------
Hello World

SQL> select 2+2 from dual;

       2+2
----------
	 4

SQL> select sysdate from dual;

SYSDATE
----------
1999-02-14

SQL> select 4+NULL from dual;

    4+NULL
----------

There is nothing magic about the dual table for these purposes; you can compute functions using the bboard table instead of dual:

select sysdate,2+2,atan2(0, -1) from bboard;

SYSDATE 	  2+2 ATAN2(0,-1)
---------- ---------- -----------
1999-01-14	    4  3.14159265
1999-01-14	    4  3.14159265
1999-01-14	    4  3.14159265
1999-01-14	    4  3.14159265
...

1999-01-14	    4  3.14159265
1999-01-14	    4  3.14159265
1999-01-14	    4  3.14159265

55010 rows selected.

Getting beyond Hello World

select * from users;

SQL> select count(*) from users;

  COUNT(*)
----------
      7352

SQL> describe users
 Name				 Null?	  Type
 ------------------------------- -------- ----
 USER_ID			 NOT NULL NUMBER(38)
 FIRST_NAMES			 NOT NULL VARCHAR2(100)
 LAST_NAME			 NOT NULL VARCHAR2(100)
 PRIV_NAME				  NUMBER(38)
 EMAIL				 NOT NULL VARCHAR2(100)
 PRIV_EMAIL				  NUMBER(38)
 EMAIL_BOUNCING_P			  CHAR(1)
 PASSWORD			 NOT NULL VARCHAR2(30)
 URL					  VARCHAR2(200)
 ON_VACATION_UNTIL			  DATE
 LAST_VISIT				  DATE
 SECOND_TO_LAST_VISIT			  DATE
 REGISTRATION_DATE			  DATE
 REGISTRATION_IP			  VARCHAR2(50)
 ADMINISTRATOR_P			  CHAR(1)
 DELETED_P				  CHAR(1)
 BANNED_P				  CHAR(1)
 BANNING_USER				  NUMBER(38)
 BANNING_NOTE				  VARCHAR2(4000)

column fancy_type format a20
select column_name, data_type || '(' || data_length || ')' as fancy_type
from user_tab_columns
where table_name = 'USERS'
order by column_id;

Simple Queries from One Table

• the select list (which columns in our report)
• the name of the table
• the where clauses (which rows we want to see)
• the order by clauses (how we want the rows arranged)

SQL> select email
from users
where email like '%mit.edu';

EMAIL
------------------------------
philg@mit.edu
andy@california.mit.edu
ben@mit.edu
...
wollman@lcs.mit.edu
ghomsy@mit.edu
hal@mit.edu
...
jpearce@mit.edu
richmond@alum.mit.edu
andy_roo@mit.edu
kov@mit.edu
fletch@mit.edu
lsandon@mit.edu
psz@mit.edu
philg@ai.mit.edu
philg@martigny.ai.mit.edu
andy@californnia.mit.edu
ty@mit.edu
teadams@mit.edu

68 rows selected.

SQL> select email 
from users
where email like '___@mit.edu';

EMAIL
------------------------------
kov@mit.edu
hal@mit.edu
...
ben@mit.edu
psz@mit.edu

SQL> select email
from users
where email like '%mit.edu'
order by email;

EMAIL
------------------------------
andy@california.mit.edu
andy@californnia.mit.edu
andy_roo@mit.edu
...
ben@mit.edu
...
hal@mit.edu
...
philg@ai.mit.edu
philg@martigny.ai.mit.edu
philg@mit.edu

Restricting results

SQL> select count(*) 
from users
where registration_date >= '1998-09-01' 
and registration_date < '1998-10-01';

  COUNT(*)
----------
       920

SQL> select count(*) 
from users
where registration_date >= '1998-09-01' 
and registration_date < '1998-10-01'
and email like '%mit.edu';

  COUNT(*)
----------
        35

select count(*) 
from classified_ads
where expires >= sysdate
or expires is null;

Subqueries

select user_id, email 
from users 
where 0 < (select count(*) 
           from classified_ads
           where classified_ads.user_id = users.user_id);

   USER_ID EMAIL
---------- -----------------------------------
     42485 twm@meteor.com
     42489 trunghau@ecst.csuchico.edu
     42389 ricardo.carvajal@kbs.msu.edu
     42393 gon2foto@gte.net
     42399 rob@hawaii.rr.com
     42453 stefan9@ix.netcom.com
     42346 silverman@pon.net
     42153 gallen@wesleyan.edu
...

Another way to describe the same result set is using EXISTS:

select user_id, email 
from users 
where exists (select 1
              from classified_ads
              where classified_ads.user_id = users.user_id);

1. takes a SQL query as its only parameter
2. returns TRUE if the query returns any rows at all, regardless of the contents of those rows (this is why we can use the constant 1 as the select list for the subquery)

JOIN

select users.user_id, users.email, classified_ads.posted
from users, classified_ads
where users.user_id = classified_ads.user_id
order by users.email, posted;

   USER_ID EMAIL			       POSTED
---------- ----------------------------------- ----------
     39406 102140.1200@compuserve.com	       1998-09-30
     39406 102140.1200@compuserve.com	       1998-10-08
     39406 102140.1200@compuserve.com	       1998-10-08
     39842 102144.2651@compuserve.com	       1998-07-02
     39842 102144.2651@compuserve.com	       1998-07-06
     39842 102144.2651@compuserve.com	       1998-12-13
...
     41284 yme@inetport.com		       1998-01-25
     41284 yme@inetport.com		       1998-02-18
     41284 yme@inetport.com		       1998-03-08
     35389 zhupanov@usa.net		       1998-12-10
     35389 zhupanov@usa.net		       1998-12-10
     35389 zhupanov@usa.net		       1998-12-10

The order by users.email, posted is key to making sure that the rows are lumped together by user and then printed in order of ascending posting time.

OUTER JOIN

select users.user_id, users.email, classified_ads.posted
from users, classified_ads
where users.user_id = classified_ads.user_id(+)
order by users.email, posted;

...
   USER_ID EMAIL			       POSTED
---------- ----------------------------------- ----------
     52790 dbrager@mindspring.com
     37461 dbraun@scdt.intel.com
     52791 dbrenner@flash.net
     47177 dbronz@free.polbox.pl
     37296 dbrouse@enter.net
     47178 dbrown@cyberhighway.net
     36985 dbrown@uniden.com		       1998-03-05
     36985 dbrown@uniden.com		       1998-03-10
     34283 dbs117@amaze.net
     52792 dbsikorski@yahoo.com
...

Extending a simple query into a JOIN

1. add the new table to your FROM clause
2. add a WHERE constraint to prevent Oracle from building a Cartesian product
3. hunt for ambiguous column names in the SELECT list and other portions of the query; prefix these with table names if necessary
4. test that you've not broken anything in your zeal to add additional info
5. add a new column to the SELECT list

select room_id, start_time, end_time
from reservations
where user_id = 37

Step 1: add the new table to the FROM clause

select room_id, start_time, end_time
from reservations, rooms
where user_id = 37

Step 2: add a constraint to the WHERE clause

select room_id, start_time, end_time
from reservations, rooms
where user_id = 37
and reservations.room_id = rooms.room_id

Step 3: look for ambiguously defined columns

select reservations.room_id, start_time, end_time
from reservations, rooms
where user_id = 37
and reservations.room_id = rooms.room_id

Step 4: test

Step 5: add a new column to the SELECT list

select reservations.room_id, start_time, end_time, rooms.room_name
from reservations, rooms
where user_id = 37
and reservations.room_id = rooms.room_id

Reference

• Oracle8 Server SQL Reference, SELECT command section

---

based on  SQL for Web Nerds

Transactions

Created by Anett Szabo, last modified by Anett Szabo 25 Jul 2007, at 04:32 PM

In the introduction we covered some examples of inserting data into a database by typing at SQL*Plus:

insert into mailing_list (name, email)
values ('Philip Greenspun','philg@mit.edu');

The simplest and most direct interface to a relational database involves a procedural program in C, Java, Lisp, Perl, or Tcl putting together a string of SQL that is then sent to to the RDBMS. Here's how the ArsDigita Community System constructs a new entry in the clickthrough log:

insert into clickthrough_log 
 (local_url, foreign_url, entry_date, click_count)
values
 ('$local_url', '$foreign_url', trunc(sysdate), 1)"

Atomicity

delete from clickthrough_log where entry_date + 120 < sysdate;

More interestingly, you can wrap a transaction around multiple SQL statements. For example, when a user is editing a comment, the ArsDigita Community System keeps a record of what was there before:

ns_db dml $db "begin transaction" 

# insert into the audit table
ns_db dml $db "insert into general_comments_audit
 (comment_id, user_id, ip_address, audit_entry_time, modified_date, content)
select comment_id, 
       user_id, 
       '[ns_conn peeraddr]', 
       sysdate, 
       modified_date, 
       content from general_comments 
where comment_id = $comment_id"

# change the publicly viewable table
ns_db dml $db "update general_comments
set content = '$QQcontent', 
    html_p = '$html_p'
where comment_id = $comment_id"

# commit the transaction
ns_db dml $db "end transaction"

Let's look at these sections piece by piece. We're looking at a Tcl program calling AOLserver API procedures when it wants to talk to Oracle. We've configured the system to reverse the normal Oracle world order in which everything is within a transaction unless otherwise committed. The begin transaction and end transaction statements never get through to Oracle; they are merely instructions to our Oracle driver to flip Oracle out and then back into autocommit mode.

The transaction wrapper is imposed around two SQL statements. The first statement inserts a row into general_comments_audit. We could simply query the general_comments table from Tcl and then use the returned data to create a standard-looking INSERT. However, if what you're actually doing is moving data from one place within the RDBMS to another, it is extremely bad taste to drag it all the way out to an application program and then stuff it back in. Much better to use the "INSERT ... SELECT" form.

Note that two of the columns we're querying from general_comments don't exist in the table: sysdate and '[ns_conn peeraddr]'. It is legal in SQL to put function calls or constants in your select list, just as you saw at the beginning of the Queries chapter where we discussed Oracle's one-row system table: dual. To refresh your memory:

select sysdate from dual;

SYSDATE
----------
1999-01-14

select sysdate, 2+2, atan2(0, -1) from dual;

SYSDATE 	  2+2 ATAN2(0,-1)
---------- ---------- -----------
1999-01-14	    4  3.14159265

select posting_time, 2+2 
from bboard 
where msg_id = '000KWj';

POSTING_TI	  2+2
---------- ----------
1998-12-13	    4

• open a transaction
• insert into an audit table whatever comes back from a SELECT statement on the comment table
• update the comment table
• close the transaction

Consistency

select user_id from bboard where msg_id = '000KWj';

   USER_ID
----------
     39685

delete from users where user_id = 39685;
*
ERROR at line 1:
ORA-02292: integrity constraint (PHOTONET.SYS_C001526) violated - child record
found

create table bboard (
	msg_id		char(6) not null primary key,
	refers_to	char(6),
        ...
	user_id		integer not null references users,
	one_line	varchar(700),
	message		clob,
	...
);

Mutual Exclusion

• read a value from the database
• perform a computation based on that value
• update the value in the database based on the computation

The /bboard module of the ArsDigita Community System has to do this. The sequence is

• read the last message ID from the msg_id_generator table
• increment the message ID with a bizarre collection of Tcl scripts
• update the last_msg_id column in the msg_id_generator table

select last_msg_id
from msg_id_generator
for update of last_msg_id

Mutual Exclusion (the Big Hammer)

select count(*) from an_alert_log 
where member_id = $member_id
and entry_type = 'sent_response'
and alert_id = $alert_id

lock table an_alert_log in exclusive mode

• open a transaction
• lock table
• select count(*)
• if the count was 0, insert a row to record the fact that the user has responded
• commit the transaction (releases the table lock)
• proceed with the rest of the script
• ...

What if I just want some unique numbers?

create table news (
	title		varchar(100) not null,
	body		varchar(4000) not null,
	release_date	date not null,
        ...
);

insert into news (title, body, release_date)
values
('French Air Traffic Controllers Strike',
 'A walkout today by controllers left travelers stranded..',
 '1995-12-14');

insert into news (title, body, release_date)
values
('French Air Traffic Controllers Strike',
 'Passengers at Orly faced 400 canceled flights ...',
 '1997-05-01');

insert into news (title, body, release_date)
values
('Bill Clinton Beats the Rap',
 'Only 55 senators were convinced that President Clinton obstructed justice ...',
 '1999-02-12');

insert into news (title, body, release_date)
values
('Bill Clinton Beats the Rap',
 'The sexual harassment suit by Paula Jones was dismissed ...',
 '1998-12-02);

	primary key (title, release_date)

The traditional database design that gets around all of these problems is the use of a generated key. If you've been annoyed by having to carry around your student ID at MIT or your patient ID at a hospital, now you understand the reason why: the programmers are using generated keys and making their lives a bit easier by exposing this part of their software's innards.

Here's how the news module of the ArsDigita Community System works, in an excerpt from http://software.arsdigita.com/www/doc/sql/news.sql:

create sequence news_id_sequence start with 1;

create table news (
	news_id		integer primary key,
	title		varchar(100) not null,
	body		varchar(4000) not null,
	release_date	date not null,
	...
);

SQL> create sequence foo_sequence;

Sequence created.

SQL> select foo_sequence.currval from dual;

ERROR at line 1:
ORA-08002: sequence FOO_SEQUENCE.CURRVAL is not yet defined in this session

SQL> select foo_sequence.nextval from dual;

   NEXTVAL
----------
	 1

SQL> select foo_sequence.nextval from dual;

   NEXTVAL
----------
	 2

SQL> select foo_sequence.nextval from dual;

   NEXTVAL
----------
	 3

SQL> select foo_sequence.currval from dual;

   CURRVAL
----------
	 3

insert into news (news_id, title, body, release_date)
values
(news_id_sequence.nextval, 
 'Tuition Refund at MIT',
 'Administrators were shocked and horrified ...',
 '1998-03-12);

In the ArsDigita Community System implementation, the news_id is actually generated in /news/post-new-2.tcl:

set news_id [database_to_tcl_string $db "select news_id_sequence.nextval from dual"]

if [catch { ns_db dml $db "insert into news
(news_id, title, body, html_p, approved_p, 
 release_date, expiration_date, creation_date, creation_user, 
 creation_ip_address)
values
($news_id, '$QQtitle', '$QQbody', '$html_p', '$approved_p',
 '$release_date', '$expiration_date', sysdate, $user_id, 
 '$creation_ip_address')" } errmsg] {
    # insert failed; let's see if it was because of duplicate submission
    if { [database_to_tcl_string $db "select count(*) 
                                      from news
                                      where news_id = $news_id"] == 0 } {
        # some error other than dupe submission
	ad_return_error "Insert Failed" "The database ..."
        return
    }
    # we don't bother to handle the cases where there is a dupe submission
    # because the user should be thanked or redirected anyway
}

Sequence Caveats

If you ask for a few nextvals and roll back your transaction, the sequence will not be rolled back.

You can't rely on sequence values to be, uh, sequential. They will be unique. They will be monotonically increasing. But there might be gaps. The gaps arise because Oracle pulls, by default, 20 sequence values into memory and records those values as used on disk. This makes nextval very fast since the new value need only be marked use in RAM and not on disk. But suppose that someone pulls the plug on your database server after only two sequence values have been handed out. If your database administrator and system administrator are working well together, the computer will come back to life running Oracle. But there will be a gap of 18 values in the sequence (e.g., from 2023 to 2041). That's because Oracle recorded 20 values used on disk and only handed out 2.

More

• Oracle8 Server Application Developer's Guide, Controlling Transactions
• Orace8 Server SQL Reference, CREATE SEQUENCE section

---

based on  SQL for Web Nerds

Views

Created by Anett Szabo, last modified by Anett Szabo 25 Jul 2007, at 04:31 PM

The relational database provides programmers with a high degree of abstraction from the physical world of the computer. You can't tell where on the disk the RDBMS is putting each row of a table. For all you know, information in a single row might be split up and spread out across multiple disk drives. The RDBMS lets you add a column to a billion-row table. Is the new information for each row going to be placed next to the pre-existing columns or will a big new block of disk space be allocated to hold the new column value for all billion rows? You can't know and shouldn't really care.

A view is a way of building even greater abstraction.

Suppose that Jane in marketing says that she wants to see a table containing the following information:

• user_id
• email address
• number of static pages viewed
• number of bboard postings made
• number of comments made

select u.user_id, 
       u.email, 
       count(ucm.page_id) as n_pages,
       count(bb.msg_id) as n_msgs,
       count(c.comment_id) as n_comments
from users u, user_content_map ucm, bboard bb, comments c
where u.user_id = ucm.user_id(+)
and u.user_id = bb.user_id(+)
and u.user_id = c.user_id(+)
group by u.user_id, u.email
order by upper(email)

create or replace view janes_marketing_view 
as 
select u.user_id, 
       u.email, 
       count(ucm.page_id) as n_pages,
       count(bb.msg_id) as n_msgs,
       count(c.comment_id) as n_comments
from users u, user_content_map ucm, bboard bb, comments c
where u.user_id = ucm.user_id(+)
and u.user_id = bb.user_id(+)
and u.user_id = c.user_id(+)
group by u.user_id, u.email
order by upper(u.email)

select * from janes_marketing_view;

Protecting Privacy with Views

create table patients (
       patient_id     integer primary key,
       patient_name   varchar(100),
       hiv_positive_p char(1),
       insurance_p    char(1),
       ...
);

create view patients_clinical
as 
select patient_id, patient_name, hiv_positive_p from patients;

create view patients_accounting
as 
select patient_id, patient_name, insurance_p from patients;

Protecting Your Own Source Code

create or replace view sh_orders_reportable 
as
select * from sh_orders
where order_state not in ('confirmed','failed_authorization','void');

If we add some order states or otherwise change the data model, the reporting programs need not be touched; we only have to keep this view definition up to date. Note that you can define every view with "create or replace view" rather than "create view"; this saves a bit of typing when you have to edit the definition later.

If you've used select * to define a view and subsequently alter any of the underlying tables, you have to redefine the view. Otherwise, your view won't contain any of the new columns. You might consider this a bug but Oracle has documented it, thus turning the behavior into a feature.

Views-on-the-fly and OUTER JOIN

select users.user_id, users.email, classified_ads.posted
from users, classified_ads
where users.user_id = classified_ads.user_id(+)
order by users.email, posted;

...
   USER_ID EMAIL			       POSTED
---------- ----------------------------------- ----------
     52790 dbrager@mindspring.com
     37461 dbraun@scdt.intel.com
     52791 dbrenner@flash.net
     47177 dbronz@free.polbox.pl
     37296 dbrouse@enter.net
     47178 dbrown@cyberhighway.net
     36985 dbrown@uniden.com		       1998-03-05
     36985 dbrown@uniden.com		       1998-03-10
     34283 dbs117@amaze.net
     52792 dbsikorski@yahoo.com
...

The plus sign after classified_ads.user_id is our instruction to Oracle to "add NULL rows if you can't meet this JOIN constraint".

Suppose that this report has gotten very long and we're only interested in users whose email addresses start with "db". We can add a WHERE clause constraint on the email column of the users table:

select users.user_id, users.email, classified_ads.posted
from users, classified_ads
where users.user_id = classified_ads.user_id(+)
and users.email like 'db%'
order by users.email, posted;

   USER_ID EMAIL			  POSTED
---------- ------------------------------ ----------
     71668 db-designs@emeraldnet.net
    112295 db1@sisna.com
    137640 db25@umail.umd.edu
     35102 db44@aol.com 		  1999-12-23
     59279 db4rs@aol.com
     95190 db@astro.com.au
     17474 db@hotmail.com
    248220 db@indianhospitality.com
     40134 db@spindelvision.com 	  1999-02-04
    144420 db_chang@yahoo.com
     15020 dbaaru@mindspring.com
...

select users.user_id, users.email, classified_ads.posted
from users, classified_ads
where users.user_id = classified_ads.user_id(+)
and users.email like 'db%'
and classified_ads.posted > '1999-01-01'
order by users.email, posted;

   USER_ID EMAIL			  POSTED
---------- ------------------------------ ----------
     35102 db44@aol.com 		  1999-12-23
     40134 db@spindelvision.com 	  1999-02-04
     16979 dbdors@ev1.net		  2000-10-03
     16979 dbdors@ev1.net		  2000-10-26
    235920 dbendo@mindspring.com	  2000-08-03
    258161 dbouchar@bell.mma.edu	  2000-10-26
     39921 dbp@agora.rdrop.com		  1999-06-03
     39921 dbp@agora.rdrop.com		  1999-11-05

8 rows selected.

select users.user_id, users.email, ad_view.posted
from 
  users, 
  (select * 
   from classified_ads
   where posted > '1999-01-01') ad_view
where users.user_id = ad_view.user_id(+)
and users.email like 'db%'
order by users.email, ad_view.posted;

   USER_ID EMAIL			  POSTED
---------- ------------------------------ ----------
     71668 db-designs@emeraldnet.net
    112295 db1@sisna.com
    137640 db25@umail.umd.edu
     35102 db44@aol.com 		  1999-12-23
     59279 db4rs@aol.com
     95190 db@astro.com.au
     17474 db@hotmail.com
    248220 db@indianhospitality.com
     40134 db@spindelvision.com 	  1999-02-04
    144420 db_chang@yahoo.com
     15020 dbaaru@mindspring.com
...
174 rows selected.

How Views Work

Materialized views consume space because Oracle is keeping a copy of the data or at least a copy of information derivable from the data. More importantly, a materialized view does not contain up-to-the-minute information. When you query a regular view, your results includes changes made up to the last committed transaction before your SELECT. When you query a materialized view, you're getting results as of the time that the view was created or refreshed. Note that Oracle lets you specify a refresh interval at which the materialized view will automatically be refreshed.

At this point, you'd expect an experienced Oracle user to say "Hey, these aren't new. This is the old CREATE SNAPSHOT facility that we used to keep semi-up-to-date copies of tables on machines across the network!" What is new with materialized views is that you can create them with the ENABLE QUERY REWRITE option. This authorizes the SQL parser to look at a query involving aggregates or JOINs and go to the materialized view instead. Consider the following query, from the ArsDigita Community System's /admin/users/registration-history.tcl page:

select 
  to_char(registration_date,'YYYYMM') as sort_key,
  rtrim(to_char(registration_date,'Month')) as pretty_month, 
  to_char(registration_date,'YYYY') as pretty_year, 
  count(*) as n_new
from users
group by 
  to_char(registration_date,'YYYYMM'), 
  to_char(registration_date,'Month'), 
  to_char(registration_date,'YYYY')
order by 1;


SORT_K PRETTY_MO PRET	   N_NEW
------ --------- ---- ----------
199805 May	 1998	     898
199806 June	 1998	     806
199807 July	 1998	     972
199808 August	 1998	     849
199809 September 1998	    1023
199810 October	 1998	    1089
199811 November  1998	    1005
199812 December  1998	    1059
199901 January	 1999	    1488
199902 February  1999	    2148

create materialized view users_by_month
   enable query rewrite
   refresh complete
   start with 1999-03-28
   next sysdate + 1 
   as 
   select 
     to_char(registration_date,'YYYYMM') as sort_key,
     rtrim(to_char(registration_date,'Month')) as pretty_month, 
     to_char(registration_date,'YYYY') as pretty_year, 
     count(*) as n_new
   from users
   group by 
     to_char(registration_date,'YYYYMM'), 
     to_char(registration_date,'Month'), 
     to_char(registration_date,'YYYY')
   order by 1

select count(*) 
from users 
where rtrim(to_char(registration_date,'Month')) = 'January'
and to_char(registration_date,'YYYY') = '1999'

select count(*) 
from users 
where rtrim(to_char(registration_date,'Month')) = 'March'
and to_char(registration_date,'YYYY') = '1999'

More:

Reference

• High level: Oracle8 Server Concepts, View section. Oracle Application Developer's Guide, Managing Views section.
• Low level: Oracle8 Server SQL Reference, Create View syntax, and Create Materialized View section

---

based on  SQL for Web Nerds

Style

Created by Anett Szabo, last modified by Anett Szabo 25 Jul 2007, at 04:31 PM

Here's a familiar simple example from the complex queries chapter:

select user_id, 
  count(*) as how_many from bboard
where not exists (select 1 from bboard_authorized_maintainers 
bam where bam.user_id = 
bboard.user_id) and 
posting_time + 60 > sysdate group by user_id order
by how_many desc;

select user_id, count(*) as how_many
from bboard
where not exists (select 1 from 
                  bboard_authorized_maintainers bam
                  where bam.user_id = bboard.user_id)
and posting_time + 60 > sysdate
group by user_id
order by how_many desc;

Software is indented properly when the structure of the software is revealed and when the indentation style is familiar to a community of programmers.

Rules for All Queries

select email from users where user_id = 34;

select * 
from news
where sysdate > expiration_date
and approved_p = 't'
order by release_date desc, creation_date desc

select 
  to_char(count(*),'999G999G999G999G999') as n_pages,
  to_char(sum(dbms_lob.getlength(page_body)),'999G999G999G999G999') as n_bytes
from static_pages;

Here's another example, this time from the top-level comment administation page for the ArsDigita Community System. We're going to get back a single row with a count of each type of user-submitted comment:

select 
  count(*) as n_total, 
  sum(decode(comment_type,'alternative_perspective',1,0)) as n_alternative_perspectives, 
  sum(decode(comment_type,'rating',1,0)) as n_ratings,
  sum(decode(comment_type,'unanswered_question',1,0)) as n_unanswered_questions,
  sum(decode(comment_type,'private_message_to_page_authors',1,0)) as n_private_messages
from comments

• The Oracle docs on DECODE
• for an explanation of the number formatting wizardry, see Oracle8 Server SQL Reference section on format conversion

Rules for GROUP BY queries

select links.user_id, first_names, last_name, count(links.page_id) as n_links
from links, users
where links.user_id = users.user_id
group by links.user_id, first_names, last_name
order by n_links desc

---

based on  SQL for Web Nerds