summaryrefslogtreecommitdiff
path: root/docs/sqlmap/latex/tut3.tex
blob: 4e16ab28bd297a730e8f8e98b8a774b083df9822 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
\section{Test, test, again ...}
Of course, tweaking the Person List display is not going to be the end of it.
Clients always want more, and now ours wants to edit, add, or delete records.
Let's write some tests for these new tasks, as shown in
Example~\ref{example:9}.

\begin{example}\label{example:9}
New stories, new tests
\begin{verbatim}
    function testPersonUpdate()
    {
        $expect = "wei";
        $edited = "Nah";

        //get it;
        $person = TMapper::instance()->queryForObject("Select", 1);

        //test it
        $this->assertNotNull($person);
        $this->assertEqual($expect, $person->FirstName);

        //change it
        $person->FirstName = $edited;
        TMapper::instance()->update("Update", $person);

        //get it again
        $person = TMapper::instance()->queryForObject("Select", 1);

        //test it
        $this->assertEqual($edited, $person->FirstName);

        //change it back
        $person->FirstName = $expect;
        TMapper::instance()->update("Update", $person);
    }

    function testPersonDelete()
    {
        //insert it
        $person = new Person;
        $person->ID = -1;
        TMapper::instance()->insert("Insert", $person);

        //delte it
        $count = TMapper::instance()->delete("Delete", -1);
        $this->assertEqual(1, $count);
    }
\end{verbatim}
\end{example}

Not the best tests ever written, but for now, they will do :)

To make the new tests work, we'll need some new mapping statements.
Example~\ref{example:10} shows the complete mapper document that we've called
\tt{personHelper.xml}.

\begin{example}
The new and improved mapper document
\begin{verbatim}
<?xml version="1.0" encoding="utf-8" ?>

<sqlMap Name="PersonHelper">
  <select id="Select" parameterClass="int" resultClass="Person">
   select
    PER_ID as ID,
    PER_FIRST_NAME as FirstName,
    PER_LAST_NAME as LastName,
    PER_BIRTH_DATE as BirthDate,
    PER_WEIGHT_KG as WeightInKilograms,
    PER_HEIGHT_M as HeightInMeters
    from PERSON
    WHERE
      PER_ID = #value#
  </select>

  <insert id="Insert" parameterClass="Person">
   insert into PERSON
    (PER_ID, PER_FIRST_NAME, PER_LAST_NAME,
    PER_BIRTH_DATE, PER_WEIGHT_KG, PER_HEIGHT_M)
   values
    (#ID#, #FirstName#, #LastName#,
    #BirthDate#, #WeightInKilograms#, #HeightInMeters#)
  </insert>

  <update id="Update" parameterClass="Person">
   update PERSON set
    PER_FIRST_NAME = #FirstName#,
    PER_LAST_NAME = #LastName#,
    PER_BIRTH_DATE = #BirthDate#,
    PER_WEIGHT_KG = #WeightInKilograms#,
    PER_HEIGHT_M = #HeightInMeters#
   where PER_ID = #ID#
  </update>

  <delete id="Delete" parameterClass="int">
   delete from PERSON
   where PER_ID = #value#
  </delete>
</sqlMap>
\end{verbatim}
\end{example}
Well, waddya know, if run our tests now, we are favored with a green bar!. It
all works!

\begin{mybox}{Note:}
Though, of course, things usually do not work perfectly the first time! We
have to fix this and that, and try, try, again. But SimpleTest makes trying
again quick and easy. You can changes to the XML mapping documents and rerun
the tests! No muss, no fuss.
\end{mybox}

Turning back to our Prado page, we can revamp the TDataGrid to allow in-place
editing and deleting. To add records, we provide a button after the grid that
inserts a blank person for client to edit. The page code is shown as
Example~\ref{example:11}.
\begin{example}\label{example:11}
Prado page code for our enhanced TDataGrid
\begin{verbatim}
    <com:TDataGrid id="personList"
            DataKeyField="ID"
            AutoGenerateColumns="False"
            OnEditCommand="editPerson"
            OnUpdateCommand="updatePerson"
            OnCancelCommand="refreshList"
            OnDeleteCommand="deletePerson">
        <com:TBoundColumn DataField="FirstName" HeaderText="First Name" />
        <com:TBoundColumn DataField="LastName" HeaderText="Last Name" />
        <com:TBoundColumn DataField="HeightInMeters" HeaderText="Height" />
        <com:TBoundColumn DataField="WeightInKilograms" HeaderText="Weight" />
        <com:TEditCommandColumn
                HeaderText="Edit"
                UpdateText="Save" />
        <com:TButtonColumn
                HeaderText="Delete"
                Text="Delete"
                CommandName="delete"/>
    </com:TDataGrid>
    <com:TButton Text="Add" OnClick="addNewPerson" />
\end{verbatim}
\end{example}

Example~\ref{example:12} shows the corresponding methods from page PHP class.

\begin{example}\label{example:12}
The page class code for our enhanced TDataGrid
\begin{verbatim}
    private function sqlmap()
    {
        return $this->Application->getModule('SQLMap')->getClient();
    }

    private function loadData()
    {
        $this->personList->DataSource =
                $this->sqlmap()->queryForList('SelectAll');
        $this->personList->dataBind();
    }

    public function onLoad($param)
    {
        if(!$this->IsPostBack)
            $this->loadData();
    }

    protected function editPerson($sender,$param)
    {
        $this->personList->EditItemIndex=$param->Item->ItemIndex;
        $this->loadData();
    }

    protected function deletePerson($sender, $param)
    {
        $id = $this->getKey($sender, $param);
        $this->sqlmap()->update("Delete", $id);
        $this->loadData();
    }

    protected function updatePerson($sender, $param)
    {
        $person = new Person();
        $person->FirstName = $this->getText($param, 0);
        $person->LastName = $this->getText($param, 1);
        $person->HeightInMeters = $this->getText($param, 2);
        $person->WeightInKilograms = $this->getText($param, 3);
        $person->ID = $this->getKey($sender, $param);
        $this->sqlmap()->update("Update", $person);
        $this->refreshList($sender, $param);
    }

    protected function addNewPerson($sender, $param)
    {
        $person = new Person;
        $person->FirstName = "-- New Person --";
        $this->sqlmap()->insert("Insert", $person);
        $this->loadData();;
    }

    protected function refreshList($sender, $param)
    {
        $this->personList->EditItemIndex=-1;
        $this->loadData();
    }

    private function getText($param, $index)
    {
        $item = $param->Item;
        return $item->Cells[$index]->Controls[0]->Text;
    }

    private function getKey($sender, $param)
    {
        return $sender->DataKeys[$param->Item->DataSourceIndex];
    }
\end{verbatim}
\end{example}

OK, we are CRUD complete! There's more we could do here. In particular, we
should add validation methods to prevent client from entering alphabetic
characters where only numbers can live. But, that's a different Prado
tutorial, and this is an SQLMap DataMapper tutorial.

\begin{figure}[!h]
    \centering
        \includegraphics[width=0.75\textwidth]{grid2}
    \caption{Person List CRUD}
    \label{figure:2}
\end{figure}