From c7d41e5bea4a5f96979a08da9cc9f79355edfe70 Mon Sep 17 00:00:00 2001 From: wei <> Date: Sun, 16 Jul 2006 06:19:36 +0000 Subject: Update Time Tracker demo. --- demos/sqlmap/protected/pages/Home.page | 1 - demos/sqlmap/protected/pages/Manual/Layout.php | 7 + demos/sqlmap/protected/pages/Manual/Layout.tpl | 45 ++++ demos/sqlmap/protected/pages/Manual/Overview.page | 10 +- demos/sqlmap/protected/pages/Manual/TopicList.php | 8 + demos/sqlmap/protected/pages/Manual/TopicList.tpl | 66 ++++++ .../protected/pages/Manual/Tutorial/TestAgain.page | 214 ++++++++++++++++++ .../protected/pages/Manual/Tutorial/TestFirst.page | 238 +++++++++++++++++++++ .../pages/Manual/Tutorial/TestSecond.page | 116 ++++++++++ .../protected/pages/Manual/Tutorial/example1.png | Bin 0 -> 236887 bytes .../protected/pages/Manual/Tutorial/grid1.png | Bin 0 -> 275250 bytes .../protected/pages/Manual/Tutorial/grid2.png | Bin 0 -> 218210 bytes .../sqlmap/protected/pages/Tutorial/TestAgain.page | 214 ------------------ .../sqlmap/protected/pages/Tutorial/TestFirst.page | 238 --------------------- .../protected/pages/Tutorial/TestSecond.page | 116 ---------- demos/sqlmap/protected/pages/Tutorial/example1.png | Bin 236887 -> 0 bytes demos/sqlmap/protected/pages/Tutorial/grid1.png | Bin 275250 -> 0 bytes demos/sqlmap/protected/pages/Tutorial/grid2.png | Bin 218210 -> 0 bytes demos/sqlmap/protected/pages/config.xml | 8 - 19 files changed, 703 insertions(+), 578 deletions(-) delete mode 100644 demos/sqlmap/protected/pages/Home.page create mode 100644 demos/sqlmap/protected/pages/Manual/Layout.php create mode 100644 demos/sqlmap/protected/pages/Manual/Layout.tpl create mode 100644 demos/sqlmap/protected/pages/Manual/TopicList.php create mode 100644 demos/sqlmap/protected/pages/Manual/TopicList.tpl create mode 100644 demos/sqlmap/protected/pages/Manual/Tutorial/TestAgain.page create mode 100644 demos/sqlmap/protected/pages/Manual/Tutorial/TestFirst.page create mode 100644 demos/sqlmap/protected/pages/Manual/Tutorial/TestSecond.page create mode 100644 demos/sqlmap/protected/pages/Manual/Tutorial/example1.png create mode 100644 demos/sqlmap/protected/pages/Manual/Tutorial/grid1.png create mode 100644 demos/sqlmap/protected/pages/Manual/Tutorial/grid2.png delete mode 100644 demos/sqlmap/protected/pages/Tutorial/TestAgain.page delete mode 100644 demos/sqlmap/protected/pages/Tutorial/TestFirst.page delete mode 100644 demos/sqlmap/protected/pages/Tutorial/TestSecond.page delete mode 100644 demos/sqlmap/protected/pages/Tutorial/example1.png delete mode 100644 demos/sqlmap/protected/pages/Tutorial/grid1.png delete mode 100644 demos/sqlmap/protected/pages/Tutorial/grid2.png delete mode 100644 demos/sqlmap/protected/pages/config.xml (limited to 'demos/sqlmap/protected/pages') diff --git a/demos/sqlmap/protected/pages/Home.page b/demos/sqlmap/protected/pages/Home.page deleted file mode 100644 index ff226d4e..00000000 --- a/demos/sqlmap/protected/pages/Home.page +++ /dev/null @@ -1 +0,0 @@ -

Welcome to Prado!

