MARKLOGIC: Print the month & year from the specific date range in the YYYY_MM Fomat using XQUERY

Hi All,

Recently I got a request to print the labels(STRING) from the provided date range. The use case of this request is to create the collection based on the date. For example, if customer1 bought the product on 01-JAN-2020 & customer 2 bought the product on 01-FEB-2020 then we need to store the customer purchase details in the corresponding collection. The above data will be store as mentioned below.

2020_JAN(collection-URI) - /customer1.json
2020_FEB(collection-URI) - /customer2.json

suppose there is one more customer bought the product on 02-FEB-2020 then FEB-2020 collection has 2 customers details.

2020_JAN(collection-URI) -
     /customer1.json
2020_FEB(collection-URI) -
    /customer2.json
    /customer3.json

Below code is useful to implement the above feature in your code. I request you please provide your comments if there is any changes required.

(:

This query is useful to print the label from the specified date range.
These labels are very useful to bucketing the data using a date range

@Developer - Srinivasan
@updateDate - 18-Jul-2020
@version - 1.0
:)

(: input - provide start & end date in date format YYYY-MM-DD :)
let $startDate := xs:date("2010-06-01")
let $endDate := xs:date("2010-07-30")

(: find the differences between date and used the same  :)
let $year-diff := year-from-date($endDate) - year-from-date($startDate)
let $month-diff := month-from-date($endDate) - month-from-date($startDate)

(: create map to store the intermedaite values :)
let $map := map:map()
let $_ := map:put($map,"date",$startDate)

(: prepare the label value in the format - YYYY_MM :)
let $printLabel :=
    for $each at $pos in (1 to (($year-diff * 12) + $month-diff) + 1)
    let $newDate := if($pos eq 1) then map:get($map,"date") else map:get($map,"date") + xs:yearMonthDuration("P1M")
    let $_ := map:put($map,"date",$newDate)
    where $newDate le $endDate
    return
        fn:format-date($newDate,"[Y0001]_[M01]")

(: return the prepared label format :)
return
    $printLabel

Marklogic: SSL enablement in Marklogic server

Enabling SSL in the Marklogic server

Every business want to secure their application from hackers. To secure the application first step is to ensure the all the server communications are secured. Our application also need to be enabled the SSL. As I am new to the SSL, I don’t have much idea about this security. By following this steps simply we can enable the SSL in marklogic server.

MarkLogic will allow you to use an existing certificate or will allow you to generate a Certificate Request. The key difference between above two lies in who generates public-private keys and other fields in the certificate.

For a Pre-Signed Certificate: In this instance, the keys already exist outside of MarkLogic Server, and 3rd party tool would have populated CN (Common Name) and other subject fields to generate Certificate Request File (.csr) containing a public key.

For a Certificate Request Generated by MarkLogic: In this instance, new keys are generated by MarkLogic Server (it does this while creating the new template), while CN and other fields are added by the MarkLogic Server Administrator (or user) through the web-based MarkLogic admin GUI during New Certificate Template creation.

Among two methods we have to use the Pre-signed certificate.

Step1:

Get the appropriate certificate and private key for the marklogic server host. As we are using trusted certificate we need to keep the private key in the marklogic server along with the certificate.

Step2:

Download the Certificate Authority details from the certificate and upload the same in the Marklogic server in the adminPage -> Security -> Certificate Authorities -> import.

Step3:

You can find the list of all the certificate authorities will be shown in the CA list in the marklogic server

Step4:

Create a new Certificate Template “SAMPLE” with the fields similar to your existing Pre-Signed Certificate. Its not necessary to follow the same. But you can extract this information from the certificate and fill it.

User the below command to print the certificate details in text

              openssl x509 -in ML.pem -text 

Note - Above fields are placeholders only for signed Certificate, and MarkLogic mainly uses above fields to generate Certificate Signing Request (.csr). For Certificate request generated by 3rd party tool, it does NOT matter if template field matches exactly with final signed Certificate or not.

Once we have Signed Certificate imported, App Server will use the Signed Certificate, and the SSL Client will only see field values from the Signed Certificate (even if they are different from Template Config page ).

Step5:

In our case we need to enable the SSL for HTTP server. Enabling the SSL in the HTTP server just follow these steps to ensure basic SSL enablement.

·       Select the ssl certificate template – we can use the “SAMPLE” certificate template

·       Select TRUE for require client certificate

·       Select the CA in the show option

Step 6:

Copy the certificate.crt and privateKey.key into the linux server(/tmp/) where marklogic installed and the certificate should the domain name as the host name.

Step 7:

Prior to installing a pre-signed certificate and private key the following verification should be performed to ensure that both certificate and key are valid and are in the correct format. 

* Generate and display the certificate checksum using the OpenSSL utility. If below command is not working for .crt then try with .pem. you can find the command to convert the .crt to .pem from google.

