Django change the database backend.

In theory if you use django, changing your RDBS from say sqlite to mysql is a breeze. All you need is generate a dump of your existing data with ./manage.py dumpdata, change settings.py and do ./manage.py migrate on the new server. Then you can import the previously exported data through ./manage.py loaddata but it isn't always that simple.

The ./manage.py migrate step on the new database invariably through an error or two dozen. Squashing migrations often works, but the simplest thing is to delete all the migrations from each app's migrations/ folder. Then you do ./manage.py makemigrations followed by ./manage.py migrate and if you are lucky you have a newly created database on your hot new server. If you are unlucky you will find that some of the model fields you have used (example ArrayField) are incompatible with the new RDBMS you are moving to. I leave you to solve that yourself with the reminder that ArrayField should never have been used in the first place.

Anyone who has ever tried to use Django's loaddata command can tell you that it will choke on django Content Types. Once you get past that hurdle, you will still find all sorts of errors popping up. Of course you can use MySql's SELECT INTO OUTFILE or Postgresql's COPY command to make the import export process less painfull but the only catch is if you have a large number of models, you will find yourself doing a lot of typing. Enter Odo.

import os, sys

if __name__ == '__main__': #pragma nocover
    # Setup environ
    sys.path.append(os.getcwd())

    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "myapp.settings")

    import django
    django.setup()

from odo import odo
from django.apps import apps

for m in apps.get_models():
    odo("sqlite://db.sqlite3::{0}".format(m._meta.db_table),"/tmp/{0}.csv".format(m._meta.db_table))

The above code will export data from every single table in your database into a set of CSV files using sqlite's .output command for the export, while most home brewed exporters would save the data line by line. Right now, you can import these into another database right? What if I told you that you can actually import export without intermediate CSV dumps? yep, quite possible but your milage might vary.

Dec. 22, 2016, 8:37 a.m. » Tagged: odo , python , django , sqlite , mysql

using SQL Server

sqlcmd sucks (like most everything else by MS).  I thought to try out VS Code to connect to the sql server. Unfortunately I had to upgrade my Linux Mint Version due partly to inistallation conflicts that sqlcmd had with it (Mint Rosa). But what do I get when I try to run the sql-server extension on VS Code?

Failed to load sql tools service 

This is a known issue with Mint 18. It will probably be fixed soon but rather than waiting for it, I thought to try our HeidiSQL, which works under wine. I am perfectly happy with using psql client for postgersql and the mysql console for mysql but having a single GUI that can connect to all three does have it's advantages too. But it will apparently not work out of the box with mssql 

Once you install native_mdac and mdac28 you need to close and open heidisql again. Oh, one more thing, it's heidiSQL portable that worked for me rather than the standard download. The standard download complained about a missing mysql.dll but the portable version worked with both mysql and mssql but not so postgresql. Heidi would complain about a missing libpq.dll which is there in the same folder at heideisql.exe. Typing `wine heidisql.exe` from the command line reveals:

err:module:import_dll Library MSVCR120.dll (which is needed by L"C:\\heidi\\LIBEAY32.dll") not found
err:module:import_dll Library LIBEAY32.dll (which is needed by L"C:\\heidi\\SSLEAY32.dll") not found
err:module:import_dll Library MSVCR120.dll (which is needed by L"C:\\heidi\\SSLEAY32.dll") not found
err:module:import_dll Library SSLEAY32.dll (which is needed by L"C:\\heidi\\libpq.dll") not found
err:module:import_dll Library MSVCR120.dll (which is needed by L"C:\\heidi\\LIBEAY32.dll") not found
err:module:import_dll Library LIBEAY32.dll (which is needed by L"C:\\heidi\\libpq.dll") not found
err:module:import_dll Library MSVCR120.dll (which is needed by L"C:\\heidi\\libpq.dll") not found

Apparently this can be fixed by installing vcrun13 in winetricks, but winetricks just hangs on it without producing any output. So I decided to ditch this and switch to DBeaver. DBeaver is JDBC and automatically downloads the driver. So it works with practically any RDBMS and CSV files too!

Dec. 17, 2016, 1:03 a.m. » Tagged: heidisql , sql-server , winetricks , wine , DBeaver

Mint Upgrade

