Changes between Version 1 and Version 2 of UsingSqlDesigner


Ignore:
Timestamp:
Dec 13, 2010, 11:58:25 AM (10 years ago)
Author:
flip
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • UsingSqlDesigner

    v1 v2  
    1 Here's a guide on how to change Vespa's database. It assumes you know SQL and
    2 SQLite.
     1= About WWW SQL Designer =
    32
    4 = Editing Vespa's Database =
     3WWW SQL Designer is a BSD-licensed, browser-based
     4tool by Ondrej Zara that offers a graphical
     5interface for database design without trying to do too much. It displays
     6a database as an entity relationship diagram, or ERD for short. WWW SQL
     7Designer can generate SQL from an ERD, and that's how we use it.
    58
    6 All three of our `.sql` files are in `common/resources`. Vespa uses them when
    7 creating
    8 a new database. They are `create_tables.sql`, `create_indices.sql` and
    9 `create_views.sql`. The latter two I wrote and edit by hand. The first is
    10 generated by a tool called SQL Designer.
     9We have [http://scion.duhs.duke.edu/www_sqldesigner/ a customized version of WWW SQL Designer]
     10installed on scion. Our customizations are important to Vespa so you
     11should use the version on scion or a local version to which you've applied
     12our customizations. Details on the customization and installation are below.
    1113
    12 == About SQL Designer ==
    1314
    14 [http://ondras.zarovi.cz/sql/demo/ SQL Designer] is a BSD-licensed,
    15 browser-based tool by Ondrej Zara that displays a graphical representation
    16 of one's database tables. This representation is also known as an
    17 [http://en.wikipedia.org/wiki/Entity-relationship_model entity relationship diagram]
    18 or ERD for short. SQL Designer can generate SQL from an ERD, and
    19 that's how we use it.
     15== What It Is and What It Isn't ==
    2016
    21 You don't need to use SQL Designer to edit our database. '''However''',
    22 one must decide to use it for all edits or for none. In other words, once
    23 you stop using SQL Designer to edit your database, you can't use it again
    24 (without a good deal of time-consuming work).
     17'''What It Is''': Handy.
    2518
    26 The reason is that SQL Designer saves the ERD in an intermediate XML file.
    27 If you edit the SQL directly to create v1.0.1 of that file but the XML file
    28 is still at v1.0.0, then the next time you load that file into SQL Designer
    29 and generate SQL, the SQL will regress to v1.0.0. The changes you made to
    30 produce v1.0.1 of the SQL will be lost.
     19'''What It Isn't''': Necessary. We use it as a convenience. You can always
     20go back to hand-editing your `.sql` files. (See Life Without WWW SQL Designer,
     21below.) ''However'', note that you can't
     22mix use of WWW SQL Designer with hand editing of `.sql` files.
    3123
    32 SQL Designer is similar to wxGlade in this respect. wxGlade ''generates'' a
    33 `.py` file that we store in our `auto_gui` directory, while wxGlade saves its
    34 work in a `.wxg` file (which happens to be XML). One can directly edit the
    35 `.py` file in `auto_gui`, but doing so makes it out of sync with the `.wxg`
    36 file.
     24The reason is that WWW SQL Designer saves the ERD in an XML
     25file. If you edit the SQL directly to create v1.0.1 of that file but the XML
     26file is still at v1.0.0, then the next time you load that file into SQL
     27Designer and generate SQL, the SQL will regress to v1.0.0. The changes you
     28made to produce v1.0.1 of the SQL will be lost.
    3729
    38 == Using SQL Designer ==
     30WWW SQL Designer is similar to wxGlade in this respect. wxGlade generates a
     31.py file that we store in our auto_gui directory, while wxGlade saves its work
     32in a .wxg file (which happens to be XML). One can directly edit the .py file
     33in auto_gui, but doing so makes it out of sync with the .wxg file.
    3934
    40 One can use SQL Designer online and it's also easy to download and use a
    41 local version.
     35== Using WWW SQL Designer ==
    4236
    43 Regardless of whether you're using a local or online version,
    44 SQL Designer features a cheap but effective way to save and re-open
    45 the ERD and SQL it generates -- copy & paste. The moving parts of SQL
    46 Designer are written in Javascript and I think it's rather difficult for
    47 Javascript to
    48 read & write arbitrary files from/to your hard disk. SQL Designer steps around
    49 the problem by putting what you need in a textbox and letting you copy & paste
    50 to/from files that you've saved.
     37WWW SQL Designer features a cheap but effective way to save and re-open the
     38ERD and SQL it generates -- copy & paste. The moving parts of WWW SQL Designer
     39are written in Javascript and I think it's rather difficult for Javascript to
     40read & write arbitrary files from/to your hard disk. WWW SQL Designer steps
     41around the problem by putting what you need in a textbox and letting you copy
     42& paste to/from files that you've saved.
    5143
    5244=== Opening Vespa's ERD ===
    5345
    54  1. Open SQL Designer in your Web browser; either a local copy or online.
     46 1. Open WWW SQL Designer in your Web browser.
    5547 1. Open `common/resources/erd.xml` in your favorite editor and copy its   
    5648 entire contents to the clipboard.
    57  1. Click the Save/Load button at the top of SQL Designer's tool menu thingy
     49 1. Click the Save/Load button at the top of WWW SQL Designer's tool menu thingy
    5850 on the right.
    59  1. Paste into SQL Designer's Input/Output textbox.
     51 1. Paste into WWW SQL Designer's Input/Output textbox.
    6052 1. Click the "Load" button. WWW SQL Designer will display something very much
    6153 like what you see in `common/resources/erd.pdf`.
     
    6759 1. Click the Save/Load button at the top of SQL Designer's tool menu thingy
    6860   on the right.
    69  1. Click the "Save XML" button. SQL Designer will populate the Input/Output
     61 1. Click the "Save XML" button. WWW SQL Designer will populate the Input/Output
    7062   textbox with XML that describes the ERD.
    7163 1. Copy the contents of the textbox to the clipboard.
    7264 1. Open `common/resources/erd.xml` in your favorite editor.
    7365 1. Paste into `erd.xml` and save it.
    74  1. In SQL Designer, click "Generate SQL (SQLite)". SQL Designer will populate
     66 1. In WWW SQL Designer, click "Generate SQL (SQLite)". SQL Designer will populate
    7567   the Input/Output textbox with SQL that matches the ERD.
    7668 1. Open `common/resources/create_tables.sql` in your favorite editor.
     
    7971   as `common/resources/erd.pdf`. This isn't necessary but it saves time when
    8072   you want to look at the database design without going through all the
    81    steps to open SQL Designer.
     73   steps to open WWW SQL Designer.
    8274
    83 === Local Changes to SQL Designer ===
     75
     76== Our Customized WWW SQL Designer ==
    8477
    8578SQLite is an odd database in that you can define columns that have datatypes
    86 of which SQLite knows nothing, like TIMESTAMP, BOOLEAN or BANANAS. We use
    87 the first two and it's nice to be able to specify these in SQL Designer.
     79of which SQLite knows nothing, like TIMESTAMP, BOOLEAN or BANANAS. The first
     80two are particularly useful because Python's `sqlite3` module
     81(which we use) automatically converts the data in such columns to
     82`DateTime` and `Boolean` objects respectively. '''Vespa relies on this
     83behavior.'''
    8884
    89 I made a local copy of SQL Designer and changed its `db/sqlite/datatypes.xml`
    90 a little bit. An svn diff is below.
     85Vespa assumes that the sqlite3 module handles conversion of timestamp and
     86boolean values to the appropriate Python objects. Therefore it's important
     87to declare these columns with the appropriate types. By default,
     88WWW SQL Designer doesn't know about TIMESTAMP and BOOLEAN columns, but it's
     89easy to add custom types. See the installation instructions below for details.
     90
     91
     92= Installing WWW SQL Designer (on Scion or Locally) =
     93
     94These are generic install instructions. The
     95[private:wiki:WwwSqlDesigner instructions for installing on scion] are a
     96little more detailed.
     97
     98== Installing From SVN ==
     99
     100WWW SQL Designer is hosted here:
     101http://code.google.com/p/wwwsqldesigner/
     102
     103Instructions for SVN checkout are here:
     104http://code.google.com/p/wwwsqldesigner/source/checkout
     105
     106You can install it anywhere on your hard drive. I'm going to install to
     107my Desktop (`/home/philip/Desktop`) which is not a good choice but it
     108emphasizes the fact that this will work just about anywhere you put it.
     109
     110 1. `cd /home/philip/Desktop`
     111 2. `svn checkout http://wwwsqldesigner.googlecode.com/svn/trunk/ www_sqldesigner`
     112 
     113Boom, it's working! You can "run" it by double-clicking on
     114`/home/philip/Desktop/www_sqldesigner/index.html`.
     115
     116== Our Customizations ==
     117
     118To apply our customizations, edit `www_sqldesigner/db/sqlite/datatypes.xml`
     119and add these two lines:
    91120
    92121{{{
    93 $ svn diff db/sqlite/datatypes.xml
    94 Index: db/sqlite/datatypes.xml
    95 ===================================================================
    96 --- db/sqlite/datatypes.xml     (revision 102)
    97 +++ db/sqlite/datatypes.xml     (working copy)
    98 @@ -2,9 +2,10 @@
    99  <datatypes db="sqlite">
    100         <group label="Affinity">
    101                 <type label="Text" default="" length="1" sql="TEXT" quote="'" color="rgb(255,200,200)" />
    102 -               <type label="Numeric" default="0" length="0" sql="NUMERIC" quote="" color="rgb(238,238,170)" />
    103                 <type label="Integer" default="0" length="0" sql="INTEGER" quote="" color="rgb(238,238,170)" />
    104                 <type label="Real" default="0" length="0" sql="REAL" quote="" color="rgb(238,238,170)" />
    105                 <type label="None" default="" length="0" sql="NONE" quote="" color="rgb(200,255,200)" />
    106 +               <type label="Timestamp" default="" length="0" sql="TIMESTAMP" quote="'" color="rgb(200,255,200)" />
    107 +               <type label="Boolean" default="" length="0" sql="BOOLEAN" quote="'" color="rgb(238,238,170)" />
    108         </group>
    109  </datatypes>
     122        <type label="Timestamp" default="" length="0" sql="TIMESTAMP" quote="'" color="rgb(200,255,200)" />
     123        <type label="Boolean"   default="" length="0" sql="BOOLEAN"   quote="'" color="rgb(238,238,170)" />
    110124}}}
     125
     126Once you're done, you should be able to see the new types in WWW SQL Designer
     127as in the screenshot below.
     128
     129[[Image(screenshot.png)]]
     130
     131
     132== Life Without WWW SQL Designer ==
     133
     134
     135Above I mentioned how
     136WWW SQL Designer is similar to wxGlade in the way it affects what files one
     137can and cannot hand edit. Well, they're dissimilar in that building GUIs
     138like Vespa's by hand (without wxGlade) is pretty intimidating, but
     139designing and edit a database without a GUI tool like WWW SQL Designer is
     140not a big deal.
     141
     142So If you don't want to use this tool at some point, that's
     143fine. You can edit Vespa's `.sql` files in a text editor. Just don't go back
     144to using WWW SQL Designer without rebuilding your ERD from scratch!
     145
     146
     147