LibreOffice Base Group 28

MySQL Workbench

We take a first look at MySQL Workbench a tool that can help us manage our LibreOffice Base MySQL databases.

We see we can view all of our LibreOffice MySQL databases as well as see information about the database or information about a table on the database.

We see how to update a MySQL table using MySQL Workbench – and that it creates SQL to apply the change.

We also use MySQL Workbench to update information on a MySQL table and that MySQL Workbench creates SQL to apply our update.

We also see MySQL Workbench has a button to reformat SQL, similar to the way I would pause the video and reformat the SQL in the LibreOffice Base SQL videos.

We learn how to reverse engineer a MySQL Database using MySQL Workbench.

We reverse engineer a MySQL database, which means create a model and an EER (Enhanced Entity Relationship) Diagram from an active MySQL database (schema).

We look at how to add a field to the model as well as add a table, then we synchronize that model with the table on the database.

We also see how to save our model to our desktop.

We create a new MySQL database called mysql_searchform and all the tables for it.

We create a mysql workbench model called mysql_searchform.

We create tables for our new model similar to the tables we used in LibreOffice Base tutorial (57) Employe search Form pt2.

We then forward engineer a database from our model to create the new MySQL database mysql_searchform

We create .CSV backup files from the odb57 LibreOffce Base tables to upload into the new MySQL tables.

We need to edit the data a little bit to have it load in properly to the MySQLfiles. We need to delete the Header Records and change the Date Format to be YYYY-MM-DD.

We update MySQL tables with the .csv files we created in the last video.

We see the SQL used to upload a .csv file into a MySQL table using MySQL Workbench.

When we upload the employee table, everywhere MySQL Workbench finds a NULL for the End Date, a date of 0000-00-00 is forced in. This date will not work with LibreOffice Base and we need to create some SQL to reset the zero date to null.

We also learn we need to turn off the MySQL Safe Update feature by using SET SQL_SAFE_UPDATES=0; before running a MySQL UPDATE.

We create some relationships for the MySQL tables using MySQL Workbench.

We see how to create a basic 1 to many relationship and set the foreign key parameters when the primary key is deleted (Cascade, Set to Null, No Action).

Then we look at how to change the colors of the tables and add layers to group like tables (for visual model use only – no effect on MySQL performance)

We finish by applying the relationship changes to the database.

We add pictures into the MySQL employee table.

We look at the MySQL employee table and see what type of blob (Binary Large Object) we should use to store our pictures (Tiny Blob, Blob, Medium Blob, or LongBlob)

Then we add a picture into the MySQL database using MySQL Workbench.

We end by creating a LibreOffice Base database form that will display the picture from the MySQL database and we will add another picture to the MySQL table using that same LibreOffice Base form.

We start to create the LibreOffice Base form to search the for employees on the MySQL database.

We start creating the LibreOffice base document by connecting it to a MySQL database using a JDBC connection.

We connect to the filter table to the form and the employee table to the subform.

We add a header, arrange and format fields on the form, and add a refresh button.

We complete the LibreOffice Base form, that searches for employees on a MySQL database, that we started in the previous video.

First we add a drop-downs to the Department and Group text boxes in the search criteria. Then we change the Department-ID and Group-ID to Department and Group in the subform and reformat the Start Date in the subform.

We add the variable link fields for the SQL join and add the SQL from odb57 into the SQL command area.

We test the form and find that most of the SQL is good, but the MySQL SQL is different than the HSQLDB SQL for upper case and concatenate. We need to use UCASE instead of UPPER to get our data to the upper case and CONCAT() in place of || to concatenate the fields together.