SQLEditor For Mac OS X Readme File

MalcolmHardie Solutions Ltd

Angus Hardie <Angus.Hardie@malcolmhardie.com>

Introduction

Hi! Welcome to SQLEditor. This is the readme file. It details various things you might want to know before using SQLEditor. In addition to this file there is also online help available within SQLEditor. Use the Help Menu to get this.

Our SQLEditor support webpage has additional information about SQLEditor.

Some of the things that you can do with SQLEditor will seriously affect your database.

If you decide to use SQLEditor with your database it's worthwhile having some kind of backup available.

About SQLEditor

SQLEditor For Mac OS X is a program to help you work with databases. Relational databases are the main type of database that SQLEditor supports, however other types can also be used.

SQLEditor allows you to create, import, view, edit and export database structures. You can use it to create diagrams of databases showing the logical layout of objects within the database. You can save images of databases to use on webpages or in documents.

You can also import and view the structure of live databases via JDBC

Requirements

Mac OS X 10.3.9 or later
To use JDBC support you need a database with a JDBC driver

This version of SQLEditor does not support Mac OS X 10.2

You should use the latest version of Mac OS X. If you are using Mac OS X 10.3 you need to use 10.3.9

If you are using 10.4 or later you should use the latest release available.

It is recommended to update the latest available version of java

Java For Mac OS X 10.5, release 1

If you have installed this update then you need to use version 1.4 or later.

There is also an experimental 1.3.9r2 release which is available by request. It is exactly the same as 1.3.9 except that has been modified to support the new java configuration

If you specifically want 1.3.9r2 rather than using 10.4 please send us an email at support@malcolmhardie.com.

Universal Binary

SQLEditor is a universal binary. This means that it will work both on PowerPC based macs and Intel based Macs.

You must run SQLEditor natively. SQLEditor cannot run using Rosetta.

Installation

To install SQLEditor you should drag the icon from the disk image (or CD-ROM) to the Applications folder.

You may install SQLEditor in another location if you wish.

However the path of the location should not have a colon (:) character in it.

(It is not normally possible to create such a folder anyway)

Getting Started

When you open SQLEditor you see the canvas, this represents your database design. You can add tables to this by dragging them from the palette or using the menus and toolbar items. You can add fields and indices to your tables in the same way.

Use the inspector to make changes to your objects, the inspector changes to give you the options and settings for the selected object.

(If the inspector is not visible choose Window→Show Inspector)

The export the design to an SQL DDL text file using File→Export, or directly to your database system using File→Export to Database.

If you have an existing database you can import it using File→Import from Database. To import from a SQL DDL text file or database dump you can use File→Import.

There is online help available Help→SQLEditor Help

Usage Notes

New Display Style

Following feedback the drawing of objects by SQLEditor has been improved. Objects are now drawn with curved borders and label colors are applied the table title bar instead of the body, with the body section remaining a constant color.

This new behaviour can be disabled in the Appearances tab of the Preferences window by de-selecting Draw rounded boxes and Use new style color box headers.

Keychain Passwords

SQLEditor (from 1.4b6) now stores saved passwords in the keychain. The password is stored as an application password using the connection string as the account description and the username as the account user name.

You may see messages asking to unlock the keychain when using database import or export.

Compound Foreign Keys

From version 1.4.4b1 SQLEditor now supports compound foreign keys. These are available in addition to the existing column-to-column foreign keys.

To create a multi column foreign key you need to add the Foreign Key object to a table, then drag from the new foreign key object to a target table. This will create a link between the tables. Then you can select the new foreign key and add column pairs using the inspector. Click the + button at the bottom of the list then use the two popup menus to choose which fields should be linked together.

By default SQLEditor will import using the old style foreign keys where possible, however when it finds a multi-column foreign key on import then it will create a new style foreign key instead.

Note that you may have to add indexes manually in the target tables. MySQL in particular requires this in some versions. SQLEditor is not yet able to do this automatically (but this should be added in a future release).

If you get an error caused by lack of indexes:

Delete the foreign key
Add a suitable index to the target table
Export to database (without the foreign key)
Add the foreign key
Export again

