Home

Whitepaper

1. 6 4 1 ADD USER eee eh eet baile me duisi 19 6 4 2 EDIT USER tte aet e ete etate e re ERE 19 6 4 3 DELETE USER E 20 6 4 4 RENAME USER eed ete ect 20 6 5 INDEX 0 0404000 0 21 6 5 1 CREATE NEW INDEX 92 eee tet er eee eo cedro e EET CRT 21 6 5 2 EDIT INDEX Rete ete SI teo a Ate 22 6 5 2 DELETE INDEX ree ertet eee ete too 22 6 6 FOREIGN KEY 2 0004 6400000 60 23 6 6 1 MANAGE FOREIGN KEYS ace ee 23 6 6 2 CREATE FOREIGN KEYS isi 24 6 6 3 EDIT FOREIGN KEYS s ee e er ete tete ee teo Pa ER 25 deiecti pate abe PW et lbi ep 26 627 t E oe DR te eth eae e Fee ee 26 CER cm EM MES 26 cC 26 6 71 2 od 26 6 7 2 EXECUTE QUERY tr etit I 27 6 8 2 1 EXECUTE CURRENT 0 4400 0 0 27 6 8 2 4 EXECUTE ALL QUERIES 27 6 8 OUERY CEDUTOB 27 6 8 1 MULTI PLE QUERY EDITOR 0000 27 6 8 3 ADVANCED QUERY EDITOR 06 28 6 9 QUERY ANALYSI
2. 6 8 2 1 Execute Current Query To execute a particular query in a batch just place the cursor on the query to be executed Click the blue arrow icon in the icon bar Only the current query would be executed To execute current query following points can be used e Click on Main menu tool bar to execute current query e Clink on Edit menu to execute current query e Click on connection tab menu toolbar to execute current query e Query Editor itself 6 8 2 4 Execute All Queries To execute the whole batch of queries entered in the SQL window Click the blue stop arrow icon in the icon bar All queries would be executed one by one showing result set in separate tab window To execute all queries following points can be used e Click on Main menu tool bar to execute all queries e Clink on Edit menu to execute all queries e Click on connection tab menu toolbar to execute all queries e Query Editor itself 6 8 Query Editor TroSQL provides a powerful Query Editor for creating and executing queries It allows you to create and edit SQL text for a query prepare and execute selected queries It provides a lots of tools in Query Editor Toolbar for all advanced operations used for a editor Query2 x akt e ij JA 53 n SELECT FROM sakila actor Limit 0 30 lt n 6 8 1 Multiple Query Editor Tab Page 27 of 36 TroSQL User Manual User Manual Document 1
3. 9 6 2 DATABASE MANAGEMENT cccccccccceecceecccecccceceeeeceeeceeeeceeteesteeeescesteeteteterteeeteeeteeetees 10 6 2 1 CREATE 6 20 00000 0000 01 tenere 10 6 2 2 ALTER DATABASE 11 6 2 3 TRUNCATE DATABASE eee Pese pee Eee SUE 12 6 2 4 EMPTY CDATABASE Eee I vant to tiae coe 12 6 2 5 DROP DATABASE eie RR Au en 12 p EA PENA rM 12 6 2 6 BACKUP DATABASE AS 65 01 12 6 2 7 RESTORE DATABASE AS 65 1 eset eseet eset sese sees eris 15 6 3 TABLE 0 00 0 2 0200 00000000000 15 6 3 1 WHAT FS TABLE 2 15 Cu 15 6 3 2 GBEATE TABLE eit bee eee es 15 6 3 3 ALTER TABLE 5 10 16 6 3 5 EMPTY TABLE eR DRE 17 6 3 0 TRUNCATE TABLE eere teer eode owt 17 6 3 7 EE HP 17 6 3 8 RENAME TABLE bietet 17 6 3 9 EXPORT FABLES ote ee e PEE Ee 17 6 3 10 IMPORT FABLE 1 52 fea ba Deed ee 18 6 4 USER 1 0000 19 LUST CEDE 19 Page 2 of 36 TroSQL User Manual User Manual Document 1 10
4. TROSYS Productivitu Targeted www trosys com TroSQL User Manual Version 1 10 TroSys Technologies Page 1 of 36 TroSQL User Manual User Manual Document 1 10 TABLE OF CONTENTS 1 28 Das Fo bd adus 4 1 1 WHAT IS DATABASE MANAGEMENT SYSTEM 20 0 02000 0 0 0 0000 000000050054 4 1 2 WHAT IS 5 000 0 000000000000 4 1 3 WHATS ROS OL 4 1 4 WHO USES 4 2 INSTALLATION 5 2 1 TROSQL 5 5 2 1 1 SYSTEM REQUIREMENTS 5 2 1 2 INSTALLATION 5 3 TROSOL EDITIONS 5 5 4 RELEASE VERSION HISTORY 2 5 5 1 TROSQL RELEASE VERSION 5 22 0 0 0 0 0 0 000 0 5 5 TROSOL a UR Fe e o ea 6 3 2 DBOSOLBFEATUBES 6 6 5 35550 9 6 1 CONNECTION SETTING FOR 5 22020202 0 0 01 me reme rrr nra
5. e Use the Table menu to empty table 6 3 6 Truncate Table When you click on truncate table you get a warning message box click Yes to truncate all data in the table but the table is not dropped from the database You get a message if the truncation is not permissible 6 3 7 Drop Table This option is only applied when you wish to drop table To drop a table e Right click on the table in the Object Browser e Use the Table menu to drop table 6 3 8 Rename Table Rename table option only applied when you wish to rename table To rename a table e Left click on the table in the Object Browser e Use the Table menu to rename table 6 3 9 Export Table Same as Export database see export database Page 17 of 36 TroSQL User Manual User Manual Document 1 10 Export Backup Export to file Export options C Users e349496 Desktop suman actor sal e Structure only Data only Structure and data Server s Table s Source options Destination database options Destination general options Lock tables for read 7 Indude Use database statement s V Set Foreign Key Checks as 0 Flush logs before export Indude Create datbase statement s Indude Drop statement s Export is completed successfully 6 3 10 Import Table Same as Import database see export database Restore Table Import Restore SQL Execute query s stored in SQL file Import from file C Users e349
6. D departments Universities and Students TroSQL Free is having a complete set of MySQL Management features which helps students to understand database deeply with its standard applications Page 4 of 36 TroSQL User Manual User Manual Document 1 10 2 Installation 2 1 TroSQL Installation 2 1 1 System Requirements Supported Operating System 1 Windows 7 2 Windows 9x 3 Windows 2000 4 Windows XP Service Packs 5 MS Embedded XP 2 1 2 Installation Download the Setup exe from our TroSys website http www trosys com common htmpages download htm and install it by following standard procedures of installation 3 TroSQL Editions 3 1 TroSQL Editions TroSQL is avaliable in one edition TroSQL Free edition which is absolutely free 4 Release Version History 5 1 TroSQL Release Version History 1 TroSQL Free 1 10 Release Date October 07 2011 New Features Windows 7 Support Execution of multiple queries from one query editor Result Set edit and update for Queries Docking Windows Flexibility Refresh made very fast All other MySQL Objects support a Views b Functions c Procedures d Triggers e Events 9 Foreign Key Manager CON APUNE Page 5 of 36 TroSQL User Manual User Manual Document 1 10 10 Index Manager 11 Flush Manager 12 Table Creation Advanced properties support 13 Table Alteration Advanced properties support Bug Fixes 1 Date is not gettin
7. The Collation specifies the default database collation 6 2 2 Alter Database Alter Database Database name sakila Database det Database clt To Alter Database e Right click any existing database and choose Alter Database e Use the Database menu to alter a exist database e Fill in the required information Rename Database MySQL does not support renaming database through its interface at this moment Page 11 of 36 TroSQL User Manual User Manual Document 1 10 Character Set You can altered the character set specifies the default database character set Collation You can altered the Collation specifies the default database collation 6 2 3 Truncate Database Truncate Database to truncate all the tables of a database 6 2 4 Empty Database Use Empty Database to drop all the tables of a database However the Database itself is not dropped it remains as an empty shell only This is practical for import of a SQL Dump with no CREATE DATABASE statement and no USE statement 6 2 5 Drop Database Drop Database Del to drop the active database along with all associated data objects 6 2 6 Backup Database as SQL You can export database for backup or for transferring the data to another SQL server The dump will contain SQL statements to create the table and or populate the table Page 12 of 36 TroSQL User Manual User Manual Document 1 10 Export Backup Export to file Export options C lUsersle 349496 Deskt
8. dedicated computers that hold the actual databases and run only the DBMS and related software Hardware database accelerators connected to one or more servers via a high speed channel are also used in large volume transaction processing environments DBMSs are found at the heart of most database applications 1 2 Whatis MySQL MySQL is a open source Relational Database Management System RDBMS MySQL is very fast reliable and flexible Database Management System It provides a very high performance and it is multi threaded and multi user Relational Database management system The MySQL can run on UNIX window and Mac OS MySQL is used for the internet applications as it provides good speed and is very secure MySQL was developed to manage large volumes of data at very high speed to overcome the problems of existing solutions MySQL can be used for verity of applications but it is mostly used for the web applications on the internet 1 3 What is TroSQL TroSQL A Visual MySQL is most powerful and efficient MySQL Manager and Admin Tool This tool has a complete set of features along with powerful and easy GUI which helps in creation maintaining and managing the MySQL Database amp Servers The TroSQL provides a complete suite for all Database Management amp Administrations and more 1 4 Who Uses TroSQL TroSQL is used by Database Architects DBAs Database Related Software Developers Web Developers Testers Corporate Database R amp
9. foreign key relationship must be indexed 3 The fields used in the foreign key relationship must be similar in data type ew_constraint Foreign Key table Current selected table Primary Key table Reference table Source On Delete Cascade Setnull Noaction Restrict On Update Target Cascade Setnull Noaction Restrict Follow below steps to create it e Provide Foreign Key Constraint Name e You can choose Table for Reference Table along with its indexes You can see Indexes for Foreign Key Table e Select Foreign Key Fields and respective Reference Fields using Select Foreign Keys and References Fields form Page 24 of 36 TroSQL User Manual User Manual Document 1 10 Select Foreign Keys and References Fields Select Foreign Key and References Source Fields Target Fields spese i ui e You can also remove it after selection if you want after a press on Remove Button e Select proper action type and Create it 6 6 3 Edit Foreign Keys To edit existing Foreign Key press Edit button on Foreign Key Manager to invoke the Foreign Key Edition Form Page 25 of 36 TroSQL User Manual User Manual Document 1 10 Edit Foreign Key 3 Conditions for Foreign Keys 1 Both Table must be of the InnoDB table type 2 The fields used in the foreign key relationship must be indexed 3 The fields used in the foreign key relationship mus
10. query execution Tabbed interface for query results Excel style grid interface to create update alter for results Excel style grid interface to create update alter for fields Table fields values management in separate tabbed window Extensive blob editor Image Text data support View data in Grid Mode levels of export and import as MySQL Dump 4 MySQL Objects Management by powerful GUI components Optimized for managing hosted MySQL Database Create Drop Alter Tables Support for major MySQL Objects Database Tables Fields Indices Connection level management A powerful Advanced Tabbed Bases UI with rich set of Tool Bars for all levels Connection Table Query Docking Windows for easy object level management 5 Other MySQL Objects support Views Management Functions Management Procedures Management Triggers Management Events Management 6 Powerful GUI Tools Index Manager Foreign Key Manager Flush Manager 7 TEXT amp BLOB Data Manager Advanced Image Manager with Import and Save capabilities Advanced Text Manager with Import and Save capabilities 8 MySQL Server Connection Management Simple one click connection Named based connection management 9 Rich set GUI Component for Data View and Edit like Grid Docking Windows capability Excel like grid interface to create alter tables Incorporations of great sense of Artificial Intelligence for entering data Excel like grid interface to edit data with support f
11. smallint 5 unsigned picture blob email varchar 50 6 5 2 Edit Index This utility can be used to edit existing index Uu Edit Index PRIMARY Index Name PRIMARY Unique Full Text Normal Primary Select Field s v staff id tinyint 3 unsigned first name varchar 45 last name varchar 45 address id smallint 5 unsigned picture blob email varchar 50 f orf 6 5 2 Delete Index This utility can be used to delete existing index Page 22 of 36 TroSQL User Manual User Manual Document 1 10 6 6 Foreign Key Management A foreign key is a field in a relational table that matches a candidate key of another table The foreign key can be used to cross reference tables For example say we have two tables a CUSTOMER table that includes all customer data and an ORDERS table that includes all customer orders The intention here is that all orders must be associated with a customer that is already in the CUSTOMER table To do this we will place a foreign key in the ORDERS table and have it relate to the primary key of the CUSTOMER table The foreign key identifies a column or set of columns in one referencing table that refers to a column or set of columns in another referenced table The columns in the referencing table must reference the columns of the primary key or other superkey in the referenced table The values in one row of the referencing columns must occur in a single
12. 10 TroSQL you can enjoy a great feature of multiple query editor tabs in the Query Editor window You can open several files at the same time and logically separate your queries between the tabs 7 Qury2 Shk amp oat 4 EI m SELECT FROM sakila actor LIMIT 0 30 SELECT FROM sakila actor LIMIT 0 30 ij gt lt Query Analysis Query Analysis Query SELECT FROM sakila actor LIMI Query SELECT FROM sakila actor LIMIT 0 30 Result Successfull Result Successfull lt gt lt Query Result Query Result Start 10 07 11 14 51 31 SELECT FROM sakila Start 10 07 11 14 51 20 SELECT FROM sakila actor lt gt lt Query History Query History Iw E 9 Note Please rewrite WHERE condition after each modification sa X Note Please rewrite WHERE condition after each modification save Ree actor_id first_name last_name actor id first name last name last upd 1 1 will Smith 1 1 will Smith 2011 10 07 13 2 2 Tom Cruise 20 2 2 Tom Cruise 2011 10 07 13 3 3 Tom Hanks 20 3 3 Tom Hanks 2011 10 07 13 4 4 Nicolas Cage 20 4 4 Nicolas Cage 2011 10 07 13 5 5 Morgan Freeman 20 5 5 Morgan Freeman 2011 10 07 13 R amp Sean Connery on R 6 Sean Connery 2nt1 10 07 lt P lt gt Result in Tabular Form Result in Tabular Form 6 8 2 Que
13. 496 Desktop suman sakila sql Page 18 of 36 TroSQL User Manual User Manual Document 1 10 6 4 User Management TroSQL provides powerful tools for adding deleting users and editing privileges on the selected databases tables and fields 6 4 1 Add User Add user utility can be used to add new user It provides the options of privileges that you want to give to the user These privileges are Global amp User creation with global privileges settings Username test Host Password X eeeeeeccse Retype Password Data W Select Insert Z Update Delete V Administration Alter V Grant F Super V Proce V Index Reload F Shutdown Drop Lock Tables v Create Temp Tables Replication Slave Show View Create Routine Alter Routine Execute Data Max Queries Per Hour Max Updates Per Hour Max Connections Per Hour Max No of Connections Server Version 5 5 15 409 6 4 2 Edit User Edit user utility can be used to edit existing user It cannot be used to change user or host specify for that particular user Page 19 of 36 TroSQL User Manual User Manual Document 1 10 amp Edit user s global privileges settings sms s Password Retype Password eeeeeee Data Structure V Select V Insert V Update V Delete V Create Administration 7 Al
14. 8 57 71 0000 00 00 00 00 00 mw 6 Sem Connery 2011 2047 13 40 16 c 0000 00 00 00 00 00 7 7 Bruce E 2011 2047 13 41 40 0000 00 00 05 00 00 a 8 Samos Jackson 2011 30407 13 42 29 zj 0000 00 00 00 00 00 9 9 Uonarde Dagro 2011 2047 13 43 18 zj 0000 00 00 00 00 00 29 10 Brad Patt 2011 30407 13 43 58 0000 00 00 00 00 00 noc m Carrey 2011 30407 13 41 B 0000 00 00 00 00 00 12 12 Cusack 2011 1047 13 45 26 0000 00 00 00 00 00 13 13 how Depp 2011 30 07 13 45 52 B 0000 00 00 00 00 00 14 Travolts 2011 1007 13 46 19 c 0000 00 00 00 00 00 15 15 2011 20 07 13 46 59 7 0000 00 00 0 00 00 16 Kevin Spacy 2011 107 13 47 56 0000 00 00 00 00 00 12 17 Anthony Hopkins 2011 1047 13 48 24 7 0000 00 00 02 00 00 18 x Poro 2011 1047 13 49 02 7 0002 00 00 0 00 00 19 19 Dwayne Xhnson 2011 1047 13 53 28 x 2011 10 07 1 50 28 20 20 trendan Fraser 2011 1047 13 54 20 7 2011 1007 14 20 To open a table e Right click the table in the Object Browser Usethe Table menu to open table 2555995599555 Page 16 of 36 TroSQL User Manual User Manual Document 1 10 6 3 5 Empty Table This option is only applied when you wish to clear all the existing records without resetting the auto increment value Note that to reset the auto increment value while emptying your table use Truncate Table below To empty a table e Right click on the table in the Object Browser
15. Connection You can create new connections with different parameters and save it Whenever Connect To MySQL Host pops up all saved connections will be available in Saved Connection choice box You can select any saved connection and proceed with making connection MySQL Host Address Specify a host address name where the database is situated or the IP address of the server e User Name Specify a User name for connecting to the database server e Password Specify a Password for connecting to the server e Port Number TCP IP port for MySQL server and it is 3306 for MySQL Server by default Page 9 of 36 TroSQL User Manual User Manual Document 1 10 Databases You may enter several database names separated by semicolon like databasel1 database 2 If you do not enter the database name select all checkbox or by selecting database button to select from the database server Saved Password Check this option if you want TroSQL to save your MySQL password and to stop asking you to enter it in the future Rename Connection You can rename any connections by selecting the rename connection button from connection window Delete Connection You can delete any saved connections from list by selecting the delete connection button from connection window Test Connection You can verify connection established or not by selecting the test connection button from connection window Compress Protocol Check it
16. S xe rire etel tede pere HEN oa 29 6 10 WIE WS oco oo seems 31 6 10 1 CREATE MIEW ten ER RENE ROT ET ITANE 31 6 10 2 ATER 31 6 10 3 DROP VIEW aes 31 6 10 4 RENAME VIEW nne es 31 6 11 STORED PROCEDURE 31 6 12 EUNCTIONS ee deo 32 6 13 TRIGGERS dto PEN et a a T eme 32 6 14 md ce KC M o 33 6 15 FLUSH 33 6 16 SERVER INFORMATION 5 22 20000000 0000 een erret 35 7 CONTACT US FOR MORE 20144000 4 4 4 0 24 401 0 44000004 2 414 27 249 36 Page 3 of 36 TroSQL User Manual User Manual Document 1 10 1 Introduction 1 1 What is Database Management System A Database Management System DBMS is a software package with computer programs that control the creation maintenance and the use of a database It allows organizations to conveniently develop databases for various applications by database administrators DBAs and other specialists A database is an integrated collection of data records files and other database objects A DBMS allows different user application programs to concurrently access the same database Database servers are
17. age 36 of 36
18. dividual row and column is a field that contains a specific piece of information Much of the power of relational databases comes from defining the relations among the tables 6 3 2 Create Table TroSQL provides you with a very easy to use Excel like Grid to Create Table s It can be created from 4 places in direct way from TroSQL UI e Right click on the connection and choose create table e Right click on the database name in the Object Browser e Right click on the table in the Object Browser Use the Table menu to create a new table Fill in the required information Enter Table Name Enter the table name for a new table and press Ok Create Table in Grid Insert your values in the Grid and press Create Table Page 15 of 36 TroSQL User Manual User Manual Document 1 10 6 3 3 Alter Table Structure You can alter an existing table s structure using TroSQL easy to use Table Editor 2000000000008 ie H 0 Ini m 6 3 4 Open Table actor x C3 CB bal O X 62 9 0 tom record 0 Table ts not modified actor id 00 fest name last name last update level update update 1 1 2011 107 1 34 99 A m 2011 10070200 00 t n 2 Ton Cuse 2011 1097 13 50 02 A 71 2011 10 070200 00 3 3 Ton s 2011 1047 13 36 04 A 7 0000 00 00 00 00 00 4 4 Nicolas Cage 2011 30 07 13 37 32 A 7 0000 00 00 00 00 00 s 5 Morgan Freeman 2011 2097 13 3
19. e specified in the Max Size of BULK INSERTS setting 6 Include DROP statement s Inserts DROP statement s in the script to drop the named object s first before restoring the script The option does have effect on the database only tables and other objects Please Note 1 In SQL Export dialog box first select the object s like database tables to be backed up from the source database You may Select All Deselect All by dicking on checkboxes to select the object s quickly Now select the file where you want to export the data Page 14 of 36 TroSQL User Manual User Manual Document 1 10 2 Click Export to create the script batch file TroSQL does the export in a different thread so you can stop the export process anytime 6 2 7 Restore Database as SQL TroSQL allows you to execute sql text file of any size Select Tools gt Restore from SQL file Select the file and press Execute If TroSQL encounters an error it stops further execution and logs the query as well as the error message in TroSQL Restore All k Import Restore SQL Execute query s stored in SQL file Import from file C Users e349496 Desktop suman sakila sql File imported successfully ge Em uu umm 7 6 3 Table Management 6 3 1 What is Table A Table is a data structure consisting of an unordered set of horizontal rows each containing the same number of vertical columns The intersection of an in
20. ed Procedure A Stored Procedure also a Stored Proc is a sub program which is physically stored within a database in compiled form under a certain name This allows you to share the procedure between a numbers of programs Page 31 of 36 TroSQL User Manual User Manual Document 1 10 6 11 1 Create Procedure To create a procedure TroSQL will generate a template in a separate tab of the TroSQL Query Editor Window Fill the template as necessary for you and execute the query to create procedure 6 11 2 Alter Procedure To alter a procedure TroSQL will generate a template in a separate tab of the TroSQL Query Editor Window Fill the template as necessary for you and execute the query to alter the procedure 6 12 Function User defined Functions are physically stored within a database in compiled form under a certain name This allows you to share the functions between a numbers of programs The basic distinction of functions is that they can return a value as a result of their work 6 12 1 Create Function To create a Function TroSQL will generate a template in a separate tab of the TroSQL Query Editor Window Fill the template as necessary for you and execute the query to create the function 6 12 2 Alter Function To alter a Function TroSQL will generate a template in a separate tab of the TroSQL Query Editor Window Fill the template as necessary for you and execute the query to alter the Function 6 13 Triggers A Trigge
21. f 36 TroSQL User Manual User Manual Document 1 10 The LOCK will be generated for one table the one for which INSERT statements is currently generated at a time 2 Flush logs before dump Enter the table name for a new table and press Ok 3 Single transaction Single transaction has only effect with Transactional storage engines like InnoDB All tables will be backed up from their state at the time the backup started This option will ensure consistency across tables with Foreign Key Constraints The Single transaction option and the LOCK ALL Tables option are mutually exclusive Select options for destination file 1 Include USE database statement To insert the USE database name in the script 2 Include CREATE database statement To insert the CREATE DATABASE in the script 3 Set FOREIGN KEY 5 0 This option should always be checked if tables with Foreign Keys are backed up as there is no way to ensure that a parent table is written to the file before a child table If they are written in opposite order restore will fail if the option is not selected 4 Add Lock Around INSERT statement s This will ensure that no other clients have WRITE access to tables during restore as long as they are not fully restored 5 Create Bulk Insert statement s When this option is checked data from more rows will be written into a single INSERT statement Each INSERT statement will be allowed to grow to the siz
22. g set if you select from dropdown calendar box 2 In blob image scroll is not getting set for window 3 Multiple rows selection in table 2 TroSQL Free 1 8 Release Date July 31 2011 Features 1 Basic MySQL Management amp Administrations Capabilities 2 MySQL 5 x compatible 3 MySQL Objects Management by powerful GUI components 4 TEXT amp BLOB Data Manger 5 MySQL Server Connection Management 6 Rich set GUI Component for Data View and Edit like Grid 7 Multiple Query Editor 8 Powerful Data Backup and Restore Utilities 9 Database Export amp Import Utilities 10 Server information view utilities 5 TroSQL Features 3 2 TroSQL Features TroSQL provides a powerful feature list to manage your MySQL Database 1 MySQL Compatibility amp others e MySQL 5 x compatible e Written entirely C C using native MySQL C APIs e Very compact EXE 2 DBA features MySQL management in Docking Windows Restoring Importing large SQL dumps Easy and extensive User Management MySQL host management Extensive connection manager Easy Objects copy Object Browser Easy Database operations create drop alter Multiple database connections Easy Table duplication Page 6 of 36 TroSQL User Manual User Manual Document 1 10 3 Developer User Students features Docking windows for fast usage Tabbed interface for different connections Easy table operations create drop alter Tabbed interface for Query Editor Multiple
23. if you want to use Compress Protocol option for MySQL Connection Session Idle Timeout The option to define timeout for the session different from the global setting is possible with MySQL servers Most users will need not to care about it not even if server timeout setting is low TroSQL will reconnect if connection was lost since last query was run By default TroSQL connect to localhost or 127 0 0 1 with default user as root port number 3306 and password field blank Same will be field for any new connection for your convenience Please Note 1 6 2 You must connect to a MySQL Server before you can perform any task with the software Check MySQL server is open or not TroSQL connection manager provide unlimited saved connection details Database Management This section will describe all details about MySQL Database Management by TroSQL 6 2 1 Create Database Page 10 of 36 TroSQL User Manual User Manual Document 1 10 Crete Database Database name sakila Database charset DEFAULT Z Database colaton To create database e Right click the opened connection and choose New Database e Right click any existing database and choose New Database e Use the Database menu to create a new database e Fill in the required information Enter Database Name Enter the database name for a new database Character Set The Character set specifies the default database character set Collation
24. internal caches used by MySQL by Single Click The following table illustrates the use of Flush Privileges Reloads the privileges from the grant tables in the MySQL database Hosts Empties the host cache tables You should flush the host tables if some of your hosts change number or if you get the error message Host host name is blocked When more than max connect errors errors occur in a row for a given host while connection to MySQL server MySQL assumes something is wrong and blocks the host from further connection requests Flushing the host tables allow the host to attempt to connect again Page 33 of 36 TroSQL User Manual User Manual Document 1 10 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 Status Resets most status variables to zero This is something one should only use when debugging a query Tables Closes all open tables and forces all tables in use to be closed Page 34 of 36 TroSQL User Manual User Manual Document 1 10 6 16 Server Information Utilities Process List Process List form displays a list of processes To Kill the selected process just simply click the Kill Process Note e The proces
25. itable them you can edit and save record from here itself t bal Note Please rewrite WHERE condition after each modification save T Read Only Query Analysis actor id first name 1 1 Will 2 2 Tom 3 3 Tom 4 4 Nicolas 5 5 Morgan 6 6 Sean 7 Bruce 8 8 Samuel 9 9 Lionardo 10 10 Brad Result in Tabular Form last name Smith Cruise Hanks Cage Freeman Connery Willis Jackson Dicaprio Pitt last update 2011 10 07 13 34 49 2011 10 07 13 50 02 2011 10 07 13 36 04 2011 10 07 13 37 32 2011 10 07 13 38 57 2011 10 07 13 40 16 2011 10 07 13 41 40 2011 10 07 13 42 29 2011 10 07 13 43 18 2011 10 07 13 43 58 Result is not modified level update A final update 2011 10 07 02 00 00 2011 10 07 02 00 00 0000 00 00 00 00 00 0000 00 00 00 00 00 0000 00 00 00 00 00 0000 00 00 00 00 00 0000 00 00 00 00 00 0000 00 00 00 00 00 0000 00 00 00 00 00 0000 00 00 00 00 00 m Query Result Tab This window shows Results of Query Page 29 of 36 TroSQL User Manual User Manual Document 1 10 select from sakila actor Limit 0 30 Successfull Query result tab provide following information e Execution time e Transfer time e Total Time Query History Tab 10 07 11 16 SELECT FROM actor LIMIT 0 30 End 10 07 11 16 31 33 10 07 11 16 32 INSERT INTO actor actor id first name last name last update
26. level update final 1 10 07 11 16 32 SELECT FROM actor LIMIT 0 30 End 10 07 11 16 32 25 10 07 11 16 32 SELECT FROM actor LIMIT 0 30 End 10 07 11 16 32 25 10 07 11 16 33 INSERT INTO actor actor id first name last name last update level update final 1 10 07 11 16 33 SELECT FROM actor LIMIT 0 30 End 10 07 11 16 33 01 10 07 11 33 SELECT FROM LIMIT 0 30 End 10 07 11 16 33 01 10 07 11 233 INSERT INTO actor first name last name last update level update final i 10 07 11 233 SELECT FROM LIMIT 0 30 End 10 07 11 16 33 34 10 07 11 SELECT FROM actor LIMIT 0 30 End 10 07 11 16 33 34 Page 30 of 36 TroSQL User Manual User Manual Document 1 10 6 10 Views A view is a virtual table whose definition is stored in the database but that does not actually contain any data A View is a virtual table based on the result set of a SELECT statement A view contains rows and columns just like a real table The fields in a view are fields from one or more real tables in the database You can add SQL functions WHERE and JOIN statements to a view and present the data as if the data were coming from a single table Views are useful for allowing users to access a set of relations tables as if it were a single table and limiting their access to just that Views can also be used t
27. o restrict access to rows 6 10 1 Create View A dialog will appear to request the view name Once you have entered the name a template for the view creation will be typed in a separate tab of the SQL Window The template contains a command to drop the view in case it already exists and a command for the view creation where you have only to add your required selection of columns You can use the WHERE clause to set selection conditions To create a view below points can be used from TroSQL UI e Right click and select the Open View from the popup menu e Click the Create View from the object toolbar 6 10 2 Alter View A template for the view alteration will appear in a separate tab of the SQL Window The template contains a command to drop the view in case it already exists and a command for the view re creation where you can specify source fields and their names in the target view Use the WHERE clause if you need to set selection conditions To alter a view e Right click and select the Alter View from the popup menu e Click the Alter View from the object toolbar 6 10 3 Drop View To Drop a view below points can be used from TroSQL UI e Right click and select the Drop View from the popup menu e Click the Drop View from the object toolbar 6 10 4 Rename View To rename a view below points can be used from TroSQL UI e Right click and select the Drop View from the popup menu e Click the Drop View from the object toolbar 6 11 Stor
28. op suman test sal Structure only Dataonly Structure and data Server s Database s Table s Field s Y Y CHARACTER SET NAME v v COLLATIONS E DEFAULT COLLATE NAME v performance schema COLLATION CHAR 2 DESCRIPTION v phpmyadmin MAXLEN GLOBAL STATUS GLOBAL VARIABLE moms om mn lt lt lt lt lt lt lt lt Source options Destination database options Destination general options Lock tables for read v Indude Use database statement s Set Foreign Key Checks as 0 Flush logs before export v Indude Create datbase statement s V Indude Drop statement s Export is completed successfully You can export create MySQL dump at different object levels Follow below steps to do this e Connection Level Right click on the connection and export database Database Level Right click on the database in the object browser Table Level Right click on the table in the object browser e Fill in the required information for all levels Database Name Select the source Database from the list of available databases Export as SQL Specify the required option depending on what you need to export only the database structure only the data or both Export to file Specify the Export file name Select Options having effect on source while backing up 1 Lock all tables for read Page 13 o
29. or Enum Set Edit Blobs in TEXT and IMAGE mode Grid based Dates and Year formatter with Date and Time controls Page 7 of 36 TroSQL User Manual User Manual Document 1 10 10 11 12 13 Multiple Query Editor Shows query results set in tabular form editable and easy to update Multiple Query Editor with rich set of Tools Advanced Tab based interface Formats SQL statements properly Executes very large SQL scripts Execute multiple queries at once Powerful Data Backup and Restore Utilities e Database backup As MySQL Dump e Database restore As MySQL Dump e Alllevels Server Database Table amp Fields of back and restore utility Database Export amp I mport Utilities e Advance Database Export utility Advance Database Import utility e Alllevels Server Database Table amp Fields of Export and Import utility Server information view utilities Server Information View Server Variables View Server Process List View with management capabilities Server status view Page 8 of 36 TroSQL User Manual User Manual Document 1 10 6 TroSQL Help 6 1 Connection Setting for MySQL Connections Connect To N MySQL 3306 Select Database s T Use compressed protocol ki Connection Timeout Default 28800 To connect MySQL Server fill in all the parameters about the server you want to connect Details about parameters are below e New
30. r is a piece of code that is executed in response to a data modification statement that is an insert update or delete and usually ensures consistent behavior of the database Each trigger refers to a certain table in the database 6 13 1 Create Trigger To create a Trigger TroSQL will generate a template in a separate tab of the TroSQL Query Editor Window Fill the template as necessary for you and execute the query to create the Trigger 6 13 2 Alter Trigger To alter a Trigger TroSQL will generate a template in a separate tab of the TroSQL Query Editor Window Fill the template as necessary for you and execute the query to alter the Trigger Page 32 of 36 TroSQL User Manual User Manual Document 1 10 6 14 Events An Event is a scheduled job executed by the MySQL server itself Events are supported from MySQL 5 1 6 14 1 Create Event To create an Event TroSQL will generate a template in a separate tab of the TroSQL Query Editor Window Fill the template as necessary for you and execute the query to create the Event 6 14 1 Alter Event To alter an Event TroSQL will generate a template in a separate tab of the TroSQL Query Editor Window Fill the template as necessary for you and execute the query to alter the Event 6 15 Flush Manager Flush clears or reloads various internal caches used by MySQL To execute Flush you must have the Reload privilege The Flush Manager utility can be used to clear or reload various
31. row in the referenced table Thus a row in the referencing table cannot contain values that don t exist in the referenced table except potentially NULL This way references can be made to link information together and it is an essential part of database normalization Multiple rows in the referencing table may refer to the same row in the referenced table Most of the time it reflects the one parent table or referenced table to many child table or referencing table relationship Foreign Key constraints are almost functionally identical to relationships in some databases like Microsoft Access 6 6 1 Manage Foreign Keys Foreign Key can be managed by Foreign Key Manager utility Foreign Key Vereor NN Foreign Key Manager 3 Conditions for Foreign Keys 1 Both Table must be of the InnoDB table type 2 The fields used in the foreign key relationship must be indexed 3 The fields used in the foreign key relationship must be similar in data type Available Foreign Keys sakila staff 1 Constraint Reference Table 1 address 2 staff store store lt Page 23 of 36 TroSQL User Manual User Manual Document 1 10 6 6 2 Create Foreign Keys To create new Foreign Key press New button on Foreign Key Manager to invoke the Foreign Key Creation Form Create Foreign Key 3 Conditions for Foreign Keys 1 Both Table must be of the InnoDB table type 2 The fields used in the
32. ry Editor for MySQL Objects You can also take advantage of special template filled SQL window tabs when creating or altering objects views stored procedures functions triggers or events On each of these actions if called via the main menu item Objects a dedicated tab will appear providing a template for the selected object and action The object s name will appear in the tab caption 6 8 3 Advanced Query Editor Tools TroSQL provide the following advanced editing tools e Goto option e Clear Option e Undo Redo option Toogle current fold e Select line Select all Find Replace e Indent increase decrease e Brace Matching e Cut copy paste Page 28 of 36 TroSQL User Manual User Manual Document 1 10 e Show Show Line number 1 2 Show Long line marker 3 Show Line endings 4 Show White spaces e Make 1 Make uppercase 2 Make lowercase 3 Make read only mode 4 Make Overtype mode 5 Make wrap mode e Edit properties 1 Total line 2 Current line 6 9 Query Analysis Query Analysis utility at the bottom of each query editor shows you information about Databases and Tables and all the query s that has been executed in the current connection This window has three tabs Query Result in Tabular form TroSQL provides a power utility Query Result in Tabular form In this when you execute query and if it returns records then this will be displayed in Tabular form If the results are ed
33. s list cannot be edited e Effect will take once you assign the value Variables Variables utility displays the list of all server variables and their values The variables list is retrieved from the server s by issuing the SQL statement SHOW VARIABLES Status Status utility displays the list of all server status of the server The status list is retrieved from the MySQL server s by issuing the SHOW STATUS statement Server Information Server List displays a list of server information The server list provides the following information e Server name e Server version e Protocol version e Client Version Server Character set Info 1 Character set and collation number Multi byte char min length 2 Character set name 3 Collation name 4 Comment 5 Directory 6 7 Multi byte char max length Page 35 of 36 TroSQL User Manual User Manual Document 1 10 7 Contact us for more information Help To report a bug or any help please write to us at help trosys com Sales For pre sales questions please contact us at sales trosys com For sales information quick response within 24 hrs click here to contact sales Customers If you are existing customers please contact us at support trosys com Or Call us at 91 80 40985752 Office TROSYS Technologies 149 Second Floor East of NGEF 2nd H Main Kasturinagar Near Airport Road Bangalore 560043 INDIA Ph 91 80 40985752 P
34. t be similar in data type Foreign Key table Current selected table Primary Key table Reference table staff Indexes address Indexes Select Foreign Keys and Refrences Fields Source Target On Delete address_id address_id Cascade Setnull Restrict On Update Cascade Setnull Restrict Remove Remove Rest steps are self explainable and see Create Foreign Key steps mentioned above for details 6 6 4 Delete Foreign Keys To delete existing Foreign Key press Delete button on Foreign Key Manager to delete the selected Foreign Key 6 7 Query A query is used to extract data from the database in a readable format according to the user s request TroSQL provide powerful tool for working with the SQL queries Query Editor for editing the query text You can save your queries for setting schedule Just simply click to open an object for Query A right click displays the popup menu or using the object toolbar below allowing you to create new edit open and delete the selected query 6 7 1 Create Query To create a new query in Query Editor launch a new query editor from following places e Click the New Query from the object pane toolbar e Right click and select New Query from the popup menu Page 26 of 36 TroSQL User Manual User Manual Document 1 10 6 7 2 Execute Query You can execute queries in TroSQL in following ways
35. ter V Grant V Super 7 Process V Index V Reload V Shutdown Show Databases v Drop V Lock Tables Z References Client V Create Temp Tables V Replication Slave V Create User V Show View 7 Create Routine V Alter Routine V Execute V Create View V Event Max No of Connections V Trigger Server Version 5 5 1509 Save Data Max Queries Per Hour Max Updates Per Hour Max Connections Per Hour 6 4 3 Delete User Delete user utility can be used to delete user 6 4 4 Rename User Rename user can be used to change the User Page 20 of 36 TroSQL User Manual User Manual Document 1 10 6 5 Index Management To Create Edit or Delete indexes on one or multiple columns of Table select the table in the Object Browser and select Objects gt Index Manager Indexes amp sakila staff Index Name Column Name Index Type staff id PRIMARY idx fk store id store id NON UNIQUE idx fk address id address id NON UNIQUE 26 1 6 5 1 Create New Index This utility can be used to create new index with Index Name and type of key with fields in the index Page 21 of 36 TroSQL User Manual User Manual Document 1 10 Unique Full Text Normal Primary Select Field s staff_id tinyint 3 unsigned first_name varchar 45 last name varchar 45 address id

Whitepaper

Contents

Download Pdf Manuals

Related Search

Related Contents