\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/Layout.php b/demos/sqlmap/protected/pages/Manual/Layout.php new file mode 100644 index 00000000..e612d52d --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/Layout.php @@ -0,0 +1,7 @@ + \ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/Layout.tpl b/demos/sqlmap/protected/pages/Manual/Layout.tpl new file mode 100644 index 00000000..7c47646d --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/Layout.tpl @@ -0,0 +1,45 @@ + + + + + + + + + + + + + +Home | +PradoSoft.com | +PDF Version | + + + + + + + + +
+ + +
+ +
+
+ + + +
+ + \ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/Overview.page b/demos/sqlmap/protected/pages/Manual/Overview.page index 694daf68..258be782 100644 --- a/demos/sqlmap/protected/pages/Manual/Overview.page +++ b/demos/sqlmap/protected/pages/Manual/Overview.page @@ -34,7 +34,15 @@ material applies to both implementations. SQLMap PHP Developer Guide.

A Tutorial is also available. We recommend reviewing the Tutorial for your -platform before reading this Guide.

+platform before reading this Guide. + +SQLMap Tutorial + +

Support

diff --git a/demos/sqlmap/protected/pages/Manual/TopicList.php b/demos/sqlmap/protected/pages/Manual/TopicList.php new file mode 100644 index 00000000..ce827cc0 --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/TopicList.php @@ -0,0 +1,8 @@ + \ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/TopicList.tpl b/demos/sqlmap/protected/pages/Manual/TopicList.tpl new file mode 100644 index 00000000..58e6d9ae --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/TopicList.tpl @@ -0,0 +1,66 @@ +
+ +
+
Introduction
+ + +
Installation and Setup
+ + +
SQLMap for PHP Tutorial
+ + +
Using SQLMap DataMapper
+ + +
Working with Data Maps
+ + +
Parameter Maps and Inline Parameters
+ + +
Result Maps
+ + +
Advanced Topics
+ + +
+ + + +
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/Tutorial/TestAgain.page b/demos/sqlmap/protected/pages/Manual/Tutorial/TestAgain.page new file mode 100644 index 00000000..4adac73b --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/Tutorial/TestAgain.page @@ -0,0 +1,214 @@ + +

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 the following.

+ + +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); +} + + +

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. +The following sample shows the complete mapper document that we've called +personHelper.xml.

+ + + + + + + + + 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#) + + + + 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# + + + + delete from PERSON + where PER_ID = #value# + + + + +

Well, waddya know, if run our tests now, we are favored with a green bar!. It +all works!

+ +
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. +
+ +

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: + + + <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" /> + + +

The following sample shows the corresponding methods from page PHP class.

+ + + 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]; + } + + +

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.

+ + class="figure" /> +
Figure 4: Person List CRUD
+ +
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/Tutorial/TestFirst.page b/demos/sqlmap/protected/pages/Manual/Tutorial/TestFirst.page new file mode 100644 index 00000000..80a155cb --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/Tutorial/TestFirst.page @@ -0,0 +1,238 @@ + +

Test First!

+ +

Let's say that our most important client has a database and one of the tables +in the database is a list of people. Our client tells us:

+ +

"We would like to use a web application to display the people in this table +and to add, edit, and delete individual records."

+ +

Not a complicated story, but it will cover the CRUD most developers want to +learn first. :) Let's start with the people table that the client mentioned. +Since we're keeping it simple, we'll say it's a table in an Access database. +The table definition is shown as:

+ + +Name Type Size +PER_ID Long Integer 4 +PER_FIRST_NAME Text 40 +PER_LAST_NAME Text 40 +PER_BIRTH_DATE Date/Time 8 +PER_WEIGHT_KG Double 8 +PER_HEIGHT_M Double 8 + + +
Tip: + This example is bundled with a SQLite database file "Data/test.db" + that contains the Person table and some data, ready to use. +
+ +

The first thing our story says is that client would like to display a list of +people. The following example shows our test for that.