Which should work. This is a limitation of the export instruction ordering and will be fixed in a future revision

This is a new feature; feedback and suggestions are most welcome.

Column Data Types

SQLEditor now (from 1.3.2) keeps a separate types list for each dialect that it knows about.

If you have not used SQLEditor before then these lists are created from scratch from the standard lists.
If you have used SQLEditor before then your current type list will be duplicated for each supported dialect.

Although the resulting lists contain the same entries, they are different lists. A change made to one will not be reflected in another.

Note also that changes made from the field inspector will now only appear in the currently selected dialect.

Also new in 1.3.2 is the concept of a default data type. This is the type used when a new field is added from a menu or toolbar button.

Previously this was always INTEGER, but it now a user preference. There is a different default type for each dialect.

The button Reset all dialects will reload all type lists to the default lists provided with SQLEditor.

The new types system uses a separate file from the old types system so that old versions of SQLEditor should hopefully continue to work.

Reset All dialects uses only the list provided with SQLEditor. It does not reimport the types from legacy preferences files.

(If you are for some reason running multiple versions; please tell us what makes this necessary. It may be something that can be fixed.)

Ruby On Rails Support

SQLEditor can import and export Ruby on Rails ActiveRecord migration class files.

Ruby On Rails Export support is available from version 1.3
Ruby On Rails Import Support is available from version 1.4b8.

Ruby On Rails Support requires a copy of Ruby to be installed or symlinked at the default location of /usr/bin/ruby

The Ruby on Rails migration format is considered a dialect of SQL (not a database export) so you should choose File→Export To File to use the feature.

You can also export (but not currently import) Ruby On Rails migration text using the clipboard with copy & paste.

You need to select the Ruby On Rails Document dialect for this to work. (In File→Document Options)

The Ruby On Rails importer works by executing the Ruby code and watching the result. After quite a bit of study this method seemed more reliable than trying to parse the structure directly.

The importer will normally execute the migration "up" method using $safe level 3. This means that methods such as file access may not work correctly and you will see security errors.

Although this has been tested and should be secure, this importer does execute the code in the migration file.

Please be cautious!

OpenBase Support

Support for OpenBase is still under development. From 1.3.7 it should import correctly.

Updating an existing database during an export may fail if the update involves adding or removing a column from an existing table. Use the drop tables option to remove tables first if this causes trouble.

Microsoft SQL Server Support

There is some support for Microsoft SQL server. However this is currently incomplete. Importing possibly may work, while exporting probably won't. Work is continuing to improve this.

Foreign keys and MySQL

The MyISAM table type in MySQL presents some issues for SQLEditor. By default although it records details of foreign keys, it doesn't normally enforce referential integrity, nor does it return details of the foreign keys when SQLEditor asks. This makes it difficult for SQLEditor to reverse engineer foreign keys from tables of this type. To get full foreign key support in MySQL you need to use the INNODB table type (or another table type that specifically supports foreign keys)

This is a known issue and work is continuing on developing a way of extracting this information.

Database Export

Data export may cause data loss

Database export first appeared in version 1.1.

When you use "File->export to database" SQLEditor will attempt to synchronize the database with the current document.

Drop Tables If this option is checked then SQLEditor will DELETE tables in the database that don't exist in the current document.

This option is normally unchecked

Drop Fields and Indexes

This option controls whether SQLEditor is permitted to drop fields and indexes during export.

Simulate Export

This option will cause SQLEditor to attempt an export but instead of executing instructions will simply record which instructions would have been executed. These can then be viewed in the Status Window by clicking the "View Log" button.

Note that if SQLEditor would have failed to succesfully export (either due a to bug or some database difficulty) the simulation instruction list will also fail if manually applied to the database.

Warnings about database export

Altering existing tables which contain data is a risky activity at the best of times, so you may loose the data in tables that are not deleted.

There are some combinations of databases that don't react very well to trying to change tables, SQLEditor has particular trouble with Postgres before 8.0, because earlier versions didn't support the change column instruction, making changes to columns difficult. Try setting the drop table option if you have trouble. It is also troublesome to change MySQL table engines (for example from INNODB to MyIASM) because this may fail at the database level.

