Tips for running Entity Framework (6) and MySQL / Maria

My first forays into running Entity Framework with MySQL have been entertaining to say the least. Each challenge seems to have followed the next and required a fair bit of research to solve so I thought it was worth compiling them all into one blog post. It’s an aide memoir for me and if it helps you readers out too, all the better!

bit vs tinyint for booleans

By default, EF will handle booleans as tinyint under the hood as MySQL in general has no built in concept of true | false other than 1 | 0 and uses tinyint(1) as a substitute. However, tinyint gets treated as byte rather than bit by EF so it’s all a ‘bit’ confusing (ahem, sorry)… Luckily, there is an easy code fix.

symptoms

Error messages usually seen in this case:
System.FormatException: String was not recognized as a valid Boolean..

fix

Add this to your database Context code:

inheritance

Unlike with MSSQL, when using EF6 with MySQL, only one entity in the hierarchy can be concrete – the leaf. This can have implications for your design if you have complex similar entities and want to abstract out those things they share in common.

symptoms

Error messages usually seen in this case:
The EntitySet 'CClass' is not defined in the EntityContainer 'TestContext'

fix

  • Redesign

In a hierarchy such as
MailBox ← ExchangeMailBox ← Exchange2016MailBox
only Exchange2016MailBox can be concrete

  • Manually map inherited fields to the table in your Context via the OnModelCreating method using

unique indexes

Where the design calls for a unique index on a varchar field (e.g. email address) this can be inhibited by strong internal database limits. For the unique index to work on MySQL, there is a max length that must be applied to the field. The exact value is dependent on the collation and database engine used and relates to an underlying byte value.

  • MyISAM allows keys of length 1000 bytes.
  • InnoDB allows keys of length 767 bytes.

On ascii (latin-1), characters are assumed to be stored as 1 byte, giving varchar(767) or above. However, UTF8 collations will store a char in between 1 and 3 bytes. Expect to be able to use max varchar(254).

For keys with collation utf8mb4 or UTF-16, the byte length per character is assumed as 4 bytes allowing a maximum of varchar(250) on MyISAM or varchar(190) on InnoDB.

symptoms

Error messages usually seen in this case:
Specified key was too long; max key length is 767 bytes

fix

  • consider changing your collation; does your internalisation policy require UTF-16 or only UTF-8?
  • consider changing your database engine – loads of StackOverflow articles address this InnoDB vs MyISAM debate, there’s even an article on wikipedia (to be read with the appropriate level of salt)
  • enforce the appropriate maximum length
  • enforce uniqueness programatically (and trust you don’t miss a use case in the code base)

performance issues

Although Entity Framework 6 supports the Find linq method it is not performant and leads to some very inefficient sql query creation under the hood. Big performance improvements can be made by using Single or SingleOrDefault instead.

If you have a complex hierarchy of entities it’s worth considering the question of database policy Table-Per-Heirarchy vs Table-Per-Type. There are good articles out there explaining the difference and suggesting that TPH provides better performance, particularly on Entity Framework. It makes use of a discriminator column which brings its own design  decisions. Historically there have been reports of issues with discriminator columns in earlier releases of EF6 but these appear to have been fixed.

Beyond that changing the underlying database to MSSQL or adopting a different ORM may be your only options. Looking ahead, EF7 does not yet support MySQL and no one knows when Oracle will change this so it’s worth investigating other solutions.

Lazy coding for the win

Its been a while since I wrote any new hook scripts so I thought I would refresh my knowledge before talking about them at phpconference.co.uk. I also love playing around with python – it’s straight forward, pretty and gives good ‘sad puppy’ feedback when you’ve missed a closing bracket.

So I came up with this beast to turn svn log output into a dictionary. You can then do what you like with it: output to a file, turn into html, stream to a feed, whatever.

What can I say, I like understanding things at a first principles level.

Of course then I remembered a cool flag which turns a predetermined text output into something much more meaningful.

xml.etree.ElementTree is the built in python way of handling xml and, again, it’s a bit hand cranked and leads to many unnecessary lines of code. So why re-invent the wheel?

“Has someone already done this?” is rapidly becoming my first question, and yes, someone has!

https://github.com/martinblech/xmltodict

