1

Closed

Generic field type mappings

description

I would like to see a feature added to abstract fields types away when creating your model objects (especially when you need to override). An example would be:

A simple model object that has a date and a string
[DataField(FieldName = "SomeData", FieldType = "nvarchar(32)")]
public string TheData { get; set; }

[DataField(FieldName = "SomeDate", FieldType = "datetime")]
public DateTime TheDate { get; set; }
Luckily, MS SQL Server and MySQL can both create a table that matches those field types so you can use either data store without a hitch. Problems arise when throwing Postgre into the mix.

Postgre does not understand 'datetime', it wants 'date'. It also has no concept of 'nvarchar', it's always a varchar and you specify the encoding type. I'm sure there are many, many more example and inconsistencies between all the data stores, so it makes sense to me not to use a string, and abstract the types.

My suggestion, use an enum as the FieldType with constants like 'String', 'UnicodeString', 'MaxString', 'DateTime' etc. so that you don't have to know the exact string to use and SauceDB can decide on the correct column type when creating the table. For string you can also add Length to the DataFieldAttribute to override the 200 character default, coupled with a FieldType of 'String' since I suspect this is a common override.

A change like this would allow a developer to define a single model which will be able to be used by any data store since you don't need to provide any implementation specific strings. SauceDB can provide even more of an abstraction to the underlying data store which is what makes it so powerful to begin with.
Closed Jan 29, 2014 at 12:07 PM by iamkrillin

comments

iamkrillin wrote Jan 24, 2014 at 12:41 PM

I'm on board. I'll add it in for the next release

iamkrillin wrote Jan 24, 2014 at 12:43 PM

To add to what you said, if you don't specify the field type yourself sauce will pick an appropriate type for the field you chose. In your example above, if you omit ", FieldType = "datetime"" it will pick the right type for the field for the datastore you are using. Same thing for strings.

wernervd wrote Jan 24, 2014 at 1:11 PM

Awesome.

I know that omitting the FieldType lets SauceDB pick a relevant mapping, my biggest problem with this is the defaults values though... A date is good example of something you should just leave out, but a string is not that simple. The default is typically 200 chars (ASCII) so if you need 500 chars then you're already in trouble because you can't set the length without specifying the type :( Same thing goes for ASCII vs Unicode types, you now suddenly need to set the length as well (instead of taking the 200 default) and the type strings don't match across all data stores.

I'll be happy to assist with this if you need, just drop me some tasks or tests. I was going to pull the code this weekend and start some of the work for it so I'm glad you're on board already.

iamkrillin wrote Jan 26, 2014 at 2:38 AM

Sounds good to me, give me some time to think through how I'd like to do this..

iamkrillin wrote Jan 27, 2014 at 12:44 PM

I ended up working on this over the weekend and got most of it done. let me know what you think! Is there a use case I missed? Something else you would like to see added?

Changeset 105094

wernervd wrote Jan 27, 2014 at 4:27 PM

This looks great!

I can only think of two small things that I would add for completeness:
  1. Add UnicodeChar as well to match the String/UnicodeString combo. You could always just Use UnicodeString(1) but adding the proper type would complete the known types. Your SQL Server converter uses 'varchar(1)' which could be more accurate just being 'char(1)', your call.
  2. I would double check the FieldLength property against int.MaxValue and then translate that to the maximum for the data store (VARCHAR(MAX) in SQL Server and VARCHAR(65532) or TEXT in MySQL etc). This is also specific to whether you picked Unicode or not since the underlying maximums could be different in each implementation (http://stackoverflow.com/questions/332798/equivalent-of-varcharmax-in-mysql). Also be careful in MySQL of going over the row size limit so you can't just hard code 65532 so TEXT might be a better option :(
This looks very promising and I can't wait to make use of it to get more data source support out the box.

iamkrillin wrote Jan 27, 2014 at 7:54 PM

Indeed, I thought about those things as well while I was implementing it and for whatever reason I decided against it/forgot before I got done. I agree that adding those two things in would def round out the feature. I'll prob. add the type 'text' as well since most data stores support it.

iamkrillin wrote Jan 28, 2014 at 2:23 AM

done

changeset 105116

wernervd wrote Jan 28, 2014 at 10:51 AM

Went through the changesets, sold!

So now the obvious question is, when will this be released?

Thanks again for adding this. These particular changes make for an incredibly powerful abstraction of the datastore column types, I've wanted something like this for quite some time and I found SauceDB just at the right time.

Time to send some tweets...

iamkrillin wrote Jan 28, 2014 at 12:18 PM

I'll prob build the release packages sometimes between now and this weekend and push it out then

wrote Jan 29, 2014 at 12:07 PM

iamkrillin wrote Jan 30, 2014 at 12:12 PM

Package Released, 3.2.0