In this article, you will learn about the MySql Tool which is a console application to export tables and generate simple entities to use with ORMs.
Introduction
MySql Tool is a console application that allows you to export tables and generate simple entities to use with ORMs.
Background
As a developer, I work with various ORMs that allow me to map database tables to class entities. If you've worked with the .NET Entity Framework, you'll know what I'm talking about. For a recent PHP project, I decided to develop a simple console application that would generate PHP class entities. After developing the application, I started adding more functionality to it, primarily because I got hooked on using the Console ForegroundColors.
Using MySql Tool
After loading the MySql Tool application, you'll be greeted with an unimpressive splash screen. The information displayed on the splash screen will tell you if you have a MySql server running on localhost, port 3306 and the version number.
The condition to determine if a MySql server is running, is to attempt to connect to port 3306 on localhost using the TcpClient class. The version number is extracted from the response MySql sends back.
The first command you'll need to use is the connect command. This command takes a single argument which is the connection string. I should at this point warn you that the connection string is stored in the system registry. The connection string will be used to automatically connect to the MySql server on subsequent reloads of the MySql Tool. There is a clean command which will remove the registry entry if you no longer want to use the application.
connect Server=127.0.0.1;database=db_name;uid=root;pwd=password
If the connection attempt was successful, the console prompt will change to indicate the current selected database. The prompt will look something like the following:
[database_name]>
In the case where you didn't specify a database in the connection string, you will be prompted repeatedly to enter a database name. The prompt looks like the following:
> Select database:
In the case above, you will be prompted repeatedly until you either enter a valid database name or you type "." (a period) to exit the prompt.
Once you're connected to the MySql server, you can try out a few commands. All available commands can be found by entering help.
The screen print above shows a list of some of the commands that you can use. While most of them won't provide any real benefit to using the application, the export and generate commands provide some benefit.
Let's take a look at some of these commands before we move onto the export and generate commands. Below is a screen print of the show command that can be used to display the create table SQL statement.
By altering the arguments for the show command, you can display different information. For example, the following command/argument shows how to list all database tables and processes.
show tables
show processlist
If you need to change the current active database, you can use the db command as shown below:
db database_name
It's now time to look at the main commands this application was designed for. Let's take a look at the export command first. The syntax for this command is shown below:
export * C:\Users\Elvis\Desktop\dump.sql
The export command takes two arguments. The first argument is the tables you want to export. In the example above, the '*' implies that all tables should be included in the export. If you want to dump a single or a group of tables, you can use the following syntax.
export table1,table2,table3 C:\Users\Elvis\Desktop\dump.sql
Table names must be separated by a comma and contain no spaces. The second argument is the SQL dump file. In some cases, you might be using the export command frequently and don't want to type the filename every time you load the application. In such situations, you can create a special variable using the set command, that will save a variable with a value to the system registry. This will then give you the option to use it in your export file path.
set path C:\Users\Elvis\Desktop
export * -path.dump.sql
In the example above, the set command is used to create a variable "path" with the value "C:\Users\Elvis\Desktop". To use the path variable, you need to first prepend the variable name with "$" and append a "." at the end. This tells the export command to replace the variable with the path value.
We now take a look at the final command generate, which can be used to generate plain class files (currently PHP and C# is supported).
generate entity User user php
The generate command takes three arguments. The first argument is the action, in this case we want to generate an entity. The second argument is the entity name. This name will be used as the class and file name. The third argument is the database table name that you want to generate an entity from. The final argument is the programming language name that the entity will be generated for. Notice that the file location of the entity is not specified. By default, the location of the entity file will be saved in the same directory as the MySql Tool application, however if you have created a registry variable with the name "path" as in the previous example, then entities will be saved in that location. The sample code below shows the entity class generated from executing the generate command above.
class User{
protected $user_id;
protected $email;
protected $password;
protected $password_salt;
protected $first_name;
protected $last_name;
protected $role_id;
protected $last_login_date;
protected $created_date;
protected $created_by_user_id;
protected $active_ind;
protected $deleted_ind;
public function setUserId($userId){
$this->user_id = $userId;
}
public function getUserId(){
return $this->user_id;
}
public function setEmail($email){
$this->email = $email;
}
public function getEmail(){
return $this->email;
}
public function setPassword($password){
$this->password = $password;
}
public function getPassword(){
return $this->password;
}
public function setPasswordSalt($passwordSalt){
$this->password_salt = $passwordSalt;
}
public function getPasswordSalt(){
return $this->password_salt;
}
public function setFirstName($firstName){
$this->first_name = $firstName;
}
public function getFirstName(){
return $this->first_name;
}
public function setLastName($lastName){
$this->last_name = $lastName;
}
public function getLastName(){
return $this->last_name;
}
public function setRoleId($roleId){
$this->role_id = $roleId;
}
public function getRoleId(){
return $this->role_id;
}
public function setLastLoginDate($lastLoginDate){
$this->last_login_date = $lastLoginDate;
}
public function getLastLoginDate(){
return $this->last_login_date;
}
public function setCreatedDate($createdDate){
$this->created_date = $createdDate;
}
public function getCreatedDate(){
return $this->created_date;
}
public function setCreatedByUserId($createdByUserId){
$this->created_by_user_id = $createdByUserId;
}
public function getCreatedByUserId(){
return $this->created_by_user_id;
}
public function setActiveInd($activeInd){
$this->active_ind = $activeInd;
}
public function getActiveInd(){
return $this->active_ind;
}
public function setDeletedInd($deletedInd){
$this->deleted_ind = $deletedInd;
}
public function getDeletedInd(){
return $this->deleted_ind;
}
}
The same entity can be generated for C# by changing the last argument for the generate command to 'cs' as shown below:
generate entity User user cs
using System;
class User
{
public int user_id
{
set;
get;
}
public string email
{
set;
get;
}
public string password
{
set;
get;
}
public string password_salt
{
set;
get;
}
public string first_name
{
set;
get;
}
public string last_name
{
set;
get;
}
public int role_id
{
set;
get;
}
public DateTime last_login_date
{
set;
get;
}
public DateTime created_date
{
set;
get;
}
public int created_by_user_id
{
set;
get;
}
public int active_ind
{
set;
get;
}
public int deleted_ind
{
set;
get;
}
}
Notice in the sample C# entity class that the properties have the correct data types.
Due to the development and crazy coding style, the application may contain some errors and bugs. If you find any errors, please be sure to let me know.
History
- 6th November, 2014: Initial version