This is the second module library I tried, and is one that can actually deal with lists of elements – essential for svn log output. If you are used to handling things in JSON, it’s really easy:

I have to admit, a little piece of me feels like I’m no longer writing ‘real’ code and that there’s nothing left unsolved or uncoded. The rest of me is doing a happy dance – now we are freed up to use technology to solve the real issues – save time, collaborate well and communicate effectively to make lives better!

No shoddy work in the free world

When evaluating a feature for value it’s easy to fall into the trap of equating value with money; direct revenue is not the only value you can get from a feature. Here are some other concepts of value:

  • ease of maintenance, monitoring and admin tools to quickly identify and diagnose potential issues (keep good customers)
  • framework designs can save time adding new features and allow parts of your system to be resold as services (robust resellable software)
  • the right free features can draw customers to your product in the face of stiff competition, gaining you greater customer numbers (get more market share)

The benefit from these features is completely wasted if their execution is ill thought out, is flaky or worse, they don’t work at all.

If you hear that someone isn’t ‘allowed’ to put too much time into a feature because it’s offered for free or its only internal, alarm bells should be ringing. The same key aspects of software should apply here as for anything else:

how this feature should work for each and every user type

Does every type of customer interact with the feature in the same way? Are there staff only actions? Does the feature work in different ways on different tariffs?

how it should be presented and supported

How does the interface appear? Does the ‘flow’ of the feature make sense to the intended audience? What help is available immediately alongside the feature? Is it easy to get to more detailed help? How do support staff learn about this feature?

what design will allow it to be flexible for the future

Does this feature scale appropriately? How much does it share with other features? How often do they change? Do we anticipate offering this through another channel or interface? Is there the possibility of a new type of customer we could be offering this to?

who we should be collaborating with

internal users, infrastructure experts, trainers, support staff, beta testers, external suppliers, industry partners

how we can develop it to be easily maintained and monitored

active monitoring and analysis tools for predictive performance, usage and capacity; intelligent logging and log file analysis; alternative access to feature data including admin tools; api endpoint test harnesses and tools

how we should be testing

when we should be testing; who should be testing; what we are testing for: functionality, usability, performance, load

the most efficient, and least disruptive, release process and cycle

where we deploy, redundancy and failover, how often we release, minimising outages, maintaining reliable and predictable service.

If your free service is mentioned as a big selling point in all the marketing material and brings in twice as many customers, you’ll be glad to have shunned the shoddy and invested wisely in the free world.

This weeks lesson : frustration

This last week has been a lesson in frustration – changing requirements, dependency on offshore decision-makers, flakey debugging environments and forgotten code constraints.

These things have always lit a fire under my calm exterior and left me wanting to scream, throw the laptop out of the window and snap at all my colleagues who only want to help. This week was different and it’s a strange (and wonderful) new world.

Continue reading This weeks lesson : frustration

It’s been a while…

I’ve moved domains, changed jobs, closed my business and visited at least two new countries since last posting on any public blog!

Porting the blog across from the old domain has involved fixing it, reading it and potentially pruning it heavily (still ongoing).

I’ve learned that while my career has moved on and I’ve taken on more then less responsibility and expanded my (coding) languages count from few to many some fundamental things don’t change and I’ve found myself returning to some core lessons – again for the first time. Posts to follow!

Three things I wish I’d known before …

… starting the Sun Java tutorial

It’s been over a month since the last blog entry and it’s been a busy month:
finishing off projects
planning a holiday biking round the south of france on a GSXF
and
getting a new job.

The new job was the impetus to take on the java tutorial, trying to get my head around strict typing, odd syntax, compiling code and a whole new API. That’s all hard enough but most of the brickwalls I’ve been banging my head against aren’t down to understanding the language… they are down to how Java is set up and the environment it expects. I feel like I learnt the lessons all in the wrong order.

So for my memory and others out there who’s javac won’t run and whose java class cannot be found, here were my Three Things I Wish I’d Learnt First…

[edit jan 2015]
those three things got lost… so the real lesson is when porting an old blog from one site to another:

  • always make sure your blog software was kept up to date
  • check the source of your posts for substituted rubbish
  • prune old posts vigorously!

My Unecessary csv Conversion

I’m finding that I’m doing some things the hard way, maybe it’s just I’m looking at problems differently to others. Take my latest fun freelance challenge:

