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