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

SQLEditor 2.0 Releases

Version 2.0 is a major update of the SQLEditor. It includes Diff support, the new find side panel, the object tree view, better object handling and numerous improvements to object editing and SQL generation. We're also bundling the Django plugin for the first time.

The SQLEditor file format remains the same and files can be shared between SQLEditor 1 and 2, however the new object types (e.g. Views) will be lost if a file is opened in an earlier version of SQLEditor and then saved out.

If you spot any issues with SQLEditor please do report it to us at support@malcolmhardie.com.

Requirements

For Database connections you also need

SQLEditor 2.3 and later includes built in drivers for Postgres, Drizzle, MySQL and SQLite.

In this case you won't need to install a driver because SQLEditor includes one already

SQLEditor 2.2 includes a bundled java runtime. This supports OS X 10.7.3 or later.

On OS X 10.7.2 or earlier (including OS X 10.6.8) you will need to have the Apple Java 6 runtime installed.

If this isn't installed, SQLEditor should be able to install it the first time you start SQLEditor.

10.5 support

This release does not support 10.5 or earlier, 10.6.8 is the minimum.

If you have particular need for 10.5 support please get in touch with us at support@malcolmhardie.com to discuss this.

10.6 and 10.7 support

Now that Apple have released OS X 10.10 Yosemite, we are looking at future support for 10.6 and 10.7

Our current plan is discontinue support for these versions in the near future.

If you have particular need for 10.6 or 10.7 support please get in touch with us at support@malcolmhardie.com to discuss this.

Installation

To install SQLEditor you should drag the SQLEditor application icon 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.

There are two main ways that we distribute SQLEditor, zip archive download or dmg disk image download.

The install process is similar but the details are slightly different.

(A) Zip Download

SQLEditor is normally distributed within a compressed zip archive. This archive must be decompressed before the SQLEditor application can be installed.

To decompress the archive file, locate it in your downloads folder and double click on it.

The SQLEditor application will then be decompressed and you can drag the it to the Applications folder
(B) Disk Image

Alternatively some releases are available in DMG disk image format.
In this case you should mount the disk by double clicking the dmg file and then when the disk image mounts on your desktop, open it and drag the SQLEditor application icon to the Applications folder

Getting Started

When you open SQLEditor you see the canvas, this represents your database design. You can add tables to this by Choosing "Add Table" from the object menu, . You can add fields and indices to your tables using the menus in the same way. Common object types also have toolbar buttons.

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

Keychain Passwords

SQLEditor 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

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:

  1. Delete the foreign key
  2. Add a suitable index to the target table
  3. Export to database (without the foreign key)
  4. Add the foreign key
  5. 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 keeps a separate data types list for each dialect that it knows about.

You can edit these types in the preferences window if you like, although you only get one set per database dialect.

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

Sometimes we make improvements to the types lists included in SQLEditor, you may need to use the reset dialect dialects option to see these changes.

Database Export

Database export may cause data loss

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 successfully 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 will offer the 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.

SQLEditor auto update is based on Sparkle, written by Andy Matuschak.

(However any bugs in this feature are ours)

Appending arbitrary SQL

Using the Document Options window (File->Document Options) you can enter a block of SQL that SQLEditor will add to the end of the SQL output.

Currently this SQL is not used when exporting directly to a database.

Note that there are some limitations. We recommend using simple single line statements, one per line, ending in a semi-colon.

Views

View support is new in SQLEditor 2.0

SQLEditor will export the select statement as entered into the inspector. It doesn't change this string (the parsing is for the visual/graphical view only)

In MySQL JDBC exporting a view will usually cause a change because MySQL expands out any wildcards in the select column list (for example *) and then saves the result.

This means that any view with a * in it will probably be regenerated.

In Postgres, if a view needs to be dropped (due to changes or because it has been removed) then the view must be owned by the user doing the modification.

Otherwise an error occurs

Diff Support

New in version 2.0 of SQLEditor is diff support. This allows you to compare two documents and generate the SQL required to move from the first to the second.