[admin@sitea ~]# openssl x509 -noout -modulus -in certificate.crt | openssl md5

(stdin)= 2ddd2ca48ad2eb4eba082f5da3fd33ab

* Generate and display the private key checksum

[admin@siteaa ~]# openssl rsa -noout -modulus -in privateKey.key | openssl md5

(stdin)= 2ddd2ca48ad2eb4eba082f5da3fd33ab

The checksum from both commands should return identical values, if the values do not match or if you are prompted for additional information such as the private key password then the certificate and private keys are not valid and should be corrected before proceeding.

Note: Proceeding to the next step without verifying the certificate and the private key could lead to the MarkLogic server being made inaccessible. 

Step 8:

As marklogic don’t have option to load the key file along with the certificate we need to use query console to do the same. By executing below query we are installing the certificate and key in the certificate template.

Execute below query in QConsole with security database selected.

xquery version "1.0-ml";

import module namespace pki = "http://marklogic.com/xdmp/pki" at "/MarkLogic/pki.xqy";

import module namespace admin = "http://marklogic.com/xdmp/admin" at "/MarkLogic/admin.xqy";

(: Update Template name for your environment :)

let $templateid := pki:template-get-id(pki:get-template-by-name("TemplateName"))

(: Path on the MarkLogic host that is readable by the MarkLogic server process (default daemon) :)

(:   File suffix could also be .txt or other format :)

let $path-to-cert := "/tmp/certificate.crt"

let $path-to-key := "/tmp/privatekey.key"

return

pki:insert-host-certificate($templateid,

  xdmp:document-get($path-to-cert,

    <options xmlns="xdmp:document-get"><format>text</format></options>),

  xdmp:document-get($path-to-key,

    <options xmlns="xdmp:document-get"><format>text</format></options>)

)

Step 9:

Once the query ran successfully we are ready to test the SSL in our appserver. We already enabled the SSL in the appserver port number 8011 then you can try below url in your browser. If you are able to access the any document from the DB using this link then HTTPS is enabled for your Marklogic http server.

https://hostName:8011/v1/documents?uri=/sample.xml

Above steps I have created for my project. If there is any disconnect in the steps or any improvements needed please comment below.

I have implemented the SSL in my project using below reference. Used the same document as a reference for this post.

https://help.marklogic.com/Knowledgebase/Article/View/297/0/import-pre-signed-certificate-and-key-for-marklogic-https-app-server

Medicine: Treatment for Throat infection by using turmeric

Hi All,

Everybody faces the throat infection. We will use lot of medicines which can cure. But i have something to share which will be very effective and will lead to permanent cure.


step 1 : Buy the Indian(Erode, If not you can use any turmeric) turmeric and grind the powder. 

step 2 : Take the clay pot or any natural vessel where we can fry the turmeric powder

step 3 : when you fry the turmeric powder(small spoon) you will feel the turmeric smell which will also very good for health.

step 4 : while frying keep the flame in low. Once the turmeric become sandal color add the cow milk

step 5 : stir the milk + turmeric for untill fully milk boiled

step 6 : Add pinch of the pepper in the turmeric milk and drink it for 3 days night after food.

Above one is not worked out then you have visit doctor. Above food is treated as a food. There is no side effect. This will give you good sleep.

If you have any doubts let me know by comments.

I am Back 😊

Last 5 years unable to update any post in my blog. Going forward i will write various posts in this space. Because Knowledge sharing is the heart of the universe. I want my readers to be actively comment on my post. Your comments can inspire me to post more which can improve your life in someway.

Marklogic: Point-in-Time queries

Enabling Point-in-time queries in Admin Interface

• In order to use point-in-time queries in a database, you must set up merges to preserve old versions of fragments.
• To access the Merge Policy Configuration page, click the Databases > db_name > Merge Policy link from the tree menu of the Admin Interface.
• To set the merge timestamp parameter to the current timestamp, click the get current timestamp button on the Merge Control Configuration page and then Click OK.

Fig1: Enabling the point-in-time queries in Training DB.

Example: Querying Deleted Documents