+ + +<?php +class PersonTest extends UnitTestCase +{ + function testPersonList() + { + //try it + $people = TMapper::instance()->queryForList("SelectAll"); + + //test it + $this->assertNotNull($people, "Person list is not returned"); + $this->assertTrue($people->getCount() > 0, "Person list is empty"); + $person = $people[0]; + $this->assertNotNull($person, "Person not returned"); + } +} +?> + + +

Well, the example sure looks easy enough! We ask a method to "select all", and +it returns a list of person objects. But, what code do we need to write to +pass this test?

+ +
Note: + Save the PersonTest.php into a tests directory. + The unit tests are written for the SimpleTest Unit Testing framework. +
+ +

Now, to setup the testing framework, suppose you have the SimpleTest +framework installed. Then we need to create an entry file to run the tests. +See the SimpleTest documentation for further details on setting up tests.

+ + +<?php +require_once('../tests/simpletest/unit_tester.php'); +require_once('../tests/simpletest/reporter.php'); +require_once('../SQLMap/TMapper.php'); +require_once('Models/Person.php'); + +//supress strict warnings from Adodb. +error_reporting(E_ALL); + +$test = new GroupTest('All tests'); +$test->addTestFile('Tests/PersonTest.php'); $test->run(new HtmlReporter()); +?> + + +

To run the tests, point your browser to the "run_test.php" script file +served from your web server.

+ +

Let's see. The test uses a list of person objects. We could start with a blank +object, just to satisfy the test, and add the display properties later. But +let's be naughty and skip a step. Our fully-formed person object is shown in +the following example

+ + +<?php +class Person +{ + public $ID = -1; + public $FirstName; + public $LastName; + public $WeightInKilograms = 0.0; + public $HeightInMeters = 0.0; + + private $_birthDate; + + //setters and getter for BirthDate + public function getBirthDate() + { + return $this->_birthDate; + } + + public function setBirthDate($value) + { + $this->_birthDate = $value; + } +} +?> + + +

OK, that was fun! The $this->assertXXX(...) methods are built into +UnitTestCase class. So to run the unit test example, we just need the +TMapper object and queryForList method. Wonderfully, the SQLMap +DataMapper framework has a TMapperclass built into it that will work just +fine for for us to use in this tutorial, so we don't need to write that +either.

+ +

When the TMapper->instance() method is called, an instance of the SQLMap +TSqlMapper class is returned that has various methods available such as +queryForList. In this example, the SQLMap TSqlMapper->queryForList() +method executes our SQL statement (or stored procedure) and returns the result +as a list. Each row in the result becomes an entry in the list. Along with +queryForList(), there are also delete(), insert(), +queryForObject(), queryForPagedList() and a few other methods in the +SQLMap API. + +

Looking at unit test example, we see that the queryForList() method +takes the name of the statement we want to run. OK. Easy enough. But where +does SQLMap get the "SelectAll" statement? Some systems try to generate SQL +statements for you, but SQLMap specializes in data mapping, not code +generation. It's our job (or the job of our database administrator) to craft +the SQL or provide a stored procedure. We then describe the statement in an +XML element, like the one shown the following where +we use XML elements to map a database statement to an application object. + + + + + + + + +

The SQLMap mapping documents can hold several sets of related elements, like +those shown in the unit test case example. We can also have as many mapping +documents as we need to help organize our code. Additionally, having multiple +mapping documents is handy when several developers are working on the project +at once.

+ +

So, the framework gets the SQL code for the query from the mapping, and plugs +it into a prepared statement. But, how does SQLMap know where to find the +table's datasource?

+ +

Surprise! More XML! You can define a configuration file for each datasource +your application uses. The following code shows a configuration file named "sqlmap.config" for +our SQLite database.

+ + + + + + + + + + + + + +

The <provider> specifies the database provider class, in this case +TAdodbProvider using the Adodb library. The <datasource> tag +specifies the database connection details. In this case, for an SQLite +database, we just need the driver name, and the host that points to the actual +SQLite database file.

+ +

The last part of the configuration file ("sqlMaps") is where we list our +mapping documents, like the one shown back in the previous code sample. We can +list as many documents as we need here, and they will all be read when the +configuration is parsed.

+ +

OK, so how does the configuration get parsed?

+ +

Look back at the unit test case example. The heart of the code is the call to the +"TMapper" object (under the remark "try it"). The TMapper object +is a singleton that handles the instantiation and configuration of an SQLMap +TSqlMapper object, which provides a facade to the SQLMap DataMapper +framework API.

+ +

The first time that the TMapper is called, it reads in the +sqlmap.config file and associated mapping documents to create an instance +of the TSqlMapper class. On subsequent calls, it reuses the +TSqlMapper object so that the configuration is not re-read.

+ +

The framework comes bundled with a default TMapper class for you to use +immediately to get access to the SQLMap client TSqlMapper object. If you want to use a +different name other than sqlmap.config at the default location for the +configuration file, or need to use more than one database and have one +TSqlMapper per database, you can also write your own class to mimic the role of +the Mapper class view by copying and modifying the standard version.

+ +
Tip: + You can also call TMapper::configure('/path/to/your/sqlmap.config') + to configure the TMapper for a specific configuration file. +
+ +

If we put this all together into a solution, we can "green bar" our test. At +this point you should have the following files.

+ +Data/person.xml % Mapping file. +Data/test.db % SQLite database file. + +Models/Person.php % Person class file. + +Tests/PersonTest.php % Unit test case for Person mapping. + +run_tests.php % Unit test entry point. +sqlmap.config % SQLMap configuration file. + + +

Run the tests by pointing your browser URL to the "run_tests.php" server +file.

+ + class="figure" /> +
Figure 2: Green Bar!
+ +
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/Tutorial/TestSecond.page b/demos/sqlmap/protected/pages/Manual/Tutorial/TestSecond.page new file mode 100644 index 00000000..706b5220 --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/Tutorial/TestSecond.page @@ -0,0 +1,116 @@ + + +

Playtest second!

+

Now that we have a passing test, we want to display some results as web pages. +The following examples utilize the Prado framework to display and manipulate +the database through SQLMap. Since SQLMap framework and Prado framework solve +different problems, they are both fairly independent, they can be used +together or separately.

+ +

SQLMap and Prado

+

To setup Prado, we need to create the follow files and directory structure +under our example/WebView directory.

+ +assets/ % application public assets + +protected/pages/Home.page % default page +protected/pages/Home.php % default page class +protected/runtime/ % run time data + +protected/application.xml % application configuration + +index.php % application entry point + + +

The application.xml and assets directory are not necessary but we +will make use of them later. The application.xml is used to define some +directory aliases and override the data source definitions in the +sqlmap.config. This is because SQLite database files are defined +relatively, otherwise we don't need to override the data source definitions. +The example application.xml is shown below, defining path aliases and override SQLite database +location.

+ + + + + + + + + + + + + + + + + + + +

The entry point to a Prado application in this example is index.php +and generally contains the following code.

+ + +<?php +error_reporting(E_ALL); +require_once('/path/to/prado/framework/prado.php'); +$application=new TApplication; +$application->run(); +?> + + +

Now we are ready to setup a page to display our list of people. +The following sample shows the Prado code for our display page. The key +piece is the TDataGrid. We save the file as Home.page.

+ + + + + + Person + + +<com:TForm> +

Person List

+ <com:TDataGrid id="personList"> + <com:TBoundColumn DataField="BirthDate" + HeaderText="Birth Date"/> + </com:TDataGrid> +</com:TForm> + + +
+ +

Of course, we still need to populate that TDataGrid. The following code +shows the PHP for Home.php. The operative method is loadData(). +The rest is supporting code.

+ + +<?php +Prado::using('Example.Models.Person'); +class Home extends TPage +{ + private function loadData() + { + $sqlmap = $this->Application->getModule('SQLMap')->getClient(); + $this->personList->DataSource = $sqlmap->queryForList('SelectAll'); + $this->personList->dataBind(); + } + + public function onLoad($param) + { + if(!$this->IsPostBack) + $this->loadData(); + } +} +?> + + +

If we run this now, we'll get a list like the one shown the figure below.

+ class="figure" /> +
Figure 3: A quick-and-dirty Person List
+ +
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/Tutorial/example1.png b/demos/sqlmap/protected/pages/Manual/Tutorial/example1.png new file mode 100644 index 00000000..b5241de6 Binary files /dev/null and b/demos/sqlmap/protected/pages/Manual/Tutorial/example1.png differ diff --git a/demos/sqlmap/protected/pages/Manual/Tutorial/grid1.png b/demos/sqlmap/protected/pages/Manual/Tutorial/grid1.png new file mode 100644 index 00000000..845b9581 Binary files /dev/null and b/demos/sqlmap/protected/pages/Manual/Tutorial/grid1.png differ diff --git a/demos/sqlmap/protected/pages/Manual/Tutorial/grid2.png b/demos/sqlmap/protected/pages/Manual/Tutorial/grid2.png new file mode 100644 index 00000000..dcafc33d Binary files /dev/null and b/demos/sqlmap/protected/pages/Manual/Tutorial/grid2.png differ diff --git a/demos/sqlmap/protected/pages/Tutorial/TestAgain.page b/demos/sqlmap/protected/pages/Tutorial/TestAgain.page deleted file mode 100644 index 4adac73b..00000000 --- a/demos/sqlmap/protected/pages/Tutorial/TestAgain.page +++ /dev/null @@ -1,214 +0,0 @@ - -

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 the following.

- - -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); -} - - -

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. -The following sample shows the complete mapper document that we've called -personHelper.xml.

- - - - - - - - - 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#) - - - - 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# - - - - delete from PERSON - where PER_ID = #value# - - - - -

Well, waddya know, if run our tests now, we are favored with a green bar!. It -all works!

- -
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. -
- -

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: - - - <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" /> - - -

The following sample shows the corresponding methods from page PHP class.

- - - 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]; - } - - -

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.

