wiki:UsingSqlDesigner

Version 2 (modified by flip, 9 years ago) (diff)

--

About WWW SQL Designer

WWW SQL Designer is a BSD-licensed, browser-based tool by Ondrej Zara that offers a graphical interface for database design without trying to do too much. It displays a database as an entity relationship diagram, or ERD for short. WWW SQL Designer can generate SQL from an ERD, and that's how we use it.

We have a customized version of WWW SQL Designer installed on scion. Our customizations are important to Vespa so you should use the version on scion or a local version to which you've applied our customizations. Details on the customization and installation are below.

What It Is and What It Isn't

What It Is: Handy.

What It Isn't: Necessary. We use it as a convenience. You can always go back to hand-editing your .sql files. (See Life Without WWW SQL Designer, below.) However, note that you can't mix use of WWW SQL Designer with hand editing of .sql files.

The reason is that WWW SQL Designer saves the ERD in an XML file. If you edit the SQL directly to create v1.0.1 of that file but the XML file is still at v1.0.0, then the next time you load that file into SQL Designer and generate SQL, the SQL will regress to v1.0.0. The changes you made to produce v1.0.1 of the SQL will be lost.

WWW SQL Designer is similar to wxGlade in this respect. wxGlade generates a .py file that we store in our auto_gui directory, while wxGlade saves its work in a .wxg file (which happens to be XML). One can directly edit the .py file in auto_gui, but doing so makes it out of sync with the .wxg file.

Using WWW SQL Designer

WWW SQL Designer features a cheap but effective way to save and re-open the ERD and SQL it generates -- copy & paste. The moving parts of WWW SQL Designer are written in Javascript and I think it's rather difficult for Javascript to read & write arbitrary files from/to your hard disk. WWW SQL Designer steps around the problem by putting what you need in a textbox and letting you copy & paste to/from files that you've saved.

Opening Vespa's ERD

  1. Open WWW SQL Designer in your Web browser.
  2. Open common/resources/erd.xml in your favorite editor and copy its entire contents to the clipboard.
  3. Click the Save/Load button at the top of WWW SQL Designer's tool menu thingy on the right.
  4. Paste into WWW SQL Designer's Input/Output textbox.
  5. Click the "Load" button. WWW SQL Designer will display something very much like what you see in common/resources/erd.pdf.

Saving Changes

If you've made some changes and want to save your work, here's what to do.

  1. Click the Save/Load button at the top of SQL Designer's tool menu thingy on the right.
  2. Click the "Save XML" button. WWW SQL Designer will populate the Input/Output textbox with XML that describes the ERD.
  3. Copy the contents of the textbox to the clipboard.
  4. Open common/resources/erd.xml in your favorite editor.
  5. Paste into erd.xml and save it.
  6. In WWW SQL Designer, click "Generate SQL (SQLite)". SQL Designer will populate the Input/Output textbox with SQL that matches the ERD.
  7. Open common/resources/create_tables.sql in your favorite editor.
  8. Paste into create_tables.sql and save it.
  9. Go back to your Web browser and print the ERD to a PDF file and save that as common/resources/erd.pdf. This isn't necessary but it saves time when you want to look at the database design without going through all the steps to open WWW SQL Designer.

Our Customized WWW SQL Designer

SQLite is an odd database in that you can define columns that have datatypes of which SQLite knows nothing, like TIMESTAMP, BOOLEAN or BANANAS. The first two are particularly useful because Python's sqlite3 module (which we use) automatically converts the data in such columns to DateTime and Boolean objects respectively. Vespa relies on this behavior.

Vespa assumes that the sqlite3 module handles conversion of timestamp and boolean values to the appropriate Python objects. Therefore it's important to declare these columns with the appropriate types. By default, WWW SQL Designer doesn't know about TIMESTAMP and BOOLEAN columns, but it's easy to add custom types. See the installation instructions below for details.

Installing WWW SQL Designer (on Scion or Locally)

These are generic install instructions. The instructions for installing on scion are a little more detailed.

Installing From SVN

WWW SQL Designer is hosted here: http://code.google.com/p/wwwsqldesigner/

Instructions for SVN checkout are here: http://code.google.com/p/wwwsqldesigner/source/checkout

You can install it anywhere on your hard drive. I'm going to install to my Desktop (/home/philip/Desktop) which is not a good choice but it emphasizes the fact that this will work just about anywhere you put it.

  1. cd /home/philip/Desktop
  2. svn checkout http://wwwsqldesigner.googlecode.com/svn/trunk/ www_sqldesigner

Boom, it's working! You can "run" it by double-clicking on /home/philip/Desktop/www_sqldesigner/index.html.

Our Customizations

To apply our customizations, edit www_sqldesigner/db/sqlite/datatypes.xml and add these two lines:

	<type label="Timestamp" default="" length="0" sql="TIMESTAMP" quote="'" color="rgb(200,255,200)" />
	<type label="Boolean"   default="" length="0" sql="BOOLEAN"   quote="'" color="rgb(238,238,170)" />

Once you're done, you should be able to see the new types in WWW SQL Designer as in the screenshot below.

Life Without WWW SQL Designer

Above I mentioned how WWW SQL Designer is similar to wxGlade in the way it affects what files one can and cannot hand edit. Well, they're dissimilar in that building GUIs like Vespa's by hand (without wxGlade) is pretty intimidating, but designing and edit a database without a GUI tool like WWW SQL Designer is not a big deal.

So If you don't want to use this tool at some point, that's fine. You can edit Vespa's .sql files in a text editor. Just don't go back to using WWW SQL Designer without rebuilding your ERD from scratch!

Attachments (1)

Download all attachments as: .zip