A Content Server Request Handler Example - Part 3 - Asking the Database

Care for a practical example in Content Server applications? Here this is the ( The example) of a Categories Search, purely based on the database, not on the normal Content Server Search Server.

The Core: The Request Handler

To get the basic idea, refer my Article on LinkedIn WTF is a Request Handler in oScript?

The request handler is a pure construct of the Legacy GUI, it cannot be compared to the smartUI GUI constructs of REST/Javascript

To recap, the video for the example video is here in my LinkedIn Article A Content Server Request Handler Example - Part 1 - the Example

The Architecture is in A Content Server Request Handler Example - Part 2 - The Architecture

How to ask the database?

The datadase is ms sql server. The sql is done with ms SQL Server management studio

In the base, we have to ask the database TWICE. First, to get a list of all categories and second to get a list of all objects with a certain search-phrase as category.

The main table for that is CatRegionMap

Lets take a look on that table:

The table is build on this fields

CatID

Number - Size 19 - Not Null

The Category node ID. This is a foreign key to DTreeCore.DataID.

CatName

Varchar - Size 248 - Null

The Category name

SetName

Varchar - Size 248 - Null

The name of the set of this Attribute (Null if there is no set)

AttrName

Varchar - SIze 248 - Null

The Attribute name

RegionName

Varchar - Size 128 - Not Null

The region name of the Attribute. It is the string: “Attr_”+ “CatID” + “_” + attribute ID.

AttrType

Number - Size 10 - Null

Numerical representation of the attribute type

This is something to work with.

List of all for this user-id readable categories

First, to get all categories, we can write

Select distinct * from catregionmap        

This will get our result

Thats, where we get the categories names "Property", "Event" (with Sets), "Support Team" (with Sets) and "Test Columns".

Also the corresponding Node-IDs are 42142, 63922, 64912 and 1096483.

This is the thing to use in the list of the categories in the first request-handler:

List of the Node-IDs for the searchphrase

This is a little bit more complex.

First, we select for a given catid (here Event)

select distinct catname from CATregionmap where catid = '63922' "        

Then, as our Attribute Type varies quite wild, we must distinguish for the Attribute Type.

First, lets select DataID, Name, ParentID, Subtype, CreateDate, ModifyDate and the AttrID. Then lets COALESCE according to the attrID.

Then, lets join with the table LLAttrData to get the values of the attributes.

So, our query looks like this

SELECT distinct d.DataID, d.Name, d.ParentID, d.SubType, d.CreateDate, d.ModifyDate, a.AttrID, 
COALESCE(a.ValStr, CAST(a.ValInt AS VARCHAR(255)), CAST(a.ValLong AS VARCHAR(255)), CAST(a.ValDate AS VARCHAR(255))) AS AttrValue 
FROM dtree d 
INNER JOIN LLAttrData a ON d.DataID = a.ID WHERE DEfID = 63922         

Ok. there is one more thing.

We want to use the LIKE operator, to avoid the the user must supply the complete attribute value.

So we add this to our query (something is the searchphrase)

AND COALESCE(a.ValStr, CAST(a.ValInt AS VARCHAR(255)),CAST(a.ValLong AS VARCHAR(255)), CAST(a.ValDate AS VARCHAR(255))) Like '%something%'        

Yes, repeat the first COALESCE.

This are our two SQL queries, which we will use in the request handlers.

Very much fun with this easy SQLs