I hate updating my OS. I only do so when I am left with no choice, like when so many packages conflicts appear as they are doing now. Many of these are related Oracle and SQL Server clients on Mint 17.3 (Ubuntu 14.04). In the old days upgrading mint was particularly diffcult because it doesn't have the 'dist upgrade' option found in other ubuntu deravatives. But luckily mintupgrade has popped on the scene in recent times. It's rather slow but at least the computer is usable while the upgrade process is running. Whatever process you use for the upgrade, there are always one or to post upgrade issues. This is what Mint Sara (18) (Ubuntu 16.04)) came up with.

missing datetime in python.

Trying to start the django dev server with ./manage.py shell and this is the result:

Traceback (most recent call last):
  File "./manage.py", line 8, in <module>
    from django.core.management import execute_from_command_line
  File "/usr/local/virtual-django-19/local/lib/python2.7/site-packages/django/__init__.py", line 1, in <module>
    from django.utils.version import get_version
  File "/usr/local/virtual-django-19/local/lib/python2.7/site-packages/django/utils/version.py", line 3, in <module>

    import datetime
ImportError: No module named datetime

The solution apparently is to replace the python binary in the virtualenv. I still haven't figured out why exactly that is so, but it certainly worked.

gvim and python

Error detected while processing /home/raditha/.vimrc: 
line    3: 
E319: Sorry, the command is not available in this version: py << EOF

What solved it was a comment on an askubuntu answer

Python3 is default interpreter for Ubuntu 16.04. If this message appears as a result of using pycommand, replace it by Python 3 equivalent 

gvim, python and execfile

That still leaves me with the following problem:

line   12:
Traceback (most recent call last):
  File "<string>", line 8, in <module>
NameError: name 'execfile' is not defined

The fixe is described here: http://stackoverflow.com/a/6357418/267540

After both these changes, the first part of my .vimrc looks like this

py3 << EOF
import os.path
import sys
import vim
if 'VIRTUAL_ENV' in os.environ:
    project_base_dir = os.environ['VIRTUAL_ENV']
    sys.path.insert(0, project_base_dir)
    activate_this = os.path.join(project_base_dir, 'bin/activate_this.py')
    exec(compile(open(activate_this,"rb").read(), activate_this, 'exec'), dict(__file__=activate_this))
EOF

Interestingly both the above vim/gvim related errors appear only when gvim is started after the virtualenv has been activated. 

Dec. 15, 2016, 1:33 p.m. » Tagged: mint , vim , gvim , python

SQL Server on Linux

Right, it's been more than 15 years since I last opened a connection to a M$ SQL Server. I thought I would have another crack at it since Mircrosoft has embracced open source in general and linux in particular.

I setup sql server on my linux machine using the official docker image provided by microsoft. However when ever the server was started according to the instructions the container would run for a few seconds and then shut down. If you try to connect to it during the brief period when it's alive, you get an error like this from sqlcmd:

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server..
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x68.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to establish connection.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to establish connection due to prelogin failure.

Searched around for quite a bit but no solid answer was found. Then I re read the instructions again. Apparently this innocuous looking bit is rather important:

  • A strong system administrator (SA) password: At least 8 characters including uppercase, lowercase letters, base-10 digits and/or non-alphanumeric symbols.

If you specify too short a password the server apparently doesn't start or dies off immediately. When you run a docker image as a daemon, the container also shuts down if there are no processes running inside it. Rather lame but there are you.

Update:

To make the data persist you have to pass the container a path on the local file system for read and write. The official document says pass the parameter like what's given below, but it forgets to tell you what exactly should go into the host directory flag.

-v <host directory>:/var/opt/mssql

Apparently almost anything will do. I used -v /var/opt/mssql:/var/opt/mssql

Dec. 13, 2016, 3:48 p.m. » Tagged: sql-server , microsoft , docker

Asp.Net in 24 hours.

Now it's time for the die hard Django fan to learn ASP.Net. Once upon a long ago, around the time that Asp.Net was born I worked with a guy who had practially every single M$ certificate they had on offer at the time. We were constantly arguing about which is better PHP or ASP. Those days PHP was crap (and now it's worse) but it was s sight better than ASP. So much so that when my friend started out on his own, his shop only dealt with PHP and not with Asp or ASP.Net. Now things have come a full circle. 

Yesterday, I made an attempt to learn C# in 24 hours. Heck it didn't take 24 hours, mostly because the language is a lot like Java and the parts that are not like java seems like python. As Thameera pointed out in a comment yesterday, it's been having Lambda's for years.

