Archive

Author Archive

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: , ,

Persistence Tracking Columns are UTC

August 7, 2014 Leave a comment

CodeFluent Entities automatically generates tracking columns. Those columns contains:

  • Creation time
  • Creation user
  • Last write time
  • Last writer user

Tracking columns

By default creation time and last write time use GETDATE function (local date of the server). We think it’s a better practice to use UTC date for this kind of data. Two years ago, we wrote an aspect to replace GETDATE function by GETUTCDATE function: http://www.softfluent.com/forums/codefluent-entities/utc-date-times.

In the latest build of CodeFluent Entities (build 772) we introduce a new built-in setting at Project Level to use UTC date instead of local date:

 

Persistence Track Columns Are UTC

This option is used by all persistence producers: Microsoft SQL Server, Microsoft SQL Azure, Oracle, MySQL and PostgreSQL.

For example with SQL Server:

CREATE TABLE [dbo].[Test] (
  [Test_Id] [uniqueidentifier] NOT NULL,
  [_trackLastWriteTime] [datetime] NOT NULL CONSTRAINT [DF_Tes__tc] 
DEFAULT (GETUTCDATE()), -- instead of getdate()
  [_trackCreationTime] [datetime] NOT NULL CONSTRAINT [DF_Tes__tk] 
DEFAULT (GETUTCDATE()), -- instead of getdate()
  [_trackLastWriteUser] [nvarchar] (64) NOT NULL,
  [_trackCreationUser] [nvarchar] (64) NOT NULL,
  [_rowVersion] [rowversion] NOT NULL
)

Happy tracking,

The R&D Team

“IS NOT NULL” and “IS NULL” in CFQL

July 31, 2014 Leave a comment

In CodeFluent Query Language (CFQL), the SQL statement “expression IS NOT NULL” is “expression Exists” and the SQL statement “expression IS NULL” is “NOT expression EXISTS”.

There is no need to write raw SQL to test if a value exists in a method as we sometimes see in models. This can be done in full CFQL thanks to the Exists operator, this way keeping your model as platform independent as possible:

LOAD() WHERE (NOT FirstName EXISTS) AND (LastName EXISTS)

More about CFQL:

Happy CFQL-ing,

The R&D team

Using SQL Server datetime2 data type

July 28, 2014 Leave a comment

CodeFluent Entities supports DateTime2 since the build 714. A datetime2 defines a date that is combined with a time of day that is based on 24-hour clock. Datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

To use DateTime2 instead of DateTime you have to configure the SQL producer:

SQL Server Use datetime2

The created table uses datetime2:

CREATE TABLE [dbo].[Customer] (
 [Customer_Id] [uniqueidentifier] NOT NULL,
 [Customer_DateOfBirth] [datetime2] NULL,
)

Note: To use datetime2 in your application you have to set useDateTime2=”true” in the configuration file:

<configuration>
  <configSections>
    <section name="MyDefaultNamespace" type="CodeFluent.Runtime.CodeFluentConfigurationSectionHandler, CodeFluent.Runtime" />
  </configSections>
  <MyDefaultNamespace connectionString="..." useDateTime2=”true” />
</configuration>

DateTime2 allows to specify the precision from 0 to 7 digits. The default precision is 7. This value is configurable at property level by setting the SQL Server specify attribute “sql size”:

SQL Server Sql Size
Now the generated script looks like:

CREATE TABLE [dbo].[Customer] (
 [Customer_Id] [uniqueidentifier] NOT NULL,
 [Customer_DateOfBirth] [datetime2] (6) NULL,
)

Note that Microsoft recommends using datetime2 data type for new work:

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffsetprovides time zone support for globally deployed applications.

Happy coding,

The R&D team

SQL Server specific data types

July 24, 2014 Leave a comment

CodeFluent Entities can use SQL Server specific data types such as Geography, Geometry and HierarchyId.

The first step is to register “Microsoft.SqlServer.Types.dll” into the model:

 

Add Reference

 

SQL Server Reference

Note: You must add the same reference in the BOM project.

Then set the type name of the property to Microsoft.SqlServer.Types.SqlGeography:

Type Name Geography

Choose Type SqlGeography

We also have to set the database type. As this is specific to SQL Server, we have to use the SQL Server producer attribute “sqlType”:

Sql Data Type

The same apply for Geometry and HierarchyId data types.

The table is generated, let’s add a simple method that compute intersection of two geography object. The code is really specific to SQL Server so we have to create a RAW method:

CFQL SqlGeography

Don’t forget to set the return type name of the method to SqlGeography:

Return Type Name SqlGeography

Let’s use the generated code:

Sample sample1 = new Sample();
sample1.Geography = SqlGeography.Parse("LINESTRING(-122.360 47.656, -122.343 47.656)");
sample1.Save();

Sample sample2 = new Sample();
sample2.Geography = SqlGeography.Parse("LINESTRING(-122.360 47.656, -122.343 47.656)");
sample2.Save();

var intersection = Sample.GetIntersection(sample1.Id, sample2.Id);
Console.WriteLine(intersection.ToString()); //LINESTRING (-122.34300000005148 47.656000000089243, -122.3599999999485 47.655999999910769)

If intersection is null, this means that you need to add an assembly binding in the app.config/web.config file:

<configuration>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.Types" culture="neutral" publicKeyToken="89845dcd8080cc91"/>        
        <bindingRedirect oldVersion="10.0.0.0" newVersion="12.0.0.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

