{"id":117,"date":"2008-05-22T07:42:47","date_gmt":"2008-05-22T07:42:47","guid":{"rendered":"https:\/\/www.wapshere.com\/missmiis\/?p=117"},"modified":"2009-04-05T07:53:23","modified_gmt":"2009-04-05T07:53:23","slug":"uniqueness-check-against-the-metaverse","status":"publish","type":"post","link":"https:\/\/www.wapshere.com\/missmiis\/uniqueness-check-against-the-metaverse","title":{"rendered":"Uniqueness check against the Metaverse"},"content":{"rendered":"<p>ILM works on a single object at a time, so it can be a little tricky if you want to make a decision based on <em>other<\/em> objects in your metaverse. It is, however, perfectly possible (though, I believe, officially discouraged) to run SQL queries against the metaverse table from your extension code.<\/p>\n<p><!--more--><\/p>\n<p>My example is a fairly simple one: I create a uid based on first initial and surname, test for uniqueness, and if a match is found I append an incrementing index number until I get a unique uid.<\/p>\n<p>The code, in this case, is entered as an Import Flow Rule in the MAExtension of the importing MA. It&#8217;s pretty simplistic, the main point being to demonstrate the query of the metaverse table.<\/p>\n<p>Note the use of NOLOCK in the SQL query &#8211; this is essential as we are querying the metaverse table at the same time as synchronising to it.<\/p>\n<h4>Snippet\u00c2\u00a0<\/h4>\n<pre>Const DB_CONNECTION_STRING As String = \"Database=MicrosoftIdentityIntegrationServer;Data Source=localhost;Integrated Security=TRUE;;\"<\/pre>\n<pre>Public Sub MapAttributesForImport(ByVal FlowRuleName As String, ByVal csentry As CSEntry, ByVal mventry As MVEntry) Implements IMASynchronization.MapAttributesForImport\r\n\u00c2\u00a0 Select Case FlowRuleName\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Case \"import_uid\"\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim uid As String = \"\"\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim index As Integer\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim sqlQueryConnection As New SqlConnection(DB_CONNECTION_STRING)\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim rowReader As SqlDataReader\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim unique As Boolean = False<\/pre>\n<pre>\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If csentry(\"firstName\").IsPresent AndAlso csentry(\"lastName\").IsPresent Then\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0uid = csentry(\"firstName\").Value.Chars(0) &amp; csentry(\"lastName\").Value\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0index = 1<\/pre>\n<pre>\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0Do Until unique Or index &gt; 99\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim query As String = \"select count (*) from mms_metaverse with (NOLOCK) \" _\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 &amp; \"where object_type = 'person' and uid = '\" &amp; uid &amp; \"'\"\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Dim sqlQuery As New SqlCommand(query, sqlQueryConnection)\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0 sqlQueryConnection.Open()\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 rowReader = sqlQuery.ExecuteReader\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 rowReader.Read()\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If rowReader.GetInt32(0) &gt; 0 Then\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If index &gt; 1 Then\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 uid = uid.Replace((index - 1).ToString, \"\")\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End If\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 uid = uid &amp; index.ToString\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Else\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 unique = True\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0 End If\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 index = index + 1\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0  sqlQueryConnection.Close()\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0Loop<\/pre>\n<pre>\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If index &lt; 100 And uid &lt;&gt; \"\" Then\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 mventry(\"uid\").Value = uid\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0End If<\/pre>\n<pre>\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0End If\r\n\u00c2\u00a0\u00c2\u00a0End Select\r\nEnd Sub\u00c2\u00a0<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>ILM works on a single object at a time, so it can be a little tricky if you want to make a decision based on other objects in your metaverse. It is, however, perfectly possible (though, I believe, officially discouraged) to run SQL queries against the metaverse table from your extension code.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":[]},"categories":[34,28,5,30],"tags":[],"class_list":["post-117","post","type-post","status-publish","format-standard","hentry","category-ilm2007","category-miis2003","category-sql","category-vbnet"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pkp1o-1T","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/117","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/comments?post=117"}],"version-history":[{"count":3,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/117\/revisions"}],"predecessor-version":[{"id":443,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/117\/revisions\/443"}],"wp:attachment":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/media?parent=117"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/categories?post=117"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/tags?post=117"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}