This is normally because there are other tables using the same engine that depend on the table in question, but it may cause errors on export.

We are continuing to improve Database export.

If you experience a problem or if you see an error message please report it to support@malcolmhardie.com.

Automatic JDBC Driver detection

SQLEditor will auto detect certain JDBC Drivers. If the driver you are using is not auto-detected you can add it manually in Preferences→Database preference pane.

If you have added new driver files you may need to restart SQLEditor if it is running in order to see these. Auto detection only occurs when SQLEditor is first started.

Please see the SQLEditor manual or the support pages for more details on this

Database Driver Location

SQLEditor uses JDBC drivers to connect to databases. You need to place these in specific locations for SQLEditor to be able to use them.

Any Java applications:	`/Library/Java/Extensions`
SQLEditor Only	`/Library/Application Support/SQLEditor/JDBC Drivers`

If these directories don't exist you may need to create them. SQLEditor will show you a list of all the drivers it can find in the Database pane of the preferences window and in the driver popup menu of the Import from database and Export from database windows.

Internet Version Checking

As an optional feature, SQLEditor will automatically check for a new release when you start up.

You can also make this check manually by selecting the relevant option from the SQLEditor menu.

The first time it starts, SQLEditor will ask you if you want to check for updates. If you later change your mind you can alter this setting in the Preferences window.

During the version check SQLEditor downloads a file from our servers and checks the data in the file against its own version number.

Some versions of SQLEditor have an option to send a small amount of system information when checking for new versions. This information does not specifically identify your computer. It includes information such as the operating system version, number and type of processors and the amount of ram.

If you have any concerns about this please contact us (below) or turn the feature off.

Version 1.4b2 uses Sparkle by Andy Matuschak. This offers various improvements over the system in use before including a new skip version option.

Registration and Serial Numbers

SQLEditor is not free. You can try it free for 30 days but after that you should buy a license for continued use. Buying a license gives you technical support and removes the registration reminder dialog box at startup.

It also means that you're supporting further development and provides support for a small Mac software developer.

To buy a copy please visit our online store

Release versions of SQLEditor will ask for a serial number when they start up. All issued serial numbers should work, but if you have a serial number that doesn't seem to work please ensure that the name and serial code are entered exactly as provided to you. If this still doesn't work please contact support@malcolmhardie.com and we'll sort the problem out. If you want to register a beta copy you can do this by choosing Help→Registration and entering your details.

Registration of a beta version is valid and will show that the software has been registered.

Also when you later install a release version you shouldn't see the annoying registration reminder screen at all.

Acknowledgments

Leynos and Bruce for helping to test alpha versions
Dhruba for some extremely useful comments
Johan Basberg for Icons and lots of useful ideas

Alexandra Hardie, John Malcolm, Dhruba Bandopadhyay, Chris Ogden
Kate Ho, George Plymale, Karl Timmermann

RBSplitView by Rainer Brockerhoff
Sparkle by Andy Matuschak
Keychain Framework by Wade Tregaskis

Ruby On Rails Plugin:
- Objective PCRE by Christopher Bess
- PCRE Library by Philip Hazel
- Ruby Active Record by David Heinemeier Hansson

People who believed enough to buy SQLEditor before the 1.0 first release.
Everyone who sent in comments, suggestions and bug reports

Thanks!

Feedback and Contact Information

From version 1.1b4 you can now send suggestions using Help→Send Suggestion

This will create a new message in Apple Mail and offer the opportunity to automatically attach the SQLEditor log file.

Alternatively please send any comments you may have on any aspect of the software to:

support@malcolmhardie.com

This is the best way to ensure that your comments get read. We promise a real person will read every (non-spam) email.

If you need support please visit the SQLEditor support webpage or send us email at support@malcolmhardie.com

For sales queries please contact sales@malcolmhardie.com

To buy a copy please visit our online store.

Legal Information

Copyright

This software may not be exported to any country to which software exports are prohibited under UK, US or European Laws.

Legal Information

An unfortunate but necessary disclaimer follows:

THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

All Trademarks acknowledged

Keychain Framework

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
Neither the name of Wade Tregaskis nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.