Building my own RPI-based Bartop Arcade Cabinet

Building my own RPI-based Bartop Arcade Cabinet

One of my pet project this summer was to build a bartop arcade cabinet. I had some rpi400 laying around, which are rpi4 imbedded in a keyboard. The idea of always having a keyboard handy for the arcade cabinet sounded like a great feature, and to access it, I had to find a way to easily open the cabinet.

That’s why there’s hinges in from of the control!

All in all, building this was fun, and I decided to use Batocera.linux as the OS. It turn out to be the easiest one and most complete one, as well as the fastest one, based on my tests.

Main goal was to load MAME arcade games (tetris, pac man, Super Street Fighter 2). But I ended up putting Mario Kart N64, and it actually runs pretty good if we set resolution to 640×480 for that game.

There’s still one bug going on with Batocera – after a while we must reboot the Arcade since there seems to be a memory leak somewhere (developers are aware).

In the box, there’s

  • rpi400
  • Old 19 inches 4:3 monitor
  • 2 set of generic dragon arcade USB controllers
  • HDMI to VGA active adapter (powered)
  • power bar outlet (re-wired to a on/off switch in the back)
  • Altec lansing speakers
Arcade Bartop Cabinet (no stickers)

I thought it might be interesting to should you various stages of the build, in case you are looking for some inspirations:

Initial frame
Hinges for the bartop
Stained, ready to assemble!

During the whole configuration, I had a problem. RetroPie was not able to output sound properly, and Batocera was not able to connect to WiFi. It turned out this was caused by an insufficient power in the rpi.

Lesson 1: avoid a USB sound card if you can. It draws a lot of power that can interfere with the Wifi & Bluetooth module (which is what happened to me). If you do that, try to get one that can draw its power from somewhere else. I prefer rely on the HDMI sound output.

Lesson 2: if you use an old monitor, get an Active HDMI to VGA adapter. These adapters will usually include an audio output (which solves above problem). If you use a passive adapter, the chip relies on the power provided by HDMI, which may result in black screen flickers in some games. Using an active adapter fixed the problem for me.

This is a very different topic than what I usually post, but I felt like a good place to share this!

Did you ever build an Arcade cabinet?

Site Transition

My wife and I decided to stop hosting web sites as it was more a hobbie than anything. After our last handover, we downgraded our package with our hosting provider, and now, unfotunately, my prototype framework https://dev.michel-roberge.com is down.

I will try to find a way to get it back online; but that might take a while. And we’ll need to re-train the tic-tac-toe AI…

STARLIMS REST API & POSTMAN – Production Mode

Alright folks! If you’ve been playing with the new STARLIMS REST API and tried production mode, perhaps you’ve run into all kind of problems providing the correct SL-API-Signature header. You may wonder “but how do I generate this?” – even following STARLIMS’s c# example may yield unexpected 401 results.

At least, it did for me.

I was able to figure it out by looking at the code that reconstructs the signature on STARLIMS side, and here’s a snippet of code that works in POSTMAN as a pre-request code:

// required for the hash part. You don't need to install anything, it is included in POSTMAN
var CryptoJS = require("crypto-js");

// get data required for API signature
const dateNow = new Date().toISOString();
// thhis is the API secret found in STARLIMS key management
const privateKey = pm.environment.get('SL-API-secret');
// this is the API access key found in STARLIMS key management
const accessKey = pm.environment.get('SL-API-Auth');
// in my case, I have a {{url}} variable, but this should be the full URL to your API endpoint
const url = pm.environment.get('url') + request.url.substring(8);
const method = request.method;
// I am not using api methods, but if you are, this should be set
const apiMethod = "";

var body = "";
if (pm.request.body.raw){
    body = pm.request.body.raw;
}

// this is  the reconstruction part - the text used for signature
const signatureBase = `${url}\n${method}\n${accessKey}\n${apiMethod}\n${dateNow}\n${body}`;

// encrype signature
var data = CryptoJS.enc.Utf8.parse(signatureBase);
const hash = CryptoJS.HmacSHA256(data, privateKey);
const encodedHash = encodeURIComponent(CryptoJS.enc.Base64.stringify(hash));

// set global variables used in header
pm.globals.set("SL-API-Timestamp", dateNow);
pm.globals.set("SL-API-Signature", encodedHash);

One point of interest – if it still is not working, and if you can’t figure out why, an undocumented STARLIMS feature is to add this application setting in the web.config to view more info:

<add key="RestApi_LogLevel" value="Debug" />

I hope this helps you use the new REST API provided by STARLIMS!

REST API Cloud

STARLIMS REST API – Add and Route your own endpoints

