Database testing. How to test and debug databases Database testing
Many entry-level and intermediate-level unit testing examples in any programming language suggest that simple tests can easily test application logic. For database-oriented applications, this is far from reality. When starting to use, for example, WordPress, TYPO3 or Symfony with Doctrine or Propel, you will easily run into serious problems with PHPUnit: simply because the database is tightly coupled with these libraries.
Make sure you have the pdo PHP extension and database extensions such as pdo_mysql installed. Otherwise the examples below will not work.
You're probably familiar with this situation from your daily work and projects where you want to put your new or professional PHPUnit skills to use, but you encounter one of the following problems:
- A method where you want to test a fairly large JOIN operation and then use the resulting data to calculate some important results.
- Your business logic executes a whole bunch of SELECT, INSERT, UPDATE, and DELETE statements.
- You need to configure test data (possibly a significant amount) into more than two tables to obtain suitable initial data for the methods being tested.
The DbUnit extension greatly simplifies setting up a database for testing purposes and allows you to check the contents of the database after performing a series of operations.
Supported Database Testing Vendors
DbUnit currently supports MySQL, PostgreSQL, Oracle and SQLite. Through integration into the Zend Framework or Doctrine 2, this extension has access to other database management systems (DBMS), such as IBM DB2 or Microsoft SQL Server.
Difficulties in database testing
There's a good reason why all unit testing examples don't include database interaction: these kinds of tests are both difficult to set up and difficult to maintain. While testing with a database, you need to take care of the following factors:
- Database schema and tables
- Inserting the rows needed for the test into these tables
- Checking the status of the database after the test has passed
- Clearing the database for each new test
Since many database APIs such as PDO, MySQLi or OCI8 are cumbersome to use and verbose to write, performing these steps manually can be a nightmare.
Test code should be as short and precise as possible for several reasons:
- You don't want to change a significant amount of test code when making small changes to production code.
- You want your test code to be easy to read and understand, even months after it was written.
Additionally, you must understand that the database is essentially a global variable inserted into your code. Two tests in your test suite can run on the same database, and possibly reuse that data multiple times. Failure in one test can easily affect the result of subsequent tests, thereby complicating the testing process. The previously mentioned cleanup step goes a long way to solving the "database - globally introduced variable" problem.
DbUnit helps simplify all these problems when testing against a database in an elegant way.
What PHPUnit can't help you with is that tests that use the database are significantly slower compared to tests that don't. Depending on how large the interaction with the database is, your tests may take a significant amount of time to run. However, if you keep a small amount of data used for each test and try to test as much code as possible that does not interact with the database, then it will take about one minute to run all the tests, even on large test suites.
For example, the Doctrine 2 project's test suite currently contains about 1000 tests, nearly half of which use a database, and all of which run within 15 seconds using a MySQL database on a standard desktop computer.
Four Stages of Database Test
In his book xUnit Test Patterns, Gerard Meszaros lists four phases of unit testing:
Setting up the fixture
Executing System Under Test
Checking the result
Cleaning (teardown)
What is a fixture?
A fixture describes the initial state of your application and database at the time the test is executed.
Database testing requires at least a setup and cleanup to clear and write the necessary fixture data to your tables. However, there is a good reason for extending the database to fall back on four steps when testing, using the database to form the workflow that runs for each of the tests:
1. Database cleanup
Since there is always a first test that operates on the database, you don't know for sure whether there is already any data in the tables. PHPUnit will perform a TRUNCATE operation on all tables to return them to an empty state.
2. Setting up the fixture
PHPUnit will then iterate through all the specified fixture rows and insert them into the appropriate tables.
3–5. Running the test, checking the result and cleaning
After the database is reset and loaded in its original state, the current test is executed by PHPUnit. This piece of test code does not require knowledge of database extension at all, you can go ahead and test whatever you like with your code.
In your test, use a special assertDataSetsEqual() for validation purposes, however, this is completely optional. This feature will be explained in the Database Assertions section.
PHPUnit Database TestCase Configuration
Typically, when using PHPUnit, your tests will inherit from PHPUnit\Framework\TestCase like this:
assertSame(2, 1 + 1); ) )
If you want to test code that uses a database, setting up such a test is a little more complicated because you need to inherit from another abstract class, TestCase, which requires implementation of two abstract methods getConnection() and getDataSet():
createDefaultDBConnection($pdo, ":memory:"); ) /** * @return PHPUnit\DbUnit\DataSet\IDataSet */ public function getDataSet() ( return $this->createFlatXMLDataSet(dirname(__FILE__)."/_files/guestbook-seed.xml"); ) )
Implementation of getConnection()
For the clearing and loading fixture functionality to work, the PHPUnit database extension requires access to a database connection that is abstracted between providers and the PDO library. It's important to note that your application does not need to be based on PDO to use the PHPUnit database extension, the connection is simply used to clean up and configure the fixture.
In the previous example, we create an SQLite connection in memory and pass it to the createDefaultDBConnection method, which wraps the PDO instance and the second parameter (the database name) in a very simple abstraction layer with a database of type PHPUnit\DbUnit\Database\Connection .
The Using the Database Connection API section explains the API of this interface and how you can best use it.
Implementation of getDataSet()
The getDataSet() method determines what the initial state of the database should be before each test is run. Database state is abstracted using two concepts - DataSet and DataTable, which are represented by interfaces PHPUnit\DbUnit\DataSet\IDataSet And PHPUnit\DbUnit\DataSet\IDataTable respectively. The next section will detail how these concepts work and what their benefits are when used in database testing.
For implementation, we only need to know that the getDataSet() method is called only once during setUp() to retrieve the fixture data set and insert it into the database. In this example we are using the factory method createFlatXMLDataSet($filename), which is a dataset based on an XML representation.
What about the Database Schema (DDL)?
PHPUnit assumes that the database schema with all its tables, triggers, sequences and views is created before the test is run. This means that you as a developer must ensure that your database is configured correctly before running your test suite.
There are several ways to achieve this precondition for testing with a database.
- If you are using a persistent connection database (not SQLite in-memory), you can easily set up the database once using tools like phpMyAdmin for MySQL and reuse the database every time you run the test.
- If you use libraries such as Doctrine 2 or Propel, you can use their API to create a database schema, which you will only need once before running your tests. You can use PHPUnit's bootstrap and configuration capabilities to run this code every time you run your tests.
PHPUnit Database TestCase
From the previous example implementation, you can easily see that the getConnection() method is quite static and can be reused in various test classes using the database. Additionally, to improve test performance and reduce database overhead, you can refactor the code slightly to create a common abstract class for your application's tests that still allows you to specify a different data fixture for each test:
conn === null) ( if (self::$pdo === null) ( self::$pdo = new PDO("sqlite::memory:"); ) $this->conn = $this->createDefaultDBConnection (self::$pdo, ":memory:" ) return $this->conn; ) )
However, this database connection is hard-coded in the PDO connection. PHPUnit has one amazing feature that helps make this test class even more versatile. If you use XML configuration, you can make the database connection customizable for each test run. First, let's create a "phpunit.xml" file in the application's tests/ directory with the following content:
Now we can change the test class to look like this:
conn === null) ( if (self::$pdo === null) ( self::$pdo = new PDO($GLOBALS["DB_DSN"], $GLOBALS["DB_USER"], $GLOBALS["DB_PASSWD "]); ) $this->conn = $this->createDefaultDBConnection(self::$pdo, $GLOBALS["DB_DBNAME"]); ) return $this->conn; ) )
We can now run the database test suite using various configurations from the command line interface:
$ user@desktop> phpunit --configuration developer-a.xml MyTests/ $ user@desktop> phpunit --configuration developer-b.xml MyTests/
The ability to easily run database tests with different configurations is very important if you are developing on a development machine. If multiple developers are running database tests using the same database connection, you can easily experience test failures due to race conditions.
Understanding DataSets and DataTables
The key concept behind PHPUnit database extension is DataSets and DataTables. You should try to understand this simple concept to master database testing using PHPUnit. DataSet and DataTable are layers of abstraction around the rows and columns of databases. A simple API hides the core content of a database in an object structure that can also be implemented by sources other than the database.
This abstraction is necessary to compare the current contents of the database with the expected ones. The expected content can be in the form of XML, YAML, CSV or PHP array files, for example. The DataSet and DataTable interfaces allow comparison of these conceptually different sources by emulating a relational database store in a semantically similar approach.
The workflow for database assertions in your tests therefore consists of three simple steps:
- Identify one or more tables in a database by table name (actual data set)
- Specify the expected data set in the preferred format (YAML, XML, ..)
- Check the statement that both representations of the data set are equal to each other (equivalent).
Assertions are not the only use case for DataSet and DataTable in the PHPUnit database extension. As shown in the previous section, they also describe the original contents of the database. You are forced to define a fixture dataset in Database TestCase, which is then used to:
- Deletes all rows from the tables specified in the dataset.
- Writes all rows to data tables in the database.
Available implementations
There are three different types of datasets/data tables:
- File-based DataSets and DataTables
- Query-based DataSet and DataTable
- Filter and join DataSets and DataTables
File sets of data and tables are typically used for initial fixtures and describe the expected state of the database.
Flat XML DataSet
The most common set is called Flat XML. This is a very simple (flat) XML format, where the tag is inside the root node
This is obviously easy to write. In this example
However, this simplicity comes at a price.
It is not obvious from the previous example how to specify an empty table. You can insert a tag without attributes with the name of an empty table. Then the XML file for an empty guest book table will look like this:
Handling NULL values in simple XML data sets is tedious. The NULL value is different from the empty string value in almost any database (Oracle is an exception), which is difficult to describe in regular XML format. You can represent a NULL value by omitting the attribute from the string (record). If our guestbook allows anonymous entries represented by a NULL value in the "user" column, a hypothetical state of the guestbook table could be:
In our case, the second entry was added anonymously. However, this introduces a serious column definition problem. During data equality statements, each data set must indicate which columns are stored in the table. If the attribute is NULL for all rows in a data table, how will the database extension determine that the column should be part of the table?
A typical XML data set now makes a crucial assumption by declaring that the attributes in the first defined row of a table define the columns of that table. In the previous example, this would mean that "id", "content", "user" and "created" would be the guestbook table columns. For the second row, where the user (“user”) is not defined, a NULL value will be inserted into the database in the “user” column.
When the first guestbook entry is removed from the dataset, only "id", "content" and "created" will be columns in the guestbook table since the "user" column is undefined.
To effectively use the Flat XML dataset when NULL values are important, the first row of each table must not contain NULL values, and only subsequent rows can omit attributes. This can be inconvenient because row order is a significant factor for database assertions.
In turn, if you specify only a subset of table columns in a Flat XML dataset, all missing values will be set to default values. This will only produce errors if one of the missing columns is defined as "NOT NULL DEFAULT NULL".
In conclusion, I can only advise using Flat XML datasets only if you don't need NULL values.
You can instantiate a regular XML dataset inside Database TestCase by calling the method createFlatXmlDataSet($filename) :
createFlatXmlDataSet("myFlatXmlFixture.xml"); ) )
XML DataSet
There is another structured XML dataset that is a little more verbose when written, but doesn't have the problems with NULL values from the Flat XML dataset. Inside the root node
Any specific tag You can instantiate an XML DataSet inside Database TestCase by calling the createXmlDataSet($filename) method: createXMLDataSet("myXmlFixture.xml"); ) ) This new XML format is specifically designed for the MySQL database server. Its support was added in PHPUnit 3.5. Files in this format can be generated using the mysqldump utility. Unlike CSV datasets, which mysqldump also supports, a single file in this XML format can contain data for multiple tables. You can create a file in this format by running mysqldump like this: $ mysqldump --xml -t -u --password= > /path/to/file.xml This file can be used in your Database TestCase by calling the method createMySQLXMLDataSet($filename) : createMySQLXMLDataSet("/path/to/file.xml"); ) ) Alternatively, you can use the YAML dataset for the guestbook example: guestbook: - id: 1 content: "Hello, buddy!" user: "joe" created: 2010-04-24 17:15:23 - id: 2 content: "I like this!" user: created: 2010-04-26 12:14:20 This format is simple and convenient, and most importantly, it solves the problem with NULL in a similar Flat XML data set. NULL in YAML is simply a column name with no value specified. An empty string is specified like this - column1: "" . Currently the YAML DataSet does not have a factory method in Database TestCase, so you need to create one yourself: Another file data set based on the CSV format. Each table in the dataset is represented by one CSV file. For our guestbook example, we define the guestbook-table.csv file: Id,content,user,created 1,"Hey buddy!","joe","2010-04-24 17:15:23" 2,"I like this!","nancy","2010-04- 26 12:14:20" While this is very convenient for editing through Excel or OpenOffice, you cannot specify NULL values in a CSV data set. An empty column will cause an empty value to be inserted into the column in the database. You can create a CSV DataSet like this: addTable("guestbook", dirname(__FILE__)."/_files/guestbook.csv"); return $dataSet; ) ) There is no DataSet-based array (yet) in the PHPUnit database extension, but we can easily implement our own. An example guest book should look like this: [ [ "id" => 1, "content" => "Hi, buddy!", "user" => "joe", "created" => "2010-04-24 17:15:23" ], [ "id" => 2, "content" => "I like this!", "user" => null, "created" => "2010-04-26 12:14:20" ], ], ]); ) ) DataSet PHP has obvious advantages over all other file-based data sets: To make this set look like Flat XML, CSV, or YAML, the keys of the first row listed define the names of the table's columns, which in the previous case would be "id", "content", "user" and "created". The implementation of a DataSet array is simple and straightforward: $rows) ( $columns = ; if (isset($rows)) ( $columns = array_keys($rows); ) $metaData = new DefaultTableMetaData($tableName, $columns); $table = new DefaultTable($metaData); foreach ($rows as $row) ( $table->addRow($row); ) $this->tables[$tableName] = $table) ) protected function createIterator($reverse = false) ( return new DefaultTableIterator($ this->tables, $reverse); ) public function getTable($tableName) ( if (!isset($this->tables[$tableName])) ( throw new InvalidArgumentException("$tableName is not a table in the current database. "); ) return $this->tables[$tableName]; ) ) For database assertions, you not only need a file-based dataset, but also a Query/SQL-based dataset containing the actual contents of the database. The Query DataSet is shown here: getConnection()); $ds->addTable("guestbook"); Adding a table simply by name is an implicit way to define a data table with the following query: getConnection()); $ds->addTable("guestbook", "SELECT * FROM guestbook"); You can use it by specifying arbitrary queries on your tables, such as limiting the number of rows, columns, or adding an ORDER BY clause: getConnection()); $ds->addTable("guestbook", "SELECT id, content FROM guestbook ORDER BY created DESC"); The Database Assertions section will provide details on how to use the Query DataSet. When you access a test connection, you can automatically create a DataSet that consists of all the tables with their contents in the database specified as the second parameter, for the factory join method. You can either create a dataset for the entire database, as shown in testGuestbook() , or limit it to a set of specified table names using a whitelist, as shown in the testFilteredGuestbook() method. createDefaultDBConnection($pdo, $database); ) public function testGuestbook() ( $dataSet = $this->getConnection()->createDataSet(); // ... ) public function testFilteredGuestbook() ( $tableNames = ["guestbook"]; $dataSet = $this- >getConnection()->createDataSet($tableNames); // ... ) ) DataSet replacement I've talked about issues with NULL in Flat XML and CSV datasets, but there is a somewhat complex workaround to get both datasets working with NULL. Replace DataSet is a decorator for an existing data set that allows you to replace values in any column of the data set with another replacement value. To get an example of our guestbook working with NULL values, we specify the file as follows:
Then we wrap the Flat XML DataSet in a Replacement DataSet: createFlatXmlDataSet("myFlatXmlFixture.xml"); $rds = new PHPUnit\DbUnit\DataSet\ReplacementDataSet($ds); $rds->addFullReplacement("##NULL##", null); return $rds; ) ) If you have a large fixture file, you can use dataset filtering to create a white and black list of tables and columns that the subset should contain. This is especially useful in combination with a DB DataSet for filtering data set columns. getConnection()->createDataSet(); $filterDataSet = new PHPUnit\DbUnit\DataSet\DataSetFilter($dataSet); $filterDataSet->addIncludeTables(["guestbook"]); $filterDataSet->setIncludeColumnsForTable("guestbook", ["id", "content"]); // .. ) public function testExcludeFilteredGuestbook() ( $tableNames = ["guestbook"]; $dataSet = $this->getConnection()->createDataSet(); $filterDataSet = new PHPUnit\DbUnit\DataSet\DataSetFilter($dataSet ); $filterDataSet->addExcludeTables(["foo", "bar", "baz"]); // only keep the guestbook table! $filterDataSet->setExcludeColumnsForTable("guestbook", ["user", "created"] ); // .. ) ) You cannot use exclude and enable column filtering on the same table at the same time, only on different ones. Additionally, this is only possible for whitelist or blacklist tables, not both. A Composite DataSet is very useful for combining (aggregating) several already existing data sets into one data set. When multiple datasets contain the same table, rows are added in the order specified. For example, if we have two data sets - fixture1.xml:
And fixture2.xml:
Using a composite DataSet, we can combine both fixture files: createFlatXmlDataSet("fixture1.xml"); $ds2 = $this->createFlatXmlDataSet("fixture2.xml"); $compositeDs = new PHPUnit\DbUnit\DataSet\CompositeDataSet(); $compositeDs->addDataSet($ds1); $compositeDs->addDataSet($ds2); return $compositeDs; ) ) When you install a database extension fixture, PHPUnit inserts rows into the database in the order they are specified in your fixture. If your database schema uses foreign keys, this means that you must specify the tables in an order that does not violate foreign key constraints. To understand the internals of DataSets and DataTables, let's take a look at the DataSet interface. You can skip this part if you don't plan to implement your own DataSet or DataTable. The public interface is used inside the assertDataSetsEqual() assertion in Database TestCase to check the quality of a data set. From the IteratorAggregate interface, IDataSet inherits the getIterator() method to iterate over all tables in the data set. The reverse iterator allows PHPUnit to clear table rows in the opposite order in which they were created to satisfy foreign key constraints. Depending on the implementation, different approaches are used to add table instances to a dataset. For example, tables are added internally during creation from a source file to all file-based datasets, such as YamlDataSet, XmlDataSet, or FlatXmlDataSet. The table is also represented by the following interface: Except for the getTableMetaData() method, which is self-explanatory. The methods used are required for various database extension assertions, which are explained in the next chapter. The getTableMetaData() method must return an implementation of the interface PHPUnit\DbUnit\DataSet\ITableMetaData, which describes the structure of the table. It contains the following information: This interface also has an assertion that checks whether two instances of Table Metadata are equal to each other, which is used by the data set equality assertion. The Connection interface has three interesting methods that need to be returned from the getConnection() method in Database TestCase: 2. The createQueryTable() method can be used to create QueryTable instances by passing them the name of the result and the SQL query. This is a convenient method when it comes to result/table assertions, as will be shown in the next section, Database Assertion API. getConnection()->createQueryTable("guestbook", "SELECT * FROM guestbook"); ) ) 3. The getRowCount() method is a convenient way to access the number of rows in a table, not necessarily filtered by an additional where clause. This can be used with a simple equality statement: assertSame(2, $this->getConnection()->getRowCount("guestbook")); ) ) The Database Extension Testing Tool certainly contains assertions that you can use to check the current state of the database, tables, and table row counts. This section describes this functionality in detail: It is often useful to check whether a table contains a certain number of rows. You can easily achieve this without any additional code using API Connection. Let's say we want to check that after inserting rows into our guestbook, we not only have the original two entries that were in all the previous examples, but also a third one that was just added: assertSame(2, $this->getConnection()->getRowCount("guestbook"), "Pre-Condition"); $guestbook = new Guestbook(); $guestbook->addEntry("suzy", "Hello world!"); $this->assertSame(3, $this->getConnection()->getRowCount("guestbook"), "Inserting failed"); ) ) The previous statement is useful, but we definitely want to check the actual contents of the table to ensure that all values have been written to the appropriate columns. This can be achieved using a table assertion. To do this, we need to define a Query Table instance that outputs content by table name and SQL query and compares it to a file/array based dataset: addEntry("suzy", "Hello world!"); $queryTable = $this->getConnection()->createQueryTable("guestbook", "SELECT * FROM guestbook"); $expectedTable = $this->createFlatXmlDataSet("expectedBook.xml") ->getTable("guestbook"); $this-> Now for this statement we have to create a regular XML file expectedBook.xml:
This assertion will only succeed if it is run exactly at 2010–05–01 21:47:08
. Dates are a particular problem when testing against a database, and we can work around this error by omitting the "created" column in the assertion. Adjusted Flat XML file expectedBook.xml should probably now look like this to pass the assertion:
We need to fix the Query Table call: getConnection()->createQueryTable("guestbook", "SELECT id, content, user FROM guestbook"); You can also validate the result of complex queries using the Query Table approach by simply specifying the name of the query result and comparing it to the dataset: getConnection()->createQueryTable("myComplexQuery", "SELECT complexQuery..."); $expectedTable = $this->createFlatXmlDataSet("complexQueryAssertion.xml") ->getTable("myComplexQuery"); $this->assertTablesEqual($expectedTable, $queryTable); ) ) Of course, you can assert the state of multiple tables at once and compare the result set query to the file dataset. There are two different ways for DataSet assertions. You can use the Database (DB) DataSet from Connection and compare it with a file-based dataset. 2. You can create the DataSet yourself: addTable("guestbook", "SELECT id, content, user FROM guestbook"); // additional tables $expectedDataSet = $this->createFlatXmlDataSet("guestbook.xml"); $this->assertDataSetsEqual($expectedDataSet, $dataSet); ) ) No, PHPUnit requires that all database objects be available when the set is run. Before running the test suite, you must create the database, tables, sequences, triggers, and views. The database testing service will help minimize risks when introducing the system into commercial operation. You will be able to check in advance the correctness and security of the database. Database performance is a critical factor in the effectiveness of management and business applications. If searching or writing data is slow, the application's ability to function normally decreases. The only way to find out the cause of poor performance is to take quantitative measurements and determine what is causing the performance problem. Memory usage metrics for tablespace pages and buffer pools (for reading data, for reading indexes), for performing sorts, for running utilities, for directories and cache memory packages, along with performance measurement metrics, are also important for tuning efficient access to data. Make sure that the connections in the database correspond to the design documentation. For all CRUD operations, verify that the corresponding tables and records are updated when the user clicks Save, Refresh, Search, or Delete from the application GUI. ACID properties of transactions include atomicity, consistency, isolation, and strength. During database testing, you should check these four properties. This area requires more extensive testing if the database is distributed. Note that different application modules (such as screens and forms) use the same data and perform CRUD operations differently. Therefore, you need to make sure that the latest state of the data is reflected equally everywhere. The system should show updated values on all forms and screens. This is called data integrity. Today, databases are designed for more than just storing records. They have evolved into very powerful tools that provide developers with ample opportunities to implement business logic at the database level. Examples of powerful database features are "referential integrity", relational constraints, triggers and stored procedures. Thus, using these and many other features offered by the database, developers implement business logic at the database level. The tester must ensure that the implemented business logic is correct and works accurately. In order to properly organize the database testing process, testers must have good knowledge of SQL and DML (Data Manipulation Language) and have a clear understanding of the internal structure of the database. This is the best and most reliable way to test the database, especially for applications with low and medium complexity. But the two described prerequisites must be met. If the application is very complex, then it will be difficult or even impossible for the tester to write all the necessary SQL queries themselves. Therefore, in case of some complex queries, the tester can turn to the developer for help. This method not only gives confidence that testing is done well, but also improves the skill of writing SQL queries. If the tester does not know SQL, then he can check the result of the CRUD operation using the application's graphical interface by viewing the tables (relations) of the database. This method of checking a database requires good knowledge of table structure and can be a bit tedious and cumbersome, especially when the database and tables have a large amount of data. This method of checking a database can be difficult for testers if the data being tested is located in several tables. The tester performs any CRUD operations on the GUI and verifies their results by executing the corresponding SQL queries written by the developer. This method does not require either good knowledge of SQL or good knowledge of the application database structure. The method seems simple and a good choice for database testing. But its downside is chaos. What if the query written by the developer is semantically incorrect or does not fulfill the user's requirements correctly? In this case, testing does not provide any guarantee about the quality of the product. Databases and database processes should be tested as an independent subsystem. In this case, all subsystems without the target user interface as an interface to the data must be tested. Additional research should be done in the database management system (DBMS) to determine the tools and techniques to support the testing defined in the following table. Validating the database to ensure that the data is populated correctly and that all database events occur as expected, or validating the returned data to ensure that the correct data is retrieved when necessary. Processes must be called manually. Small or minimally sized databases (with a limited number of records) should be used to extend the scope of all corrupted events. Laravel provides many useful tools for testing your database applications. First, you can use the PHP helper method seeInDatabase() to check that the data in the database meets a certain set of criteria. For example, if you want to check that the users table has a record with the email field equal to [email protected], you can do the following: PHP $this -> seeInDatabase("users" , [ Of course, methods such as PHP seeInDatabase() created for convenience. You can use any of PHPUnit's built-in validation methods to augment your tests. It is often useful to reset your database after each test so that data from the previous test does not affect subsequent tests. One way to reset the database state is to roll back the database after each test and migrate it before the next test. Laravel provides a simple DatabaseMigrations trait that will automatically do this for you. Just use this trait on your test class and everything will be done for you: PHP /** Another way to reset the DB state is to wrap each test case in a DB transaction. And for this, Laravel also provides a handy DatabaseTransactions trait that will automatically do this for you: PHP Use Illuminate\Foundation\Testing\WithoutMiddleware; class ExampleTest extends TestCase /** By default, this trait will only wrap the default database connection in a transaction. If your application uses multiple database connections, you need to define the PHP property $connectionsToTransact in your test class. This property should be an array of connection names to perform transactions on. When testing, you may need to insert multiple records into your DB before running the test. When creating this data, instead of manually specifying the values of each column, Laravel allows you to define a standard set of attributes for each of your Eloquent models using factories. First, look at the database/factories/ModelFactory.php file in your application. Initially, this file contains the definition of one factory: PHP $factory -> define (App \ User ::class, function (Faker \ Generator $faker ) ( Return [ In a closure that serves as a factory definition, you can return standard test values for all model attributes. The closure will receive an instance of the Faker PHP library, which allows you to conveniently generate various random data for testing. Of course, you can add your own additional factories in the ModelFactory.php file. You can also create additional factory files for each model for clearer organization. For example, you can create UserFactory.php and CommentFactory.php files in your database/factories folder. All files in the factories folder will be automatically downloaded by Laravel. States allow you to define individual changes that can be applied to your model factories in any combination. For example, your User model might have a delinquent state that changes the default value of one of the attributes. You can define your state transformations using the PHP method state() : PHP $factory -> state (App \ User ::class, "delinquent" , function ($faker ) ( Once the factories are defined, you can use the PHP global function factory() in your tests or seed files to generate model instances. So let's look at some examples of creating models. Firstly, we use the PHP method make() to create models, but will not save them in the database: PHP public function testDatabase() You can also create a collection of models or create models of a specific type: PHP $users = factory (App\User::class, 3)->make(); You can also apply any to models. If you want to apply multiple state changes to models, you need to specify the name of each state to apply: PHP $users = factory (App \ User ::class, 5 )-> states ( "delinquent" ) -> make (); $users = factory (App \ User ::class, 5 ) -> states ( "premium" , "delinquent" ) -> make (); Attribute Override If you want to override some of the default values of your models, you can pass an array of values to the PHP method make(). Only the specified values will be replaced, and the rest will be set as specified in the factory: PHP $user = factory (App\User::class)->make ([ PHP Method create() not only creates model instances, but also stores them in the database using the Eloquent PHP method save() : PHP public function testDatabase() // Create three instances of App\User... // Using the model in tests... You can override model attributes by passing an array to a PHP method create():PHP make()); Closure Relationships and Attributes You can also attach relationships to models using closure attributes in your factory definitions. For example, if you wanted to create a new instance of the User model when creating a Post, you could do this: PHP $factory -> This closure also receives a specific array of attributes from the factory that contains it: PHP $factory -> define (App \ Post ::class, function ($faker ) ( Database testing is necessary to ensure the functionality of the database. To do this, queries are drawn up in the database of various types: for sampling, with calculated fields, parametric, with data grouping, for updating, for deleting. Example query: Display a list of books taken by a specific student. Set your full name as a parameter. Example query: Display a list of books by a specific author indicating storage locations in the library. Set the author's full name as a parameter. Example request: Determine by library card number which class the corresponding student is in and who his class teacher is. Rice. 15. Query 3. “Find a student by library card number and determine in which class he is studying” Example request: Determine by Student_Full name in which class the debtor is studying and who his class teacher is. For the convenience of working with records of various tables, it was created, with which you can open any table necessary to view, update and change information. The button form is shown in Fig. 17. Rice. 17. Database button form CONCLUSION The final qualifying work was carried out on the current topic “Development of an information system for a rural school library.” The goal of the diploma design to develop an information system for the school library of the Saratov region, Fedorovsky district of the municipal educational institution secondary school in the village of Solnechny has been achieved. During the graduation project the following tasks were solved: Consider the library as an element of the educational environment; Study the government concept of supporting and developing children's reading; Technologies of work of libraries of educational institutions are analyzed; The subject area is described based on the survey; -a technical specification for the development of an information system for a rural school library was developed; - a functional model of the school library’s activities was built; - description of input and output information flows; an information system based on a DBMS has been developedACCess;
- the developed relational database was tested. In the final qualifying work for the construction of an information system that provides automation of manual operations to ensure the processes of storage, search, accounting for issuance and return by students, a technical specification was developed based on an analysis of the results of a survey of the subject area. The technical specifications (TOR) reflected the requirements of the system users – library workers. Based on the technical specifications, a functional model of the activities of a rural school library has been developed. The functional model, in turn, served as material for identifying non-automated areas in the library’s work. The choice of a DBMS as a development environment was determined by the technical capabilities of the rural library. As a result, based on the DBMS AccessThe core of the information system – the database – was built. A push-button interface has been developed for user convenience. Corresponding queries have been developed to test the database. Completing these queries allows us to judge the normal performance of the information system for a rural school library. BIBLIOGRAPHICAL LIST 1) Goals and objectives…………………………………………………………... 3 2) Description of the database …………………………………………………... 4 3) Working with the database …………………………………………………… 6 4) Load testing of the database……………………………...11 5) Conclusion……………………………………………………………………....15 6) Literature………………………………………………………………....16 Goals and objectives Target: create a database of elixirs for the game The Witcher 3, which will contain information about the type of elixirs, their properties, what they are made from, places where they can be found and about the monsters against which they can be used. Create optimized queries for this database and load test it. Tasks: · Create a database schema with at least 5 entities in MYSQL Workbench. Describe these entities and their connections. · Describe the use of this database, describe the main queries, look at their execution time and draw conclusions · Database optimization · Perform load testing using apache-jmeter. Use extensions for it to build graphs. Database Description The course work uses the created Witcher1 database, the main entities of which are tables: Fig.1 Schematic representation of the Witcher1 database The Ingridients table contains the necessary ingredients to create elixirs in the game, which are described in the Elixirs table. Several ingredients are used to create an elixir, but each one is unique to its elixir. It is for this reason that a 1: n (one-to-many) relationship was established between these tables, as shown in the database diagram (Fig. 1). The Ingridients table also contains information about the names of the ingredients (Discription) and where this ingredient can be found (WhereFind). The idElixirs column is a linking column for the Ingridients and Elixirs tables. The Elixirs table contains information on how to use a specific elixir and the name of that elixir. This table is the key table for the other tables. The Locations table contains information about exactly where or near what city a specific ingredient can be found. Table IL contains consolidated information about where and how to find a specific ingredient in a given area and what it is. An n:m (many to many) relationship was established between the Ingridients and Locations tables, since multiple ingredients can be found in multiple locations, as indicated in the IL child table. The Monsters table contains information about the types of monsters in “Witcher 3”, about how to recognize this or that monster and the names characteristic of them. The ML table is a child table of the n: m union of the Location and Monsters tables and contains specific information about how to defeat this particular monster and what elixirs can be used for this, including special witcher signs, as well as in what area and What signs should you use to look for this particular type of monster? Working with the database The Witcher1 database contains information about which elixirs should be used against which monsters, special tactics for especially dangerous monsters, such as: Pestilence Maiden, Devil, Imp, Goblin, etc. Analyzing information from each table in order will take a lot of time, so we will create special queries to the database that will be as useful as possible for the user. · A request for information on how to find a specific monster. This query will contain the keyword JOIN, thanks to which the ML and Monsters tables of the Witcher1 database will be accessed. This request will look like this:
ml JOIN monsters ON monsters.idMonsters=ml.idMonsters; After executing the query, we will receive a fairly large table as output, which is the result of combining two tables. So that the displayed table is not so huge, you can specify which monster to display information about. That is, for, for example, Him, the request will look like this: monsters.MonstersName, monsters.MonstersDiscription, ml.DiscriptionHowFind, ml.idLocations ml JOIN monsters ON monsters.idMonsters=ml.idMonsters where monsters.MonstersName=’Hym’; Which monster this or that ID corresponds to can be found out by querying the Monsters or ML tables. The queries will look like this: SELECT SELECT IdMonsters, MonstersName idMonsters, MonstersName FROM ml; FROM monsters; To check compliance, you can query both the ML and Monsters tables, first joining them by idMonsters. ml.idMonsters, monsters.MonstersName ml JOIN monsters ON ml.idMonsters=monsters.idMonsters ORDER BY monsters.idMonsters; · A request for what elixir is suitable for this monster. To implement this request, a JOIN will be used. The query will be addressed to two tables Elixirs and Monsters and will contain information about when and what elixir to drink in the fight against the monster: monsters.MonstersName ,elixirs.ElixirName, elixirs.ElixirDiscription elixirs JOIN monsters ON elixirs.idElixirs=monsters.idElixirs; · A query about what ingredient is found in a particular area. To implement this request, a JOIN will be used. The query will be addressed to two tables Ingridients and Locations and will contain information about which ingredient is located in which location and information about its type: ingridients.Discription, locations.Discription, ingridients.WhereFind ingredients JOIN locations ON ingridients.idIngridients=locations.idIngridients ORDER BY ingredients.Discription; · UPDATE requests We implement this query for a monster in the Monsters table named Hym. Let's say we want to change his name to Him: monsters SET MonstersName="Him" where idMonsters=1; But, since Hym is correct in the English version, let’s return everything back: monsters SET MonstersName="Hym" Fig.2. Implementing UPDATE queries · "Aggregation" queries. COUNT and COUNT(DISTINCT) Fig.3. Count rows in the Elixirs, Monsters, and Monsters JOIN elixirs tables. The COUNT(DISTINCT) function is used to display the number of non-repeating rows in tables and is a more optimized version of the COUNT family of functions: Fig.4. Counting non-repeating elixirs in the Monsters table. · DELETE function. Let's add another row to the Elixirs table using INSERT: INSERT INTO elixirs VALUES (6,'ForDelete','DiscriptionDelete'); Fig.5. Adding a row to the Elixirs table. Now let's make a request to delete this line, since there is no need for an elixir that will not help in any way in the fight against monsters: Fig.6. Delete the added line. Database Load Testing Now that queries have been completed and access to the database has been established, it can be tested using several parameters: · Response Times Over Time or Response Times over Time – this check displays information for each request about its average response time in milliseconds. · Response Times Distribution or Response Time Distribution - this check displays the number of responses in a certain time interval during which the request was executed. · Response Time Percentiles – This check displays percentiles for response time values. On the graph, the X axis will be percentages, and the Y axis will be the response time. For the most plausible tests, we will set certain parameters: Fig.7. Test parameters Number of Threads(users) – Number of virtual users. In our case, we will set it to 1000 in order to load our database as much as possible. Ramp-Up Period – the period during which all users will be involved. We will check all JOIN requests for their performance when activated simultaneously by several users. The last 3 points are plotters of the checks by which we will test the database. · Fig.7. The result of executing queries during the test Response Times Over Time As can be seen from the graph, the most difficult request to complete was “Monsters&Locations” and required the longest response time. You can verify the reason for the long execution of the request by running the request in the console. The main reason for this delay is that both the Monsters table and the ML table contain lengthy explanations of monsters or where to find them. Because of this, the request takes quite a long time to complete. · Fig.8. The result of executing queries during the test Response Times Distribution. From this graph we can conclude that the number of responses for each of our requests in the same period of time is the same. · The ordinate axis shows the execution time, and the abscissa axis shows percentages of the total quantity. Based on the graph, we can conclude that 90% of requests are executed in the time interval from 0 to 340 milliseconds, and from 5% to 15% the number of requests increases linearly, and then exponentially with a very small coefficient of increase. The remaining 10% is executed in the time interval from 340 milliseconds to 700 milliseconds, which leads to the conclusion that there is a very large load on the database. Conclusion In this course work, all tasks were completed. The database was designed, filled with data, the main possibilities of its use in the form of queries and their execution results were shown. At the end, testing and analysis of its results were carried out, with subsequent conclusions. It should be noted that the database itself was created as only an educational one, so it is not so voluminous. Another important characteristic is security: passwords, if such a table is created, must be stored in encrypted form and protected from unauthorized access. Literature 1. http://phpclub.ru/mysql/doc/- Internet resource “MySQL - reference guide” 2. Schwartz B., Zaitsev P., Tkachenko V. et al. - MySQL. Optimizing Performance (2nd Edition) 3. Thalmann L., Kindal M., Bell C. – “Ensuring high availability of MySQL-based systems” 4. Andrzej Sapkowski – “The Witcher (large collection)”, Number of pages: 571 5. CD PROJECT RED, GOG COM. "The Witcher 3: Wild Hunt." Related information.
,
has a name and requires defining all columns with their names. It can contain zero or any positive number of nested elements
MySQL XML DataSet
YAML DataSet
CSV DataSet
Array DataSet
Query (SQL) DataSet
Database (DB) Dataset
DataSet Filter
Composite DataSet
Beware of foreign keys
Implementing your own DataSets/DataTables
Using the Database Connection API
getConnection()->createDataSet(); ) ) Database Assertion API
Asserting the number of table rows
Table state assertion
Approving the Request Result
Asserting the state of multiple tables
getConnection()->createDataSet(["guestbook"]); $expectedDataSet = $this->createFlatXmlDataSet("guestbook.xml"); $this->assertDataSetsEqual($expectedDataSet, $dataSet); ) ) Frequently Asked Questions
Will PHPUnit (re)create the schema database for each test?
During the database testing process, the operation of the application database is checked for compliance with functional and non-functional requirements. Applications that include a database in their architecture require a database testing procedure, for example: corporate information systems, mobile and web applications.
The problems of identifying database performance bottlenecks are directly related to metrics, performance measurement methods and the technology for their implementation. For large corporations and large databases, the problem of determining database performance has another very important aspect: determining the IT infrastructure for long-term industrial operation of applications. This ultimately leads to a more accurate determination of the initial investment in hardware and underlying software. Since high database performance strongly depends on the platform and equipment, and they are purchased and operated for the long term.
The most important metrics for measuring database performance are:What else should you check when testing the database?
Data mapping
ACID transaction properties
Data integrity
Accuracy of business logic implementation
How to test a database?
Writing SQL Queries
Viewing data in tables
Developer Help
An example of a methodology for testing database data integrity
Goals of the methodology
Testing of database access methods and processes independent of the UI, so that malfunctioning target algorithms or data corruption can be observed and recorded.
Methodology
Call each database access method or process, populating each one with valid and invalid data or data requests.
Oracles(a heuristic mechanism that helps identify a problem)
Outline one or more strategies that can be used in the technique to correctly observe test results. An oracle combines elements of both the method by which an observation can be made and the characteristics of a particular outcome that indicate possible success or failure. Ideally, oracles will perform self-checking, allowing for initial assessment of success or failure by automated tests. However, you should be aware of the risks associated with automatically determining results.
Required Tools
This technique requires the following tools:
Success Criteria
This technique supports testing of all major database access methods and processes.
Specialinformation
Testing may require a DBMS development environment or drivers to enter or change data directly in the database.
{
// Making an application call...
"email" => " [email protected]"
]);
}
Resetting the database after each test
Using Migrations
{
use DatabaseMigrations ;
*
* @return void
*/
{
$this -> visit ("/" )
-> see ("Laravel 5" );
}
}
Using transactions
use Illuminate\Foundation\Testing\DatabaseMigrations;
use Illuminate\Foundation\Testing\DatabaseTransactions;
{
use DatabaseTransactions ;
* Example of a basic functional test.
*
* @return void
*/
{
$this -> visit ("/" )
-> see ("Laravel 5" );
}
}
Creation of factories
static $password ;
"name" => $faker -> name ,
"email" => $faker -> unique()-> safeEmail ,
"password" => $password ?: $password = bcrypt ("secret") ,
"remember_token" => str_random (10 ),
];
});
Factory states
return [
"account_status" => "delinquent" ,
];
});
Using factories
Creating Models
{
$user = factory (App\User::class)->make();
"name" => "Abigail" ,
]);
Permanent models
{
// Create one instance of App\User...
$user = factory (App\User::class)->create();
$users = factory (App\User::class, 3)->create();
}
});
return [
"title" => $faker -> title ,
"content" => $faker -> paragraph ,
"user_id" => function() (
return factory (App\User::class)->create()->id;
}
];
});
return [
"title" => $faker -> title ,
"content" => $faker -> paragraph ,
"user_id" => function() (
return factory (App\User::class)->create()->id;
},
"user_type" => function (array $post ) (
return App\User::find($post["user_id"])->type;
}
];
});
where idMonsters=1;
The COUNT function counts the number of non-empty rows (without NULL inside them) in a given table. COUNT has an optimized version for displaying the number of rows when used for 1 table. For example:
DELETE FROM elixirs WHERE idElixirs=6;
Checking Response Times Over Time
Examination Response Times Distribution
Checking Response Time Percentiles