使用 Informix 系统目录(1)

SELECT data, seqno 
  FROM sysprocbody a, sysprocedures b
 WHERE a.procid = b.procid
   AND b.procname = ?
   AND datakey = 'T'
 ORDER BY seqno

SELECT procname 
  FROM sysprocedures a, sysprocplan b
 WHERE a.procid = b.procid
   AND created < today - 7;

SELECT a.tabname, constrname, d.tabname
  FROM systables a, sysconstraints b, sysreferences c,
       systables d
 WHERE b.constrtype = 'R'
   AND a.tabid = b.tabid
   AND b.constrid = c.constrid
   AND c.ptabid = d.tabid
   AND a.tabname = ?;

SELECT a.tabname, constrname, d.tabname, e.state
  FROM systables a, sysconstraints b, sysreferences c,
       systables d, sysobjstate e
 WHERE b.constrtype = 'R'
   AND a.tabid = b.tabid
   AND b.constrid = c.constrid
   AND b.constrname = e.name
   AND c.ptabid = d.tabid
   AND a.tabname = ?;

SELECT a.constrname, a.constrtype, b.tabname, c.colname,
       d.checktext
  FROM sysconstraints a, systables b, syscolumns c, 
       syschecks d, syscoldepend e
 WHERE a.constrid = d.constrid
   AND a.constrid = e.constrid
   AND e.tabid = b.tabid
   AND e.colno = c.colno
   AND b.tabid = c.tabid
   AND d.type = 'C'

#############################################################
# Convert coltype/length into an SQL descriptor string
#############################################################
FUNCTION col_cnvrt(coltype, collength)

   DEFINE coltype, collength, NONULL SMALLINT,
          SQL_strg CHAR(40),
  tmp_strg CHAR(4)

   LET coltype = coltype + 1  # datatype[] is offset by one
   LET NONULL  = coltype/256  # if > 256 then is NO NULLS
   LET coltype = coltype MOD 256  # lose the NO NULLS determinator

   LET SQL_strg = datatype[coltype] # basic datatype

   CASE coltype

      WHEN 1# char
       LET tmp_strg = collength using "<<<<"
       LET SQL_strg = SQL_strg clipped, " (", tmp_strg clipped, ")"

# SQL syntax supports float(n) - Informix ignores this
# WHEN 4    # float
#  LET SQL_strg = SQL_strg clipped, " (", ")"

  WHEN 6    # decimal
   LET SQL_strg = SQL_strg clipped, " (", fix_nm(collength,0) clipped, ")"

# Syntax supports serial(starting_no) - starting_no is unavaliable
#  WHEN 7    # serial
#  LET SQL_strg = SQL_strg clipped, " (", ")"

   WHEN 9    # money
   LET SQL_strg = SQL_strg clipped, " (", fix_nm(collength,0) clipped, ")"

  WHEN 11 # datetime
   LET SQL_strg = SQL_strg clipped, " ", fix_dt(collength) clipped 

  WHEN 14 # varchar
   LET SQL_strg = SQL_strg clipped, " (", fix_nm(collength,1) clipped, ")"

  WHEN 15 # interval
   LET SQL_strg = SQL_strg clipped, " ", fix_dt(collength) clipped

   END CASE

 IF NONULL THEN
  LET SQL_strg = SQL_strg clipped, " NOT NULL"
 END IF
      
 RETURN SQL_strg

END FUNCTION

##############################################################
# Turn collength into two numbers - return as string
##############################################################
FUNCTION fix_nm(num,tp)

DEFINE num integer,
       tp smallint,
       strg CHAR(8),
       i, j   SMALLINT,
       strg1, strg2 char(3)

   LET i = num / 256
   LET j = num MOD 256
   LET strg1 = i using "<<&"
   LET strg2 = j using "<<&"
   IF tp = 0 THEN
      IF j > i THEN
         LET strg = strg1 clipped
      ELSE
         LET strg = strg1 clipped, ", ", strg2 clipped
      END IF
   ELSE# varchar is just the opposite
      IF i = 0 THEN
         LET strg = strg2 clipped
      ELSE
         LET strg = strg2 clipped, ", ", strg1 clipped
      END IF
   END IF

   RETURN strg

END FUNCTION
##############################################################
# Turn collength into meaningful date info - return as string
##############################################################
FUNCTION fix_dt(num)
DEFINE num integer,
       i, j, k, len   SMALLINT,
       strg CHAR(30)

   LET i   = (num mod 16) + 1# offset again
   LET j   = ((num mod 256) / 16) + 1# offset again
   LET k   = num / 256# length of value

