Shhhh. I’ll tell you a secret. I don’t like tables. I know. Shocking, isn't it? Don't get me wrong: I don't dislike tables per se. They're great for displaying tabular material. (For page organization, not so much.) But I so dislike the code needed to build a table within a JSP. It usually comes down to something like this: User IDNameEmail${row.userID}${row.name}${row.email} All that iterative logic simply looks incomprehensible to me. It's still better than scriptlets or custom tag libraries (both of which were, to be sure, phenomenal in their time), but it's an undigestible mass, and even if I do step through it line by line and understand what it does, I'm still left with just a table. Users accustomed to active, Javascript-assisted widgets don't respond to tables that just lie there. Many more lines of code will be needed to enable them to do useful things like paginating through long lists of items, sorting by column values, and the like. It'll be an unholy mix of HTML, JSP directives, JSP tags, EL, Javascript, Java, XML, properties files, and so forth. The whole thing seems so error-prone (note to self: more code + more languages = more "opportunities" for bugs). But recently I discovered an open-source Java library called JMesa that provides another way. I'm going to share with you some of the things I've found in JMesa, building up an HTML page containing a table from nothing to, well, considerably more than nothing. There's a good deal of code here, to give you a sense of the JMesa API; hopefully. you'll come away with some ideas about how you can use JMesa in your own projects. I won't bother with package declarations, imports, or code not relevant to the point at hand; the complete code is available for download in the form of an Eclipse project. Installation instructions will be found at the end of this article. Join me in exploring JMesa! Preparation A Page to Show Before we can get to JMesa, though, we'll need a few things: a page within which to display our table, for instance. In fact, we'll learn even more if we put this page in a context. I have recently fallen in like with Spring MVC and so will use that to build a simple site with a few pages. Just to be clear, while Spring dependency injection and utilities are woven into the code below, JMesa does not depend upon Spring. The pages are not fancy, and I am going to skip most of the setup. Everything is included in the download, of course. One thing I shouldn't skip is the controller for the search results page, the page within which we will build our table. We'll start with pretty much the simplest functionality we can: public class SimpleSearchController extends AbstractController { @Override protected ModelAndView handleRequestInternal(HttpServletRequest request, HttpServletResponse response) throws Exception { return new ModelAndView("simple-results", "results", "Here we will display search results"); } } For those not familiar with Spring MVC, the ModelAndView return value contains a string that will be resolved to a view (in this project, it is resolved to "/WEB-INF/jsp/simple-results.jsp"), and a key-value pair (the second and third constructor arguments) that can be accessed using EL on the JSP page: ${results} Finally, we use the Spring jmesa-servlet.xml configuration file to create and associate a URL with our controller: welcomeController simpleSearchController Clicking on the "Search" link in the menu now produces: [img_assist|nid=3678|title=Figure 1.|desc=A simple page for our table|link=none|align=left|width=757|height=240] All right, not much. But it's the page we need. Something to Display Another thing we need before we can build a table is something to show in it. This "domain" object should be pretty easy to display: public class HelloWorld implements Comparable { private int pk; private String hello = "Hello"; private String world = "world"; private String from = "from"; private String firstName; private String lastName; private String format = "{0}, {1}! {2} {3} {4}"; // ... accessors and mutators public String toString() { return MessageFormat.format(getFormat(), hello, world, from, getFirstName(), getLastName()); } // ... implementations of equals, hashCode, and compareTo } Persistence Service Of course, we need instances of this domain object. Normally, we'd get them from a persistence service; for now, we'll just create them in memory: public class HelloWorldService { private int nextId; private Set helloWorlds = new TreeSet(); public HelloWorldService() { nextId = 1; helloWorlds.add(newInstance("Albert", "Einstein")); helloWorlds.add(newInstance("Grazia", "Deledda")); helloWorlds.add(newInstance("Francis", "Crick")); helloWorlds.add(newInstance("Linus", "Pauling")); helloWorlds.add(newInstance("Theodore", "Roosevelt")); helloWorlds.add(newInstance("Hideki", "Yukawa")); helloWorlds.add(newInstance("Harold", "Urey")); helloWorlds.add(newInstance("Barbara", "McClintock")); helloWorlds.add(newInstance("Hermann", "Hesse")); helloWorlds.add(newInstance("Mikhail", "Gorbachev")); helloWorlds.add(newInstance("Amartya", "Sen")); helloWorlds.add(newInstance("Albert", "Gore")); helloWorlds.add(newInstance("Amnesty", "International")); helloWorlds.add(newInstance("Daniel", "Bovet")); helloWorlds.add(newInstance("William", "Faulkner")); helloWorlds.add(newInstance("Otto", "Diels")); helloWorlds.add(newInstance("Marie", "Curie")); } public Set findAll() { return helloWorlds; } private HelloWorld newInstance(String firstName, String lastName) { HelloWorld hw = new HelloWorld(); hw.setPk(nextId++); hw.setFirstName(firstName); hw.setLastName(lastName); return hw; } } That's that. Now we're ready to focus on JMesa. JMesa Let's start with something extremely simple. On the very first page of the JMesa web site we find four lines of code that we can appropriate and refashion for a Spring controller: public class BasicJMesaSearchController extends AbstractController { private HelloWorldService helloWorldService; public void setHelloWorldService(HelloWorldService helloWorldService) { this.helloWorldService = helloWorldService; } @Override protected ModelAndView handleRequestInternal(HttpServletRequest request, HttpServletResponse response) throws Exception { Set results = helloWorldService.findAll(); TableFacade tableFacade = new TableFacadeImpl("results",request); tableFacade.setItems(results); tableFacade.setColumnProperties("pk", "firstName", "lastName", "format"); return new ModelAndView("results", "results", tableFacade.render()); } } We let Spring inject the HelloWorldService, which we use to retrieve a set of items to display. Then we create and configure the JMesa TableFacade class. This class takes an HTTP request in its constructor: TableFacade is going to send itself messages passed as parameters in the request (more on this in a moment). We supply it with the set of items and with which JavaBean property of those items we want displayed in each column. We'll also need a bit of new code in the search results page (in the project, this is actually a different search results page, as you, oh sharp-eyed reader, have already noticed): ${results} And we'll need to create and point to the new controller in jmesa-servlet.xml: ... basicSearchController Redeploy, and the results look like magic. How did we get them? [img_assist|nid=3679|title=Figure 2.|desc=Using JMesa "out of the box"|link=none|align=left|width=757|height=405] The key is in the variable results, which now holds the entire text of the table generated by the JMesa TableFacade when we called its render method. We also put a self-submitting HTML form around the JMesa table that it will use to send itself messages about how to alter itself. This makes possible many amazing features. The table automagically paginates itself. It allows the user to change the number of rows displayed. It allows sorting on any column or combination of columns. It provides color striping of table rows and onMouseOver row highlighting. And every bit of this came for free: we did nothing to enable it but what you have already seen. (OK, we played around with some of JMesa's images and CSS style sheets to make it fit in with our color scheme, but that really shouldn't count.) To demonstrate, we'll use the select at the top of the form to change the number of rows displayed to 16, sort by first name ascending and last name descending (by clicking on the first column header once and the second twice), and mouse over the third row to see the highlighting: [img_assist|nid=3681|title=Figure 3.|desc=JMesa search results sorted and highlighted|link=none|align=left|width=757|height=565] Now Al Gore and Einstein appear in the order we asked for. You will have noticed the images in the table toolbar. Those on the left are standard first, previous, next, and last navigation icons. The select we've already mentioned. But there are two other images as well: these turn filtering, another amazing feature of JMesa that is active by default, on and off. Filtering allows the user to apply expressions to a column in order to display only rows having matching values in that column. While filtering can take setup beyond the scope of this article, even by default it's astonishing. Try typing "Einstein" in the text field that appears above the last-name column header and clicking on the filter icon (the magnifying glass). The results show only the row containing Einstein's name in the last name column. And we didn't have to do a thing! [img_assist|nid=3680|title=Figure 4.|desc=JMesa search results filtered|link=none|align=left|width=757|height=280] See the JMesa web site for details about filtering, editable tables that keep track of your changes for you, and much, much more: it's impressive stuff. Customizing And now, to business. The JMesa default is astounding, but no default is ever exactly like you want it. The ability to customize is critical. Also, defaults rarely exercise every feature, and this one is no exception. Let's start with some requirements: We will display the value of each HelloWorld item's toString method in an additional column We will display more user-friendly values in the format column We will ensure that columns that cannot be reasonably sorted are made unsortable We will add columns containing links to edit and delete pages for the HelloWorld items We will display images in the edit and delete columns We will not display the Pk property of each item, but will pass its value to edit and delete pages as needed We will enable the user to retrieve a comma-separated-values (CSV) copy of the table contents We will enable the user to retrieve an Excel spreadsheet copy of the table contents We will disable filtering and highlighting We will reorganize the toolbar items in a different order Believe it or not, implementing each of these features will be quite easy! and you'll begin to get a sense for the possibilities of JMesa. ToString Column Each HelloWorld item produces a formatted string within its toString method. This is not a JavaBean property method, so we cannot directly point the TableFacade at it. We want this value to be rendered (to use JMesa terminology) as the contents of a (a cell) in each HTML row. Cell contents are produced by implementations of the CellEditor interface. Its getValue method is passed the item to be displayed, the property to be called, and the current row count. Since only the item itself is actually needed for our purpose, the implementation is simple: public class ToStringCellEditor implements CellEditor { @Override public Object getValue(Object item, String property, int rowcount) { if (item == null) { return ""; } return item.toString(); } } Of course, we'll need a column into which to put the results. All we need do is add an arbitrary value to the column properties list: tableFacade.setColumnProperties("firstName", "lastName", "format", "toString"); This value is used to retrieve the column: Row row = tableFacade.getTable().getRow(); Column column = row.getColumn("toString"); column.getCellRenderer().setCellEditor(new ToStringCellEditor()); Of course, this means that the getValue method of the ToStringCellEditor will always be passed a bogus property value, but since the editor doesn't use it, that's no problem. (Note that we've also left off the pk column as per requirements.) User-Friendly Format Column We continue by introducing a more user-friendly value into the format column. The format string "{0}, {1}! {2} {3} {4}" looks ugly and most likely won't be understood by an end user. The only real information it conveys is that it is the default value. We'll use a Spring MessageSource to supply something a little easier on the eyes at runtime. First, we'll add a property to the messages.properties file loaded by Spring at application startup: format.{0},\ {1}!\ {2}\ {3}\ {4}=Default format (The backslashes are needed to escape the white space in the key.) As we have already seen, a CellEditor is needed to change a cell's displayed value. Using MessageSource to produce the display value at runtime requires a few more lines than the ToStringCellEditor: public class SpringMessageCellEditor implements CellEditor { MessageSource source; String prefix; Locale locale; public SpringMessageCellEditor(MessageSource source, String prefix, Locale locale) { this.source = source; this.prefix = prefix; this.locale = locale; } public Object getValue(Object item, String property, int rowcount) { if (item != null) { try { return source.getMessage(prefix + "." + PropertyUtils.getProperty(item, property), null, locale); } catch (IllegalAccessException ignore) { } catch (InvocationTargetException ignore) { } catch (NoSuchMethodException ignore) { } } return null; } } We still have to add this editor to the column displaying the format property: Column column = row.getColumn("format"); column.getCellRenderer().setCellEditor(new SpringMessageCellEditor(messageSource, "format", locale); Unsortable Columns Next, we want the table to know that some columns are unsortable. Columns are typically sorted by property value, but we just added a column that corresponds to no property, that displays the output of the toString method. If the user clicked on the header of that column, he or she would wind up with a very ugly NullPointerException message. Making a column (actually, we need to have an HtmlColumn, but most columns qualify) unsortable is very simple: htmlColumn.setSortable(false); With this, no onClick method will be generated for the column header, preventing users from accidentally causing a mess. Edit and Delete Columns Now we'll add columns containing links to edit and delete pages for HelloWorld items. I prefer using icons to buttons saying "Edit" and "Delete", as it reduces the amount of textual information the user must process. Tables typically present a lot of information in a compact space, making user overload a problem worthy of attention. To do this, we'll need a CellEditor (by now, you knew that was coming!). Since this is functionality I use a lot, let's design it for reuse, refactoring out reusable code into one class, and code tailored to this project into another. ImageCellEditor encapsulates the general process of setting up an image with a link, and includes a method that will let subclasses override the default processing of the link: public class ImageCellEditor extends AbstractContextSupport implements CellEditor { private String image; private String alt; private String link; public ImageCellEditor(String image, String alt, String link) { this.image = image; this.alt = alt; this.link = link; } public Object getValue(Object item, String property, int rowcount) { CoreContext context = getCoreContext(); String imagePath = context.getPreference("html.imagesPath"); StringBuilder img = new StringBuilder(); if (link != null && link.trim().length() != 0) { img.append(""); } img.append(""); if (link != null && link.trim().length() != 0) { img.append(""); } return img.toString(); } /** * This method can be overridden by subclasses to handle specific * HTML link needs. */ public String processLink(Object item, String property, int rowcount, String link) { return link; } } This is our opportunity to introduce CoreContext and WebContext, two important classes that plug our code into the JMesa infrastructure. Extending AbstractContextSupport gets us JavaBean property methods for these objects (just a convenience; I could have implemented the interface ContextSupport, but then I would have had to write the property methods myself). The CoreContext has many uses; our immediate purpose for it is to retrieve a value configured in the jmesa.properties file. This was pointed to in web.xml: jmesaPreferencesLocation WEB-INF/jmesa.properties It contains a preference called "html.imagesPath" that replaces the default path from which JMesa retrieves images: html.imagesPath=/images/ This means we won't have to hard-code a part of the image URL. (There are a lot more configurable preferences: for details, see the JMesa web site.) The WebContext provides us with the servlet context path, again letting us avoid hard-coding the image URL: getWebContext().getContextPath() Getting back to the two image columns, we have a requirement to pass the Pk property of the appropriate HelloWorld to the edit or delete pages when the images are clicked. Adding this property to the link is easy, using the MessageFormat class to process the link argument of the application-specific subclass: public class HelloWorldImageCellEditor extends ImageCellEditor { public String processLink(Object item, String property, int rowcount, String link) { return MessageFormat.format(link, ((HelloWorld) item).getPk()); } } After creating the editor, we can retrieve the context objects for it from the TableFacade: ImageCellEditor editor = new HelloWorldImageCellEditor("edit.gif", messageSource.getMessage("image.edit.alt", null, locale), "edit.html?pk={0,number,integer}"); editor.setWebContext(tableFacade.getWebContext()); editor.setCoreContext(tableFacade.getCoreContext()); Now we have the images and the links. But it would be awfully nice if the images could be centered within the column, something notoriously difficult to achieve with CSS style sheets. What would work would be to use the align and valign attributes of the cell. How can we do that? The cell itself, as opposed to its contents, is rendered by the interface CellRenderer. Unfortunately, the HtmlCellRenderer sub-interface that comes with JMesa has no method for adding attributes. The Decorator and Template patterns, however, come to the rescue. Again, we implement the functionality for reuse as two classes, the first a generic decorator with an additional template method: public abstract class AttributedHtmlCellRendererDecorator implements HtmlCellRenderer { // all other methods will be delegated to this renderer protected HtmlCellRenderer renderer; public AttributedHtmlCellRendererDecorator(HtmlCellRenderer renderer) { this.renderer = renderer; } public Object render(Object item, int rowcount) { HtmlBuilder html = new HtmlBuilder(); html.td(2); html.width(getColumn().getWidth()); addAttributes(html); html.style(getStyle()); html.styleClass(getStyleClass()); html.close(); String property = getColumn().getProperty(); Object value = getCellEditor().getValue(item, property, rowcount); if (value != null) { html.append(value.toString()); } html.tdEnd(); return html.toString(); } /** * Subclasses will add attributes. */ public abstract void addAttributes(HtmlBuilder html); } The second will be a subclass that adds the specific attributes we need: public class AlignedHtmlCellRendererDecorator extends AttributedHtmlCellRendererDecorator { private String align; private String valign; public AlignedHtmlCellRendererDecorator(HtmlCellRenderer renderer, String align, String valign) { super(renderer); this.align = align; this.valign = valign; } @Override public void addAttributes(HtmlBuilder html) { html.align(align); html.valign(valign); } } Whew, that was a mouthful! However, our images will come out nicely centered in the column, and we've learned a good deal more about how the JMesa API works. There will be edit and delete pages to link to, of course, but these are not of interest here and are completely trivial in the Eclipse project. CSV and Excel Output In JMesa terminology, output other than HTML is called exporting the table. As complex as it might seem, it's actually the easiest part of the process. Again, a single line of code will do all we need: tableFacade.setExportTypes(response, org.jmesa.limit.ExportType.CSV, org.jmesa.limit.ExportType.EXCEL); That's really all there is to it! (OK, you have to include some JAR files in the library, but what did you expect, magic?) Filtering and Highlighting Making a row (we need an HtmlRow) unfilterable and unhighlighted is just as simple as making a column unsortable: htmlRow.setFilterable(false); htmlRow.setHighlighter(false); With this, no filtering row or icons will be generated above the column header and the highlighting feature will be turned off. Toolbar The code to reorganize the toolbar is quite straightforward; while we're at it, we need to include icons for the various output formats: public class ReorderedToolbar extends AbstractToolbar { @Override public String render() { if (ViewUtils.isExportable(getExportTypes())) { addExportToolbarItems(getExportTypes()); addToolbarItem(ToolbarItemType.SEPARATOR); } MaxRowsItem maxRowsItem = (MaxRowsItem) addToolbarItem(ToolbarItemType.MAX_ROWS_ITEM); if (getMaxRowsIncrements() != null) { maxRowsItem.setIncrements(getMaxRowsIncrements()); } addToolbarItem(ToolbarItemType.SEPARATOR); addToolbarItem(ToolbarItemType.FIRST_PAGE_ITEM); addToolbarItem(ToolbarItemType.PREV_PAGE_ITEM); addToolbarItem(ToolbarItemType.NEXT_PAGE_ITEM); addToolbarItem(ToolbarItemType.LAST_PAGE_ITEM); return super.render(); } } I arranged the icons by simply specifying the order in which they are added to the toolbar. They look more natural to me this way; your mileage may vary. Note that we delegate the messy work of actually rendering the toolbar to the JMesa superclass. Putting It All Together We'll refactor out reusable code once more in writing a Factory to encapsulate building our customized table, starting with an abstract class: public abstract class AbstractTableFactory { protected abstract String getTableName(); protected abstract void configureColumns(TableFacade tableFacade, Locale locale); protected abstract void configureUnexportedTable(TableFacade tableFacade, Locale locale); protected abstract ImageCellEditor getEditImageCellEditor(Locale locale); protected abstract ImageCellEditor getDeleteImageCellEditor( Locale locale); public TableFacade createTable(HttpServletRequest request, HttpServletResponse response, Collection items) { TableFacade tableFacade = new TableFacadeImpl(getTableName(), request); tableFacade.setItems(items); tableFacade.setStateAttr("return"); configureTableFacade(response, tableFacade); Locale locale = request.getLocale(); configureColumns(tableFacade, locale); if (! tableFacade.getLimit().isExported()) { configureUnexportedTable(tableFacade, locale); } return tableFacade; } public void configureTableFacade(HttpServletResponse response, TableFacade tableFacade) { tableFacade.setExportTypes(response, getExportTypes()); tableFacade.setToolbar(new ReorderedToolbar()); Row row = tableFacade.getTable().getRow(); if (row instanceof HtmlRow) { HtmlRow htmlRow = (HtmlRow) row; htmlRow.setFilterable(false); htmlRow.setHighlighter(false); } } protected ExportType[] getExportTypes() { return null; } protected void configureColumn(Column column, String title, CellEditor editor) { configureColumn(column, title, editor, false, true); } protected void configureColumn(Column column, String title, CellEditor editor, boolean filterable, boolean sortable) { column.setTitle(title); if (editor != null) { column.getCellRenderer().setCellEditor(editor); } if (column instanceof HtmlColumn) { HtmlColumn htmlColumn = (HtmlColumn) column; htmlColumn.setFilterable(filterable); htmlColumn.setSortable(sortable); } } protected void configureEditAndDelete(Row row, WebContext webContext, CoreContext coreContext, Locale locale) { HtmlComponentFactory factory = new HtmlComponentFactory(webContext, coreContext); HtmlColumn col = factory.createColumn((String) null); col.setFilterable(false); col.setSortable(false); CellRenderer renderer = col.getCellRenderer(); ImageCellEditor editor = getEditImageCellEditor(locale); editor.setWebContext(webContext); editor.setCoreContext(coreContext); renderer.setCellEditor(editor); col.setCellRenderer(new AlignedHtmlCellRendererDecorator((HtmlCellRenderer) renderer, "center", "middle")); row.addColumn(col); col = factory.createColumn((String) null); col.setFilterable(false); col.setSortable(false); renderer = col.getCellRenderer(); editor = getDeleteImageCellEditor(locale); editor.setWebContext(webContext); editor.setCoreContext(coreContext); renderer.setCellEditor(editor); col.setCellRenderer(new AlignedHtmlCellRendererDecorator((HtmlCellRenderer) renderer, "center", "middle")); row.addColumn(col); } } This has a lot of code (note the abstract methods ), in part because I know I usually want edit and delete columns. One line that might pass by unnoticed in all this, however, is really quite something: tableFacade.setStateAttr("return"); When this attribute is set, JMesa uses the Memento design pattern to save the state of its tables. When you return to a table page and include the attribute you specify here in the URL, you return to the exact place you left: the page number to which you had moved before leaving the table, the number of values displayed per page, and so forth. The application-specific concrete class, after all this, can be pretty simple: public class HelloWorldTableFactory extends AbstractTableFactory { protected MessageSource messageSource; public void setMessageSource(MessageSource messageSource) { this.messageSource = messageSource; } @Override protected String getTableName() { return "results"; } @Override protected ExportType[] getExportTypes() { return new ExportType[] { CSV, EXCEL }; } @Override protected void configureColumns(TableFacade tableFacade, Locale locale) { tableFacade.setColumnProperties("firstName", "lastName", "format", "toString"); Row row = tableFacade.getTable().getRow(); configureColumn(row.getColumn("firstName"), messageSource.getMessage("column.firstName", null, locale), null); configureColumn(row.getColumn("lastName"), messageSource.getMessage("column.lastName", null, locale), null); configureColumn(row.getColumn("format"), messageSource.getMessage("column.format", null, locale), new SpringMessageCellEditor(messageSource, "format", locale), false, false); configureColumn(row.getColumn("toString"), messageSource.getMessage("column.toString", null, locale), new ToStringCellEditor(), false, false); } @Override protected void configureUnexportedTable(TableFacade tableFacade, Locale locale) { HtmlTable table = (HtmlTable) tableFacade.getTable(); table.setCaption(messageSource.getMessage("table.caption", null, locale)); configureEditAndDelete(table.getRow(), tableFacade.getWebContext(), tableFacade.getCoreContext(), locale); } @Override protected ImageCellEditor getEditImageCellEditor(Locale locale) { return new HelloWorldImageCellEditor("edit.gif", messageSource.getMessage("image.edit.alt", null, locale), "edit.html?pk={0,number,integer}"); } @Override protected ImageCellEditor getDeleteImageCellEditor(Locale locale) { return new HelloWorldImageCellEditor("delete.gif", messageSource.getMessage("image.delete.alt", null, locale), "delete.html?pk={0,number,integer}"); } } Controller We end as we began, with a Spring MVC Controller to launch all this infrastructure. Since the details of table creation are encapulated in a factory, this is uncluttered: the only decision to be made is whether or not the table is to be exported. If it is exported, the results will be written directly to the output stream of the response; if not, they'll be rendered as a string containing our HTML table: public class CustomJMesaSearchController extends AbstractController { private HelloWorldService helloWorldService; private HelloWorldTableFactory tableFactory; public void setHelloWorldService(HelloWorldService helloWorldService) { this.helloWorldService = helloWorldService; } public void setTableFactory(HelloWorldTableFactory tableFactory) { this.tableFactory = tableFactory; } @Override protected ModelAndView handleRequestInternal(HttpServletRequest request, HttpServletResponse response) throws Exception { Set results = helloWorldService.findAll(); TableFacade tableFacade = tableFactory.createTable(request, response, results); if (tableFacade.getLimit().isExported()) { tableFacade.render(); return null; } return new ModelAndView("results", "results", tableFacade.render()); } } We are actually reusing the same JSP page as in the basic JMesa setup: the only difference is in the Java code that generates the table. One more change in jmesa-servlet.xml to create everything and tie it all together: ... customSearchController And how different the display looks!: [img_assist|nid=3682|title=Figure 5.|desc=A customized search result|link=none|align=left|width=757|height=465] Ajax Finally, the table looks like we want it to, but it's irritating having to resubmit the form each time we want to make a change. Isn't that the sort of thing Ajax is supposed to help us avoid? The answer is, of course, yes! So how do we leverage Ajax to help us? Fortunately, the JMesa folks have already worked that out. There are two parts to the solution: changes to the controller and changes to the JSP page. ${results} In our previous solution, the onInvokeAction Javascript method called createHiddenInputFieldsForLimitAndSubmit, which submitted the form. In the Ajax solution, it assembles parameters for the TableFacade class and sends a request for the HTML for table display, adding a parameter to indicate that it's an Ajax request. Then a callback Javascript function substitutes the returned HTML for the contents of the that now holds the table. The simplicity and unusual syntax of the latter code come courtesy of the jQuery Ajax library, which is thoughtfully used by JMesa: The controller, of course, needs to interpret this new request correctly. This is just one more branch on the decision tree we saw in the previous controller: public class AjaxJMesaSearchController extends AbstractController { @Override protected ModelAndView handleRequestInternal(HttpServletRequest request, HttpServletResponse response) throws Exception { Set results = helloWorldService.findAll(); TableFacade tableFacade = tableFactory.createTable(request, response, results); if (tableFacade.getLimit().isExported()) { tableFacade.render(); return null; } else if ("true".equals(request.getParameter("ajax"))) { String encoding = response.getCharacterEncoding(); byte[] contents = tableFacade.render() .getBytes(encoding); response.getOutputStream().write(contents); return null; } return new ModelAndView("ajax-results", "results", tableFacade.render()); } } Of course, we have to make Spring aware of the controller change in jmesa-servlet.xml: ... ajaxSearchController That's all there is to it! The table looks and acts just as it did, except now it refreshes without resubmitting the form each time. Conclusion Now I don't have to like tables: I can program them in Java and not worry about them on a display JSP. This makes the page cleaner, gives me more functionality out-of-box, and enables me to nix at least some of the languages I'd otherwise have to fuss with. What's not to like? I hope you'll take a good look at JMesa and see if it can make your life easier, and that this article helps you decide. Good luck! Installation of the Eclipse Project Installing the Eclipse project is not difficult; the included Ant build file and these instructions assume Tomcat as the deployment target (I'm using version 6.0.14 with JDK 6.0_03). If you want to use another servlet container, though, feel free to modify the instructions and the Ant file as needed: download the ZIP archive unzip the archive to any directory; it will create its own top-level subdirectory open the project as a Java project in Eclipse the project must use the Java 6 compiler (available from "http://java.sun.com/javase/6/") the Tomcat installation must be version 6 (available from "http://tomcat.apache.org/download-60.cgi") open the build file and modify the path to the Tomcat root add an external JAR file to the Eclipse project build path from the Tomcat installation: lib/servlet-api.jar run the Ant "deploy" target, which will build automatically open a browser and point it to "http://localhost:8080/running-jmesa-examples/" or to an equivalent URL for your setup (N.B. Some code in the project has been refactored from the way it appears in the article.)

