/ Development  

Get a grip on your organization ID

Hi there AppWorks fans,

Welcome to a new installment of AppWorks tips.

Ever wondered what the ID of your precious organization would be? No! Why would I care? Well, that’s exactly the mindset I want you to have. Maybe you never stood still about what kind of magic is happening in the backend database of our beloved platform, but this all starts with the ID of an organization. This post extends your knowledge about it…


Let get right into it…

First a valid question to start with…Why do I care?

  • For “administrators”, it’s important to understand your back-end structure and what database tables belong to the specific organization.
  • For “development”, it’s important to understand how data storage works for a correct retrieval in your solution.
  • For “testers”, it’s important to…hmmm…skip this one, they just need to retest from scratch (automagically).
  • For “end-users”, it’s important to…Well, they don’t give a f@#$!, but they do expect it all works in runtime (even after a migration).

So, time to dive into the way I found my organization ID in a team of 5 developers working on one solution having their own organization each! This means each organization has an equal solution applied and connected to one source control location. For this post we start with a set of three organizations behind this list of URLs:

  • http://192.168.56.104:8080/home/awp_tips_dev1
  • http://192.168.56.104:8080/home/awp_tips_dev2
  • http://192.168.56.104:8080/home/awp_tips_dev3

You can create new organizations in the ‘Organization Manager’ artifact in the ‘System’ space. In this configuration I also apply my awdev account as an ‘Administrator’.

orgid_001

For me, awdev@awp is an account from OTDS which can only log in to an OTDS connected organization! ONLY, after logging in to the OTDS connected organization (Appworks Tips in my case), you can switch organizations from the top-right drop-down with a seamless authentication!…Isn’t that a nice trick with a ‘smell’!?

orgid_002

From the ‘User Manager’ artifact (for each new organization!), you can apply the ‘Developer’ role to your account to gain access to the ‘Workspace Documents’ artifact!

Hmmm…After a review, I see forgetting to mention the above steps are executed with the sysadmin account.


awp_tips_dev1

Once the organizations are in place, we dive into organization awp_tips_dev1; now with our awdev account. Here we start a new SVN-connected workspace, with a first generic project, and a basic folder structure like we learned from experience (incl. that namespace folder).

I used this input during the workspace creation wizard:

  • Workspace name: ws_orgid_svn
  • SVN URL (IP of my local machine): http://192.168.56.1/svn/AppWorksOrgId
  • Project name: prj_orgid
  • Package Owner: aw_tips
  • Product name: orgid

Learn about the SVN connection from these posts. I run VisualSVN server on my local machine (outside the AWP VM) with just one repository called AppWorksOrgId and one SVN account with name dev001; password admin.

This is my start point with an initial (only folder structure) commit to SVN:

orgid_003

From the ‘entities’ folder we prototype a claim entity with just one property clm_name. Make sure to publish it and create an instance in runtime…No fancy stuff here!

orgid_004

Don’t forget to commit this new entity to SVN (for our second and third organization)!


awp_tips_dev2

Now switch to the second organization awp_tips_dev2. We already assigned to ‘Developer’ role to the seamless account awdev. We dive directly into the ‘Workspace Documents’ artifact and create a new workspace with input like this:

  • Workspace name: ws_orgid_svn
  • SVN URL: http://192.168.56.1/svn/AppWorksOrgId

Because there is already a project in SVN, the new project will be based (checked out) on this information!

When done, you should have a similar project including that crafted claim entity. Do a publication and create an instance in runtime!


awp_tips_dev3

Well, that’s the same magic we did for awp_tips_dev2! 😅 Publish it, and create an instance in runtime.


A database view

With all the preparation, it’s time to have a look into the database with HeidiSQL. Filter the table view on claim and see three tables passing by:

orgid_005

The o in front of each table name is standing for “organization” with behind it the ID.

Now for that great question: Which table belongs to what organization!? To answer this question we have several approaches…

Brute force method

Do a select on each of the tables and see if you can find any matching result:

1
2
3
SELECT * FROM o3aw_tipsorgidclaim;
SELECT * FROM o4aw_tipsorgidclaim;
SELECT * FROM o5aw_tipsorgidclaim;

When you use equal test-data for each organization you can always create a new instance in runtime for the specific organization with a distinct claim name to find it back in the database!

This Stack Overflow post is also interesting. It explains how to find a specific value in all the tables of a database.

Nice approach, but we can do better…next one!


Query a “special” mapping table

What if I tell you (learned from experts in the field…thx!!) there is a “mapping” table available to query on…WHAT!? Have a look at this select statement:

SELECT s_organizationid, s_payload FROM s_elementdefinitions WHERE s_payload LIKE '%{"organizationName%';

With a result like this:

orgid_006

From a PostgreSQL database perspective it’s even possible to parse the JSON data with a nicer resulting query like this one:

1
2
3
4
5
6
7
SELECT
s_organizationid,
s_payload::json->'organizationName' as org_name
FROM s_elementdefinitions
WHERE
s_payload::json->'organizationName' IS NOT NULL AND
s_payload::json->'databaseProviderName' IS NULL;

This is specifically from PostgreSQL, but other databases have their own functions to parse JSON too!

Interesting query!…Especially with the experience using this information via the WS-AppServer connector! Is there more?…Ohw YEAH!…Continue the grind in the next section…


LDAP “destinationindicator”

Time to open the ‘LDAP Explorer’ artifact from any of the organizations (you require the ‘Administration’ role) to have a view on the ‘Destination indicator’ for the organization…Yes my friends, it denotes the same organization ID we’re talking about:

orgid_007

We gain knowledge by the minute here…Give me more! 😍


LDAP webservice call

Well, how about a service call from the ‘Web Service Interface Explorer’ artifact? Search for LDAP and get yourself a grip on the service call GetLDAPObject:

Request:

1
2
3
4
5
6
7
<SOAP:Envelope>
<SOAP:Body>
<GetLDAPObject>
<dn>o=awp_tips_dev1,cn=cordys,cn=defaultInst,o=mydomain.com</dn>
</GetLDAPObject>
</SOAP:Body>
</SOAP:Envelope>

Response:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<data>
<GetLDAPObjectResponse>
<tuple>
<old>
<entry dn="o=awp_tips_dev1,cn=cordys,cn=defaultInst,o=mydomain.com">
<description>
<string>awp_tips_dev1</string>
</description>
<destinationindicator>
<string>3</string>
</destinationindicator>
...
<o>
<string>awp_tips_dev1</string>
</o>
</entry>
</old>
</tuple>
</GetLDAPObjectResponse>
</data>

Boy-O-Boy! If you follow my posts, you also know we can eventually call this webservice from a BPM. The only thing to make it consumable from your solution is to add it as runtime reference to your project!

orgid_008


Well, it will not get any greater! A “DONE” with the nicest insights on retrieving the current organization ID in your situation. Now you also understand its importance when you start to ship your solution to other organizations…from DEV to TST for example! Keep on the grind on all the good AppWorks details explained on this blog-site; I see you in the next topic; Have a great week-end…

Don’t forget to subscribe to get updates on the activities happening on this site. Have you noticed the quiz where you find out if you are also “The AppWorks guy”?