Home
Objecting to databases
Part 4: Flirting with the unknown.
by Bill Schindler
In this article, we'll "punch a hole through" the code to make the database access classes work according to the design that was shown originally in the first article in this series. "Punching a hole" through means implementing enough code to make a program work front-to-back. The bells, whistles, and complete functionality are added later.
Don't column us
In order to "punch the hole" through, two additional classes are needed. The first class, Column, encapsulates the definition of a column (or field) in the database.
Since I'm the quintessential lazy programmer, I hate having to update class definitions every time the database changes. So, rather than making the consumer of the Column class build a new definition each time the database changes, the Column class can dynamically gather its information from the database. Just to cover the possibilities, the column defintion can also be set "manually."
Since most databases have their own proprietary methods for querying column and table definitions, the Column class is somewhat database specific. (This example works with MySQL.)
The Column class tracks the column specific information by storing the data in a Directory (see lines 11-20 in the code listing). The most commonly used information is the name of the column. Since different
databases use different names for the column name, the Column class
hides this minor complexity by providing a colname method (lines
24-26).
Notice that Column includes a class method (line 56 in the listing)
named columnsForTable. This is a worker method that queries the
database for column definitions and returns an array of Column
objects. This method is used internally by the SQLTable class
(described next) to dynamically determine the columns in a table.
What a table
The second new class is SQLTable. In a database, a table is a
collection of columns. SQLTable mimics this by maintaining a
collection of Column objects.
SQLTable's init method is where most of the heavy lifting happens.
The class maintains lots of internal values (many of which are not
used yet) and it takes some initialization work to get everything set
up. This is almost always true of any class that tries to build and
maintain most of its state information dynamically.
The two parameters that are required by init are the Database object
and the name of the table (line 84). The remaining arguments are
optional, since they're used for explicitly setting up column
definitions.
In lines 86-89, several key values are initialized. The !stmt tracks
the statement ID returned by Database. The !tabname is a copy of the
table name, maintained because some databases use two different forms
of the table name. The !select stores the SQL select statement--its
value is built up when the Column object array is created. Finally, !d
is used to store the values retrieved from the current row of data
when the database is being accessed.
The array of Column objects is retrieved through a call to
.column~columnsForTable (line 108). From there, !d and !select are
fully initialized.
Although about half of SQLTable's code is involved in setting up the collection of columns, most of SQLTable's interface is concerned with accessing the database. This includes the first and next methods and column access methods.
If you've looked at the code listing, you're probably wondering where the column access methods are. Well, those are all unknown....
Into the unknown
Sometimes, you'd like to be able to design classes that can be extended without writing lots of -- or any -- new code. These "self-extending" classes are almost impossible to do in C++ or Java, but Object Rexx makes the job fairly simple.
Every class in Object Rexx has a method named "unknown." The unknown method gets called whenever a non-existent (i.e. unknown) method is called on a class. The default job of the unknown method is to generate an error message. You can define your own unknown method for a class and use it to do whatever special processing you need.
The most common use of unknown is to dynamically add methods to a
class. In this capacity, unknown stands in for attributes--methods used
to get and set values stored by the class.
By creating an unknown method for SQLTable, we avoid any need to
define one-off classes for every table in the database.
Look at the unknown method in the code listing on lines 123-138. On
line 125, you'll see that the parameters passed to the method are the
message name (msgname) and any arguments for the message (msgargs).
Since SQLTable is designed to act like a Directory, it allows the
columns to be accessed using either square brackets (e.g.
myTable["FIRSTNAME"]) or as if the column name was defined as a method
on SQLTable (e.g. myTable~firstname). The unknown method implements
both access methods by examining the message name.
On line 126, the code attempts to get the column name that's being requested. This works when the access is via the twiddle (the method call version). When it's being accessed with square brackets, the name is stored in the message arguments. In that case, the code in lines 128-131 get the name, depending on whether this is a get or a set request.
Once the column name is retrieved, the code determines if this is an
assignment (line 133) by checking the rightmost character of the
message name. If that character is an equal sign (=), it's an
assignment. Otherwise, it's a get request. Data is then either stored
in or retrieved from SQLTable's internally maintained Directory !d.
The Column class also has an unknown method. This method is used for
setting and accessing the database specific information stored in
Column. Its main purpose is to simplify modifying Column for use with
other databases.
Making the doughnuts
To wrap things up, here's an example of the database classes in action:
/* Database access example */
db = .database~new("warptech")
names = .sqlTable~new(db, "AssocPerson", .nil, .nil)
ok = names~first
DO WHILE ok
.output~lineout(names~firstname names~lastname)
ok = names~next
END
RETURN 0
::requires "Database.rxx"
That's all the code needed to generate a list of names from the WarpTech database! (You won't be able to run this code as-is, since you don't have access to the WarpTech database.)
From here, you can add functionality to make these classes even more
powerful. Add set/get methods for SQLTable's !where and !order in
order to control what rows are selected and how the result is sorted.
You might also add a count method or a method to retrieve all selected
rows into an array rather than processing them one at a time.
From here, it's yours. Enjoy!
The code
1: /************************ Column ************************
2: */
3: ::class Column PUBLIC
4:
5: /*======================== init =========================
6: */
7: ::method init
8: EXPOSE !info
9: USE ARG tabname, colname
10:
11: !info = .directory~new
12: !info['TABNAME'] = tabname
13: !info['FIELD'] = colname
14: !info['TYPE'] = ''
15: !info['LENGTH'] = ''
16: !info['SCALE'] = ''
17: !info['DEFAULT'] = ''
18: !info['NULL'] = ''
19: !info['KEY'] = ''
20: !info['EXTRA'] = ''
21:
22: /*======================= colname =======================
23: */
24: ::method colname
25: EXPOSE !info
26: RETURN !info~field
27:
28: /*======================= unknown =======================
29: */
30: ::method unknown
31: EXPOSE !info
32: USE ARG msgname, msgargs
33: isAssign = (msgname~right(1) = '=')
34: PARSE VAR msgname msg '=' .
35: IF isAssign THEN
36: DO
37: IF msgname = '[]=' THEN
38: DO
39: IF msgargs[2] = 'TYPE' THEN
40: self~type = msgargs[1]
41: ELSE
42: !info[msgargs[2]] = msgargs[1]
43: END
44: ELSE
45: DO
46: !info[msg] = msgargs[1]
47: END
48: END
49: ELSE IF !info~hasIndex(msgname) THEN
50: RETURN !info[msgname]
51: ELSE
52: RETURN msg
53:
54: /*=================== columnsForTable ===================
55: */
56: ::method columnsForTable CLASS
57: USE ARG !db, tabname
58:
59: s = "SHOW COLUMNS FROM" !db~dbname || '.' || tabname
60:
61: inf = .directory~new
62: cols = .array~new
63: !db~openCursor(s)
64: DO i = 1 WHILE !db~fetch(inf)
65: cols[i] = .column~new(tabname, inf~field)
66: DO n OVER inf
67: cols[i][n] = inf[n]
68: inf[n] = ''
69: END
70: END
71: !db~closeCursor
72:
73: RETURN cols
74:
75: /*********************** SQLTable ***********************
76: */
77: ::class SQLTable PUBLIC
78:
79: /*======================== init =========================
80: */
81: ::method init
82: EXPOSE !db !select !tbl !key !tabname !d !cols !order ,
83: !where !stmt
84: USE ARG !db, !tbl, !c, !key
85:
86: !stmt = ''
87: !tabname = !tbl
88: !select = 'SELECT'
89: !d = .directory~new
90: delim = ' '
91:
92: IF !c <> .nil THEN
93: DO
94: i = 1
95: !cols = .array~new
96: DO n OVER !c
97: colNm = n~word(1)
98: !cols[i] = .column~new(!tabname, colNm)
99: !cols[i]~definition = n
100: !d[colNm~translate] = ''
101: !select = !select || delim || colNm
102: delim = ', '
103: i = i + 1
104: END
105: END
106: ELSE
107: DO
108: !cols = .column~columnsForTable(!db, !tabname)
109: DO i = 1 TO !cols~items
110: colNm = !cols[i]~field
111: !d[colNm~translate] = ''
112: !select = !select || delim || colNm
113: delim = ', '
114: END
115: END
116:
117: !select = !select 'FROM' !tbl
118: !where = ''
119: !order = ''
120:
121: /*======================= unknown =======================
122: */
123: ::method unknown
124: EXPOSE !d
125: USE ARG msgname, msgargs
126: PARSE VAR msgname msg '=' .
127:
128: IF msgname = '[]' THEN
129: msg = msgargs[1]
130: ELSE IF msgname = '[]=' THEN
131: msg = msgargs[2]
132:
133: IF msgname~right(1) = '=' THEN
134: !d[msg] = msgargs[1]
135: ELSE IF !d~hasIndex(msg) THEN
136: RETURN !d[msg]
137: ELSE
138: RETURN msg
139:
140: /*======================== first ========================
141: */
142: ::method first
143: EXPOSE !db !d !select !where !order !stmt
144: DO n OVER !d
145: !d[n] = ''
146: END
147: IF !stmt <> '' THEN
148: !db~closeCursor(!stmt)
149: !stmt = .database~statementID
150: !db~openCursor(!select !where !order, !stmt)
151: v = !db~fetch(self, !stmt)
152: RETURN v
153:
154: /*======================== next =========================
155: */
156: ::method next
157: EXPOSE !db !d !stmt
158: DO n OVER !d
159: !d[n] = ''
160: END
161: IF !stmt = '' THEN
162: RETURN .false
163: v = !db~fetch(self, !stmt)
164: IF \ v THEN
165: DO
166: !db~closeCursor(!stmt)
167: !stmt = ''
168: END
169: RETURN v
