Archive

Posts Tagged ‘MySQL’

MySQL and MariaDB Storage Engine

August 8, 2014 Leave a comment

MySQL and MariaDB support several storage engines that act as handlers for different table types. They include both those that handle transaction-safe tables and those that handle nontransaction-safe tables.

You can see supported engine by running the SQL command “SHOW ENGINES” of your favorite MySQL Client tool:

Show Engines

We understand that you may want to use another storage engine. This is why we introduce a new options.

You can define the default storage engine at producer level:

Configuration Storage Engine

By using XML:

<cf:producer name="MySQL" 
             typeName="CodeFluent.Producers.MySQL.MySQLProducer, 
                       CodeFluent.Producers.MySQL">
    <cf:configuration defaultStorageEngine="MyISAM" />
</cf:producer>

Or at entity level:

<cf:entity name="Customer" 
    cfmy:storageEngine="MyISAM" 
    xmlns:cfmy="http://www.softfluent.com/codefluent/producers.mysql/2012/1">
    
    <cf:property name="Id" key="true" />
    <cf:property name="Name" />
</cf:entity>

The generated code will now use the storage engine defined at entity level, or at producer level or InnoDB if nothing is specified:

CREATE TABLE `StorageEngine`.`Customer`
    (
    `Customer_Id` BINARY (16) NOT NULL,
    `Customer_Name` VARCHAR (256) CHARACTER SET utf8 NULL,
    `_trackLastWriteTime` DATETIME NOT NULL,
    `_trackCreationTime` DATETIME NOT NULL,
    `_trackLastWriteUser` VARCHAR (64) NOT NULL,
    `_trackCreationUser` VARCHAR (64) NOT NULL,
    `_rowVersion` BINARY (16) NOT NULL,
    CONSTRAINT `PK_Cus_Cus_Cus` PRIMARY KEY
    (
        `Customer_Id`
    )
) ENGINE = MyISAM;

Happy storing,

R&D Team

Categories: MySQL Tags: , ,

Goodbye SQL-DMO!

September 10, 2012 1 comment

Starting with build 671, SQL-DMO is not longer a requirement for the CodeFluent Entities SQL Server Producer. That’s good news!

We have been relying on SQL-DMO for almost 7 years, and it has served us well. It has been marked as obsolete for something like 5 years or so, but the producer was still relying on it. One of the reasons we kept it for so long was to maintain  compatibility with SQL Server 2000. Now, we don’t support that database either (june 2012).

We have replaced SQL-DMO by a similar technology without any dependency on the newer SQL-SMO (replacement for SQL-DMO) either. This database abstraction technology – our own SMO-like layer – works not only for SQL Server but also for all our supported importers and producers supported databases and modeling systems, namely: SQL Server, Oracle, PostgreSQL, MySQL, SqlLite, Xmi, Enterprise Architect, Access and SqlServerCe. It’s mainly located in the CodeFluent.Runtime.Database.dll assembly that ships with the product and in satellite ones for specific targeted systems (CodeFluent.Runtime.PostgreSQL.dll for PostgreSQL, CodeFluent.Runtime.MySQL.dll for MySQL, etc.).

Cheers,

The R&D team.

Introducing CodeFluent Entities MySQL Producer

August 16, 2012 Leave a comment

CodeFluent Entities 2012 – Build 060703 introduced a new Persistence layer producer: the MySQL Producer! The producer supports MySQL 5.1 and above and relies on the MySQL Connector for .NET version 6.5.4.0. The producer will try to load dynamically the MySql.Data assembly. You might need to copy the MySql.Data.dll in your CodeFluent Entities installation directory if you don’t want to register the MySQL Connector in the GAC.

Getting started

The producer is available in the Persistence Layer Producers section of the Add New Producer window:

as well as directly from the Starter Wizard:

This is how you can set the connection string (in the Wizard pages):

A first model

I created a new project with the Start Wizard and designed this very simple model composed of two entities:

Basically all the employees have a first name, a last name, a birth date, and a job. Each job has salary – of type currency, a name and employees.

I added two instances to the model: an employee named John Doe, born on June 12th, 1974 with a job named CEO and which salary is 30,000.