Convert data from csv and import into database using phpMyAdmin (access details will be supplied).

I saw this as: convert .csv file to sql file then use phpMyAdmin to process sql file either by quoting sql in the sql query screen or using the import from sql file.

The simplest way to see this is actually:
get data from .csv file into database using phpmyadmin as database access.

The difference might be subtle but my first solution was a quite a bit more complex. Continue reading My Unecessary csv Conversion

Navigation revisited and freeing your thinking

Revisiting Navigation

Last month I set out my view of using <nav> and <ul>s for navigation purposes. This was as a reaction to feelings in the php development community against changing the default behaviour of <ul>s using css.

The message that came across clearly after the fact was: the objection was not to the use of <ul>s as parent elements for a list of links. The problem comes with the styling:
/*css seen as a problem */
ul {text-align:center;list-style-type:none;}
li{float:left;}
a{display:block;padding:2px 5px;}

The problem is that default behaviour for all <ul>s is changed so <ul>s elsewhere in the page will be styled like this.

/*css seen as a reasonable solution */
nav ul{text-align:center;list-style-type:none;}
nav li {float:left;}
nav a{display:block;padding:2px 5px;}

Just the behaviour for children of the <nav> element is changed leaving other <ul>s to be styled differently (or defaultly).

This is a really subtle distinction and I don’t mind admitting it took me a while to sus out. The argument isn’t against using <ul>s in navigation but advises us to do so in a careful and considered way.

Not using <ul>s closes the door to such enhancements as submenus and accordian style menu layout. This shows up my intial example last month, at best, as being oversimplistic and a bit naive.

Jumping to Conclusions

It’s not the first time in my life I’ve jumped to a conclusion. Another I’ve revisited recently can be succinctly summed up in a sentence from PHP in Action:

Alternatively, we could achieve the same effect with Javascript, but it’s better to avoid Javascript if we can, since some users disable it in their browsers.

Reading this recently initially reinforced my long held belief that Javascript is Bad because some users disable it.

The desired effect mentioned in the quote from PHP in Action is getting submit buttons to generate individual commands where there are multiple submit buttons in a form. The authors go on to suggest solutions in javascript which work where there is already javascript present in the form so they aren’t really saying Javascript is Bad so Don’t Ever Use It.

I first hit the ‘Javascript is Bad’ assumption in accessibility and coding circles in about 2003 when the numbers of users with disabled javascript were about 20% (based on visitors to W3C at the time). As a result of taking it for granted I spent six years not bothering to learn much javascript because it’s Bad.

What a waste!

The Real Lesson

I am quickly working to fill this ridiculous hole in my knowledge, with the understanding that javascript should be used with care, for tasks where it is the best fit and always ensuring that all the functionality of the site is still there if a user should have javascript turned off or uses a screenreader.

The lessons here are ones I’ve taken a while to learn and they are relevant to coding and most things in life, I hope this blog post helps others learn them quicker:

  • Conclusions drawn, even by the ‘wise men’ in a community should never be taken on their own, as gospel.
  • Always check the date of a resource if you can.
  • Do your own tests.
  • Join in discussions and check your view against that of others.
  • Go back and review your own conclusions after time, has anything changed? new techniques or technology been released? did you jump the gun?
  • Never be afraid to admit you would change your mind (even if it’s only to yourself).
  • Never be afraid to try something new.

Is there anything you don’t agree with? have I missed something? what assumptions have you challenged recently (your own or from others)?

html markup for navigation

There’s been a lot of griping today about the use of an unordered list for a navigation menu (from @calevans among others). Although it is a generally accepted practice with the argument that a navigation menu is an unordered list of links, it’s not too difficult to find an alternative that is just as efficient is it? What’s the most basic code we could use as an alternative?

In html5 you could use the following: –

<nav>
<a title="First link additional text" href="#">Link 1</a>
<a title="Second link additional text" href="#">Link 2</a>
<a title="Third link additional text" href="#">Link 3</a>
</nav>

with css to sort out positioning and look.

For some specific layouts you may want to specify a :first-child pseudo class or a class="last" on a link.

For HTML4 strict just substitute a <div class="menu"> for <nav>.

So, not as complicated as I once thought!