This example shows how to use SqlGeography with CodeFluent Entities. The same works with SqlGeometry, HierarchyId, and any types that implement IBinarySerialize.

Happy storing,

The R&D Team

Persistent List

July 16, 2014 Leave a comment

CodeFluent Entities has a very powerful type system. But do you know you can persist a list of string, a list of integer or another kind of list (double, boolean, etc.)?

CodeFluent Entities maps all known types to their equivalent when switching from one layer to another. On the other hand, for any other “unknown” type, CodeFluent Entities relies on several kinds of serialization: binary serialization, XML serialization, and Lightweight serialization.

So if you use a List<string> the data will be persisted by serializing it as XML or Binary depending on your configuration. If you prefer to store the list as comma separated values you can use the PersitentList from the CodeFluent Runtime.

Persistent List Choose Type Name

 

The code is very easy to use:

Customer customer = new Customer();
customer.Name = "John Doe";
customer.Contacts = 
           new CodeFluent.Runtime.Utilities.PersistentList<string>('|');
customer.Contacts.Add("Jane");
customer.Contacts.Add("Bob");
customer.Contacts.Add("Ashley");
customer.Save();

In the database the row is stored as text:

Persistent List Database

But where is the magic?

This PersistentList implements the ICodeFluentSerializable interface:

/// <summary>
/// Allows an object to control its own serialization and deserialization in CodeFluent persistence layer context.
/// </summary>
public interface ICodeFluentSerializable
{
    /// <summary>
    /// Serializes this instance.
    /// </summary>
    /// <param name="mode">The serialization mode.</param>
    /// <returns>The serialized instance. May be null.</returns>
    object Serialize(PersistenceSerializationMode mode);

    /// <summary>
    /// Deserializes the specified object instance.
    /// </summary>
    /// <param name="type">The serialized object instance type. May not be null.</param>
    /// <param name="mode">The serialization mode.</param>
    /// <param name="serializedInstance">The serialized object instance. May be null.</param>
    /// <returns>The deserialized instance. May be null.</returns>
    object Deserialize(Type type, PersistenceSerializationMode mode, object serializedInstance);
}

So there is no magic. You can implement this interface for your custom object and persist them in the database in a custom manner. I remind you that if your custom class does not implement this interface, the XML serializer or Binary serializer will be used by default.

Happy storing,

The R&D team.

SQL Server In-Memory OLTP

July 10, 2014 Leave a comment

In-Memory OLTP comes with Microsoft SQL Server 2014 and can significantly improve OLTP database application performance. It is a memory-optimized database engine integrated into the standard SQL Server engine. This system provides memory-optimized tables which are fully transactional and are accessed using class Translact-SQL instructions.

In-Memory Tables comes with some limitations. We won’t enumerate all but only those which are related to CodeFluent Entities:

  1. Foreign keys aren’t supported
  2. RowVersion and Timestamp columns aren’t supported: http://msdn.microsoft.com/en-us/library/dn133179.aspx
  3. Default constraints aren’t supported
  4. Some Transact-SQL constructs aren’t supported: http://msdn.microsoft.com/en-us/library/dn246937.aspx

Let’s handle those four points!

Foreign Keys

There are two options:

  • Don’t create relation :(
  • Create relation without foreign key :)

The second solution requires the usage of an Aspect. Fortunately we already wrote it a few time ago: http://www.softfluent.com/forums/codefluent-entities/how-to-disable-creating-foreign-key-by-sql-producer-

Even if foreign keys do not exist anymore, CodeFluent Entities still generates LoadBy_Relation methods so you won’t see any difference in your code. :)

Foreign Keys

RowVersion

RowVersion is not supported by In Memory tables so let’s remove it. We have to set “Concurrency Mode” to “None”:

RowVersion

 

Default Constraints

Default constraints used by tracking columns (creation time & last write time) are not supported. Here we have two options:

  • Remove default constraints :(
  • Move them into the Save stored procedure :)

The first option is available in the Property Grid at project or entity level by removing the tracking time columns:

Properties

The second option can be done with an Aspect as you can see in the full example (see below). The edited INSERT statement looks like:

    INSERT INTO [Customer] (
        [Customer].[Customer_Id],
        [Customer].[Customer_Name],
        [Customer].[Customer_ContactSource_Id],
        [Customer].[_trackCreationUser],
        [Customer].[_trackLastWriteUser],
        [Customer].[_trackLastWriteTime])
    VALUES (
        @Customer_Id,
        @Customer_Name,
        @Customer_ContactSource_Id,
        @_trackLastWriteUser,
        @_trackLastWriteUser,
        (GETDATE())) -- Default Value

Tansact SQL

By default the SQL Server Producer surround the procedure code with a transaction. This transaction isn’t supported when using In Memory Table. The following exception is thrown when calling the stored procedure:

Unhandled Exception: System.Data.SqlClient.SqlException: Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT).

To remove it, we have to configure the SQL Server to not produce it:

SQL Server

 

Migrate the table

After those small changes, we can migrate the table to an In Memory table:

Migration

Migration Result

 

We can now use the In Memory table from the application:

Customer customer = new Customer();
customer.Name = "John Doe";
customer.Save();

All-in-One method

All the previous steps are automated by an aspect. All you have to do is include the aspect and set “enabled” on tables:

SqlServer In Memory Aspect

The full code sample including the aspect is available on our GitHub repository.

The R&D Team

Follow

Get every new post delivered to your Inbox.

Join 51 other followers