With the version 12 technology platform, STARLIMS offers a new REST API engine. It is really great – until you want to enhance it and add your own endpoints. That’s where it gets … complicated. Well – not so much – if you know where to start. Nothing here is hidden information, it is all written in the technology release documentation; just not easily applied.

If you read the doc, you’ve read something like this:

Routing maps incoming HTTP API requests to their implementation. If you are a Core Product team, you must implement routing in pre-defined Server Script API_Helper.RestApiRouter; if you are a Professional Services or Customer team, you must implement routing in pre-defined Server Script API_Helper_Custom.RestApiRouter (which you need to create, if it doesn’t exist).

STARLIMS Technology Platform Documentation
09-016-00-02 REV AB

That section is accessible using the /building_rest_api.html Url of the platform documentation.

It is really good, and it works, and everything listed is appropriate. I would only add 2 points for your sanity.

1- handle your routes in a different way than what STARLIMS suggest. Their example is very simple, but you’ll want to have something scalable / reusable. I went with a single function and nested hashtables. By default, the custom routing needs a Route method. To “store” the route, I’ll also add a private getRoutes method. In the future, we’ll only add entries in the getRoutes, which will simplify our life.

:PROCEDURE getRoutes;

	/* 
		structure is:
		
		hashTable of version
			hashTable of of service
				hashTable of entity
	;
	:DECLARE hApiVersions;
	
	/* all route definition should be in lowercase;
	
	/* store API Verions at 1st htable level;
	hApiVersions := LimsNetConnect("", "System.Collections.Hashtable");
	hApiVersions["v1"] := LimsNetConnect("", "System.Collections.Hashtable");
	hApiVersions["v2"] := LimsNetConnect("", "System.Collections.Hashtable");
	
	/* store each service within the proper version;
	hApiVersions["v1"]["examples"] := LimsNetConnect("", "System.Collections.Hashtable");
	/* then store each endpoint per entity;
	hApiVersions["v1"]["examples"]["simple"] := "API_Examples_v1.Simple";

	/* store each service within the proper version;
	hApiVersions["v1"]["system"] := LimsNetConnect("", "System.Collections.Hashtable");
	hApiVersions["v1"]["system"]["status"] := "API_CustomSystem_v1.status";
	
	/* process-locks endpoints;
	hApiVersions["v1"]["process-locks"] := LimsNetConnect("", "System.Collections.Hashtable");
	hApiVersions["v1"]["process-locks"]["process"] := "API_ProcessLocks_v1.Process";
	
	/* user-management endpoints;
	hApiVersions["v1"]["user-management"] := LimsNetConnect("", "System.Collections.Hashtable");
	hApiVersions["v1"]["user-management"]["user-session"] := "API_UserManagement_v1.UserSession";
	
	hApiVersions["v1"]["sqs"] := LimsNetConnect("", "System.Collections.Hashtable");
	hApiVersions["v1"]["sqs"]["message-queue"] := "API_SQS_v1.message";

	hApiVersions["v1"]["load"] := LimsNetConnect("", "System.Collections.Hashtable");
	hApiVersions["v1"]["load"]["encrypt"] := "API_Load_v1.encrypt";
	hApiVersions["v1"]["load"]["origrec"] := "API_Load_v1.origrec";

	:RETURN hApiVersions;
:ENDPROC;


:PROCEDURE Route;
	:PARAMETERS routingInfo;
	
	/* 	routingInfo
			.Version : string - e.g. "v1"
			.Service : string - e.g. "folderlogin"
			.Entity : string - e.g. "sample";
	
	:DECLARE hRoutesDef, sVersion, sService, sEntity;
	
	hRoutesDef := Me:getRoutes();
	
	/* remove case route;
	sVersion := Lower(routingInfo:Version);
	sService := Lower(routingInfo:Service);
	sEntity := Lower(routingInfo:Entity);
	
	:IF !Empty(hRoutesDef[sVersion]);
		:IF !Empty(hRoutesDef[sVersion][sService]);
			:RETURN hRoutesDef[sVersion][sService][sEntity];
		:ENDIF;
	:ENDIF;
	
	:RETURN "";
	
:ENDPROC;

When you need to add new routes, all you do is add new lines to the getRoutes method, the logic in the Route method is static and shouldn’t change. Then, you create the corresponding categories and scripts to actually run your logic, and you’re set.

Of course, you can build your own mechanism – it is by no mean the best one; but I do find it to be easier to manage than STARLIMS’ suggestion.

Now, I know: you might be tempted to write a generic data-driven routing. I was tempted to do it. In the end, it is a balance between convenience and security. If you let it be data-driven, you loose control on what can be routed. Someone may modify the route to, let’s say, get result, to instead return all user information, and you wouldn’t know. If it’s in the code, then you’ll know. So – although it is not as convenient, don’t get your routes handled by the database. It would also add extra load on the database. So – no good reasons other than convenience, really.

2- properly document your APIs. Heck, document your APIs before you implement them! I recommend https://swagger.io/ to generate some .yaml files. Trust me: whoever will be consuming your API will thank you!

All in all, I think the STARLIMS REST API really brings the system to an all new level. Theoretically, one could build a full UI stack using React or Angular and just consume the API to run the system on a new front end.

Or one could expose data endpoints for pipelines to maintain a data mart.

Or anything. At this point, your creativity is the limiting factor. Do you have great ideas for use cases?

JMeter + STARLIMS for load testing

JMeter + STARLIMS for load testing

JMeter is a load / stress tool built in Java which allows you to simulate multiple user connections to your system and monitor how the application & hardware response to heavy load.

In STARLIMS, I find it is a very good tool for performance optimization. One can detect redundant calls, chatty pieces of code and identify bottlenecks, even when running with a single user.

As a bonus, Microsoft has a preview version of load tests based on JMeter, which can be integrated to your CI/CD process!

So, in this article, my goal is to help you get started – once setup, it’s very easy to do.

I will proceed with the following assumptions:

  • You know your way around STARLIMS
  • You have some scripting knowledge
  • Your STARLIMS version is 12.1 or + (I leverage the REST API introduced with 12.1. It is possible to do differently, but that will be out of scope)
  • Xfd is the most difficult technology for this. Therefore, that’s what I will tackle. If you are running on HTML, it will be just easier, good for you!

Environment Setup

On your local PC

  • Install Java Runtime – you might have to reboot. Don’t worry, I’m not going anywhere!
  • Download JMeter and extract it somewhere (remember where!)
  • Make sure you have access to setting up a Manual Proxy. This can be tricky and may require your administrators to enable this for you. What you’ll want is to be able to toggle it like this (don’t enable it just yet! Just verify you can):
Proxy Setup

On your STARLIMS Server

  • Make it available through HTTP. Yes, you have read properly, HTTP. Not HTTPS. I think it can work HTTPS, but I ran into too much problems and found out HTTP is easiest. This is to simplify traffic recording when recording a scenario for re-processing.
  • Create your load users. If you expect to run 100 simultaneous users, then let’s create 100! What I did is create users named LOADUSER001 to LOADUSER250 (so I would have 250 users) and have their password to something silly like #LoadUser001 to #LoadUser250. Like I said – don’t do this if there’s any sensitive data in your system.
  • To help you, here’s a script to generate the users:
:RETURN SubmitToBatch("LoadTestPrep.UserCreator.ASync", { 100 });

:PROCEDURE Async;
:PARAMETERS nNumberOfUsers;
:DEFAULT nNumberOfUsers, 1;

:DECLARE sUserName, sOldPassword, sNewPassword, i, nOrigrec, oNewUser, aUserDetails, pwEncOld, pwEncNew;
resp := "nothing yet";
:FOR i := 1 :TO nNumberOfUsers;	
	oNewUser := CreateUdObject();
	oNewUser:USRNAM := "LOADUSER" + StrZero(i, 4,0);
	oNewUser:FULLNAME := "Load User " + StrZero(i, 4,0);
	oNewUser:JOBDESCRIPTION := "Load Test";
	oNewUser:EMAIL := "user" + StrZero(i,4,0) + "@dummy.com";
	oNewUser:LANGID := "ENG";
	oNewUser:POWERUSER := "Y";
	oNewUser:TREEAUTH := { "L" };
	oNewUser:RASCLIENTID := "Internal";
	oNewUser:DEPTLIST := "Changzhou";
	oNewUser:QUESTION_ID := 1;
    oNewUser:ANSWER := "1234";
    oNewUser:CONFIRMANSWER := "1234";
    oNewUser:PIN := "1234";	
	oNewUser:Id := "UserManagement.newUserModel-" + LimsString(i);
	
	UsrMes("Processing " + oNewUser:USRNAM);
	resp := ExecFunction("UserManagement.createNewUser", { oNewUser });
	
	resp := "User " + oNewUser:USRNAM + " does not exist";
	nOrigrec := LSearch("select ORIGREC from USERS where USRNAM = ?", 0, "DATABASE", { oNewUser:USRNAM });
	:IF nOrigrec > 0;
		aUserDetails := {
			{
				"TREEAUTH",
				{
					"L"
				},
				"S",
				{"L"}
			},
			{
				"SHOWERRORDETAILS",
				"Y",
				"S",
				"N"
			},
			{
				"STATUS",
				"Active",
				"S",
				"Pending"
			}
		};
		
		pwEncOld := "#LoadUsr" + StrZero(i, 4, 0);
		pwEncNew := "#LoadUser" + StrZero(i, 4, 0);
		ExecFunction("UserManagement.saveUserDetails", { NIL, "USERS", aUserDetails, nOrigrec });
		ExecFunction("Security_Module.ChangePassword", { oNewUser:USRNAM, "NEW", pwEncOld });
		ExecFunction("Security_Module.ChangePassword", { oNewUser:USRNAM, "", pwEncNew });
		resp := ExecFunction("UserManagement.updateHTMLUserSecurityInformation", {NIL,"USERS",{{"PWEXPD",Now():AddYears(100),"D",Now()}},nOrigrec,{}});
	:ENDIF;
:NEXT;

UsrMes( "Done" );
:ENDPROC;

You will need to test the above, on my system it worked fine (haha!) but setting password and security is not always working as expected in STARLIMS; so do not despair – just be patient.

  • Edit the web.config file. I will presume you know which one and how to achieve that. You need to change / add the following appSetting to false: <appSetting name="TamperProofCommunication" value="false" />
  • Add Endpoint to Encrypt function. That’s really the tricky part. In both XFD and HTML, STARLIMS “masks” the username and password when putting it in the payload for authentication, to prevent sending in clear text. But this encryption is significant; it is part of .NET and not easily integrated to JMeter… Unless it becomes a REST API endpoint!.
  • So, in a nutshell, the trick is to create a new API Endpoint that receives a string and a key, and call the EncryptData(text, key) function, and return the encrypted string. I will not stress it enough: do – not – enable – this – on – a -system – with – sensitive – data. And make sure you will only use load testing users. If you do so, you’re fine.

This is the code of the REST API method to expose from STARLIMS:

:PROCEDURE GET;
:PARAMETERS payload;
:DECLARE response;

response := CreateUdObject();
response:StatusCode := Me:HTTP_SUCCESS;
response:Response := CreateUdObject();
:IF payload:IsProperty("text") .and. payload:IsProperty("pw");
    :DECLARE t, p, secret;
    t := limsString(payload:text);
    p := limsString(payload:pw);
    secret := EncryptData(t, p);
    response:Response:message := secret;
:ELSE;
    response:Response:message := "Missing data";
    response:StatusCode := 500;
:ENDIF;

:RETURN response;
:ENDPROC;

Since it gets exposed as a REST API, the concept is that at the beginning of the load test, for every user, we call this with the username and the password to get the encrypted version of each, which allows us to tap into STARLIMS cookie / session mechanism. Magic!

Now, we are kind of ready – assuming you’ve followed along and got everything setup properly and were able to test your API with POSTMAN or something like that. Before moving on, let’s take a look at a typical load test plan in JMeter:

Typical setup for a single scenario

The idea is we want each user (thread) to run in its own “session”. And we want each session to be for a different user. My scenarios always involve a user login into STARLIMS once (to create a session) and the to loop on running the scenario (for example, one scenario could be aboout creating folders, another scenario about entering results, etc.) . I will leave to you the details of the test plans, but the idea is you first need to login the system, then do something.

At the level of the test plan, let’s add user-defined variables – in my case, this is only so I can switch STARLIMS instances later on (I strongly recommend you do that!):

User-defined Variables

Always at the level of the test plan, add a counter:

User Counter

This will be the magic for multiple users. Note the number format – this has to match your user naming convention, otherwise, good luck!

Now, let’s have our user login STARLIMS.

  1. Add a Transaction Controller to the Thread Group. I renamed this one “System Login” – call it what you want.
  2. On your new transaction controller, add a Sampler > HTTP Request, which will be our call to the REST API
HTTP Request – REST API for Encrypt method

As you can see, I did a few more things than just call the API. If we break it down, I have a pre-processor “Initialize User Variables”, a HTTP Header Manager, and a JSON Extractor. Let’s look at each of these.

Pre-processor – Initialize User Variables (Beanshell preprocessor)

This will run before this call is made – every time this call is made! This is where we initialize more variables we can use in the thread.

currentUser = "LOADUSER" + "${un}";
s = "000" + "${un}";
v = s.split("");
s = s.substring(v.length - 4);
currentPw = "#LoadUser" + s;
vars.put("currentUser", currentUser);
vars.put("currentPW", currentPw);
vars.put("startFolderNo", "LT22-000" + "${un}");
log.info("Current User: " + currentUser);

This will initialize the currentUser and currentPW variables we can reuse later on. Since this is a pre-processor, it means the request can reference them:

Now, let’s look at the HTTP Header Manager:

HTTP Header Manager – System Login

Pretty simple – if you have STARLIMS 12.1 or +, you just need to get yourself an API key in the RestApi application. Otherwise, this whole part might have to be adjusted according to your prefered way of calling STARLIMS. But, long story short, SL-API-Auth is the header you want, and the value should be your STARLIMS secret API key.

Finally, this API will return something (the encoded string). So we need to store it in yet another variable! Simple enough, we use a post-processor JSON extractor:

JSON Extractor

What did we just do? Here’s a breakdown:

  1. Initialized a user name and password in variables
  2. Constructed a HTTP request with these 2 variables
  3. Called the REST API with our secret STARLIMS key using this request
  4. Parsed the JSON response into another variable

If you have set the thread group to simulate 10 users, then you’ll have LOADUSER001 to LOADUSER010 initialized. This is the pattern to learn. This is what we’ll be doing all along.

Wait. How did you know what to call afterward?

Great question! That’s where the proxy gets into play. Now, we don’t want to go around and guess all the calls, and, although I like Fiddler, I think it would be very complicated to use.

In a nutshell, this is what we’ll do:

  1. We’ll add a Recording Controller to our Thread Group
    1. Right-click on your Thread Group > Add > Logic Controller > Recording Controller
  2. We’ll add a Test Script Recorder to our Test Plan
    1. Right-click on your Test Plan > Add > Non-Test Elements > HTTP(S) Test Script Recorder
    2. Change the Target Controller to your recording Controller above, so you know where the calls will go
  3. We’ll activate the proxy (bye bye internet!)
    1. Open Windows Settings
    2. Look for Proxy
    3. Change Manual Proxy > Use a proxy server to on.
    4. Local Address = http://localhost
    5. Port = 8888
    6. Click Save! I didn’t realize at first there was a save button for this…
  4. We’ll start the Test Script Recorder
Test Script Recorder
  1. We’ll peform our action in STARLIMS
    1. WARNING: A good practice is to change the value of Transaction name in the Recorder Transactions Control as you progress. What I typically do is put SYSTEM_LOGIN while I launch STARLIMS. Then SYSTEM_LOGIN/VALIDATE when I enter credentials, then SYSTEM_LOGIN/OK when I click OK, etc.
    2. If all works well, you should see items being added to your Transaction Recorder.
  2. We’ll stop the Test Script Recorder – just click on the big red Stop
  3. We’ll deactivate the proxy (yay!) – just toggle it off.

You should have something like this in your recorder:

Recorded HTTP Requests

If, like me, you let your Outlook opened, you will have all kind of unrelated HTTP calls. Just select these and delete them. You should be left with something like this:

After 1st cleanup

Now, let’s understand what happened here. We recorded all the calls to STARLIMS. If you wish, you can remove the GetImageById lines – typically, this should not have any performance impact as these should be cached. But heh, that’s your call.

Let’s look at the 1st request:

1st HTTP Request

Interestingly enough, we can see the Protocol is http, and the Server Name is our STARLIMS server. If you created user defined variables, then you can just clean these 2 fields up (make them empty). We can default them at the test plan level (later on). But if you do that, you must do it for all requests! So, let’s not do this (just yet). Let’s leave it as is.

Now, what we want, is to re-run this so we can have actual data to work with and to make our script dynamic. But we need to record all the requests sent and received.

Right-click on your Thread Group > Add > Listener > View Results Tree

I find this listener to be the best for this activity.

Now, let’s run this “as is” clicking the play button

play

The beauty here is you can get the data sent to STARLIMS as well as the responses, allowing us to understand how everything is connected. Let’s take a look at the Authentication.GetUserInfo – that’s our first challenge:

View Results Tree

If you look at the Request Body, you’ll see your user name (which you used to login), as well as a 2nd very strange parameter that looks like the above highlighted string in kind of pink. Now, when we log into STARLIMS, we must send that string, which, essentially, is the password hash based on the user name (one-way encoding). So the question is: how do we get this? This is where our REST API, which we prepared earlier, comes into play!

Hook user variables to payload

With this, you can do everything now! Well, as far as load testing is concerned, it can at least get you started!

Earlier, I mentioned you shouldn’t leave your Server name / path / protocol in there. Indeed, in my screenshot above, you can see it’s all empty. This is because I added a HTTP Request Default to my test plan:

HTTP Request Default

You’ll also want a HTTP Cookie Manager. This one doesn’t need configuration as far as I know; but it must exist so cookies are carried over.

CONCLUSION

What?? Conclusion already? But we were just getting started! Well, don’t worry. I have done a little bit more than just that, and I am including it with this post.

You can get a semi-working test plan here.

You will need to figure out a few things, like some of the APIs I use and some forms/scripts that you won’t have. But this should give you a very good overview of how it works and how it is all tied in together.

As a side note, the reason I got involved into this was caused by Microsoft adding JMeter as part of the tools in their load test preview!

Hope you find good use to this!

STARLIMS + Azure Blob containers through SAS

STARLIMS + Azure Blob containers through SAS

Here is a funny story.

Here I was trying, with the infra team, to access my Azure container through SAS. STARLIMS has a built-in Azure container support, but it relies on a connection string with account information and all. But, like most Azure customers, that is not our reality. We use shared containers, so we need a SAS token… Which is not supported to configure as a STARLIMS connection string.

This means that next step will be any other web service consumption instead of direct containers access. Is it complex? Less than I expected!

Step 1: let’s get a SAS token!

Now, finding the said token is not always obvious, but mine looked something like this:

sv=nnnn-nn-nn&sr=c&si=CompanyaccessPolicy&sig=someuglystring

Hopefully, yours too! In the Azure Container tool, look for the “Shared Access Signature”, it’s the same thing.

Step 2 – integrate the Azure API!

Now, how do we put files there? The connection string and tutorials on STARLIMS will not help… But the web services will! All we need to do is write a UploadToAzureBlob procedure and a DownloadFromAzureBlob procedure (both in SSL) and that will do the trick:

:PROCEDURE UploadToAzureBlob;
:PARAMETERS content, fileName;

:DECLARE    sasToken, storageAccount, containerName, method, sampleContent,
            contentLength, requestUri, oWebService, oClient, oRequest, innerRequest,
            stream, resp, encoding;

encoding := LimsNetConnect("", "System.Text.Encoding",, .T.);


sasToken := "yourSASToken";
storageAccount := "yourAccountName";
containerName := "yourContainerName";
blobName := fileName; /* can be something like folder/subfolder/name.ext ;

method := "PUT";
sampleContent := content;
contentLength := encoding:UTF8:GetByteCount(sampleContent);

requestUri := Replace(Replace(Replace(Replace(
                    "https://{storageAccount}.blob.core.windows.net/{containerName}/{blobName}?{sasToken}",
                        "{storageAccount}", storageAccount),
                        "{containerName}", containerName),
                        "{blobName}", blobName),
                        "{sasToken}", sasToken);

oWebService := WebServices{};
oClient := oWebService:CreateHttpClient();
oRequest := oClient:CreateHttpRequest(requestUri);

oRequest:Method := method;
oRequest:ContentType := "text/plain; charset=UTF-8";
oRequest:ContentLength := contentLength;

innerRequest := DoProc("GetInnerRequest", { oRequest });
innerRequest:Headers:Add("x-ms-blob-type", "BlockBlob");
stream := innerRequest:GetRequestStream();
stream:Write(encoding:UTF8:GetBytes(sampleContent), 0, contentLength);

resp := innerRequest:GetResponse();
:RETURN resp:StatusCode;

:ENDPROC;

And then you create the GetFromAzureBlob to retrieve the file in a similar fashion:

:PROCEDURE GetFromAzureBlob;
:PARAMETERS remoteFile;

:DECLARE    sasToken, storageAccount, containerName, method, sampleContent,
            contentLength, requestUri, oWebService, oClient, oRequest, innerRequest,
            stream, resp, encoding, sTmpFileName;

encoding := LimsNetConnect("", "System.Text.Encoding",, .T.);


sasToken := "yourSAStoken";
storageAccount := "yourAzureAccount";
containerName := "yourAzureContainer";
blobName := remoteFile;

method := "GET";

requestUri := Replace(Replace(Replace(Replace(
                    "https://{storageAccount}.blob.core.windows.net/{containerName}/{blobName}?{sasToken}",
                        "{storageAccount}", storageAccount),
                        "{containerName}", containerName),
                        "{blobName}", blobName),
                        "{sasToken}", sasToken);

oWebService := WebServices{};
oClient := oWebService:CreateHttpClient();
oRequest := oClient:CreateHttpRequest(requestUri);

oRequest:Method := method;
oRequest:ContentType := "text/plain; charset=UTF-8";
resp := oClient:GetResponse(oRequest);
sTmpFileName := GlbDefaultTempDirectory + CreateGuid() + ".tmp";
resp:SaveValueToFile(sTmpFileName);
:RETURN sTmpFileName;

:ENDPROC;

Step 3- use it

As simple as that, you got yourself an upload and download to azure containers.

Conclusion

As you can see, as usual, this was quite easy! One just needs the correct information. Next step will be to see what more can containers bring to your STARLIMS installation.

Hope this can come in handy sometime to someone!


Watermark your PDF in STARLIMS

I was working with the team on some reports requirements, and we started to get some requests for Watermarks on reports. Some of the requests were to get the same report with a watermark: same time stamp, same data, same printed by, etc.

One option would have been to have the Crystal reports template to support watermarks (using parameters for example), but then it meant implementing the support in every template.

After thinking about it, I thought “Heh! We always print from server, using PDF! There must be some way to put a watermark on a PDF, right?

Right!

Easy said, but not easily done. But I recalled that Abbott Informatics is providing a PDFSharp.dll in the bin folder of the application folder. The dlls in there are typically used by the server runtime and not necessarily accessed by user code… But I did some research, and it should be possible to use that DLL to do exactly what I wanted!

STARLIMS has a few neat functions like LimsNetConnect() and LimsNetCast() which allows us to use DLL directly.

So I started by trying to load a PDF:

document := LimsNetConnect("PDFSharp.dll", "PdfSharp.Pdf.IO.PdfReader",, .T.):Open(sPDF);

Unfortunately, PDFSharp.dll is not loaded/accessible directly by the server side scripting, so I got an error.

Next step: copy the DLL to the Components directory!

Try again: success!

Finally, all I had to do is convert c# code to STARLIMS Scripting Language code (SSL) and magic happened!

Here’s the final code below.

Parameters are the “input” PDF file and the Watermark Text. Returns the Watermarked PDF.

:PARAMETERS sPDF, sWatermark;
:DEFAULT sPDF, "c:\temp\TestWatermark.pdf";
:DEFAULT sWatermark, "COPY";
:DECLARE sOut, sWatermark;
:DECLARE gfx, prependOption, document, page, font, size, format, brush, color, 
math, width, height, ratio;

/* Define the output file name. For now, let's just insert _Watermarked at the end ;
sOut:= Left(sPDF, Rat(".", sPDF) -1) + "_Watermarked.PDF";

/* We will need to do math to rotate the text, so let's get ready!;
math := LimsNetConnect("", "System.Math",, .T.);

/* LimsNetCast allows us to get a value from an enum. That's about the simplest way to get to this;
prependOption := LimsNetCast("Prepend", "enum:PdfSharp.Drawing.XGraphicsPdfPageOptions");
fontOption := LimsNetCast("BoldItalic", "enum:PdfSharp.Drawing.XFontStyle");
nearOption := LimsNetCast("Near", "enum:PdfSharp.Drawing.XStringAlignment");

/* create necessary objects to get started, like the document, the font of the watermark, and the color;
document := LimsNetConnect("PDFSharp.dll", "PdfSharp.Pdf.IO.PdfReader",, .T.):Open(sPDF);
font := LimsNetConnect("PDFSharp.dll", "PdfSharp.Drawing.XFont", {"Times New Roman", 100, fontOption});
color := LimsNetConnect("PDFSharp.dll", "PdfSharp.Drawing.XColor",, .T.):FromArgb(64, 0, 0, 0);

/* then generate the watermark for every pages! ;
:FOR i:=1 :TO document:Pages:Count;

	page := document:Pages[i - 1];

    /* graphic canvas will be the page, and we will put the watermark "under" the text (prepend);
	gfx := LimsNetConnect("PDFSharp.dll", "PdfSharp.Drawing.XGraphics",,.T.):FromPdfPage(page, prependOption);
	size := gfx:MeasureString(sWatermark, font);
	width := page:Width:Value;
	height := page:Height:Value;
	ratio := width / height;

    /* Trigonometry anyone? This is to rotate our text;
	gfx:TranslateTransform(width/2, height/2);
	gfx:RotateTransform(-math:Atan(ratio) * 180 / math:PI);
	gfx:TranslateTransform(-width/2, -height/ 2);

    /* Create a string format ;
        format := LimsNetConnect("PDFSharp.dll", "PdfSharp.Drawing.XStringFormat");
        format:Alignment = nearOption;
        format:LineAlignment = nearOption;

    /* Create a dimmed red brush ;
        brush := LimsNetConnect("PDFSharp.dll", "PdfSharp.Drawing.XSolidBrush", { color });

	point := LimsNetConnect("PDFSharp.dll", "PdfSharp.Drawing.XPoint", { (width - size:Width) / 2, (height - size:Height) / 2});
	
    /* Draw the string on page; 
        gfx:DrawString(sWatermark, font, brush, point, format);
:NEXT;

/* that's it, we save! ;
document:Save(sOut);

:RETURN sOut;

Turns out this is quite fast! If needed, you could then expose this script as a web service (REST anyone?) and you turn your STARLIMS into a PDF Watermark Service.

Uh. What a great idea I just got here 🙂

STARLIMS Data Connector v1 Released

Finally, I think it works! You can go to google data studio and create a STARLIMS Data Source!

Search for STARLIMS; if I got it right, it should come out in the partner section.

Follow the instructions, and you should be able to get it running!

A few things to note:

  1. the predefined queries work, except for COC, which I kind of ditched for now.
  2. the QBE work! Note that I don’t (yet) apply the default QBE filters, so don’t just go and pull up all your data. That will be hard on all servers.
  3. You can create many connections; so you technically could create one for Folders, one for Samples, one for Results, one for your favourite QBE, one for your products, etc… And blend all of them! Magical!
  4. Finally, and most importantly: I – do – not – cache – the – data. Not yet. I will eventually look at doing that, but not now. Therefore, each time you run this, you actually query the database server. Be careful.
  5. I do this on my own time for fun. It’s just fun. Good if it helps you, but don’t hold me responsible if you mis-use this!

That’s it for now! Remember, this is a project for fun! Contact me if you want to know more, of if you wish me to consider adding features to this.

Cheers!

Michel

STARLIMS + Google Data Connector = lazy lab data

Yup; almost a full year without a post: shame on me. But here I am, with something cool for you STARLIMS users/developers/adminstrators!

Recently, I was looking for a good reporting tool – thinking to replace SAP’s Crystal Reports – and somehow stumbled upon Google Data. Google Data is kind of a mix in between Microsoft Power BI, Tableau, QLik and any and all Data visualization/analytics/findagoodbuzzword tools.

… Except it is free. Kind of.

DISCLAIMER: This project is in progress and I do this on my free time at home. I do not want to release the google script code just yet, but I do want to share progress and see where it takes me. Please contact me directly if you wish to know more and want to use this. Right now, this is closer to prototype than anything else.

There are good tutorials on writing your own Data Connectors. Google is really working hard to get developers on board. Of course, since this is not a very well known tool yet, it is still quite tricky to get going, even when following the said tutorials.

But chances are that if you read this, you know me. And you know this is the kind of things that intrigues me. Challenge! New playground! Undiscovered lands! So here I am: I have given it a try, and I will share my work here. Freely. Eventually, who knows; maybe someone somewhere will use it!

About STARLIMS…

Given the nature of STARLIMS, you can kind of reverse-engineer the code so I will, aside from giving you here a (somewhat) working solution, explain what I have done and share the connector’s code so you can make it better.

To know more about STARLIMS, visit their web site: https://www.informatics.abbott/

To learn how to build your own Google Data Connector, head to https://developers.google.com/datastudio/connector/get-started

That’s where I started.

Google Data Connector

Now, what’s behind a Google Data Connector? Basically, there are 4 functions really:

  1. Authentication. For STARLIMS, I am using the Username and Password approach. I pass these in headers, so if your connection is secured (HTTPS) then you’re good. If you use normal STARLIMS authentication, then this will work for you. Where I work, we use AD, and it works fine too.
  2. getSchema. This is critical – we need to provide the connector, at the beginning, the full schema of the data. Given the size of STARLIMS, I am opting for either
    1. Predefined entities (Folders, Orders, Ordtask or COC)
    2. QBE Templates (code-based)

I think I can manage the schemas from these quite easily. I need to know field names, labels and types, whether they are measures or dimensions; that’s it.

3. getData. This is tricky. This will pass a sub-set of the required fields, and the data must match the subset of these fields. It can be done, but one needs to create the right SQL statements based on the schema.

4. getConfig. This ties everything together. The way I built it is to define the URL of your connection (aka STARLIMS URL) and whether you will use a specific QBE or a predefined entity. When you first create a connection, it will require username and password. I am not storing these, google is; it is up to you then to decide if you trust google or not with the data…

And that’s it, really! This is the kind of charts you can do in a few clicks:

Of course, these are very simple / silly examples. But it is to show that you can create viz easily, and good looking ones at that!

My idea / goal is to extend my connector and code so you could create dynamic dashboards on your STARLIMS Data using existing QBE templates you spent hours / days to optimize and clean. I think this will be definitively worth it.

You can get started here. I will update the STARLIMS Code and enhance the connector. For now, all I do is a simple query on FOLDERS and ORDTASK to kind of have a proof of concept; it actually works quite well!

Let’s see where this gets me in the future!

Cheers,

Michel

P.S. Remember: this is work in progress and not production-ready!