SQLEditor For Mac OS X Readme File
MalcolmHardie Solutions Ltd
|2018-11-07||Updated for SQLEditor 3.5 Release|
- About SQLEditor
- Getting Started
- Usage Notes
- Database Specific Notes
- Check Constraints
- 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 3.0 Releases
Version 3.0 is a major update of the SQLEditor.
The SQLEditor file format remains the same and files can be shared between SQLEditor 2 and 3, however the new object types will be lost if a file is opened in an earlier version of SQLEditor and then saved out. Because SQLEditor uses auto-saving, this may happen unexpectedly, so please make a copy of your files.
If you spot any issues with SQLEditor please do report it to us at firstname.lastname@example.org.
- Mac OS X 10.9 minimum
- Mac OS X 10.12 or later is recommended.
We expect to increase the minimum Mac OS version requirement for SQLEditor in the near futureSQLEditor 3 includes built in database drivers for Postgres, Drizzle, MySQL and SQLite.
Accessing other database systems will require installation of a suitable JDBC database driver.SQLEditor 3 includes a bundled java runtime and can use JDBC drivers
even if you don't have a Java runtime installed on your Mac.
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.
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
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
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:
- 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
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.
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.
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 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 email@example.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:||
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 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 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.
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
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 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.
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.
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.
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 firstname.lastname@example.org if you have comments or suggestions on how this might be improved.
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.
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
If you have a license purchased after 1st February 2014, then SQLEditor 3 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 3.0 visit the upgrades page
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 email@example.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.
- Johan Basberg for Icons, toolbar graphics and lots of really useful ideas
- Alexandra Hardie
- John Malcolm
- David McIntosh, Rebecca Cran, Dhruba Bandopadhyay
- Kate Ho, George Plymale, Karl Timmermann, Chris Ogden
- 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 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:
We promise a real person will read every (non-spam) email and we read all reports sent via our reporting tools.
For sales queries please contact firstname.lastname@example.org
To buy a copy please visit our online store.
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.
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 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 Menu→About SQLEditor.