From 3819e887d9f485ac0917a9d415961e147581c280 Mon Sep 17 00:00:00 2001 From: aztech <> Date: Thu, 10 Jan 2008 02:43:04 +0000 Subject: [PL] partial Active Record translation + some base QST page translations --- .../protected/pages/Database/pl/ActiveRecord.page | 1163 ++++++++++++++++++++ .../protected/pages/Database/pl/ar_objects.png | Bin 0 -> 20638 bytes .../protected/pages/Database/pl/ar_relations.png | Bin 0 -> 9693 bytes .../protected/pages/Database/pl/diagram.png | Bin 0 -> 30320 bytes .../protected/pages/Database/pl/object_states.png | Bin 0 -> 9596 bytes .../pages/Database/pl/sqlmap_active_record.png | Bin 0 -> 17351 bytes 6 files changed, 1163 insertions(+) create mode 100644 demos/quickstart/protected/pages/Database/pl/ActiveRecord.page create mode 100644 demos/quickstart/protected/pages/Database/pl/ar_objects.png create mode 100644 demos/quickstart/protected/pages/Database/pl/ar_relations.png create mode 100644 demos/quickstart/protected/pages/Database/pl/diagram.png create mode 100755 demos/quickstart/protected/pages/Database/pl/object_states.png create mode 100755 demos/quickstart/protected/pages/Database/pl/sqlmap_active_record.png (limited to 'demos/quickstart/protected/pages/Database') diff --git a/demos/quickstart/protected/pages/Database/pl/ActiveRecord.page b/demos/quickstart/protected/pages/Database/pl/ActiveRecord.page new file mode 100644 index 00000000..53bbdee5 --- /dev/null +++ b/demos/quickstart/protected/pages/Database/pl/ActiveRecord.page @@ -0,0 +1,1163 @@ + + +

Rekord Aktywny (ang. Active Record)

+ +

Rekordy Aktywne są obiektami, które opakowują wiersz w bazie danych lub widoku, + obudowują (ang. encapsulate) dostęp do bazy danych oraz dziedziny logiki dla tych danych. + Podstawą Rekordu Aktywnego są klasy biznesowe np. klasa + Produkty, które są bardzo podobne do struktury rekordu należącego do bazy danych. Każdy Rekord Aktywny jest odpowiedzialny + za zapisywanie i łądowanie danych do i z bazy danych.

+
Info: + Struktura danych Rekordu Sktywnego powinna zgadzać się ze strukturą tablicy w bazie danych. + Każda kolumna w bazie danych powinna posiadać odpowiadający element: zmienną lub właściwość w klasie Rekordu Aktywnego reprezentującego tablicę. +
+ +

Kiedy używać?

+

Rekord Aktywne jest dobrym wyborem dla dziedziny logiki, która nie jest zbyt złożona, tak jak tworzenie, odczyty, aktualizacje oraz usuwanie. + Pochocne (ang. derivations) oraz sprawdzenia bazujące na pojedyńczym rekordzie sprawdzają się dobrze w tej konstrukcji. + Rekord Aktywne ma podstawową zaletę, którą jest prostota. Łatwo jest stworzyć Rekord Aktywny, łatwo go również zrozuieć. +

+ +

Jednakże, jeśli twoja logika biznesowa staje się coraz bardziej złożona, wkrótce będziesz chciał + używać bezpośrednich relacji, zbiorów, dziedziczenia twojego obiektu i tak dalej. Nie da się tego łatwo odwzorować za pomocą Rekordu Aktywnego, + a dodawanie ich po kawałku staje się bardzo kłopotliwe. Innym argumentem przeciw Rekordowi Aktywnemu jest fakt, że łączy model obiektowy z modelem baz danych. + To czyni trudniejszym refaktoring, gdy projekt idzie naprzód. +

+ +

Alternatywą jest używanie wzorca Data Mapper (mapa danych), który odseparowuje role obiektu biznesowego od tego jak te obiekty są przechowywane. + Prado dostarcza + Prado provides a darmowy wybór pomiędzy rekordem aktywnym a SqlMap Data Mapper. + SqlMap Data Mapper może być uzywany do wczytania obiektów Rekordu Aktywnego, i na odwrót, te Rekordy Aktywne mogą zostać użyte do aktualizacji bazy danych. + Związek pomiędzy Rekordem Aktywnym a SqlMap przedstawiony jest na kolejnym diagramie. Więcej informacji związanych z SqlMap Data Mapper można znaleźć w + manualu SqlMap. + alt="Active Records and SqlMap DataMapper" id="fig:diagram.png" class="figure"/> +

+ +

+ Klasa Rekordu aktywnego posiada funkcjonalność do przeprowadzenia następujących zadań: +

+ +

Implikacje modelu

+

+Implementacja wzorca Aktywnego Rekordu w PRADO nie zapewnia referencyjnej tożsamości (ang. referential identity). Każdy istniejący obiekt używający +Rekordu Aktywnego jest koopią danych z bazy danych. Na przykład jeśli zapytasz o konkretnego klienta i zostanie zwrócony obiekt Klient, +to następnym razem kiedy zapytasz o tego klienta otrzymasz spowrotem inną instancję obiektu Klient. To implikuje, że ścisłe porównianie (np. używając ===) +zwróci fałsz, natomiast luźne porównianie (np. używając ==) zwróci prawdę jeśli wartości obiektu są równe poprzez luźne porónanie. +