Consider a simple web application that requires user input in some fields, lets say some search box. Suppose a user types the following string in that textbox: '; DROP DATABASE pubs -- On submit our application executes the following dynamic SQL statement SqlDataAdapter myCommand = new SqlDataAdapter("SELECT OrderId, OrderNumber FROM Orders WHERE OrderNumber = '" + OrderNumberTextBox.Text + "'", myConnection); Or stored procedure: SqlDataAdapter myCommand = new SqlDataAdapter("uspGetOrderList '" + OrderNumberTextBox.Text + "'", myConnection); The intention being that the user input would be run as: SELECT OrderId, OrderNumber FROM Orders WHERE OrderNumber = 'PO123' However, the code inserts the user's malicious input and generates the following query: SELECT OrderId, OrderNumber FROM Orders WHERE OrderNumber = ''; DROP DATABASE pubs --' In this case, the ' (single quotation mark) character that starts the rogue input terminates the current string literal in the SQL statement. As a result, the opening single quotation mark character of the rogue input results in the following statement. SELECT OrderId, OrderNumber FROM Orders WHERE OrderNumber = '' The; (semicolon) character tells SQL that this is the end of the current statement, which is then followed by the following malicious SQL code. ; DROP DATABASE pubs Finally, the -- (double dash) sequence of characters is a SQL comment that tells SQL to ignore the rest of the text. In this case, SQL ignores the closing ' (single quotation mark) character, which would otherwise cause a SQL parser error. --' Using stored procedures doesn’t solve the problem either because the generated query would be: uspGetOrderList ''; DROP DATABASE pubs--' Or perhaps this was your login page and your query being: SELECT UserId FROM Users WHERE LoginId = AND Password = AND IsActive = 1 Someone could easily login by typing in the following in your login textbox: ' OR 1 = 1; -- Which makes our query: SELECT UserId FROM Users WHERE LoginId = '' OR 1 = 1; --' AND Password = '' AND IsActive = 1 Viola, the attacker has now successfully logged in to your site using SQL injection attack. SQL injection can occur, as demonstrated above, when an application uses input to construct dynamic SQL statements or when it uses stored procedures to connect to the database. Conventional security measures, such as the use of SSL and IPSec, do not protect your application from SQL injection attacks. Successful SQL injection attacks enable malicious users to execute commands in an application's database. Common vulnerabilities that make your data access code susceptible to SQL injection attacks include: Weak input validation. Dynamic construction of SQL statements without the use of type-safe parameters. Use of over-privileged database logins. So what can we do to help protect our application from such attacks? To counter SQL injection attacks, we need to: Constrain and sanitize input data Check for known good data by validating for type, length, format, and range and using a list of acceptable characters to constrain input. Create a list of acceptable characters and use regular expressions to reject any characters that are not on the list. Using the list of unacceptable characters is impractical because it is very difficult to anticipate all possible variations of bad input. Start by constraining input in the server-side code for your ASP.NET Web pages. Do not rely on client-side validation because it can be easily bypassed. Use client-side validation only to reduce round trips and to improve the user experience. Check my other blog on Validation Application Block for server-side validation. If in the previous code example, the Order Number value is captured by an ASP.NET TextBox control, you can constrain its input by using a RegularExpressionValidator control as shown in the following. If the Order Number input is from another source, such as an HTML control, a query string parameter, or a cookie, you can constrain it by using the Regex class from the System.Text.RegularExpressions namespace. The following example assumes that the input is obtained from a cookie. using System.Text.RegularExpressions; if (Regex.IsMatch(Request.Cookies["OrderNumber"], "^PO\d{3}-\d{2}$")) { // access the database } else { // handle the bad input } Performing input validation is essential because almost all application-level attacks contain malicious input. You should validate all input, including form fields, query string parameters, and cookies to protect your application against malicious command injection. Assume all input to your Web application is malicious, and make sure that you use server validation for all sources of input. Use client-side validation to reduce round trips to the server and to improve the user experience, but do not rely on it because it is easily bypassed. Apply ASP.NET request validation during development to identify injection attacks ASP.NET request validation detects any HTML elements and reserved characters in data posted to the server. This helps prevent users from inserting script into your application. Request validation checks all input data against a hard-coded list of potentially dangerous values. If a match occurs, it throws an exception of type HttpRequestValidationException. Request validation is enabled by ASP.NET by default. You can see the following default setting in the Machine.config.comments file. Confirm that you have not disabled request validation by overriding the default settings in your server's Machine.config file or your application's Web.config file. You can disable request validation in your Web.config application configuration file by adding a element with validateRequest="false" or on an individual page by setting ValidateRequest="false" on the @ Pages element. NOTE: You should disable Request Validation only on the page with a free-format text field that accepts HTML-formatted input. You can test the effects of request validation. To do this, create an ASP.NET page that disables request validation by setting ValidateRequest="false", as follows: When you run the page, "Hello" is displayed in a message box because the script in txtString is passed through and rendered as client-side script in your browser. If you set ValidateRequest="true" or remove the ValidateRequest page attribute, ASP.NET request validation rejects the script input and produces an error similar to the following. A potentially dangerous Request. Form value was detected from the client (txtString=" Use type-safe SQL parameters for data access Parameter collections such as SqlParameterCollection provide type checking and length validation. If you use a parameters collection, input is treated as a literal value, and SQL Server does not treat it as executable code. An additional benefit of using a parameters collection is that you can enforce type and length checks. Values outside of the range trigger an exception. You can use these parameters with stored procedures or dynamically constructed SQL command strings. Using stored procedures does not necessarily prevent SQL injection. The important thing to do is use parameters with stored procedures. If you do not use parameters, your stored procedures can be susceptible to SQL injection if they use unfiltered input. The following code shows how to use SqlParameterCollection when calling a stored procedure: using System.Data; using System.Data.SqlClient; using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet userDataset = new DataSet(); SqlDataAdapter myCommand = new SqlDataAdapter("uspGetOrderList", connection); myCommand.SelectCommand.CommandType = CommandType.StoredProcedure; myCommand.SelectCommand.Parameters.Add("@OrderNumber", SqlDbType.VarChar, 11); myCommand.SelectCommand.Parameters["@OrderNumber"].Value = OrderNumberTextBox.Text; myCommand.Fill(userDataset); } The @OrderNumber parameter is treated as a literal value and not as executable code. Also, the parameter is checked for type and length. In the preceding code example, the input value cannot be longer than 11 characters. If the data does not conform to the type or length defined by the parameter, the SqlParameter class throws an exception. You should review your application's use of stored procedures because simply using stored procedures with parameters does not necessarily prevent SQL injection. For example, the following parameterized stored procedure has several security vulnerabilities. CREATE PROCEDURE dbo.uspRunQuery @var ntext AS exec sp_executesql @var GO The stored procedure executes whatever statement is passed to it. Consider the @var variable being set to: DROP TABLE ORDERS; If you cannot use stored procedures, you should still use parameters when constructing dynamic SQL statements. The following code shows how to use SqlParametersCollection with dynamic SQL. using System.Data; using System.Data.SqlClient; using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet userDataset = new DataSet(); SqlDataAdapter myDataAdapter = new SqlDataAdapter("SELECT OrderId, OrderNumber FROM Orders WHERE OrderNumber = @OrderNumber", connection); myCommand.SelectCommand.Parameters.Add("@OrderNumber", SqlDbType.VarChar, 11); myCommand.SelectCommand.Parameters["@OrderNumber"].Value = OrderNumberTextBox.Text; myDataAdapter.Fill(userDataset); } If you concatenate several SQL statements to send a batch of statements to the server in a single round trip, you can still use parameters if you make sure that parameter names are not repeated i.e. use unique parameter names during SQL text concatenation. SELECT OrderId, OrderNumber FROM Orders WHERE OrderNumber = 'PO123' using System.Data; using System.Data.SqlClient; using (SqlConnection oConn = new SqlConnection(connectionString)) { SqlDataAdapter oAdapter = new SqlDataAdapter( "SELECT CustomerID INTO #Temp1 FROM Customers " + "WHERE CustomerID > @custIDParm; " + "SELECT CompanyName FROM Customers " + "WHERE Country = @countryParm and CustomerID IN " + "(SELECT CustomerID FROM #Temp1);", oConn); SqlParameter custIDParm = oAdapter.SelectCommand.Parameters.Add("@custIDParm", SqlDbType.NChar, 5); custIDParm.Value = customerID.Text; SqlParameter countryParm = oAdapter.SelectCommand.Parameters.Add("@countryParm", SqlDbType.NVarChar, 15); countryParm.Value = country.Text; oConn.Open(); DataSet dataSet = new DataSet(); oAdapter.Fill(dataSet); } Use a least privileged account that has restricted permissions in the database Ideally, you should only grant execute permissions to selected stored procedures in the database and provide no direct table access. The problem is more severe if your application uses an over-privileged account to connect to the database. For example, if your application's login has privileges to eliminate a database, then without adequate safeguards, an attacker might be able to perform this operation. If you use Windows authentication to connect, the Windows account should be least-privileged from an operating system perspective and should have limited privileges and limited ability to access Windows resources. Additionally, whether or not you use Windows authentication or SQL authentication, the corresponding SQL Server login should be restricted by permissions in the database. Consider the example of an ASP.NET application running on Microsoft Windows Server 2003 that accesses a database on a different server in the same domain. By default, the ASP.NET application runs in an application pool that runs under the Network Service account. This account is a least privileged account. Create a SQL Server login for the Web server's Network Service account. The Network Service account has network credentials that are presented at the database server as the identity DOMAIN\WEBSERVERNAME$. For example, if your domain is called XYZ and the Web server is called 123, you create a database login for XYZ\123$. Grant the new login access to the required database by creating a database user and adding the user to a database role. Establish permissions to let this database role call the required stored procedures or access the required tables in the database. Only grant access to stored procedures the application needs to use, and only grant sufficient access to tables based on the application's minimum requirements. If the ASP.NET application only performs database lookups and does not update any data, you only need to grant read access to the tables. This limits the damage that an attacker can cause if the attacker succeeds in a SQL injection attack. Use Character Escaping Techniques In situations where parameterized SQL cannot be used, consider using character escaping techniques. If you are forced to use dynamic SQL and parameterized SQL cannot be used, you need to safeguard against input characters that have special meaning to SQL Server (such as the single quote character). If not handled, special characters such as the single quote character in the input can be utilized to cause SQL injection. Escape routines add an escape character to characters that have special meaning to SQL Server, thereby making them harmless. private static string GetStringForSQL(string inputSQL) { return inputSQL.Replace("'", "''"); } Special input characters pose a threat only with dynamic SQL and not when using parameterized SQL. Your first line of defense should always be to use parameterized SQL. Avoid disclosing database error information In the event of database errors, make sure you do not disclose detailed error messages to the user. Use structured exception handling to catch errors and prevent them from propagating back to the client. Log detailed error information locally, but return limited error details to the client. If errors occur while the user is connecting to the database, be sure that you provide only limited information about the nature of the error to the user. If you disclose information related to data access and database errors, you could provide a malicious user with useful information that he or she can use to compromise your database security. Attackers use the information in detailed error messages to help deconstruct a SQL query that they are trying to inject with malicious code. A detailed error message may reveal valuable information such as the connection string, SQL server name, or table and database naming conventions. See my other post on Exception Handling - Do's and Dont's. You can use the element to configure custom, generic error messages that should be returned to the client in the event of an application exception condition. Make sure that the mode attribute is set to "remoteOnly" in the web.config file as shown in the following example. After installing an ASP.NET application, you can configure the setting to point to your custom error page as shown in the following example. Conclusion The above list is just some points found on MSDN on how you can make your site more secure by effectively preventing SQL injection attacks. You should always be reviewing your code to find these or other security vulnerabilities; remember all type of attacks start with some input, and your first line of defense should be input validation using both client-side and server-side validation. Original Author Original article written by Misbah Arefin

I have been searching for a method to dynamically route objects to databases at runtime using Hibernate and recently I found a solution which fit the bill.

This article covers queries - in particular a tuning test case and the relations between simple queries, join fetch queries, paging query results, and batch size. Paging the Query Results I will start with a short introduction about paging in EJB3: To support paging the EJB3 Query interface defines the following two methods: setMaxResults - sets the number of maximum rows to retrieve from the database setFirstResult - sets the first row to retrieve For example if our GUI displays a list of customers and we have 500,000 customers (database rows) in out database we wouldn't like to display all 500,000 records is one view (even if we put performance considerations aside - nobody can do anything with a list of 500,000 rows). The GUI design would usually include paging - we break the list of records to display into logical pages (for example 100 records per page) and the user can navigate between pages (same as Google's results navigator down the search page). When using the paging support it is important to remember that the query has to be sorted otherwise we can't be sure that when fetching the "next page" it will really be the next page (since in the absence of the 'order by' clause form a SQL query the order in which rows are fetch is unpredictable). Here is a sample use, for fetching the first tow pages of 100 rows each: Query q = entityManager.createQuery("select c from Customer c order by c.id"); q.setFirstResult(0).setMaxResults(100); .... next page ... Query q = entityManager.createQuery("select c from Customer c order by c.id"); q.setFirstResult(100).setMaxResults(100); This is a simple API and it's important (for performance) to remember using it when we need to fetch only parts of the results. Test Case Description This test cased is based on a real tuning I did for an application, I just changed the class names to Customer and Order. Let's assume that I have a Customer entity with a set of orders (lazily fetched - but it happens in eager fetch as well) and we need to: Fetch customers and their orders Do it in a "paging mode" - 100 customers per page Tuning Requirement #1 - Fetch Customers and Their Orders There are two possibilities to perform this kind of fetch: Simple select: select c from customer c order by c.id Join fetch: select distinct c from Customer c left outer join fetch c.orders order by c.id The simple select is as simple as it can be, we load a list of customers with a proxy collection in their orders field. The orders collection will be filled with data once I access it (for example c.getOrders().getSize() ). The 'join fetch' means that we want to fetch an association as an integral part of the query execution. The joined fetched entities (in the example above: c.orders) must be part of an association that is referenced by an entity returned from the query (in the example above: c). The 'join fetch' is one of the tools used for improving queries performance (see more in here). The Hibernate core documentations explains that "a 'fetch' join allows associations or collections of values to be initialized along with their parent objects, using a single select" (see here). I have in my database 18,998 customer records, each with few orders. Let's compare execution time for the two queries. My code looks the same for both queries (except of the query itself), I execute the query, then I iterate the results checking the size of of each customer orders collection and print the execution time and number of records fetch (as a sanity for the query syntax): Query q = entityManager.createQuery(queryStr); long a = System.currentTimeMillis(); List l = q.getResultList(); for (Customer c : l) { c.getOrders().size(); } long b = System.currentTimeMillis(); System.out.println("Execution time: " + (b - a)+ "; Number of records fetch: " + l.size() ); And to the numbers (avg. 3 executions): Simple select: 24,984 millis Join fetch: 1,219 millis The join fetch query execution time was 20 times faster(!) than the simple query. The reason is obvious, using the join fetch select I had only one round trip to the database. While using a simple select I had to fetch the customers (1 round trip to the database) and each time I accessed a collection I had another round trip (that's 18,998 additional round trips!). The winner is 'join fetch'. But does it? wait for the next one - the paging... Tuning Requirement #2 - Use Paging The second requirement was to do it in paging - each page will have 100 customers (so we will have 18,900/100+1 pages - the last page has 98 customers). So let's change the code above a little bit: Query q = entityManager.createQuery(queryStr); q.setFirstResult(pageNum*100).setMaxResults(100); long a = System.currentTimeMillis(); List l = q.getResultList(); for (Customer c : l) { c.getOrders().size(); } long b = System.currentTimeMillis(); System.out.println("Execution time: " + (b - a)+ "; Number of records fetch: " + l.size() ); I added the second line which limits the query result to a specific page with up to 100 records per page. And the numbers are (avg. 3 executions): Simple select: 328 millis Join fetch: 1,660 millis The wheel has turned over. Why? First a quote from the EJB3 Persistence specification: "The effect of applying setMaxResults or setFirstResult to a query involving fetch joins over collections is undefined" (section 3.6.1 - Query Interface) We could have stopped here but it is interesting to understand the issue and to see what Hibernate does. To implement the paging features Hibernate delegates the work to the database using its syntax to limit the number of records fetched by the query. Each database has its own proprietary syntax for limiting the number of fetched records, some examples: Postgres uses LIMIT and OFFSET Oracle has rownum MySQL uses its version of LIMIT and OFFSET MSSQL has the TOP keyword in the select and so on The important thing to remember here is meaning of such limit: the database returns a subset of the query result. So if we asked for the first 100 customers which their names contain 'Eyal' the outcome is logically the same as building a table in memory out of all customers that match the criteria and take from there the first 100 rows. And here is the catch: if the query with the limit includes a join clause for a collection than the first 100 row in the "logical table" will not necessarily be the first 100 customers. the outcome of the join might duplicate customers in the "logical tables" but the database doesn't aware or care about that - it performs operations on tables not on objects!. For example think of the extreme case, the customer 'Eyal' has 100 orders. The query will return 100 rows, hibernate will identify that all belong to the same customer and return only one Customer as the query result - this is not what we were asking for. This also works, of course, the other way around. If a customer had more than 100 orders and the result set size was limited to 100 rots the orders collection would not contain all of the customer's orders. To deal with that limitation Hibernate actually doesn't issue an SQL statement with a LIMIT clause. Instead it fetches all of the records and performs the paging in memory. This explains why using the 'join fetch' statement with paging took more than the one without paging - the delta is the in-memory paging done by Hibernate. If you look at Hibernate logs you will find the next warning issued by Hibernate: WARNING: firstResult/maxResults specified with collection fetch; applying in memory! Final Tuning - BatchSize Does it mean that in the case of paging we shouldn't use a join fetch? usually it does (unless your page size is very close to the actual number of records). But even if you use a simple select this is a classic case for using the @BatchSize annotation. If my session/entity manager has 100 customers attached to it than, be default, for each first access to one of the customers' order collection Hibernate will issue a SQL statement to fill that collection. At the end I will execute 100 statements to fetch 100 collections. You can see it in the log: Hibernate: /* select c from Customer c order by c.id */ select customer0_.id as id0_, customer0_.ccNumber as ccNumber0_, customer0_.name as name0_, customer0_.fixedDiscount as fixedDis5_0_, customer0_.DTYPE as DTYPE0_ from CUSTOMERS customer0_ order by customer0_.id limit ? offset ? Hibernate: /* load one-to-many par2.Customer.orders */ select orders0_.customer_id as customer4_1_, orders0_.id as id1_, orders0_.id as id1_0_, orders0_.customer_id as customer4_1_0_, orders0_.description as descript2_1_0_, orders0_.orderId as orderId1_0_ from ORDERS orders0_ where orders0_.customer_id=? Hibernate: /* load one-to-many par2.Customer.orders */ select orders0_.customer_id as customer4_1_, orders0_.id as id1_, orders0_.id as id1_0_, orders0_.customer_id as customer4_1_0_, orders0_.description as descript2_1_0_, orders0_.orderId as orderId1_0_ from ORDERS orders0_ where orders0_.customer_id=? Hibernate: /* load one-to-many par2.Customer.orders */ select orders0_.customer_id as customer4_1_, orders0_.id as id1_, orders0_.id as id1_0_, orders0_.customer_id as customer4_1_0_, orders0_.description as descript2_1_0_, orders0_.orderId as orderId1_0_ from ORDERS orders0_ where orders0_.customer_id=? ............ Hibernate: /* load one-to-many par2.Customer.orders */ select orders0_.customer_id as customer4_1_, orders0_.id as id1_, orders0_.id as id1_0_, orders0_.customer_id as customer4_1_0_, orders0_.description as descript2_1_0_, orders0_.orderId as orderId1_0_ from ORDERS orders0_ where orders0_.customer_id=? Hibernate: /* load one-to-many par2.Customer.orders */ select orders0_.customer_id as customer4_1_, orders0_.id as id1_, orders0_.id as id1_0_, orders0_.customer_id as customer4_1_0_, orders0_.description as descript2_1_0_, orders0_.orderId as orderId1_0_ from ORDERS orders0_ where orders0_.customer_id=? Hibernate: /* load one-to-many par2.Customer.orders */ select orders0_.customer_id as customer4_1_, orders0_.id as id1_, orders0_.id as id1_0_, orders0_.customer_id as customer4_1_0_, orders0_.description as descript2_1_0_, orders0_.orderId as orderId1_0_ from ORDERS orders0_ where orders0_.customer_id=? The @BatchSize annotation can be used to define how many identical associations to populate in a single database query. If the session has 100 customers attached to it and the mapping of the 'orders' collection is annotated with @BatchSize of size n. It means that whenever Hibernate needs to populate a lazy orders collection it checks the session and if it has more customers which their orders collections need to be populated it fetches up to n collections. Example: if we had 100 customers and the batch size was set to 16 when iterating over the customers to get their number of orders hibernate will go to the database only 7 times (6 times to fetch 16 collections and one more time to fetch the 4 remaining collections - see the sample below). If our batch size was set to 50 it would go only twice. @OneToMany(mappedBy="customer",cascade=CascadeType.ALL, fetch=FetchType.LAZY) @BatchSize(size=16) private Set orders = new HashSet(); And in the log: Hibernate: /* select c from Customer c order by c.id */ select customer0_.id as id0_, customer0_.ccNumber as ccNumber0_, customer0_.name as name0_, customer0_.fixedDiscount as fixedDis5_0_, customer0_.DTYPE as DTYPE0_ from CUSTOMERS customer0_ order by customer0_.id limit ? offset ? Hibernate: /* load one-to-many par2.Customer.orders */ select orders0_.customer_id as customer4_1_, orders0_.id as id1_, orders0_.id as id1_0_, orders0_.customer_id as customer4_1_0_, orders0_.description as descript2_1_0_, orders0_.orderId as orderId1_0_ from ORDERS orders0_ where orders0_.customer_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Hibernate: /* load one-to-many par2.Customer.orders */ select orders0_.customer_id as customer4_1_, orders0_.id as id1_, orders0_.id as id1_0_, orders0_.customer_id as customer4_1_0_, orders0_.description as descript2_1_0_, orders0_.orderId as orderId1_0_ from ORDERS orders0_ where orders0_.customer_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Hibernate: /* load one-to-many par2.Customer.orders */ select orders0_.customer_id as customer4_1_, orders0_.id as id1_, orders0_.id as id1_0_, orders0_.customer_id as customer4_1_0_, orders0_.description as descript2_1_0_, orders0_.orderId as orderId1_0_ from ORDERS orders0_ where orders0_.customer_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Hibernate: /* load one-to-many par2.Customer.orders */ select orders0_.customer_id as customer4_1_, orders0_.id as id1_, orders0_.id as id1_0_, orders0_.customer_id as customer4_1_0_, orders0_.description as descript2_1_0_, orders0_.orderId as orderId1_0_ from ORDERS orders0_ where orders0_.customer_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Hibernate: /* load one-to-many par2.Customer.orders */ select orders0_.customer_id as customer4_1_, orders0_.id as id1_, orders0_.id as id1_0_, orders0_.customer_id as customer4_1_0_, orders0_.description as descript2_1_0_, orders0_.orderId as orderId1_0_ from ORDERS orders0_ where orders0_.customer_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Hibernate: /* load one-to-many par2.Customer.orders */ select orders0_.customer_id as customer4_1_, orders0_.id as id1_, orders0_.id as id1_0_, orders0_.customer_id as customer4_1_0_, orders0_.description as descript2_1_0_, orders0_.orderId as orderId1_0_ from ORDERS orders0_ where orders0_.customer_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Hibernate: /* load one-to-many par2.Customer.orders */ select orders0_.customer_id as customer4_1_, orders0_.id as id1_, orders0_.id as id1_0_, orders0_.customer_id as customer4_1_0_, orders0_.description as descript2_1_0_, orders0_.orderId as orderId1_0_ from ORDERS orders0_ where orders0_.customer_id in (?, ?, ?, ?) Back to our test case. In my example setting the batch size to 100 looks like a nice tuning opportunity. And indeed when setting it to 100 the total execution time dropped to 188 millis (that's an 132 (!!!) times faster than worse result we had). The batch size can also be set globally by setting the hibernate.default_batch_fetch_size property for the session factory. From http://www.jroller.com/eyallupu/

