<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Smin on Medium]]></title>
        <description><![CDATA[Stories by Smin on Medium]]></description>
        <link>https://medium.com/@sminrana?source=rss-c0bbdd9a58c5------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*Hjeu6wxVLt7cjidK9sMT2g.jpeg</url>
            <title>Stories by Smin on Medium</title>
            <link>https://medium.com/@sminrana?source=rss-c0bbdd9a58c5------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 07:36:40 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@sminrana/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[MySQL RANGE Partition]]></title>
            <link>https://sminrana.medium.com/mysql-range-partition-4ef024ce9168?source=rss-c0bbdd9a58c5------2</link>
            <guid isPermaLink="false">https://medium.com/p/4ef024ce9168</guid>
            <dc:creator><![CDATA[Smin]]></dc:creator>
            <pubDate>Mon, 17 Oct 2022 09:10:09 GMT</pubDate>
            <atom:updated>2022-10-17T09:10:42.221Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/934/1*MlEDtLfJxwk2zL6wSasDlQ.png" /></figure><h3>When partition doesn’t help</h3><ul><li>A lot of relations in the table.</li><li>A small table and won’t grow in the future.</li><li>Don’t use a partition unless you have 1M rows.</li></ul><p>It’s tempting to believe that partition will solve performance problems. But it is so often wrong. Partitioning splits up one table into several smaller tables. But table size is rarely a performance issue. Instead, I/O time and indexes are the issues.</p><h3>What is Partitioning?</h3><p>Partitioning is a physical database design technique that many data modelers and DBAs are quite familiar with. Although partitioning can be used to accomplish a number of various objectives, the main goal is to reduce the amount of data read for particular SQL operations so that overall response time is reduced.</p><ol><li><strong>Horizontal Partitioning</strong> — this form of partitioning segments table rows so that distinct groups of physical row-based datasets are formed that can be addressed individually (one partition) or collectively (one-to-all partitions). All columns defined to a table are found in each set of partitions so no actual table attributes are missing. An example of horizontal partitioning might be a table that contains ten years worth of historical invoice data being partitioned into ten distinct partitions, where each partition contains a single year’s worth of data</li></ol><p><strong>Range</strong> — this partitioning mode allows a DBA to specify various ranges for which data is assigned. For example, a DBA may create a partitioned table that is segmented by three partitions that contain data for the 1980’s, 1990’s, and everything beyond and including the year 2000.</p><p><strong>Hash</strong> — this partitioning mode allows a DBA to separate data based on a computed hash key that is defined on one or more table columns, with the end goal being an equal distribution of values among partitions. For example, a DBA may create a partitioned table that has ten partitions that are based on the table’s primary key.</p><p><strong>Key</strong> — a special form of Hash where MySQL guarantees even distribution of data through a system-generated hash key.</p><p><strong>List</strong> — this partitioning mode allows a DBA to segment data based on a pre-defined list of values that the DBA specifies. For example, a DBA may create a partitioned table that contains three partitions based on the years 2004, 2005, and 2006.</p><p><strong>Composite</strong> — this final partitioning mode allows a DBA to perform sub-partitioning where a table is initially partitioned by, for example range partitioning, but then each partition is segmented even further by another method (for example, hash).</p><p><strong>2. Vertical Partitioning</strong> — this partitioning scheme is traditionally used to reduce the width of a target table by splitting a table vertically so that only certain columns are included in a particular dataset, with each partition including all rows. An example of vertical partitioning might be a table that contains a number of very wide text or BLOB columns that aren’t addressed often being broken into two tables that has the most referenced columns in one table and the seldom-referenced text or BLOB data in another.</p><h3>Why the Range partition is the best choice in my case</h3><pre>CREATE TABLE `HISTORY` (<br>  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,<br>  `symbol` varchar(16) NOT NULL,<br>  `open` float DEFAULT NULL,<br>  `high` float DEFAULT NULL,<br>  `low` float DEFAULT NULL,<br>  `close` float DEFAULT NULL,<br>  `volume` double DEFAULT &#39;0&#39;,<br>  `exchange` varchar(8) NOT NULL,<br>  `timestamp` int(16) NOT NULL DEFAULT &#39;0&#39;,<br>  `date` varchar(16) DEFAULT NULL,<br>  `update_date_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,<br>  PRIMARY KEY (`id`),<br>  KEY `symbol` (`symbol`),<br>  KEY `exchange` (`exchange`),<br>  KEY `timestamp` (`timestamp`)<br>) ENGINE=InnoDB AUTO_INCREMENT=144753037 DEFAULT CHARSET=utf8;</pre><p>My table structure is very simple. We will store <a href="https://finance.yahoo.com/quote/C/?guccounter=1&amp;guce_referrer=aHR0cHM6Ly93d3cuZ29vZ2xlLmNvbS8&amp;guce_referrer_sig=AQAAAHpIPbWASakCujwphpauNocaBVo9myDbxvhG33covP59_nhhK6fnVgl9GhyALt4eTkDJc39lZ4OpcgkgOcTL_OUOfCKtGk10upcNdcSvE-2gZZCi7KYsPfcQyVOUypV-az-ZZa1mNANWXxAIekmcv97h3l-HyKSZjyuuyVb6m5ya">Citigroup Inc</a> daily stock data. Example dataset in <a href="https://www.sminrana.com/wp-content/uploads/2022/10/C.csv">CSV</a>.<br>Since I have another 30K different company stock data like Citygroup and my total rows of this table is 128,947,460 at this moment. A Range partition is perfect in this case.</p><p>I will partition the table by year, like all the 2018 data in a partition called ‘p_2018’ and for the year 2019 it is ‘p_2019’.</p><p>My primary key is ID which is AUTO_INCREMENT. I have to find the ID of first trading day of the next year 2019 (1st January), then I can create a partition for all 2018 data. ID 56839003 is the first trading of the year 2019.</p><pre>PARTITION p_2018 VALUES less than (56839003)</pre><p>Now p_2018 has all the data from 2018, we can create another partition 2019, 2020 and so on.</p><h4>How my existing SQL query gonna affected</h4><p>None of your SQL need to be changed.<br>But you can take advantage of the partition by adding your partition name in your SQL SELECT, INSERT queries.</p><pre>SELECT * FROM  `HISTORY` PARTITION (`p_2018`)&amp;nbsp; WHERE  `symbol`=&#39;C&#39;;</pre><h3>PARTITION SQLs</h3><h3>SHOW PARTITION</h3><pre>SHOW CREATE TABLE HISTORY</pre><h3>ADD PARTITION</h3><pre>ALTER TABLE `HISTORY` </pre><pre>PARTITION BY RANGE(`id`) </pre><pre>(</pre><pre> PARTITION p_2018 VALUES less than (56839003),</pre><pre> PARTITION p_2019 VALUES less than (61466903),</pre><pre> PARTITION p_others VALUES LESS THAN MAXVALUE</pre><pre>);</pre><h3>DROP PARTITION</h3><pre>ALTER TABLE t1 DISCARD PARTITION p_2018, p_2019 TABLESPACE;</pre><h3>DELETE PARTITION</h3><p><strong>All partitions</strong></p><pre>ALTER TABLE t1 REMOVE PARTITIONING;</pre><p><strong>One Partition</strong></p><pre>ALTER TABLE t1 DROP PARTITION p_2018, p_2019;</pre><h3>IMPORT PARTITION</h3><p><strong>Import Tablespace Partition</strong></p><pre>ALTER TABLE t1 IMPORT PARTITION p_2018, p_2019 TABLESPACE;</pre><h3>REORGANIZE PARTITION</h3><pre>ALTER TABLE SIGNALS <br>REORGANIZE PARTITION p_others INTO (<br>    PARTITION p_2018 VALUES less than (91466903),<br>    PARTITION p_2019 VALUES LESS THAN MAXVALUE<br>);</pre><h3>SELECT PARTITION</h3><p><strong>Rows of each partition using </strong><strong>information_schema</strong></p><pre>SELECT PARTITION_ORDINAL_POSITION, TABLE_ROWS, PARTITION_METHOD FROM informatio_schema.PARTITIONS WHERE TABLE_SCHEMA = &#39;table&#39; AND TABLE_NAME = &#39;HISTORY&#39;;</pre><h3>Few things to remember</h3><ul><li>Duplicate your database before you try any partitions.</li><li>You have to find your partition method. RANGE partition may not be suitable in your case.</li><li>Run the same query before and after the partition and compare the time it takes.</li></ul><p>Original post <a href="https://www.sminrana.com/mysql/mysql-range-partition/">https://www.sminrana.com/mysql/mysql-range-partition/</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4ef024ce9168" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[App Store Server Notifications V2 with JWT, PHP and Laravel]]></title>
            <link>https://sminrana.medium.com/app-store-server-notifications-v2-with-jwt-php-and-laravel-34b7d2e07012?source=rss-c0bbdd9a58c5------2</link>
            <guid isPermaLink="false">https://medium.com/p/34b7d2e07012</guid>
            <category><![CDATA[jwt]]></category>
            <category><![CDATA[laravel-framework]]></category>
            <category><![CDATA[in-app-purchase]]></category>
            <category><![CDATA[ios]]></category>
            <category><![CDATA[php]]></category>
            <dc:creator><![CDATA[Smin]]></dc:creator>
            <pubDate>Mon, 03 Oct 2022 08:38:26 GMT</pubDate>
            <atom:updated>2022-10-17T08:43:03.604Z</atom:updated>
            <content:encoded><![CDATA[<p>App Store Server Notifications is a server-to-server service that notifies you in real-time when the status of in-app purchases and refunds changes. Use the data in the notifications to update your user-account database, and to monitor and respond to in-app purchase refunds.</p><p>My main goal is to get originalTransactionId from the notification data then when a subscription gets Expired or Refunded, will downgrade the user subscription status.</p><p>FYI when user purchases we verify Apple’s receipt and we save originalTransactionId in our database. So each user have originalTransactionId and we can find the user with this originalTransactionId. You can read more about this here <a href="https://www.sminrana.com/ios/auto-renewable-subscriptions-with-swiftui/">Auto-renewable subscriptions with SwiftUI</a></p><p>Make sure you set your app store notification URL on the <a href="http://appstoreconnect.apple.com/">AppStoreConnect</a>. Choose Version 2 Notifications.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*85ukduFdLeA8XSgt.png" /></figure><p>For this article, our production server URL looks like sminrana.com/jwt.php. You can get whatever Apple sends you and write a log in your server, if you are interested.</p><pre>$appleData = file_get_contents(&#39;php://input&#39;);<br>$file = fopen(<br>    &quot;/var/www/html/appstore_prod.log&quot;, &quot;a&quot;<br>);<br>fwrite($file, $appleData);<br>fclose($file);</pre><p>Each data will look like this, call it signedPayload.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*kCOUiUF2HpGHwFvO.png" /></figure><p>The signedPayload object is a JWS representation. To get the transaction and subscription renewal details from the notification payload, process the signedPayload as follows:</p><ol><li>Parse signedPayload to identify the JWS header, payload, and signature representations.</li><li>Base64 URL-decode the payload to get the <a href="https://developer.apple.com/documentation/appstoreservernotifications/responsebodyv2decodedpayload">responseBodyV2DecodedPayload</a>. The decoded payload contains the <a href="https://developer.apple.com/documentation/appstoreservernotifications/notificationtype">notificationType</a> , <a href="https://developer.apple.com/documentation/appstoreservernotifications/subtype">subtype</a>, other notification metadata, and a <a href="https://developer.apple.com/documentation/appstoreservernotifications/data">data</a> object.</li><li>The data object contains a signedTransactionInfo (<a href="https://developer.apple.com/documentation/appstoreserverapi/jwstransaction">JWSTransaction</a>) and depending on the notification type, a signedRenewalInfo (<a href="https://developer.apple.com/documentation/appstoreservernotifications/jwsrenewalinfo">JWSRenewalInfo</a>). Parse and Base64 URL-decode these signed JWS representations to get transaction and subscription renewal details.</li></ol><p>Each of the signed JWS representations, signedPayload, signedTransactionInfo, and signedRenewalInfo, have a JWS signature that you can validate on your server. Use the algorithm specified in the header’s <a href="https://developer.apple.com/documentation/appstoreservernotifications/alg">alg</a> parameter to validate the signature. For more information about validating signatures, see the JSON Web Signature (JWS) <a href="https://datatracker.ietf.org/doc/html/rfc7515">IETF RFC 7515</a> specification.</p><p>Hopefully, you are already getting this data. Now let’s get originalTransactionId from this. We will do this without any 3rd party library first to understand the process.</p><p>Very first thing, I will download the Apple root certificate and make .PEM file from it.</p><p>Download the certificate <a href="https://www.apple.com/certificateauthority/AppleRootCA-G3.cer">https://www.apple.com/certificateauthority/AppleRootCA-G3.cer</a></p><p>Get .PEM file from it, on your Mac Terminal</p><pre>openssl x509 -in AppleRootCA-G3.cer -out apple_root.pem</pre><p>For testing purposes, I’m loading apple_root.pem and my signedPayload from a file called notification.json (replace it with file_get_contents(‘php://input’);) and then decoding the signedPayload. signedPayload has three parts, separated by .(dot), line 13.</p><p>First part is the header, the Second part is the body (payload) and the Third part is the signature. The header has an algorithm and x5c, x5c has three elements. Certificate, intermediate certificate, and root certificate. We can verify the certificate in two steps. Once the verification is completed we know we have signedPayload from Apple.</p><p>Finally, decode the payload again and get the originalTransactionId from line 56 to 65.</p><h3>Without any 3rd party library(not recommended).</h3><pre>&lt;?php</pre><pre>ini_set(&#39;display_errors&#39;, 1);<br>error_reporting(E_ALL);</pre><pre>// Download the certificate -&gt; https://www.apple.com/certificateauthority/AppleRootCA-G3.cer<br>// Convert it to .PEM file, run on macOS terminal -&gt;  ```bash openssl x509 -in AppleRootCA-G3.cer -out apple_root.pem```</pre><pre>$pem = file_get_contents(&#39;apple_root.pem&#39;);<br>$data = file_get_contents(&#39;notification.json&#39;); // replace with file_get_contents(&#39;php://input&#39;);<br>$json = json_decode($data);</pre><pre>$header_payload_secret = explode(&#39;.&#39;, $json-&gt;signedPayload);</pre><pre>//------------------------------------------<br>// Header<br>//------------------------------------------<br>$header = json_decode(base64_decode($header_payload_secret[0]));<br>$algorithm = $header-&gt;alg;<br>$x5c = $header-&gt;x5c; // array<br>$certificate = $x5c[0];<br>$intermediate_certificate = $x5c[1];<br>$root_certificate = $x5c[2];</pre><pre>$certificate =<br>      &quot;-----BEGIN CERTIFICATE-----\n&quot;<br>    . $certificate<br>    . &quot;\n-----END CERTIFICATE-----&quot;;</pre><pre>$intermediate_certificate =<br>      &quot;-----BEGIN CERTIFICATE-----\n&quot;<br>    . $intermediate_certificate<br>    . &quot;\n-----END CERTIFICATE-----&quot;;</pre><pre>$root_certificate =<br>      &quot;-----BEGIN CERTIFICATE-----\n&quot;<br>    . $root_certificate<br>    . &quot;\n-----END CERTIFICATE-----&quot;;</pre><pre>//------------------------------------------<br>// Verify the notification request   <br>//------------------------------------------<br>if (openssl_x509_verify($intermediate_certificate, $root_certificate) != 1){ <br>    echo &#39;Intermediate and Root certificate do not match&#39;;<br>    exit;<br>}</pre><pre>// Verify again with Apple root certificate<br>if (openssl_x509_verify($root_certificate, $pem) == 1){<br>    //------------------------------------------<br>    // Payload<br>    //------------------------------------------<br>    // https://developer.apple.com/documentation/appstoreservernotifications/notificationtype<br>    // https://developer.apple.com/documentation/appstoreservernotifications/subtype<br>    $payload = json_decode(base64_decode($header_payload_secret[1]));<br>    $notificationType = $payload-&gt;notificationType;<br>    $subtype = $payload-&gt;subtype;</pre><pre>    if ($notificationType == &quot;EXPIRED&quot; || $notificationType == &quot;REFUND&quot;) {<br>        $transactionInfo = $payload-&gt;data-&gt;signedTransactionInfo;<br>        $ti = explode(&#39;.&#39;, $transactionInfo);<br>        <br>        $data = json_decode(base64_decode($ti[1]));<br>        var_dump($data); // this will contain our originalTransactionId<br>    }<br>} else {<br>    echo &#39;Header is not valid&#39;;<br>    exit;<br>}</pre><h3>Using firebase/php-jwt in composer (recommended), big difference is to use the public key to decode the payload using JWT.</h3><pre>composer require firebase/php-jwt</pre><pre>&lt;?php</pre><pre>ini_set(&#39;display_errors&#39;, 1);<br>error_reporting(E_ALL);</pre><pre>// No need these 3 lines for Laravel<br>require_once &#39;./vendor/firebase/php-jwt/src/JWT.php&#39;;<br>require_once &#39;./vendor/firebase/php-jwt/src/JWK.php&#39;;<br>require_once &#39;./vendor/firebase/php-jwt/src/Key.php&#39;;</pre><pre>use Firebase\JWT\JWT;<br>use Firebase\JWT\Key;</pre><pre>// Download the certificate -&gt; https://www.apple.com/certificateauthority/AppleRootCA-G3.cer<br>// Convert it to .PEM file, run on macOS terminal -&gt;  ```bash openssl x509 -in AppleRootCA-G3.cer -out apple_root.pem```</pre><pre>$pem = file_get_contents(&#39;apple_root.pem&#39;);</pre><pre>$data = file_get_contents(&#39;notification.json&#39;);  // replace with file_get_contents(&#39;php://input&#39;);<br>$json = json_decode($data);</pre><pre>$header_payload_secret = explode(&#39;.&#39;, $json-&gt;signedPayload);</pre><pre>//------------------------------------------<br>// Header<br>//------------------------------------------<br>$header = json_decode(base64_decode($header_payload_secret[0]));<br>$algorithm = $header-&gt;alg;<br>$x5c = $header-&gt;x5c; // array<br>$certificate = $x5c[0];<br>$intermediate_certificate = $x5c[1];<br>$root_certificate = $x5c[2];</pre><pre>$certificate =<br>      &quot;-----BEGIN CERTIFICATE-----\n&quot;<br>    . $certificate<br>    . &quot;\n-----END CERTIFICATE-----&quot;;</pre><pre>$intermediate_certificate =<br>      &quot;-----BEGIN CERTIFICATE-----\n&quot;<br>    . $intermediate_certificate<br>    . &quot;\n-----END CERTIFICATE-----&quot;;</pre><pre>$root_certificate =<br>      &quot;-----BEGIN CERTIFICATE-----\n&quot;<br>    . $root_certificate<br>    . &quot;\n-----END CERTIFICATE-----&quot;;</pre><pre>//------------------------------------------<br>// Verify the notification request   <br>//------------------------------------------</pre><pre>if (openssl_x509_verify($intermediate_certificate, $root_certificate) != 1){ <br>    echo &#39;Intermediate and Root certificate do not match&#39;;<br>    exit;<br>}</pre><pre>// Verify again with Apple root certificate<br>if (openssl_x509_verify($root_certificate, $pem) == 1){<br>    $cert_object = openssl_x509_read($certificate);<br>    $pkey_object = openssl_pkey_get_public($cert_object);<br>    $pkey_array = openssl_pkey_get_details($pkey_object);<br>    $publicKey = $pkey_array[&#39;key&#39;];</pre><pre>    //------------------------------------------<br>    // Payload<br>    //------------------------------------------<br>    $payload = json_decode(base64_decode($header_payload_secret[1]));<br>    $notificationType = $payload-&gt;notificationType;</pre><pre>    //if ($notificationType == &quot;EXPIRED&quot; || $notificationType == &quot;REFUND&quot;) {<br>        $transactionInfo = $payload-&gt;data-&gt;signedTransactionInfo;<br>        $signedRenewalInfo = $payload-&gt;data-&gt;signedRenewalInfo;</pre><pre>        $transactionDecodedData = JWT::decode($transactionInfo, new Key($publicKey, $algorithm));<br>        var_dump($transactionDecodedData-&gt;originalTransactionId);<br>        echo &quot;========================================&quot;;<br>        $signedRenewalDecodedData = JWT::decode($signedRenewalInfo, new Key($publicKey, $algorithm));<br>        var_dump($signedRenewalDecodedData);<br>    //}</pre><pre>} else {<br>    echo &#39;Header is not valid&#39;;<br>    exit;<br>}</pre><p><a href="https://github.com/sminrana/app-store-notification-v2-php">Download the source code</a></p><p>Original post <a href="https://www.sminrana.com/ios/app-store-server-notifications-v2-with-php-and-laravel/">https://www.sminrana.com/ios/app-store-server-notifications-v2-with-php-and-laravel/</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=34b7d2e07012" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>