Data Providers (MySql and other) -Status and problems

Topics: General, Standard packages
Nov 2, 2011 at 2:20 PM

Hi

Our team has managed without changing a lot C1 code to:

  • to integrate DbLinq with Composite C1 (currently only MySqlConnector) in order to get some
    other DB providers...
  • in Composite.config able to load:
    			<add connectionString="Data Source=.\SQLEXPRESS;Database=DemoDBC1;Integrated Security=True" sqlQueryLoggingEnabled="false" sqlQueryLoggingIncludeStack="false" type="Composite.Plugins.Data.DataProviders.MSSqlServerDataProvider.SqlDataProvider, Composite" name="DynamicSqlDataProvider" />
    			<add connectionString="Server=127.0.0.1;Database=DemoDBC1;Uid=root;Pwd=mysqlpwd;" type="Composite.Plugins.Data.DataProviders.MySqlDataProvider.SqlDataProvider, Composite" name="DynamicMySqlDataProvider" />
    
  • in Composite\InstalledPackages\content\views\Composite.Tools.SqlServerDataProvider
    changed UI Wizard Step 1 to target MySql too with connection string:
    			<asp:DropDownList 
    				ID="dropDownListDataProvider" runat="server" AutoPostBack="True"
    				OnSelectedIndexChanged="dropDownListDataProvider_SelectedIndexChanged"
    			>
    				<asp:ListItem>MS SQL Server</asp:ListItem>
    				<asp:ListItem>MySQL</asp:ListItem>
    			</asp:DropDownList>
    
    
     
  • Currently able to connect to both DBs

... but (always but):

  1. wizard seems to store succesfull Connection String into Composite.config
    1. Composite.config before "Next" in Wizard step 1:
      	<Composite.Data.Plugins.DataProviderConfiguration defaultDynamicTypeDataProviderName="DynamicXmlDataProvider">
      		<DataProviderPlugins>
      			<!--
      			-->
      			<add connectionString="Data Source=.\SQLEXPRESS;Database=DemoDBC1;Integrated Security=True" sqlQueryLoggingEnabled="false" sqlQueryLoggingIncludeStack="false" type="Composite.Plugins.Data.DataProviders.MSSqlServerDataProvider.SqlDataProvider, Composite" name="DynamicSqlDataProvider" />
      			<add connectionString="Server=127.0.0.1;Database=DemoDBC1;Uid=root;Pwd=mysqlpwd;" type="Composite.Plugins.Data.DataProviders.MySqlDataProvider.SqlDataProvider, Composite" name="DynamicMySqlDataProvider" />
      
      
       
  2. Composite.config after in Wizard step 1:
    	<Composite.Data.Plugins.DataProviderConfiguration defaultDynamicTypeDataProviderName="DynamicXmlDataProvider">
    		<DataProviderPlugins>
    			<!--
    			-->
    			<add connectionString="Server=127.0.0.1;Database=DemoDBC1;Uid=root;Pwd=mysqlpwd;" sqlQueryLoggingEnabled="false" sqlQueryLoggingIncludeStack="false" type="Composite.Plugins.Data.DataProviders.MSSqlServerDataProvider.SqlDataProvider, Composite" name="DynamicSqlDataProvider" />
    			<add connectionString="Server=127.0.0.1;Database=DemoDBC1;Uid=root;Pwd=mysqlpwd;" type="Composite.Plugins.Data.DataProviders.MySqlDataProvider.SqlDataProvider, Composite" name="DynamicMySqlDataProvider" />
    
    
     
  3. this is the code that returns Connection String:
    				DataFacade.GetDynamicDataProviderNames()
    				.Where(d => d == DynamicSqlDataProviderName)
    				.Select(d => Reflection.GetDataProviderConfiguration(d).GetProperty("ConnectionString"))
    
    
  4. so it always write to DynamicSqlDataProvider which causes
    public static SqlConnection GetConnection(string connectionString)
    in
    Composite.Core.Sql.SqlConnectionManager to throw exception.

 

Before we proceede are there any recommendations how to go further...

It seems like we'll have to make SqlConnectionManager.GetConnection either polymorfic or generic to
obtain other ConnectionTypes....

 