+

+Jest to implikacja modelu wynikająca z następującego pytania: +"Czy myślisz o kliencie jako o obiekcie, którego któy jest tylko jeden, czy też myślisz o obiekcie na którym działasz jako o kopii bazy danych. +Inne mapowania O/R implikują, że istnieje tylko jeden obiekt Klienta z KlientID 100 +Other O/R mappings will imply that there is only one Customer object with custID 100 i to dosłownie jest ten klient. +Jeśli pobierzesz klienta i zmienisz pole w nim, wtedy masz zmienionego tego klienta. +"To kontroastuje z: zmieniłeś tą kopię klienta ale nie tamtą kopię. +Jeśli dwóch ludzi zaktualizuje kleinta z dwóch kopii obiektu, kto zaktualizuje pierwszy lub być może ostanie wygrywa." [A. Hejlsberg 2003] +

+ +

Wspierane bazy danych

+

+Implementacja Aktywnego Rekordu wykorzystuje kalsy Prado DAO by uzyskać dostęp do danych. Aktualna implementacja Aktywnego Rekordu wspiera następujace bazy danych +

+ +

Wsparcie dla pozostałych baz danych może zostać wprowadzone, keidy będzie dostatecne zapotrzebowanie

+ +

Definiowanie Aktywnego Rekordu

+

Rozważmy następującą tablicę "users", która zawiera dwie kolumny nazwane "username" oraz "email", + gdzie "username" jest kluczem głównym. + +CREATE TABLE users +( + username VARCHAR( 20 ) NOT NULL , + email VARCHAR( 200 ) , + PRIMARY KEY ( username ) +); + +

+