Announcing the new Swing Tree Table yesterday, Tim Boudreau writes: Usage is incredibly easy - you just provide a standard Swing TreeModel of whatever sort you like, and an additional RowModel that can be queried for the other columns contents, editability and so forth. I found an example from some time ago, by Tim, and have been playing with it to get used to this new development. The result is as follows: To get started, I simply download the latest NetBeans IDE development build from netbeans.org and then attached the platform8/org-netbeans-swing-outline.jar to my Java SE project. For the rest, I wasn't required to do anything with NetBeans, necessarily. I could have attached the JAR to a project in Eclipse or anywhere else. Then I created a JFrame. To work with this Swing tree table, you need to provide the new "org.netbeans.swing.outline.Outline" class with the new "org.netbeans.swing.outline.OutlineModel" which, in turn, is built from a plain old javax.swing.tree.TreeModel, together with the new "org.netbeans.swing.outline.RowModel". Optionally, to change the default rendering, you can use the new "org.netbeans.swing.outline.RenderDataProvider". Let's first create a TreeModel for accessing files on disk. We will receive the root of the file system as a starting point: private static class FileTreeModel implements TreeModel { private File root; public FileTreeModel(File root) { this.root = root; } @Override public void addTreeModelListener(javax.swing.event.TreeModelListener l) { //do nothing } @Override public Object getChild(Object parent, int index) { File f = (File) parent; return f.listFiles()[index]; } @Override public int getChildCount(Object parent) { File f = (File) parent; if (!f.isDirectory()) { return 0; } else { return f.list().length; } } @Override public int getIndexOfChild(Object parent, Object child) { File par = (File) parent; File ch = (File) child; return Arrays.asList(par.listFiles()).indexOf(ch); } @Override public Object getRoot() { return root; } @Override public boolean isLeaf(Object node) { File f = (File) node; return !f.isDirectory(); } @Override public void removeTreeModelListener(javax.swing.event.TreeModelListener l) { //do nothing } @Override public void valueForPathChanged(javax.swing.tree.TreePath path, Object newValue) { //do nothing } } The above could simply be set as a JTree's model and then you'd have a plain old standard JTree. It would work, no problems, it would be a normal JTree. However, it wouldn't be a tree table since you'd only have a tree, without a table. Therefore, let's now add two extra columns, via the new "org.netbeans.swing.outline.RowModel" class, which will enable the creation of a tree table instead of a tree: private class FileRowModel implements RowModel { @Override public Class getColumnClass(int column) { switch (column) { case 0: return Date.class; case 1: return Long.class; default: assert false; } return null; } @Override public int getColumnCount() { return 2; } @Override public String getColumnName(int column) { return column == 0 ? "Date" : "Size"; } @Override public Object getValueFor(Object node, int column) { File f = (File) node; switch (column) { case 0: return new Date(f.lastModified()); case 1: return new Long(f.length()); default: assert false; } return null; } @Override public boolean isCellEditable(Object node, int column) { return false; } @Override public void setValueFor(Object node, int column, Object value) { //do nothing for now } } Now, after dragging-and-dropping an Outline object onto your JFrame (which is possible after adding the beans from the JAR to the NetBeans IDE Palette Manager) which, in turn, automatically creates a JScrollPane as well, this is how you could code the JFrame's constructor: public NewJFrame() { //Initialize the ui generated by the Matisse GUI Builder, which, //for example, adds the JScrollPane to the JFrame ContentPane: initComponents(); //Here I am assuming we are not on Windows, //otherwise use Utilities.isWindows() ? 1 : 0 //from the NetBeans Utilities API: TreeModel treeMdl = new FileTreeModel(File.listRoots()[0]); //Create the Outline's model, consisting of the TreeModel and the RowModel, //together with two optional values: a boolean for something or other, //and the display name for the first column: OutlineModel mdl = DefaultOutlineModel.createOutlineModel( treeMdl, new FileRowModel(), true, "File System"); //Initialize the Outline object: outline1 = new Outline(); //By default, the root is shown, while here that isn't necessary: outline1.setRootVisible(false); //Assign the model to the Outline object: outline1.setModel(mdl); //Add the Outline object to the JScrollPane: jScrollPane1.setViewportView(outline1); } Alternatively, without the NetBeans Matisse GUI Builder and NetBeans Palette Manager, i.e., simply using a standard Java class, you could do something like this: private Outline outline; public NewJFrame() { setDefaultCloseOperation(EXIT_ON_CLOSE); getContentPane().setLayout(new BorderLayout()); TreeModel treeMdl = new FileTreeModel(File.listRoots()[0]); OutlineModel mdl = DefaultOutlineModel.createOutlineModel( treeMdl, new FileRowModel(), true); outline = new Outline(); outline.setRootVisible(false); outline.setModel(mdl); getContentPane().add(new JScrollPane(outline),BorderLayout.CENTER); setBounds(20, 20, 700, 400); } At this point, you can run the JFrame, with this result: So, we see a lot of superfluous info that doesn't look very nice. Let's implement "org.netbeans.swing.outline.RenderDataProvider", as follows: private class RenderData implements RenderDataProvider { @Override public java.awt.Color getBackground(Object o) { return null; } @Override public String getDisplayName(Object o) { return ((File) o).getName(); } @Override public java.awt.Color getForeground(Object o) { File f = (File) o; if (!f.isDirectory() && !f.canWrite()) { return UIManager.getColor("controlShadow"); } return null; } @Override public javax.swing.Icon getIcon(Object o) { return null; } @Override public String getTooltipText(Object o) { File f = (File) o; return f.getAbsolutePath(); } @Override public boolean isHtmlDisplayName(Object o) { return false; } } Now, back in the constructor, add the renderer to the outline: outline1.setRenderDataProvider(new RenderData()); Run the JFrame again and the result should be the same as in the first screenshot above. Look again at the rendering code and note that, for example, you have tooltips:

The hardest part about learning Hbase (the open source implementation of Google's BigTable), is just wrapping your mind around the concept of what it actually is. I find it rather unfortunate that these two great systems contain the words table and base in their names, which tend to cause confusion among RDBMS indoctrinated individuals (like myself). This article aims to describe these distributed data storage systems from a conceptual standpoint. After reading it, you should be better able to make an educated decision regarding when you might want to use Hbase vs when you'd be better off with a "traditional" database. It's all in the terminology Fortunately, Google's BigTable Paper clearly explains what BigTable actually is. Here is the first sentence of the "Data Model" section: A Bigtable is a sparse, distributed, persistent multidimensional sorted map. Note: At this juncture I like to give readers the opportunity to collect any brain matter which may have left their skulls upon reading that last line. The BigTable paper continues, explaining that: The map is indexed by a row key, column key, and a timestamp; each value in the map is an uninterpreted array of bytes. Along those lines, the HbaseArchitecture page of the Hadoop wiki posits that: HBase uses a data model very similar to that of Bigtable. Users store data rows in labelled tables. A data row has a sortable key and an arbitrary number of columns. The table is stored sparsely, so that rows in the same table can have crazily-varying columns, if the user likes. Although all of that may seem rather cryptic, it makes sense once you break it down a word at a time. I like to discuss them in this sequence: map, persistent, distributed, sorted, multidimensional, and sparse. Rather than trying to picture a complete system all at once, I find it easier to build up a mental framework piecemeal, to ease into it... map At its core, Hbase/BigTable is a map. Depending on your programming language background, you may be more familiar with the terms associative array (PHP), dictionary (Python), Hash (Ruby), or Object (JavaScript). From the wikipedia article, a map is "an abstract data type composed of a collection of keys and a collection of values, where each key is associated with one value." Using JavaScript Object Notation, here's an example of a simple map where all the values are just strings: { "zzzzz" : "woot", "xyz" : "hello", "aaaab" : "world", "1" : "x", "aaaaa" : "y" } persistent Persistence merely means that the data you put in this special map "persists" after the program that created or accessed it is finished. This is no different in concept than any other kind of persistent storage such as a file on a filesystem. Moving along... distributed Hbase and BigTable are built upon distributed filesystems so that the underlying file storage can be spread out among an array of independent machines. Hbase sits atop either Hadoop's Distributed File System (HDFS) or Amazon's Simple Storage Service (S3), while a BigTable makes use of the Google File System (GFS). Data is replicated across a number of participating nodes in an analogous manner to how data is striped across discs in a RAID system. For the purpose of this article, we don't really care which distributed filesystem implementation is being used. The important thing to understand is that it is distributed, which provides a layer of protection against, say, a node within the cluster failing. sorted Unlike most map implementations, in Hbase/BigTable the key/value pairs are kept in strict alphabetical order. That is to say that the row for the key "aaaaa" should be right next to the row with key "aaaab" and very far from the row with key "zzzzz". Continuing our JSON example, the sorted version looks like this: { "1" : "x", "aaaaa" : "y", "aaaab" : "world", "xyz" : "hello", "zzzzz" : "woot" } Because these systems tend to be so huge and distributed, this sorting feature is actually very important. The spacial propinquity of rows with like keys ensures that when you must scan the table, the items of greatest interest to you are near each other. This is important when choosing a row key convention. For example, consider a table whose keys are domain names. It makes the most sense to list them in reverse notation (so "com.jimbojw.www" rather than "www.jimbojw.com") so that rows about a subdomain will be near the parent domain row. Continuing the domain example, the row for the domain "mail.jimbojw.com" would be right next to the row for "www.jimbojw.com" rather than say "mail.xyz.com" which would happen if the keys were regular domain notation. It's important to note that the term "sorted" when applied to Hbase/BigTable does not mean that "values" are sorted. There is no automatic indexing of anything other than the keys, just as it would be in a plain-old map implementation. multidimensional Up to this point, we haven't mentioned any concept of "columns", treating the "table" instead as a regular-old hash/map in concept. This is entirely intentional. The word "column" is another loaded word like "table" and "base" which carries the emotional baggage of years of RDBMS experience. Instead, I find it easier to think about this like a multidimensional map - a map of maps if you will. Adding one dimension to our running JSON example gives us this: { "1" : { "A" : "x", "B" : "z" }, "aaaaa" : { "A" : "y", "B" : "w" }, "aaaab" : { "A" : "world", "B" : "ocean" }, "xyz" : { "A" : "hello", "B" : "there" }, "zzzzz" : { "A" : "woot", "B" : "1337" } } In the above example, you'll notice now that each key points to a map with exactly two keys: "A" and "B". From here forward, we'll refer to the top-level key/map pair as a "row". Also, in BigTable/Hbase nomenclature, the "A" and "B" mappings would be called "Column Families". A table's column families are specified when the table is created, and are difficult or impossible to modify later. It can also be expensive to add new column families, so it's a good idea to specify all the ones you'll need up front. Fortunately, a column family may have any number of columns, denoted by a column "qualifier" or "label". Here's a subset of our JSON example again, this time with the column qualifier dimension built in: { // ... "aaaaa" : { "A" : { "foo" : "y", "bar" : "d" }, "B" : { "" : "w" } }, "aaaab" : { "A" : { "foo" : "world", "bar" : "domination" }, "B" : { "" : "ocean" } }, // ... } Notice that in the two rows shown, the "A" column family has two columns: "foo" and "bar", and the "B" column family has just one column whose qualifier is the empty string (""). When asking Hbase/BigTable for data, you must provide the full column name in the form ":". So for example, both rows in the above example have three columns: "A:foo", "A:bar" and "B:". Note that although the column families are static, the columns themselves are not. Consider this expanded row: { // ... "zzzzz" : { "A" : { "catch_phrase" : "woot", } } } In this case, the "zzzzz" row has exactly one column, "A:catch_phrase". Because each row may have any number of different columns, there's no built-in way to query for a list of all columns in all rows. To get that information, you'd have to do a full table scan. You can however query for a list of all column families since these are immutable (more-or-less). The final dimension represented in Hbase/BigTable is time. All data is versioned either using an integer timestamp (seconds since the epoch), or another integer of your choice. The client may specify the timestamp when inserting data. Consider this updated example utilizing arbitrary integral timestamps: { // ... "aaaaa" : { "A" : { "foo" : { 15 : "y", 4 : "m" }, "bar" : { 15 : "d", } }, "B" : { "" : { 6 : "w" 3 : "o" 1 : "w" } } }, // ... } Each column family may have its own rules regarding how many versions of a given cell to keep (a cell is identified by its rowkey/column pair) In most cases, applications will simply ask for a given cell's data, without specifying a timestamp. In that common case, Hbase/BigTable will return the most recent version (the one with the highest timestamp) since it stores these in reverse chronological order. If an application asks for a given row at a given timestamp, Hbase will return cell data where the timestamp is less than or equal to the one provided. Using our imaginary Hbase table, querying for the row/column of "aaaaa"/"A:foo" will return "y" while querying for the row/column/timestamp of "aaaaa"/"A:foo"/10 will return "m". Querying for a row/column/timestamp of "aaaaa"/"A:foo"/2 will return a null result. sparse The last keyword is sparse. As already mentioned, a given row can have any number of columns in each column family, or none at all. The other type of sparseness is row-based gaps, which merely means that there may be gaps between keys. This, of course, makes perfect sense if you've been thinking about Hbase/BigTable in the map-based terms of this article rather than perceived similar concepts in RDBMS's. And that's about it Well, I hope that helps you understand conceptually what the Hbase data model feels like. As always, I look forward to your thoughts, comments and suggestions.

The star schema represents data as a table of facts (measurable values) that are associated with the various dimensions of the fact. Common dimensions include time, geography, organization, product and the like. I'm working with some folks whose facts are a bunch of medical test results, and the dimensions are patient, date, and a facility in which the tests were performed. I got an email with the following situation: "a client who is processing gigs of incoming fact data each day and they use a host of C/C++, Perl, mainframe and other tools for their incoming fact processing and I've seriously considered pushing Python in their organization.". Here are my thoughts on using Python for data warehousing when you've got Gb of data daily. Small Dimensions The pure Python approach only works when your dimension will comfortably fit into memory -- not a terribly big problem with most dimensions. Specifically, it doesn't work well for those dimensions which are so huge that the dimensional model becomes a snowflake instead of a simple star. When dealing with a large number of individuals (public utilities, banks, medical management, etc.) the "customer" (or "patient") dimension gets too big to fit into memory. Special bridge-table techniques must be used. I don't think Python would be perfect for this, since this involves slogging through a lot of data one record at a time. However, Python is considerably faster than PL/SQL. I don't know how it compares with Perl. Any programming language will be faster than any SQL procedure, because there's no RDBMS overhead. For all small dimensions. Load the dimension values from the RDBMS into a dict with a single query. Read all source data records (ideally from a flat file); conform the dimension, tracking changes; write a result record with the dimension FK information to a flat file. Iterate through the dimension dictionary and persist the dimension changes. The details vary with the Slowly Changing Dimension (SCD) rules you're using. The conformance algorithm is is essentially the following: row= Dimension(...) ident= ( row.field, row.field, row.field, ... ) dimension.setdefault( ident, row ) In some cases (like the Django ORM) this is called the get-or-create query. The Dimension Bus For BIG dimensions, I think you still have to implement the "dimension bus" outlined in The Data Warehouse Toolkit. To do this in Python, you should probably design things to look something like the following. For any big dimensions. Use an external sort-merge utility. Seriously. They're way fast for data sets too large to fit into memory. Use CSV format files and the resulting program is very tidy. The outline is as follows: First, sort the source data file into order by the identifying fields of the big dimension (customer number, patient number, whatever). Second, query the big dimension into a data file and sort it into the same order as the source file. (Using the SQL ORDER BY may be slower than an external sort; only measurements can tell which is faster.) Third, do a "match merge" to locate the differences between the dimension and the source. Don't use a utility like diff, it's too slow. This is a simple key matching between two files. The match-merge loop looks something like this. src= sourceFile.next() dim= dimensionFile.next() try: while True: src_key = ( src['field'], src['field'], ... ) dim_key= ( dim['field'], dim['field'], ... ) if src_key < dim_key: # missing some dimension values update_dimension( src ) src= sourceFile.next() elif dim_key < src_key: # extra dimension values dim= dimensionFile.next() else: # src and dim keys match # check non-key attributes for dimension change. src= sourceFile.next() except StopIteration, e: # if source is at end-of-file, that's good, we're done. # if dim is at end of file, all remaining src rows are dimension updates. for src in sourceFile: update_dimension( src ) At the end of this pass, you'll accumulate a file of customer dimension adds and changes, which is then persisted into the actual customer dimension in the database. This pass will also write new source records with the customer FK. You can also handle demographic or bridge tables at this time, too. Fact Loading The first step in DW loading is dimensional conformance. With a little cleverness the above processing can all be done in parallel, hogging a lot of CPU time. To do this in parallel, each conformance algorithm forms part of a large OS-level pipeline. The source file must be reformatted to leave empty columns for each dimension's FK reference. Each conformance process reads in the source file and writes out the same format file with one dimension FK filled in. If all of these conformance algorithms form a simple OS pipe, they all run in parallel. It looks something like this. src2cvs source | conform1 | conform2 | conform3 | load At the end, you use the RDBMS's bulk loader (or write your own in Python, it's easy) to pick the actual fact values and the dimension FK's out of the source records that are fully populated with all dimension FK's and load these into the fact table. I've written conformance processing in Java (which is faster than Python) and had to give up on SQL-based conformance for large dimensions. Instead, we did the above flat-file algorithm to merge large dimensions. The killer isn't the language speed, it's the RDBMS overheads. Once you're out of the database, things blaze. Indeed, products like the syncsort data sort can do portions of the dimension conformance at amazing speeds for large datasets. Hand Wringing "But," the hand-wringers say, "aren't you defeating the value of the RDBMS by working outside it?" The answer is NO. We're not doing incremental, transactional processing here. There aren't multiple update transactions in a warehouse. There are queries and there are bulk loads. Doing the prep-work for a bulk load outside the database is simply more efficient. We don't need locks, rollback segments, memory management, threading, concurrency, ACID rules or anything. We just need to match-merge the large dimension and the incoming facts.

WSDL is a version of XML used to better work with web severs. In this post, we'll learn how to better use it alongside the Java language.

Migrate4j is a migration tool for java, similar to Ruby's db:migrate task. Unlike other Java based migration tools, database schema changes are defined in Java, not SQL. This means your migrations can be applied to different database engines without worrying about whether your DDL statements will still work. Schema changes are defined in Migration classes, which define "up" and "down" methods - "up" is called when a Migration is being applied, while "down" is called when it is being rolled back. A simple Migration, which simply adds a table to a database, is written as: package db.migrations; import static com.eroi.migrate.Define.*; import static com.eroi.migrate.Define.DataTypes.*; import static com.eroi.migrate.Execute.*; import com.eroi.migrate.Migration; public class Migration_1 implements Migration { public void up() { createTable( table("simple_table", column("id", INTEGER, primaryKey(), notnull()), column("desc", VARCHAR, length(50), defaultValue("NA")))); } public void down() { dropTable("simple_table"); } } This Migration can be applied at application startup, from an Ant task (included in migrate4j) or from the command line. Migrate4j will only apply the migration if it has not yet been applied. LIkewise, migrate4j will roll back the migration when instructed, only if the migration has been previously applied. The migrate4j team is happy to announce a new release which adds improved usability (simplified syntax), additional schema changes and support for more database products. While migrate4j does not yet have support for all database products, we are actively seeking developers interested in helping fix this situation. Visit http://migrate4j.sourceforge.net for more information on how migrate4j can simplify synchronizing your databases. To obtain migrate4j, go to http://sourceforge.net/projects/migrate4j and download the latest release. For questions or to help with future development of migrate4j, email us at migrate4j-users AT lists.sourceforge.net (replacing the AT with the "at symbol").

Formerly called ACEGI Security for Spring, the re-branded Spring Security 2.0 has delivered on its promises of making it simpler to use and improving developer productivity. Already considered as the Java platform's most widely used enterprise security framework with over 250,000 downloads from SourceForge, Spring Security 2.0 provides a host of new features. This article outlines how to convert your existing ACEGI based Spring application to use Spring Security 2.0. What is Spring Security 2.0 Spring Security 2.0 has recently been released as a replacement to ACEGI and it provides a host of new security features: Substantially simplified configuration. OpenID integration, single sign on standard. Windows NTLM support, single sign on against Windows corporate networks. Support for JSR 250 ("EJB 3") security annotations. AspectJ pointcut expression language support. Comprehensive support for RESTful web request authorization. Long-requested support for groups, hierarchical roles and a user management API. An improved, database-backed "remember me" implementation. New support for web state and flow transition authorization through the Spring Web Flow 2.0 release. Enhanced WSS (formerly WS-Security) support through the Spring Web Services 1.5 release. A whole lot more... Goal Currently I work on a Spring web application that uses ACEGI to control access to the secure resources. Users are stored in a database and as such we have configured ACEGI to use a JDBC based UserDetails Service. Likewise, all of our web resources are stored in the database and ACEGI is configure to use a custom AbstractFilterInvocationDefinitionSource to check authorization details for each request. With the release of Spring Security 2.0 I would like to see if I can replace ACEGI and keep the current ability to use the database as our source of authentication and authorization instead of the XML configuration files (as most examples demonstrate). Here are the steps that I took... Steps The first (and trickiest) step was to download the new Spring Security 2.0 Framework and make sure that the jar files are deployed to the correct location. (/WEB-INF/lib/) There are 22 jar files that come with the Spring Security 2.0 download. I did not need to use all of them (especially not the *sources packages). For this exercise I only had to include: spring-security-acl-2.0.0.jar spring-security-core-2.0.0.jar spring-security-core-tiger-2.0.0.jar spring-security-taglibs-2.0.0.jar Configure a DelegatingFilterProxy in the web.xml file. springSecurityFilterChain org.springframework.web.filter.DelegatingFilterProxy springSecurityFilterChain /* Configuration of Spring Security 2.0 is far more concise than ACEGI, so instead of changing my current ACEGI based configuration file, I found it easier to start from a empty file. If you do want to change your existing configuration file, I am sure that you will be deleting more lines than adding. The first part of the configuration is to specifiy the details for the secure resource filter, this is to allow secure resources to be read from the database and not from the actual configuration file. This is an example of what you will see in most of the examples: Replace this with: The main part of this piece of configuration is the secureResourceFilter, this is a class that implements FilterInvocationDefinitionSource and is called when Spring Security needs to check the Authorities for a requested page. Here is the code for MySecureResourceFilter: package org.security.SecureFilter; import java.util.Collection; import java.util.List; import org.springframework.security.ConfigAttributeDefinition; import org.springframework.security.ConfigAttributeEditor; import org.springframework.security.intercept.web.FilterInvocation; import org.springframework.security.intercept.web.FilterInvocationDefinitionSource; public class MySecureResourceFilter implements FilterInvocationDefinitionSource { public ConfigAttributeDefinition getAttributes(Object filter) throws IllegalArgumentException { FilterInvocation filterInvocation = (FilterInvocation) filter; String url = filterInvocation.getRequestUrl(); // create a resource object that represents this Url object Resource resource = new Resource(url); if (resource == null) return null; else{ ConfigAttributeEditor configAttrEditor = new ConfigAttributeEditor(); // get the Roles that can access this Url List roles = resource.getRoles(); StringBuffer rolesList = new StringBuffer(); for (Role role : roles){ rolesList.append(role.getName()); rolesList.append(","); } // don't want to end with a "," so remove the last "," if (rolesList.length() > 0) rolesList.replace(rolesList.length()-1, rolesList.length()+1, ""); configAttrEditor.setAsText(rolesList.toString()); return (ConfigAttributeDefinition) configAttrEditor.getValue(); } } public Collection getConfigAttributeDefinitions() { return null; } public boolean supports(Class arg0) { return true; } } This getAttributes() method above essentially returns the name of Authorities (which I call Roles) that are allowed access to the current Url. OK, so now we have setup the database based resources and now the next step is to get Spring Security to read the user details from the database. The examples that come with Spring Security 2.0 shows you how to keep a list of users and authorities in the configuration file like this: You could replace these examples with this configuration so that you can read the user details straight from the database like this: While this is a very fast and easy way to configure database based security it does mean that you have to conform to a default databases schema. By default, the requires the following tables: user, authorities, groups, group_members and group_authorities. In my case this was not going to work as my security schema it not the same as what the requires, so I was forced to change the : By adding the users-by-username-query and authorities-by-username-query properties you are able to override the default SQL statements with your own. As in ACEGI security you must make sure that the columns that your SQL statement returns is the same as what Spring Security expects. There is a another property group-authorities-by-username-query which I am not using and have therefore left it out of this example, but it works in exactly the same manner as the other two SQL statements. This feature of the has only been included in the past month or so and was not available in the pre-release versions of Spring Security. Luckily it has been added as it does make life a lot easier. You can read about this here and here. The dataSource bean instructs which database to connect to, it is not included in my configuration file as it's not specific to security. Here is an example of a dataSource bean for those who are not sure: And that is all for the configuration of Spring Security. My last task was to change my current logon screen. In ACEGI you could create your own logon by making sure that you POSTED the correctly named HTML input elements to the correct URL. While you can still do this in Spring Security 2.0, some of the names have changed. You can still call your username field j_username and your password field j_password as before. However you must set the action property of your to point to j_spring_security_check and not j_acegi_security_check. Logout Conclusion This short guide on how to configure Spring Security 2.0 with access to resources stored in a database does not come close to illustrating the host of new features that are available in Spring Security 2.0, however I think that it does show some of the most commonly used abilities of the framework and I hope that you will find it useful. One of the benefits of Spring Security 2.0 over ACEGI is the ability to write more consice configuration files, this is clearly shown when I compare my old ACEGI configration (172 lines) file to my new one (42 lines). Here is my complete securityContext.xml file: As I said in step 1, downloading Spring Security was the trickiest step of all. From there on it was plain sailing...

If you have root access to your MySQL database then you can simply run a query on the database to resolve the problem.

Today's release of DJ Native Swing 0.9.4 greatly improves stability and brings new components: in addition to the JWebBrowser and JFlashPlayer, there is now the JVLCPlayer and the JHTMLEditor. Here is a summary of what to expect when using this library. 1. Various native components DJ Native Swing was designed to handle all the complexity of native integration, mainly in the form of components with a simple Swing-like API. Here are some screenshots of several components in action (click to enlarge): JWebBrowser: JFlashPlayer: JVLCPlayer: JHTMLEditor: 2. Simple API First, we need to initialize the framework. This needs to happen before any feature is used. A common place for this call is the first line of the main(): public static void main(String[] args) { NativeInterfaceHandler.init(); // Here goes the rest of the initialization. } Now, let's see how to create a JWebBrowser, with its URL set to Google's homepage: JWebBrowser webBrowser = new JWebBrowser(); webBrowser.setURL("http://www.google.com"); myContentPane.add(webBrowser); Note that we set a URL, but we could as well set the HTML text. The JWebBrowser also allows to execute Javascript calls, and we can even propagate notifications from custom pages to our Swing application. Moving to a more practical example, we may want to be notified of URL change events or track window opening events, potentially preventing navigation to occur or open the page elsewhere. This is easily achieved by attaching a listener: webBrowser.addWebBrowserListener(new WebBrowserAdapter() { public void urlChanging(WebBrowserNavigationEvent e) { String newURL = e.getNewURL(); if(newURL.startsWith("http://www.microsoft.com/")) { // Prevent the navigation to happen. e.consume(); } else { // We can consume the event and decide to open this page in a tab. } } public void windowWillOpen(WebBrowserWindowWillOpenEvent e) { // We can prevent, add the URL to a tab, etc. } // There are of course more events that can be received. }); Let's have a look at the JFlashPlayer: JFlashPlayer flashPlayer = new JFlashPlayer(); flashPlayer.setURL(myFlashURL); The JFlashPlayer can open local or remote Flash files, and files from the classpath; the latter being a general capability of the library by proxying files using a minimalistic web server. Of course, the JFlashPlayer allows to retrieve and set Flash variables, and play/pause/stop the execution. The JVLCPlayer and the JHTMLEditor are no exceptions to this simplicity: playlist can be manipulated in the VLC player, HTML can be set and retrieved from the HTML editor, etc. There is also the possibility to integrate Ole controls on Windows, still with a simple Swing-like API. An example is provided in the library in the form of an embedded Windows Media Player. 3. Advanced capabilities The library takes care of most common integration issues. This covers modal dialog handling, Z-ordering, heavyweight/lightweight mix (to a certain extent), invisible native components with regards to focus handling and threading. Here are some more screenshots, showing a lightweight/heavyweight mix, and Z-ordering capability: The demo application that is part of the distribution shows all the features along with the source code, so check it out! 4. Project info and technical notes Webstart demo: http://djproject.sourceforge.net/ns/DJNativeSwingDemo.jnlp Screenshots: http://djproject.sourceforge.net/ns/screenshots Native Swing: http://djproject.sourceforge.net/ns The DJ Project: http://djproject.sourceforge.net The 0.9.4 version has a completely new architecture. It still uses SWT under the hood, but it does not use the SWT_AWT bridge anymore. The JWebBrowser and browser-based components require XULRunner to be installed, except on Windows when using Internet Explorer. The JVLCPlayer requires VLC to be installed. The JHTMLEditor uses the FCKeditor. 5. Conclusions This project finally brings all that is needed to make Java on the desktop a reality. A web browser, a flash player, a multimedia player, and even an HTML editor. So, what next? Yes, what next? For that one, I am waiting for your feedback. So, what do you think? What are your comments and suggestions? -Christopher

SVNKitis an Open Source pure Java Subversion library. SVNKit literally brings Subversion, popular open source version control system, to the Java world. With SVNKit you can do the following: All standard Subversion operations: For instance, the following snipped checks out project from repository: File dstPath = new File("c:/svnkit"); SVNURL url = SVNURL. parseURIEncoded("http://svn.svnkit.com/repos/svnkit/branches/1.1.x/"); SVNClientManager cm = SVNClientManager.newInstance(); SVNUpdateClient uc = cm.getUpdateClient(); uc.doCheckout(url, dstPath, SVNRevision.UNDEFINED, SVNRevision.HEAD, true); Updates it to the latest revision: uc.doUpdate(dstPath, SVNRevision.HEAD, true); And finally commits local changes in "www" subdirectory if there are any: SVNCommitClient cc = cm.getCommitClient(); cc.doCommit(new File[] {new File(dstPath, "www")}, false, "message", false, true); SVNKit supports all standard Subversion operations and compatible with the latest version of Subversion. Access Subversion repository directly: Some applications will benefit from working with repository directly, without keeping working copy locally. Example below displays list of files in "www" directory. SVNURL url = SVNURL.parseURIEncoded("http://svn.svnkit.com/repos/svnkit/branches/1.1.x/"); SVNRepository repos = SVNRepositoryFactory.create(url); long headRevision = repos.getLatestRevision(); Collection entriesList = repos.getDir("www", headRevision, null, (Collection) null); for (Iterator entries = entriesList.iterator(); entries.hasNext();) { SVNDirEntry entry = (SVNDirEntry) entries.next(); System.out.println("entry: " + entry.getName()); System.out.println("last modified at revision: " + entry.getDate() + " by " + entry.getAuthor()); } Direct repository access API allows to perform operations like update, commit, diff and many other. Additionaly to the performance benefits of the direct access to repository, this API makes it possible to version arbitrary objects or object models within Subevrsion repository, not only files from the file system. Replace JNI Subversion bindings with SVNKit: Native Subversion provides Java interface that works with Subversion binaries through JNI. In case you already using it or would like to use as an option, you may also use SVNKit through exactly the same interface. This way you'll let your application dynamically switch between JNI and SVNKit implementation of the same API or let your application work on the platforms where there are no native Subversion binaries. For example: // pure Java implementation of the standard Subversion Java interface SVNClientInterface jniAPI = SVNClientImpl.newInstance(); byte[] contents = jniAPI.fileContent("http://svn.svnkit.com/repos/svnkit/branches/1.1.x/changelog.txt", Revision.HEAD); SVNKit is widely used in different applications, including IntelliJ IDEA, Eclipse Subversion integrations, SmartSVN, JDeveloper, bug tracking server side applications (e.g. Atlassian JIRA) and repository management and tracking tools (e.g. Atlassian FishEye) and many others. Where to get more information: Recently we've released SVNKit version 1.1.6 which is bugfix release. At http://svnkit.com/ you will find more information on that new version and, of course, downloads, documentation, source code example and articles explaining how to use SVNKit. In case of any questions you're welcome at our mailing list, or just contact us at [email protected] SVNKit is widely used in different applications, including IntelliJ IDEA, Eclipse Subversion integrations, SmartSVN, JDeveloper, bug tracking server side applications (e.g. Atlassian JIRA) and repository management and tracking tools (e.g. Atlassian FishEye) and many others. With best regards, TMate Software, http://svnkit.com/ - Java [Sub]Versioning Library!

As much as I read articles online, I still print a fair amount of them out. Sometimes I print them to pass on to others, other times to read again when I have more time. Unfortunately a great deal of websites put no effort into providing their content in a printer-friendly fashion. The result of them overlooking the print audience is a great article not being read. If only these writers knew how easy it can be to optimize their site for print and how it can greatly enhance the value of their website. The secret to creating printable pages is being able to identify and control the "content area(s)" of your website. Most websites are composed of a header, footer, sidebars/subnavigation, and one main content area. Control the content area and most of your work is done. The following are my tips to conquering the print media without changing the integrity of your website. Create A Stylesheet For Print Of course you have at least one stylesheet to control the layout of the page and formatting of the content, but do you have a stylesheet to control how your page will look like in print? Add the print style sheet, with the media attribute set to "print", at the end of the list of stylesheets in the header. This will allow you to create custom CSS classes applied only at the time of print. Make sure your structure CSS file is given a media attribute of "all." Avoid Unnecessary HTML Tables As much as I try to steer clear of using tables, there's no way to avoid the occasional experience. Forms are much easier to code when using tables. Tables are also great for...get this...data tables. Other than these two situations, a programmer should try to avoid using table, especially when considering print. Controlling the content area of your website can be extremely challenging when the page structure is trapped in a table. Know Which Portions Of The Page Don't Have Any Print Value You know that awesome banner you have at the top of your site? Ditch it. And those ads on the right and left sides of the page? Goodbye. Web visitors print your page because of the content on it, not to see the supporting images on your website. Create a class called "no-print" and add that class declaration to DIVS, images, and other elements that have no print value: .no-print { display:none; } .... Use Page Breaks Page breaks in the browser aren't as reliable as they are in Microsoft Word, especially considering the variable content lengths on dynamically created pages, but when utilized well make all the different in printing your website. The CSS specs don't provide a lot of print flexibility but the "page-break-before" / "page-break-after" properties prove to be useful. Page breaks are much more reliable when used with DIV elements instead of table cells. .page-break { page-break-before: always; } /* put this class into your main.css file with "display:none;" */ Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Fusce eu felis. Curabitur sit amet magna. Nullam aliquet. Aliquam ut diam... Lorem ipsum dolor sit amet, consectetuer adipiscing elit.... Size Your Page For Print Obviously your computer monitor can provide a large amount of width to view a page, but I recommend setting the content area width to 600px (an inch equivalent may be better, but I try to deal with one unit specifically, which is pixels). This ensures that words wont bleed outside the print area. Use this width measurement with the page break DIVs you've created in your stylesheet. After you know the width of your printed content area, adjust the dimensions of content blocks inside the main content area if necessary. Test! Like any type of programming, testing is important. Note that if you have a website that serves dynamic data, you wont be able to win all the time but you may be able to figure a scheme to format content well most of the time. Be sure to test in multiple browsers (when creating customer websites, I try to check all "Grade A" browsers). Modifying your page structure for better print results is probably easier than you think -- at least improving your existing template will be. Check back soon for part two, where we analyze optimizing a website's content for print.

SQL Server doesn't always adhere to its date/time formatting. Here's how to create your own.

DOM ISO.v.0.3.0.7.bookmarklet.js bookmarklet for selecting and isolating an element on a page. two sections: section 1: Mouseover DOM, setup and handle mouse events and show information about element in informational div. Click to select, Any key to cancel. section 2: Element Isolation with help of XPath. prompt user for XPath expression e.g., //DIV[@id='post-body']. then use XPath to select all elements not(ancestor or descendant or self), then delete those elements. also ignore self-or-descendants of head and title. tools: Ruderman's javascript development environment: https://www.squarefree.com/bookmarklets/webdevel.html#jsenv Mielczarek's js to bookmarklet generator: http://ted.mielczarek.org/code/mozilla/bookmarklet.html (function() { //GLOBALS //globals for classMausWork var gSelectedElement; //currently only one selection var gHoverElement; //whatever element the mouse is over var gHovering=false; //mouse is over something var gObjArrMW=[]; //global array of classMausWork objects. for removing event listeners when done selecting. //extended var infoDiv; //currently just container for InfoDivHover, might add more here var infoDivHover; //container for hoverText text node. var hoverText; //show information about current element that the mouse is over //const EXPERIMENTAL_NEW_CODE=true; //debugging. new features. //START SetupDOMSelection(); //(Section 1) Element Selection function SetupDOMSelection() { { //setup event listeners //var pathx="//div | //span | //table | //td | //tr | //ul | //ol | //li | //p"; var pathx="//div | //span | //table | //th | //td | //tr | //ul | //ol | //li | //p | //iframe"; var selection=$XPathSelect(pathx); for(var element, i=0;element=selection(i);i++) { if(element.tagName.match(/^(div|span|table|td|tr|ul|ol|li|p)$/i)) //redundant check. { var m = new classMausWork(element); gObjArrMW.push(m); attachMouseEventListeners(m); } } document.body.addEventListener('mousedown',MiscEvent,false); document.body.addEventListener('mouseover',MiscEvent,false); document.body.addEventListener('mouseout',MiscEvent,false); document.addEventListener('keypress',MiscEvent,false); } { //setup informational div to show which element the mouse is over. infoDiv=document.createElement('div'); var s=infoDiv.style; s.position='fixed'; s.top='0'; s.right='0'; s.display='block'; s.width='auto'; s.padding='0px'; document.body.appendChild(infoDiv); infoDivHover=document.createElement('div'); s=infoDivHover.style; s.fontWeight='bold'; s.padding='3px'; s.Opacity='0.8'; s.borderWidth='thin'; s.borderStyle='solid'; s.borderColor='white'; s.backgroundColor='black'; s.color='white'; infoDiv.appendChild(infoDivHover); hoverText=document.createTextNode('selecting'); infoDivHover.appendChild(hoverText); } } function CleanupDOMSelection() { for(var m; m=gObjArrMW.pop(); ) { detachMouseEventListeners(m); } ElementRemove(infoDiv); document.body.removeEventListener('mousedown',MiscEvent,false); document.body.removeEventListener('mouseover',MiscEvent,false); document.body.removeEventListener('mouseout',MiscEvent,false); document.removeEventListener('keypress',MiscEvent,false); } function attachMouseEventListeners(c) { //c is object of class classMausWork c.element.addEventListener("mouseover",c.mouse_over,false); c.element.addEventListener("mouseout",c.mouse_out,false); c.element.addEventListener("mousedown",c.mouse_click,false); } function detachMouseEventListeners(c) { //c is object of class classMausWork c.resetElementStyle(); c.element.removeEventListener("mouseover",c.mouse_over,false); c.element.removeEventListener("mouseout",c.mouse_out,false); c.element.removeEventListener("mousedown",c.mouse_click,false); } //mouse event handling class for element, el. function classMausWork(element) { //store information about the element this object is assigned to handle. element, original style, etc. this.element=element; var elementStyle=element.getAttribute('style'); var target; this.mouse_over=function(ev) { if(gHovering)return; var e=element; var s=e.style; s.backgroundColor='yellow'; s.borderWidth='thin'; s.borderColor='lime'; s.borderStyle='solid'; InfoMSG(ElementInfo(e),'yellow','blue','yellow'); gHoverElement=e; gHovering=true; target=ev.target; ev.stopPropagation(); }; this.mouse_out=function(ev) { if(!gHovering)return; if(gHoverElement!=element ||ev.target!=target)return; var e=element; e.setAttribute('style',elementStyle); InfoMSG('-','white','black','white'); gHoverElement=null; gHovering=false; target=null; //ev.stopPropagation(); }; this.mouse_click=function(ev) { if(!gHovering)return; if(gHoverElement!=element ||ev.target!=target)return; var e=element; e.setAttribute('style',elementStyle); ev.stopPropagation(); CleanupDOMSelection(); gHoverElement=null; gHovering=false; target=null; if(ev.button==0) { gSelectedElement=e; ElementSelected(e); //finished selecting, cleanup then move to next part (section 2), element isolation. } }; this.resetElementStyle=function() { element.setAttribute('style',elementStyle); }; } function MiscEvent(ev) //keypress, and mouseover/mouseout/mousedown event on body. cancel selecting. { if(ev.type=='mouseout' && !gHovering) { InfoMSG('-','white','black','white'); } else if(ev.type=='mouseover' && !gHovering) { InfoMSG('cancel','yellow','red','yellow'); } else //keypress on document or mousedown on body, cancel ops. { CleanupDOMSelection(); } } function InfoMSG(text,color,bgcolor,border) { var s=infoDivHover.style; if(color)s.color=color; if(bgcolor)s.backgroundColor=bgcolor; if(border)s.borderColor=border; if(text)hoverText.data=text; } //(Section 2) Element Isolation function ElementSelected(element) //finished selecting element. setup string to prompt user. { PromptUserXpath(ElementInfo(element)); } function PromptUserXpath(defaultpath) //prompt user, isolate element. { var userpath = prompt("XPath of elements to isolate : ", defaultpath); if(userpath && userpath.length>0) { var addPredicate = "[count(./ancestor-or-self::head)=0][count(./ancestor-or-self::title)=0]"; //exclude head & title elements from selection so they aren't removed var addPath = "//script | //form | //object | //embed"; //include these elements in selection for removal var pathx=TransformXPath_NoAncestorDescendentSelf(userpath, addPredicate, addPath); //the xpath selection of all elements to be removed/deleted. try { var element; var elements=$XPathSelect(pathx); for(var i=0;element=elements(i);i++) { if(!element.nodeName.match(/^(head|title)$/i)) //redundant check. { ElementRemove(element); } } } catch(err) { alert("wtf: "+err); } } } //support function $XPathSelect(p, context) { if (!context) context = document; var i, arr = [], xpr = document.evaluate(p, context, null, XPathResult.UNORDERED_NODE_SNAPSHOT_TYPE, null); return function(x) { return xpr.snapshotItem(x); }; //closure. wooot! returns function-type array of elements (usually elements, or something else depending on the xpath expression). } function ElementRemove(e) { if(e)e.parentNode.removeChild(e); } function ElementInfo(element) { var txt=''; if(element) { txt=element.tagName.toLowerCase(); //txt=element.tagName; txt=attrib(txt,element,'id'); txt=attrib(txt,element,'class'); txt='//'+txt; } return txt; function attrib(t,e,a) { if(e.hasAttribute(a)) { t+="[@"+a+"='"+e.getAttribute(a)+"']"; } return t; } } //function to 'invert' the XPath by selecting all elements that are not ancestor and not descendent and not self. function TransformXPath_NoAncestorDescendentSelf(u, includePredicates, includePaths) { //sample input (u): //div[@class='sortbox'] //sample output //*[ not(./descendant-or-self::*=//div[@class='sortbox'])][ not(./ancestor-or-self::*=//div[@class='sortbox'])] //sample output with additional conditions: //*[ not(./descendant-or-self::*=//div[@class='sortbox'])][ not(./ancestor-or-self::*=//div[@class='sortbox'])][count(./ancestor-or-self::head)=0][count(./ancestor-or-self::title)=0] //obsolete method. much faster but can only be used for limited types of (simple) xpath expressions -- unlike the current version, which should be able to convert any xpath. //input: table[@id='topbar'] //output: //*[not(./descendant-or-self::table[@id='topbar']) and not(./ancestor-or-self::table[@id='topbar'])] //output (alternative): //*[count(./descendant-or-self::table[@id='topbar'])=0 and count(./ancestor-or-self::table[@id='topbar'])=0] var o1= './descendant-or-self::*='+gr(u); o1= 'not' + gr(o1); o1= nt(o1); var o2= './ancestor-or-self::*='+gr(u); o2= 'not' + gr(o2); o2= nt(o2); var o= '//*'+o1+o2; if(includePredicates && includePredicates.length>0) o += includePredicates; if(includePaths && includePaths.length>0) o += ' | ' + includePaths; return o; function nt(term){return wrap(term,'[]');} //node test; predicate - enclose with bracket. function gr(term){return wrap(term,'()');} //group - parenthesize. function wrap(term, enclosure){return enclosure.charAt(0)+term+enclosure.charAt(1);} } })();

PHP // Resolves a TinyURL.com encoded URL to its source. // Example: reverse_tinyurl('http://tinyurl.com/2ocfun') => "http://logankoester.com" function reverse_tinyurl($url) { $url = explode('.com/', $url); $url = 'http://preview.tinyurl.com/' . $url[1]; $preview = file_get_contents($url); preg_match('/redirecturl" href="(.*)">/', $preview, $matches); return $matches[1]; }

For more details on how to call this script from php if your server doesn't support python, click http://just-tech.blogspot.com/2007/01/python-html-tables-to-mediawiki.html import HTMLParser, re, sys class html2wiki(HTMLParser.HTMLParser): def __init__(self): HTMLParser.HTMLParser.__init__(self) self.wiki = '' # The Wiki text self.wikirow = '' # The current Wiki row of table being constructed from HTML self.inTD = 0 # Used to track if we are inside or outside a ... tag. self.inTR = 0 # Used to track if we are inside or outside a ... tag. self.re_multiplespaces = re.compile('\s+') # regular expression used to remove spaces in excess self.rowCount = 0 # output row counter. self.rowspan = '' self.colspan = '' self.linebreak = ' ' self.data = '' self.prop = '' def handle_starttag(self, tag, attrs): if tag == 'table': self.start_table() elif tag == 'tr': self.start_tr() elif tag == 'td': self.start_td(attrs) def handle_endtag(self, tag): if tag == 'table': self.end_table(); elif tag == 'tr': self.end_tr() elif tag == 'td': self.end_td() def start_table(self): self.wiki += '{| border=1' + self.linebreak self.wiki += '|-' + self.linebreak def end_table(self): self.wiki += '|}' + self.linebreak def start_tr(self): if self.inTR: self.end_tr() # implies self.inTR = 1 def end_tr(self): if self.inTD: self.end_td() # implies self.inTR = 0 if len(self.wikirow) > 0: self.wiki += self.wikirow self.wiki += '|-' + self.linebreak self.wikirow = '' self.rowCount += 1 def start_td(self, attrs): if not self.inTR: self.start_tr() # implies self.data = '' self.prop = '' self.rowspan = '' self.colspan = '' for key, value in attrs: if key == 'rowspan': self.rowspan = value elif key == 'colspan': self.colspan = value self.inTD = 1 def end_td(self): if self.inTD: self.wikirow += '| ' + self.prop + self.re_multiplespaces.sub(' ',self.data.replace('\t',' ').replace(self.linebreak,'').replace('\r','').replace('"','""'))+ self.linebreak; self.data = '' self.inTD = 0 def handle_data(self, data): if self.inTD: if data.strip() != '': self.prop = '' if self.rowspan != '': self.prop = ' rowspan = '+self.rowspan if self.colspan != '': self.prop += ' colspan = '+self.colspan if self.prop: self.prop += ' | ' self.data += data if __name__ == '__main__': parser = html2wiki() if len(sys.argv) == 2: in_file = open(sys.argv[1],"r") text = in_file.read() parser.feed(text) in_file.close() print parser.wiki else: print 'Argument - filename required'

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = 'THE_COLUMN_NAME' )

