drop PROCEDURE if exists get_ddl(varchar,varchar,varchar);
/*****************************************************
 * PROCEDURE: get_ddl(OBJECT_TYPE, OWNER, OBJECT_NAME
 * DESCRIPTION: get schema for OBJECT_NAME
 *              OBJECT_TYPE : table, indexes, view, synonym, procedure
 *              OWNER not use now.
 * USAGE EG : get_dll('table', 'gbasedbt', 'Tab1')
 * WRITE BY : liaosnet@gbasedbt.com 2024-09-13
 *****************************************************/
create PROCEDURE get_ddl(p_type varchar(40), p_user varchar(32), p_name varchar(128))
returning lvarchar(32000);
  define v_numtab int;
  define v_tabname varchar(128);
  define v_ddl lvarchar(32000);
  define v_colname varchar(128);
  define v_coltype_name varchar(128);
  define v_nullable varchar(128);
  define v_defvalue varchar(254);
  define v_deftype  varchar(10);
  define v_collength int;
  define v_decimal_p int;
  define v_decimal_s int;
  define v_pk_collist lvarchar(2048);
  define v_pk_name varchar(128);
  define v_idx_name varchar(128);
  define v_idx_sqls lvarchar(2048);
  define v_view_text nchar(256);
  define v_proc_text nchar(256);
  define v_comments varchar(255);
  define v_synonym_tabname varchar(128);

  on exception
    return null;
  end exception;

  -- exists table, delimident=y only for tablename
  let v_tabname = '';
  let v_numtab = 0;
  let v_ddl = '';
  if lower(p_type) = 'procedure' then
    select count(1) into v_numtab
    from sysprocedures where (procname = p_name or procname = lower(p_name));
  else
    -- table, view, indexes
    select count(1) into v_numtab
    from systables where (tabname = p_name  or tabname = lower(p_name));
  end if;
  if v_numtab = 0 then
    return null;
  elif v_numtab > 1 then
    let v_tabname = chr(34) || p_name || chr(34);
  else
    let v_tabname = lower(p_name);
  end if;