Having learnt the basic stuff, I even wrote a bit of code. It was to parse a CSV, transform it and load it into an SQLite database. Of course I know how to load a CSV directly into SQLite (I generally prefer sqlitemon for this rather than the command line interface). But this data set needed some transformations, beside the whole point is to write some code!

apparently System.Data.Sqlite isn't available for Dot Net Core, you need to use Microsoft.Data.Sqlite and the latter is sparesly documented, but no fear, it seems to be compatible with the former.

So on to Asp.Net , let's hope to finish it by this time tommorrow 

Dec. 7, 2016, 1:10 a.m. » Tagged: csharp , asp.net , django

C# in 24 hours

What the hell am i doing with c#? What the hell is microsoft doing on github? When Visual Studio Code was announced for linux, I was more than sceptical. I scoffed and folks who tried it out. But now here I am having a crack at it myself. Right now I am on holiday at my sister's place in the hills. This is where I learned python a few years back. Maybe it's time to have a crack at learning C# in 24 hours. I will probably make a fool of myself with the comments I am making on this blog post and maybe even the next few posts as well. But never mind. The time now BTW, is 6:41 is in the morning. It's cold but very bright and sunny.

Program strcuture.

The first thing that csharp hits you in the face with is Namespace. I rekon this is like java packages and inside a name space you can have classes and interfaces and we all know what they are. Less obvious was Using, this sounds a bit like import package.* in java. Shudder. The program entry point is public static void Main(string[] args) now where have I seen that before?

Types

Why does csharp have so many different types? Who in his right mind will bother with ubyte, ushort or byte instead of using plain old int? What's the good of toasting a few brain cells to save a byte of storage? And what's this enum type? No one in their right minds uses enum in mysql, I get the feeling that csharp's is something along the same lines. The struct keyword is straight out of C, so why is it needed in an object oriented language?

Delegates are a bit tricky, it seems to be like a pointer to a function in C which again raises the question of why it's needed, perhaps a better way to think of it would be to compare it to python function which are objects and can be passed around as parameters to other functions.

While still on the subject of types, you can always trust microsoft to make things as complicated as possible. Proof:

Nullable value types also do not have to be declared before they can be used. For each non-nullable value type T there is a corresponding nullable value type T?, which can hold an additional value, null. For instance, int? is a type that can hold any 32-bit integer or the value null.

Expressions

Nothing much to write home about here. Pretty much the same as almost any other language. Except for maybe checked and unchecked. If the expression is wrapped in checked it will not raise an exception if there is an arithmatic overflow.

Classes and objects

Again a well traveled path but the internal access modifier was something that I didn't quite get the hang of and I had to brush up on virtual methods to get it through to my head. After so much of Java and Python almost didn't recognize the ':' notation to extend a class as coming from C++. properties smell like @properties in python. Events and event handlers are going to take a bit of getting used to, specially because they are tied to delegates (which I am not 100%) with yet and also because event handlers are added with += and removed with -= . I suppose it does make some sense when you think about it.

 

This is beginning to look easy. Should have tried this out long before this.

Dec. 6, 2016, 12:33 a.m. » Tagged: csharp , dotnet , vs code

Predictability of tournament results based on ratings.

This is a part of a series of articles on the King Of The Hill Pairing System and it's use at Scrabble tournaments. King Of The Hill or KOTH for short is a strict 1 plays 2, 3 plays 4 , 5 plays 6 pairing system commonly used at the last few rounds of a Scrabble competition. It allows repeat pairing. In other sports it's sometimes used without repeats and then it's called Monrad, Danish Pairing, Australian Draw etc. This analysis is based on the results of the Sri Lanka age group Scrabble Championship 2015. As any Scrabble enthusiast will tell you, Sri Lanka is a power house for scrabble at the junior level, so the conclusions drawn here can be applied in other countries as well.

Pre tournament ratings are an excellent indicator of whether or not a player can be considered a contender for any of the prizes. Past winners of this event have been players with high ratings. The defending champion Hasindu’s rating was 900 at the time he won the event and he entered the tournament with a rating of 1053 which clearly indicates a significant improvement of his playing skill.

A total of 17 rated players took part in the event with the highest being Migara with 1148 points. These 17 players regularly compete against adults and beat them in rated tournaments. The rest of the field only participate in age group or inter schools tournaments which are not rated and the standard of competition is much lower. As a result There is a huge gap between the strengths of the rated and unrated players. 