- - class="figure" /> -
Figure 4: Person List CRUD
- -
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Tutorial/TestFirst.page b/demos/sqlmap/protected/pages/Tutorial/TestFirst.page deleted file mode 100644 index 80a155cb..00000000 --- a/demos/sqlmap/protected/pages/Tutorial/TestFirst.page +++ /dev/null @@ -1,238 +0,0 @@ - -

Test First!

- -

Let's say that our most important client has a database and one of the tables -in the database is a list of people. Our client tells us:

- -

"We would like to use a web application to display the people in this table -and to add, edit, and delete individual records."

- -

Not a complicated story, but it will cover the CRUD most developers want to -learn first. :) Let's start with the people table that the client mentioned. -Since we're keeping it simple, we'll say it's a table in an Access database. -The table definition is shown as:

- - -Name Type Size -PER_ID Long Integer 4 -PER_FIRST_NAME Text 40 -PER_LAST_NAME Text 40 -PER_BIRTH_DATE Date/Time 8 -PER_WEIGHT_KG Double 8 -PER_HEIGHT_M Double 8 - - -
Tip: - This example is bundled with a SQLite database file "Data/test.db" - that contains the Person table and some data, ready to use. -
- -

The first thing our story says is that client would like to display a list of -people. The following example shows our test for that.

