SQLEditor For Mac OS X Readme File

MalcolmHardie Solutions Ltd

<support@malcolmhardie.com>

2018-11-07 Updated for SQLEditor 3.5 Release
2020-05-05 Updated for SQLEditor 3.7 Release
2021-11-30 Updated for SQLEditor 4.0 Release


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 4.0 Releases

Version 4.0 is a major update of the SQLEditor.

The

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

Requirements

We expect to increase the minimum Mac OS version requirement for SQLEditor in the near future

SQLEditor 4 includes built in database drivers for Postgres, Drizzle, MySQL and SQLite.

Accessing other database systems will require installation of a suitable JDBC database driver.

In SQLEditor 4.0b1 the Java virtual machine is optionally bundled depending on which version you downloaded.

Later versions may change this.

Non-bundled versions can use a current Java JVM (e.g OpenJDK) installed in the /Library/Java/ directory

Apple Silicon / M1 / ARM

This release will run natively on Apple Silicon.

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.

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

Getting Started

When you open SQLEditor you see the canvas, this represents your database design. You can add tables to this by Choosing Object Menu→Add Table, . 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 panel on the right side of the document window 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 Menu→Object Sidebar→Show Inspector)

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

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

There is online help available Help Menu→SQLEditor Help and a tutorial

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 supports both simple column to column foreign keys and also foreign keys involving multiple columns.

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

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.

In the Preferences→Data types panel, the button Reset all dialects will reload all type lists to the default lists provided with SQLEditor.

SQLEditor may update its list of types with version updates, so if you delete a type it may re-appear the next time we update the types list.

Database Export

Database export may cause data loss

When you use File Menu→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 SQLEditor Menu→Check for Updates

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 Menu→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 was 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.

SVG Export

SVG Export is now available in SQLEditor, use the File Menu→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.

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 Menu→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 Menu→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.

Microsoft SQL Server Support

There is some support for Microsoft SQL server. However this is still less developed than some other dialects.

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.

Check Constraints

Check constraints are new in v3.1

Check constraints appear on individual fields and on tables.

Check constraints can have a constraint name specified.

To add a table level check constraint, use the Object→Add constraint. Then use the inspector to change the constraint type to "CHECK"

Add a field level check constraint using the field inspector.

When exporting to the database, check constraints will be added or modified, but will not be dropped entirely unless the Drop Objects section "Check Constraints" check box is checked. This is to prevent check constraints that were added separately from SQLEditor from being deleted. It is recommended to add check constraints to your SQLEditor document for any missing constraints.

Dark Mode

SQLEditor 3.5 and later support Dark Mode user interface on Mac 10.14 and later.

SQLEditor 3.7 and later additionally support dark content mode, where the canvas and objects are drawn with dark background and light colored objects.

By default SQLEditor will always use a light content mode, but changes the user interface to match the system setting.

In addition to matching the system setting, there are 3 further options for this setting

When saving an image, you can now choose dark or light content mode in the save panel. The preset choice should match the document setting, and this is normally recommended.

When printing in dark content mode, the document will be temporarily converted to light mode. This may affect colors if they were chosen for dark mode.

It is recommended to choose light content mode if the document is intended for printing

A different set of default object colors is maintained for light and dark mode, so if you change these, the appearance of the document will differ between light and dark modes. This particularly applies to the default label color, which if changed will only affect the current appearance setting (light OR dark, not both).

Feedback on dark content mode (or any other feature of SQLEditor) is much appreciated.

Registration and Serial Numbers

Please support SQLEditor development and buy a license.

You can try SQLEditor 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

If you have a license purchased after 1st January 2020 then SQLEditor 4 will accept this and you can use it immediately.

If you have an older license, then you will need to upgrade it.

To upgrade to SQLEditor 4 visit the upgrades page

serial numbers

Release versions of SQLEditor will ask for a serial number when they start up. Version 3.0 is a paid upgrade.

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 send bugs or suggestions to us via our bug reporting tool. You can also choose whether to attach log files and system info.

Alternatively you can send us email at:

support@malcolmhardie.com

We promise a real person will read every (non-spam) email and we read all reports sent via our reporting tools.

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-2016 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 Menu→About SQLEditor.