regards

 

mel 


 

 

Nov 2, 2011 at 2:34 PM
Edited Nov 2, 2011 at 2:35 PM

As long as your provider shouldn't be able to work with dynamic datatypes or static types provided by anyone its actually very easy to create providers.

The problem arises when you don't have a concrete class to work with but only a interface. Then you need to go out at compile classes yourself and create/modify database tables accordingly since neither LinqForSql, DbLinq, MongoDB or whatever can work with interfaces. You also need to let your provider tell which types it can work with, have you done this? I see you still have the default MSSqlServerDataProvider provider registered. Unless you remove the types from it that you instead want to handle with your new MySqlProvider, it will still be the provider to respond to all your data queries. This is done in ~/App_Data/Composite/Configuration/DynamicSqlDataProvider.config.

To my best knowledge it will never work to build a new generic provider based on DbLinq by just inhering from MSSqlServerDataProvider since database manipulation can be different from database to database. Instead you should build your own from the ground up, using MSSqlServerDataProvider as inspiration. Im in the process of doing that myself for being able to use MongoDB.

Nov 2, 2011 at 9:00 PM
burningice wrote:

As long as your provider shouldn't be able to work with dynamic datatypes or static types provided by anyone its actually very easy to create providers.

The problem arises when you don't have a concrete class to work with but only a interface. Then you need to go out at compile classes yourself and create/modify
database tables accordingly since neither LinqForSql, DbLinq, MongoDB or whatever can work with interfaces. You also need to let your provider tell which
types it can work with, have you done this?

No. In \App_Data\Composite\Configuration there are:

  • DynamicMySqlDataProvider.config  (auto generated? cos we did not generate it...)
  • DynamicSqlDataProvider.config
  • DynamicXmlDataProvider.config

DynamicMySqlDataProvider.config:
 

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <configSections>
        <section name="Composite.Data.Plugins.SqlDataProviderConfiguration" type="Composite.Plugins.Data.DataProviders.MySqlDataProvider.SqlDataProviderConfigurationSection, Composite, Version=2.1.4323.24893, Culture=neutral, PublicKeyToken=null" />
    </configSections>
</configuration>

DynamicSqlDataProvider.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <configSections>
        <section name="Composite.Data.Plugins.SqlDataProviderConfiguration" type="Composite.Plugins.Data.DataProviders.MSSqlServerDataProvider.SqlDataProviderConfigurationSection, Composite, Version=2.1.4323.17322, Culture=neutral, PublicKeyToken=null" />
    </configSections>
    <Composite.Data.Plugins.SqlDataProviderConfiguration>
        <Interfaces>
            <add interfaceType="Composite.Data.Types.ICompositionContainer,Composite"
                isGeneratedType="false">
                <Stores>
                    <add tableName="Composite_Data_Types_ICompositionContainer_Published"
                        dataScope="public" cultureName="" />
                </Stores>
                <DataIdProperties>
                    <add name="Id" type="System.Guid, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
                </DataIdProperties>
            </add>
        </Interfaces>
    </Composite.Data.Plugins.SqlDataProviderConfiguration>
</configuration>

I see you still have the default MSSqlServerDataProvider provider registered. Unless you remove the types from it that you instead want to handle with your new MySqlProvider

So this means they are mutually exclusive? 

, it will still be the provider to respond to all your data queries. This is done in ~/App_Data/Composite/Configuration/DynamicSqlDataProvider.config.

This means element <Composite.Data.Plugins.SqlDataProviderConfiguration> should be moved to DynamicMySqlDataProvider.config?

To my best knowledge it will never work to build a new generic provider based on DbLinq by just inhering from MSSqlServerDataProvider

we are trying to reuse MSSqlServerDataProvider in completely new namespace. No inheritance, copy/paste...

since database manipulation can be different from database to database. Instead you should build your own from the ground up,
using MSSqlServerDataProvider as inspiration. Im in the process of doing that myself for being able to use MongoDB.

It seems like this is MS Enterprise lib stuff?? Sorry, but haven't worked with it yet.

 

