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.

MARKLOGIC:Load Files to Marklogic Server By using info:load

Load Files to Marklogic Server By using info:load

·         Execute the Following Query,

xquery version "1.0-ml";

import module namespace info = "http://marklogic.com/appservices/infostudio" 

at "/MarkLogic/appservices/infostudio/info.xqy";

let $delta :=  <options xmlns="http://marklogic.com/appservices/infostudio">

                                 <collection>/test</collection>

                                 <uri>

<literal>/mydir/</literal>

                                                <filename/>

                                                <literal>.</literal>

                                                 <ext/>

                                </uri>

                        </options>

return  info:load("C:\TestFolder", (), $delta, "test")

·         In above query “C:\TestFolder” is the local uri which contains XML files, "test" is the Database Name and we can provide Our own collection like “<collection>/test</collection>”
·         Now all of your Documents has 2 collections

o   /tickets/ticket/########### (Built-in collection added by info api)

o   /test (User defined collection)

·         If you want to remove Ticket collection use xdmp: document-remove-collections(“Document-name”,”/tickets/ticket/###########”)
·         If you have multiple documents you have implement above step in loop. For Example,

for $each in xdmp:directory("/mydir/","infinity")

return

          xdmp:document-remove-collections ( fn:base-uri($each),"/tickets/ticket/###")

XPATH-1 : Use Parentheses give better Performance

Hi,

I want to share important feature of parentheses in XPath. When you are going to use Xquery in Applications, Its difficult to querying database without Predicates. When we use predicate means we have to use parentheses for increasing performance. For Example, Following query takes  45 seconds to execute

fn:doc("Test.xml")/root/result[child1="text1"][child2="text2"][child3="text3"]

But Following query takes only 7 seconds get the same result,

((( fn:doc("Test.xml")/root/result[child1="text1"] )[child2="text2"] )[child3="text3"])

How parentheses takes Minimum Time ?


The reason behind this is when we are using first XPath means it takes each record of selected document and apply all the predicates. But Second XPath Get the filtered records for first predicate and then only it applies the second predicate and then third predicate.

Get Rich By Quitting Your Job!! - From Silicon India

Bangalore: Who doesn’t want to be rich? Each and everyone want to be rich. But the only thing which is required is your smartness and creativity. With that you can earn not only millions, but also your talent will increase. Quitting your job and searching for a new job is not a big deal. But your decision of quitting a monotonous job will be only appreciated once you start thinking of an idea which can make you a rich person. That doesn’t mean only wrong steps or ideas will take you forward to become a millionaire. There are other key factors which helps you to get rich, but that just requires your presence of mind and the hunger to work hard. Here are few main advantages that you can look upon by quitting a job, as reported on Make Money Website.

1. Rich people don’t work for money:

This is considered to be the hardest concept for people to understand. Many people think that those who are rich may be having lot of allowance; in fact it’s actually true and thus you need to work hard to get the allowance that helps you to become rich. But actually this is not the case. The rich people don’t actually work for money, they actually work for properties and later that properties will work for money. So in the same sense, if you are smarter enough, then buy a property that can produce you great income and work on them rather than going to a office to do the same monotonous work.

2. Quitting Your job helps you to bring out your creativity:

It is said that each one of us will be having some or the other talent inside us. But for few reasons we tend to be careless about creativity that is hidden inside us. If you work for a company, you tend to spend most of your hours of the week at your office working for that company. Many times you start working on something which you are not so passionate about. With all your work pressure at office you won’t have any time left to think about a new idea which can work for you as you would be already stressed out working the entire day. So, by quitting your job, it doesn’t mean that you have lost your creativity. Instead you will get more time to explore your inner talent. And for sure most of you will be creative enough to get the ideas which work out to you to become rich. This advantage you would never get if you are recruited somewhere working the whole day.

Other than these two ideas reported in the Make Money Website, there are few other tips reported by Rod on the Success Center website.

3. Save sufficient money, Quit Your Job and earn millions:

If you start saving your money from the very first salary without spending it much, you will get a good opportunity to spend it worthy in your future. So, if you save sufficient money for your future from your current job, in future if at all you plan to quit your job and start your own business, the money that you have saved comes to your help at this point of time. It makes setting up of your own business quite easy as you will be financially stable and you would be free from worrying about the financial support for your business. Once you start your business, try to maintain it in a way so that your business runs actively without any loss. With this method for sure you can earn lots of money.

4. Maximize your social media after you start a business:

Once you start your business you must always maximize your social media contacts because at present your social media has expanded enormously which we can say it’s mainly because of its low cost [or you can also say pretty much free] and you can get a large return on investment. It is absolutely a good way to connect with potential customers and also engage in a good way with present clients which makes it easier for them to trust upon you.