I also set the Target Version property of the MySQL Producer to version 5.5, which is the version I am currently using on my computer, and built the project, like any other CodeFluent Entites project. In the output window I can see the result of the generation process. The MySQL producer has generated 9 files and succeeded:

All the automatically generated .SQL script files are available in the Persistence project of the solution:

If I connect to my MySQL database with an administration tool I can see that a new database has been created with the named I specified in the Connection String, and populated with the tables automatically inferred from my model as well as instances:

 

 

 

And I haven’t written a line of code yet!

Consuming the generated persistence layer

Consuming the MySQL generated database is as easy as configuring the right connection string in the client project. I my case, I created a Console Application Project named MyCompany.Management.Client.

To prepare it, I had to reference my BOM Project – in my case MyCompany.Management – that was automatically created by the Starter Wizard, MySql.Data.dll (the MySQL ADO.NET provider) and the CodeFluent Entities runtime, i.e. CodeFluent.Runtime.dll and CodeFluent.Runtime.MySQL.dll. Note that you might need to set the Copy Local property to True for the MySql.Data.dll reference if the dll is not registered in the GAC.

I also had to change the target framework of the project from .NET Framework 4.0 Client Profile to the full .NET Framework 4.0.

As I am using the MySQL Producer I have to set the correct Connection String in App.config file:

<?xml version="1.0"?>
<configuration>
  <configSections>
    <section name="MyCompany.Management" type="CodeFluent.Runtime.CodeFluentConfigurationSectionHandler, CodeFluent.Runtime" />
  </configSections>
  <MyCompany.Management persistenceTypeName="MySQL" connectionString="Server=localhost;Database=MyCompany_Management;User Id=root;" />
</configuration>

Note that it is important to specify the persistenceTypeName=“MySQL” attribute, otherwise we would be using the default persistence layer which is SQL Server. Here is the code of my console application :

using System;
namespace MyCompany.Management.Client
{
  class Program
  {
    static void Main(string[] args)
    {
      // Create a new employee
      Employee newbie = new Employee();
      newbie.FirstName = "Jane";
      newbie.LastName = "Doe";
      newbie.BirthDate = new DateTime(1990, 01, 15);

      // Persist Jane Doe
      newbie.Save();

      // Load all the employees
      EmployeeCollection employees = EmployeeCollection.LoadAll();
      foreach (Employee employee in employees)
      {
        Console.Write("- {0} {1}, born on {2:MM-dd-yyyy}",
          employee.FirstName,
          employee.LastName,
          employee.BirthDate);

        if (employee.Job != null)
        {
          Console.Write(", is {0} with a salary of {1:C}",
            employee.Job.Name,
            employee.Job.Salary);
        }

        Console.WriteLine();
      }
    }
  }
}

It creates a new employee, saves it, and then loads a list of all the employees. As we can see, there is no specific code about MySQL. Its use is completely transparent at this level.

And here is the result:

And the populated table:

 

Happy MySQL-ing!

Baudouin Giard

CodeFluent Entities: Import MySQL and PostgreSQL databases


Starting with CodeFluent Entities 60529.661 available here, you can import MySQL and PostgreSQL databases into CodeFluent Entities models Smile

Several ways to import your project:

  • Option #1: From the Solution Explorer, right-click on your project and click “Import An Existing Database or Model…”,
  • Option #2: From the Server Explorer, drag and drop tables from your database onto your surface,
  • Option #3: This is brand new with the latest build (661 and upper), from the Visual Studio toolbar, open the “CodeFluent Entities” menu and click on “Import An Existing Database or Model…

Whichever option you choose, the same wizard will open and it now contains two new importers: MySQL and PostgreSQL.

ImportWizard

 

Prerequisites

  • CodeFluent Entities (build 60529.661 or upper),
  • MySQL or PostgreSQL .NET Data Provider depending on which database you want to import.

In both cases you’ll need the appropriate .NET provider to be accessible by CodeFluent Entities so it can connect and import the database to your model. To do so place the MySQL.Data.dll for MySQL or Npgsql.dll for PostgreSQL in CodeFluent Entities’ installation directory (%ProgramFiles(x86)%\SoftFluent\CodeFluent\Modeler).

 

Cheers,

Carl Anderson

Follow

Get every new post delivered to your Inbox.

Join 49 other followers