#!/bin/env ruby # pg_diff - compare two PostgreSQL database schemas # # URL: http://snippets.dzone.com/posts/show/949 # # This is a simple approach to track database schema changes in PostgreSQL. # In some way it is similar to diff program, finding out structure changes # and results in SQL script to upgrade to new schema. # # Differences are tracked on schemas, domains, sequences, views, tables, indices, constraints, rules, functions, triggers. # Two objects with the same name are considered equal if they have the same definitions. # # Missing features: tracking of ownership, user rights, object dependencies, table inheritance, type casts, aggregates, operators. # # Usage: # ./pg_diff dbname=db_v03_dev dbname=db_v04_dev # # Developed using PostgreSQL v8.0.3, v8.1 with ruby-postgres libpq binding (20051127 snapshot). # # This software is released under MIT License # # Copyright (c) 2005 Dmitry Severin # # Permission is hereby granted, free of charge, to any person obtaining a copy # of this software and associated documentation files (the "Software"), to deal # in the Software without restriction, including without limitation the rights # to use, copy, modify, merge, publish, distribute, sublicense, and/or sell # copies of the Software, and to permit persons to whom the Software is # furnished to do so, subject to the following conditions: # # The above copyright notice and this permission notice shall be included in # all copies or substantial portions of the Software. # # THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR # IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, # FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE # AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER # LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, # OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN # THE SOFTWARE. # require 'postgres' module PostgreSqlSchema class Attribute attr_accessor :name, :type_def, :notnull, :default def initialize(name, typedef, notnull, default) @name = name @type_def = typedef @notnull = notnull @default = default end def definition out = [' ', @name, @type_def] out << 'NOT NULL' if @notnull out << 'DEFAULT ' + @default if @default out.join(" ") end def == (other) definition == other.definition end end class Table attr_accessor :table_name, :schema, :attributes, :constraints, :indexes def initialize(conn, schema, table_name) @schema = schema @table_name = table_name @attributes = {} @constraints = {} @indexes = {} @atlist = [] att_query = <<-EOT select attname, format_type(atttypid, atttypmod) as a_type, attnotnull, pg_get_expr(adbin, attrelid) as a_default from pg_attribute left join pg_attrdef on (adrelid = attrelid and adnum = attnum) where attrelid = '#{schema}.#{table_name}'::regclass and not attisdropped and attnum > 0 order by attnum EOT conn.query(att_query).each do |row| attname = row[0] @attributes[attname] = Attribute.new(attname, row[1], row[2], row[3]) @atlist << attname end ind_query = <<-EOT select indexrelid::regclass as indname, pg_get_indexdef(indexrelid) as def from pg_index where indrelid = '#{schema}.#{table_name}'::regclass and not indisprimary EOT conn.query(ind_query).each do |row| @indexes[row[0]] = row[1] end cons_query = <<-EOT select conname, pg_get_constraintdef(oid) from pg_constraint where conrelid = '#{schema}.#{table_name}'::regclass EOT conn.query(cons_query).each do |row| @constraints[row[0]] = row[1] end @constraints.keys.each do |cname| @indexes.delete("#{schema}.#{cname}") if has_index?(cname) end end def has_attribute?(name) @attributes.has_key?(name) end def has_index?(name) @indexes.has_key?(name) || @indexes.has_key?("#{schema}.#{name}") end def has_constraint?(name) @constraints.has_key?(name) end def table_creation out = ["CREATE TABLE #{name} ("] stmt = [] @atlist.each do |attname| stmt << @attributes[attname].definition end out << stmt.join(",\n") out << ");" out.join("\n") end def name "#{schema}.#{table_name}" end def constr_creation out = [] @constraints.each do |n, c| out << "ALTER TABLE #{name} ADD CONSTRAINT #{n} #{c};" end out.join("\n") end def index_creation out = [] @indexes.values.each do |c| out << (c+";") end out.join("\n") end end class Sequence def initialize(conn, sch, relname) @name = "#{sch}.#{relname}" end def definition "CREATE SEQUENCE #{@name} ;" end end class View attr_reader :def, :name def initialize(conn, sch, relname) @name = "#{sch}.#{relname}" view_qery = <<-EOT SELECT pg_catalog.pg_get_viewdef('#{@name}'::regclass, true) EOT @def = conn.query(view_qery)[0][0] end def definition "CREATE VIEW #{@name} AS #{@def}" end end class Database attr_accessor :tables, :views, :sequences, :schemas, :domains, :rules, :functions, :triggers def initialize(conn) cls_query = <<-EOT SELECT n.nspname, c.relname, c.relkind FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r','S','v') AND n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema') ORDER BY 1,2; EOT @views = {} @tables = {} @sequences = {} @schemas = {} @domains = {} @functions = {} @rules = {} @triggers = {} conn.query(cls_query).each do |row| schema, relname, relkind = row case relkind when 'r' then @tables["#{schema}.#{relname}"] = Table.new(conn, schema, relname) when 'v' then @views ["#{schema}.#{relname}"] = View.new(conn, schema, relname) when 'S' then @sequences["#{schema}.#{relname}"] = Sequence.new(conn, schema, relname) end end domain_qry = <<-EOT SELECT n.nspname, t.typname, pg_catalog.format_type(t.typbasetype, t.typtypmod) || ' ' || CASE WHEN t.typnotnull AND t.typdefault IS NOT NULL THEN 'not null default '||t.typdefault WHEN t.typnotnull AND t.typdefault IS NULL THEN 'not null' WHEN NOT t.typnotnull AND t.typdefault IS NOT NULL THEN 'default '||t.typdefault ELSE '' END FROM pg_catalog.pg_type t LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace WHERE t.typtype = 'd' ORDER BY 1, 2 EOT conn.query(domain_qry).each do |row| @domains["#{row[0]}.#{row[1]}"] = row[2] end schema_qry = <<-EOT select nspname from pg_namespace EOT conn.query(schema_qry).each do |row| @schemas[row[0]]=row[0] end func_query = <<-EOT SELECT proname AS function_name , nspname AS namespace , lanname AS language_name , pg_catalog.obj_description(pg_proc.oid, 'pg_proc') AS comment , proargtypes AS function_args , proargnames AS function_arg_names , prosrc AS source_code , proretset AS returns_set , prorettype AS return_type, provolatile, proisstrict, prosecdef FROM pg_catalog.pg_proc JOIN pg_catalog.pg_language ON (pg_language.oid = prolang) JOIN pg_catalog.pg_namespace ON (pronamespace = pg_namespace.oid) JOIN pg_catalog.pg_type ON (prorettype = pg_type.oid) WHERE pg_namespace.nspname !~ 'pg_catalog|information_schema' AND proname != 'plpgsql_call_handler' AND proname != 'plpgsql_validator' EOT conn.exec(func_query).result.each do |tuple| func = Function.new(conn, tuple) @functions[func.signature] = func end rule_query = <<-EOT select schemaname || '.' || tablename || '.' || rulename as rule_name, schemaname || '.' || tablename as tab_name, rulename, definition from pg_rules where schemaname !~ 'pg_catalog|information_schema' EOT conn.exec(rule_query).result.each do |tuple| @rules[tuple['rule_name']] = Rule.new(tuple['tab_name'], tuple['rulename'], tuple['definition']) end trigger_query = <<-EOT select nspname || '.' || relname as tgtable, tgname, pg_get_triggerdef(t.oid) as tg_def from pg_trigger t join pg_class c ON (tgrelid = c.oid ) JOIN pg_namespace n ON (c.relnamespace = n.oid) where not tgisconstraint and nspname !~ 'pg_catalog|information_schema' EOT conn.exec(trigger_query).result.each do |tuple| @triggers[tuple['tgtable'] + "." + tuple['tgname']] = Trigger.new(tuple['tgtable'], tuple['tgname'], tuple['tg_def']) end end end class Rule attr_reader :table_name, :name, :definition def initialize(table_name, name, df) @table_name = table_name @name = name @definition = df end def == (other) other.definition == definition end end class Trigger attr_reader :table_name, :name, :definition def initialize(table_name, name, df) @table_name = table_name @name = name @definition = df + ";" end def == (other) other.definition == definition end end class Function def initialize(conn, tuple) @name = tuple['namespace'] + "." + tuple['function_name'] @language = tuple['language_name'] @src = tuple['source_code'] @returns_set = tuple['returns_set'] @return_type = format_type(conn, tuple['return_type']) @tipes = tuple['function_args'].split(" ") if tuple['function_arg_names'] && tuple['function_arg_names'] =~ /^\{(.*)\}$/ @arnames = $1.split(',') elsif tuple['function_arg_names'].is_a? Array # my version of ruby-postgres @arnames = tuple['function_arg_names'] else @arnames = [""] * @tipes.length end alist = [] @tipes.each_with_index do |typ,idx| alist << (@arnames[idx] +" " + format_type(conn, typ)) end @arglist = alist.join(" , ") @strict = tuple['proisstrict'] ? ' STRICT' : '' @secdef = tuple['prosecdef'] ? ' SECURITY DEFINER' : '' @volatile = case tuple['provolatile'] when 'i' then ' IMMUTABLE' when 's' then ' STABLE' else '' end end def signature "#{@name}(#{@arglist})" end def definition <<-EOT CREATE OR REPLACE FUNCTION #{@name} (#{@arglist}) RETURNS #{@returns_set ? 'SETOF' : ''} #{@return_type} AS $_$#{@src}$_$ LANGUAGE '#{@language}' #{@volatile}#{@strict}#{@secdef}; EOT end def == (other) definition == other.definition end def format_type(conn, oid) t_query = <<-EOT SELECT pg_catalog.format_type(pg_type.oid, typtypmod) AS type_name FROM pg_catalog.pg_type JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = typnamespace) WHERE pg_type.oid = EOT return conn.query(t_query + oid.to_s)[0][0] end end class Diff def initialize(old_db_spec, new_db_spec) @old_conn = PGconn.new(old_db_spec) @new_conn = PGconn.new(new_db_spec) @sections = [ :triggers_drop, :rules_drop, :functions_drop, :indices_drop , :constraints_drop, :views_drop, :sequences_drop , :tables_drop , :domains_drop , :schemas_drop , :schemas_create, :domains_create, :sequences_create, :tables_create , :table_changes , :views_create , :functions_create , :rules_create , :triggers_create , :indices_create, :constraints_create ] @script = {} @sections.each {|s| @script[s] = []} end def run_compare @old_database = Database.new(@old_conn) @new_database = Database.new(@new_conn) compare_schemas compare_domains compare_sequences compare_triggers_drop compare_rules_drop compare_views_drop compare_table_attrs compare_views_create compare_functions compare_rules_create compare_triggers_create compare_table_constraints end def add_script(section, statement) @script[section] << statement end def compare_schemas @old_database.schemas.keys.each do |name| add_script(:schemas_drop , "DROP SCHEMA #{name};") unless @new_database.schemas.has_key?(name) end @new_database.schemas.keys.each do |name| add_script(:schemas_create , "CREATE SCHEMA #{name};") unless @old_database.schemas.has_key?(name) end end def compare_domains @old_database.domains.keys.each do |name| add_script(:domains_drop , "DROP DOMAIN #{name} CASCADE;") unless @new_database.domains.has_key?(name) end @new_database.domains.each do |name, df| add_script(:domains_create , "CREATE DOMAIN #{name} AS #{df};") unless @old_database.domains.has_key?(name) old_domain = @old_database.domains[name] if old_domain && old_domain != df add_script(:domains_drop, "DROP DOMAIN #{name} CASCADE;") add_script(:domains_create, "-- [changed domain] :") add_script(:domains_create, "-- OLD: #{old_domain}") add_script(:domains_create, "CREATE DOMAIN #{name} AS #{df};") end end end def compare_sequences @old_database.sequences.keys.each do |name| add_script(:sequences_drop , "DROP SEQUENCE #{name} CASCADE;") unless @new_database.sequences.has_key?(name) end @new_database.sequences.keys.each do |name| add_script(:sequences_create , "CREATE SEQUENCE #{name};") unless @old_database.sequences.has_key?(name) end end def compare_functions @old_database.functions.keys.each do |name| add_script(:functions_drop , "DROP FUNCTION #{name} CASCADE;") unless @new_database.functions.has_key?(name) end @new_database.functions.each do |name, func| add_script(:functions_create , func.definition) unless @old_database.functions.has_key?(name) old_function = @old_database.functions[name] if old_function && old_function.definition != func.definition add_script(:functions_create , '-- [changed function] :') add_script(:functions_create , '-- OLD :') add_script(:functions_create , old_function.definition.gsub(/^/, "--> ") ) add_script(:functions_create , func.definition) end end end def compare_rules_drop @old_database.rules.each do |name, rule| add_script(:rules_drop , "DROP RULE #{rule.name} ON #{rule.table_name} CASCADE;") unless @new_database.rules.has_key?(name) end end def compare_rules_create @new_database.rules.each do |name, rule| add_script(:rules_create , rule.definition) unless @old_database.rules.has_key?(name) old_rule = @old_database.rules[name] if old_rule && old_rule != rule add_script(:rules_drop , "DROP RULE #{rule.name} ON #{rule.table_name} CASCADE;") add_script(:rules_create , "-- [changed rule] :") add_script(:rules_create , "-- OLD: #{old_rule.definition}") add_script(:rules_create , rule.definition ) end end end def compare_triggers_drop @old_database.triggers.each do |name, trigger| add_script(:triggers_drop , "DROP trigger #{trigger.name} ON #{trigger.table_name} CASCADE;") unless @new_database.triggers.has_key?(name) end end def compare_triggers_create @new_database.triggers.each do |name, trigger| add_script(:triggers_create , trigger.definition) unless @old_database.triggers.has_key?(name) old_trigger = @old_database.triggers[name] if old_trigger && old_trigger != trigger add_script(:triggers_drop , "DROP trigger #{trigger.name} ON #{trigger.table_name} CASCADE;") add_script(:triggers_create , "-- [changed trigger] :") add_script(:triggers_create , "-- OLD #{old_trigger.definition}") add_script(:triggers_create , trigger.definition) end end end def compare_views_drop @old_database.views.keys.each do |name| add_script(:views_drop , "DROP VIEW #{name};") unless @new_database.views.has_key?(name) end end def compare_views_create @new_database.views.each do |name, df| add_script(:views_create , df.definition) unless @old_database.views.has_key?(name) old_view = @old_database.views[name] if old_view && df.definition != old_view.definition add_script(:views_drop , "DROP VIEW #{name};") add_script(:views_create , "-- [changed view] :") add_script(:views_create , "-- #{old_view.definition.gsub(/\n/, ' ')}") add_script(:views_create , df.definition) end end end def compare_table_attrs @old_database.tables.each do |name, table| add_script(:tables_drop, "DROP TABLE #{name} CASCADE;") unless @new_database.tables.has_key?(name) end @to_compare = [] @new_database.tables.each do |name, table| unless @old_database.tables.has_key?(name) add_script(:tables_create , table.table_creation) add_script(:indices_create , table.index_creation) unless table.indexes.empty? @to_compare << name else diff_attributes(@old_database.tables[name], table) diff_indexes(@old_database.tables[name], table) @to_compare << name end end end def compare_table_constraints @c_check = [] @c_primary = [] @c_unique = [] @c_foreign = [] @to_compare.each do |name| if @old_database.tables[name] diff_constraints(@old_database.tables[name], @new_database.tables[name]) else @new_database.tables[name].constraints.each do |cname, cdef| add_cnstr(name, cname, cdef) end end end @script[:constraints_create] += @c_check @script[:constraints_create] += @c_primary @script[:constraints_create] += @c_unique @script[:constraints_create] += @c_foreign end def output out = [] @sections.each do |sect| if @script[sect].empty? out << "-- [SKIP SECTION : #{sect.to_s.upcase}] : no changes\n" else out << "-- [START SECTION : #{sect.to_s.upcase}]" out += @script[sect] out << "-- [END SECTION : #{sect.to_s.upcase}]\n" end end out.join("\n") end def diff_attributes(old_table, new_table) dropped = [] added = [] changed = [] old_table.attributes.keys.each do |attname| if new_table.has_attribute?(attname) changed << attname if old_table.attributes[attname] != new_table.attributes[attname] else dropped << attname end end new_table.attributes.keys.each do |attname| added << attname unless old_table.has_attribute?(attname) end add_script(:table_changes , "-- [#{old_table.name}] dropped attributes") unless dropped.empty? dropped.each do |attname| add_script(:table_changes , "ALTER TABLE #{old_table.name} DROP COLUMN #{attname} CASCADE;") end add_script(:table_changes , "-- [#{old_table.name}] added attributes") unless added.empty? added.each do |attname| add_script(:table_changes , "ALTER TABLE #{old_table.name} ADD COLUMN #{new_table.attributes[attname].definition};") end add_script(:table_changes , "-- [#{old_table.name}] changed attributes") unless changed.empty? changed.each do |attname| old_att = old_table.attributes[attname] new_att = new_table.attributes[attname] add_script(:table_changes , "-- attribute: #{attname}") add_script(:table_changes , "-- OLD : #{old_att.definition}") add_script(:table_changes , "-- NEW : #{new_att.definition}") if old_att.type_def != new_att.type_def add_script(:table_changes , "ALTER TABLE #{old_table.name} ALTER COLUMN #{attname} TYPE #{new_att.type_def};") end if old_att.default != new_att.default if new_att.default.nil? add_script(:table_changes , "ALTER TABLE #{old_table.name} ALTER COLUMN #{attname} DROP DEFAULT;") else add_script(:table_changes , "ALTER TABLE #{old_table.name} ALTER COLUMN #{attname} SET DEFAULT #{new_att.default};") end end if old_att.notnull != new_att.notnull add_script(:table_changes , "ALTER TABLE #{old_table.name} ALTER COLUMN #{attname} #{new_att.notnull ? 'SET' : 'DROP'} NOT NULL;") end end end def diff_constraints(old_table, new_table) dropped = [] added = [] old_table.constraints.keys.each do |conname| if new_table.has_constraint?(conname) if old_table.constraints[conname] != new_table.constraints[conname] dropped << conname added << conname end else dropped << conname end end new_table.constraints.keys.each do |conname| added << conname unless old_table.has_constraint?(conname) end dropped.each do |name| add_script(:constraints_drop , "ALTER TABLE #{old_table.name} DROP CONSTRAINT #{name};") end added.each do |name| add_cnstr(old_table.name, name, new_table.constraints[name]) end end def add_cnstr(tablename, cnstrname, cnstrdef) c_string = "ALTER TABLE #{tablename} ADD CONSTRAINT #{cnstrname} #{cnstrdef} ;" case cnstrdef when /^CHECK / then @c_check << c_string when /^PRIMARY / then @c_primary << c_string when /^FOREIGN / then @c_foreign << c_string when /^UNIQUE / then @c_unique << c_string end end def diff_indexes(old_table, new_table) dropped = [] added = [] old_table.indexes.keys.each do |name| if new_table.has_index?(name) if old_table.indexes[name] != new_table.indexes[name] dropped << name added << name end else dropped << name end end new_table.indexes.each do |name| added << name unless old_table.has_index?(name) end dropped.each do |name| add_script(:indices_drop , "DROP INDEX #{name};") end added.each do |name| add_script(:indices_create , (new_table.indexes[name] + ";")) if new_table.indexes[name] end end end end def parse_conn_params(str) h = {} str.split(/:/).each{|pair| key, value = pair.split('=', 2); h[key]=value} h end diff = PostgreSqlSchema::Diff.new(parse_conn_params(ARGV[0]), parse_conn_params(ARGV[1]) ) diff.run_compare puts diff.output