Table of Contents

Using the EMS MySQL Manager User’s Guide

CHAPTER 1 GENERAL INFO
What is MySQL Manager?
What do you need to start working with MySQL Manager
How to purchase and register MySQL Manager
Other EMS HiTech Software
EMS MySQL Manager FAQ
CHAPTER 2 DATABASE MANAGEMENT
Creating Database
Registering Database
Connecting to the Database
Register Host Wizard
Database Explorer
New Object / Duplicate Object

CHAPTER 3 DATABASE OBJECTS: TABLES AND UDFS
Creating Table
Table Editor
Table Data Management
UDF Editor

CHAPTER 4 DATA MANIPULATION
Export Data Dialog
Export Data as INSERT Dialog
Import Data Wizard
Load Data Wizard

CHAPTER 5 DATABASE TOOLS
SQL Editor
Visual Query Builder
SQL Monitor
SQL Script Editor
Extract Metadata Expert
Print Metadata
HTML Report
Report Designer
User Manager
Grant Manager
Visual Database Designer

CHAPTER 6 DATABASE SERVICES
Backup Tables
Restore Tables
Flush
Analyze Tables
Check Tables
Repair Tables
Optimize Tables
Ping Server / Shutdown Server

CHAPTER 7 MYSQL MANAGER OPTIONS
Environment Options
Editor Options
Visual Options
External Tools
Keyboard Templates
Plugins options
Save Settings Wizard
Select Program Language

Using the EMS MySQL Manager User’s Guide

The EMS MySQL Manager User’s Guide will help you to master EMS MySQL Manager – the best tool for MySQL administration and development. You can read this guide either as a whole, from the beginning to the end, or only the selected chapters. If you have no experience in working with our product, this guide will thoroughly explain you, how to perform basic MySQL operations in MySQL Manager: creating and registering databases, creating and editing tables and UDFs, building queries and so on. You will learn how to work with such MySQL Manager tools as SQL Editor, Visual Query Builder, Grant Manager, User Manager, SQL Script, Visual Database Designer, HTML Report, Report Designer and others; how to export and import data, print and extract metadata and many more. EMS MySQL Manager User’s Guide also gives explanation of all the MySQL Manager options, which allow you to customize your work with the program. If you have already worked with the MySQL Manager and you just want to make some questions clear, you can easily find what you want, using the table of contents and the following chapter descriptions. Chapter 1 – General Info contains information about general features of MySQL Manager, and tells you what the MySQL Server is and how to download it. Section How to purchase and register EMS MySQL Manager describes the process of registering MySQL Manager. If your interests lie beyond the bounds of MySQL, you should certainly read section Other EMS HiTech Software to learn about other useful programs and components, developed by EMS HiTech. The FAQ section contains answers to most of the questions users usually ask about MySQL Manager. Chapter 2 - Database Management describes how to perform general database operations in MySQL Manager: create database and register database in the program; connect to database and create new database object, etc. You will undoubtedly need this chapter to learn how to work with the Database Explorer – the basic MySQL Manager tool for database navigation and management.

Chapter 3 - Database Objects: Tables and UDFs fully describes such processes as creating a new
table, creating and editing fields and indices for table, managing table data (including BLOBs), and
many more operations that can be performed within the powerful tool for table management –
MySQL Manager Table Editor. The last section of the chapter is devoted to the UDF Editor, which
allows you to declare new User-definable Functions and edit the existing declarations.
MySQL Manager provides very effective tools to make data import and export simple and fast. If
you want to import your data from MS Excel, DBF or TXT files, or export them to Excel, HTML,
RTF or some other format, you will not do without Import Data Wizard and Export Data Dialog,
and Chapter 4 - Data Manipulation will thoroughly explain you how to use them. Also this
chapter describes Export Data as INSERT Dialog, which allows you to export data to SQL script,
and Load Data Wizard, which automates executing the MySQL function LOAD DATA INFILE.
Chapter 5 - Database Tools is one of the most important chapters of the book, as it guides you
through creating and executing database queries, using such powerful tools, as SQL Editor and
Visual Query Builder. The last one allows you to build and execute queries, even if you don’t know
SQL syntax at all! Using MySQL Manager Database Tools you can also monitor SQL code for all
database operations, execute SQL scripts, extract and print metadata, administrate users and grants.
This chapter will tell you in detail how to use these tools.
5
MySQL Server provides a lot of services to keep your table data safe and correct: backup/restore
tables, analyze, check tables, and more. In MySQL Manager you can perform these operations
visually for all database tables at once, or for the selected tables only, with all options, available in
MySQL. Even if you new to MySQL, Chapter 6 - Database Services will give you the detailed
explanation on each service and option.
MySQL Manager is a very customizable product. You can customize almost everything: how
program windows look and behave; various details of what database tools do; data grid formats;
program language; SQL text attributes; and many, many more. To grasp the meaning of all these
options read Chapter 7 - MySQL Manager Options, each section of which corresponds to the
proper Options menu item.

CHAPTER 1
GENERAL INFO
What is MySQL Manager?

EMS MySQL Manager provides you powerful and effective tools for MySQL Server administration and object management. Its Graphical User Interface (GUI) allows you to create/edit all MySQL database objects in a most easy and simple way, run SQL scripts, manage users and administrate user privileges, visually build SQL queries, extract or print metadata, export/import data, view/edit BLOBs and many more services that will make your work with the MySQL server as easy as it can be...

Version 2.0 features
The Linux Version of MySQL Manager is immediately available
Now our software became cross-platform. MySQL Manager for Linux is the same powerful tool,
providing GUI for all the database operations, allowing you create and drop databases,
create/alter/drop tables, fields and indices, and many more. See the Feature Matrix for details or visit our web-site: http://www.mysqlmanager.com.
Visual Database Designer
Design your database visually - it takes seconds! See Visual Database Designer to learn how.
MDI Interface implemented
Microsoft Office Style - all program windows are child windows of the main window now. You can
customize the Environment Style using the Environment Options window.
Foreign key support for InnoDB tables
Now MySQL Manager supports InnoDB foreign keys. See Foreign Keys for details.
HTML Report Generator
Create a detailed report about your database in HTML format. Take a look at HTML Report.
Third-party plugin support
Install third-party plugins in MySQL Manager and make your work with MySQL even easier! See
Plugins and External Tools.
Find and replace functions implemented in the Localization Editor
Editing localizations becomes easier! See Localization Editor for details.

