Processing JSON in MS SQL 2014 / 2012

One of the new additions to MS SQL 2016 is support for JSON data. It’s pretty much similar to what we have in SQL 2014 for XML processing – similair functions and processing features.

But what if we need to process JSON data but only SQL 2014 server is available? Well, for simple reading of properties we could just use some string manipulation – it will be fast and more or less readable:

SELECT *
FROM tb1
JOIN tb2 on tb2.bvin = 
    SUBSTRING(
        tb1.json
        ,CHARINDEX('"bvin":"', tb1.json) + LEN('"bvin":"')
        ,CHARINDEX('"', tb1.json, CHARINDEX('"bvin":"', tb1.json) + LEN('"bvin":"')) - CHARINDEX('"bvin":"', tb1.json) - LEN('"bvin":"')
    )

-- from: https://stackoverflow.com/questions/23723473/query-json-inside-sql-server-2012-column
Czytaj dalej Processing JSON in MS SQL 2014 / 2012

[Console] New approach to improved console in C# (part 1)

I’m back after a looooong time. I’ve been working on several tools, mostly on my console libraries. And I think this is the time when I can start posting about how to use it. Now, when base library is in the state that looks and works as expected and is almost polished.

So, at first I would like to thank Michał Białecki for hist Blog Post, that finally encouraged me to write about my console implementation. Mainly because it’s a nice improvement to the stuff he is / was working on, secondly because I would like people to start using what I’ve created 😉

Then. To the point.

Github for Console libraries is located HERE, please note, that there are many more components (projects) in that repository that I’m NOT going to touch in this post and are in much less usage-ready form than – so called – Root package.
You can also find some Nuggets on Nuget.org, but they are a lot outdated – they come from previous, differently divided solution. I shall soon update them and here in the post.