# If this is an interval then life gets interesting, 'k' is 
# the length of the entire string.  So a YEAR TO DAY is 
# YYYYMMDD or 8.  A DAY(3) TO MINUTE is DDDHHMM or 7.  
# We don't know how long the first one is, but 
# we can work it out by computing the 'should 
# be length' of the string and then adding/subtracting 
# the result from the 'should be length' of 
# the major element.
#
# Keep in mind --->    YYYYMMDDHHMMSSFFFFF
#     vs.         j =    1  2 3 4 5 678901
#
# I was just working an algorithm to do this, 4 
# notepads, 90 minutes, and 50 lines into it I realized
# that I was creating something impossible to test or 
# maintain.  Therefore I am opting for something a lot simpler.
#
# In the globals I have created an ARRAY of RECORD with 
# start and end points for the major and minor pieces.  
# By subtracting the START point of the 
# major element from the END point of the minor element
#  I get the 'should be length'
#

LET len = intvray[i].end_point - intvray[j].start_point

# len should match k.e.g.:
#DAY(5) TO MINUTE  ==> k = 9, len = 6
#YEAR(6) TO HOUR   ==> k = 12, len = 14

   LET len = k - len    # add len to the major

   IF len = 0 OR j > 11 THEN   # is the default
# 12 on have the precision already coded

    LET strg = datetype[j] clipped, " TO ", datetype[i] clipped

   ELSE# isn't the default

# uh-oh, how long IS the default major?
   LET k = intvray[j].end_point - intvray[j].start_point 

# add in the extra
   LET k = k + len

   LET strg = datetype[j] clipped, "(", k using "<<", ")", " TO ", 
                 datetype[i] clipped
   
   END IF

   RETURN strg

END FUNCTION
#############################################
Required global definitions:
   DEFINE  datatype ARRAY[40] OF CHAR(20), # for coltype conversions
           datetype ARRAY[16] OF CHAR(11), 
           intvray  ARRAY[16] OF RECORD
              start_point SMALLINT,
              end_point SMALLINT
           END RECORD

   LET datatype[1]  = "CHAR"
   LET datatype[2]  = "SMALLINT"
   LET datatype[3]  = "INTEGER"
   LET datatype[4]  = "FLOAT"
   LET datatype[5]  = "SMALLFLOAT"
   LET datatype[6]  = "DECIMAL"
   LET datatype[7]  = "SERIAL"
   LET datatype[8]  = "DATE"
   LET datatype[9]  = "MONEY"
   LET datatype[10] = "UNKNOWN"
   LET datatype[11] = "DATETIME"
   LET datatype[12] = "BYTE"
   LET datatype[13] = "TEXT"
   LET datatype[14] = "VARCHAR"
   LET datatype[15] = "INTERVAL"
   LET datatype[16] = "NCHAR"
   LET datatype[17] = "NVARCHAR"
   LET datatype[18] = "UNKNOWN"# little room for growth
   LET datatype[19] = "UNKNOWN"
   LET datatype[20] = "UNKNOWN"

   LET datetype[1] = "YEAR"
   LET intvray[1].start_point = 1
   LET intvray[1].end_point = 5# offset by one for easy math

   LET datetype[3] = "MONTH"
   LET intvray[3].start_point = 5
   LET intvray[3].end_point = 7

   LET datetype[5] = "DAY"
   LET intvray[5].start_point = 7
   LET intvray[5].end_point = 9

   LET datetype[7] = "HOUR"
   LET intvray[7].start_point = 9
   LET intvray[7].end_point = 11

   LET datetype[9] = "MINUTE"
   LET intvray[9].start_point = 11
   LET intvray[9].end_point = 13

   LET datetype[11] = "SECOND"
   LET intvray[11].start_point = 13
   LET intvray[11].end_point = 15

   LET datetype[12] = "FRACTION(1)"
   LET intvray[12].start_point = 15
   LET intvray[12].end_point = 16

   LET datetype[13] = "FRACTION(2)"
   LET intvray[13].start_point = 16
   LET intvray[13].end_point = 17

   LET datetype[14] = "FRACTION(3)"
   LET intvray[14].start_point = 17
   LET intvray[14].end_point = 18

   LET datetype[15] = "FRACTION(4)"
   LET intvray[15].start_point = 18
   LET intvray[15].end_point = 19

   LET datetype[16] = "FRACTION(5)"
   LET intvray[16].start_point = 19
   LET intvray[16].end_point = 20