Geekly Articles each Day
📜 ⬆️ ⬇️

Creating a web application in PHP using Firebird and Laravel

firebird-logo Hi Habr!

In the last article I talked about the package for supporting the Firebird DBMS in the Laravel framework. This time we will look at the process of creating a web application using Firebird in PHP using Laravel.

Firebird driver review


In PHP, there are two drivers for working with Firebird DBMS:

Firebird / Interbase Extension Overview


The Firebird / Interbase extension appeared earlier and is the most proven. To install the Firebird / Interbase extension in the php.ini configuration file, uncomment the line
') 
extension=php_interbase.dll

or for unix similar systems string

 extension=php_interbase.so

This extension requires that you have installed the fbclient.dll / gds32.dll client library (for UNIX-like fbclient.so systems) of the corresponding capacity.

Note for Win32 / Win64 users

For this extension of the Windows PATH system variable, the DLL files fbclient.dll or gds32.dll must be available. Although copying DLL files from the PHP directory to the Windows system folder also solves the problem (because the default system directory is in the PATH variable), this is not recommended. This extension requires the following files in the PATH variable: fbclient.dll or gds32.dll.

In Linux, this extension, depending on the distribution, can be installed by one of the following commands (it is necessary to specify the supported versions, it may be necessary to connect a third-party repository):

 apt-get install php5-firebird rpm –ihv php5-firebird yum install php70w-interbase zypper install php5-firebird

This extension uses a procedural approach to writing programs. Functions with the ibase_ prefix can return or accept as one of the parameters the connection identifier, transaction, prepared query, or cursor (the result of a SELECT query). This identifier is of type resource. All allocated resources must be released as soon as they are no longer required. I will not describe each of the functions in detail, you can see their description by reference, instead I will give a few small examples with comments.

 $db = 'localhost:example'; $username = 'SYSDBA'; $password = 'masterkey'; //    $dbh = ibase_connect($db, $username, $password); $sql = 'SELECT login, email FROM users'; //   $rc = ibase_query($dbh, $sql); //       while ($row = ibase_fetch_object($rc)) { echo $row->email, "\n"; } //       ibase_free_result($rc); //      ibase_close($dbh);

Instead of the ibase_connect function, you can use the ibase_pconnect function, which creates so-called persistent connections. In this case, when ibase_close is called, the connection is not closed, all associated resources are released, the default transaction is confirmed, other types of transactions are rolled back. Such a connection can be reused in another session if the connection parameters are the same. In some cases, persistent connections can significantly improve the performance of your web application. This is especially noticeable if the installation costs of the connection are high. They allow the child process to use the same connection throughout the entire life cycle instead of creating it when processing each page that interacts with the SQL server. By this, persistent connections resemble work with a pool of connections. Read more about persistent connections at the link .

Attention!

Many ibase functions allow you not to pass the connection identifier (transaction, prepared request) into them. In this case, these functions use the identifier of the last established connection (started transaction). I do not recommend this, especially if your web application can use more than one connection.

The ibase_query function executes a SQL query and returns a result identifier or true if the query does not return a data set. This function, in addition to the connection identifier (transaction) and the SQL query text, can take a variable number of arguments as the values ​​of the SQL query parameters. In this case, our example looks like this:

 $sql = 'SELECT login, email FROM users WHERE id=?'; $id = 1; //   $rc = ibase_query($dbh, $sql, $id); //       if ($row = ibase_fetch_object($rc)) { echo $row->email, "\n"; } //       ibase_free_result($rc);

Very often, parameterized queries are used repeatedly with a different set of parameter values; in this case, it is recommended to use prepared queries to improve performance. In this case, you must first obtain the ID of the prepared request using the ibase_prepare function, and then execute the prepared request using the ibase_execute function.

 $sql = 'SELECT login, email FROM users WHERE id=?'; //   $sth = ibase_prepare($dbh, $sql); $id = 1; //   $rc = ibase_execute($sth, $id); //       if ($row = ibase_fetch_object($rc)) { echo $row->email, "\n"; } //       ibase_free_result($rc); //    ibase_free_query($sth);

Prepared queries are much more often used when a massive fill of data is needed.

 $sql = 'INSERT INTO users(login, email) VALUES(?, ?)'; //   $sth = ibase_prepare($dbh, $sql); $users = [["user1", "user1@gmail.com"], ["user2", "user2@gmail.com"]]; //   foreach ($users as $user)) { ibase_execute($sth, $user[0], $user[1]); } //    ibase_free_query($sth);

In the latter example, one of the drawbacks of this extension can be seen, namely, functions with a variable number of arguments are not very convenient for parameterized queries. This disadvantage is especially pronounced if you try to write a universal class for executing any queries. It would be much more convenient if the parameters could be passed in one array. Of course, there are workarounds like this:

 function fb_execute ($stmt, $data) { if (!is_array($data)) return ibase_execute($stmt, $data); array_unshift($data, $stmt); $rc = call_user_func_array('ibase_execute', $data); return $rc; }

The Firebird / Interbase extension does not work with named query parameters.