OK. Why single data provider? This could be used as Publisher-Subscribers pattern, where subscribers could be
local or remote DB(s) or xml. OK performance degrades for writing (can be avoided), but could do parallel,
concurrent reads from different DBs (let say 2 SQLServer instance or one SQLServer and another MySQL, MongoDB).

tomorrow we'll look into DB structure and dive deeper into DataProviders...

thanks and regards 

 

mel

 

Nov 3, 2011 at 10:11 AM
Edited Nov 3, 2011 at 10:17 AM

There is a 1:1 relation between a known interface and a dataprovider, and to my experience its the provider that was the last one to register its responsibility for an interface, that would be used when querying the datalayer for that interface. In your above example your new MySql dataprovider is not responsible for any interface and it will therefor never be used.

Its not like you can only have one dataprovider in total, you can have as many as you want. But when C1 gets a query for lets say IPage, its only forwarding that request to ONE dataprovider, the one that said "hey, i know about IPage". What you do behind the scene, is all up to you. Sure you could implement failovers or concurrent reads from several databases, but that is an implementation detail that C1 doesn't know anything about. All it cares about is getting a IQueryable<IPage> object back from your dataprovider :)

The Dynamic<Something>DataProvider.config is a config file the built-in XML and SQL dataproviders use to store internally which interfaces they are responsible for and related configuration data. This means that for SQL dataprovider we store ie. table-name for each interface, while the XML dataprovider stores xml-file names for each interface its responsible for. These files are necessary since the providers by nature are dynamic, and we as programmers can't know on beforehand which interfaces it will be responsible for. This also means that the provider suddenly becomes responsible for compiling concrete classes for all the interfaces as well which can be a daunting task if your not familiar with dynamic CodeDom generation.

If you already copied all the existing code into a new provider, why don't you just change the returned Connection-class to MySqlConnection?

Database manipulation (tables, indexes, foreign keys etc etc.) in the SQL provider is all handled by raw sql statements. It used to be handled with SQL Server Management Objects (SMO) but it was changed to remove dependencies on these assemblies.

Nov 5, 2011 at 3:29 PM
burningice wrote:

There is a 1:1 relation between a known interface and a dataprovider, and to my experience its the provider that was the last one to register its
responsibility for an interface, that would be used when querying the datalayer for that interface. In your above example your new
MySql dataprovider is not responsible for any interface and it will therefor never be used.

So this is what I need:

    <Composite.Data.Plugins.SqlDataProviderConfiguration>
        <Interfaces>
            <add interfaceType="Composite.Data.Types.ICompositionContainer,Composite"
                isGeneratedType="false">
                <Stores>
                    <add tableName="Composite_Data_Types_ICompositionContainer_Published"
                        dataScope="public" cultureName="" />
                </Stores>
                <DataIdProperties>
                    <add name="Id" type="System.Guid, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
                </DataIdProperties>
            </add>
        </Interfaces>
    </Composite.Data.Plugins.SqlDataProviderConfiguration>

Its not like you can only have one dataprovider in total, you can have as many as you want. But when C1 gets a query for lets say IPage, its only forwarding that request to
ONE dataprovider, the one that said "hey, i know about IPage". What you do behind the scene, is all up to you. Sure you could implement failovers or concurrent reads
from several databases, but that is an implementation detail that C1 doesn't know anything about. All it cares about is getting a IQueryable<IPage> object back
from your dataprovider :)

What if more Providers answer with "I know about IPage"??

The Dynamic<Something>DataProvider.config is a config file the built-in XML and SQL dataproviders use to store internally which interfaces they are responsible
for and related configuration data. This means that for SQL dataprovider we store ie. table-name for each interface, while the XML dataprovider stores
xml-file names for each interface its responsible for. These files are necessary since the providers by nature are dynamic, and we as programmers can't know
on beforehand which interfaces it will be responsible for. This also means that the provider suddenly becomes responsible for compiling concrete classes
for all the interfaces as well which can be a daunting task if your not familiar with dynamic CodeDom generation.

If you already copied all the existing code into a new provider, why don't you just change the returned Connection-class to MySqlConnection?

