Changes between Version 2 and Version 3 of UsingSqlDesigner


Ignore:
Timestamp:
Dec 17, 2010, 5:33:01 PM (9 years ago)
Author:
flip
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • UsingSqlDesigner

    v2 v3  
     1We make all of our changes to the database structure with a tool called
     2[http://ondras.zarovi.cz/sql/demo/ WWW SQL Designer], so this document is
     3dedicated to explaining that tool and how we use it.
     4
    15= About WWW SQL Designer =
    26
     
    610a database as an entity relationship diagram, or ERD for short. WWW SQL
    711Designer can generate SQL from an ERD, and that's how we use it.
    8 
    9 We have [http://scion.duhs.duke.edu/www_sqldesigner/ a customized version of WWW SQL Designer]
    10 installed on scion. Our customizations are important to Vespa so you
    11 should use the version on scion or a local version to which you've applied
    12 our customizations. Details on the customization and installation are below.
    1312
    1413
     
    7473
    7574
    76 == Our Customized WWW SQL Designer ==
     75== WWW SQL Designer and Our Custom Types ==
    7776
    78 SQLite is an odd database in that you can define columns that have datatypes
     77SQLite is an odd database in that it allows one to define columns that have datatypes
    7978of which SQLite knows nothing, like TIMESTAMP, BOOLEAN or BANANAS. The first
    80 two 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
    83 behavior.'''
     79two are particularly useful. Python's `sqlite3` module (which we use)
     80automatically converts the data in TIMESTAMP columns to `DateTime` objects.
     81Similarly, our `db.py` code has two small functions that translate BOOLEAN
     82column data into Python's `True` or `False`. '''Vespa relies on this behavior.'''
    8483
    85 Vespa assumes that the sqlite3 module handles conversion of timestamp and
    86 boolean values to the appropriate Python objects. Therefore it's important
     84Vespa assumes that these columns will be translated into the appropriate
     85objects, therefore it's important
    8786to declare these columns with the appropriate types. By default,
    8887WWW SQL Designer doesn't know about TIMESTAMP and BOOLEAN columns, but it's
    89 easy to add custom types. See the installation instructions below for details.
     88easy to add custom types.
    9089
     90=== Adding Custom Types ===
    9191
    92 = Installing WWW SQL Designer (on Scion or Locally) =
     92WWW SQL Designer saves datatype information in the XML file it generates, so
     93if you paste our `erd.xml` into WWW SQL Designer, it will automatically know
     94about TIMESTAMP and BOOLEAN.
    9395
    94 These are generic install instructions. The
    95 [private:wiki:WwwSqlDesigner instructions for installing on scion] are a
    96 little more detailed.
    97 
    98 == Installing From SVN ==
    99 
    100 WWW SQL Designer is hosted here:
    101 http://code.google.com/p/wwwsqldesigner/
    102 
    103 Instructions for SVN checkout are here:
    104 http://code.google.com/p/wwwsqldesigner/source/checkout
    105 
    106 You can install it anywhere on your hard drive. I'm going to install to
    107 my Desktop (`/home/philip/Desktop`) which is not a good choice but it
    108 emphasizes 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  
    113 Boom, it's working! You can "run" it by double-clicking on
    114 `/home/philip/Desktop/www_sqldesigner/index.html`.
    115 
    116 == Our Customizations ==
    117 
    118 To apply our customizations, edit `www_sqldesigner/db/sqlite/datatypes.xml`
    119 and add these two lines:
     96Suppose you want to add the custom type BANANAS. All you'd have to do is
     97edit `erd.xml` and add the following line below all of the other `<type>`
     98elements.
    12099
    121100{{{
    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)" />
     101<type label="Bananas" default="" length="0" sql="BANANAS" quote="'" color="yellow" />
    124102}}}
    125 
    126 Once you're done, you should be able to see the new types in WWW SQL Designer
    127 as in the screenshot below.
    128 
    129 [[Image(screenshot.png)]]
    130103
    131104