Stored Procedure

[Raghunadhan]

I want to stored procedure in elastic search as in sql
For example in sql

stored procedure contains
insert operations
conditions
insert operations based on conditions(like if condition).

Example of Stored Procedure

Create Procedure sp_ADD_USER_EXTRANET_CLIENT_INDEX_PHY
(
@ParLngId int output
)
as
Begin
SET @ParLngId = (Select top 1 ParLngId from T_Param where ParStrNom = 'Extranet Client')
if(@ParLngId = 0)
begin
Insert Into T_Param values ('PHY', 'Extranet Client', Null, Null, 'T', 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL)
SET @ParLngId = @@IDENTITY
End
Return @ParLngId
End

[dadoonet (David Pilato)]

There is no Stored Procedure in elasticsearch.

You can have a look at Node Ingest feature (Easier) or Logstash (more flexible) may be to solve what you want to solve.

Or could you explain what is the use case ?

[Raghunadhan]

We are book publishing company.Need to generate reports and we process the book for various stages for publishing like spell check,artwork ,pagination etc

To keep track of the stages of book we developed a tool and using this tool we move from one stage to another stage i.e insert from table to another table that means we write various insert statement using conditions in stored procedures and generate report.Is elastic search is suitable this kind of business.

[dadoonet (David Pilato)]

So you want to record in elasticsearch the different steps of your process, right?
You should may be index events like:

POST book-event/_doc
{
  "isbn": "XYZ",
  "date":  "2018-11-26T10:00:00.000",
  "status": "spell check"
}

POST book-event/_doc
{
  "isbn": "XYZ",
  "date":  "2018-11-26T11:00:00.000",
  "status": "artwork"
}

PUT book/_doc/XYZ
{
  "isbn": "XYZ",
  "lastUpdated":  "2018-11-26T11:00:00.000",
  "dates": {
    "spellcheck": "2018-11-26T10:00:00.000",
    "artwork": "2018-11-26T11:00:00.000",
  },
  "status": "artwork"
}

And so on... With that I believe you can easily track what you need...
For sure, you need to think about your design first. Before thinking of implementation details like the question you asked.

[Raghunadhan]

SEE THE CONDITIONS IN BELOW SP and tell can we write like this

CREATE PROCEDURE usp_AUSBWF_PageProof_Movement(
IN par_schoolId VARCHAR(20),
IN par_unitId VARCHAR(20),
IN par_currentNodeId SMALLINT,
IN par_processCycle SMALLINT,
IN par_nextNodeId SMALLINT,
IN par_nextMenuId SMALLINT,
IN par_stageMove VARCHAR(15),
IN par_processRemarks VARCHAR(50),
IN par_userId VARCHAR(50),
IN par_overTime TINYINT UNSIGNED,
IN par_currStage VARCHAR(15),
IN par_year_level VARCHAR(50),
IN par_subject VARCHAR(100)

)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

DECLARE var_nextProcessCycle SMALLINT;
DECLARE var_workDays INT;
DECLARE var_recDate DATETIME;
DECLARE var_dispDate DATETIME;
DECLARE var_deptDeadLine DATETIME;
DECLARE var_prvDeptDue DATETIME;
DECLARE var_percentage INT;
DECLARE var_workflow VARCHAR(50);
DECLARE var_currProcCyle INT;
DECLARE var_nextProcCyle INT;
DECLARE var_ProcCyle INT;
DECLARE var_OperatorCode VARCHAR (100);
DECLARE var_currentMenuId VARCHAR (100);
DECLARE v_recDate DATETIME;
DECLARE v_dueDate DATETIME;
DECLARE v_workDays INT ;
DECLARE v_prvDeptDue DATETIME;
DECLARE v_percentage INT;
DECLARE v_deptDeadLine DATETIME;
DECLARE var_UnitCnt INT;
DECLARE var_DatasetUnitCnt INT;

/*********************************** START: Get TAT Percentage **********************************************/
SET v_recDate = (SELECT  book_pp_received_date FROM tblausbook WHERE book_school_id=par_schoolId AND book_year_level=par_year_level AND book_subject=par_subject );
SET v_dueDate = (SELECT  book_pp_due_date FROM tblausbook WHERE book_school_id=par_schoolId AND book_year_level=par_year_level AND book_subject=par_subject  );
SET var_workflow=(SELECT book_workflow FROM tblausbook WHERE book_school_id=par_schoolId AND book_year_level=par_year_level AND book_subject=par_subject );



