Source code

001package bradleyross.j2ee.servlets;
002import bradleyross.library.database.DatabaseProperties;
003import bradleyross.library.helpers.GenericPrinter;
004import bradleyross.library.helpers.StringHelpers;
005import java.io.IOException;
006import java.io.PrintWriter;
007import java.io.StringWriter;
008import java.sql.SQLException;
009import java.sql.ResultSet;
010import java.sql.ResultSetMetaData;
011import java.sql.DatabaseMetaData;
012import java.sql.Types;
013import java.sql.Driver;
014import java.util.Vector;
015import javax.servlet.ServletConfig;
016import javax.servlet.ServletException;
017import javax.servlet.http.HttpServlet;
018import javax.servlet.http.HttpServletRequest;
019import javax.servlet.http.HttpServletResponse;
020import org.slf4j.Logger;
021import org.slf4j.LoggerFactory;
022import bradleyross.library.helpers.ExceptionHelper;
023/**
024 * Servlet for showing database schema information using
025 *  {@link DatabaseMetaData}.
026 * 
027 * <p>This was an earlier attempt to do an autodocumenter for
028 *    database schemas.  There was a later version that also listed indices
029 *    and foreign keys and used better formatting, and I'm trying to locate it.</p>
030 * 
031 * <p>It should be noted that SQL Server does not have a BOOLEAN
032 *    type but uses an integer value instead for result sets
033 *    containing boolean data.  A value of 0 indicates false
034 *    while a value of 1 indicates true.<p>
035 * 
036 * <p>The servlet uses the init parameters class, system, and account.</p>
037 * <ul>
038 * <li>class is the name of the class that is a subclass of 
039 *     {@link DatabaseProperties} that serves to connect the databases.</li>
040 * <li>system is the identifier for the database to be used.</li>
041 * <li>account is the name of the account to be used.</li>
042 * </ul>
043 * 
044 * @author Bradley Ross
045 * @see DatabaseMetaData#getCrossReference(String, String, String, String, String, String)
046 * @see DatabaseMetaData#getIndexInfo(String, String, String, boolean, boolean)
047 * @see DatabaseMetaData#getTables(String, String, String, String[])
048 * @see DatabaseMetaData#getImportedKeys(String, String, String)
049 * @see DatabaseMetaData#getExportedKeys(String, String, String)
050 */
051@SuppressWarnings("serial")
052public class ShowSchema extends HttpServlet 
053{
054        Logger log = LoggerFactory.getLogger(this.getClass());
055        ExceptionHelper logger = new ExceptionHelper(log);
056        /**
057         * True if servlet was correctly instantiated.
058         */
059        protected boolean isValid = true;
060        /**
061         * Name of class for handling database.
062         */
063        protected String databaseClassName = null;
064        /**
065         * Class for handling database.
066         */
067        protected Class<?> databaseClass = null;
068        /**
069         * Name of system for database connection.
070         */
071        protected String databaseSystem = null;
072        /**
073         * Description of database.
074         */
075        protected String databaseDescription = null;
076        /**
077         * Name of account to be used for database connection.
078         */
079        protected String databaseAccount = null;
080        /**
081         * List of warning or error messages generated by this application.
082         */
083        protected Vector<String> errorMessages = new Vector<String>();
084        /**
085         * List of informative messages generated by this application.
086         */
087        protected Vector<String> infoMessages = new Vector<String>();
088        /**
089         * Generates an error message for the application.
090         * @param title Text to appear in title
091         * @param message Text to appear in body of message
092         * @return HTML page
093         */
094        public String buildErrorMessage(String title, String message)
095        {
096                StringWriter writer = new StringWriter();
097                GenericPrinter output = new GenericPrinter(writer);
098                output.println("<html><head>");
099                output.println("<title>" + StringHelpers.escapeHTML(title) + "</title>");
100                output.println("</head><body");
101                output.println("<h1>" + StringHelpers.escapeHTML(title) + "</h1>");
102                output.println("<p>" + StringHelpers.escapeHTML(message) + "</p>");
103                if (errorMessages.size() > 0)
104                {
105                        output.println("<h2>Possible Problems</h2>");
106                        output.println("<ul>");
107                        for (int i = 0; i < errorMessages.size(); i++)
108                        {
109                                output.println("<li>" + StringHelpers.escapeHTML(errorMessages.elementAt(i)) 
110                                                + "</li>");
111                        }
112                        output.println("</ul");
113                }
114                if (infoMessages.size() > 0)
115                {
116                        output.println("<h2>Messages</h2>");
117                        output.println("<ul>");
118                        for (int i = 0; i < infoMessages.size(); i++)
119                        {
120                                output.println("<li>" + StringHelpers.escapeHTML(infoMessages.elementAt(i)) 
121                                                + "</li>");
122                        }
123                        output.println("</ul>");
124                }
125                output.println("</body></html>");
126                String result = writer.toString();
127                output.close();
128                return result;
129        }
130
131        /**
132         * Determines whether an item in the database is to be included in the
133         * reports.
134         * 
135         * <p>When dealing with SQLServer, dbo.dtproperties was showing up on the list
136         *    of tables even though it was a system table.  The other system tables
137         *    weren't appearing.</p>
138         * 
139         * @param data Object describing data connection
140         * @param catalog Name of catalog for item in database
141         * @param schema Name of schema for item in database
142         * @param item Name of item in database
143         * @return True if item is to be included in report
144         */
145        protected boolean isIncluded(DatabaseProperties data, String catalog, String schema, String item)
146        {
147                String database = data.getDbms();
148                if (database == null)
149                {
150                        return true;
151                }
152                else if (database.equalsIgnoreCase("sqlserver"))
153                {
154                        try
155                        {
156                                String schemaTest = null;
157                                if (schema == null)
158                                {
159                                        schemaTest = new String();
160                                }
161                                else
162                                {
163                                        schemaTest = schema;
164                                }
165                                if (schemaTest.equalsIgnoreCase("dbo") && item.equalsIgnoreCase("dtproperties"))
166                                {
167                                        return false;
168                                }
169                                else if (schemaTest.equalsIgnoreCase("sys"))
170                                {
171                                        return false;
172                                }
173                                else if (schemaTest.equalsIgnoreCase("INFORMATION_SCHEMA"))
174                                {
175                                        return false;
176                                }
177                                else
178                                {
179                                        return true;
180                                }
181                        }
182                        catch (Exception e)
183                        {
184                                return true;
185                        }
186                }
187                else
188                {
189                        return true;
190                }
191        }
192        /**
193         * Default constructor.
194         * 
195         * <p>The default constructor is used by the Tomcat application
196         *    server when setting up the servlet.  The default 
197         *    constructor must be explicitly defined since there
198         *    is an explicit constructor for the class.</p>
199         */
200        public ShowSchema()
201        { ; }
202        /**
203         * Constructor to allow the methods to be used by a stand-alone
204         * application.
205         * @param className Name of class for defining database connections.
206         * @param system Database system for which connection is to be made.
207         * @param account Account name to be used in connecting to the
208         *        database.
209         */
210        public ShowSchema(String className, String system, String account)
211        {
212                isValid = true;
213                databaseClassName = className;
214                databaseSystem = system;
215                databaseAccount = account;
216                try
217                {
218                        databaseClass = Class.forName(className);
219                }
220                catch (ClassNotFoundException e)
221                {
222                        errorMessages.add("Unable to find class " + className);
223                        isValid = false;
224                        return;
225                }
226                if (!DatabaseProperties.class.isAssignableFrom(databaseClass))
227                {
228                        errorMessages.add("Class " + className + " is not subclass of DatabaseProperties");
229                        isValid = false;
230                        return;
231                }
232                infoMessages.add("Initialization with ShowSchema complete: " +
233                                className + ", " + system + ", " + account);
234        }
235        /**
236         * Obtain a connection to the database based on the initialization
237         * parameters for the servlet.
238         * 
239         * @see #databaseAccount
240         * @see #databaseSystem
241         * @see #databaseClassName
242         * @return Database connection information
243         */
244        protected DatabaseProperties getData()
245        {
246                DatabaseProperties data = null;
247                if (!isValid)
248                {
249                        return null;
250                }
251                try 
252                {
253                        data = (DatabaseProperties) databaseClass.newInstance();
254                } 
255                catch (InstantiationException e) 
256                {
257                        errorMessages.add("Error while instantiating DatabaseProperties object");
258                        errorMessages.add(e.getClass().getName() + " " + e.getMessage());
259                        isValid = false;
260                        return null;
261                } 
262                catch (IllegalAccessException e) 
263                {
264                        errorMessages.add("Error while instantiating DatabaseProperties object");
265                        errorMessages.add(e.getClass().getName() + " " + e.getMessage());
266                        isValid = false;
267                        return null;
268                }
269                data.setDatabaseInstance(databaseSystem, databaseAccount);
270                try 
271                {
272                        data.connect();
273                } 
274                catch (SQLException e) 
275                {
276                        errorMessages.add("Error while opening database connection");
277                        errorMessages.add(data.showAttributes());
278                        errorMessages.add(e.getClass().getName() + " " + e.getMessage());
279                        isValid = false;
280                }
281                if (!isValid)
282                {
283                        return null;
284                }
285                infoMessages.add("Database connection has been opened");
286                return data;
287        }
288        /**
289         * Initializes object for use with servlets.
290         * <p>The following servlet configuration items are used.</p>
291         * <ul>
292         * <li>class - Subclass of DatabaseProperties that is used to connect with the
293         *             database.</li>
294         * <li>system</li>
295         * <li>account</li>
296         * </ul>
297         * @param configIn Servlet configuration object
298         * 
299         */
300        public void init(ServletConfig configIn) throws ServletException
301        { 
302                isValid = true;
303                try
304                {
305                        databaseClassName = configIn.getInitParameter("class");
306                        databaseSystem = configIn.getInitParameter("system");
307                        databaseAccount = configIn.getInitParameter("account");
308                        databaseClass =  Class.forName(databaseClassName);
309                }
310                catch (ClassNotFoundException e)
311                {
312                        isValid = false;
313                        return;
314                }
315                if (!DatabaseProperties.class.isAssignableFrom(databaseClass))
316                {
317                        errorMessages.add("Class " + databaseClassName + " is not subclass of DatabaseProperties");
318                        isValid = false;
319                        return;
320                }
321        }
322        /** 
323         * Place error message on web page if exception is encountered.
324         * 
325         * @param e Exception
326         * @param output Output device for servlet writer
327         */
328        protected void ProblemFound (Exception e, GenericPrinter output, String message)
329        {
330                output.println("<html><head>");
331                output.println("<title>Error encountered in processing database</title>");
332                output.println("</head><body>");
333                output.println("<h1>Problem encountered in connecting to database</h1>");
334                output.println("<p>" + StringHelpers.escapeHTML(message) + "</p>");
335                output.println("<p>System name is " + databaseSystem + "</p>");
336                output.println("<p>Account name is " + databaseAccount + "</p>");
337                output.println("<p>" + StringHelpers.escapeHTML(e.getClass().getName()) + " " + 
338                                StringHelpers.escapeHTML(e.getMessage()) + "</p>");
339                StringWriter sw = new StringWriter();
340                PrintWriter pw = new PrintWriter(sw);
341                e.printStackTrace(pw);
342                output.println("<p>" + StringHelpers.escapeHTML(sw.toString()) + "</p>");
343                output.println("</body></html>");
344        }       
345        /**
346         * This is the method that is executed each time a request is made for the
347         * web page.
348         * 
349         * @param req Request information
350         * @param res Response information
351         * @throws IOException
352         */
353        public void service (HttpServletRequest req,
354                        HttpServletResponse res) throws IOException
355                        {
356                // String rootName = req.getContextPath() + req.getServletPath();
357                DatabaseProperties data = null;
358                PrintWriter writer = null;
359                GenericPrinter generic = null;
360                String pathInfo = null;
361                res.setContentType("text/html");
362                writer = res.getWriter();
363                generic = new GenericPrinter(writer);
364                pathInfo = req.getPathInfo();
365
366                try
367                {
368                        data = (DatabaseProperties) databaseClass.newInstance();
369                        data.setDatabaseInstance(databaseSystem, databaseAccount);      
370                        data.connect();
371                        databaseDescription = data.getSystemDescription();
372                        if (databaseDescription == null)
373                        {
374                                databaseDescription = new String();
375                        }
376                }
377                catch (InstantiationException e)
378                {
379                        logger.error("Problem setting up database connection: ", e);
380                        ProblemFound(e, generic, "Problem setting up database connection: " +
381                                        "InstantiationException");
382                        return;
383                }
384                catch (IllegalAccessException e)
385                {
386                        ProblemFound(e, generic, "Problem setting up database connection: " +
387                                        "IllegalAccessException");
388                        return;
389                }
390                catch (SQLException e)
391                {
392                        ProblemFound(e, generic, "Problem setting up database connection: " +
393                                        "SQLException");
394                        return;
395                }
396                /*
397                 * Now that the page is open, carry out the appropriate action for
398                 * writing the web page.
399                 */
400                if (pathInfo == null || pathInfo.length() == 0 || pathInfo.equals("/") ||
401                                pathInfo.equalsIgnoreCase("/main.do"))
402                {
403                        // generic.print(buildRootPage(data, rootName, debugLevel));
404                        processRootPage(data, generic, req, res);
405                }
406                else if (pathInfo.equalsIgnoreCase("/listtables") ||
407                                pathInfo.equalsIgnoreCase("/listtables.do"))
408                {
409                        processListTables(data, generic, req, res);
410                }
411                else if (pathInfo.equalsIgnoreCase("/listviews") ||
412                                pathInfo.equalsIgnoreCase("/listviews.do"))
413                {
414                        processListViews(data, generic, req, res);
415                }
416                else if (pathInfo.equalsIgnoreCase("/listprocedures") ||
417                                pathInfo.equalsIgnoreCase("/listprocedures.do"))
418                {
419                        processListProcedures(data, generic, req, res);
420                }
421                else if (pathInfo.equalsIgnoreCase("/databaseinformation")||
422                                pathInfo.equalsIgnoreCase("/databaseinformation.do"))
423                {
424                        processDatabaseInformation(data, generic, req, res);
425                }
426                else if (pathInfo.equalsIgnoreCase("/listudts") ||
427                                pathInfo.equalsIgnoreCase("/listudts.do"))
428                {
429                        processUDTInformation(data, generic, req, res);
430                }
431                else if (pathInfo.equalsIgnoreCase("/describetable") ||
432                                pathInfo.equalsIgnoreCase("/describetable.do"))
433                {
434                        processDescribeTable(data, generic, req, res);
435                }
436                else
437                {
438                        //                      generic.print(buildRootPage(data, rootName, debugLevel));
439                        processRootPage(data,generic, req, res);                        
440                }                               
441                /*
442                 * Close the database now that the page is complete.
443                 */
444                try
445                {
446                        data.close();
447                }
448                catch (SQLException e)
449                {
450                        writer.println("<p>Problem closing database connection</p>");
451                        writer.println("<p>" + 
452                                        StringHelpers.escapeHTML(e.getClass().getName() + " " + e.getMessage()) +
453                                        "</p>");
454                }
455                writer.println("</body></html>");
456                        }
457        /**
458         * Generate the root page for schema information for the database.
459         * @param data Object describing database connection
460         * @param output Object for writing web page
461         * @param req HTTP request information
462         * @param res HTTP response information
463         * @throws IOException
464         */
465        protected void processRootPage(DatabaseProperties data, GenericPrinter output,
466                        HttpServletRequest req, HttpServletResponse res) throws IOException
467                        {
468                String rootName = req.getContextPath() + req.getServletPath();
469                output.println("<html><head>");
470                output.println("<title>Database Information</title>");
471                output.println("</head><body>");
472                output.println("<p>The root URL is " + rootName + "</p>");
473                output.println("<h1>Database Schema</h1>");
474                output.println("<ul>");
475                output.println("<li><a href=\"" + rootName + "/DatabaseInformation\">Database Information</a></li>");
476                output.println("<li><a href=\"" + rootName + "/ListTables\">List Tables</a></li>");
477                output.println("<li><a href=\"" + rootName + "/ListViews\">List Views</a></li>");
478                output.println("<li><a href=\"" + rootName + "/ListProcedures\">List Procedures</a></li>");
479                output.println("<li><a href=\"" + rootName + "/ListUDTs\">List User Defined Types (UDT)</a></li>");
480                output.println("</ul>");
481                output.println("<p>DBMS is " + data.getDbms());
482                output.println("<p>System is " + data.getSystem() + "</p>");
483                output.println("<p>" + StringHelpers.escapeHTML(data.showAttributes()) + "</p>");
484                output.println("The URL's for this process start with " +
485                                req.getContextPath() + req.getServletPath());
486                        }
487        /**
488         * Generate the list of tables in the database.
489         * @param data Object describing database connection
490         * @param output Object for writing web page
491         * @param req HTTP request information
492         * @param res HTTP response information
493         * @throws IOException
494         */
495        protected void processListTables(DatabaseProperties data, GenericPrinter output,
496                        HttpServletRequest req, HttpServletResponse res) throws IOException
497                        {
498                int counter = 0;
499                DatabaseMetaData meta = null;
500                ResultSet rs = null;
501                String list[] = { "TABLE" };
502
503                String rootName = req.getContextPath() + req.getServletPath();
504
505                output.println("<html><head>");
506                output.println("<title>" + rootName + "/ListTables</title>");
507                output.println("</head><body>");
508                output.println("<h1>List of Tables</h1>");
509                if (databaseDescription.length() > 0)
510                {
511                        output.println("<h2>" + databaseDescription + "</h2>");
512                }
513                try
514                {
515                        meta = data.getConnection().getMetaData();
516                        rs = meta.getTables(null, null, null, list);
517                }
518                catch (SQLException e)
519                {
520                        output.println("<p>Unable to get list of tables</p>");
521                        output.println(StringHelpers.escapeHTML(e.getClass().getName() + " " +
522                                        e.getMessage()));
523                        output.println("</body></html>");
524                        return;
525                }
526                try
527                {
528                        output.println("<table border><tr>");
529                        output.println("<td><b>Catalog</b></td><td><b>Schema</b></td><td><b>Table</b></td>");
530                        output.println("<td><b>Remarks</b></td></tr>");
531
532                        while (rs.next())
533                        {
534                                String catalog = rs.getString("TABLE_CAT");
535                                if (rs.wasNull()) {
536                                        catalog = new String();
537                                }
538                                
539                                String schema = rs.getString("TABLE_SCHEM");
540                                if (rs.wasNull()) {
541                                        schema = new String();
542                                }
543                                String item = rs.getString("TABLE_NAME");
544                                if (!isIncluded(data, catalog, schema, item))
545                                {
546                                        continue;
547                                }
548                                counter++;
549                                output.println("<tr>");
550                                output.println("<td>" + catalog + "</td><td>" + schema +
551                                                "</td><td>" + 
552                                                "<a href=\"" + rootName + "/DescribeTable?CATALOG=" + catalog +
553                                                "&SCHEMA=" + schema + "&ITEM=" + item +
554                                                "\">" + item + "</a></td><td>" +
555                                                rs.getString("REMARKS") + "</td>");
556                                output.println("</tr>");
557                        }
558                        output.println("</table>");
559                }
560                catch (SQLException e)
561                {
562                        output.println("<p>A problem was encountered while creating the list of tables</p>");
563                }
564                output.println("<p>There are " + Integer.toString(counter) + " tables</p>");
565                output.println("<hr />");
566                output.println("<p><a href=\"" + rootName + "\">Return to root page</a></p>");
567                        }
568        /**
569         * Generate the list of views in the database.
570         * @param data Object describing database connection
571         * @param output Object for writing web page
572         * @param req HTTP request information
573         * @param res HTTP response information
574         * @throws IOException
575         */
576        protected void processListViews(DatabaseProperties data, GenericPrinter output,
577                        HttpServletRequest req, HttpServletResponse res) throws IOException
578                        {
579                if (!isValid)
580                {
581
582                }
583                String list[] = { "VIEW" };
584                String catalog = null;
585                String schema = null;
586                String item = null;
587                ResultSet rs = null;
588                DatabaseMetaData meta = null;
589                int counter = 0;
590                String rootName = req.getContextPath() + req.getServletPath();
591                output.println("<html><head>");
592                output.println("<title>" + rootName + "/ListViews</title>");
593                output.println("</head><body>");
594                output.println("<h1>List of Views</h1>");
595                try
596                {
597                        meta = data.getConnection().getMetaData();
598                        rs = meta.getTables(null, null, null, list);
599                        output.println("<table border><tr><td></td><td><b>Catalog</b></td>" +
600                                        "<td><b>Schema</b></td><td><b>View Name</b></td>" +
601                                        "<td><b>Remarks</b></td></tr>");
602                        while (rs.next())
603                        {
604                                catalog = rs.getString("TABLE_CAT");
605                                schema = rs.getString("TABLE_SCHEM");
606                                item = rs.getString("TABLE_NAME");
607                                if (!isIncluded(data, catalog, schema, item))
608                                {
609                                        continue;
610                                }
611                                counter++;
612                                output.println("<tr><td>" + Integer.toString(counter) + "</td><td>" +
613                                                catalog + "</td><td>" + schema + "</td><td>" +
614                                                item + "</td><td>" + rs.getString("REMARKS") + "</td></tr>");
615                        }
616                        output.println("</table>");
617                        output.println("<p>There are " + Integer.toString(counter) + " views</p>");
618                }
619                catch (SQLException e)
620                {
621                        output.println("<p>Problem generating list of views</p>");
622                }
623
624                output.println("<p><a href=\"" + rootName + "\">Return to root page</a></p>");
625                        }
626        /**
627         * Generate the list of procedures in the database.
628         * 
629         * @param data Object containing database connection information
630         * @param output Object for writing web page
631         * @param req HTTP request information
632         * @param res HTTP response information
633         * 
634         * @throws IOException
635         * 
636         * @see DatabaseMetaData#getProcedures(String, String, String)
637         */     
638        protected void processListProcedures(DatabaseProperties data, GenericPrinter output,
639                        HttpServletRequest req, HttpServletResponse res) throws IOException
640                        {
641                ResultSet rs = null;
642                DatabaseMetaData meta = null;
643                int counter = 0;
644                String rootName = req.getContextPath() + req.getServletPath();
645                output.println("<html><head>");
646                output.println("<title>" + rootName + "/ListProcedures</title>");
647                output.println("</head><body>");
648                output.println("<h1>List of Procedures</h1>");
649                try
650                {
651                        meta = data.getConnection().getMetaData();
652                        rs = meta.getProcedures(null, null, null);
653                        counter = showResultSet(output, rs );
654                }
655                catch (SQLException e)
656                {
657                        output.println("<p>Unable to get list of procedures</p>");
658                }
659                output.println("<p>There are " + Integer.toString(counter) + " procedures</p>");
660                output.println("<p><a href=\"" + rootName + "\">Return to root page</a></p>");
661                        }
662        /**
663         * Generate the list of user defined types for the database system.
664         * 
665         * <p>Needs to have code for User Defined Types inserted.</p>
666         * 
667         * @param data Database connection information
668         * @param output GenericPrinter object to which material is sent
669         * @param req HTTP request object
670         * @param res HTTP response object
671         * @throws IOException
672         * 
673         * @see DatabaseMetaData#getUDTs(String, String, String, int[])
674         */
675        protected void processUDTInformation(DatabaseProperties data, GenericPrinter output,
676                        HttpServletRequest req, HttpServletResponse res) throws IOException
677                        {
678                String rootName = req.getContextPath() + req.getServletPath();
679                output.println("<html><head");
680                output.println("<title>User Defined Types</title>");
681                output.println("</head><body>");
682                output.println("<h1>User Defined Types</h1>");
683                output.println("<hr />");
684                output.println("<p><a href=\"" +rootName + "\">Return to root page</a></p>");
685                        }
686        /**
687         * Generate a page containing information about the database system.
688         * 
689         * @param data Object containing database connection information
690         * @param output Object for writing web page
691         * @param req HTTP request information
692         * @param res HTTP response information
693         * @throws IOException
694         * @see DatabaseMetaData
695         */     
696        protected void processDatabaseInformation(DatabaseProperties data, GenericPrinter output,
697                        HttpServletRequest req, HttpServletResponse res) throws IOException
698                        {
699                boolean hasMetaData = true;
700                DatabaseMetaData meta = null;
701                Driver driver = data.getDriver();
702                ResultSet rs = null;
703                String rootName = req.getContextPath() + req.getServletPath();
704                try
705                {
706                        meta = data.getConnection().getMetaData();
707                }
708                catch (SQLException e)
709                {
710                        hasMetaData = false;
711                        meta = null;
712                }
713                output.println("<html><head>");
714                output.println("<title>" + rootName + "/DatabaseInformation</title>");
715                output.println("</head><body>");
716                if (!hasMetaData || meta == null)
717                {
718                        output.println("<p>Unable to obtain data about database</p>");
719                        output.println("</body></html>");
720                        return;
721                }
722                else
723                {
724                        boolean transactions = true;
725                        output.println("<p><a href=\"#catalogs\">Catalogs</a> " +
726                                        "<a href=\"#schemas\">Schemas</a> " +
727                                        "<a href=\"#tables\">Tables</a> " +
728                                        "<a href=\"#procedures\">Procedures</a> " +
729                                        "</p>");
730                        try
731                        {
732                                String name = meta.getDatabaseProductName();
733                                String version = Integer.toString(meta.getDatabaseMajorVersion()) +
734                                                "." + Integer.toString(meta.getDatabaseMinorVersion());
735                                transactions = meta.supportsTransactions();
736                                output.println("<h2><a name=\"database\">Database Information</a></h2>");
737                                output.println("<p>Major version: " + Integer.toString(driver.getMajorVersion()) + "</p>");
738                                output.println("<p>Minor version: " + Integer.toString(driver.getMinorVersion()) + "</p>");
739                                output.println("<p>Using database " + name +
740                                                "  Version " + version + "</p>");
741                                output.println("<p>Supports transactions: " + Boolean.toString(transactions) + "</p>");
742                        }
743                        catch (SQLException e)
744                        {
745                                output.println("<p>Unable to get database name and version</p>");
746                        }
747                        output.println("<h2><a name=\"catalogs\">Catalog Information</a></h2>");
748                        try
749                        {
750                                boolean catalogsInDataManipulation =
751                                                meta.supportsCatalogsInDataManipulation();
752                                boolean catalogAtStart = meta.isCatalogAtStart();
753                                String catalogTerm = meta.getCatalogTerm();
754                                String catalogSeparator = meta.getCatalogSeparator();
755                                if (catalogTerm == null)
756                                {
757                                        output.println("<p>No term for catalogss</p>");
758                                }
759                                else if (catalogTerm.length() == 0)
760                                {
761                                        output.println("<p>No term for catalogs</p>");
762                                }
763                                else
764                                {
765                                        output.println("<p>Term for catalogs is " + catalogTerm + "</p>");
766                                }
767                                output.println("<p>Supports catalogs in data manipulation: " +
768                                                Boolean.toString(catalogsInDataManipulation) + "</p>");
769                                output.println("<p>Supports catalog name at start of identifier: " +
770                                                Boolean.toString(catalogAtStart));
771                                if (catalogAtStart)
772                                {
773                                        output.println("<p>Separator for catalog name is <code>" +
774                                                        catalogSeparator + "</code></p>");
775                                }
776                        }
777                        catch (SQLException e)
778                        {
779                                output.println("<p>Unable to get information about catalogs</p>");
780                        }
781                        /*
782                         * Print information on schemas.
783                         */
784                        output.println("<h2><a name=\"schemas\">Schema Information</a></h2>");
785                        try
786                        {
787                                boolean schemasInDataManipulation =
788                                                meta.supportsSchemasInDataManipulation();
789                                String schemaTerm = meta.getSchemaTerm();
790                                if (schemaTerm == null)
791                                {
792                                        output.println("<p>No term for schemas</p>");
793                                }
794                                else if (schemaTerm.length() == 0)
795                                {
796                                        output.println("<p>No term for schemas</p>");
797                                }
798                                else
799                                {
800                                        output.println("<p>Term for schemas is " + schemaTerm + "</p>");
801                                }
802                                output.println("<p>Supports schemas in data manipulation: " +
803                                                Boolean.toString(schemasInDataManipulation) + "</p>");
804                        }
805                        catch (SQLException e)
806                        {
807                                output.println("<p>Unable to get information about schemas</p>");
808                        }
809                        /*
810                         * Print information on tables
811                         */
812                        output.println("<h2><a name=\"tables\">Tables</a></h2>");
813                        try
814                        {
815                                int counter = 0;
816                                rs = meta.getTableTypes();
817                                output.println("<p>The table types are </p>");
818                                counter = showResultSet(output, rs);
819                                output.println("<p>There are " + Integer.toString(counter) + " table types</p>");
820                        }
821                        catch(SQLException e)
822                        {
823                                output.println("<p>Unable to get information on table types</p>");
824                        }
825                        /*
826                         * Print information on procedures.
827                         */
828                        output.println("<h2><a name=\"procedures\">Procedures</a></h2>");
829                        try
830                        {
831                                String value = meta.getProcedureTerm();
832                                output.println("<p>Term for procedure is " + value + "</p>");
833                        }
834                        catch (SQLException e)
835                        {
836                                output.println("<p>Unable to get information on procedures</p>");
837                        }
838                }
839                output.println("<hr />");
840                output.println("<p><a href=\"" + rootName + "\">Return to root page</p>");
841                        }
842        /**
843         * Generate a web page describing the layout of a table.
844         * 
845         * @param data Database connection data object
846         * @param output GenericPrinter object to which information is sent
847         * @param req HTTP request object
848         * @param res HTTP response object
849         * @throws IOException
850         * @see DatabaseMetaData#getTables(String, String, String, String[])
851         * @see DatabaseMetaData#getColumns(String, String, String, String)
852         * @see DatabaseMetaData#getIndexInfo(String, String, String, boolean, boolean)
853         * @see DatabaseMetaData#getExportedKeys(String, String, String)
854         * @see DatabaseMetaData#getImportedKeys(String, String, String)
855         */
856        protected void processDescribeTable(DatabaseProperties data, GenericPrinter output, 
857                        HttpServletRequest req, HttpServletResponse res) throws IOException
858                        {
859                String catalog = null;
860                String schema = null;
861                String item = null;
862                int counter = -1;
863                String rootName = req.getContextPath() + req.getServletPath();
864                DatabaseMetaData meta = null;
865                ResultSet rs = null;
866                try
867                {
868                        catalog = req.getParameter("CATALOG");
869                        if (catalog.length()==0) {
870                                catalog=null;
871                        }
872
873                        schema = req.getParameter("SCHEMA");
874                                                if (schema.length()==0) {
875                                schema = null;
876                        }
877                        item = req.getParameter("ITEM");
878                        output.println("<html><head>");
879                        output.println("<title>Layout of Table " + item + "</title>");
880                        output.println("</head><body>");
881                        output.println("<h1>Layout of Table " + item + "</h1>");
882                        if (catalog != null) {
883                                output.println("<p>Catalog: " + catalog + "</p>");
884                        }
885                        if (schema != null) {
886                                output.println("<p>Schema: " + schema + "</p>");
887                        }
888                        output.println("<p>Running processDescribeTable</p>");
889                        meta = data.getConnection().getMetaData();
890                        output.println("<p>Result of getColumns</p>");
891                        rs = meta.getColumns(catalog, schema, item, (String) null);
892                        output.println("<p>Information on columns</p>");
893                        counter = showResultSet(output, rs);
894                        output.println("<p>There are " + Integer.toString(counter) + " columns</p>");
895                        rs = null;
896                        output.println("<hr />");
897                        output.println("<p>Result of getIndexInfo</p>");
898                        rs = meta.getIndexInfo(catalog, schema, item, false, false);
899                        counter = showResultSet(output, rs);
900                        output.println("<p>There are " + Integer.toString(counter) + " indexes</p>");
901                        rs = null;
902                        output.println("<hr />");
903                        output.println("<p>Result of getImportedKeys</p>");
904                        rs = meta.getImportedKeys(catalog, schema, item);
905                        counter = showResultSet(output, rs);
906                        output.println("<p>There are " + Integer.toString(counter) + " imported keys</p>");
907                        rs = null;
908                        output.println("<hr />");
909                        output.println("<p>Result of getExportedKeys</p>");
910                        rs = meta.getExportedKeys(catalog, schema, item);
911                        counter = showResultSet(output, rs);
912                        output.println("<p>There are " + Integer.toString(counter) + " exported keys</p>");
913                        rs = null;
914                        output.println("<hr />");
915                        output.println("<p><a href=\"" + rootName + "\">Return to root page</a></p>");  
916                }
917                catch (SQLException e)
918                {
919                        System.out.println("<p>Unable to get information</p>");
920                        System.out.println("<hr />");
921                        System.out.println("<p><a href=\"" + rootName + "\">Return to root page</a></p>");
922                }
923                        }
924
925        /**
926         * Displays a result set as an HTML table.
927         * @param output Object for printing to HTML page
928         * @param rs Result set to be processed
929         * @return number of rows
930         */
931        protected int showResultSet(GenericPrinter output, ResultSet rs)
932        {
933                int counter = 0;
934                ResultSetMetaData rsmeta = null;
935                try
936                {
937                        rsmeta = rs.getMetaData();
938                }
939                catch (SQLException e)
940                {
941                        output.println("<p>Unable to get metadata</p>");
942                        output.println(StringHelpers.escapeHTML(e.getClass().getName() + " " +
943                                        e.getMessage()));
944                        output.println("</body></html>");
945                        return -1;
946                }
947                try
948                {
949                        int columnCount = rsmeta.getColumnCount();
950                        int columnType[] = new int[columnCount];
951                        output.println("<table border><tr><td>&nbsp;</td>");
952                        for (int i = 1; i <= columnCount; i++)
953                        {
954                                columnType[i-1] = rsmeta.getColumnType(i);
955                                output.println("<td><b><code>" + 
956                                                StringHelpers.escapeHTML(rsmeta.getColumnName(i)) + "</code></b></td>");
957                        }
958                        output.println("</tr>");
959                        while (rs.next())
960                        {
961                                counter++;
962                                output.println("<tr><td align=\"right\">" + Integer.toString(counter) +
963                                                "</td>");
964                                for (int i = 1; i <= columnCount; i++)
965                                {
966                                        int localType = columnType[i-1];
967                                        if (localType == Types.BOOLEAN)
968                                        {
969                                                output.println("<td>" + Boolean.toString(rs.getBoolean(i)) + "</td>");
970                                        }
971                                        else if (localType == Types.VARCHAR || localType == Types.CHAR)
972                                        {
973                                                output.println("<td><code>" + StringHelpers.escapeHTML(rs.getString(i)) + "</code></td>");
974                                        }
975                                        else
976                                        {
977                                                output.println("<td align=\"right\"><code>" + 
978                                                                StringHelpers.escapeHTML(rs.getString(i)) + "</code></td>");
979                                        }
980                                }
981                                output.println("</tr>");
982                        }
983                        output.println("</table>");
984                }
985                catch (SQLException e)
986                {
987                        output.println("<p>Problems generating list</p>");
988                        return -1;
989                }
990                return counter;
991        }
992}