By default, the Firebird / Interbase extension automatically starts a default transaction after connecting. The default transaction is confirmed when the connection is closed by the ibase_close function. It can be confirmed or rolled back earlier if you call the ibase_commit or ibase_rollback methods by passing the connection identifier to them, or not passing anything (if you are using a single connection.)

If you need explicit transaction management, you need to start a transaction using the ibase_trans function. If transaction parameters are not specified, then the transaction will be started with IBASE_WRITE | IBASE_CONCURRENCY | IBASE_WAIT. Description of constants for setting transaction parameters can be found at the link php.net/manual/ru/ibase.constants.php . The transaction must be completed using the ibase_commit or ibase_rollback method passing the transaction identifier to these functions. If you use the functions ibase_commit_ret or ibase_rollback_ret ​​instead of these functions, then the transaction will end as COMMIT RETAIN or ROLLBACK RETAIN.

Comment.

The default transaction parameters are suitable for most cases. The fact is that the connection to the database, as well as all the resources associated with it, is a maximum until the end of the PHP script. Even if you use persistent connections, all associated resources will be released after calling the function ibase_close. Despite what has been said, I strongly recommend completing all allocated resources explicitly by calling the appropriate ibase_ functions.

I strongly do not recommend using the functions ibase_commit_ret and ibase_rollback_ret, since it does not make sense. COMMIT RETAIN and ROLLBACK RETAIN were introduced in order to keep cursors open on desktop applications when a transaction is completed.

 $sql = 'INSERT INTO users(login, email) VALUES(?, ?)'; //   $sth = ibase_prepare($dbh, $sql); $users = [["user1", "user1@gmail.com"], ["user2", "user2@gmail.com"]]; $trh = ibase_trans($dbh, IBASE_WRITE | IBASE_CONCURRENCY | IBASE_WAIT); try { //   foreach ($users as $user)) { $r = ibase_execute($sth, $user[0], $user[1]); if ($r === false) { //   ,   $err_msg = ibase_errmsg(); throw new \Exception($err_msg); } } ibase_commit($trh); } catch(\Exception $e) { ibase_rollback($trh); echo $e->getMessage(); } //    ibase_free_query($sth);

Attention!

ibase functions do not throw an exception in case of an error. Some functions return false if an error occurred. I draw your attention that the result must be compared with false by the strict comparison operator ===. Potentially, an error may occur in the call field of any ibase function. The error text can be found using the ibase_errmsg function. The error code can be obtained using the ibase_errcode function.

The Firebird / Interbase extension allows you to interact with the Firebird server not only through SQL queries, but also using the Service API (see the functions ibase_service_attach, ibase_service_detach, ibase_server_info, ibase_maintain_db, ibase_db_info, ibase_backup, ibase_restore). These functions allow you to get information about the Firebird server, make a backup, restore or get statistics. This functionality is required mainly for database administration, so we will not consider it in detail.

The Firebird / Interbase extension also supports working with Firebird events (see the functions ibase_set_event_handler, ibase_free_event_handler, ibase_wait_event).

PDO extension overview (Firebird driver)


The PDO extension provides a generic interface for accessing various types of databases. Each database driver that implements this interface can represent database-specific functionality as standard extension functions.

PDO and all major drivers are embedded in PHP as loadable modules. To use them, you just need to enable them by editing the php.ini file as follows:

 extension=php_pdo.dll

Comment

This step is optional for versions of PHP 5.3 and higher, since PDOs no longer require DLLs to work.

Next, you need to select DLLs of specific databases and either load them at runtime with the dl () function, or enable them in php.ini after php_pdo.dll. For example:

 extension=php_pdo.dll extension=php_pdo_firebird.dll

These DLLs must be located in the extension_dir directory. The pdo_firebird driver requires that you have the fbclient.dll / gds32.dll client library installed (for UNIX-like systems fbclient.so) of the corresponding capacity.

In Linux, this extension, depending on the distribution, can be installed by one of the following commands (it is necessary to specify the supported versions, it may be necessary to connect a third-party repository):

 apt-get install php5-firebird rpm –ihv php5-firebird yum install php70w-firebird zypper install php5-firebird

PDO uses an object-oriented approach to writing programs. Which driver will be used in the PDO depends on the connection string, also called DSN (Data Source Name). A DSN consists of a prefix that defines the type of database, and a set of parameters in the form <key> = <value>, separated by a semicolon ";". The valid parameter set depends on the type of database. To work with Firebird, the connection string must begin with the firebird: prefix and have the form described in the documentation in the PDO_FIREBIRD DSN section.