SET v_workDays = udf_AUSBWF_CompWorkdays (v_recDate,v_dueDate);   

SET v_prvDeptDue = NOW();



SET v_percentage  = (SELECT percentage FROM tblaus_deadline WHERE stage='PageProof' AND serialNo=par_nextMenuId AND workflow=var_workflow);
SET v_deptDeadLine =udf_AUSBWF_DeadLineFun(v_percentage,v_recDate,v_dueDate,v_workDays);


SELECT MAX(process_cycle) INTO var_nextProcessCycle FROM tblausunitstatusdetails  
WHERE school_id = par_schoolId AND year_level=par_year_level AND subject=par_subject AND  unit_id = par_unitId AND menu_serialno = par_nextMenuId;


SET var_nextProcessCycle =    var_nextProcessCycle + 1 ;

IF var_nextProcessCycle IS NULL THEN
BEGIN
    SET var_nextProcessCycle = 1;
END;
END IF;

SELECT process_cycle, menu_serialno INTO var_currProcCyle, var_currentMenuId FROM tblausunitstatusdetails
WHERE school_id = par_schoolId AND year_level=par_year_level AND subject=par_subject  AND unit_id = par_unitId AND node_id = par_currentNodeId AND process_in_dt IS NOT NULL AND process_out_dt IS NULL;

SELECT operator_id INTO var_OperatorCode FROM tbloperatorassignment
WHERE school_id = par_schoolId AND year_level=par_year_level AND subject=par_subject AND unit_id = par_unitId AND menu_serialno = var_currentMenuId AND process_cycle = var_currProcCyle;

/START: Movement*******/

IF par_currStage = 'PageProof' THEN
BEGIN

    IF(var_currentMenuId=14 AND par_nextMenuId=14)  THEN
    BEGIN

        UPDATE tblausunitstatusdetails SET process_out_dt = NOW(), over_time = par_overTime, user_id = var_OperatorCode, Track_User_Id = par_userId, Movement_remarks = 'From PMS'
        WHERE  school_id = par_schoolId  AND  unit_id = par_unitId AND year_level=par_year_level AND subject=par_subject     AND node_id = par_currentNodeId AND process_out_dt IS NULL;
        
        SET var_UnitCnt =(SELECT COUNT(unitd_id) FROM tblausunit_details WHERE unitd_school_id=par_schoolId and unitd_year_level=par_year_level and unitd_subject=par_subject);
        SET var_DatasetUnitCnt=(SELECT COUNT(unit_id) FROM tblausunitstatusdetails where school_id=par_schoolId AND year_level=par_year_level and subject=par_subject and menu_serialno='14' and process_out_dt is not null and process_cycle in( SELECT max(process_cycle) FROM tblausunitstatusdetails where school_id=par_schoolId AND year_level=par_year_level and subject=par_subject and menu_serialno='14' and process_out_dt is not null group by school_id,year_level,subject));
        
        IF(var_UnitCnt=var_DatasetUnitCnt) THEN
        BEGIN
        UPDATE tblausbook SET book_pp_despatch_date=NOW() WHERE  book_school_id=par_schoolId AND book_year_level=par_year_level AND book_subject=par_subject   ;
        END;
        END IF;
    END;

END;
END IF;

END

[dadoonet (David Pilato)]

I don't read SQL code sorry. Explain in clear english the use case not the technical implementation you have today.

I mean that moving from a SQL relational model to a NoSQL Object oriented model requires some thinking. I'd not try to mimic one system in the other.

[Raghunadhan]

We had tables for every stages of book to keep track

For example If we complete the book process in artwork and next stage is pagination
then we move the book datas to pagination table from artwork table based on conditions.

we will code all this functions in a single name stored procedures.

[dadoonet (David Pilato)]

That's similar to the model I described earlier with an example.

[Raghunadhan]

OK ,Thank you for your help.

I will come back with details explanation sir

[Raghunadhan]

Dear all,

We use term and also keyword while using aggregation query what does term and keyword mean in elastisearch

See below example
GET /bank/_search
{
"size": 0,
"aggs": {
"group_by_state": {
"terms": {
"field": "state.keyword"
},
"aggs": {
"average_balance": {
"avg": {
"field": "balance"
}
}
}
}
}
}