What do you need to start working with MySQL
Manager
EMS MySQL Manager is developed for working with the MySQL Server, so first of all you must
have a possibility to connect to some local or remote MySQL Server to work with MySQL
Manager. Besides you need your computer to meet the minimal system requirements. Pentium 166
and 32 MB RAM recommended.
What is MySQL?
MySQL is the world's most popular Open Source Database, designed for speed, power and
precision in mission critical, heavy load use. The MySQL database server embodies an ingenious
software architecture that maximizes speed and customizability. Extensive reuse of pieces of code
within the software and an ambition to produce minimalistic but functionally rich features has
resulted in a database management system unmatched in speed, compactness, stability and ease of
deployment. The unique separation of the core server from the table handler makes it possible to
run MySQL under strict transaction control or with ultra fast transactionless disk access, whichever
is most appropriate for the situation.
Today MySQL is the most popular open source database server in the world with more than 2
million installations powering websites, datawarehouses, business applications, logging systems
and more. Customers such as Yahoo! Finance, MP3.com, Motorola, NASA, Silicon Graphics, and
Texas Instruments use the MySQL server in mission-critical applications.
You can download MySQL Server from http://www.mysql.com/downloads/index.html (download
is free).
How to purchase and register MySQL Manager
For your convenience, we have contracted with RegSoft and ShareIt Companies to process orders
you wish to make with your Visa, MasterCard, American Express and Discover. After registering
you will receive the registered version within 48 hours by e-mail. Please make sure to include a
valid e-mail address with your order.
ShareIt (http://www.shareit.com) accepts payments in US Dollars, Euro, Pound Sterlings, Japanese
Yens, Australian Dollars, Canadian Dollars or Swiss Franks by Credit Card (Visa,
MasterCard/Eurocard, American Express, Diners Club), Bank/Wire Transfer, Check or Cash.
RegSoft (http://www.regsoft.com) accepts payments in US Dollars by Credit Card (Visa,
MasterCard/Eurocard, American Express, Discover), FAX, Postal Mail, TOLL-Free Phone or
Purchase Order.

Product Description Price Share It! RegSoft.com
EMS MySQL Manager Professional Edition for
Windows (single license)
$135 Register Now! Register Now!
EMS MySQL Manager Professional Edition for
Windows (site license)
$895 Register Now! Register Now!
EMS MySQL Manager Lite Edition for Windows
(single license)
$85 Register Now! Register Now!
EMS MySQL Manager Lite Edition for Windows
(site license)
$595 Register Now! Register Now!
Other EMS HiTech Software
EMS IB Manager (http://www.ems-hitech.com/ibmanager/) provides you with
effective and powerful tools for InterBase/FireBird administration. It helps you to edit
all database objects, search in metadata, extract metadata, print metadata, import data
and export it into as many as 12 most popular formats. IB Manager also includes
Database Designer, SP Debugger, SQL Editor, Visual Query Builder, Grant and User
Managers, BLOB Viewer/Editor, SQL Script processor, Third-Party plugins support and many
more other features.
EMS PostgreSQL Manager (http://www.ems-hitech.com/pgmanager/) is a powerful
graphical tool for PostgreSQL administration and development. It makes creating and
editing PostgreSQL database objects easy and fast, and allows you to run SQL scripts,
manage users and their privileges, build SQL queries visually, extract, print and
search metadata, export data to 14 available formats and import them from most
popular formats, view and edit BLOB fields, and many more...
EMS MySQL Utils (http://www.ems-hitech.com/mysqlutils) are powerful data
management utilities for MySQL Server, which make your work with the server much
easier and faster. Currently MySQL Utils include MySQL DataPump - a wizard
application for converting ADO-compatible databases to MySQL, MySQL Export - a
powerful tool for MySQL data export, and MySQL Import - an utility for quick
importing data to MySQL tables.
EMS PostgreSQL Utils (http://www.ems-hitech.com/pgsqlutils/) are powerful data
management utilities for PostgreSQL Server, which make your work with the server
much easier and faster. Currently PostgreSQL Utils include PostgreSQL DataPump
- a wizard application for converting ADO-compatible databases to PostgreSQL,
PostgreSQL Export - a powerful tool for PostgreSQL data export, and PostgreSQL
Import - an utility for quick importing data to PostgreSQL tables.
EMS QuickExport Component Suite (http://www.ems-hitech.com/quickexport/) is
a set of native Delphi/C++Builder components for exporting your data to 12 most
popular formats (MS Excel, MS Word (RTF), HTML, XML, TXT, CSV, SYLK, DIF,
LaTeX, SQL and Windows Clipboard) for the future viewing, modification, printing
or web publication. There will be no need to spend your time for a tiresome data
11
conversion - EMS QuickExport will do this task quickly and it will give the result in the desired
format.
EMS QueryBuilder (http://www.ems-hitech.com/querybuilder/) is a powerful
component intended for visual building SQL statement for the SELECT clause. It
enables you to use visual query building or representing the existing statements in
your project on Delphi. The component can work with different databases (not only
through BDE). It simplifies writing a large and complicated statement and allows
making up SQL statement without knowledge of the SQL syntax.
EMS QuickImport Component Suite (http://www.ems-hitech.com/quickimport/)
allows you to import your data to the database from files in the most popular data
formats. There will be no need to spend your time for a tiresome data conversion -
EMS QuickImport will do this task quickly, irrespective of the source data format.
EMS QuickLocalizer (http://www.ems-hitech.com/quicklocal/) is an indispensable
component suite for adding the ability of multilingual support to your Delphi
applications. Using powerful component editors of this suite you can easily and
quickly localize the properties of your project components within each form, generate
the template of language file containing current values of component properties,
manage the localization files, specify the components and properties to be localized and choose
other localization options.
EMS ExcelReport (http://www.ems-hitech.com/excelreport/) component is a
powerful band-oriented generator of template-based reports in MS Excel. Easy-to-use
component property editors allow you to create powerful reports in MS Excel quickly,
easily and intuitively understandable. Now you can easily create reports, which can be
edited, saved to file and viewed almost on any computer. ExcelReport supports
Borland Delphi 5, 6, 7, and MS Office 97 SR-1, 2000, 2002 (XP).
EMS MySQL Manager FAQ
Please read this page attentively if you have questions about EMS MySQL Manager.
Q: What is EMS MySQL Manager?
A: EMS MySQL Manager provides you with powerful and effective tools for MySQL Server
administration and objects management. It allows you to create and edit all MySQL database
objects easily, run SQL scripts, manage users and administrate users' privileges, visually build SQL
queries, extract or print metadata, export/import data, view/edit BLOBs and includes many more
services to make your work with MySQL server as easy as you want...
Q: How can I register EMS MySQL Manager?
A: All the information about purchasing EMS MySQL Manager can be found at
http://www.mysqlmanager.com/purchase.phtml.
Q: I am a registered user of EMS MySQL Manager 1.0. How can I upgrade to the new
version of MySQL Manager?
A: According to our Upgrade Policy you can make upgade from version 1.XX to version 2.01 for
half a price. If you are a registered user of the 1.XX version you have to pay only 50 percent of the
12
announced price to buy the version 2.01.
After you upgrade to version 2.01 you will receive all the MySQL Manager upgrades labeled 2.01
up to 2.99 as they are released for free.
Q: Where can I download a trial version of EMS MySQL Manager?
A: You can always download the latest version of EMS MySQL Manager at
http://www.mysqlmanager.com/download.phtml.
Q: How does the trial version of EMS MySQL Manager differ from the registered version?
A: The trial version of EMS MySQL Manager is fully functional. You can use it for evaluation
purposes for a period of 30 days following the initial installation.
Q: What benefits shall I acquire if I register EMS MySQL Manager?
A: As a registered user you will have a right to obtain a technical support, to receive information
about all the product updates and to have free in-line upgrades and full version upgrades for half a
price. Also your suggestions will be taken into consideration in developing the new versions of
MySQL Manager. And at last we will thank you very much for your help in developing the product.
Q: What is the difference between Professional/Lite and Windows/Linux editions of MySQL
Manager?
A: These editions of MySQL Manager differ in price and features. To learn how to register MySQL
Manager see Purchasing MySQL Manager and to learn the difference in features see the Feature
Matrix.
Q: What is the difference between single and site licenses of MySQL Manager?
A: If you buy a single license of MySQL Manager you will get only one registered copy of the
product without a right of giving it to anyone else. If you buy a site license then you will be able to
make copies and give them to as many people as you want, but within the only one organization.
Buying a site license is reasonable if you need to supply with our software all your company stuff or
some company department stuff. In such case you can buy a site license instead of buying single
licenses for each person and save a bunch of money.
Q: What discounts can I get buying EMS MySQL Manager?
A: You can get significant discounts if you simultaneously purchase several copies of MySQL
Manager. Each additional copy will be cheaper than the previous.
If you are a representative of some academic institution and you want to use MySQL Manager for
educational purposes then you can buy an Academic License that is much cheaper than the standard
license. See our License page (http://www.ems-hitech.com/license-pol.phtml) for details or send us
a written request at [email protected].
Q: What do I need to start working with EMS MySQL Manager?
A: First of all you must have a possibility to connect to some local or remote MySQL Server to
work with MySQL Manager. You can download MySQL Server from
http://www.mysql.com/downloads/index.html (download is free).
Besides you need your computer to satisfy the system requirements of MySQL Manager. MySQL
Manager runs on Windows 95/98/Me/NT4/2000/XP and Pentium 166, 32 Mb RAM is
recommended.
13
Q: I need to work with several servers, located on different computers, is single license OK?
A: If you work with these servers from one single computer, then a single license is OK. But if you
have several clients installed on different computers, you have to buy a license for each client or
buy a site license.
Q: Is registered copy of EMS MySQL Manager locked to the definite computer?
A: No, we do not lock registered copies of our products to user's hardware ID, so if you change
your hardware it will not cause any problems with using your registered copy of MySQL Manager.
Q: What is the difference between the Export/Import functions in the MySQL Manager tool
and the MySQL Export/Import utilities that you sell separately?
A: MySQL Export/Import (see http://www.mysqlutils.com for details) include some additional features,
which are not available in MySQL Manager such as:
• export/import data from/to several tables at once;
• export/import data from/to tables selected from different databases on one host;
• possibility of working under Linux;
• command line utility to export/import data using the configuration file with all the
export/import options.
If you still have any questions, write us to [email protected].
14
CHAPTER 2
DATABASE MANAGEMENT
Creating Database
If you have no database on local or
remote server, you can create a new
database right from the MySQL
Manager. To do this click button
Create Database on the control
panel or choose the menu item
Database | Create Database.
The first step of the Create
Database Wizard is ‘General
Options’.
Set the name of the new database,
and then set the following database
properties:
?? Host - the server, where your
database will be situated (default
‘localhost’ means that the server
is situated on your machine);
?? Port – the TCP/IP port for
connecting to the server;
?? Login – the name, by which you
are registered on server;
?? Password - your password for
connecting to the server.
The Register After Creating option
indicates, that the Register Database
Dialog for the new database will
15
appear right after creating the database (you need to register the database to start working with it in
MySQL Manager). If you want to register your database later, uncheck this option.
When you are done, click ‘Next’ to continue.
On the last step of the wizard you can view the result SQL statement for creating the database. You
can’t edit it. To finish the wizard and start creating the database, click ‘Create’.
Registering Database
To make the database you created or an already existing database available for working in MySQL
Manager, you should register it. If you created this database in MySQL Manager, and Register
after creating was checked, then the Register Database Dialog is displayed automatically,
otherwise you should click button Register Database on the control panel or choose the menu
item Database | Register Database.
This is the ‘General’ tab of the Register Database window.
On the ‘Connection Info’ panel the database
server connection parameters are set: host, port,
user name and password. These parameters
should correspond to those set on creating the
database.
Select the database name from the ‘Database
Name’ drop-down list of all the databases
available on the server and set the database alias
in the ‘Database Alias’ edit field (choose any
alias that suits you; the default alias is
‘<database_name> on <host>’). Set the
character set to use in the data grids in the
‘Database Font Charset’ edit field. If you choose
‘NONE’ (default), then the default Windows
charset will be used.
The following options are also available:
?? Login Prompt Before Connection - if this option is checked, MySQL Manager will ask you to
enter your login and password on each connection to the database.
?? Quote Identifiers - if this option is checked, all the identifiers will be quoted by the back quote
symbol ('`'). To make SQL scripts, obtained with the Extract Metadata Wizard, compatible
with elder versions of MySQL, uncheck this option. Note that this option will work only with
those servers that support quoting names.
?? Use SSL protocol - check this option to use the encrypted protocol when connecting to the
database server. Your version of MySQL Server must support SSL protocols for you could use
this option.
?? Use compression protocol - check this option to use the compressed data protocol when
connecting to the database server.
16
On the ‘Directories’ tab you can set the default directories for extracting database metadata,
exporting data from the table or query result, and importing Excel, DBF or text files to the table.
On the ‘Logs’ tab you can enable logging changes in the database
metadata (‘Enable log of metadata changes’ option) and enable logging
queries executed in the SQL Editor or Visual Query Builder. After
checking these options you should set filenames for storing this
information in the ‘Metadata log file’ and the ‘SQL Editor log file’ edit
fields.
Click button ‘Test Connect’ to check the connection with the database
server. Button ‘Copy Alias from’ allows you to copy the registration
parameters from one of the already registered databases and apply them
to the current database.
When you are done, click ‘Register’. If everything was correct, your
database will be registered, and its icon and alias will appear in the DB
Explorer window, on the ‘Databases’ tab.
To unregister database, click button Unregister Database on the control panel or choose the
menu item Database | Unregister Database.
17
Connecting to the Database
To start working with the registered database, you should
connect to it. Just double-click the database alias in the DB
Explorer or click button Connect to Database on the
control panel to start connecting.
If connection is successful, the database alias changes its
appearance, and the ‘Tables’ and ‘UDFs’ branches become
available. These branches contain lists of database tables
and UDFs. After connecting to the database you can create
new objects or edit the existing through the popup menu of
DB Explorer.
To disconnect from the database, click button Disconnect
from Database on the control panel.
Register Host Wizard
If you need to register several
databases on one server, you can
register all them at once, not
registering them one by one. To do
that you should use the Register
Host Wizard. This wizard allows
you to register “packs” of databases
on one server.
To activate the wizard, click button
Register Host on the toolbar or
choose the menu item
Database | Register Host.
On the first step the wizard - ‘Connection Info’ - you should set the server connection properties,
which are common for all the databases you register:
?? Host - server, where your databases are situated;
?? Port – TCP/IP port for connecting to the server;
?? Login – name, by which you are registered on server;
?? Password - your password for connecting to the server.
On the ‘Databases’ step select the databases to register from those available on the server by
moving them from the ‘Available Databases’ list to the ‘Selected Databases’.
18
To move all the databases from one list to another use buttons >>, <<; to move the selected
databases, use buttons >, < or drag the databases; to move one database, just double-click it.
The last step of the wizard is ‘Registration Options’. Select databases in the list and set the
following registration options for each of them. These properties are the same as in the Register
Database Dialog (see above).
Click ‘Register’ when you are done to register all the databases on the host.
To unregister host, click button Unregister Host on the control panel or choose the menu item
Database | Unregister Host.
Database Explorer
DB Explorer is the basic MySQL Manager navigation
tool for working with databases and database objects.
Its control panel and popup menu allow you to perform
various metadata and data operations, such as: registering
and connecting to the database, creating, editing and
dropping tables and UDFs, exporting and importing data
and so on.
DB Explorer tabs allow you to access all the registered
databases and database objects (‘Databases’ tab), create
your own projects to work only with the selected objects
(‘Projects’ tab), access any of the MySQL Manager active
windows (‘Windows’ tab) and recently edited objects
(‘Recent’ tab). For easier navigation between the objects
each tab has its own object tree.
The SQL Assistant area gives you short information for
each database or database objects, e.g. object description or list of its subobjects.
Control Panel
Quick Jump To Database – clicking this button activates the drop-down list of all registered
databases. Choose the required database from the list to select it in the tree.
Register Database / Unregister Database - these two buttons allow you to register new
database or to unregister the selected database.
Register Host / Unregister Host - these buttons allow you to register several databases on
the host or to unregister the current host.
Connect to Database / Disconnect from Database - use this buttons to connect to the
selected database or to disconnect from one.
Refresh - this button refreshes the object tree.
View Mode - using the drop-down menu of this button you can adjust the DB Explorer
appearance. The following items are available in the menu:
Show Table Subobjects - if this button is dropped, then the table fields and indices are available
in the database tree of DB Explorer.
19
Page Mode – this button enables the DB Explorer page mode, i.e. splitting the DB Explorer
window in two and displaying the ‘Projects’ tab in the right area. This mode allows you to drag
objects from the ‘Databases’ area to the ‘Projects’ one.
Show Hosts - if this button is dropped, the database hosts are visible in the DB Explorer tree.
Tables’ Details – this submenu allows you to switch the SQL Assistant mode for displaying table
fields, indices or table status (table properties set on creating).
Popup menu
New Object – this item allows you to create a new object of the current
type (table or UDF).
Edit Object – this item allows you to edit the current table or UDF in
the proper object editor.
Drop Object - this item allows you to drop the current object.
Rename Object - this item allows you to edit the alias of the current
object.
Duplicate Object - this item allows you to create a new object with the
same properties as the selected object has. E.g. if you duplicate a table,
the new table will have the same fields, indices, data and other properties.
They will only differ in names (you’ll be asked for a new table name
when duplicating).
Data Manipulation - this submenu is available only if a table is selected.
Its items allow you to export data, export data as INSERT statements,
import data and load data to the selected table.
Object Properties - this item allows you to edit the object properties, set
on its creation.
Grants for … – this item activates the Grant Manager, which allows you to set the access grants
for the selected object.
Find Item - this item allows you to find an object in the object tree by the first symbols of its name.
Refresh - this item refreshes the object tree.
Connect to Database / Disconnect from Database – these items allow you to connect/disconnect
to/from the current database.
Register Database / Unregister Database – these items allow you to register new database or to
unregister the selected one.
Database Registration Info - this item activates the Database Registration window, which allows
you to view and edit the database registration parameters.
Sort by Aliases - this item sorts objects in alphabetical order by their aliases.
Register Host / Unregister Host – these items allow you to register several databases on the host
or to unregister the current host.
View Toolbar - if this option is checked, DB Explorer control panel is visible.
View SQL Assistant - if this option is checked, SQL Assistant area is visible.
Hide Disconnected Databases - if this option checked, databases not connected to server, are not
displayed in the object tree.
Projects
This page is provided for working with the selected database objects. You can place objects from
the database object tree and queries from SQL Editor here.
To move the objects from the tree, switch the DB Explorer view mode, using button Page
Mode of the View Mode menu on the toolbar. Now the ‘Projects’ area is always displayed at
the right of the window, and you can drag here objects from the ‘Databases’ area. To add query
20
from the SQL Editor, select the text in the editor window and drag it to the folder, created in
advance. To create a folder or a subfolder, right-click the object name and choose New Folder or
New Subfolder in accordance.
If you need to find the Project tree object in the Databases tree, right-click the object and choose
item 'Find Object in Database Tree'. Databases tab will be displayed, and the object you need will
be selected.
New Object / Duplicate Object
This window provides an alternative way of
creating databases objects. To activate this window
choose the menu item Database | New Object or
Database | Duplicate Object.
First select a database, where a new object should
be created, from the ‘Database’ drop-down list.
On the ‘New’ tab select the object type and click
'OK' to activate the Create Table Dialog or the
UDF Editor for setting the properties of the new
object.
To create a new object with the same parameters as
one of the existing database objects has, use the ‘Duplicate existing’ tab. Select an object type,
using toolbar at the right of the window, then select an object to duplicate and click 'OK'. Set the
name of the new object in the dialog window and the new object will be created.
21
CHAPTER 3
DATABASE OBJECTS :
TABLES AND UDFS
Creating Table
To create a table, select the ‘Tables’ branch in the database
tree on the ‘Databases’ tab of the DB Explorer, right-click and
choose item ‘New object’ from the popup menu.
On the first step of the Create Table Dialog you should set
various table parameters.
Set the table name in the ‘Table’ edit field. Choose the table type using the ‘Table Type Preference’
switch and the ‘Table Type’ drop-down list. The following table types are available:
Non-transaction-safe types
?? MyISAM - the new binary portable table handler
that is replacing ISAM;
?? ISAM - the original table handler;
?? HEAP - the data for this table is only stored in
memory;
?? MRG_MyISAM - a collection of MyISAM tables
used as one table.
Transaction-safe types
?? Berkeley_db - transaction-safe tables with page
locking;
?? InnoDB - transaction-safe tables with row locking;
?? GEMINI - table type, developed by NuSphere Company. It is not supported in the last versions
of MySQL.
Select the row format in the ‘Row Format’ edit field: default, fixed, dynamic, or compressed.
22
Set the following table options:
?? Check Sum (MyISAM only) - check this option if you want MySQL Manager to maintain a
checksum for all rows (makes table a little slower to update but makes it easier to find the
corrupted tables);
?? Delay Key Write (MyISAM only) - check this option to delay key table updates until the table
is closed;
?? Pack Keys (MyISAM, ISAM only) - set this to 1 if you want to have a smaller index. This
usually makes updates slower and reads faster;
?? Min. Rows - minimum number of rows you plan to store in the table;
?? Max. Rows - max number of rows you plan to store in the table;
?? Temporary – check this option to create a temporary table.
A temporary table will automatically be deleted if a connection dies and the name is per connection.
This means that two different connections can both use the same temporary table name without
conflicting with each other or with an existing table of the same name. (The existing table is hidden
until the temporary table is deleted).
Use Union Tables when you want to use a collection of
identical tables as one. This only works with
MRG_MyISAM tables. Click the button next to the ‘Union
Tables’ edit field. In the Union Tables List window you
can add a new table from the current database to the ‘Union
Tables’ list by selecting it from the ‘Union Table’ dropdown
list and clicking 'Add'. You can edit the list by
removing the tables ('Remove' button), replacing them with
the new ones ('Replace' button), and changing their order
('Up' and 'Down' buttons).
On the second step of the dialog you should set the
properties of the first table field. Set the field name in
the ‘Column’ edit field, and select the data type for the
field from the ‘Type’ drop-down list. For some of the
types size and precision can also be set. Check ‘Not
null’ to forbid an empty field value. Set the following
field properties, if necessary:
?? Unsigned –
check this
option to
give the
UNSIGNED attribute to the field;
?? Zerofill - check this option to give the ZEROFILL
attribute to the field;
?? Binary - check this option to give the BINARY attribute
to the field;
?? Unique - check this option to include the field to the
unique key (index);
?? Autoincrement - check this option to give the AUTO_INCREMENT attribute to the field;
?? Primary Key - check this option to include the field to the primary key.
In the ‘Default Value’ edit field you can set the default field value.
23
If you have chosen the SET field type (see above), you can create a list of possible field values,
using the ‘Values’ edit field. Click the button next to the edit field to activate the Values List
window. Enter the value in the ‘Value’ edit field and click ‘Add’ to add new value to the set. You
can edit the ‘Values’ list by removing the values ('Remove' button), replacing them with the new
ones ('Replace' button), and changing their order ('Up' and 'Down' buttons).
Click ‘OK’ when you are done to create the new table with the parameters you set.
The created table will be opened in the Table Editor, where you can create and edit table fields and
indices, manage table data, and so on (see below).
The table will also become available in the database tree of
the DB Explorer.
Any time you want to edit the table, you can open it in the
Table Editor by right-clicking the table alias in the database
tree of DB Explorer and choosing item ‘Edit table
<table_name>’ in the popup menu. Using this popup menu you can also rename or drop the table.
Table Editor
Table Editor is a
powerful tool,
allowing you to
create, edit and drop
table fields and
indices, manage
table data and set
other table
properties. From the
Table Editor you
can activate various
MySQL Manager
tools for working
with the database table: Grant Manager, Print Metadata Dialog, BLOB Viewer/Editor and more.
In the left part of the window there is a tree of table’s
subobjects: fields and indices. It allows you to access the
required object quickly. Using the Databases dropdown
button on the editor toolbar you can switch between
the active databases. The drop-down list of all the
database tables on the toolbar allows you to change the
edited database.
To manage the table fields use the ‘Fields’ tab of the
Table Editor.
This tab displays all the table fields and their basic
properties (type, size, precision, etc.). To create a new
24
field right-click and choose item ‘New Field’ or ‘Insert Field’ (this item inserts new field after the
selected field).
To edit the field, double-click the field in the list, or select the field and choose item ‘Edit Field
<Field_Name>’ in the popup menu.
To drop the selected field, choose item ‘Drop Field <Field_Name>’ in the popup menu.
To create a new field with the same properties as the
selected field has, choose item ‘Duplicate’. You will be
asked for a new field name, and the field will be created.
To change the field order, choose item ‘Reorder Fields’
and set the new field order in the dialog window. Item
‘Copy List of Field Names to Clipboard’ allows you to
copy all the table field names to Windows Clipboard.
The properties of the field are set and edited in the Field
Editor. The edit fields of the editor correspond to the
edit fields of the Create Table Dialog, Step 2 (see
above). The only difference is that you can choose the
insert mode (insert the field last, first or after the field,
specified as ‘Insert After’).
When you are done, click ‘OK’ to create a new field for the table or to change its properties.
The ‘Indices’ tab of the Table Editor works in the same
way as the ‘Fields’ one. To create a new index for the
table, open this tab, right-click in the main area and
choose item ‘New Index’ from the popup menu. The
Index Editor will appear. Set the index name in the
‘Index Name’ edit field and select the fields for the index
by moving the fields from the ‘Available Fields’ list to
the ‘Included Fields’. To move the fields use buttons
between the lists, drag the selected fields, or double-click
one of the fields. Choose the index order (‘Ascending’ or
‘Not ordered’), set other index properties and click ‘OK’.
‘Foreign
keys’ tab are
available for InnoDB tables only and are supported
since MySQL version 3.23.44 (3.23.50 or higher
recommended). To view or change table type, use the
Table Properties dialog. To add the foreign key to table,
open ‘Foreign key’ tab, right-click and select Add
Foreign Key item from the popup menu. This opens
the Foreign Key Editor dialog, where you can select
fields for the key.
The Foreign Key Editor allows you to select fields for
the foreign key, when you add a foreign key to the
InnoDB table.
Select fields to include into the key by moving them
25
from the Available Fields list box to the Selected Fields.
Select a table to link with the fields selected above from the Foreign Table Name drop-down list.
Select foreign fields to link with the fields selected above by moving them from the Available
Fields list box to the Selected Fields.
Note that all fields included into the foreign key must be unique, i.e. included into the primary keys
first. If there are several fields included into the primary key, the field must be the first one in the
list.
The On Delete Rule drop-down list allows you to define an action for deleting one of the fields.
The following values are available:
?? CASCADE - deletes the corresponding foreign key;
?? SET NULL - sets all the columns of the corresponding foreign key to NULL.
The On Update Rule drop-down list allows you to define action for updating one of the fields. The
following values are available:
?? NO ACTION - does not change the foreign key; may cause the primary key update to fail
due to referential integrity checks.
?? RESTRICT - produces an error indicating that the update would create a foreign key
constraint violation.
?? CASCADE - updates the corresponding foreign key to the new value of the primary key.
?? SET NULL - sets all the columns of the corresponding foreign key to NULL.
Currently you can't edit or drop foreign keys. This isn't the limitation of MySQL Manager, but the
limitation of InnoDB foreign keys.
The ‘Description’ tab displays a simple edit area, where you can set optional text, describing the
current table.
The ‘DDL’ tab displays the SQL text for creating the current table with all the parameters you set:
fields, keys, table type, etc. This text can't be edited, but it can be copied to the clipboard.
The control panel of the Table Editor provides the following functions:
Databases - use this drop-down menu to switch between the databases. If you switch to the
database, which is not currently active, you’ll be offered to connect to it. After you switch to the
new database, the first table of this database becomes active in the Table Editor. To switch
between the tables, use the drop-down list of the current database tables in the middle of the panel.
Grants On Table - use this button to activate the Grant Manager for the current table, where
you can set the access grants for the table.
Save Description - this button saves the table description, set on the ‘Description’ tab.
Help on SQL - this button runs the MySQL Reference Help file ‘mysql.chm’, which is included
into the MySQL Manager installation package.
Print Table Metadata - this button allows you to print the table metadata: fields, indices,
description and DDL. The dialog window that appears after clicking this button also allows you to
preview the report before printing or design it, using the FastReport report designer.
26
Default Size - this button brings the window to its default size (restricted by the main window
and the DB Explorer).
Table Properties - this button allows you to edit the table properties, set on its creation: table
type, attributes, etc.
Commit Transaction / Rollback Transaction - use these buttons to commit or rollback the
current transaction.
Table Data Management
Table data are managed on the ‘Data’ tab of the Table Editor. Data can be displayed in three
modes:
?? Grid View - view data as a
grid. The columns
correspond to the fields,
rows - to the records. To
navigate through the table
data use navigation buttons
at the top of the area. Button
+ inserts a record at a current
position, button ? deletes the
record. To edit the record,
click it in the grid and enter
the new value. To confirm
the changes you made, click
button ??, to cancel them
click ??. Clicking the field title allows you to switch the mode of sorting records by this field
(ascending or descending). You can multi-select the fields by click the row captions with the
buttons Shift or Ctrl down. It allows you to delete several records at once.
?? Form View - view data as a form. On the form a current record is displayed: field name and its
value. This view mode also has a control panel, which allows you to switch the records. If these
data are available for editing, you can do it on this form. Each field has a Null switch, which
allows you to clear the field value quickly; near each number field a calculator button is placed
(calculator field automatically fills the current field); near each date field calendar button is
placed (date, selected in the calendar, automatically fills the current field); near each BLOB
field the button for calling the BLOB Editor (see below) is placed.
?? Print Data - data are displayed in the way they will be printed, in WYSIWYG mode. You can
change the view scope, save a report to file and set page parameters.
To switch these modes use buttons at the bottom of the main area.
Button Export Data on the toolbar allows you to export table data to file (14 formats are
supported: MS Excel, HTML, TXT, DBF, RTF and more). Button Export Data as INSERT
Statements allows you to export data to the SQL script as SQL statement INSERT.
27
Using button Import Data
you can import data from MS
Excel, MS Access, DBF, TXT or
CSV file. Load Data reads
rows to the table from the text file.
If there are BLOB fields in the
table, you can edit them, using
the BLOB Viewer/Editor. The
editor is called by clicking button
BLOB Editor on the control
panel.
The window of the editor is divided into two areas: object tree area and data view/edit area. In the
object tree you can view the current database and the database table, edited field belongs to, and in
the data area you can view BLOB data in different formats by choosing the proper tab
(‘Hexadecimal’, ‘Text’, ‘Rich Text’, ‘Image’ and ‘HTML’).
Data, performed as Image or HTML are not available for editing. Image tab supports the following
image formats: bitmaps, WMF, icons, JPEG and GIF.
The toolbars of the editor allows you to load and save files (buttons Load from File and Save to
File ), navigate through records (the navigation panel is the same as on the ‘Data’ tab of the
Table Editor) and provide various tools for editing text.
When you are done editing BLOB, just confirm the changes you made and close the editor.
BLOB Viewer/Editor is available only in the Professional Edition of MySQL Manager.
UDF Editor
The UDF Editor allows you to declare new UDFs and
to edit the existing declarations. To declare new UDF,
right-click on the ‘UDFs’ branch in the database tree of
the DB Explorer and choose item ‘New Object’ in the
popup menu. To edit the existing UDF, right-click the
UDF alias in the database tree of the DB Explorer and
choose item ‘Edit UDF <UDF_name> in the popup
menu.
Set the name of the new UDF in the ‘Name’ edit field.
Select the type of the returned value (STRING, REAL or INTEGER) from the ‘Returns’ drop-down
list. Set the filename, identifying the library that contains the UDF in the ‘Library Name’ edit field.
Note that in the current MySQL Server version UDF libraries are supported only by UNIX systems.
Check ‘Aggregate’ to make the UDF aggregate. Aggregate functions work exactly like the native
MySQL GROUP functions like SUM or COUNT ().
28
On the ‘DDL’ tab you can view the SQL
text for creating the UDF with the
properties you set. This text can’t be
edited, but can be copied to the clipboard.
When you are done, click button
Compile on the toolbar to compile the
UDF.
To call the MySQL Reference, click button Help on SQL on the toolbar.
Button Default Size brings the window to the default size (restricted by the main window and
the DB Explorer).
The drop-down list of the current database UDFs on the toolbar allows you to switch the edited
UDF.
29
CHAPTER 4
DATA MANIPULATION
Export Data Dialog
You can export data from table or query result to
any of 14 available formats (MS Excel, MS
Access, MS Word, RTF, HTML, PDF, TXT and
more). Export Data Dialog is a very powerful
tool, allowing you to export data easily and
quickly, and set various export options. It is based
on EMS QuickExport Component Suite (check
http://www.ems-hitech.com/quickexport/ for
details).
To call the Export Data Dialog, select the table in
the DB Explorer, right-click and choose item
‘Export Data’ from the ‘Data Manipulation’
submenu or open the ‘Data’ (‘Results’) tab of the
Table Editor (SQL Editor or Query Builder),
click button ‘Export Data’ on the toolbar or right-click and choose item ‘Export Data’.
On the ‘Export Type’ tab choose the file type to
export data to and set the filename in the
‘Destination file’ edit field (use button to
browse for files). Check ‘Open file after export’ to
open the result file in the appropriate program
right after export.
If you don’t want all the fields to be exported,
select the fields to export on the ‘Fields’ tab. Note,
that if your table contains BLOB fields, they will
be not exported default.
To choose the fields for export, move them from
30
the ‘Available Fields’ list to the ‘Included Fields’ one. To move the field from one list to another
double-click it or select it (use Ctrl or Shift to select multiple fields) and click button > or <. To
move all the fields click button >> or <<.
On the ‘Formats’ tab you can change the formats
of the exported fields, if necessary. You can also
define your own formats for numeric and
Date/Time fields, using the ‘User’ tab. These
fields (if there are any in your table) are available
in the list. Choose the format of the field from the
drop-down list of the ‘Formats’ column and edit it
if necessary. You can see the example of the result
value if the ‘Sample’ column.
The default column captions in the result table
correspond to the field names of the source table,
but you can edit these captions on the ‘Captions’
tab of the dialog, if necessary. This tab is
unavailable for DBF export type.
The last tab of the dialog contains specific options for the selected export type. The following
options are available:
HTML Options
Preview. This tab allows you to define the colors
of various elements of the result HTML document,
such as: default font color, header font color, table
font color, background colors and link colors. To
change the color of some element, just click this
element and set the color you need. You can also
use various HTML templates to make the result
document look in the way you need. Select a
template from the Template drop-down list or
click ‘Load template’ to browse for templates. If
you have changed some HTML elements
manually and you like the result, you can save it
as a template for future using by clicking ‘Save as
template’ button.
?? Basic. This tab allows you to define the title of the result document and set the mode of the CSS
(Cascade Style Sheets) using. The default CSS using is internal, but you can set to external and
define your own CSS file in the CSS file name edit field.
?? Advanced. On the Body options panel you can set the default font name of the result document
and set the document background. In the Advanced attributes edit field you can define any
attribute values for the HTML tag <BODY>.
?? The Table options panel allows you to define such attributed as Cell padding,
Cell Spacing and Border. In the Advanced attributes edit field you can define
any attribute values for the HTML tag <TABLE>.
?? The Multi-file export panel allows you to export your data not to a single
HTML document, but to a number of documents. Check option Use multi-file
export to enable this mode and define the maximum number of records in each
result file in the appropriate edit field.
31
Excel Options
The Data Format tab allows you to define a
specific format for each data column, header,
footer, column captions and aggregate functions.
Select the field from the Fields list or select an
element of the result Excel sheet (captions, footer,
etc.) from the Options list and set its font, borders
and fill. All the changes you make are displayed in
the sample cell. For data columns you can also
define aggregate functions ('Aggregate' tab): AVG
(average value), MAX (maximum value), SUM
(sum of the values), and MIN (minimum value).
The aggregate function is added to the cell under
the column. Click 'Reset item' to reset all format
setting for the current item, click 'Reset all' to reset format settings for all items.
You can also define repeating styles for data columns or rows on the Styles tab. Click button to
add a style and set its format. After you define all the styles, set the strip style to 'Col' or 'Row' (on
this depends if these styles will be applied to columns or rows). To delete a style, use button . To
load and save styles use buttons and .
The Advanced tab allows you to define headers and footers of the result document pages (the
default page footer is page number) and the sheet title.
Access Options
This tab allows you to set the MS Access export
options. There you can set the Table Name in
appropriate field, and check the Create table if it
does not exist in database option to enable
creating new table, if it does not exists.
RTF Options
This tab is available only if the chosen export type
is MS Word or RTF. It allows you to define
properties of the default document font and row
header font.
PDF Options
This tab allows you to select the document font in
the list at the left (Header Font, Caption Font, Data Font, Footer Font) and set its properties at the
right - font name, encoding, size and color. The Sample box shows how the selected element will
look like in the result PDF document.
Set options for displaying the table in the result PDF document in the Grid Options.
XML Options
This tab allows you to set the encoding of the result XML document and define if the result
document will be standalone by checking/unchecking the 'Standalone' option.
When you are done click ‘Start Export’ to start export. If ‘Open file after export’ was checked, the
result file will be opened in the appropriate program.
Export Data Dialog is available only in the Professional Edition of MySQL Manager.
32
Export Data as INSERT Dialog
You can export data from table or query result as SQL statement INSERT to the SQL script. After
that you’ll be able to execute this script and load all the exported data to another table. This can be
useful for data transfer, e.g. from one database to another.
To call the Export Data as INSERT Dialog, select
the table in the DB Explorer, right-click and choose
item ‘Export Data as INSERT’ from the ‘Data
Manipulation’ submenu or open the ‘Data’ (‘Results’)
tab of the Table Editor (SQL Editor or Query
Builder), click button ‘Export Data as INSERT
Statements’ on the toolbar or right-click and
choose item ‘Export As INSERT’.
First of all you should set the filename for the result
script in the ‘File Name’ edit field (use button to
browse for files). The default file name corresponds
to the source table name and has the extension 'sql'.
The field ‘Table Name’ contains the name of the
table to use in the result INSERT statement (e.g.
INSERT into ‘countries’). This name does not result
the source table, it changes only the result SQL statement.
The ‘Add CREATE TABLE statement’ option inserts the SQL statement CREATE TABLE
<table_name> to the result script before the INSERT statement. The name of the created name is
defined in the ‘Table Name’ edit field. After checking this option the ‘Create Table’ tab becomes
visible and active, where you can edit the statement.
On the ‘Fields’ tab the list of fields for export is set. All the table fields are included to the
‘Included Fields’ list default; if you don’t want some fields to be exported, move them back to the
‘Available Fields’ list. To move the field from one list to another double-click it or select it (use
Ctrl or Shift to select multiple fields) and click button > or <. To move all the fields click button >>
or <<.
The ‘Options’ tab allows you to define the following export options:
?? Replace non-print characters in strings with spaces - if this option is checked, all the nonprint
symbols will be replaced with spaces in the created file.
?? Insert 'COMMIT' after each ... records - this option allows you to define the number of
records, after exporting which the COMMIT statement will be automatically inserted to the
created file.
?? Load script into Script Editor after export - if this option is checked, the created file will be
opened in the Script Editor after export.
When you are done, click ‘Export’ to start export. If ‘Load script into Script Editor after export’
was checked, the result SQL file will be opened in the SQL Script Editor.
Export Data as INSERT Dialog is available only in the Professional Edition of MySQL Manager.
33
Import Data Wizard
You can import data to the table from MS Excel, MS Access, DBF, TXT or CSV file, using the
Import Data Wizard. This wizard is a very powerful tool, allowing you to import data easily and
quickly, and set various import options. It is based on the EMS QuickImport Component Suite
(check http://www.ems-hitech.com/quickimport/ for details).
To activate the Import Data Wizard,
select the table in the DB Explorer,
right-click and choose item ‘Import
Data’ from the ‘Data Manipulation’
submenu or open the ‘Data’ tab of
the Table Editor, click button
Import Data on the toolbar or
right-click in the grid and choose
item ‘Import Data’ in the popup
menu.
On the first step of the wizard you
should choose the file type and set
the filename of the source file (use
button to browse for files). If you
choose ‘Import from CSV’ then you should also select the character, delimiting columns in the
source table. Click ‘Next’ to proceed to the next step.
Step 2 allows you to set the correspondence between the source table columns and the database
table fields. It varies for each import type.
Import from Excel
Select the database table field from
the ‘Fields’ drop down-list and select
the cells to import to this field in the
grid. To select column or row in the
grid, just click its caption. You can
also set this value manually in the
‘Cells’ edit field. Then select the next
field and set the cells for this field. If
the source table columns (or rows)
and the database table fields are
ordered in the same way, you can set
the correspondence automatically by
clicking button ‘Auto fill cols’ (or ‘Auto fill rows’). The first table field will correspond to the first
source table column (or row); second field to the second column (row), etc. If the first rows (or
columns) of the source table contains data, not intended for import, you can skip them by setting the
appropriate value in the ‘Skip … row(s)’ (or ‘Skip … col(s)’) edit field.
34
Import from Access
When you choose import from the
MS Access file, the Select Source
tab becomes available.
First select the data source for import
- MS Access table or SQL query. If
you choose import from a table, then
you should select a table name from
the list, if you choose to import from
a query, you should set the query
SQL text in the lower area, e.g.:
SELECT Name, Capital FROM COUNTRY WHERE CONTINENT='South America'
This text can be saved or loaded to/from SQL flea, using the Save and Load buttons at the
right respectively.
Import from DBF
First select the database field from
the ‘Table fields’ list. Then select the
corresponding field in the
‘<table_name>.dbf’ list.
Click button ‘Add’ to link these
fields. These fields will be added to
the list at the bottom of the window.
Repeat these operations for each
database table field. If you want to
remove the accordance you set,
select the linked fields in the bottom
list and click button ‘Remove’. If the source table fields and the database table fields are ordered in
the same way, you can set the correspondence automatically by clicking button ‘Auto fill’. First
field of the source table will correspond to the first field of the database table, second field to the
second field, etc.
Import from TXT
First select the database table field
from the 'Fields' drop-down list.
Then set two separator lines to
delimit the source table column.
Click to add a separator, double-click
to delete one. Drag separators to
change the column width. You can
also set the column starting position
and the column width manually in
the edit fields 'Pos' and 'Size'. When
you set the separators correctly,
proceed to another field and repeat
35
these operations for each database table field. If you don't want some first rows of the source table
to be imported set the number of such rows in the 'Skip ... first line(s)' edit field.
Import from CSV
If the delimiter you have defined on
the first step was found in the source
table, then you will find the table
columns already separated and
delimited. Select the database table
field from the 'Fields' drop-down list.
Then click the corresponding source
table column or set the 'Col' value
manually. Repeat these operations
for each database table field. If the
source table fields and the database
table fields are ordered in the same
way, you can set the correspondence automatically by clicking button ‘Auto fill’. First field of the
source table will correspond to the first field of the database table, second field to the second field,
etc. If you don't want some first rows of the source table to be imported set the number of such rows
in the 'Skip ... first line(s)' edit field.
When you are done, click ‘Next’ to proceed to the next step.
On the step 3 of the wizard you can edit the formats of the imported fields.
On the ‘Base Formats’ tab the following format options are available:
Decimal separator - set a character,
which delimits the decimal parts of
the imported numbers.
Thousand separator - set a
character, which separates the digit
groups in the imported numbers.
?? Short date format, Long date
format, Short time format,
Long time format - use these
edit fields to set the date and time
formats.
?? Left quotation - set a character
or a number of characters, which
denote quoting in the imported strings.
?? Right quotation - set a character or a number of characters, which denote unquoting in the
imported strings.
?? Quotation action - you can select 'Add' to add quotation marks to each imported string or
'Remove' to remove all the quotation marks from the imported strings. 'As is' saves the original
quotation marks.
?? Boolean true - set some variants of TRUE value representation in the imported table, e.g. 'Yes'
or '+'. Use new line for each new variant.
36
?? Boolean false - set some variants of FALSE value representation in the imported table, e.g. 'No'
or '-'. Use new line for each new variant.
On the ‘Data Formats’ tab you can customize the format of each imported field in case when
additional formatting is required. Select the field in the 'Field Name' list and set its format in the
proper edit fields.
Generator
?? Value - use this edit field to set
the initial value of the
autoincrement field.
?? Step - set the step of the
autoincrement field. If it is 0 then
the value of the generator will be
ignored.
Constant
Value - use this edit field to set the
constant value of the field.
Default
?? Null - set the value, which will be understood as NULL to set the default value.
?? Default - set the default value of the NULL field.
Quotation
?? Left quotation - set a character or a number of characters, which denote quoting in the
imported string.
?? Right quotation - set a character or a number of characters, which denote unquoting in the
imported string.
?? Quotation action - you can select 'Add' to add quotation marks to the imported string, 'Remove'
to remove all the quotation marks from the imported string or 'As is' to save the original
quotation marks.
String conversion
Char case - set the case of the imported string. 'As is' saves the original string case, 'Upper' sets the
whole string to upper case, 'Lower' sets the whole string to lower case, 'UpperFirst' sets the first
letter of the string to upper case, 'UpperFirstWord' sets the first letter of each word to upper case.
Char set - set the char set of the imported string to ANSI or OEM. 'As is' saves the original string
char set.
Click 'Next' to proceed to the next step.
On the last step of the wizard the following import options are set:
?? Commit after done - check this option to commit the transaction after import is finished.
?? Commit after ... records - set a number of records, after importing which the transaction shall
be committed.
?? Import all records - check this option to import all records from the source table.
?? Import only ... first record(s) - if you don't want all the records to be imported, set a number of
records to import them from the source file. In this case only this number of records (beginning
from the first one) will be imported.
37
Note, that on each step of the wizard you can use buttons ‘Load Template’ and ‘Save Template’ on
the left panel, which allow you to save/restore all the import settings (file type and name, field
correspondence, format options, etc.) to/from the template file. This is very useful, if you often use
the same import configuration: you don’t have to choose fields or edit field formats on each import
session – you can simply load a previously saved template and skip all the unneeded steps.
When you are done, click 'Finish' to start import.
Import Data Wizard is available only in the Professional Edition of MySQL Manager.
Load Data Wizard
Load Data is a native MySQL function, which allows you to read rows from a text file to the table
at a very high speed. The Load Data Wizard guides you through the process of loading data from
file.
To call the Load Data Wizard,
select the table in the DB Explorer,
right-click and choose item ‘Load
Data’ from the ‘Data Manipulation’
submenu or open the ‘Data’ tab of
the Table Editor, click button ‘Load
Data’ on the toolbar or right-click
and choose item ‘Load Data’.
On the first step of the wizard you
should set the name of the loaded
text file in the ‘Filename’ edit field
(use button to browse for files).
Set the function attributes by
checking the following options:
?? Local - if this option is checked, the file is read from the client host. Otherwise, the file must be
located on the server.
?? Low priority - if this option is checked, loading data is delayed until no other clients are
reading from the table.
?? Concurrent - if this option is checked, other threads can retrieve data from the table while
loading data is executed.
?? Replace - if this option is checked, new rows replace existing rows that have the same unique
key value.
?? Ignore - if this option is checked, input rows that duplicate an existing row on a unique key
value are skipped. If you don't specify either option, an error occurs when a duplicate key value
is found, and the rest of the text file is ignored.
Step 2 allows you to define fields to load data to by moving them from the ‘Available Fields’ list to
the ‘Included Fields’. To move the field from one list to another double-click it or select it (use Ctrl
or Shift to select multiple fields) and click button > or <. To move all the fields click button >> or
<<.
38
On the step 3 you can specify the characters, used in the data file for delimiting fields and lines.
Option 'Ignore Lines Count' allows you to specify the number of the first file lines, which will be
not loaded into the table.
When you are done, click ‘Load’ to start loading. The ‘Load Data’ step is automatically activated,
where you can view the operation log of the process.
39
CHAPTER 5
DATABASE TOOLS
SQL Editor
SQL Editor is the basic MySQL Manager tool for creating and executing database queries. It
allows you to create and edit SQL text for the query, prepare and execute queries and view the
results of execution. SQL Editor supports Quick Code and Syntax Highlight systems, which make
your work much easier.
The main area of the editor is situated on the ‘Edit’
tab. This area is provided for working with the text of
the query. For your convenience the Quick Code
system is enabled, i.e. when you type first word
symbols in the SQL text editor you are offered some
variants for the word completion in a popup list
(analogue of the Code Insight in Delphi IDE).
You can activate these popup lists yourself by
pressing the following key combinations:
Ctrl+Space - All SQL keywords and database objects;
Ctrl+Alt+S - SQL glossary;
Ctrl+Alt+T - Table list;
Ctrl+Alt+U - UDF list;
Ctrl+Alt+F - Field list.
You can change the sorting mode of the Code Completion list items by right-clicking in the popup
list and switching to the sorting mode you need: sort alphabetically by item names or sort by the
scope categories (SQL keywords, tables, UDFs).
Database objects are highlighted in the text. You can open the proper object editor by clicking the
object name in the text, holding button Ctrl pressed on the keyboard.
The popup menu of the edit area contains standard functions for working with text (Cut, Copy,
Paste, Find, Replace, Toggle Bookmarks, etc) and also functions for processing the whole query,
which allow you to execute/prepare query, save/load query to/from file, and preview/print query.
40
When the query text is ready, click button Execute on the toolbar or press F9 to check the query
text for errors. If there are any errors in the query text, these errors will be displayed in the bottom
area of the editor window, and the text line, containing the first error, will be indicated with a purple
line.
If the text is correct the query is executed and the ‘Results’ area becomes active.
This area displays the
result data returned by
the query. They can be
viewed in three modes
(chosen by clicking the
according button at the
bottom of the window):
?? Grid View - view
data as a grid;
?? Form View - view
data as a form:
there is only one
record displayed at
the time, to view
another record use
the navigation
buttons.
?? Print Data - view
data in WYSIWYG mode, ready for printing. The acquired query can be saved to filed and/or
printed.
These data can’t be edited, but can be exported (‘Export Data’ item in the popup menu or button
on the toolbar) or exported as INSERT statement to the SQL Script (‘Export as Insert’ item in the
popup menu or button on the toolbar).
The status bar displays the number of records, acquired while executing the query. The comment
bar also displays time of execution.
On the ‘Logs’ page information of all the executed statements, including queries and carried out
transactions, is displayed.
To select the database for the query, click button Databases on the toolbar and select the
required database from the drop-down list of the available databases. The alias of the selected
database will be displayed in the window caption.
You can load a query from file by clicking button Load on the toolbar. To save the query to file,
click button Save ; to save all the opened queries to one file, click Save All Queries To File .
To create new query, click button New Query on the toolbar. The clear query will be available
on the 'Edit' page (note, that the old query will not be deleted; you can activate it using button with
the query number at the bottom of the edit area).
To delete the current query, click button Delete Current Query on the toolbar; to delete all the
queries, click button Clear All History .
41
To commit or rollback the current transaction, click button Commit Transaction or Rollback
Transaction in accordance.
Visual Query Builder
Visual Query Builder is a powerful tool, provided for visual building database queries. Using
Query Builder you can select tables and fields, join tables, set conditions for the selection and
perform many more query operations without knowledge of SQL. Visual Query Builder is based
on the EMS QueryBuilder Component (check http://www.ems-hitech.com/querybuilder/ for
details).
The main area of the Query Builder is the Builder area.
Here you can build your query by placing the database
tables on the area, selecting the required data and setting
links between objects.
To add a table to the query, choose it in the table list at the
right, then double-click it or drag it to the Builder area.
The selected table will appear on the Builder area with
the list of its fields. To include the table field to the query,
click at the left of the field name in the list or double-click
it to set the blue icon next to the field name. To include all
the fields, set a flag at the left of the table alias. To
remove the fields from the query, uncheck the fields; to
remove the table, close it by clicking the button ‘?’ next
to the table alias. To edit the table alias, double-click it.
To associate tables by two fields, just drag one field from
the table field list to another. This will set a link between
these tables by the selected fields. When you drop a field,
a line will appear between the linked fields. You can view and edit the properties of object
association. To view the properties, just aim cursor to the link line and a hint, containing the
association condition, will appear. To edit the properties, double click the line or right-click and
choose 'Properties' item from the popup menu. A dialog window will appear, where you can change
the association condition by choosing it from the list (=, >, <, >=, <=, <>). Also you can check or
uncheck 'Include all from <table_name>' option for each object, included into the association. Click
'OK' to confirm the changes you made. To drop a link between the tables, right-click on the link line
and choose 'Delete Link' item from the popup menu. To delete all the links of the table, click button
‘-‘ next to the table alias. To insert a point to the link line, right-click on the line and choose 'Insert
Point' item from the popup menu. A new point will appear, using which you can move the link line.
It doesn't cause any changes in the query but
makes the diagram performing more obvious
and the visual building handler.
In the ‘Criterions’ area you can set the selection
conditions. To add a condition, click button at
the left and select ‘Add condition’ in the popup
menu. Edit the condition by clicking its parts and setting their values. Clicking the button at the left
of the condition string activates the popup menu, which allows you to add a new condition of the
42
same enclosure level, add a new enclosure level, delete the current condition, open or close the
condition, if it is composite. A simple condition string contains three fields: an argument, a
condition and a second argument (if required for the condition). Clicking each field allows you to
set its value. Clicking the argument field allows editing it as a text field. You can set a table name or
a definite value in this field. Right-clicking the field in the edit mode activates the popup menu,
which contains the 'Insert Field' function (also called by Shift+Enter). This function allows you to
choose a field from the list of all the table fields, available in the query. Clicking the condition field
activates the popup menu, where you choose the condition you need. The way of processing the
condition is set in the upper string of the area (All, Any, None, or Not all of the following are met).
Click the underlined word to change it.
The ‘Selection’ area displays the output fields
of the query. It allows you to edit the names of
the query output fields, set their displaying
order and set the aggregate functions (SUM,
MIN, MAX, AVG, COUNT) for each field. To
remove the field from the list, right-click the
field row and choose ‘Delete current row’ from
the popup menu. To change the input query field, double click it and then type the field name on the
keyboard or choose it from the drop-down list.
To change the output query field name, double click it and type the field name on the keyboard.
To set the aggregate function for the field, double click the field row in the 'Aggregate' column and
then type the function name on the keyboard or choose it from the drop-down list.
If you check option ‘Include only unique records’ then the repeated records will not be included
into the query result.
In the ‘Grouping Criterions’ area you can set
the conditions for grouping the query records.
They are set in the same way as the selection
conditions (see above). These conditions will
be included into the HAVING statement of the
current query.
Set the way of sorting the query records in the ‘Sorting’ area. The field list at the left represents all
the output query fields; the list at the right contains fields, by which the query records will be sorted.
To move the field from one list to another, drag the selected field or use buttons ‘Add’ and
‘Remove’. To change the sorting order, select a field in the right list and move it using buttons 'Up'
and 'Down'. To change the sorting direction, select a filed in the right list and switch the direction
(Ascending, Descending) using button ‘A..Z’/'Z..A'.
When the query is ready, click button Prepare Query on the toolbar or press Ctrl+F9 to check
the query for errors. If there were any errors in building the query text, you will get the appropriate
message, describing the error. If everything is correct, you can execute the query by clicking button
Execute on the toolbar. This will display the ‘Results’ area.
This area displays the result data returned by the query. They can be viewed in three modes (chosen
by clicking the according button at the bottom of the window):
?? Grid View - view data as a grid;
?? Form View - view data as a form: there is only one record displayed at the time, to view
another record use the navigation buttons.
?? Print Data - view data in WYSIWYG mode, ready for printing. The acquired query can be
saved to filed and/or printed.
43
These data can’t be edited,
but can be exported
(‘Export Data’ item in the
popup menu or button
on the toolbar) or exported
as INSERT statement to the
SQL Script (‘Export as
Insert’ item in the popup
menu or button on the
toolbar).
The status bar displays the
number of records, acquired
while executing the query.
In the ‘Edit’ area the query
text is automatically
generated while you build
query. You can edit this text
according to the rules of
SQL, and all the changes
will be displayed on the
other pages of the Query Builder.
To select the database for the query, click button Databases on the toolbar and select the
required database from the drop-down list of the available databases. The alias of the selected
database will be displayed in the window caption.
You can load a query from file by clicking button Load Query on the toolbar. To save the query
to file, click button Save Query .
To commit or rollback the current transaction, click button Commit Transaction or Rollback
Transaction in accordance.
To clear the current query, click button Clear Current Query on the toolbar.
To hide the table list at the right of the window, click button View on the toolbar and uncheck
the appropriate item in the drop-down menu.
Visual Query Builder is available only in the Professional Edition of MySQL Manager.
44
SQL Monitor
SQL Monitor allows you to view the SQL code of
all the operations executed over databases and
database objects in MySQL Manager.
The content of the window can’t be edited, but can be
copied to the clipboard, saved to the text file or
printed.
To save the content, click button Save on the
toolbar.
To clear the content, click button Clear Content
on the toolbar.
The popup menu of SQL Monitor provides standard
functions for searching text in the window, copying it
to the clipboard and printing the content of the
window.
SQL Script Editor
Using this editor, you can view, edit and execute SQL scripts.
In the Script area you can view and edit the
SQL script text. You can use quick code to
fasten this process: when you type the first
word symbols in the edit area, you are
offered some variants for the word
completion in a popup list (analogue of the
Code Insight in Delphi IDE).
The popup menu of the edit area contains
standard functions for working with text (Cut,
Copy, Paste, Find, Replace, Toggle
Bookmarks, etc) and also functions for
processing the script, which allow you to
save/load script to/from file, and
preview/print script.
The Object Explorer at the left of the window displays the tree of objects, used in the current script
and allows you to get to the needed script fragment quickly by clicking the object in the tree.
To change the database for the script, use the drop-down menu on the toolbar.
To load the script from the *.sql file, click button Open Script File on the toolbar; to save script,
click Save Script . To create the new script, click button on the toolbar.
45
To execute the script, click button Execute . You can also execute script right from the file
without opening it by clicking button Execute Script from File . To stop executing script, click
button Stop Script .
The results of executing the script are displayed on the Results page. This text can't be edited, but
can be copied to the clipboard.
The errors in executing the script are displayed in the bottom area of the window. The popup menu
of this area allows you to copy the selected error message or to copy all the error messages.
If you want the script to be aborted on errors, check option ‘Abort Script on Error’ in the
Environment Options window on Tools: SQL Script page. In the case of successful executing the
script you will receive message, informing you about the execution time.
Extract Metadata Expert
Using the Extract Metadata Expert you can extract the database metadata and table data to SQL
script.
Select Extract Type. On this step
you choose the database, from which
data will be extracted, and the
extracted data type (metadata or
table data). Also you have to choose,
if the extract results should be
loaded into the Script Editor
automatically or they should be
saved into the file (in this case you
should set the file name).
Select Meta Objects. This page will
be available only if you choose
Extract Metadata. Here you should
choose metadata to be extracted. To choose the objects you need select the database object type
from the drop-down list and move objects from one list to another, using buttons, by doubleclicking
or dragging them. The Extract All option allows you to extract all the metadata from the
database.
Select Data Objects. This page will be available only if you choose Extract Data. Here you should
choose the tables, which data should be extracted. To choose a table move it from one list to another,
using buttons, by double-clicking or dragging it.
Set Extract Options. On this step you can set the following extract options:
?? Generate 'create database' statement - if this option is checked, the statement 'create
database' will be added to the generated SQL script.
?? Generate Drop statements - if this option is checked, the 'Drop' statements will be added to the
generated SQL script.
46
?? Data Options – use option ‘Commit after each block’ and counter ‘Records in a block’ to
define number of records, after extracting which the COMMIT statement should be inserted in
the result script.
?? Load script into Script Editor - if this option is checked, the created SQL script will be
opened in the Script Editor after extract.
You can save the extract configuration (extract type, meta and data objects, extract options) for
future use as a template. Just click button Save Template on the left panel and set the template
name. Next time you will be able to configure your extract quickly by clicking the button Load
Template and choosing the appropriate previously saved template.
When you are done, click 'Extract'.
Print Metadata
The powerful module Print Metadata allows you to print all the database metadata: table fields,
indices, description and various UDF properties. The created report can be previewed before
printing and saved to file.
Select tables or UDFs for
printing, using buttons on
the toolbar. Move the
objects from the list
Available... to the list ...for
Print, using buttons 'Add',
'Add all', 'Remove' and
'Remove all', by doubleclicking
or dragging them
(multiple objects can be
selected using buttons Ctrl
and Shift).
Button Print on the control panel prints the selected metadata; button Preview enables the
metadata preview mode.
Print Metadata is available only in the Professional Edition of MySQL Manager.
HTML Report
HTML Report allows you to generate a detailed HTML report about the selected database
metadata.
To call the HTML Report window select the Tools | HTML Report menu item.
Select the database for report from the Databases drop-down list and set the directory for storing
the result HTML files in the Output directory box.
47
Select the database objects to include their metadata into the report by selecting the appropriate
options of the Report Meta Objects group.
If necessary, you can also select the character set for the
result files from the Charset drop-down list and check
option Show report after generating to open the result
report in your default browser after generating.
To start generating the report with the parameters you set,
click the Generate HTML Report button.
Report Designer
The Report Designer allows you to create and
edit reports.
To call this window select the Tools | Report
Designer menu item.
This module is provided by FastReport
(http://www.fast-report.com) and has its own help
system. Press F1 to call the FastReport help.
Please find below the instruction on how to
create a simple report in the Report Designer.
1. Open the Report Designer.
2. In the designer select the Edit | Add
Dialog Form menu item.
3. Pick the Database component (lower on
the left toolbar) and drop it on the form.
4. Within the Object Inspector enter the
HostName and the DatabaseName properties manually, e.g. 'localhost' and 'mysql'.
5. Set the LoginPrompt property to True.
6. Set the Connected property to True.
7. Enter your username and password (e.g. 'root' and empty password).
8. Set the LoginPrompt property to False.
9. Pick the Query component on the left toolbar and drop it on the form.
10. Select the name of your Database component in the drop-down list of the Query1.Database
property.
11. Set the Query1.SQL property value, e.g. 'SELECT * FROM user'.
12. Set Query1.Active to True.
13. Go to the Page1 in the Designer.
14. Using the Insert DB Fields button on the top toolbar insert your fields.
15. Save the report on your local drive, e.g. C:\mysql_user.frf
16. Click the Preview button on the toolbar. This mode allows you to view and print the result
report.
48
17. That's all! You can always edit your report by opening the designer and selecting the File |
Open menu item
User Manager
User Manager is provided for administering users
and their global privileges.
To open the User Manager select the Tools |
User Manager menu item.
Selects the server for administering users on in
Servers list.
Users list box displays all the users on server with their
privileges. Right-click on the list allows you to add a new
user, edit the selected user's privileges in the User Editor,
delete the selected user and show/hide the list box
columns.
To add new user, click Add button. Set user privileges
in User Editor window and click ‘Ok’.
To edit an existing user in User Editor, click Edit
button.
To delete an existing user, click Delete button.
Grant Manager
The Grant Manager allows you to set
the access grants for users, roles and
database objects.
Select a database from the drop-down
list at the top of the window to set the
access grants on its objects.
To modify the user's access grants select
the user from the list Privileges for and
select the object type to set grants on
(tables or columns) from the drop-down
list Grants on.
If option ‘Granted only’ is checked, only granted objects are displayed in the grid.
49
You can also use Filter in the upper right corner of the window to display only the objects you need.
E.g. to display objects, which names begin from 'c' letter type 'c' in the filter edit field.
After you choose the user and the required objects, right-click in grid to change the access grants for
statements Alter, Delete, Drop, Index, Insert, Reference, Select and Update.
Visual Database Designer
Visual Database Designer is provided
for designing your database visually. It
allows you create, edit and drop tables
and table fields, set links between tables,
and so on.
To call the designer select the Tools |
Visual Database Designer menu item.
Working in the main area
On the right of the window a list of
database tables is placed. To show/hide
this list use the Tables button on the
toolbar. To add a table to the diagram,
double-click its alias in the list or drag
it to the main area. Now table appears
in the main area with the list of all its fields.
To change the table display mode click the Table menu button in the table caption and select the
proper item from the drop-down menu:
?? Captions only - displays only the table caption.
?? Short - displays table with the list of object fields.
?? Full - displays table with the list of object fields and their types.
?? Tune Size - fits the table size so to display all the fields.
?? Auto Size - fits the table size so to display all the fields and locks the table size.
?? Close - removes the table from the diagram.
Click the object caption to make the table active. To select multiple tables, use button Shift or select
multiple tables with a mouse pointer. Double-click the table caption to activate the Table Editor for
editing the table. Right-click the table caption to activate the popup menu with the following items
available:
?? Close Table - removes the table from the diagram.
?? Refresh Table - refreshes the table in the diagram.
?? New Table - opens the Create Table Dialog for creating a new table.
?? Edit Table - opens the Table Editor for editing the table.
?? Drop Table - drops the current table out the database.
?? Create Link - opens the Foreign Key Editor for creating a foreign key for the table. Note,
that foreign keys are available for InnoDB tables only and are supported since MySQL
Server version 3.23.44 (version 3.23.50 or higher recommended).
50
Multiple fields select with Ctrl key pressed. Right-click in the field list of the table activates the
popup menu with the following items available:
?? Add Field - opens the Field Editor for adding a new field to the table.
?? Edit Field - opens the Field Editor for editing the field.
?? Drop Field(s) - drops the selected fields out of the table.
?? Set selected fields as primary key - opens the Index Editor for including the selected
fields into the primary key.
?? Visible - makes the selected field visible in the printed diagram (default on).
Right-click in the main area activates the popup menu with the following items available:
?? Arrange Objects - fits all the tables in the designer to the visible area.
?? Reverse Engineer - adds all the database tables to the diagram.
?? Edit Object - opens the Table Editor for editing the selected table.
?? Close Object(s) - removes the selected tables from the diagram.
?? Drop Object(s) - drops the selected tables out of database.
?? Create Table - opens the Create Table Dialog for creating a new table.
?? Create Link - opens the Foreign Key Editor for creating a foreign key for the selected
table.
To create a foreign key for the table, drag one of the table fields to the field from another table. This
opens the Foreign Key Editor with these fields selected in the proper list boxes. Note, that foreign
keys are available for InnoDB tables only and are supported since MySQL Server version 3.23.44
(version 3.23.50 or higher recommended).
Buttons at the left of the window allow you to align the diagram objects in the way you like.
Database Designer Toolbar
Databases - this button drops down with a list of registered databases, allowing you to select the
database for design. If you switch to the database, which is not currently active, you’ll be offered to
connect to it. After you switch to the new database, it becomes active in the Visual Database
Designer.
New Diagram - create the new design diagram by clicking this button.
Open Diagram - this button opens a previously saved diagram from file.
Save Diagram - if you want to save the diagram to file, click this button.
Tables - this button shows/hides the table list at the right.
Show Error Window - this button shows/hides the Errors area at the bottom.
Print Setup - click this button for activate the Diagram Print Setup dialog window, which
allows you to set the parameters of diagram printing.
Print - by clicking this button you can send the diagram for printing.
Arrange Objects - this button sets the zoom value automatically so that the objects fit in the
main area best.
Reverse Engineer - this button adds all the objects of the currently active database to the
diagram.
Display Mode - drops down with a menu allowing you to set the display mode for the selected
objects. Item Set auto size locks the size of all the selected objects, item Clear auto size unlocks it.
Edit Actions - this button drops down with a menu allowing you to edit, hide or drop the
selected object(s), create a new table and select all the diagram objects.
Set Default Link Color - you can select the color of the diagram links by clicking this button.
51
Create Link - by clicking this button you can open the Foreign Key Editor for creating a
foreign key for the table selected in the main area.
Default Size and Position - this button brings the window to its default size and position
(restricted by the main window and DB Explorer).
52
CHAPTER 6
DATABASE SERVICES
Backup Tables
The Backup Tables Wizard allows
you to make a copy of all the table
files to the backup directory.
Currently MySQL supports backup
only for MyISAM tables and
transaction-safe type tables. Backup
Tables copies .frm (definition)
and .MYD (data) files. The index file
can be rebuilt from those two.
Note, that you can’t backup tables on
remote server; backup only works on
local host. To backup tables from the
remote server, use Extract
Metadata, and then restore them using Script Editor.
To activate the Backup Tables Wizard use the menu item Services | Backup Tables.
Step 1 - Backup Path
Database - select the database from the drop-down list to backup its tables.
Backup Directory - set a path to the directory, where the database tables should be saved.
Step 2 - Backup Tables
Available Tables - a list of tables, available for backup. It is the list of all the tables, included into
the database you set on the 'Backup Path' step.
Selected Tables - a list of tables to backup.
53
To move the table from one list to another, double-click it or select it (use Ctrl or Shift to select
multiple tables) and click button > or <. To move all the tables click button >> or <<.
Step 3 - Run Backup
This page is activated automatically on clicking button 'Backup'. It displays the backup process
(Operation log) and its results (Results).
Restore Tables
The Restore Tables Wizard allows you to restore database tables, saved in advance with Backup
Tables.
To activate the Restore Tables
Wizard use the menu item Services |
Restore Tables.
Step 1 - Restore Path
Restore Directory - type the
directory name, where the database
tables are stored, or select it from the
drop-down list.
Registered database – check this
option to restore the tables into the
database, registered in MySQL
Manager. Select the host and the database name from the proper drop-down lists.
Unregistered database – check this option to restore the tables into the database, not registered in
MySQL Manager. Type the host and the database name or select them from the drop-down lists. Set
the Port (default 3306), login and password.
Step 2 - Restore Tables
Available Tables - a list of tables, available for restoring. It is the list of all the tables, stored in the
directory you set on the 'Restore Path' step.
Selected Tables - a list of tables to restore.
To move the table from one list to another, double-click it or select it (use Ctrl or Shift to select
multiple tables) and click button > or <. To move all the tables click button >> or <<.
Step 3 - Run Restore
This page is activated automatically on clicking button 'Restore'. It displays the restoration process
(Operation log) and its results (Results).
54
Flush
Use the Services | Flush menu if you want to clear some of the internal caches MySQL uses. To
execute FLUSH, you must have the RELOAD privilege.
The following table illustrates the use of FLUSH:
HOSTS
Empties the host cache tables. You should flush the host tables if some of your
hosts change IP number or if you get the error message Host ... is blocked.
When more than max_connect_errors errors occur in a row for a given host
while connection to the MySQL server, MySQL assumes something is wrong
and blocks the host from further connection requests. Flushing the host tables
allows the host to attempt to connect again.
LOGS
Closes and reopens all log files. If you have specified the update log file or a
binary log file without an extension, the extension number of the log file will
be incremented by one relative to the previous file. If you have used an
extension in the file name, MySQL will close and reopen the update log file.
PRIVILEGES Reloads the privileges from the grant tables in the MySQL database.
TABLES Closes all open tables and force all tables in use to be closed.
TABLES WITH
READ LOCK
Closes all open tables and locks all tables for all databases with a read until
one executes UNLOCK TABLES. This is very convenient way to get backups
if you have a file system, like Veritas, that can take snapshots in time.
STATUS Resets most status variables to zero. This is something one should only use
when debugging a query.
Analyze Tables
The Analyze Tables Wizard allows
you to analyze and store the key
distribution for the table. During the
analysis the table is locked with a
read lock. Currently MySQL
supports analyzing only for
MyISAM tables and transaction-safe
type tables. MySQL uses the stored
key distribution to decide in which
order tables should be joined when
one does a join on something else
than a constant.
To activate the Analyze Tables
Wizard use the menu item Services | Analyze Tables.
55
Step 1 - Location
Host - select the host, where the database to analyze its tables is situated, from the drop-down list.
Database - select the database from the drop-down list to analyze its tables.
Step 2 - Select Tables
Available Tables - a list of tables, available for analysis. It is the list of all the tables, included into
the database you set on the 'Location' step.
Selected Tables - a list of tables to analyze.
To move the table from one list to another, double-click it or select it (use Ctrl or Shift to select
multiple tables) and click button > or <. To move all the tables click button >> or <<.
Step 3 - Analyze Tables
This page is activated automatically on clicking button 'Analyze'. It displays the analysis process
(Operation log) and its results (Results).
Check Tables
The Check Tables Wizard allows
you to check the database tables on
errors. Currently works only for
MyISAM and transaction-safe type
tables.
To activate the Check Tables
Wizard use the menu item Services |
Check Tables.
Step 1 - Location
Host - select the host, where the
database to check its tables is
situated, from the drop-down list.
Database - select the database from the drop-down list to check its tables.
Check Options
?? Quick - don't scan the rows to check for wrong links.
?? Fast - only check tables which haven't been closed properly.
?? Changed - only check tables which have been changed since last check or haven't been closed
properly.
?? Medium - scan rows to verify that deleted links are ok. This also calculates a key checksum for
the rows and verifies this with a calculated checksum for the keys.
?? Extended - do a full key lookup for all keys for each row. This ensures that the table is 100 %
consistent, but will take a long time!
56
Step 2 - Select Tables
Available Tables - a list of tables, available for checking. It is the list of all the tables, included into
the database you set on the 'Location' step.
Selected Tables - a list of tables to check.
To move the table from one list to another, double-click it or select it (use Ctrl or Shift to select
multiple tables) and click button > or <. To move all the tables click button >> or <<.
Step 3 - Check Tables
This page is activated automatically on clicking button 'Check'. It displays the checking process
(Operation log) and its results (Results).
Repair Tables
The Repair Tables Wizard allows
you to repair the database tables that
may be corrupted. Currently works
only for MyISAM and transactionsafe
type tables.
To activate the Repair Tables
Wizard use the menu item Services |
Repair Tables.
Step 1 - Location
Host - select the host, where the
database to repair its tables is
situated, from the drop-down list.
Database - select the database from the drop-down list to repair its tables.
Repair Options
?? Quick - do a repair of only the index tree.
?? Extended - create the index row by row instead of creating one index at a time with sorting.
This may be better than sorting on fixed-length keys if you have long char() keys that compress
very good.
Step 2 - Select Tables
Available Tables - a list of tables, available for repair. It is the list of all the tables, included into
the database you set on the 'Location' step.
Selected Tables - a list of tables to repair.
To move the table from one list to another, double-click it or select it (use Ctrl or Shift to select
multiple tables) and click button > or <. To move all the tables click button >> or <<.
57
Step 3 - Repair Tables
This page is activated automatically on clicking button 'Repair'. It displays the repair process
(Operation log) and its results (Results).
Optimize Tables
The Optimize Table Wizard should be used if you have deleted a large part of a table or if you
have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB,
or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT
operations reuse old record positions. You can use optimization to reclaim the unused space and to
defragment the data file.
OPTIMIZE TABLE works the
following way:
?? if the table has deleted or split
rows, repair the table;
?? if the index pages are not sorted,
sort them;
?? if the statistics are not up to date
(and the repair couldn't be done
by sorting the index), update
them.
Currently MySQL Server supports
optimizing only for MyISAM tables
and transaction-safe type tables.
To activate the Optimize Tables Wizard use the menu item Services | Optimize Tables.
Step 1 - Location
Host - select the host, where the database to optimize its tables is situated, from the drop-down list.
Database - select the database from the drop-down list to optimize its tables.
Step 2 - Select Tables
Available Tables - a list of tables, available for optimization. It is the list of all the tables, included
into the database you set on the 'Location' step.
Selected Tables - a list of tables to optimize.
To move the table from one list to another double-click it or select it (use Ctrl or Shift to select
multiple tables) and click button > or <. To move all the tables click button >> or <<.
Step 3 - Optimize Tables
This page is activated automatically on clicking button 'Optimize'. It displays the optimization
process (Operation log) and its results (Results).
58
Server Properties
Use Server Properties to view the properties of the selected
server. This window is activated via Services | Server Properties
menu item.
Use drop-down list on the toolbar to select the server.
On the ‘Variables’ tab you can view a list of all server variables
and their values. These values can’t be edited. The ‘Process List’
tab displays a list of all the processes, executed on the server, and
additional process information: ID, User, Host, etc.
Button Refresh on the toolbar or in the popup menu refreshes
these lists. In the ‘Process List’ area item ‘Kill process’ is also
available in the popup menu.
Ping Server / Shutdown Server
Use the menu item Services | Ping Server to check the connection with the server. If the
connection is correct, a message 'Success! Connection is alive' will appear, otherwise - an error
message.
Use Services | Shutdown Server to shutdown the MySQL server. You must have the proper rights
to execute this operation.
59
CHAPTER 7
MYSQL MANAGER
OPTIONS
Environment Options
The Environment Options window allows you to set the general MySQL Manager options.
Preferences
?? Show splash screen
on startup – if this
option is checked,
the splash screen is
shown on each
MySQL Manager
startup.
?? Save desktop on
disconnect – if this
option is checked, all
object editors, active
on disconnecting
from the database,
will be opened on next connection.
?? Play sounds – this option allows you to enable MySQL Manager sounds.
?? Disable multiple instances – this option prohibits running multiple instances of MySQL
Manager.
?? Encrypted passwords – if this option is checked, passwords for connecting to databases are
encrypted while storing in the Windows registry.
60
Confirmations
?? Confirm saving of object - if this option is checked, the program requires confirmation each
time you want to save changes in database object.
?? Confirm exit from editor - if this option is checked, the program asks you to confirm exit from
the editor, if you have made any changes.
?? Confirm dropping object - if this option is checked, the program requires confirmation for
dropping database object.
?? Confirm exit from MySQL Manager - if this option is checked, the program requires
confirmation when you want to exit MySQL Manager.
?? Confirm successful compilation - if this option is checked, the program requires confirmation
of the successful compilation.
Windows
?? Environment style – this option allows you to set the environment style: MDI Environment
(like Microsoft Office applications) or Floating-windows Environment (like Borland Delphi
IDE).
?? Windows Restrictions - this option allows you to set the number of table and UDF editors that
can be opened at a time.
?? Zoom options - this option allows you to set the window maximization size: full screen,
restricted by main form, restricted by main form and DB Explorer.
Tools
?? Control Toolbar Buttons Action - this option allows you to choose the reaction on clicking
buttons on the control panel. If you choose ‘Create New Object’, then the new database objects
will be created on clicking these buttons, if you choose ‘Show Last Object’, then the last viewed
database objects will be opened for editing.
?? Disable Transaction Confirmation - if this option is checked, no transaction confirmation will
be required on closing Visual Query Builder and SQL Editor. Specify the default action
('Commit' or 'Rollback') and this action will be performed automatically each time when you
exit Query Builder or SQL Editor.
?? Show only connected databases in drop-down menu – this option allows you to enable
showing only connected databases in drop-down menu
• DB Explorer
?? Show hosts in Database Explorer – if this option is checked, database hosts are visible in the
DB Explorer database tree.
?? Show table subobjects – if this option is checked, table subobjects (fields and indices) are
visible in the DB Explorer database tree.
?? Recent Object Count – this option defines number of database objects on the ‘Recent’ tab of
the DB Explorer.
?? Tables’ Details in SQL Assistant – this panel allows you to switch the SQL Assistant mode
for displaying table fields, indices or table status (table properties set on creating).
• SQL Editor
?? Fetch All - if this option is checked, all the records according to the query will be extracted
from the table, if unchecked - only those displayed on Results tab in the SQL Editor window.
?? Explain Query - if this option is checked, query plan is displayed at the bottom of the SQLEditor
window.
61
?? Show result for each query – if this option is checked, the ‘Results’ tab is activated after
executing each query.
• SQL Script
?? Abort Script on Error - if this option is checked, script execution aborts when an error occurs.
?? Rollback on Abort - this option is available only if Abort Script on Error is checked. This
option evokes automatic rollback on script execution abort.
• SQL Monitor
?? Operations - Select the operations displayed in the SQL Monitor window.
?? SQL Log - You can enable logging all the SQL Monitor events to a log file. Check option Log
SQL Monitor events to file and set the name of the log file. To clear the log file after it reaches
some definite size check option Clear log file when it is greater than and set the maximum file
size in kilobytes.
• Visual Query Builder
?? Select condition row - displays the selected condition in different row on the Criteria and
Grouping Criteria tabs of the Query Builder.
?? Drag field name - displays the dragged field name in the Builder area.
?? Hide selection when inactive - hides the selection when Query Builder is inactive.
?? Show field types in hints - displays the field type next to the field in the table box.
?? Color Palette - these options define colors of different Query Builder objects: condition row,
active caption, table client area, etc. Click an item to select a color for the appropriate Query
Builder object.
?? Visible Tabs - these options specify which Query Builder tabs are available and which are not.
Check boxes to make the appropriate tabs visible.
?? Script Format - these options specify the case formatting of keywords and functions in query
text on the Edit tab. As is saves the original case, Uppercase sets all the keywords/functions to
upper case, Lowercase sets all the keywords/functions to lower case, and First upper sets the
first letters of all keywords/functions to upper case.
?? Style - these options specify how different Query Builder objects look like - 3D, flat, etc.
Fonts
On this tab you can set the font parameters, used by MySQL Manager.
Grid
Here you can set the table data display properties: cell colors, data display format, string length,
Null value representation, font color and others.
Localization
This tab allows you to translate the MySQL Manager string resources in your native language. In
the current version the following localizations are available: English (set as default), Portuguese
(Brazilian), Chinese (Simplified), Chinese (Traditional), Danish, French, German, Spanish, Italian,
Dutch and Russian.
The list of the available languages is displayed in the 'Available Languages' panel.
To add a language, click the button 'Add', then in the 'Add Language' dialog form choose the proper
language file (*.lng file containing the translated string resources, e.g. french.lng or dutch.lng),
62
using the button and set the language name in the proper edit field. Click 'OK' when done. After
you add a language, it appears in the 'Choose program language' drop-down list. Choose it and click
'OK' to set this language as a program language.
You can edit the language names and the related files by choosing any language in the 'Available
Languages' list and clicking 'Edit'. To remove the language from the list, select the language and
click 'Delete'.
Default directory - the default directory to store the *.lng files (files where the translated string
resources are stored) is $(MySQL Manager)\Languages. You can change this directory if necessary
using button .
You can edit any language file using the Localization Editor window. Just press Shift+Ctrl+L on
any form to edit the string resources of this form. You can also create your own *.lng file based on
any of the existing ones (see $(MySQL Manager)\Languages folder to find them).
The Localization Editor window allows you to edit the
captions and hints of any MySQL Manager window, if
the selected program language is other than default.
All the window text consists of the element names and
the corresponding strings, divided by the '=' character.
These strings are what you see in the program as menu
items, window captions, button hints, etc. Edit them to
change the program appearance. Don't edit anything that
stands before the '=' character - this will not take any
effect.
For your convenience we have provided the Find and
Replace buttons on the toolbar, which call the Find
Text Dialog or the Replace Text Dialog respectively.
The Search Again button enables repeated search of the text last searched.
When you are done editing click the Save button on the toolbar to apply the changes you made.
Editor Options
This window allows you to set the parameters of database object editors.
General
?? Auto Indent - if this option is checked, each new indention will be the same as previous when
editing SQL text.
?? Insert Mode - if this option is checked, insert symbols mode is default on.
?? Use Syntax Highlight - this option enables syntax highlight in the object editor window.
?? Find Text at Cursor - if this option is checked, Text to Find field in the Find Text Dialog
window is automatically filled with the text, cursor set on.
?? Always Show Hyperlinks - if this option is checked, hyperlinks are displayed in the editor
window. To open link click with button Ctrl pressed.
63
?? Show number of lines – check this option to enable displaying number of lines in editor
window
?? Tab Stops - this option allows you to define the tab length, used when editing text.
Undo Limit - this option defines maximum number of changes, you will be able to undo.
Display
?? Visible Right Margin - this option makes the
right text margin visible.
?? Visible Gutter - this option makes the gutter
visible in the editor window.
?? Right Margin - this option defines the position of
the right text margin in the editor window.
?? Gutter Width - this option defines the gutter
width in the editor window.
?? Editor Font, Size - using these options you can
choose editor font and its size.
Color
On this tab you can set font and background colors and attributes of the text, editor uses to mark out
different text fragments: default, comments, strings, SQL keywords, numbers, links, wrong symbols,
identifiers, symbols, and selected text.
Quick Code
?? Code Completion - if this option is checked, then when you type first word symbols in the SQL
text editor you are offered some variants for the word completion in a popup list (analogue of
the Code Insight in Delphi IDE). The popup list will appear at a time, defined by the 'Delay'
option. You can activate popup lists yourself by pressing the following key combinations:
• Ctrl+Space - all SQL keywords and database objects;
• Ctrl+Alt+S - SQL glossary;
• Ctrl+Alt+T - table list;
• Ctrl+Alt+U - UDF list;
• Ctrl+Alt+F - field list;
• Ctrl+Enter - open link.
?? Code Parameters - if this option is checked, MySQL Manager automatically offers you
procedure parameter list after the procedure name and left bracket.
?? Delay - using this option you can change the time, at which the popup list will appear.
?? Code Case - this option allows you to change the case of the automatically inserted words.
?? Use Keyboard Templates - this option allows you to use keyboard templates for faster typing
regularly met expressions.
?? Emulate Typewriting - this option defines the delay of the symbols displaying.
?? Color Scope Categories - if this option is checked, the scope categories (SQL keywords, tables,
functions) are colored in the Code Completion list.
?? Sort By Scope / Sort By Name - this switch allows you to change the sorting mode of the Code
Completion list items: alphabetically by name, or by the scope categories.
64
Visual Options
This window allows you to customize the application interface style to your liking.
Scheme - select the interface scheme you like: Classic Windows or Windows XP style.
You can create your own interface
schemes by customizing any visual options
you like on the appropriate tabs ('Bars and
Menus', 'Trees and Lists', 'Edit Controls',
'Check Boxes' and 'Page Controls') and
clicking the button 'Save'. You can also
delete your own scheme by clicking
'Delete'. 'Classic Style' and 'Windows XP'
schemes can't be deleted.
All the customizing you make is displayed
on the 'Sample' panel.
Bars and Menus
?? Bar Style - choose the style of
displaying the application toolbars. Check the 'Sunken border' option to change the panel
display style.
?? Menu Animation - choose the style of animating the menu items. Use options at the right to
customize the application menus.
Trees and Lists
?? Look and Feel - choose the style of flatting the application trees and lists.
?? Tree Lines Style - choose the style of displaying the application tree lines.
?? Border Style - choose to display or not the borders of the application trees and lists.
Use options at the right to customize the object selection in the application trees and lists.
Edit Controls
?? Border Style - choose the style of displaying the borders of the application controls.
?? Button Style - choose the style of displaying the application buttons.
?? Button Transparence - choose the style of displaying the transparent buttons.
?? Edges - check the edges to display in the application controls.
Use also 'Hot Track' and 'Shadow' options to customize the application control view in accordance.
Check Boxes
?? Border Style - choose the style of displaying the borders of the application check boxes.
?? Button Style - choose the style of displaying the check box buttons.
?? Button Transparence - choose the style of displaying the transparent check box buttons.
?? Edges - check the edges to display in the application check boxes.
Use also 'Hot Track' and 'Shadow' options to customize the application check box view in
accordance.
65
Page Control
?? Tab style - choose the style of displaying the application tabs.
Use also 'Multiline pages' and 'Hot track' options to customize the application tab view in
accordance.
External Tools
This window allows you to define new tools
for working with the MySQL Manager. To add
a new tool click 'Add', set the tool name and
the path to the application (you can use the
button ). You can define a hot key to access
the tool quickly, its working directory and the
program executing parameters, if necessary
You can edit or delete the existing tools using
buttons 'Edit' and 'Delete' and also you can
change the order of the created tools using
buttons and the bottom of the window, by
dragging or by pressing Shift+Ctrl+Up,
Shift+Ctrl+Down. The created tools become
available in the Tools menu.
Keyboard Templates
This window allows you to create new keyboard templates for quicker typing regular met
expressions and to edit the existing ones.
You can deactivate the existing template by
choosing it from the list at the left of the
window and removing flag from its name.
Also you can edit template name, using
button 'Edit', delete a template, using button
'Delete' and edit template expression in the
right part of the window. For faster editing
you can use the 'Symbols' menu and buttons
'Author', 'Time', 'Date'.
To add a new template, click 'Add', set the
template name and define the template
expression. In the upper left corner of the
window you can choose the case of the
template expression.
66
Plugins options
The Plugins Options window allows you to install new plugins for working with MySQL Manager.
To call this window select the Options | Plugins Options from the popup menu.
Plugins Tab
On this tab the installed plugins are displayed. They are divided into the Common tools, that are
plugins, performed for working with the program as a whole, and Tools for objects, performed for
working with database objects. At the bottom of the window the description of the selected plugin is
displayed.
To add new plugin, click the Add plugin button.
This opens a dialog for selecting the plugin DLL
and installs the selected plugin. To remove a
plugin from the list, select it and click the
Remove plugin button.
To add a submenu to the selected menu in the
Common Tools list box, click the New
submenu button. The Rename button allows
you to change the selected submenu name. To
delete the selected submenu, click the Delete
button.
To change the position of the plugins in
submenus drag them to the submenus you need
or use the arrow buttons.
Plugin Options Tab
On this tab the information about the selected plugin is displayed.
?? Plugin is a common tool - the selected plugin is performed for working with the program as
a whole.
?? Plugin is built in object editor - the selected plugin is built-in to the database object editor
(table or UDF).
?? Plugin has options dialog - the options of the selected plugin are available for customizing
via the Options menu.
?? Unload plugin after executing - the selected plugin automatically unloads after executing.
?? Object Editors - displays the list of database objects. The plugin is available in the editors
of the selected objects.
?? Menu caption - the plugin menu caption and the shortcut for faster accessing the plugin.
?? Options menu caption - the caption of the menu item for calling the plugin option and the
shortcut for faster accessing plugin options. These boxes are available only if the selected
plugin has options.
?? Place button on toolbar – this option places a button for calling the plugin to the main
window toolbar.
67
Save Settings Wizard
Save Settings Wizard allows you to export all or partial MySQL Manager settings to single *.reg
file, which you can apply to MySQL Manager installed on another machine or use to backup
previous settings.
Step 1 - Destination & Options
Filename to export - specify a *.reg
file, to save MySQL Manager setting
to.
Set the following options:
Database registration infos - if this
option is checked, then all the
information about the registered
databases will be included in the
result file.
Database projects - if this option is
checked then all the projects you
created in the DB Explorer - Project will be included in the result file.
Environment options - if this option is checked then all the Environment Options will be
included in the result file.
Editor options - if this option is checked then all the Editor Options will be included in the result
file.
?? Visual options - if this option is checked then all the Visual Options will be included in the
result file.
?? Form placements - if this option is checked then current positions of all the MySQL Manager
forms will be included in the result file.
MRU lists - if this option is checked then all the lists of most recently used hosts will be included in
the result file.
Step 2 - Databases to save
Available - a list of databases, settings of which are available for export.
Selected - a list of databases to export their settings.
To move the database from one list to another double-click it or select it (use Ctrl or Shift to select
multiple databases) and click button > or <. To move all the databases click button >> or <<.
Step 3 - Start Export
This page is activated automatically on clicking button 'Save'. It displays the exporting process
(Exporting log).
68
Select Program Language
This dialog allows you to select a language for MySQL Manager
localization from the list of available languages, set on the
'Localization' tab of Environment Options. In the current version the
following localizations are available: English (set as default),
Portuguese (Brazilian), Chinese (Simplified), Chinese (Traditional),
Danish, French, German, Spanish, Italian, Dutch and Russian. Choose
your native language from the list and click 'OK'.