Rank Name Rating Wins Position Difference
1 Migara Jayasinghe 1148 5 2 -1
2 Aabid Ismail 1134 6 1 1
3 Janul De Silva 1054 4 20 -17
4 Hasindu Arumapperuma 1053 4 13 -9
5 Janidu Karunarathne 964 3 38 -33
6 Thavalakshman Yoganathan 938 4 21 -15
7 Hayati Rassool 924 5 3 4
8 Radinka Dissanayake 897 5 5 3
9 Thirandi De Silva 847 3 37 -28
10 Vignesh Pirapaharan 840 4 15 -5

Table 2.7 Players sorted by rank vs their final position.

Only four of the ten highest rated players managed to secure prizes. As has been shown in 2.3 many of the players who missed out had to play more contenders than the prize winners. It will be shown in the ensuing sections that the pairing effectively prevented these players from winning any prizes.

In previous sections, we had already discussed how the draw had a huge negative impact on Janidu and Thirandi, but Thavalakshman is a special case. The draw did effect him by pitting him against 5th placed Radinka very early in the tournament. He might still have ended up in the top ten if he didn't suffer an unexpected defeat in the last round by just one point!

July 28, 2016, 6:51 a.m. » Tagged: KOTH , Scrabble , Pairing

When should top players meet in a tournament?

In any sport, it's undesirable for the top players to play against each other during the early stages of the tournament. When it happens some of them will be knocked out or be out of contention for the top prize. That's why almost all chess tournaments involving a large number of players employ the swiss pairing system. If we take as an example a swiss tournament with 50 players and 5 rounds, the top two players will meet in the fourth or fifth round provided that both of them have managed to defeat all weaker opponents. Thus the overall winner isn't known until very end of the tournament.

A chess tournament

Now suppose the same chess tournament was conducted using King Of The Hill pairing (In chess KOTH is actually known as the Monrad system). Because players are seeded based on their pre tournament rating, if both the top players win their first round game, they would be adjacent to each other in the standings. King of the hill, the simplest pairing system of them all pairs 1 against 2, 3 against 4, 5 plays 6 etc. Thus top two players would be paired against each other in the second round itself. If they draw their game someone else might go on to win the tournament! If either player wins, the loser is immidiately out of contention for the top prize and we are only just at the start of the tournament!

How does it work at Scrabble tournaments that employ KOTH? Lets look at the results from the Sri Lanka Age Group Scrabble Championship 2015 to see what happens. Janul entered the tournament as the third highest rated player. Migara had the highest pre tournament rating and he also happened to be the champion of 2013. They met in the second round and Janul lost. Hasindu was 4th in pre tournament ratings and he entered the tournament as defending champion. He met 5th rated Janidu in the second round. Hasindu lost. Which meant at the end of the seond round the defending champion had practically been knocked out!

The software used for pairing this tournament was TSH, and it's website speaks thus:

“Top players should be paired with each other, especially toward the end of the tournament. King-of-the-Hill pairings do this best, Round Robin pairings do this worst.” (Chew, John 2016)

This statement clearly isn't very accurate, the following table which highlights the number of prize winning players met by each top 10 finisher provides more proof. It allso provides the Average Opponent Rank for the same players.

 

position Player Average Opponent Rank Opponents Among prize winners
1 Aabid Ismail 16.67 1
2 Migara Jayasinghe 42.83 0
3 Hayati Rassool 32.50 2
4 Hansi Weerasooriya 22.67 3
5 Radinka Dissanayake 28.17 2
6 Rehan Wijesekera 46.17 1
7 Danusha Muhandiramge 31.50 2
8 Yenuli Hewage 35.50 0
9 Navodya Jayaratna 37.50 1
10 Akshai Yogalingam 56.67 0

As well as their Average Opponent Rank. Three of the prize winners have not played against any other prize winners at all. Akshai has not played any prize winner and as will be shown in section 2.4 has not played with any top ten rated players either.

The last round of the tournament saw Aabid paired against Hansi as they were ranked first and second based on round 5 results. This was the only occasion where the champion was paired with another prize winner or another top rated player.

Thus King Of The Hill pairing has failed to ensure that top players meet towards the end of the tournament.

July 13, 2016, 6:28 a.m. » Tagged: scrabble , chess , swiss , dutch