Następnie zdefiniujemy naszą klasę Rekordu Aktywnego odpowiadającą tablicy "users". + +class UserRecord extends TActiveRecord +{ + const TABLE='users'; //nazwa tablicy + + public $username; //kolumna nazwana "username" w tablicy "users" + public $email; + + /** + * @return TActiveRecord intancja finder rekordu aktywnego + */ + public static function finder($className=__CLASS__) + { + return parent::finder($className); + } +} + +

+

Każda kolumna tablicy "users" musi posiadać odpowiadającą jej właściwość o tej samej nazwie co kolumna w tablicy w klasie UserRecord. + Oczywiście, możesz zdefiniować dodatkowe zmienne lub właściwości, które nie istnieją w strukturze tablicy. + Stała TABLE jest opcjonalna w klasie , kiedy nazwa klasy jest taka sama jak nazwa tablicy w bazie danych, w przeciwnym przypadku TABLE + musi określać nazwę tablicy, która odpowiada klasie Rekordu Aktywnego. +

+ +
Tip: +Możesz określić kwalifikowane (ang. qualified) nazwy tablic np dla MySQL, TABLE = "`bazadanych1`.`tablica1`". +
+ +

+ Odkąd TActiveRecord rozszerza TComponent, metody setter i getter mogą zostać zdefiniowane + by umożliwić kontrolę nad tym jak zmienne są ustawiane i zwracane. Na przykłąd dodanie właściwości $level + do klasy UserRecord: +

+ +class UserRecord extends TActiveRecord { + ... //istniejąca uprzednio część definicji + + private $_level; + public function setLevel($value) { + $this->_level=TPropertyValue::ensureInteger($value,0); + } + public function getLevel($value){ + return $this->_level; + } +} + +

Więcej szczegółów dotyczących TComponent można znaleźć dokumentacji komponentów. +Później użyjemy metod getter/setters by umożliwić opóźnione ładowanie (ang. lazy loading) obiektów relacji. +

+ +
Info: +TActiveRecord może również działać z widokami poprzez przypisanie do stałej TABLE + odpowiedniej nazwy widoku. Jednakże obiektu zwracane przez widoki są tylko do odczytu, wywołanie metod save() lub delete() +spowoduje wywołanie wyjątku. +
+ +

+ Metoda statyczna finder() zwraca instancję UserRecord, która może zostać użyta do załadowania rekordów z bazy. + Ładowanie rekordów za pomocą tej metody będzie omówione później. Statyczna metoda TActiveRecord::finder() + pobiera nazwę klasy Rekord Aktywnego jako parametr. +

+ +

Ustanawianie połączenia z bazą danych

+

+ Domyślne połączenie z bazą dla Rekordu Aktywnego może zostać ustawione następujaco. + Zobacz Ustanawianie połączenia z bazą + by uzyskać ogólnie dalsze szczegóły odnośnie tworzenia połączenia z bazą danych. +

+ +//utwóz połączenie i przekaż je do menadżera Rekordu Aktywnego +$dsn = 'pgsql:host=localhost;dbname=test'; //Postgres SQL +$conn = new TDbConnection($dsn, 'dbuser','dbpass'); +TActiveRecordManager::getInstance()->setDbConnection($conn); + + +

Alternatywnie, możesz stworzyć klasę bazową i nadpisać metodę getDbConnection() +do zwracania połączenia z bazą. To jest prosty spodób, by umożliwić wielkokrotne połączenia do wielu baz danych. + Następujący kod demonstruje definiowanie połączenia z bazą danych w klasie bazowej (nie ma potrzeby by ustawiać połączenie DB gdziekolwiek indziej). +

+ +class MyDb1Record extends TActiveRecord +{ + public function getDbConnection() + { + static $conn; + if($conn===null) + $conn = new TDbConnection('xxx','yyy','zzz'); + return $conn; + } +} +class MyDb2Record extends TActiveRecord +{ + public function getDbConnection() + { + static $conn; + if($conn===null) + $conn = new TDbConnection('aaa','bbb','ccc'); + return $conn; + } +} + + + +

Używanie application.xml w frameworku Prado

+
+

+ Domyślne połączenie z bazą może zostać również skonfigurowane używając tagu <module> + w pliku application.xml + lub config.xml następująco: + + + + + + + +

Wskazówka: + Atrybut EnableCache gdy ustawiony na "true" będzie keszował metadane tablicy, to oznacza, że nazwy kolumn, indeksy i ograniczenia (ang. constraints) + są zapisywane w keszu i używane ponownie. Musisz wyczyścić lub wyłączyć kesz jeśli chcesz zobaczyć wprowadzone zmiany do definicji twoich tablic. + Moduł keszowania musi być również zdefiniowany dla keszu by zadziałał. +
+

+ +

Do właściwość ConnectionID może zostać przypisana wartość ID z konfiguracji z innego modułu + TDataSourceConfig. To pozwala uyżywać to połączenie z bazą danych w innych modułach, takich jak SqlMap (mapa SQL). + + + + + + + + + + + +

+
+ +

Ładowanie danych z tablicy

+

+ Klasa TActiveRecord dostarcza wielu wygodnych metod do wyszukiwania rekordów z bazy danych. + Najprostszym jest znajdowanie jednego rekordu poprzez dopasowanie klucza głównego lub klucza złożonego (ang. composite key) + (klucz główny skłądający się z wielu kolumn). + Zobacz by dowiedzieć się więcej. +

+ +
Info: +Wszystkie metody wyszukujące, które mogą zwrócić tylko 1 rekord zwrócą null jeśli nie znajdą pasujących danych. +Wszystkie metody wyszukujące, które zwracają tablicę rekordów zwrócą pustą tablicęm jeśli nie znajdą pasujących danych. +
+ +

findByPk()

+

Znajduje jeden rekord używając klucza głównego lub klucza złożonego. + +$finder = UserRecord::finder(); +$user = $finder->findByPk($primaryKey); + +//kiedy tablica używa klucza złożonego +$record = $finder->findByPk($key1, $key2, ...); +$record = $finder->findByPk(array($key1, $key2,...)); + +

+ +

findAllByPks()

+

Znajduje wiele rekordów używając listy kluczy głównych lub kluczy złożonych. +Co następuje jest odpowiednie dla kluczów głównych (klucz główny składa się tylko z jednego pola/kolumny) +

+ +$finder = UserRecord::finder(); +$users = $finder->findAllByPks($key1, $key2, ...); +$users = $finder->findAllByPks(array($key1, $key2, ...)); + +Co następuje jest odpowiednie dla kluczów złożonych: + +//kiedy tablica używa klucza złożonego +$record = $finder->findAllByPks(array($key1, $key2), array($key3, $key4), ...); + +$keys = array( array($key1, $key2), array($key3, $key4), ... ); +$record = $finder->findAllByPks($keys); + + + +

find()

+

Znajduj pojedyńczy rekord, który spełnia kryteria. Kryteria mogą być częściowym łąńcuchem SQL lub obiektem TActiveRecordCriteria

+ +$finder = UserRecord::finder(); + +//:name oraz :pass są pojemnikami dla konkretnych wartości $name oraz $pass +$finder->find('username = :name AND password = :pass', + array(':name'=>$name, ':pass'=>$pass)); + +//użycie znaków zastępczych +$finder->find('username = ? AND password = ?', array($name, $pass)); +//jak wyżej +$finder->find('username = ? AND password = ?', $name, $pass); + +//$criteria są typu TActiveRecordCriteria +$finder->find($criteria); //drugi parametr dla find() jest zignorowany. + + +

Klasa TActiveRecordCriteria ma następujące właściwości: +

+ + + +$criteria = new TActiveRecordCriteria; +$criteria->Condition = 'username = :name AND password = :pass'; +$criteria->Parameters[':name'] = 'admin'; +$criteria->Parameters[':pass'] = 'prado'; +$criteria->OrdersBy['level'] = 'desc'; +$criteria->OrdersBy['name'] = 'asc'; +$criteria->Limit = 10; +$criteria->Offset = 20; + + +
Note: +For MSSQL and when Limit and Offset are positive integer values. The +actual query to be executed is modified by the + +class according to +http://troels.arvin.dk/db/rdbms/ +to emulate the Limit and Offset conditions. +
+ +

findAll()

+

Same as find() but returns an array of objects.

+ +

findBy*() and findAllBy*()

+

Dynamic find method using parts of the method name as search criteria. +Method names starting with findBy return 1 record only +and method names starting with findAllBy return an array of records. +The condition is taken as part of the method name after findBy or findAllBy. + +The following blocks of code are equivalent: +

+ +$finder->findByName($name) +$finder->find('Name = ?', $name); + + + +$finder->findByUsernameAndPassword($name,$pass); +$finder->findBy_Username_And_Password($name,$pass); +$finder->find('Username = ? AND Password = ?', $name, $pass); + + + +$finder->findAllByAge($age); +$finder->findAll('Age = ?', $age); + + +
Tip: +You may also use a combination of AND and OR as a condition in the dynamic methods. +
+ +

findBySql() and findAllBySql()

+

Finds records using full SQL where findBySql() +return an Active Record and findAllBySql()returns an array of record objects. +For each column returned, the corresponding Active Record class must define a member variable or +property for each corresponding column name. + +class UserRecord2 extends UserRecord +{ + public $another_value; +} +$sql = "SELECT users.*, 'hello' as another_value FROM users"; +$users = TActiveRecord::finder('UserRecord2')->findAllBySql($sql); + +

+

count()

+

Find the number of matchings records, accepts same parameters as the findAll() method.

+ +

Inserting and updating records

+

+Add a new record using TActiveRecord is very simple, just create a new Active +Record object and call the save() method. E.g. +

+ +$user1 = new UserRecord(); +$user1->username = "admin"; +$user1->email = "admin@example.com"; +$user1->save(); //insert a new record + +$data = array('username'=>'admin', 'email'=>'admin@example.com'); +$user2 = new UserRecord($data); //create by passing some existing data +$user2->save(); //insert a new record + +
Tip: +The objects are update with the primary key of those the tables that contains +definitions that automatically creates a primary key for the newly insert records. +For example, if you insert a new record into a MySQL table that has columns +defined with "autoincrement", the Active Record objects will be updated with the new +incremented value.
+ +

+To update a record in the database, just change one or more properties of +the Active Record object that has been loaded from the database and then +call the save() method. + + +$user = UserRecord::finder()->findByName('admin'); +$user->email="test@example.com"; //change property +$user->save(); //update it. + +

+ +

+Active Record objects have a simple life-cycle illustrated in the following diagram. +

+ alt="Active Records Life Cycle" id="fig:cycle.png" class="figure"/> +

+We see that new TActiveRecord objects are created by either using one of the find*() +methods or using creating a new instance by using PHP's new keyword. Objects +created by a find*() method starts with clean state. New instance of +TActiveRecord created other than by a find*() method starts with new state. +Whenever you +call the save() method on the TActiveRecord object, the object enters the clean +state. Objects in the clean becomes dirty whenever one of more of its +internal states are changed. Calling the delete() method on the object +ends the object life-cycle, no further actions can be performed on the object. +

+ +

Deleting existing records

+

+ To delete an existing record that is already loaded, just call the delete() method. + You can also delete records in the database by primary keys without + loading any records using the deleteByPk() method (and equivalently the deleteAllByPks() method). + For example, to delete one or several records with tables using one or more primary keys. +

+ +$finder->deleteByPk($primaryKey); //delete 1 record +$finder->deleteAllByPks($key1,$key2,...); //delete multiple records +$finder->deleteAllByPks(array($key1,$key2,...)); //delete multiple records + + +

+For composite keys (determined automatically from the table definitions): +

+ +$finder->deleteByPk(array($key1,$key2)); //delete 1 record + +//delete multiple records +$finder->deleteAllByPks(array($key1,$key2), array($key3,$key4),...); + +//delete multiple records +$finder->deleteAllByPks(array( array($key1,$key2), array($key3,$key4), .. )); + + +

deleteAll() and deleteBy*()

+

+To delete by a criteria, use deleteAll($criteria) and deleteBy*() +with similar syntax to findAll($criteria) and findAllBy*() as +described above. +

+ +//delete all records with matching Name +$finder->deleteAll('Name = ?', $name); +$finder->deleteByName($name); + +//delete by username and password +$finder->deleteBy_Username_And_Password($name,$pass); + + +

Transactions

+

All Active Record objects contain the property DbConnection + that can be used to obtain a transaction object. + +$finder = UserRecord::finder(); +$finder->DbConnection->Active=true; //open if necessary +$transaction = $finder->DbConnection->beginTransaction(); +try +{ + $user = $finder->findByPk('admin'); + $user->email = 'test@example.com'; //alter the $user object + $user->save(); + $transaction->commit(); +} +catch(Exception $e) // an exception is raised if a query fails +{ + $transaction->rollBack(); +} + + +

Events

+

+The TActiveRecord offers two events, OnCreateCommand and OnExecuteCommand. +

+ +

The OnCreateCommand event is raised when a command is prepared and +parameter binding is completed. The parameter object is TDataGatewayEventParameter of which the +Command property can be inspected to obtain the SQL query to be executed. +

+ +

+The OnExecuteCommand event is raised when a command is executed and the +result from the database was returned. The parameter object is TDataGatewayResultEventParameter +of which the Result property contains the data return from the database. +The data returned can be changed by setting the Result property. +

+ +

Logging Example

+

Using the OnExecuteCommand we can attach an event handler to log +the entire SQL query executed for a given TActiveRecord class or instance. For example, we define +a base class and override either the getDbConnection() or the constructor. +

+ + +class MyDb1Record extends TActiveRecord +{ + public function getDbConnection() + { + static $conn; + if($conn===null) + { + $conn = new TDbConnection('xxx','yyy','zzz'); + $this->OnExecuteCommand[] = array($this,'logger'); + } + return $conn; + } + public function logger($sender,$param) + { + var_dump($param->Command->Text); + } +} +//alternatively as per instance of per finder object +function logger($sender,$param) +{ + var_dump($param->Command->Text); +} +TActiveRecord::finder('MyRecord')->OnExecuteCommand[] = 'logger'; +$obj->OnExecuteCommand[] = array($logger, 'log'); //any valid PHP callback. + + +

Active Record Relationships

+ +

+The Prado Active Record implementation supports the foreign key mappings for database +that supports foreign key constraints. For Active Record relationships to function the +underlying database must support foreign key constraints (e.g. MySQL using InnoDB). +

+ +

+In the following sections we will consider the following table relationships between +Teams, Players, Skills and Profiles. +

+ class="figure" /> + + +

The goal is to obtain object models that represent to some degree the entity +relationships in the above figure. +

+ + class="figure" /> + +

+There is a mismatch between relationships with objects and table relationships. +First there's a difference in representation. Objects handle links by storing references +that are held by the runtime memory-managed environment. Relational databases handle +links by forming a key into another table. Second, objects can easily use collections +to handle multiple references from a single field, while normalization forces +all entity relation links to be single valued. This leads to reversals of the data +structure between objects and tables. The approach taken in the Prado Active Record +design is to use the table foreign key constraints to derive object relationships. This implies +that the underlying database must support foreign key constraints. +

+
Tip: +For SQLite database, you may create tables that defines the foreign key +constraints such as the example below. However, these constraints are NOT +enforced by the SQLite database itself. + +CREATE TABLE foo +( + id INTEGER NOT NULL PRIMARY KEY, + id2 CHAR(2) +); +CREATE TABLE bar +( + id INTEGER NOT NULL PRIMARY KEY, + foo_id INTEGER + CONSTRAINT fk_foo_id REFERENCES foo(id) ON DELETE CASCADE +); + +
+ +

Foreign Key Mapping

+

The entity relationship between the Teams and Players table is what is known +as an 1-M relationship. That is, one Team may contain 0 or more Players. In terms of +object relationships, we say that a TeamRecord object has many PlayerRecord objects. +(Notice the reversal of the direction of relationships between tables and objects.) +

+ +

Has Many Relationship

+

+We model the Team object as the following Active Record classes. +

+ +class TeamRecord extends TActiveRecord +{ + const TABLE='Teams'; + public $name; + public $location; + + public $players=array(); // this declaration is no longer needed since v3.1.2 + + //define the $player member having has many relationship with PlayerRecord + public static $RELATIONS=array + ( + 'players' => array(self::HAS_MANY, 'PlayerRecord', 'team_name'), + ); + + public static function finder($className=__CLASS__) + { + return parent::finder($className); + } +} + +

+The static $RELATIONS property of TeamRecord defines that the +property $players has many PlayerRecords. Multiple relationships +is permitted by defining each relationship with an entry in the $RELATIONS +array where array key for the entry corresponds to the property name. +In array(self::HAS_MANY, 'PlayerRecord'), the first element defines the +relationship type, the valid types are self::HAS_MANY, self::HAS_ONE, +self::BELONGS_TO and self::MANY_TO_MANY. +The second element is a string 'PlayerRecord' that corresponds to the +class name of the PlayerRecord class. +And the third element 'team_name' refers to the foreign key column in the Players table that +references to the Teams table. +

+ +
Note: +As described in the code comment above, since version 3.1.2, related properties no longer +need to be explicitly declared. By default, they will be implicitly declared according to +keys of the $RELATIONS array. A major benefit of declared related properties implicitly +is that related objects can be automatically loaded in a lazy way. For example, assume we have +a TeamRecord instance $team. We can access the players via $team->players, +even if we have never issued fetch command for players. If $players is explicitly declared, +we will have to use the with approach described in the following to fetch the player records. +
+ +

+The foreign key constraint of the Players table is used to determine the corresponding +Teams table's corresponding key names. This is done automatically handled +in Active Record by inspecting the Players and Teams table definitions. +

+ +
Info: +Since version 3.1.2, Active Record supports multiple foreign key +references of the same table. Ambiguity between multiple foreign key references to the same table is +resolved by providing the foreign key column name as the 3rd parameter in the relationship array. +For example, both of the following foreign keys owner_id and reporter_id +references to the same table defined in UserRecord. + +class TicketRecord extends TActiveRecord +{ + public $owner_id; + public $reporter_id; + + public $owner; // this declaration is no longer needed since v3.1.2 + public $reporter; // this declaration is no longer needed since v3.1.2 + + public static $RELATION=array + ( + 'owner' => array(self::BELONGS_TO, 'UserRecord', 'owner_id'), + 'reporter' => array(self::BELONGS_TO, 'UserRecord', 'reporter_id'), + ); +} + +This is applicable to relationships including BELONGS_TO, HAS_ONE and +HAS_MANY. See section Self Referenced Association Tables for solving ambiguity of MANY_TO_MANY +relationships. +
+ +

The "has many" relationship is not fetched automatically when you use any of the Active Record finder methods. +You will need to explicitly fetch the related objects as follows. In the code below, both lines +are equivalent and the method names are case insensitive. +

+ +$team = TeamRecord::finder()->withPlayers()->findAll(); +$team = TeamRecord::finder()->with_players()->findAll(); //equivalent + +

+The method with_xxx() (where xxx is the relationship property +name, in this case, players) fetches the corresponding PlayerRecords using +a second query (not by using a join). The with_xxx() accepts the same +arguments as other finder methods of TActiveRecord, e.g. with_players('age = ?', 35). +

+ +
Note: +It is essential to understand that the related objects are fetched using additional +queries. The first query fetches the source object, e.g. the TeamRecord in the above example code. +A second query is used to fetch the corresponding related PlayerRecord objects. +The usage of the two query is similar to a single query using Left-Outer join with the +exception that null results on the right table +are not returned. The consequence of using two or more queries is that the aggregates +and other join conditions are not feasible using Active Records. For queries outside the +scope of Active Record the SqlMap Data Mapper may be considered. +
+ +
Info: +The above with approach also works with implicitly declared related properties (introduced +in version 3.1.2). So what is the difference between the with approach and the lazy loading +approach? Lazy loading means we issue an SQL query if a related object is initially accessed and not ready, +while the with approach queries for the related objects once for all, no matter the related objects +are accessed or not. The lazy loading approach is very convenient since we do not need to explictly +load the related objects, while the with approach is more efficient if multiple records are +returned, each with some related objects. +
+ +

Has One Relationship

+

The entity relationship between Players and Profiles is one to one. That is, +each PlayerRecord object has one ProfileRecord object (may be none or null). +A has one relationship is nearly identical to a has many relationship with the exception +that the related object is only one object (not a collection of objects). +

+ +

Belongs To Relationship

+

The "has many" relationship in the above section defines a collection of foreign +objects. In particular, we have that a TeamRecord has many (zero or more) +PlayerRecord objects. We can also add a back pointer by adding a property +in the PlayerRecord class that links back to the TeamRecord object, +effectively making the association bidirectional. +We say that the $team property in PlayerRecord class belongs to a TeamRecord object. +The following code defines the complete PlayerRecord class with 3 relationships. +

+ +class PlayerRecord extends TActiveRecord +{ + const TABLE='Players'; + public $player_id; + public $age; + public $team_name; + + public $team; // this declaration is no longer needed since v3.1.2 + public $skills=array(); // this declaration is no longer needed since v3.1.2 + public $profile; // this declaration is no longer needed since v3.1.2 + + public static $RELATIONS=array + ( + 'team' => array(self::BELONGS_TO, 'TeamRecord', 'team_name'), + 'skills' => array(self::MANY_TO_MANY, 'SkillRecord', 'Player_Skills'), + 'profile' => array(self::HAS_ONE, 'ProfileRecord', 'player_id'), + ); + + public static function finder($className=__CLASS__) + { + return parent::finder($className); + } +} + +

+The static $RELATIONS property of PlayerRecord defines that the +property $team belongs to a TeamRecord. +The $RELATIONS array also defines two other relationships that we +shall examine in later sections below. +In array(self::BELONGS_TO, 'TeamRecord', 'team_name'), the first element defines the +relationship type, in this case self::BELONGS_TO; +the second element is a string 'TeamRecord' that corresponds to the +class name of the TeamRecord class; and the third element 'team_name' refers +to the foreign key of Players referencing Teams. +A player object with the corresponding team object may be fetched as follows. +

+ +$players = PlayerRecord::finder()->with_team()->findAll(); + + +

+ The method with_xxx() (where xxx is the relationship property + name, in this case, team) fetches the corresponding TeamRecords using + a second query (not by using a join). The with_xxx() accepts the same +arguments as other finder methods of TActiveRecord, e.g. +with_team('location = ?', 'Madrid'). +

+ +
Tip: +Additional relationships may be fetched by chaining the with_xxx() together as the following +example demonstrates. + +$players = PlayerRecord::finder()->with_team()->with_skills()->findAll(); + +Each with_xxx() method will execute an additional SQL query. Every +with_xxx() accepts arguments similar to those in the findAll() method and is only +applied to that particular relationship query. +
+ +

The "belongs to" relationship of ProfileRecord class is defined similarly.

+ +class ProfileRecord extends TActiveRecord +{ + const TABLE='Profiles'; + public $player_id; + public $salary; + + public $player; // this declaration is no longer needed since v3.1.2 + + public static $RELATIONS=array + ( + 'player' => array(self::BELONGS_TO, 'PlayerRecord'), + ); + + public static function finder($className=__CLASS__) + { + return parent::finder($className); + } +} + + +

In essence, there exists a "belongs to" relationship for objects corresponding to +entities that has column which are foreign keys. In particular, we see that +the Profiles table has a foreign key constraint on the column player_id +that relates to the Players table's player_id column. Thus, the ProfileRecord +object has a property ($player) that belongs to a PlayerRecord object. +Similarly, the Players table has a foreign key constraint on the column team_name that relates to the +Teams table's name column. +Thus, the PlayerRecord object has a property ($team) that belongs to a +TeamRecord object. +

+ +

Parent Child Relationships

+

A parent child relationship can be defined using a combination of has many and belongs to +relationship that refers to the same class. The following example shows a parent children relationship between +"categories" and a "parent category". +

+ + +class Category extends TActiveRecord +{ + public $cat_id; + public $category_name; + public $parent_cat_id; + + public $parent_category; // this declaration is no longer needed since v3.1.2 + public $child_categories=array(); // this declaration is no longer needed since v3.1.2 + + public static $RELATIONS=array + ( + 'parent_category' => array(self::BELONGS_TO, 'Category', 'parent_cat_id'), + 'child_categories' => array(self::HAS_MANY, 'Category', 'parent_cat_id'), + ); +} + + +

Query Criteria for Related Objects

+

+In the above, we show that an Active Record object can reference to its related objects by +declaring a static class member $RELATIONS which specifies a list of relations. Each relation +is specified as an array consisting of three elements: relation type, related AR class name, +and the foreign key(s). For example, we use array(self::HAS_MANY, 'PlayerRecord', 'team_name') +to specify the players in a team. There are two more optional elements that can be specified +in this array: query condition (the fourth element) and parameters (the fifth element). +They are used to control how to query for the related objects. For example, if we want to obtain +the players ordered by their age, we can specify array(self::HAS_MANY, 'PlayerRecord', 'team_name', 'ORDER BY age'). +If we want to obtain players whose age is smaller than 30, we could use +array(self::HAS_MANY, 'PlayerRecord', 'team_name', 'age<:age', array(':age'=>30)). In general, +these two additional elements are similar as the parameters passed to the find() method in AR. +

+ + + +

Association Table Mapping

+

+Objects can handle multivalued fields quite easily by using collections as field values. +Relational databases don't have this feature and are constrained to single-valued fields only. +When you're mapping a one-to-many association you can handle this using has many relationships, +essentially using a foreign key for the single-valued end of the association. +But a many-to-many association can't do this because there is no single-valued end to +hold the foreign key. +

+

+The answer is the classic resolution that's been used by relational data people +for decades: create an extra table (an association table) to record the relationship. +The basic idea is using an association table to store the association. This table +has only the foreign key IDs for the two tables that are linked together, it has one +row for each pair of associated objects. +

+

+The association table has no corresponding in-memory object and its primary key is the +compound of the two primary keys of the tables that are associated. +In simple terms, to load data from the association table you perform two queries (in general, it may also be achieved using one query consisting of joins). +Consider loading the SkillRecord collection for a list PlayerRecord objects. +In this case, you do queries in two stages. +The first stage queries the Players table to find all the rows of the players you want. +The second stage finds the SkillRecord object for the related player ID for each row +in the Player_Skills association table using an inner join. +

+ +

The Prado Active Record design implements the two stage approach. For the +Players-Skills M-N (many-to-many) entity relationship, we +define a many-to-many relationship in the PlayerRecord class and +in addition we may define a many-to-many relationship in the SkillRecord class as well. +The following sample code defines the complete SkillRecord class with a +many-to-many relationship with the PlayerRecord class. (See the PlayerRecord +class definition above to the corresponding many-to-many relationship with the SkillRecord class.) +

+ + +class SkillRecord extends TActiveRecord +{ + const TABLE='Skills'; + public $skill_id; + public $name; + + public $players=array(); // this declaration is no longer needed since v3.1.2 + + public static $RELATIONS=array + ( + 'players' => array(self::MANY_TO_MANY, 'PlayerRecord', 'Player_Skills'), + ); + + public static function finder($className=__CLASS__) + { + return parent::finder($className); + } +} + + +

+The static $RELATIONS property of SkillRecord defines that the +property $players has many PlayerRecords via an association table 'Player_Skills'. +In array(self::MANY_TO_MANY, 'PlayerRecord', 'Player_Skills'), the first element defines the +relationship type, in this case self::MANY_TO_MANY, +the second element is a string 'PlayerRecord' that corresponds to the +class name of the PlayerRecord class, and the third element is the name +of the association table name. +

+ +
Note: +Prior to version 3.1.2 (versions up to 3.1.1), the many-to-many relationship was +defined using self::HAS_MANY. For version 3.1.2 onwards, this must be changed +to self::MANY_TO_MANY. This can be done by searching for the HAS_MANY in your +source code and carfully changing the appropriate definitions. +
+ +

+A list of player objects with the corresponding collection of skill objects may be fetched as follows. +

+ +$players = PlayerRecord::finder()->withSkills()->findAll(); + +

+The method with_xxx() (where xxx is the relationship property +name, in this case, Skill) fetches the corresponding SkillRecords using +a second query (not by using a join). The with_xxx() accepts the same +arguments as other finder methods of TActiveRecord. +

+ +

Self Referenced Association Tables

+

+For self referenced association tables, that is, the association points to the same +table. For example, consider the items table with M-N related +item via the related_items association table. The syntax in the following +example is valid for a PostgreSQL database. For other database, consult their respective documentation for +defining the foreign key constraints. + +CREATE TABLE items +( + "item_id" SERIAL, + "name" VARCHAR(128) NOT NULL, + PRIMARY KEY("item_id") +); +CREATE TABLE "related_items" +( + "item_id" INTEGER NOT NULL, + "related_item_id" INTEGER NOT NULL, + CONSTRAINT "related_items_pkey" PRIMARY KEY("item_id", "related_item_id"), + CONSTRAINT "related_items_item_id_fkey" FOREIGN KEY ("item_id") + REFERENCES "items"("item_id") + ON DELETE CASCADE + ON UPDATE NO ACTION + NOT DEFERRABLE, + CONSTRAINT "related_items_related_item_id_fkey" FOREIGN KEY ("related_item_id") + REFERENCES "items"("item_id") + ON DELETE CASCADE + ON UPDATE NO ACTION + NOT DEFERRABLE +); + + +

The association table name in third element of the relationship array may +contain the foreign table column names. The columns defined in the association +table must also be defined in the record class (e.g. the $related_item_id property +corresponds to the related_item_id column in the related_items table). +

+ +class Item extends TActiveRecord +{ + const TABLE="items"; + public $item_id; + public $details; + + //additional foreign item id defined in the association table + public $related_item_id; + public $related_items=array(); // this declaration is no longer needed since v3.1.2 + + public static $RELATIONS=array + ( + 'related_items' => array(self::MANY_TO_MANY, + 'Item', 'related_items.related_item_id'), + ); +} + +
Tip: +Compound keys in the foreign table can +be specified as comma separated values between brackets. E.g. +'related_items.(id1,id2)'. +
+ + + +

Lazy Loading Related Objects

+ +
Note: +Implicitly declared related properties introduced in version 3.1.2 automatically have lazy +loading feature. Therefore, the lazy loading technique described in the following is no longer +needed in most of the cases, unless you want to manipulate the related objects through getter/setter. +
+ +

Using the with_xxx() methods will load the relationship record on demand. Retrieving the +related record using lazy loading (that is, only when those related objects are accessed) can be +achieved by using a feature of the TComponent that provides accessor methods. In particular, +we define a pair of getter and setter methods where the getter method will retrieve the relationship +conditionally. The following example illustrates that the PlayerRecord can retrieve its +$skills foreign objects conditionally. +

+ +class PlayerRecord extends BaseFkRecord +{ + //... other properties and methods as before + + private $_skills; //change to private and default as null + + public function getSkills() + { + if($this->_skills===null && $this->player_id !==null) + { + //lazy load the skill records + $this->setSkills($this->withSkills()->findByPk($this->player_id)->skills); + } + else if($this->_skills===null) + { + //create new TList; + $this->setSkills(new TList()); + } + + return $this->_skills; + } + + public function setSkills($value) + { + $this->_skills = $value instanceof TList ? $value : new TList($value); + } +} + +

We first need to change the $skills=array() declaration to a private property +$_skills (notice the underscore) and set it to null instead. This allows us +to define the skills property using getter/setter methods +(see Components for details). The getSkills() +getter method for the skills property will lazy load the corresponding skills foreign record +when it is used as follows. Notice that we only do a lazy load when its $player_id is +not null (that is, when the record is already fetched from the database or player id was already set). +

+ +$player = PlayerRecord::finder()->findByPk(1); +var_dump($player->skills); //lazy load it on first access +var_dump($player->skills[0]); //already loaded skills property +$player->skills[] = new SkillRecord(); //add skill + + +

The setSkills() ensures that the skills property will always be a TList. +Using a TList allows us to set the elements of the skills property as if they were +arrays. E.g. $player->skills[] = new SkillRecord(). If array was used, a PHP error +will be thrown. +

+ +

Column Mapping

+

+Since v3.1.1, Active Record starts to support column mapping. Column mapping allows developers +to address columns in Active Record using a more consistent naming convention. In particular, +using column mapping, one can access a column using whatever name he likes, rather than limited by +the name defined in the database schema. +

+

+To use column mapping, declare a static array named COLUMN_MAPPING in the Active Record class. +The keys of the array are column names (called physical column names) as defined in the database +schema, while the values are corresponding property names (called logical column names) defined +in the Active Record class. The property names can be either public class member variable names or +component property names defined via getters/setters. If a physical column name happens to be the same +as the logical column name, they do not need to be listed in COLUMN_MAPPING. +

+ +class UserRecord extends TActiveRecord +{ + const TABLE='users'; + public static $COLUMN_MAPPING=array + ( + 'user_id'=>'id', + 'email_address'=>'email', + 'first_name'=>'firstName', + 'last_name'=>'lastName', + ); + public $id; + public $username; // the physical and logical column names are the same + public $email; + public $firstName; + public $lastName; + //.... +} + +

+With the above column mapping, we can address first_name using $userRecord->firstName +instead of $userRecord->first_name. This helps separation of logic and model. +

+ +

References

+ + +
$Id$
 \ No newline at end of file diff --git a/demos/quickstart/protected/pages/Database/pl/ar_objects.png b/demos/quickstart/protected/pages/Database/pl/ar_objects.png new file mode 100644 index 00000000..ac33b88b Binary files /dev/null and b/demos/quickstart/protected/pages/Database/pl/ar_objects.png differ diff --git a/demos/quickstart/protected/pages/Database/pl/ar_relations.png b/demos/quickstart/protected/pages/Database/pl/ar_relations.png new file mode 100644 index 00000000..48e29f48 Binary files /dev/null and b/demos/quickstart/protected/pages/Database/pl/ar_relations.png differ diff --git a/demos/quickstart/protected/pages/Database/pl/diagram.png b/demos/quickstart/protected/pages/Database/pl/diagram.png new file mode 100644 index 00000000..0a0ca73d Binary files /dev/null and b/demos/quickstart/protected/pages/Database/pl/diagram.png differ diff --git a/demos/quickstart/protected/pages/Database/pl/object_states.png b/demos/quickstart/protected/pages/Database/pl/object_states.png new file mode 100755 index 00000000..db194783 Binary files /dev/null and b/demos/quickstart/protected/pages/Database/pl/object_states.png differ diff --git a/demos/quickstart/protected/pages/Database/pl/sqlmap_active_record.png b/demos/quickstart/protected/pages/Database/pl/sqlmap_active_record.png new file mode 100755 index 00000000..6d958d33 Binary files /dev/null and b/demos/quickstart/protected/pages/Database/pl/sqlmap_active_record.png differ -- cgit v1.2.3