You can also compare a document with the version that you last saved (or with the document as originally opened or imported if you haven't yet saved)

This diff is generated using the system used for database exporting although it doesn't require a database driver to be installed for use.

If you want to compare a document against a database you can import the database into an empty document and then compare your current document against the imported one.

Database Specific Notes

Ruby On Rails Support

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

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!

Postgres

SQLEditor may get confused if you attempt to edit or drop a Postgres primary key index (_pkey) that is in use.

Instead edit the primary key by setting the primary key option in the inspector on the fields you want.

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.

Version 1.6.1 offers major improvements to export, which should now work at a basic level.

It also offers improvements to import, including better index importing.

Work is continuing to improve this.

Filemaker

SQLEditor has basic support for Filemaker databases using the filemaker JDBC driver. This has been tested mainly on FIlemaker 12, but should work on earlier versions.

Note that the JDBC driver doesn't use the filemaker native data types, instead it uses standard SQL types, so SQLEditor will convert the filemaker dialect types to native SQL types when exporting or importing. The source view sidebar panel will show these types, rather than the filemaker types.

If you add columns via SQLEditor they will appear in the database window, however they will not be added to any layouts, so you will need to do this manually.

If you're interested in how this mapping works see the FileMaker 11 ODBC and JDBC Guide (PDF)

Please note also that filemaker doesn't currently allow extracting or creating relationships via the JDBC driver.

Feedback or suggestions for improving this are greatly appreciated.

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.

Primary Keys and SQLite

SQLite supports two kinds of primary key. Regular primary keys which are similar to indexes on the table and native primary keys which are the actual reference id of the table (a signed 64 bit integer ROWID) that SQLite uses internally to reference the table.

Note that only columns aliased to the ROWID can be auto increment and only one column can be aliased to the ROWID.

SQLEditor will generate code to make a column the rowid if that column is of type INTEGER and it is the only primary key column in the table.

If you add another column to the primary key then SQLEditor will try to avoid the column becoming an alias of the rowid.

This behavior is slightly different from 1.6, which would always choose the first column to the rowid, additional columns being relegated to a UNIQUE index instead.

This should give the desired outcomes more often and avoid several edge cases which previously gave unexpected results.

Please get in touch with us at support@malcolmhardie.com if you have comments or suggestions on how this might be improved.

Autosave on Lion

This release of SQLEditor supports autosave when running on Lion. However it is still saving synchronously, our tests suggest that even running on a fairly slow disk this shouldn't impact performance, however if you do find it a problem please tell us.

You can also disable autosave in the preferences panel. (This will also disable versions support)

SVG Export

SVG Export is now available in SQLEditor, use the Export to Image command and select SVG as the image type.

SVG is an interpreted vector image format, so different viewers may render the image differently. Currently we are testing the output against the webkit renderers in Chrome and Safari, plus Firefox. Inkscape is being used as the primary image editing test app.

If you find issues with SVG export please report them.

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

upgrades

You can buy an upgrade at a discount against new copies and licenses purchased after 1st August 2010 can be upgraded free of charge. Licenses where upgrade assurance has been purchased are also eligible for a free upgrade.

To upgrade to 2.0 visit the upgrades page

serial numbers

Release versions of SQLEditor will ask for a serial number when they start up. Version 2.0 is a paid upgrade and requires a different serial number. Serial numbers for SQLEditor 1.x won't work.

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 SQLEditor→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

Thanks!

Feedback and Contact Information

We welcome any comments or suggestions that you have about SQLEditor.

Bug reports are also most gratefully received.

You can 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 you can send us email at:

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

SQLEditor, this document and all related materials are Copyright (c) 2003-2012 by Angus W Hardie and MalcolmHardie Solutions Limited

All rights reserved, unauthorized distribution, modification, reverse engineering or rental are prohibited in part or whole, except where legislation exists providing these rights.

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.

Note that use of this software is subject to our End User License Agreement

All Trademarks acknowledged

Additional legal notices

There are additional legal notices in the file Acknowledgements.html contained within the application bundle.

This can be viewed by clicking the "Acknowledgements" button in the about box of the application. Choose SQLEditor→About SQLEditor.