Cmis - Cmis Sql Search Tutorial - Eng

Today, let’s talk about CMIS-SQL

Eh Mister!! Before starting.. Who is CMIS ?

That’s right, let’s share the story of CMIS

Everyone was living in their own world…

Until the day when the encounter became unescapable…

$

Because each of them wanted to go higher than the others.

A few deaths later…

They realized they couldn’t go in so many different directions anymore

They shared a similar objective Content Interope rability

So instead of fighting, They gathered their strengths…

To keep going further on…

And to share information!

To resume …

CMIS stands for Content Management Interoperability Services (CMIS).

Actually it’s a draft specification submitted by leading ECM vendors to OASIS (the Organization for the Advancement of Structured Information Standards) with the aim of becoming an ECM industry standard.

Officially…

CMIS defines a domain model and set of bindings, such as Web Service and REST/Atom that can be used by applications to work with one or more Content Management repositories.

Visually, it looks like that…

Ok ! Could you explain the different part of CMIS ?

Ok… Let’s try a comparison…

Do you know how goods travel ?

http://www.flickr.com/photos/anniemole/313981428/

Indeed, goods enjoy great trip !

Most of them, travel by…

Trucks…

http://www.flickr.com/photos/87913776@N00/422603859/

Ships…

http://www.flickr.com/photos/danramarch/2875308394/

Or Trains…

http://www.flickr.com/photos/pfly/199823544/

What’s the common point ?

CONTAINERS !

http://www.flickr.com/photos/nelsoncruz/431244400/

It’s standardized…

http://www.flickr.com/photos/ppdigital/2327889692/

http://www.flickr.com/photos/marelles/852012838/

It’s interoperable…

That’s why, it increase exchange !

http://www.flickr.com/photos/bcnbits/2859509269/

And CMIS ?

1. CMIS is THE container for document management

It standardizes a unique domain/data model http://www.flickr.com/photos/circulating/2533948028/

2. CMIS is a means of transport

http://www.flickr.com/photos/grumbler/448401254/

By WebServices

http://www.flickr.com/photos/adam_knight/356512207/

By REST/ATOM

http://www.flickr.com/photos/theolaphoto/23255

3. CMIS provides services

http://www.flickr.com/photos/ejouvenaux/2886232980/

Document Management

http://www.flickr.com/photos/misterdna/49841409/

http://www.flickr.com/photos/chrisjohnbeckett/514914659/

CMIS SQL search

That’s why, CMIS increase exchange !

http://www.flickr.com/photos/bcnbits/2859509269/

All right folks ? Can we go back to CMIS-SQL ?

Ok! Let’s explain CMIS-SQL !

So what’s CMIS-SQL…

CMIS-SQL ? •

•

•

•

CMIS provides a type-based query service for discovering objects that match specified criteria, by defining a read-only projection of the CMIS data model into a Relational View. Through this relational view, queries may be performed via a simplified SQL SELECT statement. This query language, called CMIS SQL, is based on a subset of the SQL-92 grammar, with a few extensions to enhance its filtering capability for the CMIS data model, such as existential quantification for multi-valued property, full-text search, and folder membership. Other statements of the SQL language are not adopted by CMIS. The semantics of CMIS SQL is defined by the SQL92 standard, plus the extensions, in conjunction with the model mapping defined by CMIS’s relational view Source: Oasis CMIS Part 1 – Domain Model v0.61c

OK… Demo time!

A b it of theo ry

CMIS enables to query on…

CMIS enables to query on…

CMIS DOMAIN MODEL

Syntax

Simple query • SELECT [columns] • FROM [Virtual Table Names] • WHERE [conditions] • ORDER BY [sort specification]

SELECT [columns] • This clause identifies the set of columns that will be included in the query results for each row.

FROM [Virtual Table Names] • This clause identifies which Virtual Table(s) the query will run against.

WHERE [conditions] • This clause identifies the constraints that rows SHALL satisfy to be considered a result for the query.

ORDER BY [sort specification] • This clause identifies the order in which the result rows SHALL be sorted in the result row set.

Predica te Functio ns

CONTAINS() • This is a predicate function that encapsulates the fulltext search capability that MAY be provided by a Repository • The predicate returns a Boolean value.

SCORE() • This is a predicate function that encapsulates the fulltext search capability that MAY be provided by a Repository • The SCORE() predicate function returns a decimal value in the interval [0,1]

IN_FOLDER() • This is a predicate function that tests whether or not a candidate object is a childobject of the folder object identified by the given . • The predicate returns a Boolean value.

IN_TREE() • This is a predicate function that tests whether or not a candidate object is a descendant-object of the folder object identified by the given . • The predicate returns a Boolean value.

Operat or

Logical operators AND To add a criteria OR To add a choice between several criterias NOT To exclude a criteria

Predicate LIKE allows you to use wildcards in a WHERE clause IS [NOT] NULL Check if an expression is NULL. [NOT] IN allows you to specify multiple values in a WHERE clause.

Now how to make simple queries with CMIS-SQL ?

CMIS-SQL : My Recipe

http://www.flickr.com/photos/piston9/2497672447/

Install a content Repository

http://www.flickr.com/photos/piston9/2497672447/

Alfresco 3.x Community (for me)

I’m always here !

Add an independent CMIS client like Shane Johnson's Flex/AIR CMIS Browser

Bind them…

http://www.flickr.com/photos/intherough/325334910

Then create your queries

http://www.flickr.com/photos/sgw/2329607771/

And watch results.

http://www.flickr.com/photos/fozzeee/3756133976/

Let’s try some samples..

I want all informations of all folders SELECT * FROM FOLDER

I just want the name of all folders SELECT F.Name FROM FOLDER AS F

I want all informations of « MyFolder » SELECT F.* FROM FOLDER AS F WHERE F.Name='MyFolder'

I want all information of all documents in « MyFolder » SELECT * FROM DOCUMENT AS D WHERE IN_FOLDER( 'workspace://SpacesStore/b7008f8b-5352-4e9b-97e7-4374479ee984')

I want all information of folders in « MyFolder » SELECT * FROM FOLDER AS F WHERE IN_FOLDER( 'workspace://SpacesStore/b7008f8b-5352-4e9b-97e7-4374479ee984')

I want ObjectType and Name of « MyDocument.doc » SELECT D.ObjectTypeId, D.Name FROM document AS D WHERE D.Name=‘MyDocument.doc'

I want ObjectType and Name of all document that contains the word ‘CMIS’ SELECT D.ObjectTypeId, D.Name FROM document AS D WHERE CONTAINS(‘CMIS‘)

I want the name of all document that contains the word ‘CMIS’ and objectType is ‘cmis_doc’ SELECT D.ObjectTypeId, D.Name FROM document AS D WHERE CONTAINS(‘CMIS‘) AND D.ObjectTypeId='D/cmis_doc'

Voilà! Enough for today!

Now it’s your turn!

Don’t hesitate to create or use.. CMIS Toolkits CMIS Client CMIS Repository

Test your combination

And make your own !

Be the first to enjoy content interoperability!

And finally…

To discover…

To go further on…

And to reach our goals…

Choose ECM Open Source

CMIS-SQL By JM.PASCAL www.opensourceecm.fr & www.open-source-ecm.com