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.

Dream Job: Not the Best? Be Exceptional! from Silicon India

Not the Best? Be Exceptional!

Bangalore: You may be just an average job seeker who is spending months for searching a job after graduation. Are you disappointed on your fate? Probably, you may hope for the luck and best indeed- with your paled face. Anyone can guess it hurts more when you are asked about your job search. Being pretty smart enough cannot alone bring you up among the hundreds of resumes your hiring manager gets daily.

Now, you need to evaluate how you are doing in job search. Your hard work on the city to get employed so far is not good as you are still with an unemployment tag. Apart from doing the same thing as local listings in newspapers and handing in numerous resumes, what have you been done so far to get a job? You live in an internet era where with number of social media access you can be informed about the hiring trend of big gates. You must be doing well as the every potential job seeker do.

Don’t you have the skills which can land your dream job? Of course, to be the best, you need to be best with necessary skills. You may be among those flock that had never been serious to acquire new skills or to become best in new skills. To be successful, you should be on track of either best or exceptional. How to be exceptional will be the question you are loaded with, now.

Few things you can do to be exceptional and so successful. Keep in mind you have to make your dream job comes true. Well, you would be following a custom style in your job hunting. Hoping for a miracle and doing as everyone does will make you deadlocked either. Sending resumes may be on the way to the heap with hundreds or thousands others. What you think about a video portfolio or a right away conversation with hiring manager?

Be realistic should be your mantra to accomplish your goal as you must stop wasting time applying for each and every job. Seek out the jobs which can suit your skills, academic qualifications and relevant experiences. You should be polite and persistent to follow the hiring process once you have applied for. Building a relationship with the hiring manger through follow-ups, phone calls and e-mails will sometimes help you to receive an offer letter.

When you stand as an exceptional one you should be about excellent and disciplined. When you nurture good habits in you and having a hold on reality will also make you different. You may not be emotionally balanced but you can balance your emotions to stand up every way. Setting out the goals itself will not speak unless you think out of box and execute your ideas with a disciplined approach. Also you get a better fit everywhere when you embrace honesty as your best of everything.  To gain the respect of being exceptional, you must have to believe in yourself while following a creatively executed plan to explore your exceptional skills to visualize the ‘success’ with an attitude of ‘never give up’.