MySQL Workbench menyediakan kemampuan ekstensif untuk membuat dan memanipulasi model database. Beberapa kemampuan ini tercantum di sini
Ini bukan daftar yang lengkap. Ini, dan kemampuan pemodelan data tambahan, dibahas di bagian berikut
Layar Beranda adalah titik awal umum untuk bekerja dengan pemodelan data. Di bagian Pemodelan Data di Ruang Kerja, Anda dapat menggunakan item tindakan di sana untuk membuat dan mengelola model, merekayasa maju dan mundur, serta membandingkan dan menyinkronkan skemata. Item tindakan ini tercantum di bawah ini
7. 1. Buka Model EER yang Ada
Mengklik item tindakan ini meluncurkan browser file. Anda kemudian dapat memilih file model yang ingin Anda muat. Tab Model MySQL baru kemudian akan dibuat, dan model Anda ditampilkan
Buka Model EER yang Ada (ikon)
Jika Anda telah membuat satu atau lebih file model, Anda cukup mengklik dua kali item model yang ingin Anda muat. Tab Model MySQL baru akan dibuat, dan model Anda ditampilkan
Anda dapat membaca lebih lanjut tentang pemodelan di bagian ini
7. 2. Buat Model EER baru
Mengklik item tindakan ini akan meluncurkan tab Model MySQL baru, dengan model kosong yang siap untuk Anda kerjakan
Anda dapat membaca lebih lanjut tentang pemodelan di bagian ini
7. 3. Buat Model EER dari Database yang Ada
Tujuan item tindakan ini adalah untuk memungkinkan Anda membuat Model EER dari database langsung yang sudah ada. Mengklik item tindakan ini meluncurkan Reverse Engineer Database. Ini adalah wizard multi-tahap yang memungkinkan Anda memilih koneksi ke server langsung, dan memilih skema dan objek yang ingin Anda rekayasa ulang ke dalam model baru Anda. Ini adalah cara mudah untuk melihat bagaimana struktur database yang ada
Untuk informasi lebih lanjut tentang reverse engineering lihat
7. 4. Buat Model EER dari Skrip SQL
Tujuan dari item tindakan ini adalah untuk memungkinkan Anda membuat model dari skrip SQL Create. Skrip semacam itu mungkin dibuat dengan tangan atau mungkin sebagai hasil dari rekayasa balik database yang ada untuk menghasilkan skrip, yang kemudian dapat dimodifikasi sesuai dengan kebutuhan. Mengklik item tindakan ini meluncurkan wizard Reverse Engineer SQL Script. Ini adalah wizard multi-tahap yang memungkinkan Anda memilih skrip yang ingin Anda buat modelnya
Untuk informasi lebih lanjut lihat
Ketika Editor Model dijalankan dari Layar Beranda, halaman Model MySQL ditampilkan. Halaman Model MySQL memiliki tiga panel utama. Editor Deskripsi, panel Daftar/Riwayat Jenis Pengguna, dan panel utama - panel Ikhtisar Model. Editor Deskripsi dan panel Daftar/Riwayat Jenis Pengguna terdapat di dalam Bilah Samping. Bilah Samping terletak di sebelah kiri secara default, tetapi dapat dipindahkan ke kanan menggunakan pengaturan dalam dialog Preferensi Meja Kerja
Gambar 7. 1. Halaman Total number of Schemata: {{SCHEMA_COUNT}}5
Bagian dalam panel Model Overview adalah
Diagram EER
Skema Fisik
Hak Istimewa Skema
Skrip SQL
Catatan Model
Untuk setiap objek bagian ini dapat ditambahkan ke proyek dengan mengklik ikon tambahkan objek yang sesuai. Anda juga dapat mengganti nama, mengedit, memotong, menyalin, atau menghapus objek di halaman ini dengan mengklik kanan. Melakukan ini akan membuka menu pop-up
Bagian dalam halaman Model MySQL dibahas di bagian berikut
Beberapa pilihan menu tidak tersedia di versi OSS aplikasi ini, dan hanya tersedia di Edisi Standar. Ini ditunjukkan jika berlaku
Opsi cetak hanya diaktifkan jika tab Diagram EER dipilih. Anda memiliki pilihan untuk mencetak model Anda langsung ke printer Anda, mencetaknya sebagai file PDF, atau membuat file PostScript. Untuk informasi lebih lanjut lihat
Opsi pencetakan hanya tersedia dalam versi komersial MySQL Workbench
Total number of Schemata: {{SCHEMA_COUNT}}_6. Default ke Total number of Schemata: {{SCHEMA_COUNT}}5
Total number of Schemata: {{SCHEMA_COUNT}}_8. Nomor versi proyek
Total number of Schemata: {{SCHEMA_COUNT}}_9. Penulis proyek
Total number of Schemata: 20. Nama proyek
Total number of Schemata: 21. Tidak dapat diedit, ditentukan oleh atribut file MWB
Total number of Schemata: 22. Tidak dapat diedit, ditentukan oleh atribut file MWB
Total number of Schemata: 2_3. Deskripsi proyek Anda
Jika halaman Total number of Schemata: {{SCHEMA_COUNT}}5 aktif, objek yang dipilih akan dihapus dari katalog dan tidak akan ada kotak dialog konfirmasi
7. 5. 1. 2. 1. Temukan Jendela Dialog
Edisi Standar Meja Kerja MySQL menyertakan fasilitas Temukan yang lebih canggih
Gambar 7. 2. Jendela Temukan
Anda dapat mencari lokasi berikut
Seluruh Model. Cari seluruh model
Pandangan saat ini. Cari tampilan saat ini saja. Ini mungkin halaman Total number of Schemata: {{SCHEMA_COUNT}}5
Semua Tampilan. Cari Total number of Schemata: 2_6 dan semua diagram EER
Objek Basis Data. Cari objek basis data saja
Angka Terpilih. Cari objek yang dipilih saat ini. Fitur ini hanya berfungsi untuk diagram EER
Masukkan teks yang ingin Anda cari di kotak daftar tarik-turun Temukan Teks. Anda juga dapat memilih salah satu atau semua kotak centang berikut
Cocok Kasus
Seluruh kata
Gunakan Ekspresi Reguler
Cari di Komentar
Cari di SQL untuk Views, SPs dll
Teks apa pun yang Anda masukkan ke dalam kotak daftar drop-down Temukan Teks dipertahankan selama durasi sesi Anda. Gunakan tombol Berikutnya atau Sebelumnya untuk menemukan kemunculan kriteria pencarian Anda
Mengklik tombol Temukan Semua membuka jendela Temukan Hasil yang berlabuh di bagian bawah aplikasi. Jika Anda mau, Anda dapat melepaskan jendela ini seperti yang Anda lakukan pada jendela lainnya
Gunakan jendela ini untuk menavigasi ke objek. Misalnya, mengklik dua kali Total number of Schemata: 2_3 objek yang terletak di diagram EER akan mengarahkan ke diagram tertentu dan memilih objek. Perhatikan bahwa properti objek ditampilkan di palet Total number of Schemata: 28
Jendela dialog Total number of Schemata: 2_9 juga dapat dibuka menggunakan kombinasi tombol Ctrl F. Gunakan Ctrl G untuk menemukan kejadian berikutnya dan Ctrl Shift G untuk menemukan kejadian sebelumnya. Tutup jendela dialog Total number of Schemata: 2_9 dengan mengklik x di sudut kanan atas atau dengan menekan tombol Esc
7. 5. 1. 2. 2. Preferensi Meja Kerja
Opsi menu ini memungkinkan Anda mengatur preferensi global untuk aplikasi MySQL Workbench
Untuk informasi lebih lanjut lihat
Opsi yang tersedia di bawah item menu ini adalah
Opsi menu ini menyediakan sarana untuk membuka jendela yang terkait dengan opsi ini
Kemampuan untuk memperbesar diagram EER juga tersedia menggunakan alat penggeser di palet {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}1. Lihat
7. 5. 1. 4. Menu Susun
Opsi menu {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}_2 hanya berlaku untuk objek pada kanvas diagram EER dan hanya terlihat jika tampilan diagram EER aktif. Opsi di bawah item menu ini adalah sebagai berikut
Opsi menu yang tersedia di bawah item menu {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}_3 adalah sebagai berikut
Tambahkan Diagram. Buat Diagram EER baru. Pintasan keyboard adalah Ctrl T
Buat Diagram Dari Objek Katalog. Buat diagram EER dari semua objek di katalog
DBDoc – Pelaporan Model. Untuk informasi tentang penggunaan opsi menu ini, lihat. Versi komersial saja
Jenis yang Ditentukan Pengguna. Memilih opsi menu ini memberi Anda kotak dialog, memungkinkan Anda untuk menambah dan menghapus tipe data yang ditentukan pengguna
Notasi Objek. Item yang tersedia di bawah opsi ini dibahas di
Notasi Relasi. Item yang tersedia di bawah opsi ini dibahas di
Properti Diagram dan Ukuran. Memilih opsi menu ini akan membuka kotak dialog ukuran diagram. Gunakan kotak dialog ini untuk menyesuaikan lebar atau tinggi kanvas. Satuan ukurannya adalah halaman;
Saat Anda memiliki tabel dengan banyak kolom, gunakan opsi menu ini untuk menambah ukuran EER
Validasi. Item yang tersedia di bawah opsi ini dibahas di. Versi komersial saja
Opsi Model. Tetapkan opsi di tingkat model. Opsi ini tidak boleh disamakan dengan opsi yang ditetapkan secara global untuk aplikasi Workbench, dan yang sekarang disebut sebagai Preferensi Workbench. Opsi model yang tersedia adalah subset dari opsi Preferensi Meja Kerja
Untuk informasi lebih lanjut tentang Preferensi Meja Kerja, lihat
7. 5. 1. 5. 1. Jendela Dialog Pelaporan Model DBDoc (Versi Komersial)
Gunakan jendela dialog ini untuk menyetel opsi untuk membuat dokumentasi model database Anda
Anda dapat tentang item menu ini secara lebih rinci di bagian berikut
7. 5. 1. 5. 3. Opsi Menu Notasi Objek
Pilihan menunya adalah sebagai berikut
Gaya notasi objek yang Anda pilih tetap ada selama sesi MySQL Workbench Anda dan disimpan bersama dengan model Anda. Saat MySQL Workbench dimulai ulang, notasi objek kembali ke default
Jika Anda berencana untuk mengekspor atau mencetak diagram EER, pastikan untuk menentukan gaya notasi terlebih dahulu. Mengubah gaya notasi setelah objek ditempatkan pada diagram dapat mengubah tampilan diagram secara signifikan
7. 5. 1. 5. 4. Opsi Menu Notasi Relasi
Untuk melihat gaya yang berbeda, atur hubungan antara dua tabel atau lebih dan pilih opsi menu yang berbeda
Gaya notasi hubungan yang Anda pilih tetap ada selama sesi MySQL Workbench Anda dan disimpan bersama dengan model Anda. Saat MySQL Workbench dimulai ulang, notasi hubungan kembali ke default, gaya {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}4
Jika Anda berencana untuk mengekspor atau mencetak diagram EER, pastikan untuk menentukan gaya notasi terlebih dahulu. Mengubah gaya notasi setelah objek ditempatkan pada diagram dapat mengubah tampilan diagram secara signifikan
7. 5. 1. 6. Menu Basis Data
7. 5. 1. 7. Menu Plugin
7. 5. 1. 8. Menu Skrip
Menu ini saat ini memiliki tiga item
7. 5. 1. 9. Menu Komunitas
Gunakan opsi menu ini untuk online dan mempelajari lebih lanjut tentang MySQL Workbench
Gunakan opsi menu ini untuk online dan mempelajari lebih lanjut tentang MySQL Workbench
read_mysql_cfg_file C:\Program Files\MySQL\MySQL Server 5.1\my.ini [('tmp_table_size', '9M'), ('myisam_sort_buffer_size', '18M'), ('table_cache', '256'), ('read_rnd_buffer_size', '256K'), ('port', '3306'), ('max_connections', '100'), ('innodb_buffer_pool_size', '18M'), ('myisam_max_sort_file_size', '100G'), ('sql-mode', '"STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"'), ('basedir', '"C:/Program Files/MySQL/MySQL Server 5.1/"'), ('default-character-set', 'latin1'), ('datadir', '"C:/ProgramData/MySQL/MySQL Server 5.1/Data/"'), ('innodb_log_buffer_size', '1M'), ('innodb_log_file_size', '10M'), ('innodb_thread_concurrency', '8'), ('read_buffer_size', '64K'), ('innodb_additional_mem_pool_size', '2M'), ('thread_cache_size', '8'), ('innodb_flush_log_at_trx_commit', '1'), ('query_cache_size', '0'), ('sort_buffer_size', '256K'), ('default-storage-engine', 'INNODB'), ('key_buffer_size', '11M')] MySQL Workbench OSS for Windows version 5.2.8 Cairo Version: 1.8.6 Rendering Mode: GDI requested (create a diagram to confirm) OpenGL Driver Version: Not Detected OS: unknown CPU: Intel(R) Core(TM)2 Duo CPU T9300 @ 2.50GHz, 1.0 GB RAM Video adapter info: Adapter type: VirtualBox Graphics Adapter Chip Type: VBOX BIOS String: Version 0xB0C2 or later Video Memory: 12288 KB
Bilah alat MySQL Workbench ditemukan tepat di bawah bilah menu. Alat berikut selalu muncul di bilah alat
Ikon dokumen baru. Klik ikon ini untuk membuat dokumen baru
Ikon folder. Klik ikon ini untuk membuka file MySQL Workbench (MWB)
Ikon simpan. Klik ikon ini untuk menyimpan proyek MySQL Workbench saat ini
Panah kanan dan kiri. Klik panah kiri untuk melakukan operasi "Undo". Klik panah kanan untuk melakukan operasi "Redo".
Alat lain muncul di bilah alat tergantung pada konteksnya
Saat kanvas diagram EER dipilih, ikon berikut muncul di sebelah kanan ikon panah
Ikon kotak beralih. Digunakan untuk menghidupkan dan mematikan jaringan
Ikon kisi. Digunakan untuk menyelaraskan objek di kanvas dengan kisi
7. 5. 2. 1. Item Toolbar khusus alat
Bilah alat juga berubah tergantung alat mana dari bilah alat vertikal yang aktif. Alat-alat ini dibahas di
Jika alat {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}_5 aktif, kotak daftar drop-down skemata, jenis mesin, dan susunan muncul di bilah alat. Properti tabel kemudian dapat dimodifikasi menggunakan Editor Properti
Saat objek dipilih, properti objek, seperti warna, dapat diubah di Editor Properti
Gunakan ikon {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}_6 di area ini untuk membuat diagram EER. Saat Anda menambahkan diagram EER, tab baru muncul di bawah bilah alat. Gunakan tab ini untuk menavigasi ke diagram EER yang baru dibuat. Diagram EER dibahas secara mendalam di
7. 5. 4. Skema Fisik
Panel {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}_7 dari halaman Total number of Schemata: {{SCHEMA_COUNT}}5 menunjukkan skema aktif dan objek yang dikandungnya
Luaskan dan kontraksikan bagian {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}7 dengan mengklik dua kali panah di sebelah kiri bilah judul {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}7. Saat bagian {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}_7 diperluas, semua skemata yang saat ini dimuat akan ditampilkan
Setiap skema ditampilkan sebagai tab; . Saat MySQL Workbench pertama kali membuka skema default, Schema: Airlines Schema: Airports2 dipilih. Anda dapat mulai bekerja dengan skema ini atau memuat file MySQL Workbench Models (MWB) baru
Ada berbagai cara untuk menambahkan skema ke panel {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}7. Anda dapat membuka file MWB, merekayasa balik skrip pembuatan MySQL, atau, jika Anda menggunakan versi komersial MySQL Workbench, Anda dapat merekayasa balik database dengan menghubungkan ke server MySQL
Anda juga dapat menambahkan skema baru dengan mengeklik tombol + di kanan atas panel {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}7. Untuk menghapus skema, klik tabnya dan gunakan tombol - yang ada di sebelah kiri tombol +. Di sebelah kiri tombol ini ada tiga tombol yang mengontrol cara ikon objek database ditampilkan. Tombol paling kiri menampilkan objek database sebagai ikon besar, tombol di sebelah kanan menampilkan ikon kecil dalam beberapa baris, dan tombol terakhir menampilkan ikon kecil dalam satu daftar
Panel {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}_7 dibagi menjadi beberapa bagian berikut
Tabel
Tampilan
Rutinitas
Grup Rutin
Setiap bagian berisi objek database yang ditentukan dan ikon yang digunakan untuk membuat objek tambahan
Setiap objek database yang ditambahkan ke kanvas diagram EER juga muncul di bagian {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}7. Untuk informasi tentang menambahkan objek ke kanvas diagram EER, lihat
Panel Schema: Airlines Schema: Airports_7 dari halaman Total number of Schemata: {{SCHEMA_COUNT}}5 digunakan untuk membuat pengguna untuk skema Anda dan juga untuk menentukan peran —
Panel Schema: Airlines Schema: Airports_9 dibagi menjadi beberapa bagian berikut
Gambar berikut menampilkan bagian Schema: Airlines Schema: Airports_9 dari tab Total number of Schemata: {{SCHEMA_COUNT}}5
Gambar 7. 3. Peran dan Keistimewaan
Untuk menambahkan peran, klik dua kali ikon Report ------ Column Name: {{COLUMN_NAME}} 2. Melakukan hal ini akan membuat peran dengan nama default Report ------ Column Name: {{COLUMN_NAME}} 3. Mengklik kanan peran akan membuka menu pop-up dengan opsi berikut
Untuk mengganti nama peran, cukup klik nama peran dan Anda kemudian dapat mengedit teksnya
Semua peran yang telah ditentukan tercantum di bawah Report ------ Column Name: {{COLUMN_NAME}} 4 di sisi kiri editor peran. Mengklik dua kali objek peran akan membuka editor peran yang terpasang di bagian bawah layar
Gambar 7. 4. Editor Peran
Pilih peran yang ingin Anda tambahkan objek. Anda dapat menarik dan melepas objek dari {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}7 ke bagian Report ------ Column Name: {{COLUMN_NAME}} 6 editor peran. Untuk menetapkan hak istimewa ke peran, pilih peran dari bagian Report ------ Column Name: {{COLUMN_NAME}} 4 lalu pilih objek di bagian Report ------ Column Name: {{COLUMN_NAME}} 6. Di bagian Schema: Airlines Schema: Airports_7 centang hak yang ingin Anda tetapkan untuk peran ini. Misalnya, peran Report ------ {{#COLUMNS}} Column Name: {{COLUMN_NAME}} {{/COLUMNS}} _0 mungkin hanya memiliki Report ------ {{#COLUMNS}} Column Name: {{COLUMN_NAME}} {{/COLUMNS}} 1 hak istimewa dan hanya untuk objek database yang diekspos melalui antarmuka web. Membuat peran dapat mempermudah proses pemberian hak kepada pengguna baru
Untuk menambahkan pengguna, klik dua kali ikon Report ------ {{#COLUMNS}} Column Name: {{COLUMN_NAME}} {{/COLUMNS}} 2. Melakukan hal ini membuat pengguna dengan nama default Report ------ {{#COLUMNS}} Column Name: {{COLUMN_NAME}} {{/COLUMNS}} 3. Mengklik dua kali pengguna ini akan membuka editor pengguna yang terpasang di bagian bawah aplikasi
Di Report ------ {{#COLUMNS}} Column Name: {{COLUMN_NAME}} {{/COLUMNS}} _4, atur nama pengguna menggunakan kotak teks Nama dan atur kata sandi menggunakan kotak teks Kata Sandi. Tetapkan satu peran atau beberapa peran kepada pengguna dengan memilih peran yang diinginkan dari area teks di sebelah kanan, lalu klik tombol {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}38 command. Using the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}37 option ensures that the script contains DDL statements only. However, if you are working with a script that also contains DML statements you need not remove them; they will be ignored
If you plan to redesign a database within MySQL Workbench and then export the changes, be sure to retain a copy of the original DDL script. You will need the original script to create an Report ------ {{#COLUMNS}} Column Name: {{COLUMN_NAME}} {{/COLUMNS}} 9 script. For more information, see
Use the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}41 option with mysqldump if you wish to create the database as well as all its objects. If there is no {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}42 statement in your script file, you must import the database objects into an existing schema or, if there is no schema, a new unnamed schema is created
If your script creates a database, a new physical schemata tab is created on the Total number of Schemata: {{SCHEMA_COUNT}}5 page
Any database objects may be imported from a script file in this fashion; tables, views, routines, and routine groups. Any indexes, keys, and constraints are also imported. Objects imported using an SQL script can be manipulated within MySQL Workbench in the same way that any other objects can
See for a tutorial on reverse engineering the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}44 database
7. 7. 9. 2. Reverse Engineering a Live Database
This section explains how to reverse engineer a live database using MySQL Workbench
Figure 7. 14. Reverse Engineer Database Wizard
The first page of the wizard enables you to set up a connection to the live database you wish to reverse engineer. This enables you to set up a new connection, or select a previously created connection. Typical information required for the connection includes hostname, username and password
Once this information has been entered, or you have selected a stored connection, click the Next button to proceed to the next page
Figure 7. 15. Connect to DBMS
Review the displayed information to make sure that the connection did not generate errors, then click Next
On the next page you can select the database schema you want to connect to among those available on the server. Cukup klik kotak centang atau kotak centang yang terkait dengan skema yang ingin Anda proses
Figure 7. 16. Select Schemata
Setelah Anda memilih skema yang diinginkan, klik tombol Berikutnya untuk melanjutkan
The wizard then displays the tasks it carried out and summarizes the results of the operation
Figure 7. 17. Fetch Object Info
Review the results before clicking Next to continue
The next page is the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}45 page. It is sectioned off by object type. This screen is of special interest if you do not wish to import all the objects from the existing database—this screen gives you the option of filtering which objects are imported. Each section has a Show Filter button. Click this button if you do not want to import all the objects of a specific type
Gambar 7. 18. Pilih Objek
For the Import MySQL Table Objects section, if you click the Show Filter button the following page is displayed
Figure 7. 19. Show Filter
This enables you to select specific tables that you wish to import. Having selected the desired tables you can optionally hide the filter by clicking the Hide Filter button
The other sections, such as MySQL Routine Objects, have similar filters available
You can click Execute to continue to the next page
The wizard then displays the tasks that have been carried out and whether the operation was successful or not. If errors were generated then you can click the Show Logs button to see the nature of the errors
Figure 7. 20. Progress
Click Next to continue to the next page
The final screen of the wizard provides a summary of the reverse engineered objects
Figure 7. 21. Results
Click Finish to exit the wizard
7. 7. 9. 2. 1. Errors During Reverse Engineering
During reverse engineering the application checks for tables and views that duplicate existing names and disallows duplicate names if necessary. If you attempt to import an object that duplicates the name of an existing object you will be notified with an error message. To see any errors that have occurred during reverse engineering you can click the button Show Logs. This will create a panel containing a list of messages, including any error messages than may have been generated. Click the Hide Logs button to close the panel
Figure 7. 22. Message Log
If you wish to import an object with the same name as an existing object, rename the existing object before reverse engineering
If you import objects from more than one schema, there will be a tab in the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}7 section of the Total number of Schemata: {{SCHEMA_COUNT}}5 page for each schema imported
You cannot reverse engineer a live database that has the same name as an existing schema. If you wish to do this, first rename the existing schema
7. 7. 10. Forward Engineering
It is possible to forward engineer a database using an SQL script or by connecting to a live database
7. 7. 10. 1. Forward Engineering Using SQL Scripts
If you choose to create a database, there are a number of export options that you may choose from
7. 7. 10. 1. 1. Membuat Skema
Figure 7. 23. SQL Export Options
The SQL Export Options displays the following facilities
Output SQL Script File
You can enter the name of your output file by entering it into the Output SQL Script File text box, or by using the Browse button to select a file. If this text box is left blank you will be able to view the generated script, but it will not be saved to a file
Generate DROP Statements Before Each CREATE Statement
Omit Schema Qualifier in Object Names
Generate Separate CREATE INDEX Statements
Choosing this option creates separate statements for index creation instead of creating indexes as part of a Total number of Schemata: {{SCHEMA_COUNT}}69 statement
Add SHOW WARNINGS after every DDL statement
Do Not Create Users. Only Export Privileges
To update the privileges of existing users as opposed to creating new users, select this check box. Exporting privileges for nonexistent users will result in errors when you execute the Total number of Schemata: {{SCHEMA_COUNT}}82 script. Exporting users that already exist, will also result in an error
Generate INSERT Statements for Tables
If you have added any records to a table using the Insert tab of the MySQL Table Editor, choose this option. For more information about inserting records see
Clicking Next takes you to the SQL Object Export Filter page where you can select the objects you wish to export
Figure 7. 24. SQL Object Export Filter
Precise control over the objects to be exported can be fine tuned by clicking the Show Filter button. Once the objects to be exported have been selected it is possible to reduce the expanded panel by clicking the same button, now labelled Hide Filter
Having selected the objects you wish to export you can click the Next button to review the script that has been generated
Figure 7. 25. Review Generated Script
The Finish button saves the script file and exits. You may return to the previous screen using the Back button
You can then use the saved script to create a database
7. 7. 10. 1. 2. Altering a Schema
Figure 7. 26. Options
This first screen enables you to select a SQL script and compare it with the model currently in MySQL Workbench. The difference between the two models will be used to create an alter script that can be used to modify the target schema so that it matches the model held in MySQL Workbench. You can also simply view the script generated, rather than saving it to a file, by leaving the Output File text box empty
The script selected as the Input File must use full schema qualifiers, such as {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}50, otherwise MySQL Workbench will not be able to generate a useable alter script
Pressing Next brings you to the Review SQL Script screen
Figure 7. 27. Naskah
Here you can review and change the alter script that will be generated. Make any changes you wish and, if you are happy with the changes, save the Report ------ {{#COLUMNS}} Column Name: {{COLUMN_NAME}} {{/COLUMNS}} 9 script to file using the Save to File . tombol. You can also click the Execute button to write the script to the previously specified output file
The generated script can then be used to update the database
7. 7. 10. 2. Forward Engineering to a Live Server
Use forward engineering to export your schema design to a MySQL server
The first page to be displayed is Catalog Validation (validation is only available in the Standard Edition)
Figure 7. 28. Validasi Katalog
Klik Jalankan Validasi untuk memvalidasi katalog
Klik Berikutnya untuk melanjutkan
The next page enables you to set options for the database to be created. These options are as described in
Figure 7. 29. Options
Select the required options and then click Next
The next page enables you to select the objects to forward engineer
Figure 7. 30. Select Objects to Forward Engineer
To select a subset of objects to forward engineer use the Show Filter/Hide Filter button, and then select specific objects. Once you have selected your objects click Next to continue
On the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}52 page you may review and edit the SQL script that will be executed
Figure 7. 31. Review Script
Click Next to continue if you are satisfied with the generated script
The next step of the process is to connect to a MySQL server in order to create the new database schema. This page enables you to use a previously stored connection, or enter the connection parameters
Figure 7. 32. Set parameters for connecting to a DBMS
Once the connection parameters have been set click Execute. The next page of the wizard displays the results of the forward engineering process
Figure 7. 33. Set parameters for connecting to a DBMS
You can confirm the creation of the schema by connecting to the target MySQL server and issuing the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}53 command
7. 7. 10. 3. Database Synchronization
It is possible to synchronize a model in MySQL Workbench with a live database. By default, the synchronization process will change the live database to be the same as the model, but this is configurable during the synchronization process
MySQL Workbench enables control over the direction of synchronization, and the objects synchronized, in a completely flexible way. You can choose to synchronize only certain tables, allow synchronization to the live database only, allow synchronization from the live database to the model only, or a combination of directions. In effect you have complete control as to whether the synchronization is unidirectional or bidirectional, and which objects exactly are subject to synchronization. This is all controlled in the Select Changes to Apply page of the synchronization wizard
Figure 7. 34. Model and Database Differences
In the above example the live database consists of Total number of Schemata: {{SCHEMA_COUNT}}19, Total number of Schemata: {{SCHEMA_COUNT}}20 and {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}56. In MySQL Workbench an additional table, {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}57, has been created, along with a relationship between it and {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}56. Further, {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}59 exists in the live database, but not in the model. Tindakan yang dikonfigurasi untuk terjadi akan mengakibatkan {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}56 diubah (untuk menyertakan hubungan dengan {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}57), {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}57 dibuat dan {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}59 dijatuhkan, di database langsung. It is possible to reconfigure this though
The next example shows how the direction of synchronization can be changed
Figure 7. 35. Controlling Synchronization Direction
In this case the synchronization direction has been changed so that rather than the default action of {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}59 being dropped from the live database, it will be incorporated into the MySQL Workbench model
For convenience the wizard provides three additional buttons to allows synchronization directions to be applied to a group of selected changes. The Update Model button causes the selected changes to only be applied to the model itself
Figure 7. 36. Update Model Button
In the above example {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}65 would be added to the model
The Ignore button causes the selected changes to be ignored, no synchronization will take place for those changes
Figure 7. 37. Ignore Button
In the above example no changes would take place
The Update Source button causes the selected changes to only update the live database
Figure 7. 38. Update Source Button
In this example {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}66 would be added to the live database and {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}65 would be dropped from the live database
It is also possible to control individual changes by clicking the arrows. Clicking an arrow causes it to change between the three available synchronization directions
Figure 7. 39. Click arrows to change direction of synchronization
In the above example {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}66 will be created in the live database, and {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}65 will be created in the model
7. 7. 10. 4. Creating a Catalog Diff Report
Having started the wizard the first step is to specify the catalogs you wish to compare. For example, you may simply wish to compare your live database against your current MySQL Workbench model
Figure 7. 40. Catalog Sources
You then proceed through the wizard, providing connection information if accessing a live database. The wizard will then produce a catalog diff report showing the differences between the compared catalogs
Figure 7. 41. Catalog Diff Report
This chapter contains three short tutorials intended to familiarize you with the basics of MySQL Workbench. These tutorials show how MySQL Workbench can be used both to design and to document databases
Creating a database from scratch is the focus of and exploring the graphic design capabilities of MySQL Workbench is touched upon in . Both these tutorials show the database design capabilities of MySQL Workbench
Importing an SQL data definition script is probably the quickest way to familiarize yourself with MySQL Workbench—this tutorial makes use of the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}44 database and emphasizes the use of MySQL Workbench as a documentation tool. Contoh yang diambil dari database sakila digunakan di seluruh dokumentasi sehingga melakukan tutorial ini bisa sangat membantu dalam memahami MySQL Workbench
7. 8. 1. Importing a Data Definition SQL Script
For this tutorial use the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}44 database script found in the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}72 section of the http. //dev. mysql. com/doc/ page
If the file was successfully imported, the application's status bar reads, {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}73 To view the newly imported script, expand the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}7 section by double-clicking the arrow on the left of the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}7 title bar. Select the tab labelled sakila
Yo may also wish to remove the default schema tab, Schema: Airlines Schema: Airports2. Do this by selecting this tab and then clicking the - button on the upper right in the Physical Schemata panel
To view all the objects in the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}44 schema, you may need to expand the Physical Schemata window. To do this move the mouse pointer anywhere over the gray area that defines the lower edge of the Physical Schemata window. Hold down the right mouse button and move the mouse to adjust the size of the window
Once you've expanded the window, all the objects in the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}44 database should be visible. Tables appear at the top followed by views and then routines. There are no routine groups in this schema, but you should see the Routine Groups section and an {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}79 icon
For a complete description of importing a MySQL create script see
7. 8. 1. 1. Adding an EER Diagram
Untuk membuat diagram EER untuk database {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}_44, pertama tambahkan diagram EER dengan mengklik dua kali ikon {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}6 di panel Diagram EER. Ini harus membuat dan membuka Total number of Schemata: {{SCHEMA_COUNT}}24 baru
The Total number of Schemata: {{SCHEMA_COUNT}}24 canvas is where object modeling takes place. To add a table to the canvas, select the Catalog tab in the middle panel on the right side of the application. This should display any schemata that appear in the MySQL Model tab
Find the sakila schema and expand the view of its objects by clicking the + button to the left of the schema name. Expand the tables list in the same way
You can add tables to the EER canvas by picking them up from the Catalog panel and placing them on the canvas. Drop the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}84 table and the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}85 table onto the canvas
Figure 7. 42. Adding Tables to the Canvas
MySQL Workbench automatically discovers that {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}86 has been defined as a foreign key referencing the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}87 field. Drop the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}88 table onto the canvas and immediately you should see the relationship between the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}88 table and the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}85 table. ( See to view a PNG file of all the relationships in the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}44 database. )
Choose the Total number of Schemata: 28 tab of the panel on the lower right and then click one of the tables on the canvas. This displays the properties of the table in the Total number of Schemata: 28 window. While a table is selected you can use the Total number of Schemata: 28 window to change a table's properties. For example, entering {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}96 for the color value will change the color accent to red
Changing the color of a table is a good way to identify a table quickly—something that becomes more important as the number of tables increases. Changing the color of a table is also an easy way to identify a table in the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}1 panel. This panel, the uppermost panel on the left side of the screen, gives a bird's eye view of the entire EER canvas
7. 8. 2. Using the Default Schema
When you first open MySQL Workbench a default schema, Schema: Airlines Schema: Airports2 appears as the left-most tab of the Physical Schemata section of MySQL Workbench. You can begin designing a database by using this default schema
Figure 7. 43. The Default Schema
To change the name of the default schema, double-click the schema tab. This opens a schema editor window docked at the bottom of the application. To undock or redock this window, simply double-click anywhere in the editor title bar
To rename the schema, use the text box labeled Name. Setelah Anda mengganti nama skema, ikon petir muncul rata kanan di kotak teks Nama, menunjukkan bahwa perubahan lain sedang menunggu. Click the Comments text area and a dialog box opens asking if you wish to rename all schema occurrences. Clicking Yes ensures that your changes are propagated throughout the application. Add comments to the database and change the collation if you wish. Close the schema editor by clicking the x button
7. 8. 2. 1. Creating a New Table
Create a new table by double-clicking the Add Table icon in the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}7 panel. Doing this opens the table editor docked at the bottom of the application. If you wish, you can undock or dock this editor in exactly the same way as the schema editor window
Use the first tab of the table editor to change the name, collation, and engine. You may also add a comment
Add columns to the new table by selecting the Columns tab. Use the default column name or enter a new name of your choosing. Use the Tab key to move to the next column and set the column's data type
Altering the table by adding indexes or other features is also easily done using the table editor
7. 8. 2. 2. Creating Other Schema Objects
Additional objects such as views or routines can be added in the same way as tables
Any objects you have created can be found in the Catalog palette on the right. To view these schema objects select the Catalog tab in the middle palette on the right. View all the objects by clicking the + button to the left of the schema name
On the Total number of Schemata: {{SCHEMA_COUNT}}5 page, double-click the Add Diagram icon. This creates and opens a new Total number of Schemata: {{SCHEMA_COUNT}}24
Figure 7. 44. Adding an EER Diagram
From an EER diagram page you can graphically design a database
The tools in the vertical toolbar on the left of the EER Diagram tab are used for designing an EER diagram. Start by creating a table using the table tool. The table tool is the rectangular grid in the middle of the vertical toolbar. Mousing over it shows the message, Schema: Airlines Schema: Airports02
Clicking this tool changes the mouse pointer to a hand with a rectangular grid. Create a table on the canvas by clicking anywhere on the Total number of Schemata: {{SCHEMA_COUNT}}24 grid
The table name defaults to Total number of Schemata: {{SCHEMA_COUNT}}19. Change the name by entering Schema: Airlines Schema: Airports05 into the Name. text box. Notice that the name of the tab in the table editor and the name of the table on the canvas, both change to this new value
Pressing Tab or Enter while the cursor is in the table name text box, selects the Columns tab of the table editor and creates a default column named, Schema: Airlines Schema: Airports06
Pressing Tab or Enter again sets the focus on the Schema: Airlines Schema: Airports07 drop-down list box with Schema: Airlines Schema: Airports08 selected. Notice that a field has been added to the table on the EER canvas
Pressing Tab yet again and the focus shifts to adding a second column. Add a Total number of Schemata: 23 and a Schema: Airlines Schema: Airports10 column. When you are finished, close the table editor, by clicking the x button on the top left of the table editor
7. 8. 3. 2. Create a Foreign Key
Select the table tool again and place another table on the canvas. Name this table Schema: Airlines Schema: Airports11. Next click the Schema: Airlines Schema: Airports12 tool
First click the Schema: Airlines Schema: Airports11 table; notice that a red border indicates that this table is selected. Next click the Schema: Airlines Schema: Airports05 table. Doing this creates a foreign key in the Schema: Airlines Schema: Airports11 table, the table on the “many” side of the relationship. This relationship between the two tables is shown graphically in crow's foot notation
Revert to the default mouse pointer by clicking the arrow at the top of the vertical toolbar. Click on the Schema: Airlines Schema: Airports11 table and select the Foreign keys tab
Click the Foreign key Name text box. Tabel yang direferensikan harus ditampilkan di kolom Tabel yang Direferensikan dan kolom yang sesuai di kolom Kolom yang Direferensikan
To delete the relationship between two tables, click the line joining the tables and then press Ctrl Delete
Experiment with the other tools on the vertical toolbar. Hapus hubungan dengan memilih alat penghapus dan mengklik garis yang menghubungkan dua tabel. Create a view, add a text object, or add a layer
7. 8. 4. Documenting the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}44 Database
This chapter highlights the capabilities of MySQL Workbench as a documentation tool using the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}44 database as an example. Ini adalah contoh database yang disediakan oleh MySQL dan ditemukan di bagian {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}72 dari http. //dev. mysql. com/doc/ page. An EER diagram is an invaluable aid to a quick understanding of any database. Tidak perlu membaca pernyataan definisi tabel;
Anda juga dapat melihat bagaimana tabel terkait;
7. 8. 4. 1. File PNG dari {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}_44 Database
Figure 7. 45. The {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}44 EER Diagram
The object notation style used in is Schema: Airlines Schema: Airports23. This notation only shows primary keys and no other columns so it is especially useful where space is at a premium. The relationship notation is the default, Crow's Foot
As the connection lines show, each table is related to at least one other table in the database (with the exception of the Schema: Airlines Schema: Airports24 table). Some tables have two foreign keys that relate to the same table. For example the Total number of Schemata: 202 table has two foreign keys that relate to the Schema: Airlines Schema: Airports26 table, namely Schema: Airlines Schema: Airports27 and Schema: Airlines Schema: Airports28. Where there is more than one relationship between two tables, the connection lines run concurrently
Identifying and nonidentifying relationships are indicated by solid and broken lines respectively. For example, the foreign key Schema: Airlines Schema: Airports29 is part of the primary key in the Schema: Airlines Schema: Airports30 table so its relationship to the Schema: Airlines Schema: Airports31 table is drawn with a solid line. On the other hand, in the {{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}85 table, the foreign key, Schema: Airlines Schema: Airports33, is not part of the primary key so the connection uses a broken line
The printing menu options are grayed if an EER Diagram is not active. The menu options are as follows
Use this option to choose the paper size, orientation, and margins
Use this option to send your EER Diagram directly to the printer. This option generates a preview before printing. From the preview you can adjust the scale of the view and also choose a multi-page view. Clicking the printer icon at the top left of this window, prints the currently selected EER Diagram. Close the print preview window if you need to adjust the placement of objects on the EER Diagram canvas
Use this option to create a PDF file of your EER Diagram
Use this option to create a PostScript file of your EER Diagram
7. 10. MySQL Workbench Schema Validation Plugins (Commercial Version)
MySQL Workbench provides validation modules so that you can test your models before implementing them
The tasks performed by the validation modules are outlined in what follows
7. 10. 1. General Validation
The types of validation and examples that violate validation are listed in what follows
Empty Content validation
A table with no columns
A routine or view with no SQL code defined
A routine group containing no routines
A table, view, or routine not referenced by at least one role
Seorang pengguna tanpa hak istimewa
Objek seperti tabel yang tidak muncul pada setidaknya satu Diagram EER
Table Efficiency Validation
A table with no primary key
A primary key that does not use an integer-based data type
A foreign key that refers to a column with a different data type
Duplicated Identifiers Validation
Duplicate object names
Duplicate role or user names
Duplicate index or routine names
Consistency Validation
Use of the same column with columns of differing data types
Logic Validation
A foreign key that refers to a column other than the primary key in the source table
Any object that is object is either read- or write-only by role definition
Placeholder objects left over from reverse engineering
7. 10. 2. MySQL-Specific Validation
The types of MySQL-specific validation and examples that violate validation are listed in the following
Integrity Violation
An object name longer than the maximum allowed
A foreign key defined for an engine type that doesn't support foreign keys (not yet implemented)
A view or routine that references a nonexistent table (not yet implemented)
A default value that does not match a column's data type
An invalid partitioning scheme
Syntax Violation
A routine, trigger, or view with incorrect SQL syntax
A reserved keyword used as an identifier
Use of an invalid character
7. 11. Customizing DBDoc Model Reporting Templates
This document aims to provide an overview of creating and modifying DBDoc Model Reporting templates, as used by MySQL Workbench
The MySQL Workbench DBDoc Model Reporting system is based on the Google Template System. This document does not attempt to explain the Google Template System in detail. The Google document How To Use the Google Template System provides a useful overview of how the Google Template System works
The templates employed by the DBDoc Model Reporting system are text files that contain Schema: Airlines Schema: Airports34. These text files are processed by the template system built into MySQL Workbench, and the markers replaced by actual data. The output files are then generated. It is these output files, typically HTML or text, that are then viewed by the user
Markers can be of six types
Template Include
Comment
Set delimiter
Pragma
Variable
Section start and Section end
The last two are the most commonly used in MySQL Workbench templates and these important markers will be briefly described in the following sections
Variables
The use of variables in the templates is straightforward. Any variables denoted by markers in the template file, will be replaced by their corresponding data, prior to the output file being generated. The mapping between variables and their corresponding data is stored by MySQL Workbench in what is known as a Data Dictionary. In the data dictionary the variable name is the key and the variable's corresponding data is the value. The data dicionaries are built by MySQL Workbench and filled with the data contained in the model being processed
Sebagai contoh, cuplikan kode berikut menunjukkan bagian dari file template
Total number of Schemata: {{SCHEMA_COUNT}}In the generated output file the variable {{SCHEMA_COUNT}} will be replaced by the number of schemata in the model
Total number of Schemata: 2Variabel dapat muncul sebanyak yang diperlukan dalam file template
Sections
Bagian digunakan untuk melakukan iterasi dalam template. When MySQL Workbench exchanges the variables in a section for data it will do so iteratively, using all data in the data dictionary in which the variable is defined. MySQL Workbench builds the data dictionaries according to the model currently being processed
Again, this is best illustrated by example
{{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}In the previous code snippet the section start is indicated by the Schema: Airlines Schema: Airports35 marker. Akhir bagian ditandai dengan penanda ________28______36. When the template is processed, MySQL Workbench will note the section and iterate the section until the variable data for Schema: Airlines Schema: Airports37 in the corresponding data dictionary is exhausted. For example, if the model being processed contains two schemata, the output for the section might resemble the following
Schema: Airlines Schema: AirportsThat is, the model contains two schemata, Airlines and Airports
Data Dictionaries
It is important to understand the relationship between sections and data dictionaries in more detail. In a data dictionary the key for a variable is the variable name, a marker. The variable value is the variable's data. The entry for a section in a data dictionary is different. For a section entry in a data dictionary, the key is the section name, the marker. However, the value associated with the key is a list of data dictionaries. In MySQL Workbench each section is usually associated with a data dictionary. You can think of a section as activating its associated dictionary (or dictionaries)
When a template is processed, data dictionaries are loaded in a hierarchical pattern, forming a tree of data dictionaries. This is illustrated by the following table
Data DictionaryLoads Data DictionaryMAINSCHEMATASCHEMATATABLES, COLUMNS (Detailed is true), FOREIGN_KEYS (Detailed is true), INDICES (Detailed is true)TABLESREL_LISTING, INDICES_LISTING, COLUMNS_LISTING, TABLE_COMMENT_LISTING, DDL_LISTINGCOLUMNS_LISTINGCOLUMNS (Detailed is false)REL_LISTINGREL (Detailed is false)INDICES_LISTINGINDICES (Detailed is false)
The root of the tree is the main dictionary. Additional dictionaries are then loaded from the root to form the dictionary tree
If a template has no sections in it, then any variables used in the template will be looked up in the main dictionary. If a variable is not found in the main dictionary (which can be thought of as associated with the default, or main, section) then no data will be generated in the output file for that marker
Evaluation of variables
The tree structure of the data dictionaries is important when it comes to evaluation of variables. As variables are defined in data dictionaries, their associated value only has meaning when that particular data dictionary is active, and that means when the section associated with that data dictionary is active. When a variable lookup occurs, the system will check the data dictionary associated with the current section. If the variable value can be found there the replacement is made. However, if the variable's value is not found in the current data dictionary then the parent data dictionary will be checked for the variable's value and so on up the tree until the main data dictionary, or root, is reached
This can best be illustrated by an example. Assume we want to display the names of all columns in a model. Consider the following template as an attempt to achieve this
Report ------ Column Name: {{COLUMN_NAME}}This template will produce no output, even for a model that contains many columns. In this example the only data dictionary active is the main dictionary. Schema: Airlines Schema: Airports38 however is stored in the Schema: Airlines Schema: Airports39 data dictionary, which is associated with the Schema: Airlines Schema: Airports39 section
With this knowledge the template can be improved as follows
Report ------ {{#COLUMNS}} Column Name: {{COLUMN_NAME}} {{/COLUMNS}}This still does not produce output. Referring to the table explains why. Kamus data ________28______39 memiliki kamus induk Schema: Airlines Schema: Airports42. Schema: Airlines Schema: Airports42 has the parent Schema: Airlines Schema: Airports44, which has the parent Schema: Airlines Schema: Airports45, whose parent is the main dictionary. Remember that for a dictionary to be involved in variable lookup, its associated section must currently be active
So to achieve the desired output we would need the template to be something like the following
Report ------ {{#SCHEMATA}} {{#TABLES}} {{#COLUMNS_LISTING}} {{#COLUMNS}} Column Name: {{COLUMN_NAME}} {{/COLUMNS}} {{/COLUMNS_LISTING}} {{/TABLES}} {{/SCHEMATA}}The following template is the same, but with explanatory comments added
Report ------ {{! Main dictionary active}} {{#SCHEMATA}} {{! SCHEMATA dictionary active}} {{#TABLES}} {{! TABLES dictionary active}} {{#COLUMNS_LISTING}} {{! COLUMNS_LISTING dictionary active}} {{#COLUMNS}} {{! COLUMNS dictionary active}} Column Name: {{COLUMN_NAME}} {{! COLUMN_NAME variable is looked-up, and found, in COLUMNS data dictionary}} {{/COLUMNS}} {{/COLUMNS_LISTING}} {{/TABLES}} {{/SCHEMATA}}Imagine now that for each column name displayed you also wanted to display its corresponding schema name, the template would look like this
Report ------ {{#SCHEMATA}} {{#TABLES}} {{#COLUMNS_LISTING}} {{#COLUMNS}} Schema Name: {{SCHEMA_NAME}} Column Name: {{COLUMN_NAME}} {{/COLUMNS}} {{/COLUMNS_LISTING}} {{/TABLES}} {{/SCHEMATA}}When variable lookup is performed for Schema: Airlines Schema: Airports46 the Schema: Airlines Schema: Airports39 dictionary will be checked. As the variable is not found there the parent dictionary will be checked, Schema: Airlines Schema: Airports42, and so on until the variable is eventually found, where it is held, in the Schema: Airlines Schema: Airports45 dictionary
If there are multiple schemata in the model the outer section will be iterated over a matching number of times, and Schema: Airlines Schema: Airports46 will accordingly have the correct value on each iteration
It's important to always consider which dictionary needs to be active (and which parents) for a variable to be evaluated correctly. In the following section you will find a table that helps you identify section requirements
7. 11. 1. Supported Template Markers
A list of supported markers follows. These markers can be used in any template, including custom templates
Marker textTypeData Dictionary defined in (if variable) or parent dictionary (if section)Corresponding dataTITLEVariableMAINTitle of the reportGENERATEDVariableMAINDate and time that the report was generatedSTYLE_NAMEVariableMAINThe name of the style selected in MySQL Workbench. This is typically used to load the corresponding CSS file, depending on the name of the style selected in MySQL Workbench. SCHEMA_COUNTVariableMAINThe total number of schemata in the modelPROJECT_TITLEVariableMAINPROJECT_NAMEVariableMAINPROJECT_AUTHORVariableMAINPROJECT_VERSIONVariableMAINPROJECT_DESCRIPTIONVariableMAINPROJECT_CREATEDVariableMAINPROJECT_CHANGEDVariableMAINTOTAL_TABLE_COUNTVariableMAINTotal number of tables in all schemata contained in the model. TOTAL_COLUMN_COUNTVariableMAINTotal number of columns in all tables in all schemata in the model. TOTAL_INDEX_COUNTVariableMAINTotal number of indexes in the model. TOTAL_FK_COUNTVariableMAINTotal number of foreign keys in the model. SCHEMATASectionMAINUsed to mark the start and end of a SCHEMATA section. The SCHEMATA data dictionary becomes active in this section. SCHEMA_NAMEVariableSCHEMATAThe name of a schema in the modelSCHEMA_IDVariableSCHEMATAThe ID of the schemaTABLE_COUNTVariableSCHEMATATotal total number of tables in the current schemaCOLUMN_COUNTVariableSCHEMATAThe total number of columns in the current schemaINDICES_COUNTVariableSCHEMATAThe total number of indexes in the current schemaFOREIGN_KEYS_COUNTVariableSCHEMATAThe total number of foreign keys in the current schemaTABLESSectionSCHEMATAMarks the start and end of a TABLES section. The TABLES data dictionary becomes active in this section. TABLE_NAMEVariableTABLESThe table nameTABLE_IDVariableTABLESThe table IDCOLUMNS_LISTINGSectionTABLESMarks the start and end of a COLUMNS_LISTING section. The COLUMNS_LISTING data dictionary becomes active in this section. COLUMNSSectionCOLUMNS_LISTINGMarks the start and end of a COLUMNS section. The COLUMNS data dictionary becomes active in this section. COLUMN_KEYVariableCOLUMNSWhether the column is a primary key or notCOLUMN_NAMEVariableCOLUMNSThe name of the columnCOLUMN_DATATYPEVariableCOLUMNSThe data type of the columnCOLUMN_NOTNULLVariableCOLUMNSWhether or not the column allows NULLsCOLUMN_DEFAULTVALUEVariableCOLUMNSThe default value of entries in this columnCOLUMN_COMMENTVariableCOLUMNSAny comment for this columnCOLUMN_IDVariableCOLUMNSThe ID of the columnCOLUMN_KEY_PARTVariableCOLUMNS (if detailed)The type of column keyCOLUMN_NULLABLEVariableCOLUMNS (if detailed)Can the column contain NULLsCOLUMN_AUTO_INCVariableCOLUMNS (if detailed)Does the column auto-incrementCOLUMN_CHARSETVariableCOLUMNS (if detailed)The character set used by the columnCOLUMN_COLLATIONVariableCOLUMNS (if detailed)The collation used by the columnCOLUMN_IS_USERTYPEVariableCOLUMNS (if detailed)Whether or not the column is a user typeINDICES_LISTINGSectionTABLESMarks the start and end of an INDICES_LISTING section. The INDICES_LISTING data dictionary becomes active in this section. INDICESSectionINDICES_LISTINGMarks the start and end of an INDICES section. The INDICES data dictionary becomes active in this section. INDEX_NAMEVariableINDICESThe name of the indexINDEX_PRIMARYVariableINDICESIs this a primary indexINDEX_UNIQUEVariableINDICESIs this a unique indexINDEX_TYPEVariableINDICESThe type of index e. g. PRIMARYINDEX_KINDVariableINDICESThe kind of indexINDEX_COMMENTVariableINDICESA comment for the indexINDEX_IDVariableINDICESThe ID of the indexINDEX_COLUMNSSectionINDICESMarks the start and end of an INDEX_COLUMNS section. The INDEX_COLUMNS data dictionary becomes active in this section. INDEX_COLUMN_NAMEVariableINDEX_COLUMNSThe index column nameINDEX_COLUMN_ORDERVariableINDEX_COLUMNSThe index column order e. g. ascending, descendingINDEX_COLUMN_COMMENTVariableINDEX_COLUMNSA comment for the index columnINDEX_KEY_BLOCK_SIZEVariableINDEX_COLUMNS (if detailed)Index key block sizeREL_LISTINGSectionTABLESMarks the start and end of a REL_LISTING section. The REL_LISTING data dictionary becomes active in this section. RELSectionREL_LISTINGMarks the start and end of a REL section. The REL data dictionary becomes active in this section. REL_NAMEVariableREL, FOREIGN_KEYSRelationship nameREL_TYPEVariableREL, FOREIGN_KEYSRelationship typeREL_PARENTTABLEVariableREL, FOREIGN_KEYSParent table of relationshipREL_CHILDTABLEVariableREL, FOREIGN_KEYSChild table of relationshipREL_CARDVariableREL, FOREIGN_KEYSRelationship cardFOREIGN_KEY_IDVariableRELForeign key IDFOREIGN_KEYSSectionSCHEMATAMarks the start and end of a FOREIGN_KEYS section. The FOREIGN_KEYS data dictionary becomes active in this section. FK_DELETE_RULEVariableFOREIGN_KEYSForeign key delete ruleFK_UPDATE_RULEVariableFOREIGN_KEYSForeign key update ruleFK_MANDATORYVariableFOREIGN_KEYSForeign key mandatoryTABLE_COMMENT_LISTINGSectionTABLESMarks the start and end of a TABLE_COMMENT_LISTING section. The TABLE_COMMENT_LISTING data dictionary becomes active in this section. TABLE_COMMENTVariableTABLE_COMMENT_LISTINGA comment for the tableDDL_LISTINGSectionTABLESMarks the start and end of a DDL_LISTING section. The DDL_LISTING data dictionary becomes active in this section. DDL_SCRIPTVariableDDL_LISTINGDisplay the DDL script of the currently active entity e. g. SCHEMATA, TABLES
Using the table
The table shows which variables are defined in which sections. The variable should be used in its correct section, otherwise its value will not be displayed
It should be remembered though that the data dictionaries used to perform the lookup form a hierarchical tree, so it is possible to use a variable defined in a parent section, in a child section
7. 11. 2. Creating a custom template
In the simplest case a template consists of two files. A template file, which has a Schema: Airlines Schema: Airports51 extension, and a special file Schema: Airlines Schema: Airports52. The Schema: Airlines Schema: Airports52 file has important metadata about the template. A third file is optional, that is the preview image file. This preview file provides a thumbnail image illustrating the appearance of the generated report
One of the easiest ways to create a custom template is to make a copy of any existing template
For example, you make a custom template based on the Schema: Airlines Schema: Airports54. The following procedure demonstrates this
First you need to make a copy of the template on which you are going to base your custom template. Untuk melakukan ini navigasikan ke folder tempat template disimpan. Dengan asumsi MySQL Workbench telah diinstal ke lokasi default di Windows, ini akan menjadi Schema: Airlines Schema: Airports55
Kemudian buat salinan folder template yang Anda inginkan sebagai dasar template baru Anda. Dalam hal ini salinan folder Schema: Airlines Schema: Airports_56 dibuat. Salinan dapat diberi nama yang sesuai, misalnya, Schema: Airlines Schema: Airports57
Sekarang file Schema: Airlines Schema: Airports_52 perlu diedit, untuk mencerminkan template khusus Anda. File yang belum diedit dalam hal ini ditampilkan di sini
Total number of Schemata: {{SCHEMA_COUNT}}0Dua objek didefinisikan dalam file. Objek Schema: Airlines Schema: Airports_59 dan objek Schema: Airlines Schema: Airports60. Objek ini berisi informasi tentang template yang akan ditampilkan di layar utama wizard Pelaporan Model DBDoc
Hal pertama yang perlu Anda ubah adalah GUID objek yang digunakan dalam file. Dalam contoh ini ada dua yang perlu diganti
Total number of Schemata: {{SCHEMA_COUNT}}_1Hasilkan dua GUID baru. Ini dapat dilakukan dengan menggunakan alat baris perintah yang sesuai. Ada juga alat online gratis yang dapat digunakan untuk menghasilkan GUID. File Schema: Airlines Schema: Airports_52 kemudian harus diedit sesuai kebutuhan
Edit informasi tekstual untuk objek Schema: Airlines Schema: Airports59 dan Schema: Airlines Schema: Airports60 untuk mencerminkan tujuan dari template kustom
File yang dimodifikasi sekarang akan terlihat seperti berikut
Total number of Schemata: {{SCHEMA_COUNT}}2The next step is to create the new template file. Again this may best be achieved, depending on your requirements, by editing an existing template. In this example the template file Schema: Airlines Schema: Airports64 is shown here
Total number of Schemata: {{SCHEMA_COUNT}}3This template shows details for all schemata in the model
The above template file can be edited in any way you like, with new markers being added, and existing markers being removed as required. For the custom template example you might want to create a much simpler template. Such as the one following
Total number of Schemata: {{SCHEMA_COUNT}}4This simplified report just lists the schemata and the tables in a model. Tanggal dan waktu pembuatan laporan juga akan ditampilkan sebagai hasil dari penggunaan variabel Schema: Airlines Schema: Airports65
What data model is MySQL?
Together, we'll focus on how to model data into relational tables because MySQL is a relational database .Is MySQL a relational data model?
MySQL is the most widely adopted open source relational database and serves as the primary relational data store for many popular websites, applications, and commercial products.What are the 4 types of database models?
Types of database models Hierarchical database model . Relational model. Network model. Object-oriented database model.