docs/php/database-objects.md

   1 # Database Objects
   2
   3 WoltLab Suite uses a unified interface to work with database rows using an object based approach instead of using native arrays holding arbitrary data. Each database table is mapped to a model class that is designed to hold a single record from that table and expose methods to work with the stored data, for example providing assistance when working with normalized datasets.
   4
   5 Developers are required to provide the proper DatabaseObject implementations themselves, they're not automatically generated, all though the actual code that needs to be written is rather small. The following examples assume the fictional database table `wcf1_example`, `exampleID` as the auto-incrementing primary key and the column `bar` to store some text.
   6
   7
   8 ## DatabaseObject
   9
  10 The basic model derives from `wcf\data\DatabaseObject` and provides a convenient constructor to fetch a single row or construct an instance using pre-loaded rows.
  11
  12 ```php
  13 <?php
  14 namespace wcf\data\example;
  15 use wcf\data\DatabaseObject;
  16
  17 class Example extends DatabaseObject {}
  18 ```
  19
  20 The class is intended to be empty by default and there only needs to be code if you want to add additional logic to your model. Both the class name and primary key are determined by `DatabaseObject` using the namespace and class name of the derived class. The example above uses the namespace `wcf\…` which is used as table prefix and the class name `Example` is converted into `exampleID`, resulting in the database table name `wcfN_example` with the primary key `exampleID`.
  21
  22 You can prevent this automatic guessing by setting the class properties `$databaseTableName` and `$databaseTableIndexName` manually.
  23
  24
  25 ## DatabaseObjectDecorator
  26
  27 If you already have a `DatabaseObject` class and would like to extend it with additional data or methods, for example by providing a class `ViewableExample` which features view-related changes without polluting the original object, you can use `DatabaseObjectDecorator` which a default implementation of a decorator for database objects.
  28
  29 ```php
  30 <?php
  31 namespace wcf\data\example;
  32 use wcf\data\DatabaseObjectDecorator;
  33
  34 class ViewableExample extends DatabaseObjectDecorator {
  35     protected static $baseClass = Example::class;
  36
  37     public function getOutput() {
  38         $output = '';
  39
  40         // [determine output]
  41
  42         return $output;
  43     }
  44 }
  45 ```
  46
  47 It is mandatory to set the static `$baseClass` property to the name of the decorated class.
  48
  49 Like for any decorator, you can directly access the decorated object's properties and methods for a decorated object by accessing the property or calling the method on the decorated object.
  50 You can access the decorated objects directly via `DatabaseObjectDecorator::getDecoratedObject()`.
  51
  52
  53 ## DatabaseObjectEditor
  54
  55 !!! info "This is the low-level interface to manipulate data rows, it is recommended to use `AbstractDatabaseObjectAction`."
  56
  57 Adding, editing and deleting models is done using the `DatabaseObjectEditor` class that decorates a `DatabaseObject` and uses its data to perform the actions.
  58
  59 ```php
  60 <?php
  61 namespace wcf\data\example;
  62 use wcf\data\DatabaseObjectEditor;
  63
  64 class ExampleEditor extends DatabaseObjectEditor {
  65     protected static $baseClass = Example::class;
  66 }
  67 ```
  68
  69 The editor class requires you to provide the fully qualified name of the model, that is the class name including the complete namespace. Database table name and index key will be pulled directly from the model.
  70
  71 ### Create a new row
  72
  73 Inserting a new row into the database table is provided through `DatabaseObjectEditor::create()` which yields a `DatabaseObject` instance after creation.
  74
  75 ```php
  76 <?php
  77 $example = \wcf\data\example\ExampleEditor::create([
  78     'bar' => 'Hello World!'
  79 ]);
  80
  81 // output: Hello World!
  82 echo $example->bar;
  83 ```
  84
  85 ### Updating an existing row
  86
  87 !!! warning "The internal state of the decorated `DatabaseObject` is not altered at any point, the values will still be the same after editing or deleting the represented row. If you need an object with the latest data, you'll have to discard the current object and refetch the data from database."
  88
  89 ```php
  90 <?php
  91 $example = new \wcf\data\example\Example($id);
  92 $exampleEditor = new \wcf\data\example\ExampleEditor($example);
  93 $exampleEditor->update([
  94     'bar' => 'baz'
  95 ]);
  96
  97 // output: Hello World!
  98 echo $example->bar;
  99
 100 // re-creating the object will query the database again and retrieve the updated value
 101 $example = new \wcf\data\example\Example($example->id);
 102
 103 // output: baz
 104 echo $example->bar;
 105 ```
 106
 107 ### Deleting a row
 108
 109 !!! warning "Similar to the update process, the decorated `DatabaseObject` is not altered and will then point to an inexistent row."
 110
 111 ```php
 112 <?php
 113 $example = new \wcf\data\example\Example($id);
 114 $exampleEditor = new \wcf\data\example\ExampleEditor($example);
 115 $exampleEditor->delete();
 116 ```
 117
 118
 119 ## DatabaseObjectList
 120
 121 Every row is represented as a single instance of the model, but the instance creation deals with single rows only. Retrieving larger sets of rows would be quite inefficient due to the large amount of queries that will be dispatched. This is solved with the `DatabaseObjectList` object that exposes an interface to query the database table using arbitrary conditions for data selection. All rows will be fetched using a single query and the resulting rows are automatically loaded into separate models.
 122
 123 ```php
 124 <?php
 125 namespace wcf\data\example;
 126 use wcf\data\DatabaseObjectList;
 127
 128 class ExampleList extends DatabaseObjectList {
 129     public $className = Example::class;
 130 }
 131 ```
 132
 133 The following code listing illustrates loading a large set of examples and iterating over the list to retrieve the objects.
 134
 135 ```php
 136 <?php
 137 $exampleList = new \wcf\data\example\ExampleList();
 138 // add constraints using the condition builder
 139 $exampleList->getConditionBuilder()->add('bar IN (?)', [['Hello World!', 'bar', 'baz']]);
 140 // actually read the rows
 141 $exampleList->readObjects();
 142 foreach ($exampleList as $example) {
 143     echo $example->bar;
 144 }
 145
 146 // retrieve the models directly instead of iterating over them
 147 $examples = $exampleList->getObjects();
 148
 149 // just retrieve the number of rows
 150 $exampleCount = $exampleList->countObjects();
 151 ```
 152
 153 `DatabaseObjectList` implements both [SeekableIterator](https://secure.php.net/manual/en/class.seekableiterator.php) and [Countable](https://secure.php.net/manual/en/class.countable.php).
 154
 155 Additionally, `DatabaseObjectList` objects has the following three public properties that are useful when fetching data with lists:
 156
 157 - `$sqlLimit` determines how many rows are fetched.
 158   If its value is `0` (which is the default value), all results are fetched.
 159   So be careful when dealing with large tables and you only want a limited number of rows:
 160   Set `$sqlLimit` to a value larger than zero!
 161 - `$sqlOffset`:
 162   Paginated pages like a thread list use this feature a lot, it allows you to skip a given number of results.
 163   Imagine you want to display 20 threads per page but there are a total of 60 threads available.
 164   In this case you would specify `$sqlLimit = 20` and `$sqlOffset = 20` which will skip the first 20 threads, effectively displaying thread 21 to 40.
 165 - `$sqlOrderBy` determines by which column(s) the rows are sorted in which order.
 166   Using our example in `$sqlOffset` you might want to display the 20 most recent threads on page 1, thus you should specify the order field and its direction, e.g. `$sqlOrderBy = 'thread.lastPostTime DESC'` which returns the most recent thread first.
 167
 168 For more advanced usage, there two additional fields that deal with the type of objects returned.
 169 First, let's go into a bit more detail what setting the `$className` property actually does:
 170
 171 1. It is the type of database object in which the rows are wrapped.
 172 2. It determines which database table is actually queried and which index is used (see the `$databaseTableName` and `$databaseTableIndexName` properties of `DatabaseObject`).
 173
 174 Sometimes you might use the database table of some database object but wrap the rows in another database object.
 175 This can be achieved by setting the `$objectClassName` property to the desired class name.
 176
 177 In other cases, you might want to wrap the created objects in a database object decorator which can be done by setting the `$decoratorClassName` property to the desired class name:
 178
 179 ```php
 180 <?php
 181 $exampleList = new \wcf\data\example\ExampleList();
 182 $exampleList->decoratorClassName = \wcf\data\example\ViewableExample::class;
 183 ```
 184
 185 Of course, you do not have to set the property after creating the list object, you can also set it by creating a dedicated class:
 186
 187 ```php
 188 <?php
 189 namespace wcf\data\example;
 190
 191 class ViewableExampleList extends ExampleList {
 192     public $decoratorClassName = ViewableExample::class;
 193 }
 194 ```
 195
 196
 197 ## AbstractDatabaseObjectAction
 198
 199 Row creation and manipulation can be performed using the aforementioned `DatabaseObjectEditor` class, but this approach has two major issues:
 200
 201 1. Row creation, update and deletion takes place silently without notifying any other components.
 202 2. Data is passed to the database adapter without any further processing.
 203
 204 The `AbstractDatabaseObjectAction` solves both problems by wrapping around the editor class and thus provide an additional layer between the action that should be taken and the actual process. The first problem is solved by a fixed set of events being fired, the second issue is addressed by having a single entry point for all data editing.
 205
 206 ```php
 207 <?php
 208 namespace wcf\data\example;
 209 use wcf\data\AbstractDatabaseObjectAction;
 210
 211 class ExampleAction extends AbstractDatabaseObjectAction {
 212     public $className = ExampleEditor::class;
 213 }
 214 ```
 215
 216 ### Executing an Action
 217
 218 !!! warning "The method `AbstractDatabaseObjectAction::validateAction()` is internally used for AJAX method invocation and must not be called programmatically."
 219
 220 The next example represents the same functionality as seen for `DatabaseObjectEditor`:
 221
 222 ```php
 223 <?php
 224 use wcf\data\example\ExampleAction;
 225
 226 // create a row
 227 $exampleAction = new ExampleAction([], 'create', [
 228     'data' => ['bar' => 'Hello World']
 229 ]);
 230 $example = $exampleAction->executeAction()['returnValues'];
 231
 232 // update a row using the id
 233 $exampleAction = new ExampleAction([1], 'update', [
 234     'data' => ['bar' => 'baz']
 235 ]);
 236 $exampleAction->executeAction();
 237
 238 // delete a row using a model
 239 $exampleAction = new ExampleAction([$example], 'delete');
 240 $exampleAction->executeAction();
 241 ```
 242
 243 You can access the return values both by storing the return value of `executeAction()` or by retrieving it via `getReturnValues()`.
 244
 245 <span class="label label-info">Events</span> `initializeAction`, `validateAction` and `finalizeAction`
 246
 247 ### Custom Method with AJAX Support
 248
 249 This section is about adding the method `baz()` to `ExampleAction` and calling it via AJAX.
 250
 251 #### AJAX Validation
 252
 253 Methods of an action cannot be called via AJAX, unless they have a validation method. This means that `ExampleAction` must define both a `public function baz()` and `public function validateBaz()`, the name for the validation method is constructed by upper-casing the first character of the method name and prepending `validate`.
 254
 255 The lack of the companion `validate*` method will cause the AJAX proxy to deny the request instantaneously. Do not add a validation method if you don't want it to be callable via AJAX ever!
 256
 257 #### create, update and delete
 258
 259 The methods `create`, `update` and `delete` are available for all classes deriving from `AbstractDatabaseObjectAction` and directly pass the input data to the `DatabaseObjectEditor`. These methods deny access to them via AJAX by default, unless you explicitly enable access. Depending on your case, there are two different strategies to enable AJAX access to them.
 260
 261 ```
 262 <?php
 263 namespace wcf\data\example;
 264 use wcf\data\AbstractDatabaseObjectAction;
 265
 266 class ExampleAction extends AbstractDatabaseObjectAction {
 267     // `create()` can now be called via AJAX if the requesting user posses the listed permissions
 268     protected $permissionsCreate = ['admin.example.canManageExample'];
 269
 270     public function validateUpdate() {
 271         // your very own validation logic that does not make use of the
 272         // built-in `$permissionsUpdate` property
 273
 274         // you can still invoke the built-in permissions check if you like to
 275         parent::validateUpdate();
 276     }
 277 }
 278 ```
 279
 280 #### Allow Invokation by Guests
 281
 282 Invoking methods is restricted to logged-in users by default and the only way to override this behavior is to alter the property `$allowGuestAccess`. It is a simple string array that is expected to hold all methods that should be accessible by users, excluding their companion validation methods.
 283
 284 #### ACP Access Only
 285
 286 Method access is usually limited by permissions, but sometimes there might be the need for some added security to avoid mistakes. The `$requireACP` property works similar to `$allowGuestAccess`, but enforces the request to originate from the ACP together with a valid ACP session, ensuring that only users able to access the ACP can actually invoke these methods.