The problems were

  • SqlConnectionManager whit GetConnection which returns SqlConnection simple overloading based on return object type
    dows not work beacuse c# is not like c++ so that method signature includes return type too. No covariance and/or
    contravariance.
    We made it generic so in call of SqlConnectionManager<>.GetConnection we define type. This is temporarily and
    design should be discussed and agreed by You - Composite team.
    There are other possibilities, but right now we didi it this way..
  • another smaller problems were in SqlFunction and 

Database manipulation (tables, indexes, foreign keys etc etc.) in the SQL provider is all handled by raw sql statements.

This is inSqlDataProviderStoreManipulator? Some of the statemens will probably be needed to be rewritten for MySQL.

Coludn't Linq do this stuff? http://msdn.microsoft.com/en-us/library/bb399420.aspx

It used to be handled with SQL Server Management Objects (SMO) but it was changed to remove dependencies on these assemblies.

 

Another problem: after 2 subsequent Conversions to Sql on the same SQL Server right now I get following exception.
Database was previously deleted and recreated several times. Can not figure whati si going on:

Violation of PRIMARY KEY constraint 'PK_Composite_Data_Types_ICompositionContainer_Published'. 
Cannot insert duplicate key in object 'dbo.Composite_Data_Types_ICompositionContainer_Published'. 
The duplicate key value is (eb210a75-be25-401f-b0d4-b3787bce36fa).The statement has been terminated. 

It looks like some foreign keys are written somwhere in sysobjects....

 

regards

 

mel

 

 

Nov 5, 2011 at 4:06 PM
Edited Nov 5, 2011 at 4:11 PM

Like i mentioned, if there is several Providers telling C1 that its responsible for IPage, its the last provider that got registered that will end up being the one C1 uses. It has something to do with the order
they are entered in composite.config. If your new MySql dataprovider is a complete copy of the existing Sql dataprovider, then its the Dynamic...config file that controls which interface your
provider is responsible for, so its just a matter of making sure IPage is only present in the .config for your MySql provider and not for the Sql or Xml provider.

Regarding table/database manipulation, linq is for querying... selects, joins and projection. And how you manage the actual store is an implementation details that C1 doesn't know or care about.
All it has knowledge about is a MetaDataDefinition about the interface that you, as a dataprovider developer, can do something intelligent about. Like creating tables and columns and foreign keys and...

The SqlDataProvider is as it is... i see no need to change it since you're just copying all the code. You are free to do all the modifications you need and want without 
C1 having to change anything about its existing SqlDataProvider.

Nov 6, 2011 at 12:07 PM

Hi again

 

The problem with:

Violation of PRIMARY KEY constraint 'PK_Composite_Data_Types_ICompositionContainer_Published'. 
Cannot insert duplicate key in object 'dbo.Composite_Data_Types_ICompositionContainer_Published'. 
The duplicate key value is (eb210a75-be25-401f-b0d4-b3787bce36fa).The statement has been terminated. 

... was caused with impatience during conversion process, button Finish was pressed 2nd time if
the process took longer (looked like button was not pressed). Maybe disabling the button? 

 

Regards

 

mel

Nov 20, 2011 at 4:48 PM

@burningice

How's the MongoDB provider coming along? Is it something that you would be willing to share? 

Many thanks

Tony

Nov 23, 2011 at 6:06 PM

@eggheaddesign

I do have a provider up and running, but its not very interesting since i haven't made it Dynamic. This means it can only operate on the built-in types of C1 like IPage etc. This is due to the nature of interfaces/classes - MongoDB can't serialize a interface, it needs a concrete class and the same way when you pull data out of MongoDB, you need to instantiate a concrete class. So so far, i just included a bunch of implementations for the C1 types, but for types you define yourself Dynamic Codegeneration needs to be included and that's a huge area.

Another problem i faced with dynamic types, is how to handle when you rename a property. MongoDB is schemaless so you can't just tell it to rename a specific column to a new name, instead you risk loosing all the existing data. Normal procedure is to loop through all existing and copy values from old Property name to the new Property name, but still an cumbersome and error-prone task.

 

You still want it in the state its in? :)