Main points that this root project supports:

  • Support for 24bit colors (Windows 10).
  • Color schemes support (soon importing from files)
  • Automatic adjustment to closest color (this was the first feature I’ve implemented, way in 2016… A long before discovering colorfulconsole
  • Fullscreen support
  • Easy configuration of resolution
  • Multithreading support (will write about this later)
  • Buffered and non-buffered modes (comming soon)
  • Support for improved colorfullconsole syntax (soon, if someone does that 😉

Czytaj dalej [Console] New approach to improved console in C# (part 1)

Flaksator – testing noSQL DB and dictionaries storage

In the previous post I’d described interface for storing String Lists as collections. In the meantime, I’d decided that StringWrapper was not good name for that object, and therefore I’d decided to change it into StringCollectionWrapper.

In this post I’m then continuing with noSQL storage.

Testing StringCollection storage

I’d decided to keep shared data file as part of the solution (/Data/Flaksator.db) to be able to easily manage it using GithubPlugin. Because there are / will be several projects that depend on it and are located on different level in file system hierarchy I do expect to have problems with accessing it – or having to manage different, scattered configurations. While clean unit tests should do not depend on any external resources, current piece of tests (kind of integration tests, I guess) require access to physical DB. The problem is that I prefer to keep test projects in dedicated subfolder in both file system and solution. Therefore path to db file is different depending from where it is going to be called:
VariousLevelFileAccess
Czytaj dalej Flaksator – testing noSQL DB and dictionaries storage

Flaksator – storing core resources in noSQL

As I’d already mentioned, I’m going to store all Flaksator’s resources in noSQL database. I’d already decided to use lightweight (it’s just package / DLL) implementation.

And before I start I owe one explanation to English speaking visitors. Flaksator is just a play-on-words: original library responsible for managing Polish flection rules I’d called „Fleksator”, from Polish „fleksja” meaning „flection”. Then, my – more or less – funny application that randomized brutal, blasphemous lyrics I’d called „Flaksator”, which differs only by one, yet meaningful letter from library name. The Polish word „flak” means „guts, entrails, bowels” in English. Pleas enjoy my creativity 😉

I know I have several types of resources in the Flaksator:

  1. Simple lists of strings (title verses templates, verse templates, song static elements)
  2. Simple dictionary items (translations, enumerations, word categories)
  3. Definitions – complicated dictionaries (mainly postfixes lists)
  4. Word definitions themself (will be covered in next article).

Czytaj dalej Flaksator – storing core resources in noSQL

Flaksator – Introduction to song generation details

Well, I’m afraid this is about time to stop procrastination. As one of fellow bloggers suggested (thanks Andrzej) I’m switching this series into English. There are at least few reasons: wider audience, more ways to promote my blog and most importantly – to share some nuances of Polish language with English speaking people that might be interested in.

Well, where to start…

Song structure

Every song has a title. And then body. Or not – if it is instrumental one. So, first decision is to randomize that case – let say giving an instrumental creation a 5% chance. The fun factor comes from sometimes ridiculous brain associations on how such stupid title can be realized by only instrumental performance. See few examples and try imagining those tracks:

ORGIASTYCZNI DOKTORZY - Orgiastic Doctors
NAJBARDZIEJ SPLUGAWIONY UPIÓR - The most defiled phantom
WYRACHOWANI RESTAURATORZY (KABŁĄKOWATY KONIEC MISIA) - Selfish (calculated) restaurateurs (baily end of teddy-bear)
SPODNIE W OKNIE BOKU KLEJU (NADCHODZI PRADZIAD) - Pants (trousers ;-)) in the window on the side of glue (Here it comes the great grandfather)

Titles being stored in separate file (Titles.txt) and are treated just as regular verse (will be discussed later). Sometimes titles are being decorated with extra parts from TitleEx.txt to increase fun factor even more 😉
Czytaj dalej Flaksator – Introduction to song generation details

Flaksator – analiza przechowywanych danych: rzeczowniki

Stara implementacja Flaksatora bazowała na dość skomplikowanym, tekstowym i pół-skompresowanym formacie. Plus był taki – że względnie dało się to edytować ręcznie… Dopóki się pamiętało o co w formacie chodziło. Aktualnie planuję umieścić słowniki w jakiejś bazie noSQL. Nie potrzebuję żadnego rozbuchanego systemu typu Cassandra czy MongoDb – wystarczy mi ten cusotm made: LiteDB. Zweryfikowałem to już przy innej okazji i dla potrzeb Flaksatora jest więcej niż wystarczające.
Tym niemniej, potrzebuję skonwertować istniejące bazy tekstowe do nowego formatu. Prawdopodobnie najszybciej będzie po prostu odczytać i zdeserializować obiekty ze starego systemu, skonwertować je do obiektów z nowego systemu i zapisać gotowce do noSQL. Ale tak czy siak, dla porządku spróbuję przypomnieć sobie, jak działał ten stary format:

Rzeczowniki

alibi|!N|$7,4,18,11|#
pies|@ps|!LM4|$3,8|+1D1|%V1psie|%L1psie|%C1psa
jelit|!NN3|%L1jelicie
bliźni|!PM1|$7,1|%G1bliźniego|%D1bliźniemu|%C1bliźniego|%A1bliźnim|%V1bliźni|%L1bliźnim|%N2bliźni|%G2bliźnich|%D2bliźnim|%V2bliźnich|%L2bliźnich|%A2bliźnimi|%C2bliźnich|%G1bliźniego|%D1bliźniemu|%C1bliźniego|%A1bliźnim|%V1bliźni|%L1bliźnim|%N2bliźni|%G2bliźnich|%D2bliźnim|%V2bliźnich|%L2bliźnich|%A2bliźnimi|%C2bliźnich
boa|!L|$3,11|#
kakao|!N|#

Jak widać format jest dość paskudny, ale nawet niewprawne oko jest w stanie wypatrzeć kilka faktów:

  1. Jeden wiersz, to jedno słowo. Porządek jest przypadkowy – w kolejności dodawania.
  2. „Pajpy” rozdzielają definicję na różne sekcje
  3. Kolejność sekcji wydaje się dość swobodna, poza pierwszą.
  4. Pierwsza sekcja zawiera „root” – sam temat słowa, ale nie samo słowo w mianowniku (chociaż w niektórych przypadkach tak jest) – jak można by oczekiwać
  5. Słowa z regularną odmianą mają bardzo krótkie definicje.
  6. Za to te nieregularne – masakryczną

Na pewno zagadką jest znaczenie symboli takich jak !, #, $ czy @. Zerknijmy na kod – nie da się tego dłużej odwlekać…
Czytaj dalej Flaksator – analiza przechowywanych danych: rzeczowniki

Konstruowanie obiektów IRandomizer oraz testy (wydajności)

W poprzednim odcinku dokonałem wyabstrahowania interfejsu IRandomization by umożliwić testowanie kodu korzystającego z elementów losowości. W obecnym skupię się na podstawowych obiektach konstrukcyjnych. Kłania się wzorzec fabryki abstrakcyjnej.

public interface IRandomizerBuilder
{
   IRandomizer ConstructDeafultRandomizer();

   IRandomizer ConstructRandomizer(int seed);
}

Czytaj dalej Konstruowanie obiektów IRandomizer oraz testy (wydajności)