Step 1: Execute the below query to insert a sample XML into Marklogic DB by using query console(http://localhost:8000/qconsole/). Copy the below code and made few changes and execute the query in query console.

(: defining the version of the xquery used for ML-DB :)

xquery version "1.0-ml";

(: delimited input data for XML :)

let $record := "778171;srinivasan;SE,557474;raman;ITA,663344;hachiko;ITC"

(: prepare the XML from input data :)

let $recordToXML := element employeeXML { for $each in fn:tokenize($record,",")

return

element employee {

let $eachrec := fn:tokenize($each,";")

return

(element empid {$eachrec[1]},element empname {$eachrec[2]},element empdesignation {$eachrec[3]})

}

}

(: provide the name for your sample XML name :)

let $sampleXMLfilename := "/examples/empsmall.xml"

(: insert this XML into Marklogic DB :)

return xdmp:document-insert($sampleXMLfilename,$recordToXML)

Note: You can replace the coloured details with your own details.

Step-2: Execute the below code in query console to view the document from Marklogic DB

xquery version "1.0-ml";

declare namespace html = "http://www.w3.org/1999/xhtml";

(: specify the document URI - unique resource identifier:)

let $docURI := "/examples/empsmall.xml"

(: retrieve the document by using "doc" function:)

let $viewDoc := fn:doc($docURI)

return $viewDoc

Step-3: execute the below query to delete the document from DB

xquery version "1.0-ml";

declare namespace html = "http://www.w3.org/1999/xhtml";

(: specify the document URI - unique resource identifier:)

let $docURI := "/examples/empsmall.xml"

(: delete the document by using XDMP function:)

let $deleteDoc := xdmp:document-delete($docURI)

return ()

Step-4: Run the Step 2 again for view the document. It returns the empty sequence because document was just deleted.

“In Marklogic it is possible to travel back in TIME and view the deleted document”

Step-5: Run a point-in-time query, specifying the current timestamp (this is semantically the same as querying the document without specifying a timestamp):

xquery version "1.0-ml";
declare namespace html = "http://www.w3.org/1999/xhtml";

(: eval function usefull when want to run the query for specific transaction :)
xdmp:eval("doc('/examples/empsmall.xml')", (),
<options xmlns="xdmp:eval">
  <timestamp>{xdmp:request-timestamp()}</timestamp>
</options>)

(: returns the empty sequence because the document has been deleted :)

Step-6: Run the point-in-time query at one less than the current timestamp, which is the old timestamp in this case because only one change has happened to the database. The following query statement returns the old document.

xquery version "1.0-ml";
declare namespace html = "http://www.w3.org/1999/xhtml";

(: Below eval function using the older timestamp to fetch the deleted document version. Here I have copied this time stamp from Fig-1: field merge-timestamp  :)
xdmp:eval("doc('/examples/empsmall.xml')", (),
<options xmlns="xdmp:eval">
  <timestamp>13934835606579742</timestamp>
</options>)
(: returns the deleted version of the document :)

How to change the Username and Password in Tomcat server?

Actually, Inside tomcat folder we usually comment all the user details. We can find this file in the below location

“ c://tomcat/conf/tomcat-users.xml “

we should add the manager role in this xml

“ <role rolename="manager"/> ”

Now we can create a user and assign manager role to this user.

“ <user username="tomcat" password="tomcat" roles="manager"/> ”

Note : The users who dont have “manager” role they cant enter into the tomcat configuration manager.

MARKLOGIC: How to use MLSQL Xquery Library for Marklogic

MLSQL: An XQuery Library for Relational Database Access

From Jason Hunter

Introduction

MLSQL is an open source XQuery library (written by Jason Hunter of MarkLogic and Ryan Grimm of O'Reilly Media) that allows easy access to relational database systems from within the MarkLogic environment. MLSQL lets you execute arbitrary SQL commands against any relational database (MySQL, Oracle, DB2, etc) and it captures the results as XML for processing within the MarkLogic environment. Using the library enables XQuery applications to leverage a relational database without having to resort to Java or C# glue code to manage the interaction.

This article introduces MLSQL, its architecture and API, and demonstrates a few real life applications built on the library.

Architecture

MLSQL consists of a couple hundred lines of XQuery and a few hundred lines of Java servlet code. The XQuery uses xdmp:http-post() to send the SQL query to the servlet, and the servlet uses JDBC to pass the query to the relational database. Any database with JDBC support will do. The servlet then returns the result set or update details as XML over the wire to the XQuery environment.

The servlet holds the database credentials, connection details, and manages a connection pool for efficiency. The XQuery can pass "bind parameters" to the database as part of the query for efficiency and safety. The diagram below shows the architecture. 

Installation and configuration details follow at the end of this article.

Credentials and Security

For simplicity in MLSQL 1.0 we've opted to encapsulate the database details inside the servlet. An alternate design would allow the XQuery to pass the details, such as in the query string. If you're interested in this, talk to us. Note that for database security, you should restrict access to the MLSQL web service to only the trusted MarkLogic host. Tools like tcpwrappers makes this easy.

MySpace Begins from here

First of all I want to thank Jason Hunter, developer of this technology. One of the below reasons you may need MLSQL in your Application,

·      If you have one application in Marklogic and another one in Relational Database for same purpose. So you need synchronization between these two databases.

Installation

For installation you need following applications in your machine,

•         JDK (1.6 or any advanced version)
•          Tomcat Server(6.0 or any advanced version)
•          Marklogic Server 5.0
•          MySql Database( or any relational database)
•          Winant (ant builder software)

Prior to this installation you should have,

•        In Marklogic

a.       Your application Database(TestDatabase)

b.      Application Server(TestHttpServer)

•         In MySql

a.       Your application Database(TestDatabase)

b.      Table which you want to update from ML(TestTable)

·         Download MLSQL source from

              http://community.marklogic.com/download/binaries/code/mlsql/releases/mlsql-src-1.1.zip

Step 1

                Copy the C:\Folderlocation\mlsql-src-1.1\client\ sql.xqy library module to your XQuery code(Root directory which mentioned in application server). This usually requires copying the file into your HTTP server root directory, but depending on configuration might require loading into the server's modules database.

Step 2

Installing servlet code,

• Run the “ant” command in the command prompt by using following steps

• Go to extracted folder location (i.e. C:\Folderlocation\mlsql-src-1.1\ )

• Now type “ant” and press enter

• Now you can get see one additional folder named “C:\Folderlocation\mlsql-src-1.1\ buildtmp”

•  Copy “C:\Folderlocation\mlsql-src-1.1\ buildtmp\mlsql” and paste it into “C:\Tomcat60\webapps”.

Step 3

• Provide database credentials and connection details.

• The web application includes a web.xml file with init parameters the servlet will read in order to connect to the database. Adjust these as appropriate for your system.

• You'll need to set the JDBC driver, connection URL, username, and password. The web.xml file includes instructions.

Step 4

    Make sure to add your JDBC driver JAR files to the WEB-INF/lib directory. Driver libraries aren't usually licensed for redistribution, so you'll have to obtain them on your own.

Step 5 

     test the servlet's URL. Try making a basic web request to the MLSQL servlet. You'll know it's listening and you've found it when you get a <sql:exception> talking about "Premature end of file". That means you didn't provide a query, but at least you found the servlet.

Step 6

•  Test the XQuery connection. Write a basic query like this:

import module namespace sql  = "http://xqdev.com/sql" at "sql.xqy"

sql:execute("select 1 as test", "http://localhost:8080/mlsql", ())

•  You might need to adjust the "at" depending on where you placed the library module. If it works you'll see this result:

<sql:result xml:lang="en" xmlns:sql="http://xqdev.com/sql">

  <sql:meta/>

  <sql:tuple>

    <test>1</test>

  </sql:tuple>

</sql:result>

 What Next?

 Now you can View, Create, Update and Delete records from your Relational database by using “MLSQL Module”. I will share the sample code for this operations one by one before that I want to mention some details on sample code

·         Localhost – indicates your local machine details

·     Import Module – I have the sql.xqy file in the same directory(Note: You might need to  adjust the "at" depending on where you placed the library module)

·         Test – Relational DB Table Name

·      In sql:execute(SQLQuery,URL,Options) function – Maintain following parameters for    this function

o   SQLQuery – SQL query for your requirement

o   URL – Pointing the Servlet which is placed in the Tomcat server

o   Options – The third argument to sql:execute() is an XML options node that can include various things including a list of bind parameter values.

View records query

                 Using this XQuery you can view all of your records from Relational DB. Note: If you go for large table you have to face performance issues.

Sample.xqy

import module namespace sql  = "http://xqdev.com/sql" at "sql.xqy"

sql:execute("select  * from test", "http://localhost:8080/mlsql", ())

Results

<sql:result xmlns:sql="http://xqdev.com/sql">

  <sql:meta/>

  <sql:tuple>

    <id>1</id>

    <fname>Jason</fname>

    <lname>Hunter</lname>

    <age>32</age>

  </sql:tuple>

  <sql:tuple>

    <id>1235</id>

    <fname>Ryan</fname>

    <lname>Grimm</lname>

    <age null="true"></age>

  </sql:tuple>

</sql:result>

Once you got this result in Marklogic you can reconstruct the Result XML as per your Requirement and you can change the SQL Query parameter also.

Bind Variables with your query

                 By using the third parameter of sql:execute() you can bind variables with you SQL query.

Sample.xqy

import module namespace sql  = "http://xqdev.com/sql" at "sql.xqy"

sql:execute("select  * from test where id = ?", "http://localhost:8080/mlsql", sql:params(1235) )

Results

<sql:result xmlns:sql="http://xqdev.com/sql">

  <sql:meta/>

  <sql:tuple>

    <id>1235</id>

    <fname>Ryan</fname>

    <lname>Grimm</lname>

    <age null="true"></age>

  </sql:tuple>

</sql:result>

Note

         Above SQL query returns record which has the id = "1235".

Performance

In simple testing on a laptop, the MLSQL servlet was able to push about 1,000 simple tuples per second toward the client. We anticipate that's fast enough for most SQL queries you will write from XQuery. Just don't try to pull a whole table into the XQuery environment and process it with XPath. That's why SQL has WHERE clauses.