wiki:UsingSqlDesigner

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

--

Here's a guide on how to change Vespa's database. It assumes you know SQL and SQLite.

Editing Vespa's Database

All three of our .sql files are in common/resources. Vespa uses them when creating a new database. They are create_tables.sql, create_indices.sql and create_views.sql. The latter two I wrote and edit by hand. The first is generated by a tool called SQL Designer.

About SQL Designer

SQL Designer is a BSD-licensed, browser-based tool by Ondrej Zara that displays a graphical representation of one's database tables. This representation is also known as an entity relationship diagram or ERD for short. SQL Designer can generate SQL from an ERD, and that's how we use it.

You don't need to use SQL Designer to edit our database. However, one must decide to use it for all edits or for none. In other words, once you stop using SQL Designer to edit your database, you can't use it again (without a good deal of time-consuming work).

The reason is that SQL Designer saves the ERD in an intermediate 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.

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 SQL Designer

One can use SQL Designer online and it's also easy to download and use a local version.

Regardless of whether you're using a local or online version, 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 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. 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 SQL Designer in your Web browser; either a local copy or online.
  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 SQL Designer's tool menu thingy on the right.
  4. Paste into 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. 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 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 SQL Designer.

Local Changes to 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. We use the first two and it's nice to be able to specify these in SQL Designer.

I made a local copy of SQL Designer and changed its db/sqlite/datatypes.xml a little bit. An svn diff is below.

$ svn diff db/sqlite/datatypes.xml 
Index: db/sqlite/datatypes.xml
===================================================================
--- db/sqlite/datatypes.xml	(revision 102)
+++ db/sqlite/datatypes.xml	(working copy)
@@ -2,9 +2,10 @@
 <datatypes db="sqlite">
 	<group label="Affinity">
 		<type label="Text" default="" length="1" sql="TEXT" quote="'" color="rgb(255,200,200)" />
-		<type label="Numeric" default="0" length="0" sql="NUMERIC" quote="" color="rgb(238,238,170)" />
 		<type label="Integer" default="0" length="0" sql="INTEGER" quote="" color="rgb(238,238,170)" />
 		<type label="Real" default="0" length="0" sql="REAL" quote="" color="rgb(238,238,170)" />
 		<type label="None" default="" length="0" sql="NONE" quote="" color="rgb(200,255,200)" />
+		<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)" />
 	</group>
 </datatypes>

Attachments (1)

Download all attachments as: .zip