You will remember that last month our hero, me, was in Sweden teaching a SQL class. The other half of my "European tour" was a visit to the U.K. for a few days on my way back. My mission was to pick up "Britannica Bear" and "Princess Bear" Beanie Babies not available in the States for my wife. Beanie Babies are insanely overpriced stuffed animal toys that are a fad among collectors (just do a Web search on "Beanie Babies" and "TY" to see what I mean). If you do not know what they are, do not feel alone; most of the clerks in the London stores thought I was nuts, too.
I also went to Cambridge to visit Superbase, drink some single malt Scotch and advise them on SQL-92 features for the next release of their product. They are proud of the ability of Superbase to handle multiple file formats, generate tight client/server code and localize to whatever target language the market needs. The current updates will add improved SQL access and more Internet features.
Superbase is working on a great demonstration of the localization ability of the product, but for legal reasons I cannot yet tell you what it is. All I can say is that it will demonstrate font changes, grammar changes, and every problem you would find in moving an application to an exotic natural language. You can contact Superbase on its CompuServe forum or its Web site (www.superbase.com); maybe it will have an announcement by the time you read this column.
When I got back home, I got another tour of an interesting tool at United Systems Inc. (www.unitedsystemsinc.com). This company is a VAR, consulting, and public training partner of SilverStream Software Inc., a new company run by Dave Litwack, the former president and founder of Powersoft. (See the SilverStream product review in DBMS, March 1998.) A familiar name with prior success always gives you a good feeling about a new company.
SilverStream is a Java-based Web application development tool that has the usual graphic tools and so forth that you would expect in such a product. Developers can create and deploy Internet and intranet applications with sophisticated business logic, full JDBC/ODBC database access, Internet security, and agent-based push technology. The gimmick is that it does not have to have the database on the same server as the Java application.
There are several advantages to having a separate "Java server" ý instead of treating the Java applets as stored procedures within a database, you can move them around. Users can connect to almost any back-end database on another server. They can pass the Java code to any tier in the architecture from the server and run it on fat or thin clients or on Java-enabled middleware.
In the long run, SilverStream can act as a "business rules" repository and server. There is no standard language for business rules yet, so Java is as good as any other choice right now. I favor declarative languages over procedural languages for rules because of my SQL background, but someone could build a declarative compiler that produces Java.
It is no great surprise that people are working on using Java and SQL together. In fact, a research document (X3H2-97-235 = DBL:LGW-125) was distributed to both the ANSI and ISO database standards committees on this idea. As a result, a new subproject of ANSIýs SQL3 project has been requested for part 10, SQL/OLB (Object Language Bindings), and that document has been mentioned as one possible source of text for that new part 10.
This work is being done without a formal organization or consortium, but the effort to date has involved approximately 10 to 15 people from some five to eight companies. The work currently involves several aspects: embedding SQL into Java programs, invoking Java (stored) routines from SQL code, storing Java object instances in SQL columns, and so forth.
If you are interested in learning more about (or even joining) the group doing this work, please contact either Gray Clossman (gclossma@us.oracle.com) or Richard Pledereder (pleder@sybase.com).
I am starting to see the distinction between client/server applications and Web-based applications, but I do not have a formal statement of it yet, and I am sure that I am missing something. It is hard to unlearn an old technology when faced with a new one.
Thereýs an old folk tale about an old Cobol programmer who was given the job of putting the telephone book for Fort Benning, Georgia on video terminals. Before this project, huge piles of green-bar paper were printed and distributed every month from a batch file program. Terminals would save time and money and be faster and easier to use.
His first attempt was to change the output device from the line printer to a video terminal. The whole directory scrolled too fast to read, and it was 132 columns wide, so the 80-column terminals cut off the last 52 characters of each line.
He was told to slow it down so users could read it one page at a time. His second attempt displayed the message, "Would you like to read page one (yes/no)?" and waited for an answer before showing page one or asking about page two. By the fifth or sixth attempt, he had discovered scrolling and simple first-letter string searching. The gap between batch processing and interactive programs took a while to learn.
Likewise, there is a difference between programming for client/server and programming for the Web. Let me tell another story to illustrate my point. When I was at Software Development ý98 in San Francisco in February, I had a short bull session with one of the Sybase people. Sybase remembered how IBM failed to produce a timely Web site at the Atlanta Games. The company was determined to do better with a Web site that posted scores for a series of winter games in France.
Before an event begins, the Web page for that event is static and the traffic is light and almost predictable. You donýt even need a SQL query to build an HTML file for your ISP server. The worst thing that can happen is a change in the time and lineups.
When the event is over, the Web page for that event is static and the traffic is light and almost predicable. You run a SQL query once, turn it into an HTML file, and send it over to the ISP server. You might worry about correcting the page if a judge makes a ruling that changes things in some way.
The action comes while the event is progress. Suddenly, the Web page for that event is being pinged by a million people. In between postings of official scores, the Web page can be static and might be replicated on other servers for traffic considerations. The problem is at the cusp, when everyone hits the site at the same time and the developer has to refresh it with new data. Building a new Web page on the server and sending it down is a big performance hit.
The solution is to send not new pages but only the changes to the old pages. A local application has to apply the changes, one line at a time. SQL does not support this idea of a partial query.
Centura Software (www.centurasoft.com), the database company formerly known as Gupta, won a contract to supply secure, fully encrypted databases to a major German bank. Under the agreement, Centura will integrate electronic funds-transfer security features into SQLBase 7.5. The bank will use the application in locations outside the control of its standard secured environment.
Economist Julian Simon, one of my personal heroes, died on February 8, 1998 of a heart attack. You can get an idea of his optimistic economic views from his last book, The Ultimate Resource 2: People, Materials, and Environment (Princeton University Press, 1996). His position was the exact opposite of the professional doomsayers, and he was consistently right in all his predictions.
While it is an old story, it might be worth retelling. Julian Simon and Paul Ehrlich (author of The Population Bomb, Buccaneer Books, 1997, and a whole raft of other doomsday books) made a bet in 1980. They took an imaginary $1,000 and let Ehrlich pick commodities. The bet was that the real price would go up or down, depending on the state of the world, in the next 10 years. If the real costs went down, then Simon would collect the real difference adjusted to then current dollars; if the real costs went up, then Ehrlich would collect the difference adjusted to current dollars.
Ehrlich picked metals: copper, chrome, nickel, tin, and tungsten, and he "invested" $200 in each. In the fall of 1990, Ehrlich paid Simon $576.07 and did not call one of his press conferences about it. What was even funnier is that if Ehrlich had paid off in current dollars, not adjusted for inflation, he would still have lost!
More than 20 years ago, Simon devised the volunteer seat auction system used by U.S. airlines today to handle overbooked flights. Airlines have to fill as much of an airplane as they can to distribute the costs over the passengers and save them money. They also have to overbook flights if they want to be sure of filling those seats because of no-shows. In the days before Simonýs proposal was adopted, airlines arbitrarily bumped some booked passengers. This practice rendered flying unnecessarily inconvenient. Those people who absolutely had to be at their destinations by a certain time could not depend upon airlines to get them there.
He also wrote "Resampling Statistics," a statistics course with a small programming language. This language sets up and repeats experiments as part of the teaching technique. The reason that we used complex formulas with assumptions about population distribution in traditional statistics courses was that statistics was developed before computers could do repeated experiments for us. Resampling gets students so much further along in a shorter time than anything I have seen because students can understand what is happening.
This is a problem that came up in Sweden. We have a table that shows the time sheets filled out by employees. Dropping some of the details, the table looks like this:
CREATE TABLE Timesheets (employee INTEGER NOT NULL, task CHAR(3) NOT NULL, startdate DATE NOT NULL, enddate DATE NULL, PRIMARY KEY (employee, task, startdate));INSERT INTO Timesheets VALUES (1, ýaý, ý1997-01-01ý, ý1997-01-03ý); INSERT INTO Timesheets VALUES (1, ýbý, ý1997-01-02ý, ý1997-01-04ý); INSERT INTO Timesheets VALUES (1, ýcý, ý1997-01-04ý, ý1997-01-05ý); INSERT INTO Timesheets VALUES (1, ýdý, ý1997-01-06ý, ý1997-01-09ý); INSERT INTO Timesheets VALUES (1, ýeý, ý1997-01-09ý, ý1997-01-09ý); INSERT INTO Timesheets VALUES (1, ýfý, ý1997-01-09ý, ý1997-01-09ý); INSERT INTO Timesheets VALUES (1, ýgý, ý1997-01-12ý, ý1997-01-15ý); INSERT INTO Timesheets VALUES (1, ýhý, ý1997-01-13ý, ý1997-01-14ý); INSERT INTO Timesheets VALUES (1, ýiý, ý1997-01-14ý, ý1997-01-14ý); INSERT INTO Timesheets VALUES (1, ýjý, ý1997-01-17ý, ý1997-01-17ý);
The goal is to produce a listing of the consecutive days the employees were working because we pay by the day and not by the task. The results we want from this sample data are:
Results startdate enddate ================================= ý1997-01-02ý, ý1997-01-05ý ý1997-01-06ý, ý1997-01-09ý ý1997-01-12ý, ý1997-01-15ý ý1997-01-17ý, ý1997-01-17ý
| Puzzle Answer |
|---|
|
If you have a product that supports SQL-92, you can put this
CREATE VIEW V1 (startdate, enddate)
AS
SELECT T1.startdate, T2.enddate
FROM Timesheets AS T1, Timesheets AS T2, Timesheets AS T3
WHERE T1.enddate <= T2.enddate
GROUP BY T1.startdate, T2.enddate
HAVING COUNT(CASE
WHEN (T1.startdate > T3.startdate
AND T1.startdate <= T3.enddate)
OR
(T2.enddate >= T3.startdate
AND T2.enddate < T3.enddate)
THEN 1 END) = 0;
SELECT V1.startdate, MIN(V1.enddate) AS enddate
FROM V1
GROUP BY V1.startdate;
The T1 copy of the Timesheets is the starting date for the consecutive period and the T2 copy is the ending date. The outermost The next step is to eliminate pairs of start and end dates with gaps in them. The T3 copy of Timesheets is part of a characteristic function that finds the gaps that are inside the start and end dates. This is a set complement approach to the problem.
This answer is once more due to Richard Romley. What is surprising is that using a |