{"id":4693,"date":"2023-06-01T13:50:00","date_gmt":"2023-06-01T13:50:00","guid":{"rendered":"https:\/\/www.ntspl.co.in\/blog\/?p=4693"},"modified":"2023-06-01T13:55:12","modified_gmt":"2023-06-01T13:55:12","slug":"migrating-a-php-application-to-use-cloud-spanner","status":"publish","type":"post","link":"https:\/\/www.ntspl.co.in\/blog\/migrating-a-php-application-to-use-cloud-spanner\/","title":{"rendered":"Migrating a PHP application to use Cloud Spanner"},"content":{"rendered":"<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<p>PHP is <a href=\"https:\/\/w3techs.com\/technologies\/overview\/programming_language\" target=\"_blank\" rel=\"noopener noreferrer\">used in ~78% of websites<\/a>, making it a popular language for developers. If your application runs PHP and you want to take advantage of <a href=\"https:\/\/cloud.google.com\/spanner\">Google Cloud Spanner<\/a> for its reliability and scalability, this post is for you!<\/p>\n<p>We will highlight the common steps to migrate an existing PHP application to Spanner. The example we work through modifies the e-commerce platform Magento\u2019s Catalog Module to use the Spanner PHP client library. But these principles can be used for any PHP application that makes use of the <a href=\"https:\/\/googleapis.github.io\/google-cloud-php\/#\/docs\/cloud-spanner\/v1.46.3\/spanner\/readme\" target=\"_blank\" rel=\"noopener noreferrer\">Spanner PHP library<\/a>.<\/p>\n<h2>What we aim to accomplish<\/h2>\n<ul>\n<li>Use the PHP client library to configure a connection to the Spanner database and manage a session pool.<\/li>\n<li>Execute CRUD operations on the configured database in a reliable manner with transactions.<\/li>\n<li>Become acquainted with some useful snippets and queries inspired by the Magento application implementation.<\/li>\n<li>Observe how the snippets can be used in an application, along with the usual CRUD operations.<\/li>\n<\/ul>\n<h2>Before we begin<\/h2>\n<ul>\n<li>Consider working through the <a href=\"https:\/\/codelabs.developers.google.com\/codelabs\/magento-cloud-spanner#0\" target=\"_blank\" rel=\"noopener noreferrer\">Magento Codelab<\/a> that leverages the <a href=\"https:\/\/github.com\/searceinc\/magento-spanner-port\" target=\"_blank\" rel=\"noopener noreferrer\">magento-spanner-port<\/a>. While the codelab describes the steps required to get the actual integration working, this post goes into more detail to explain what is happening under the hood.<\/li>\n<li>This document shows a number of examples of how to use Magento with Spanner. The examples are taken from the <a href=\"https:\/\/github.com\/searceinc\/magento-spanner-port\/blob\/main\/lib\/internal\/Magento\/Framework\/DB\/Adapter\/Spanner\/Spanner.php\" target=\"_blank\" rel=\"noopener noreferrer\">SpannerAdapter<\/a> and the <a href=\"https:\/\/github.com\/searceinc\/magento-spanner-port\/blob\/main\/lib\/internal\/Magento\/Framework\/Model\/ResourceModel\/Db\/AbstractDb.php\" target=\"_blank\" rel=\"noopener noreferrer\">AbstractDb<\/a> implementation in the magento-spanner-port. These are the main files of the Magento Spanner adapter implementation.<\/li>\n<\/ul>\n<h2>Setup<\/h2>\n<h3>Authentication<\/h3>\n<p>As the first step, please follow the <a href=\"https:\/\/github.com\/googleapis\/google-cloud-php\/blob\/master\/AUTHENTICATION.md\" target=\"_blank\" rel=\"noopener noreferrer\">Authentication guide<\/a> to ensure the application is authenticated before executing any of the code snippets below.<\/p>\n<h3>Creating a session pool and the connection<\/h3>\n<p>After successfully authenticating, create a session pool to facilitate the connection between the application and Spanner. Creating a session is an expensive operation, so it is recommended <a href=\"https:\/\/github.com\/googleapis\/google-cloud-php\/tree\/master\/Spanner#session-warmup\" target=\"_blank\" rel=\"noopener noreferrer\">to create a persistent pool of sessions<\/a> that can be reused.<\/p>\n<p>The Spanner client library provides a handy way to alleviate this problem by having a cached session pool. More information on using a cached Session Pool and best practices is available <a href=\"https:\/\/cloud.google.com\/spanner\/docs\/sessions#best_practices_when_using_google_client_libraries\">here<\/a>.<\/p>\n<p>With <i>SysVCacheItemPool<\/i>, you can share the cached sessions among multiple processes. The <a href=\"https:\/\/github.com\/searceinc\/magento-spanner-port\/blob\/21de65b795dfbe7b822e4c2236bd1fcd8f1473a8\/lib\/internal\/Magento\/Framework\/DB\/Adapter\/Spanner\/Spanner.php#L82-L92\" target=\"_blank\" rel=\"noopener noreferrer\"><i>CreateSessionPool()<\/i><\/a> method creates and returns <a href=\"https:\/\/googleapis.github.io\/google-cloud-php\/#\/docs\/cloud-spanner\/latest\/spanner\/session\/cachesessionpool\" target=\"_blank\" rel=\"noopener noreferrer\">a session pool object<\/a>. Each session connects to a single database and a session can only execute one transaction at a time. The variable minSessions can be used to set the minimum number of concurrent sessions that is expected. The pool is initialized with minSessions when it is created. Then if more than minSessions are required, new sessions will be created up until maxSessions is reached.<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<p>To learn more about project identifiers in PHP in relation to ftok, please refer to the section of the PHP documentation <a href=\"https:\/\/www.php.net\/manual\/en\/function.ftok.php\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a>.<\/p>\n<h3>Connect to the Spanner database<\/h3>\n<p>The created session pool can be used to make a connection to Spanner in the <a href=\"https:\/\/github.com\/searceinc\/magento-spanner-port\/blob\/21de65b795dfbe7b822e4c2236bd1fcd8f1473a8\/lib\/internal\/Magento\/Framework\/DB\/Adapter\/Spanner\/Spanner.php#L64-L80\" target=\"_blank\" rel=\"noopener noreferrer\"><i>connect()<\/i><\/a> method as shown below. The connection object can be used to perform CRUD operations on Spanner.<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<h3>Warm up the session pool<\/h3>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<h3>Keeping the session alive<\/h3>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<p>The <a href=\"https:\/\/github.com\/googleapis\/google-cloud-php-spanner\/blob\/8a76a300aadaee3aec8523576bf2f4f972d46d68\/src\/Session\/CacheSessionPool.php\" target=\"_blank\" rel=\"noopener noreferrer\">maintain<\/a> method refreshes sessions that would otherwise expire within the next 10 minutes. In some cases, it can also refresh sessions that would otherwise expire in more than 10 minutes in order to distribute refresh calls more evenly. Only minSessions sessions are maintained. Excess sessions are left to expire.<\/p>\n<p><b>Note<\/b>: You should set maxSessions high enough to handle peak concurrency with some additional buffer to allow for growth in peak concurrency. Be aware of memory usage of the process, to avoid the process being killed. Additionally, we recommend setting minSessions equal to maxSessions to avoid the latency cost of having to create new sessions when serving requests. This should minimize tail latencies.<\/p>\n<h2>Executing Transactions on Spanner<\/h2>\n<p>After setting up the connection and creating the session pool successfully, we are all set to try some Insert, Read, Update, and Delete operations on the Spanner database.<\/p>\n<p>The snippets below showcase executing CRUD operations using transactions, where they will be committed to the database if and only if all the enclosed queries are successful.<\/p>\n<h3>Insert<\/h3>\n<p>In the <a href=\"https:\/\/github.com\/searceinc\/magento-spanner-port\/blob\/21de65b795dfbe7b822e4c2236bd1fcd8f1473a8\/lib\/internal\/Magento\/Framework\/DB\/Adapter\/Spanner\/Spanner.php#L232-L250\" target=\"_blank\" rel=\"noopener noreferrer\"><i>insert()<\/i><\/a> method, we use runTransaction to create the sequence of instructions that need to be executed.<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<p>We attempt to insert multiple rows using insertBatch, and if all inserts are successful the transaction is committed to the database.<\/p>\n<h3>Update<\/h3>\n<p>Very similar to insert, updating an existing batch of data can be done using updateBatch in the <a href=\"https:\/\/github.com\/searceinc\/magento-spanner-port\/blob\/21de65b795dfbe7b822e4c2236bd1fcd8f1473a8\/lib\/internal\/Magento\/Framework\/DB\/Adapter\/Spanner\/Spanner.php#L252-L270\" target=\"_blank\" rel=\"noopener noreferrer\">update()<\/a> method.<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<p>Transactions allow you to rollback your updates in case any update of a row fails to process.<\/p>\n<p>While updateBatch uses the mutations API, updates can also be performed using DML statements, with executeUpdate. The same is true for inserts mentioned previously. Refer to <a href=\"https:\/\/cloud.google.com\/spanner\/docs\/dml-versus-mutations\">Comparing DML and Mutations<\/a> for guidelines on when it&#8217;s appropriate to use mutations vs. DML. Please keep in mind that Spanner limits the number of mutations that can be performed in a single transaction. To learn more about how the number of mutations is calculated, please review <a href=\"https:\/\/cloud.google.com\/spanner\/quotas#limits_for_creating_reading_updating_and_deleting_data\">the documentation<\/a>. To find the mutation count for a transaction and learn how to prevent your transactions from hitting this limit, see the <a href=\"https:\/\/cloud.google.com\/spanner\/docs\/commit-statistics\">commit statistics<\/a>documentation.<\/p>\n<h3>Delete<\/h3>\n<p>The <a href=\"https:\/\/github.com\/searceinc\/magento-spanner-port\/blob\/21de65b795dfbe7b822e4c2236bd1fcd8f1473a8\/lib\/internal\/Magento\/Framework\/DB\/Adapter\/Spanner\/Spanner.php#L272-L294\" target=\"_blank\" rel=\"noopener noreferrer\"><i>delete()<\/i><\/a><i><\/i>method deletes the row using transactions to safeguard against partial failed queries.<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<p>In the above code snippet, <i>$where<\/i> contains the WHERE clause condition and <i>$table<\/i> is the name of the table to delete from. Spanner requires the presence of a WHERE clause to prevent accidental deletion of the entire table.<\/p>\n<h3>Reading from the database<\/h3>\n<p>Reading from the database is simpler compared to the transaction queries above, as read queries do not require explicit rollback or commit calls. The example <a href=\"https:\/\/github.com\/searceinc\/magento-spanner-port\/blob\/21de65b795dfbe7b822e4c2236bd1fcd8f1473a8\/lib\/internal\/Magento\/Framework\/DB\/Adapter\/Spanner\/Spanner.php#L154-L164\" target=\"_blank\" rel=\"noopener noreferrer\"><i>fetchAll()<\/i><\/a> method is shown below.<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<p>Here <i>$sql<\/i> is an SQL string that would be used to query for the results over the database.<\/p>\n<p>&nbsp;<\/p>\n<h3>Running any SQL query on the Spanner database<\/h3>\n<p>Queries are made using the <i>execute()<\/i> function on the Spanner connection from the class&#8217;s <a href=\"https:\/\/github.com\/searceinc\/magento-spanner-port\/blob\/21de65b795dfbe7b822e4c2236bd1fcd8f1473a8\/lib\/internal\/Magento\/Framework\/DB\/Adapter\/Spanner\/Spanner.php#L166-L176\" target=\"_blank\" rel=\"noopener noreferrer\"><i>query()<\/i><\/a> method.<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<h3>Closing the connection<\/h3>\n<p>The <a href=\"https:\/\/github.com\/searceinc\/magento-spanner-port\/blob\/21de65b795dfbe7b822e4c2236bd1fcd8f1473a8\/lib\/internal\/Magento\/Framework\/DB\/Adapter\/Spanner\/Spanner.php#L357-L367\" target=\"_blank\" rel=\"noopener noreferrer\"><i>closeConnection()<\/i><\/a> method releases the resources of the Spanner connection. This must be done after all logical operations have been successfully completed by the application, otherwise sessions will be held forever by the previous caller. This will eventually exhaust the session pool, preventing any further requests from being executed.<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<h2>More Useful Queries<\/h2>\n<p>So far, we described examples of basic CRUD operations. Here are a few more interesting operations inspired from the Magento e-commerce implementation using Spanner.<\/p>\n<h3>Convert iterator into array<\/h3>\n<p>Copies the iterator object of results from a query into an array to be used by the application. It&#8217;s important to note that large result sets will use a significant amount of memory, so best practice would be to limit results of your Spanner query.<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<h3>Generate a UUID<\/h3>\n<p>Spanner does not support auto-increment fields and recommends using a UUID. This is because incremental primary keys can create hotspots when accessing data due to the manner in which data is distributed across Spanner servers. See <a href=\"https:\/\/cloud.google.com\/spanner\/docs\/schema-and-data-model#choosing_a_primary_key\">Choosing a primary key<\/a> for more details. You can use the <a href=\"https:\/\/github.com\/searceinc\/magento-spanner-port\/blob\/21de65b795dfbe7b822e4c2236bd1fcd8f1473a8\/lib\/internal\/Magento\/Framework\/DB\/Adapter\/Spanner\/Spanner.php#L307-L329\" target=\"_blank\" rel=\"noopener noreferrer\"><i>generateUUID()<\/i><\/a> method to generate random UUIDs.<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<h3>Sanitize SQL query<\/h3>\n<p>Since Spanner uses a strict type of formatting the query, you can use the following <a href=\"https:\/\/github.com\/searceinc\/magento-spanner-port\/blob\/21de65b795dfbe7b822e4c2236bd1fcd8f1473a8\/lib\/internal\/Magento\/Framework\/DB\/Adapter\/Spanner\/Spanner.php#L369-L401\" target=\"_blank\" rel=\"noopener noreferrer\"><i>sanitizeSQL()<\/i><\/a> method to ensure the value matches the Spanner data type. This is only an example function that handles sanitizing integer data types. It can be expanded in your application to handle other sanitization.<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<h3>Add casting by type to the columns in queries<\/h3>\n<p>As we just mentioned, Spanner is strict on the type of the data being read and written. Although Spanner performs implicit casting, sometimes queries fail when the default implicit casting fails. So, to be safe, we can explicitly specify the cast to be applied to the column using a snippet like the one below. More information on the cast() function can be found <a href=\"https:\/\/cloud.google.com\/spanner\/docs\/conversion_functions\">here<\/a>.<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<h2>Putting everything together<\/h2>\n<p>Taking a few excerpts from the Magento code lab that you worked through, let\u2019s discuss how we can use our learnings in practice.<\/p>\n<h3>Fetching a catalog of items from the database<\/h3>\n<p>Fetching all the items from the database can be as simple as:<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<p>Here <i>$select<\/i> references to the SQL query to select from the database, as described in the \u201cReading from the database\u201d section above.<\/p>\n<p>Considering that Spanner enforces strict types, using the <a href=\"https:\/\/github.com\/searceinc\/magento-spanner-port\/blob\/21de65b795dfbe7b822e4c2236bd1fcd8f1473a8\/lib\/internal\/Magento\/Framework\/DB\/Adapter\/Spanner\/Spanner.php#L343-L355\" target=\"_blank\" rel=\"noopener noreferrer\"><i>addCast()<\/i><\/a> function we learned about, the entire operation of reading from the catalog of items from the database can be framed as:<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<h3>Getting Wishlist or Cart items<\/h3>\n<p>In fetching the required items in the catalog for the wishlist, we would need to run a SQL query for items again. This can be done as follows:<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<p>We also learned about sanitizing the queries to adhere to Spanner\u2019s strict type requirements. To do this, the simple <i>getData()<\/i> method should be modified as shown below:<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<p>The <a href=\"https:\/\/github.com\/searceinc\/magento-spanner-port\/blob\/21de65b795dfbe7b822e4c2236bd1fcd8f1473a8\/lib\/internal\/Magento\/Framework\/DB\/Adapter\/Spanner\/Spanner.php#L369-L401\" target=\"_blank\" rel=\"noopener noreferrer\"><i>sanitizeSql()<\/i><\/a> function from the snippets discussed earlier takes care of the strict type formatting mandated on the queries.<\/p>\n<h3>Fetching data with conditions<\/h3>\n<p>You will need to ensure your query knows the type of the field. Consider the implementation below from the <a href=\"https:\/\/github.com\/searceinc\/magento-spanner-port\/blob\/21de65b795dfbe7b822e4c2236bd1fcd8f1473a8\/lib\/internal\/Magento\/Framework\/Model\/ResourceModel\/Db\/AbstractDb.php#L371-L377\" target=\"_blank\" rel=\"noopener noreferrer\"><i>load()<\/i><\/a> function. The\u00a0 <i>_getLoadSelect()<\/i> method returns a simple MySQL query where strings and numeric values for field types are treated alike, i.e. as strings.<\/p>\n<p>&nbsp;<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<p>To ensure that we are meeting Spanner\u2019s type requirements, replace the function <i>_getLoadSelect()<\/i> with <i>getLoadSelectForSpanner()<\/i> as shown here:<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<p>Where the implementation of <a href=\"https:\/\/github.com\/searceinc\/magento-spanner-port\/blob\/21de65b795dfbe7b822e4c2236bd1fcd8f1473a8\/lib\/internal\/Magento\/Framework\/DB\/Adapter\/Spanner\/Spanner.php#L307-L329\" target=\"_blank\" rel=\"noopener noreferrer\"><i>getLoadSelectForSpanner()<\/i><\/a> is as follows:<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<h3>Saving and updating the objects in your Items<\/h3>\n<p>Here we demonstrate the usage of <a href=\"https:\/\/github.com\/searceinc\/magento-spanner-port\/blob\/21de65b795dfbe7b822e4c2236bd1fcd8f1473a8\/lib\/internal\/Magento\/Framework\/DB\/Adapter\/Spanner\/Spanner.php#L307-L329\" target=\"_blank\" rel=\"noopener noreferrer\"><i>generateUuid()<\/i><\/a>, based on the fact that Spanner strongly recommends the use of random UUIDs instead of incremental numerals in primary keys.<\/p>\n<p>Consider a non-Spanner implementation as below:<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<p>Which can be replaced by the following:<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<p><a href=\"https:\/\/github.com\/searceinc\/magento-spanner-port\/blob\/21de65b795dfbe7b822e4c2236bd1fcd8f1473a8\/lib\/internal\/Magento\/Framework\/Model\/ResourceModel\/Db\/AbstractDb.php#L917-L948\" target=\"_blank\" rel=\"noopener noreferrer\"><i>updateObjectInSpanner()<\/i><\/a> and <a href=\"https:\/\/github.com\/searceinc\/magento-spanner-port\/blob\/21de65b795dfbe7b822e4c2236bd1fcd8f1473a8\/lib\/internal\/Magento\/Framework\/Model\/ResourceModel\/Db\/AbstractDb.php#L846-L868\" target=\"_blank\" rel=\"noopener noreferrer\"><i>saveNewObjectInSpanner()<\/i><\/a> make use of our <a href=\"https:\/\/github.com\/searceinc\/magento-spanner-port\/blob\/21de65b795dfbe7b822e4c2236bd1fcd8f1473a8\/lib\/internal\/Magento\/Framework\/DB\/Adapter\/Spanner\/Spanner.php#L307-L329\" target=\"_blank\" rel=\"noopener noreferrer\"><i>generateUuid()<\/i><\/a> snippet explained earlier.<\/p>\n<p>To save an object:<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<p>To update an existing object:<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<p>For reference, the implementation of <i>prepareDataForSpannerUpdate()<\/i> in <i>updateObjectInSpanner()<\/i> above, is shown below:<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<h3>Deleting an object<\/h3>\n<p>To complete the D in the CRUD, here is an example of safely deleting an object from Spanner in the context of Magento:<\/p>\n<\/div>\n<\/div>\n<div class=\"block-code\">\n<div class=\"article-module h-c-page\">\n<div class=\"h-c-grid uni-paragraph-wrap\">\n<div class=\"uni-paragraph h-c-grid__col h-c-grid__col--8 h-c-grid__col-m--6 h-c-grid__col-l--6 h-c-grid__col--offset-2 h-c-grid__col-m--offset-3 h-c-grid__col-l--offset-3\">\n<pre><code><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"block-paragraph\">\n<div class=\"rich-text\">\n<p>In the Magento example, id columns are generally integers. For other applications it may be useful to validate that the value matches the expected Spanner data type.<\/p>\n<h2>Wrapping it all up<\/h2>\n<p>The basic tools when interfacing with any database are its CRUD operations. This article introduced these operations for Spanner in the context of PHP and Magento, along with other nuances to keep an eye out for. Please refer to the official documentation <a href=\"https:\/\/cloud.google.com\/spanner\/docs\/getting-started\/php\">here<\/a>, to continue your journey with Spanner and PHP.<\/p>\n<\/div>\n<\/div>\n<div class=\"block-related_article_tout\">\n<div class=\"uni-related-article-tout h-c-page\">\n<section class=\"h-c-grid\">\n<div class=\"uni-related-article-tout__inner-wrapper\">\n<p class=\"uni-related-article-tout__eyebrow h-c-eyebrow\">Related Article<\/p>\n<div class=\"uni-related-article-tout__content-wrapper\">\n<div class=\"uni-related-article-tout__image-wrapper\">\n<div class=\"uni-related-article-tout__image\"><\/div>\n<\/div>\n<div class=\"uni-related-article-tout__content\">\n<h4 class=\"uni-related-article-tout__header h-has-bottom-margin\">Django ORM support for Cloud Spanner is now Generally Available<\/h4>\n<p class=\"uni-related-article-tout__body\">Today we&#8217;re happy to announce GA support for Google Cloud Spanner in the Django ORM.<\/p>\n<div class=\"cta module-cta h-c-copy uni-related-article-tout__cta muted\"><span class=\"nowrap\">Read Article<\/span><\/div>\n<\/div>\n<\/div>\n<\/div>\n<p>&nbsp;<\/p>\n<\/section>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>PHP is used in ~78% of websites, making it a popular language for developers. If your application runs PHP and you want to take advantage of Google Cloud Spanner for its reliability and scalability, this post is for you! We will highlight the common steps to migrate an existing PHP application to Spanner. The example [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":7250,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[524,25],"tags":[540,539],"class_list":["post-4693","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cloud","category-php","tag-cloud-spanner","tag-php-app"],"acf":{"custom_meta_title":"Unleashing the Power of Cloud Spanner: Migrating PHP App","meta_description":"Unlock the potential of Cloud Spanner for migrating your PHP app. Experience scalability, high availability, and global consistency for seamless operations. Read This Blog To Know More!!!","meta_keyword":"mysql to spanner, migrate postgres to spanner, \r\ncloud spanner schema migrations, \r\nUnleashing the power of cloud spanner migrating php app java","other_meta_tag":"<meta property=og:type content=\"article\" \/>\r\n<meta property=og:title content=\"Unleashing the Power of Cloud Spanner: Migrating PHP App\"\/>\r\n<meta property=og:description content=\"Unlock the potential of Cloud Spanner for migrating your PHP app. Experience scalability, high availability, and global consistency for seamless operations. Read This Blog To Know More!!!\"\/>\r\n<meta property=\"og:image\" content=\"https:\/\/www.ntspl.co.in\/blog\/wp-content\/uploads\/2022\/02\/PHP-BLOG.jpg\"\/>\r\n<meta property=og:url content=\"https:\/\/www.ntspl.co.in\/blog\/migrating-a-php-application-to-use-cloud-spanner\/\"\/>\r\n<meta property=og:site_name content=NTSPL \/>\r\n<meta name=\"twitter:site\" content=\"@NTSPL\">\r\n<meta name=twitter:card content=\"summary\" \/>\r\n<meta name=twitter:description content=\"Unlock the potential of Cloud Spanner for migrating your PHP app. Experience scalability, high availability, and global consistency for seamless operations. Read This Blog To Know More!!!\"\/>\r\n<meta name=twitter:title content=\"Unleashing the Power of Cloud Spanner: Migrating PHP App\"\/>"},"_links":{"self":[{"href":"https:\/\/www.ntspl.co.in\/blog\/wp-json\/wp\/v2\/posts\/4693"}],"collection":[{"href":"https:\/\/www.ntspl.co.in\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ntspl.co.in\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ntspl.co.in\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ntspl.co.in\/blog\/wp-json\/wp\/v2\/comments?post=4693"}],"version-history":[{"count":2,"href":"https:\/\/www.ntspl.co.in\/blog\/wp-json\/wp\/v2\/posts\/4693\/revisions"}],"predecessor-version":[{"id":7252,"href":"https:\/\/www.ntspl.co.in\/blog\/wp-json\/wp\/v2\/posts\/4693\/revisions\/7252"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.ntspl.co.in\/blog\/wp-json\/wp\/v2\/media\/7250"}],"wp:attachment":[{"href":"https:\/\/www.ntspl.co.in\/blog\/wp-json\/wp\/v2\/media?parent=4693"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ntspl.co.in\/blog\/wp-json\/wp\/v2\/categories?post=4693"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ntspl.co.in\/blog\/wp-json\/wp\/v2\/tags?post=4693"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}