Connections are established automatically when you create a PDO object from its base class. The class constructor takes arguments to specify a data source (DSN), as well as an optional username and password (if any). The fourth argument can be an array of driver-specific connection settings in the format key => value.

 $dsn = 'firebird:dbname=localhost:example;charset=utf8;'; $username = 'SYSDBA'; $password = 'masterkey'; try { //    $dbh = new \PDO($dsn, $username, $password, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]); $sql = 'SELECT login, email FROM users'; //   $query = $dbh->query($sql); //       while ($row = $query->fetch(\PDO::FETCH_OBJ)) { echo $row->email, "\n"; } $query->closeCursor(); //   } catch (\PDOException $e) { echo $e->getMessage(); }

By setting the \ PDO :: ATTR_ERRMODE property to \ PDO :: ERRMODE_EXCEPTION, we set the mode in which any error, including an error while connecting to the database, will throw an \ PDOException exception. Working in this mode is much more convenient than checking for an error after each call to ibase_ functions.

In order for PDO to use persistent connections, you must pass PDO :: ATTR_PERSISTENT => true to the PDO constructor in the property array.

The query method executes a SQL query and returns the result set as an \ PDOStatement object. In addition to SQL queries, you can pass to this method how to return values ​​during the fetch. It can be a column, an instance of a given class, an object. You can see the various ways of calling by the link http://php.net/manual/ru/pdo.query.php .

If you need to execute a SQL query that does not return a data set, then you can use the exec method, which returns the number of rows involved. This method does not support the execution of prepared queries.

If the request uses parameters, then you must use prepared queries. In this case, instead of the query method, you must call the prepare method. This method returns an object of the \ PDOStatement class, which encapsulates methods for working with prepared queries and their results. To execute the query, you must call the execute method, which can take as an argument an array with named or unnamed parameters. The result of a selective query can be obtained using the methods fetch , fetchAll , fetchColumn , fetchObject . The fetch and fetchAll methods can return results in various forms: an associative array, an object, or an instance of a particular class. The latter is quite often used in the MVC pattern when working with models.

 $dsn = 'firebird:dbname=localhost:example;charset=utf8;'; $username = 'SYSDBA'; $password = 'masterkey'; try { //    $dbh = new \PDO($dsn, $username, $password, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]); $sql = 'INSERT INTO users(login, email) VALUES(?, ?)'; $users = [ ["user1", "user1@gmail.com"], ["user2", "user2@gmail.com"] ]; //   $query = $dbh->prepare($sql); //   foreach ($users as $user)) { $query->execute($user); } } catch (\PDOException $e) { echo $e->getMessage(); }

An example of using named parameters.

 $dsn = 'firebird:dbname=localhost:example;charset=utf8;'; $username = 'SYSDBA'; $password = 'masterkey'; try { //    $dbh = new \PDO($dsn, $username, $password, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]); $sql = 'INSERT INTO users(login, email) VALUES(:login, :email)'; $users = [ [":login" => "user1", ":email" => "user1@gmail.com"], [":login" => "user2", ":email" => "user2@gmail.com"] ]; //   $query = $dbh->prepare($sql); //   foreach ($users as $user)) { $query->execute($user); } } catch (\PDOException $e) { echo $e->getMessage(); }

Comment

To support the named parameters, the PDO prepares the request and replaces parameters of the form: paramname with “?”, While maintaining an array of correspondences between the name of the parameter and the numbers of its positions in the request. For this reason, the EXECUTE BLOCK statement will not work if variables marked with a colon are used inside it. At the moment, there is no way to make the PDO work with the EXECUTE BLOCK operator otherwise, for example, to specify an alternative prefix of parameters, as is done in some access components.

You can pass parameters to the request in another way, using the so-called binding. The bindValue method binds a value to a named or unnamed parameter. The bindParam method binds a variable to a named or unnamed parameter. The latter method is especially useful for stored procedures that return a value through the OUT or IN OUT parameter (in Firebird, the mechanism for returning values ​​from stored procedures is different).

 $dsn = 'firebird:dbname=localhost:example;charset=utf8;'; $username = 'SYSDBA'; $password = 'masterkey'; try { //    $dbh = new \PDO($dsn, $username, $password, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]); $sql = 'INSERT INTO users(login, email) VALUES(:login, :email)'; $users = [ ["user1", "user1@gmail.com"], ["user2", "user2@gmail.com"] ]; //   $query = $dbh->prepare($sql); //   foreach ($users as $user)) { $query->bindValue(":login", $user[0]); $query->bindValue(":email", $user[1]); $query->execute(); } } catch (\PDOException $e) { echo $e->getMessage(); }

Attention

The numbering of unnamed parameters in the bindParam and bindValue methods starts with 1.

 $dsn = 'firebird:dbname=localhost:example;charset=utf8;'; $username = 'SYSDBA'; $password = 'masterkey'; try { //    $dbh = new \PDO($dsn, $username, $password, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]); $sql = 'INSERT INTO users(login, email) VALUES(?, ?)'; $users = [ ["user1", "user1@gmail.com"], ["user2", "user2@gmail.com"] ]; //   $query = $dbh->prepare($sql); //   foreach ($users as $user)) { $query->bindValue(1, $user[0]); $query->bindValue(2, $user[1]); $query->execute(); } } catch (\PDOException $e) { echo $e->getMessage(); }

By default, the PDO automatically confirms the transaction after the execution of each SQL query, if you need explicit transaction management, you must start the transaction using the \ PDO :: beginTransaction method . By default, the transaction starts with the parameters CONCURRENCY | WAIT | READ_WRITE. You can complete the transaction using the \ PDO :: commit or \ PDO :: rollback method.

 $dsn = 'firebird:dbname=localhost:example;charset=utf8;'; $username = 'SYSDBA'; $password = 'masterkey'; try { //    $dbh = new \PDO($dsn, $username, $password, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]); //        $dbh->beginTransaction(); //      $users_stmt = $dbh->prepare('SELECT login, email FROM old_users'); $users_stmt->execute(); $users = $users_stmt->fetchAll(\PDO::FETCH_OBJECT); $users_stmt->closeCursor(); //      $sql = 'INSERT INTO users(login, email) VALUES(?, ?)'; //   $query = $dbh->prepare($sql); //   foreach ($users as $user)) { $query->bindValue(1, $user->LOGIN); $query->bindValue(2, $user->EMAIL]); $query->execute(); } //   $dbh->commit(); } catch (\PDOException $e) { //      ,   if ($dbh && $dbh->inTransaction()) $dbh->rollback(); echo $e->getMessage(); }

Unfortunately, the beginTransaction method does not provide the ability to change the parameters of a transaction, but you can do a clever trick by setting the transaction parameters with the SET TRANSACTION statement.

 $dbh = new \PDO($dsn, $username, $password); $dbh->setAttribute(\PDO::ATTR_AUTOCOMMIT, false); $dbh->exec("SET TRANSACTION READ ONLY ISOLATION LEVEL READ COMMITTED NO WAIT"); //     // …. $dbh->exec("COMMIT"); $dbh->setAttribute(\PDO::ATTR_AUTOCOMMIT, true);

Below is a summary table of the capabilities of various drivers for working with Firebird.

OpportunityFirebird / Interbase extensionPDO
Programming paradigmFunctionalObject oriented
Supported DBFirebird, Interbase, Yaffil and other Interbase clones.Any database for which there is a PDO driver, including Firebird.
Work with query parametersOnly unnamed parameters are not very convenient to work, because a function with a variable number of arguments is used.It is possible to work with both named and unnamed parameters. It is very convenient to work, however, some Firebird features (EXECUTE BLOCK statement) do not work.
Error processingChecking the result of the ibase_errmsg, ibase_errcode functions. An error may occur after calling any ibase function, and an exception will not be raised.It is possible to set the mode in which any error will lead to the excitation of the exception.
Transaction managementAllows you to set transaction parameters.It does not give the opportunity to set the parameters of the transaction. There is a workaround through the execution of the SET TRANSACTION statement.
Interbase / Firebird specific featuresService API (backup, restore, ..), ., , SQL.

, PDO.

WEB


web , MVC. , , . web .

, MVC. , . , : .php, , . , Yii, Laravel, Symphony, Kohana . Laravel, .

Laravel


Laravel , .


Laravel Composer . Composer, Laravel.

composer windows – Composer-Setup.exe . Composer PATH, Composer .

Composer ,

c Composer
 php -r "copy('https://getcomposer.org/installer', 'composer-setup.php');" php -r "if (hash_file('SHA384', 'composer-setup.php') === 'aa96f26c2b67226a324c27919f1eb05f21c248b987e6195cad9690d5c1ff713d53020a02ac8c217dbf90a7eacc9d141d') { echo 'Installer verified'; } else { echo 'Installer corrupt'; unlink('composer-setup.php'); } echo PHP_EOL;" php composer-setup.php php -r "unlink('composer-setup.php');"


:


composer.phar (phar – ) — PHP , (install, update, ...) . windows, , composer.bat PATH.
echo @php "%~dp0composer.phar" %*>composer.bat

composer .

Laravel:

 composer global require "laravel/installer"

, .

 laravel new fbexample

, . Laravel. :


composer.json, , , , Laravel. : « zofe/rapyd-laravel » — (grids) , « sim1984/laravel-firebird » — Firebird. «sim1984/laravel-firebird» «jacquestvanzuydam/laravel-firebird» ( « Firebird Laravel »). minimum-stability dev, , .

 … "repositories": [ { "type": "package", "package": { "version": "dev-master", "name": "sim1984/laravel-firebird", "source": { "url": "https://github.com/sim1984/laravel-firebird", "type": "git", "reference": "master" }, "autoload": { "classmap": [""] } } } ], …

require :

 "zofe/rapyd": "2.2.*", "sim1984/laravel-firebird": "dev-master"

( )

 composer update

. .

 php artisan vendor:publish

zofe/rapyd.

config/app.php . providers

  Zofe\Rapyd\RapydServiceProvider::class, Firebird\FirebirdServiceProvider::class,

config/databases.conf, . connections

  'firebird' => [ 'driver' => 'firebird', 'host' => env('DB_HOST', 'localhost'), 'port' => env('DB_PORT', '3050'), 'database' => env('DB_DATABASE', 'examples'), 'username' => env('DB_USERNAME', 'SYSDBA'), 'password' => env('DB_PASSWORD', 'masterkey'), 'charset' => env('DB_CHARSET', 'UTF8'), 'engine_version' => '3.0.0', ],

,

 'default' => env('DB_CONNECTION', 'firebird'),

env, .env, . .env

 DB_CONNECTION=firebird DB_HOST=localhost DB_PORT=3050 DB_DATABASE=examples DB_USERNAME=SYSDBA DB_PASSWORD=masterkey

config/rapid.php , :

 'fields' => [ 'attributes' => ['class' => 'form-control'], 'date' => [ 'format' => 'dmY', ], 'datetime' => [ 'format' => 'dmY H:i:s', 'store_as' => 'Ymd H:i:s', ], ],

, web .


Laravel ORM Eloquent. ORM Eloquent — ActiveRecord . -, . .

, Laravel artisan .

 php artisan make:model Customer

. , :

 namespace App; use Firebird\Eloquent\Model; class Customer extends Model { /** * ,    * * @var string */ protected $table = 'CUSTOMER'; /** *    * * @var string */ protected $primaryKey = 'CUSTOMER_ID'; /** *       * * @var bool */ public $timestamps = false; /** *       * @var string */ protected $sequence = 'GEN_CUSTOMER_ID'; }

, Firebird\Eloquent\Model sim1984/laravel-firebird . , $sequence, .

– Product.

Product
 namespace App; use Firebird\Eloquent\Model; class Product extends Model { /** * ,    * * @var string */ protected $table = 'PRODUCT'; /** *    * * @var string */ protected $primaryKey = 'PRODUCT_ID'; /** *       * * @var bool */ public $timestamps = false; /** *       * @var string */ protected $sequence = 'GEN_PRODUCT_ID'; }


-.

Invoice
 namespace App; use Firebird\Eloquent\Model; class Invoice extends Model { /** * ,    * * @var string */ protected $table = 'INVOICE'; /** *    * * @var string */ protected $primaryKey = 'INVOICE_ID'; /** *       * * @var bool */ public $timestamps = false; /** *       * * @var string */ protected $sequence = 'GEN_INVOICE_ID'; /** *  * * @return \App\Customer */ public function customer() { return $this->belongsTo('App\Customer', 'CUSTOMER_ID'); } /** *    * @return \App\InvoiceLine[] */ public function lines() { return $this->hasMany('App\InvoiceLine', 'INVOICE_ID'); } /** *  */ public function pay() { $connection = $this->getConnection(); $attributes = $this->attributes; $connection->executeProcedure('SP_PAY_FOR_INOVICE', [$attributes['INVOICE_ID']]); } }


. customer CUSTOMER_ID. belongsTo, . lines -, InvoiceLine ( ). lines hasMany, . Laravel.

pay . SP_PAY_FOR_INVOICE. . ( ) attributes. executeProcedure. sim1984/laravel-firebird.

.

InvoiceLine
 namespace App; use Firebird\Eloquent\Model; use Illuminate\Database\Eloquent\Builder; class InvoiceLine extends Model { /** * ,    * * @var string */ protected $table = 'INVOICE_LINE'; /** *    * * @var string */ protected $primaryKey = 'INVOICE_LINE_ID'; /** *       * * @var bool */ public $timestamps = false; /** *       * * @var string */ protected $sequence = 'GEN_INVOICE_LINE_ID'; /** *     * * @var array */ protected $appends = ['SUM_PRICE']; /** *  * * @return \App\Product */ public function product() { return $this->belongsTo('App\Product', 'PRODUCT_ID'); } /** *    * * @return double */ public function getSumPriceAttribute() { return $this->SALE_PRICE * $this->QUANTITY; } /** *      *   , ..         * * @param \Illuminate\Database\Eloquent\Builder $query * @param array $options * @return bool */ protected function performInsert(Builder $query, array $options = []) { if ($this->fireModelEvent('creating') === false) { return false; } $connection = $this->getConnection(); $attributes = $this->attributes; $connection->executeProcedure('SP_ADD_INVOICE_LINE', [ $attributes['INVOICE_ID'], $attributes['PRODUCT_ID'], $attributes['QUANTITY'] ]); // We will go ahead and set the exists property to true, so that it is set when // the created event is fired, just in case the developer tries to update it // during the event. This will allow them to do so and run an update here. $this->exists = true; $this->wasRecentlyCreated = true; $this->fireModelEvent('created', false); return true; } /** *        *   , ..         * * @param \Illuminate\Database\Eloquent\Builder $query * @param array $options * @return bool */ protected function performUpdate(Builder $query, array $options = []) { $dirty = $this->getDirty(); if (count($dirty) > 0) { // If the updating event returns false, we will cancel the update operation so // developers can hook Validation systems into their models and cancel this // operation if the model does not pass validation. Otherwise, we update. if ($this->fireModelEvent('updating') === false) { return false; } $connection = $this->getConnection(); $attributes = $this->attributes; $connection->executeProcedure('SP_EDIT_INVOICE_LINE', [ $attributes['INVOICE_LINE_ID'], $attributes['QUANTITY'] ]); $this->fireModelEvent('updated', false); } } /** *       *   , ..         * * @return void */ protected function performDeleteOnModel() { $connection = $this->getConnection(); $attributes = $this->attributes; $connection->executeProcedure('SP_DELETE_INVOICE_LINE', [$attributes['INVOICE_LINE_ID']]); } }


product, ( App/Product), . PRODUCT_ID belongsTo.

SumPrice getSumPriceAttribute. , $appends.

insert, update delete , , . , . , . .

, Laravel , , . Laravel . . ,

 $customers = DB::table('CUSTOMER')->get();

SQL . , ,

 DB::table('users') ->join('contacts', function ($join) { $join->on('users.id', '=', 'contacts.user_id')->orOn(...); }) ->get();

. Eloquent ORM laravel.ru/docs/v5/eloquent .

 $customers = Customer::all();

20 .

 $customers = App\Customer::select() ->orderBy('name') ->take(20) ->get();

. , - 1.

 $lines = Invoice::find(1)->lines;

, save.

 $flight = new Flight; $flight->name = $request->name; $flight->save();

, save.

 $flight = App\Flight::find(1); $flight->name = 'New Flight Name'; $flight->save();

delete.

 $flight = App\Flight::find(1); $flight->delete();

destroy. .

 App\Flight::destroy(1);

, «» . .

. , , Eloquent ORM.

 DB::transaction(function () { //       $line = new App\InvoiceLine(); $line->CUSTOMER_ID = 45; $line->PRODUCT_ID = 342; $line->QUANTITY = 10; $line->COST = 12.45; $line->save(); //         $invoice = App\Invoice::find($line->CUSTOMER_ID); $invoice->INVOICE_SUM += $line->SUM_PRICE; $invoice->save(); });

, transaction, .


Laravel . , , .

 Route::get('/', function () { return 'Hello World'; }); Route::post('foo/bar', function () { return 'Hello World'; });

GET , – POST /foo/bar.

HTTP ,

 Route::match(['get', 'post'], 'foo/bar', function () { return 'Hello World'; });

-

 Route::get('posts/{post}/comments/{comment}', function ($postId, $commentId) { // });

. « ». app/Http/routes.php Laravel 5.2 routes/wep.php Laravel 5.3.

, Controller, . app/Http/Controllers.

Laravel App\Http\Controllers\Controller, Laravel . HTTP- .

.

 /* *   */ namespace App\Http\Controllers; use App\Http\Controllers\Controller; use App\Customer; class CustomerController extends Controller { /** *    * * @return Response */ public function showCustomers() { //     20  //    $customers = Customer::select() ->orderBy('NAME') ->take(20) ->get(); var_dump($customers); } }

. routes.php (web.php)

 Route::get('/customers', 'CustomerController@showCustomers');

@.

« zofe/rapyd ». . zofe/rapyd Eloquent ORM. , (grid), , , .

Customer
 /* *   */ namespace App\Http\Controllers; use App\Http\Controllers\Controller; use App\Customer; class CustomerController extends Controller { /** *    * * @return Response */ public function showCustomers() { //     $filter = \DataFilter::source(new Customer); //      $filter->add('NAME', '', 'text'); //     $filter->submit(''); //         $filter->reset(''); //       $grid = \DataGrid::source($filter); //   // , ,  $grid->add('NAME', '', true); $grid->add('ADDRESS', ''); $grid->add('ZIPCODE', ''); $grid->add('PHONE', ''); //    ,     $grid->edit('/customer/edit', '', 'show|modify|delete'); //     $grid->link('/customer/edit', " ", "TR"); //   $grid->orderBy('NAME', 'asc'); //      $grid->paginate(10); //   customer        return view('customer', compact('filter', 'grid')); } /** * ,     * * @return Response */ public function editCustomer() { if (\Input::get('do_delete') == 1) return "not the first"; //   $edit = \DataEdit::source(new Customer()); //         switch ($edit->status) { case 'create': $edit->label(' '); break; case 'modify': $edit->label(' '); break; case 'do_delete': $edit->label(' '); break; case 'show': $edit->label(' '); //         $edit->link('customers', '', 'TR'); break; } //     ,    //     $edit->back('insert|update|do_delete', 'customers'); //    ,    //       $edit->add('NAME', '', 'text')->rule('required|max:60'); $edit->add('ADDRESS', '', 'textarea') ->attributes(['rows' => 3]) ->rule('max:250'); $edit->add('ZIPCODE', '', 'text')->rule('max:10'); $edit->add('PHONE', '', 'text')->rule('max:14'); //   customer_edit      return $edit->view('customer_edit', compact('edit')); } }


Laravel blade. view resources/views, HTML . , , . blade Blade .

:

 @extends('example') @section('title','') @section('body') <h1></h1> <p> {!! $filter !!} {!! $grid !!} </p> @stop

example body. $filter $grid HTML . example .

example.blade
 @extends('master') @section('title', '   Firebird') @section('body') <h1></h1> @if(Session::has('message')) <div class="alert alert-success"> {!! Session::get('message') !!} </div> @endif <p>   Firebird.<br/> </p> @stop @section('content') @include('menu') @yield('body') @stop


master, menu. , , .

 <nav class="navbar main"> <div class="navbar-header"> <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".main-collapse"> <span class="sr-only"></span> <span class="icon-bar"></span> <span class="icon-bar"></span> <span class="icon-bar"></span> </button> </div> <div class="collapse navbar-collapse main-collapse"> <ul class="nav nav-tabs"> <li @if (Request::is('customer*')) class="active"@endif>{!! link_to("customers", "") !!}</li> <li @if (Request::is('product*')) class="active"@endif>{!! link_to("products", "") !!}</li> <li @if (Request::is('invoice*')) class="active"@endif>{!! link_to("invoices", " ") !!}</li> </ul> </div> </nav>

master css JavaScript .

master.blade
 <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>@yield('title', ' Web   Firebird')</title> <meta name="description" content="@yield('description', ' Web   Firebird')" /> @section('meta', '') <link href="http://fonts.googleapis.com/css?family=Bitter" rel="stylesheet" type="text/css" /> <link href="//netdna.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css" rel="stylesheet"> <link href="//maxcdn.bootstrapcdn.com/font-awesome/4.1.0/css/font-awesome.min.css" rel="stylesheet"> {!! Rapyd::styles(true) !!} </head> <body> <div id="wrap"> <div class="container"> <br /> <div class="row"> <div class="col-sm-12"> @yield('content') </div> </div> </div> </div> <div id="footer"> </div> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script> <script src="//netdna.bootstrapcdn.com/bootstrap/3.2.0/js/bootstrap.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery.pjax/1.9.6/jquery.pjax.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/riot/2.2.4/riot+compiler.min.js"></script> {!! Rapyd::scripts() !!} </body> </html>


customer_edit

 @extends('example') @section('title', ' ') @section('body') <p> {!! $edit !!} </p> @stop

.

Product
 /* *   */ namespace App\Http\Controllers; use App\Http\Controllers\Controller; use App\Product; class ProductController extends Controller { /** *    * * @return Response */ public function showProducts() { //     $filter = \DataFilter::source(new Product); //      $filter->add('NAME', '', 'text'); $filter->submit(''); $filter->reset(''); //       $grid = \DataGrid::source($filter); //    // , ,  $grid->add('NAME', '', true); //    2    $grid->add('PRICE|number_format[2,., ]', ''); $grid->row(function($row) { //     $row->cell('PRICE')->style("text-align: right"); }); //    ,     $grid->edit('/product/edit', '', 'show|modify|delete'); //     $grid->link('/product/edit', " ", "TR"); //   $grid->orderBy('NAME', 'asc'); //      $grid->paginate(10); //   customer        return view('product', compact('filter', 'grid')); } /** * ,     * * @return Response */ public function editProduct() { if (\Input::get('do_delete') == 1) return "not the first"; //   $edit = \DataEdit::source(new Product()); //         switch ($edit->status) { case 'create': $edit->label(' '); break; case 'modify': $edit->label(' '); break; case 'do_delete': $edit->label(' '); break; case 'show': $edit->label(' '); $edit->link('products', '', 'TR'); break; } //     ,    //     $edit->back('insert|update|do_delete', 'products'); //    ,    //       $edit->add('NAME', '', 'text')->rule('required|max:100'); $edit->add('PRICE', '', 'text')->rule('max:19'); $edit->add('DESCRIPTION', '', 'textarea') ->attributes(['rows' => 8]) ->rule('max:8192'); //   product_edit      return $edit->view('product_edit', compact('edit')); } }


. . . . . .

Invoice
 /* *    */ namespace App\Http\Controllers; use App\Http\Controllers\Controller; use App\Invoice; use App\Customer; use App\Product; use App\InvoiceLine; class InvoiceController extends Controller { /** *   - * * @return Response */ public function showInvoices() { //      //    $invoices = Invoice::with('customer'); //     $filter = \DataFilter::source($invoices); //      $filter->add('INVOICE_DATE', '', 'daterange'); //      $filter->add('customer.NAME', '', 'text'); $filter->submit(''); $filter->reset(''); //       $grid = \DataGrid::source($filter); //    // , ,  //     ,      $grid->add('INVOICE_DATE|strtotime|date[dmY H:i:s]', '', true); //          $grid->add('TOTAL_SALE|number_format[2,., ]', ''); $grid->add('customer.NAME', ''); //  boolean   / $grid->add('PAID', '') ->cell(function( $value, $row) { return $value ? '' : ''; }); //      $grid->row(function($row) { //     $row->cell('TOTAL_SALE')->style("text-align: right"); //       if ($row->cell('PAID')->value == '') { $row->style("background-color: #ddffee;"); } }); //    ,     $grid->edit('/invoice/edit', '', 'show|modify|delete'); //    - $grid->link('/invoice/edit', " ", "TR"); //   $grid->orderBy('INVOICE_DATE', 'desc'); //      $grid->paginate(10); //   customer        return view('invoice', compact('filter', 'grid')); } /** * ,      * * @return Response */ public function editInvoice() { //    ,    $error_msg = \Request::old('error_msg'); //     $edit = \DataEdit::source(new Invoice()); //   ,        if (($edit->model->PAID) && ($edit->status === 'modify')) { $edit->status = 'show'; $error_msg = '  .   .'; } //   ,        if (($edit->model->PAID) && ($edit->status === 'delete')) { $edit->status = 'show'; $error_msg = '  .   .'; } //         switch ($edit->status) { case 'create': $edit->label(' '); break; case 'modify': $edit->label(' '); break; case 'do_delete': $edit->label(' '); break; case 'show': $edit->label(''); $edit->link('invoices', '', 'TR'); //     ,    if (!$edit->model->PAID) $edit->link('invoice/pay/' . $edit->model->INVOICE_ID, '', 'BL'); break; } //     ,    //      $edit->back('insert|update|do_delete', 'invoices'); //    ,    //      $edit->add('INVOICE_DATE', '', 'datetime') ->rule('required') ->insertValue(date('Ymd H:i:s')); //     .     //     $edit->add('customer.NAME', '', 'autocomplete') ->rule('required') ->options(Customer::lists('NAME', 'CUSTOMER_ID')->all()); //  ,     ,    $edit->add('TOTAL_SALE', '', 'text') ->mode('readonly') ->insertValue('0.00'); //    $paidCheckbox = $edit->add('PAID', '', 'checkbox') ->insertValue('0') ->mode('readonly'); $paidCheckbox->checked_output = ''; $paidCheckbox->unchecked_output = ''; //        $grid = $this->getInvoiceLineGrid($edit->model, $edit->status); //   invoice_edit       //     return $edit->view('invoice_edit', compact('edit', 'grid', 'error_msg')); } /** *    * * @return Response */ public function payInvoice($id) { try { //      $invoice = Invoice::findOrFail($id); //    $invoice->pay(); } catch (\Illuminate\Database\QueryException $e) { //   ,  //    $pos = strpos($e->getMessage(), 'E_INVOICE_ALREADY_PAYED'); if ($pos !== false) { //         return redirect('invoice/edit?show=' . $id) ->withInput(['error_msg' => '  ']); } else throw $e; } //     return redirect('invoice/edit?show=' . $id); } /** *       * @param \App\Invoice $invoice * @param string $mode * @return \DataGrid */ private function getInvoiceLineGrid(Invoice $invoice, $mode) { //     //           $lines = InvoiceLine::with('product')->where('INVOICE_ID', $invoice->INVOICE_ID); //       $grid = \DataGrid::source($lines); //    // , ,  $grid->add('product.NAME', ''); $grid->add('QUANTITY', ''); $grid->add('SALE_PRICE|number_format[2,., ]', '')->style('min-width: 8em;'); $grid->add('SUM_PRICE|number_format[2,., ]', '')->style('min-width: 8em;'); //      $grid->row(function($row) { $row->cell('QUANTITY')->style("text-align: right"); //     $row->cell('SALE_PRICE')->style("text-align: right"); $row->cell('SUM_PRICE')->style("text-align: right"); }); if ($mode == 'modify') { //    ,     $grid->edit('/invoice/editline', '', 'modify|delete'); //     $grid->link('/invoice/editline?invoice_id=' . $invoice->INVOICE_ID, " ", "TR"); } return $grid; } /** * ,       * * @return Response */ public function editInvoiceLine() { if (\Input::get('do_delete') == 1) return "not the first"; $invoice_id = null; //      $edit = \DataEdit::source(new InvoiceLine()); //         switch ($edit->status) { case 'create': $edit->label(' '); $invoice_id = \Input::get('invoice_id'); break; case 'modify': $edit->label(' '); $invoice_id = $edit->model->INVOICE_ID; break; case 'delete': $invoice_id = $edit->model->INVOICE_ID; break; case 'do_delete': $edit->label(' '); $invoice_id = $edit->model->INVOICE_ID; break; } //  url   $base = str_replace(\Request::path(), '', strtok(\Request::fullUrl(), '?')); $back_url = $base . 'invoice/edit?modify=' . $invoice_id; //     $edit->back('insert|update|do_delete', $back_url); $edit->back_url = $back_url; //        $edit->add('INVOICE_ID', '', 'hidden') ->rule('required') ->insertValue($invoice_id) ->updateValue($invoice_id); //     .     //     $edit->add('product.NAME', '', 'autocomplete') ->rule('required') ->options(Product::lists('NAME', 'PRODUCT_ID')->all()); //     $edit->add('QUANTITY', '', 'text') ->rule('required'); //   invoice_line_edit      return $edit->view('invoice_line_edit', compact('edit')); } }


zofe/rapyd , . invoice_edit .

invoice_edit.blade
 @extends('example') @section('title',' ') @section('body') <div class="container"> {!! $edit->header !!} @if($error_msg) <div class="alert alert-danger"> <strong>!</strong> {{ $error_msg }} </div> @endif {!! $edit->message !!} @if(!$edit->message) <div class="row"> <div class="col-sm-4"> {!! $edit->render('INVOICE_DATE') !!} {!! $edit->render('customer.NAME') !!} {!! $edit->render('TOTAL_SALE') !!} {!! $edit->render('PAID') !!} </div> </div> {!! $grid !!} @endif {!! $edit->footer !!} </div> @stop


, , , . , app/Http/routes.php Laravel 5.2 routes/wep.php Laravel 5.3.

 //   Route::get('/', 'InvoiceController@showInvoices'); Route::get('/customers', 'CustomerController@showCustomers'); Route::any('/customer/edit', 'CustomerController@editCustomer'); Route::get('/products', 'ProductController@showProducts'); Route::any('/product/edit', 'ProductController@editProduct'); Route::get('/invoices', 'InvoiceController@showInvoices'); Route::any('/invoice/edit', 'InvoiceController@editInvoice'); Route::any('/invoice/pay/{id}', 'InvoiceController@payInvoice'); Route::any('/invoice/editline', 'InvoiceController@editInvoiceLine');

/invoice/pay/{id} payInvoice. .

.





. https://github.com/sim1984/phpfbexample

Source: https://habr.com/ru/post/317458/

More articles:


All Articles