case lower(p_type)
  when 'table' then
    -- table
    let v_ddl = 'CREATE TABLE ' || v_tabname || '(' || chr(10);
    -- colname, coltype, nullable, default_value, default_type_flag
    foreach SELECT tmp.colname,tmp.coltype_name,
              CASE d.type
                  WHEN 'L' THEN gbasedbt.get_default_value(tmp.coltype, tmp.extended_id, tmp.collength, d.default::lvarchar(256))::VARCHAR(254)
                  WHEN 'C' THEN 'current year to second'::VARCHAR(254)
                  WHEN 'S' THEN 'dbservername'::VARCHAR(254)
                  WHEN 'U' THEN 'user'::VARCHAR(254)
                  WHEN 'T' THEN 'today'::VARCHAR(254)
                  WHEN 'E' THEN de.default::VARCHAR(254)
                  ELSE          NULL::VARCHAR(254)
              END AS def_value,
              tmp.nullable,
              d.type,
              tmp.collength
            INTO v_colname, v_coltype_name, v_defvalue, v_nullable, v_deftype, v_collength
            FROM (
                SELECT ce.tabid, ce.colno, ce.colname,ce.coltype,ce.extended_id,ce.collength,ce.coltypename2::varchar(128) AS coltype_name,
                CASE WHEN ce.coltype > 255 THEN 'NOT NULL' ELSE '' END AS nullable
                FROM syscolumnsext ce, systables t
                WHERE ce.tabid = t.tabid
                AND t.tabname = v_tabname
				-- order by colno default
                ORDER BY ce.colno) tmp LEFT JOIN sysdefaults d ON (tmp.tabid = d.tabid AND tmp.colno = d.colno)
                    LEFT JOIN sysdefaultsexpr de ON (tmp.tabid = de.tabid AND tmp.colno = de.colno and de.type='T')

      -- no column found, return null
      if v_colname is null or v_colname = '' then
        return null;
      end if;

      IF v_coltype_name in ('DECIMAL','MONEY') THEN
        let v_decimal_p = v_collength / 256;
        let v_decimal_s = mod(v_collength,256);
        if v_decimal_s = 255 then
          let v_coltype_name = v_coltype_name || '(' || v_decimal_p || ')';
        else
          let v_coltype_name = v_coltype_name || '(' || v_decimal_p || ',' || v_decimal_s || ')';
        end if;
      END IF;

      -- default values with function not do.
      if v_deftype = 'E' then
        let v_defvalue = 'DEFAULT (' || v_defvalue || ')';
      elif v_deftype = 'L' and v_coltype_name[1,4] in ('CHAR','VARC','LVAR','NCHA','NVAR') then
        let v_defvalue = 'DEFAULT ''' || v_defvalue || '''';
      elif v_defvalue is not null then
        let v_defvalue = 'DEFAULT ' || v_defvalue;
      end if;

      -- column: colname coltype default nullable
      let v_ddl = v_ddl || '  ' || v_colname || ' ' || v_coltype_name  || ' ' || v_defvalue || ' ' || v_nullable || ',' || chr(10);
    end foreach;

    -- primary key
    let v_pk_collist = '';
    let v_pk_name = '';
    foreach SELECT so.constrname,
        rtrim((select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part1)) || ',' ||
              (select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part2)) || ',' ||
              (select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part3)) || ',' ||
              (select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part4)) || ',' ||
              (select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part5)) || ',' ||
              (select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part6)) || ',' ||
              (select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part7)) || ',' ||
              (select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part8)) || ',' ||
              (select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part9)) || ',' ||
              (select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part10)) || ',' ||
              (select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part11)) || ',' ||
              (select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part12)) || ',' ||
              (select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part13)) || ',' ||
              (select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part14)) || ',' ||
              (select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part15)) || ',' ||
              (select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part16)), ',') as keys
          INTO v_pk_name, v_pk_collist
          FROM  systables st, sysconstraints so, sysindexes si
          WHERE st.tabname = v_tabname
          AND st.tabid = so.tabid
          AND so.constrtype = 'P'
          AND so.idxname = si.idxname
    end foreach;

    if v_pk_collist = '' or v_pk_collist is null then
      let v_ddl = rtrim(v_ddl, ',' || chr(10));
    elif v_pk_name[1,1] = 'u' then
      let v_ddl = v_ddl || '  PRIMARY KEY(' || v_pk_collist || ')';
    else
      let v_ddl = v_ddl || '  PRIMARY KEY(' || v_pk_collist || ') CONSTRAINT ' || v_pk_name ;
    end if;
    let v_ddl = v_ddl || chr(10) || ');' || chr(10);

    -- indexes, from proc_idxsql(idxname)
    let v_idx_name = '';
    foreach select si.idxname
          into v_idx_name
          from sysindexes si, systables st
         where si.tabid = st.tabid
           and st.tabname = v_tabname
           and si.idxname[1,1] != ' '
		   -- order by default
      call proc_idxsql(v_idx_name) returning v_idx_sqls;
      let v_ddl = v_ddl || v_idx_sqls || chr(10) ;
    end foreach;

    -- has syscomments table, version 2.0 and older has not comments.  2024-09-13
    select tabname 
	into v_comments 
	from systables 
	where tabname = 'syscomments';
	if v_comments = 'syscomments' then 
      -- comment table;
  	  foreach select replace(comments, chr(39), '''''') into v_comments
  	         from syscomments
  			 where tabname = v_tabname
  	    let v_ddl = v_ddl || 'COMMENT ON TABLE ' || v_tabname || ' IS ''' || v_comments || ''';' || chr(10);
  	  end foreach;
  	
  	  -- comment column;
  	  foreach select colname,replace(comments, chr(39), '''''') into v_colname, v_comments
  	          from syscolcomments
  			 where tabname = v_tabname
        let v_ddl = v_ddl || 'COMMENT ON COLUMN ' || v_tabname || '.' || v_colname || ' IS ''' || v_comments || ''';' || chr(10);
  	  end foreach;
    end if;

  when 'indexes' then
    -- indexes, from proc_idxsql(idxname)
    let v_idx_name = '';
    foreach select si.idxname
          into v_idx_name
          from sysindexes si, systables st
         where si.tabid = st.tabid
           and st.tabname = v_tabname
           and si.idxname[1,1] != ' '
		   -- order by default
      call proc_idxsql(v_idx_name) returning v_idx_sqls;
      let v_ddl = v_ddl || v_idx_sqls || chr(10) ;
    end foreach;

  when 'view' then
    -- view
    foreach select v.viewtext
              into v_view_text
              from systables t,sysviews v
             where t.tabid = v.tabid
               and t.tabtype = 'V'
               and t.tabname = v_tabname
             order by v.seqno
      let v_ddl = v_ddl || v_view_text;
    end foreach;

  when 'procedure' then
    -- procedure / function
    foreach select b.data
              into v_proc_text
              from sysprocedures p, sysprocbody b
             where p.procid = b.procid
               and p.procname = v_tabname
               and b.datakey = 'T'
             order by b.seqno
      let v_ddl = v_ddl || v_proc_text;
    end foreach;

  when 'synonym' then 
    -- SYNONYM, local OR remote
	foreach select nvl(syn.tabname, s.dbname || '@' || nvl(s.servername,DBSERVERNAME) || ':' || s.tabname) AS rtabname
	          into v_synonym_tabname
	          from syssyntable s LEFT JOIN systables syn
	            ON s.btabid = syn.tabid, systables t1 
	         WHERE s.tabid = t1.tabid
	           AND t1.tabname = v_tabname
      let v_ddl = 'CREATE SYNONYM ' || v_tabname || ' FOR ' || v_synonym_tabname || ';';			   
	end foreach;
  
  else
    let v_ddl = 'NOT_SUPPORTED';
end case;
  return v_ddl;
end PROCEDURE;


/*****************************************************
 * PROCEDURE: proc_idxsql(idxname)
 * DESCRIPTION: build index sql from idxname
 * USAGE: call proc_idxsql(idxname);
 * WRITE BY: liaosnet@gbasedbt.com 2023-12-19
 *****************************************************/
DROP PROCEDURE IF EXISTS proc_idxsql(varchar);
CREATE PROCEDURE proc_idxsql(p_idxname varchar(128)) returning lvarchar(1024);
  define v_idxsql lvarchar(1024);
  define v_idx_typename varchar(20);
  define v_tabid int;
  define v_tabname varchar(128);
  define v_idxtype char(1);
  define v_idxcluster char(1);
  define v_idxowner varchar(128);
  define v_idxkeys varchar(128);
  define v_idxkeyarr varchar(128);
  define v_funcname varchar(128);
  define v_procid varchar(128);
  define v_start int;
  define v_end   int;
  define v_colno int;
  define v_colname varchar(128);
  define curr int;
  define tmp varchar(128);
  define v_desc varchar(10);
  
  ON EXCEPTION
    RETURN NULL;
  END EXCEPTION;
  
  -- GET INDEXKEYS
  SELECT t.tabid, t.tabname, i.owner,i.idxtype,clustered, i.indexkeys::lvarchar
  INTO v_tabid, v_tabname, v_idxowner, v_idxtype, v_idxcluster,v_idxkeys
  FROM sysindices i,systables t
  WHERE i.tabid = t.tabid AND i.idxname = p_idxname;
  
  let v_idx_typename = '';
  if v_idxtype = 'U' then
    let v_idx_typename = 'UNIQUE';
  elif v_idxcluster = 'C' then
    let v_idx_typename = 'CLUSTER';
  end if;
  LET v_idxsql = 'CREATE ' || v_idx_typename;
  LET v_idxsql = rtrim(v_idxsql) || ' INDEX ' || chr(34) || trim(v_idxowner) || chr(34) || '.' || p_idxname || ' ON ' || v_tabname || ' (' ;
  
  -- process IDXKEYS
  let v_funcname = '';
  IF substr(v_idxkeys,1,1) = '<' THEN
    let v_start = 2;
    let v_end = instr(v_idxkeys,'>');
    let v_procid = substr(v_idxkeys, v_start, v_end - v_start);
    SELECT procname INTO v_funcname
    FROM sysprocedures
    WHERE procid = v_procid::int;
    let v_start = instr(v_idxkeys,'(') + 1;
    let v_end = instr(v_idxkeys,')');
    -- 1,2,
    let v_idxkeys = substr(v_idxkeys, v_start, v_end - v_start) || ',';
    
    let tmp = v_funcname || '(';
    while v_idxkeys != '' 
      let curr = instr(v_idxkeys, ',');
      let v_colno = substr(v_idxkeys, 1, curr - 1)::int;
      SELECT colname
      INTO v_colname
      FROM syscolumns
      WHERE tabid = v_tabid
      AND colno = v_colno;
      let tmp = tmp || v_colname || ',';
      let v_idxkeys = substr(v_idxkeys, curr + 1);
    END while;
    let tmp = rtrim(tmp,',') || ')';

  ELSE
    let v_idxkeys = replace(v_idxkeys, ' [1]') || ',';
    let tmp = '';
    let v_desc = '';
    while v_idxkeys != '' 
      let curr = instr(v_idxkeys, ',');
      let v_colno = substr(v_idxkeys, 1, curr - 1)::int;
      IF v_colno < 0 THEN
        let v_desc = ' DESC';
      END IF;
      SELECT colname
      INTO v_colname
      FROM syscolumns
      WHERE tabid = v_tabid
      AND colno = abs(v_colno);
      let tmp = tmp || v_colname || v_desc || ',';
      let v_idxkeys = substr(v_idxkeys, curr + 1);
    END while;
    let tmp = rtrim(tmp,',');   
  
  END IF;
  
  LET v_idxsql = v_idxsql || tmp || ');';   
  RETURN v_idxsql;
END PROCEDURE;