- - -<?php -class PersonTest extends UnitTestCase -{ - function testPersonList() - { - //try it - $people = TMapper::instance()->queryForList("SelectAll"); - - //test it - $this->assertNotNull($people, "Person list is not returned"); - $this->assertTrue($people->getCount() > 0, "Person list is empty"); - $person = $people[0]; - $this->assertNotNull($person, "Person not returned"); - } -} -?> - - -

Well, the example sure looks easy enough! We ask a method to "select all", and -it returns a list of person objects. But, what code do we need to write to -pass this test?

- -
Note: - Save the PersonTest.php into a tests directory. - The unit tests are written for the SimpleTest Unit Testing framework. -
- -

Now, to setup the testing framework, suppose you have the SimpleTest -framework installed. Then we need to create an entry file to run the tests. -See the SimpleTest documentation for further details on setting up tests.

- - -<?php -require_once('../tests/simpletest/unit_tester.php'); -require_once('../tests/simpletest/reporter.php'); -require_once('../SQLMap/TMapper.php'); -require_once('Models/Person.php'); - -//supress strict warnings from Adodb. -error_reporting(E_ALL); - -$test = new GroupTest('All tests'); -$test->addTestFile('Tests/PersonTest.php'); $test->run(new HtmlReporter()); -?> - - -

To run the tests, point your browser to the "run_test.php" script file -served from your web server.

- -

Let's see. The test uses a list of person objects. We could start with a blank -object, just to satisfy the test, and add the display properties later. But -let's be naughty and skip a step. Our fully-formed person object is shown in -the following example

- - -<?php -class Person -{ - public $ID = -1; - public $FirstName; - public $LastName; - public $WeightInKilograms = 0.0; - public $HeightInMeters = 0.0; - - private $_birthDate; - - //setters and getter for BirthDate - public function getBirthDate() - { - return $this->_birthDate; - } - - public function setBirthDate($value) - { - $this->_birthDate = $value; - } -} -?> - - -

OK, that was fun! The $this->assertXXX(...) methods are built into -UnitTestCase class. So to run the unit test example, we just need the -TMapper object and queryForList method. Wonderfully, the SQLMap -DataMapper framework has a TMapperclass built into it that will work just -fine for for us to use in this tutorial, so we don't need to write that -either.

- -

When the TMapper->instance() method is called, an instance of the SQLMap -TSqlMapper class is returned that has various methods available such as -queryForList. In this example, the SQLMap TSqlMapper->queryForList() -method executes our SQL statement (or stored procedure) and returns the result -as a list. Each row in the result becomes an entry in the list. Along with -queryForList(), there are also delete(), insert(), -queryForObject(), queryForPagedList() and a few other methods in the -SQLMap API. - -

Looking at unit test example, we see that the queryForList() method -takes the name of the statement we want to run. OK. Easy enough. But where -does SQLMap get the "SelectAll" statement? Some systems try to generate SQL -statements for you, but SQLMap specializes in data mapping, not code -generation. It's our job (or the job of our database administrator) to craft -the SQL or provide a stored procedure. We then describe the statement in an -XML element, like the one shown the following where -we use XML elements to map a database statement to an application object. - - - - - - - - -

The SQLMap mapping documents can hold several sets of related elements, like -those shown in the unit test case example. We can also have as many mapping -documents as we need to help organize our code. Additionally, having multiple -mapping documents is handy when several developers are working on the project -at once.

- -

So, the framework gets the SQL code for the query from the mapping, and plugs -it into a prepared statement. But, how does SQLMap know where to find the -table's datasource?

- -

Surprise! More XML! You can define a configuration file for each datasource -your application uses. The following code shows a configuration file named "sqlmap.config" for -our SQLite database.

- - - - - - - - - - - - - -

The <provider> specifies the database provider class, in this case -TAdodbProvider using the Adodb library. The <datasource> tag -specifies the database connection details. In this case, for an SQLite -database, we just need the driver name, and the host that points to the actual -SQLite database file.

- -

The last part of the configuration file ("sqlMaps") is where we list our -mapping documents, like the one shown back in the previous code sample. We can -list as many documents as we need here, and they will all be read when the -configuration is parsed.

- -

OK, so how does the configuration get parsed?

- -

Look back at the unit test case example. The heart of the code is the call to the -"TMapper" object (under the remark "try it"). The TMapper object -is a singleton that handles the instantiation and configuration of an SQLMap -TSqlMapper object, which provides a facade to the SQLMap DataMapper -framework API.

- -

The first time that the TMapper is called, it reads in the -sqlmap.config file and associated mapping documents to create an instance -of the TSqlMapper class. On subsequent calls, it reuses the -TSqlMapper object so that the configuration is not re-read.

- -

The framework comes bundled with a default TMapper class for you to use -immediately to get access to the SQLMap client TSqlMapper object. If you want to use a -different name other than sqlmap.config at the default location for the -configuration file, or need to use more than one database and have one -TSqlMapper per database, you can also write your own class to mimic the role of -the Mapper class view by copying and modifying the standard version.

- -
Tip: - You can also call TMapper::configure('/path/to/your/sqlmap.config') - to configure the TMapper for a specific configuration file. -
- -

If we put this all together into a solution, we can "green bar" our test. At -this point you should have the following files.

- -Data/person.xml % Mapping file. -Data/test.db % SQLite database file. - -Models/Person.php % Person class file. - -Tests/PersonTest.php % Unit test case for Person mapping. - -run_tests.php % Unit test entry point. -sqlmap.config % SQLMap configuration file. - - -

Run the tests by pointing your browser URL to the "run_tests.php" server -file.

- - class="figure" /> -
Figure 2: Green Bar!
- -
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Tutorial/TestSecond.page b/demos/sqlmap/protected/pages/Tutorial/TestSecond.page deleted file mode 100644 index 706b5220..00000000 --- a/demos/sqlmap/protected/pages/Tutorial/TestSecond.page +++ /dev/null @@ -1,116 +0,0 @@ - - -

Playtest second!

-

Now that we have a passing test, we want to display some results as web pages. -The following examples utilize the Prado framework to display and manipulate -the database through SQLMap. Since SQLMap framework and Prado framework solve -different problems, they are both fairly independent, they can be used -together or separately.

- -

SQLMap and Prado

-

To setup Prado, we need to create the follow files and directory structure -under our example/WebView directory.

- -assets/ % application public assets - -protected/pages/Home.page % default page -protected/pages/Home.php % default page class -protected/runtime/ % run time data - -protected/application.xml % application configuration - -index.php % application entry point - - -

The application.xml and assets directory are not necessary but we -will make use of them later. The application.xml is used to define some -directory aliases and override the data source definitions in the -sqlmap.config. This is because SQLite database files are defined -relatively, otherwise we don't need to override the data source definitions. -The example application.xml is shown below, defining path aliases and override SQLite database -location.

- - - - - - - - - - - - - - - - - - - -

The entry point to a Prado application in this example is index.php -and generally contains the following code.

- - -<?php -error_reporting(E_ALL); -require_once('/path/to/prado/framework/prado.php'); -$application=new TApplication; -$application->run(); -?> - - -

Now we are ready to setup a page to display our list of people. -The following sample shows the Prado code for our display page. The key -piece is the TDataGrid. We save the file as Home.page.

- - - - - - Person - - -<com:TForm> -

Person List

- <com:TDataGrid id="personList"> - <com:TBoundColumn DataField="BirthDate" - HeaderText="Birth Date"/> - </com:TDataGrid> -</com:TForm> - - -
- -

Of course, we still need to populate that TDataGrid. The following code -shows the PHP for Home.php. The operative method is loadData(). -The rest is supporting code.

- - -<?php -Prado::using('Example.Models.Person'); -class Home extends TPage -{ - private function loadData() - { - $sqlmap = $this->Application->getModule('SQLMap')->getClient(); - $this->personList->DataSource = $sqlmap->queryForList('SelectAll'); - $this->personList->dataBind(); - } - - public function onLoad($param) - { - if(!$this->IsPostBack) - $this->loadData(); - } -} -?> - - -

If we run this now, we'll get a list like the one shown the figure below.

- class="figure" /> -
Figure 3: A quick-and-dirty Person List
- -
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Tutorial/example1.png b/demos/sqlmap/protected/pages/Tutorial/example1.png deleted file mode 100644 index b5241de6..00000000 Binary files a/demos/sqlmap/protected/pages/Tutorial/example1.png and /dev/null differ diff --git a/demos/sqlmap/protected/pages/Tutorial/grid1.png b/demos/sqlmap/protected/pages/Tutorial/grid1.png deleted file mode 100644 index 845b9581..00000000 Binary files a/demos/sqlmap/protected/pages/Tutorial/grid1.png and /dev/null differ diff --git a/demos/sqlmap/protected/pages/Tutorial/grid2.png b/demos/sqlmap/protected/pages/Tutorial/grid2.png deleted file mode 100644 index dcafc33d..00000000 Binary files a/demos/sqlmap/protected/pages/Tutorial/grid2.png and /dev/null differ diff --git a/demos/sqlmap/protected/pages/config.xml b/demos/sqlmap/protected/pages/config.xml deleted file mode 100644 index e0850c2c..00000000 --- a/demos/sqlmap/protected/pages/config.xml +++ /dev/null @@ -1,8 +0,0 @@ - - - - - - - - \ No newline at end of file -- cgit v1.2.3