SQLEditor For Mac OS X Readme File
MalcolmHardie Solutions Ltd
Angus Hardie <Angus.Hardie@malcolmhardie.com>
- About SQLEditor
- Getting Started
- Usage Notes
- Database Specific Notes
- Registration and Serial Numbers
- Feedback and Contact Information
- Legal Information
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.
If you decide to use SQLEditor with your database it's worthwhile having some kind of backup available.
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 1.6 Releases
Version 1.6 is a major update of the SQLEditor. Several major components have been entirely rewritten to improve performance and compatibility. There may be some areas where the new components do not work quite the same as the old ones. In particular the SQL parser may interpret some things that the old one did not and there are probably some cases where the reverse is true.
If you spot any issues with SQLEditor please do report it to us at email@example.com.
If you tried beta versions in the SQLEditor 1.5 series then you will have seen many of these new components already.
- Mac OS X 10.5.8 or later
- To use JDBC support you need a database with a JDBC driver
Mac OS X 10.6 or later is strongly recommended.
SQLEditor works on OS X 10.7/Lion.
and support for 10.5 is likely to be limited.
SQLEditor on OS X Lion/10.7
SQLEditor uses Java, which is an optional install on 10.7
Java will automatically be installed by OS X the first time you open SQLEditor (or any other program that uses Java)
If you prefer you can install Java by downloading an installer directly from Apple
SQLEditor is a universal binary. This means that it will work both on PowerPC based macs and Intel based Macs.
InstallationTo 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
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 Object menu 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
New Display StyleFollowing 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.
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 TypesSQLEditor 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.
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 uncheckedDrop 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 firstname.lastname@example.org.
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:||
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.
SQLEditor can be set to automatically save a copy of your document at regular intervals to guard against crashes or unexpected power failures.
The auto-save is made to a special separate file that is stored in a standard location inside the Library folder.
It does not affect your document file
Internet Version CheckingAs 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)
Database Specific Notes
Ruby On Rails Support
SQLEditor can import and export Ruby on Rails ActiveRecord migration and schema class files.
- Ruby On Rails Migration Export support is available from version 1.3
- Ruby On Rails Migration Import Support is available from version 1.4b8.
- Ruby On Rails Schema.rb file import and export is available from version 1.6.2
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.
Please be cautious!
PostgresSQLEditor 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 SupportSupport 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.
FilemakerSupport for filemaker is now being added to SQLEditor. The current version should be able to import from filemaker and export.
The Filemaker dialect should NOT be used for exporting yet, because filemaker doesn't accept SQL importing using its own data types.
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 email@example.com if you have comments or suggestions on how this might be improved.
Registration and Serial NumbersSQLEditor 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 storeRelease 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 firstname.lastname@example.org 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.
- Johan Basberg for Icons, toolbar graphics and lots of really useful ideas
- Leynos and Bruce for helping to test alpha versions
- Dhruba for some extremely useful comments
- 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
- ANTLR by Terrance Parr
- Objective PCRE by Christopher Bess
- PCRE Library by Philip Hazel
- Ruby On Rails Plugin:
- 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
Feedback and Contact InformationWe 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:
This is the best way to ensure that your comments get read. We promise a real person will read every (non-spam) email.
For sales queries please contact email@example.com
To buy a copy please visit our online store.
SQLEditor, this document and all related materials are Copyright (c) 2003-2010 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.
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
Additional legal noticesThere 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.