<?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 Yevgeniy Samoilenko on Medium]]></title>
        <description><![CDATA[Stories by Yevgeniy Samoilenko on Medium]]></description>
        <link>https://medium.com/@yevgeniy.sam?source=rss-cd22af8da646------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*VbtjhIqz2410uI67gUcWSg.jpeg</url>
            <title>Stories by Yevgeniy Samoilenko on Medium</title>
            <link>https://medium.com/@yevgeniy.sam?source=rss-cd22af8da646------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 17:06:50 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@yevgeniy.sam/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[A Step-by-Step Guide to Populating a Table with Data from a REST API Using Oracle SQL and PL/SQL]]></title>
            <link>https://medium.com/codex/a-step-by-step-guide-to-populating-a-table-with-data-from-a-rest-api-using-oracle-sql-and-pl-sql-e99a3f167ee?source=rss-cd22af8da646------2</link>
            <guid isPermaLink="false">https://medium.com/p/e99a3f167ee</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[how-to]]></category>
            <category><![CDATA[oracle]]></category>
            <category><![CDATA[rest-api]]></category>
            <dc:creator><![CDATA[Yevgeniy Samoilenko]]></dc:creator>
            <pubDate>Thu, 23 Feb 2023 16:56:38 GMT</pubDate>
            <atom:updated>2023-02-23T16:56:38.949Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/827/1*ZePmvhpzLll7x-MPIccK6Q.png" /></figure><p>Populating a database table with data from a REST API has become a critical task for businesses that rely on real-time information. Whether it’s financial data, social media insights, or weather updates, having access to accurate and up-to-date information can give organizations a competitive edge. In this article, we will explore the process of easily populating a table with data from a REST API using Oracle SQL and PL/SQL. From setting up the database to writing the code, we will provide step-by-step instructions and examples to help you get started. Whether you are a seasoned database professional or just starting out, this guide will demonstrate how to harness the power of REST APIs and unlock the value of your data.</p><p>Here’s an example of how to populate a table with data from the public “<a href="https://api.exchangerate.host/latest">https://api.exchangerate.host/latest</a>&quot; REST API using Oracle SQL and PL/SQL. This particular API provides an exchange rate.</p><p>The steps will be:</p><ol><li>Create a table into which we will load the received data</li><li>Configure Oracle to work with network resources</li><li>Create a procedure that will take data through the REST API and add the received data to a table</li><li>Create a job to execute the procedure on schedule</li></ol><h4>Step 1. Create a table into which we will load the received data</h4><p>Let’s create a table to store the data received from the REST API. Here’s an example of a table definition in Oracle SQL:</p><pre>CREATE TABLE exchange_rates (<br>  currency_code VARCHAR2(3) PRIMARY KEY,<br>  rate VARCHAR(16)<br>);</pre><p>In this example, we are creating a table named “exchange_rates” with two columns: “currency_code” and “rate”. The currency_code column is defined as a primary key and is a variable-length string with a maximum length of 3 characters. The rate column is defined as a varchar as well.</p><h4>Step 2. Configure Oracle to work with network resources</h4><p>Now, before we will write a code to access REST API we should prepare our database and give to user some additional rights to communicate through the network.</p><p>Create an ACL: we create an ACL with a initial user SCOTT, and the privilege is resolve.</p><pre>BEGIN<br>  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (<br>  acl =&gt; &#39;Connect_Access.xml&#39;,<br>  description =&gt; &#39;Connect Network&#39;,<br>  principal =&gt; &#39;SCOTT&#39;,<br>  is_grant =&gt; TRUE,<br>  privilege =&gt; &#39;resolve&#39;,<br>  start_date =&gt; NULL,<br>  end_date =&gt; NULL);<br>END;<br>/</pre><p>Then we have to assign the ACL to a specific network: We open the widest scope ‘*’ to users.</p><pre>BEGIN<br>  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl =&gt; &#39;Connect_Access.xml&#39;,<br>  host =&gt; &#39;*&#39;,<br>  lower_port =&gt; NULL,<br>  upper_port =&gt; NULL);<br>END;<br>/</pre><p>Checking that we have got rights to connect.</p><pre>select UTL_INADDR.get_host_name() from dual;</pre><p>We have to get something like that:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/386/1*ZCXujuCM2qQ1DI75A9r84A.png" /></figure><p>In order to make connections to a secured resource, we need to get the necessary certificate. The easiest way to do this is using a browser. The example below uses the Firefox browser.</p><p>Using the browser, go to the URL you are attempting to access from PL/SQL. In this case “<a href="https://api.exchangerate.host/latest">https://api.exchangerate.host/latest</a>&quot;. Click the lock icon in the URL bar to display the certificate menu and click on the “Secure Connection” tab.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/529/1*SlQJlWtAEwlv6rI1XN8TUg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/701/1*3RMq_59y-32NCh1u0dJpmw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/705/1*T3GnRk5GJvdoTHQQ0pGa8Q.png" /></figure><p>Click the “More information” link and click the “View Certificate” button on the resulting dialog.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/786/1*d4dEoO8kkZiDlcmcg7ULVA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/722/1*nmlQtu8bx0dhTCXluL8u3g.png" /></figure><p>And click the “Export” button to save the certificate information.</p><p>Select the “X.509 (.PEM)” option and click the “Save” button. Other formats work, but I’ve found this to be the most consistent.</p><p>Create a new location to hold the wallet.</p><pre>$ mkdir -p /u01/app/oracle/admin/DB19G/wallet</pre><p>Create a new wallet.</p><pre>$ orapki wallet create -wallet /u01/app/oracle/admin/DB19G/wallet -pwd OraPass098 -auto_login</pre><p>With the wallet created, we can add the certificate we saved earlier.</p><pre>orapki wallet add -wallet /u01/app/oracle/admin/DB19G/wallet -trusted_cert -cert &quot;/u01/userhome/oracle/Downloads/BaltimoreCyberTrustRoot.crt&quot; -pwd OraPass098</pre><p>We are now ready to access the secured resource, but we must provide the UTL_HTTP package with the wallet details so it can make the secured connections. This is done using the UTL_HTTP.SET_WALLET procedure. Repeating the previous test now works successfully.</p><pre>SET SERVEROUTPUT ON<br>EXEC UTL_HTTP.set_wallet(&#39;file:/u01/app/oracle/admin/DB19G/wallet&#39;, NULL);<br>EXEC show_html_from_url(https://medium.com/&#39;https://api.exchangerate.host/latest&#39;);<br><br>... HTML output removed ...<br><br>PL/SQL procedure successfully completed.<br><br>SQL&gt;</pre><h4>Step 3. Create a procedure that will take data through the REST API and add the received data to a table</h4><p>Next, let’s write a PL/SQL block to retrieve data from the REST API and populate the table. Here’s an example using the UTL_HTTP package in PL/SQL:</p><pre>CREATE OR REPLACE PROCEDURE GET_EXCCHANGE_RATE AS <br>  l_url         VARCHAR2(256) := &#39;https://api.exchangerate.host/latest&#39;;<br>  l_wallet_path VARCHAR2(256) := &#39;file:/u01/app/oracle/admin/DB19G/wallet&#39;;<br>  l_http_req    UTL_HTTP.req;<br>  l_http_resp   UTL_HTTP.resp;<br>  l_response    CLOB;<br>  l_json        JSON_OBJECT_T;<br>  l_json2       JSON_OBJECT_T;<br>  l_key_list2   JSON_KEY_LIST; <br>  val_curr      VARCHAR2(3);<br>  val_rate      VARCHAR2(18);<br>BEGIN<br>  UTL_HTTP.set_wallet(l_wallet_path, NULL);<br>  l_http_req := UTL_HTTP.begin_request(l_url, &#39;GET&#39;);<br>  l_http_resp := UTL_HTTP.get_response(l_http_req);<br><br>  UTL_HTTP.read_text(l_http_resp, l_response);<br><br>  l_json := JSON_OBJECT_T.PARSE(l_response);<br>  l_json2 := l_json.get_object(&#39;rates&#39;); <br>  l_key_list2 := l_json2.get_keys; <br><br>  FOR counter IN 1 .. l_json2.get_size<br>    LOOP <br>      val_curr := l_key_list2 (counter);<br>      val_rate := l_json2.get_string(l_key_list2 (counter));<br>     <br>      INSERT INTO exchange_rates (currency_code, rate)<br>      VALUES (val_curr, val_rate);<br>   END LOOP; <br>  <br>  COMMIT;<br><br>END GET_EXCCHANGE_RATE;<br>/</pre><p>The UTL_HTTP.read_text function is then used to read the response into a PL/SQL CLOB variable. The JSON_OBJECT_T type is used to parse the response as a JSON object.</p><p>In the PL/SQL loop, we are using the INSERT INTO statement to insert the data into the “exchange_rates” table. The l_json.get_keys and l_json.get_string functions are used to retrieve the currency code and rate values from the JSON object, respectively.</p><p>Finally, we are committing the changes to the database using the COMMIT statement.</p><p>Now we can execute this procedure.</p><pre>BEGIN<br>  GET_EXCCHANGE_RATE();<br>END;<br>/</pre><p>Let’s check that table was populated with the data from the REST API.</p><pre>SELECT<br>    currency_code,<br>    to_number(rate)<br>FROM<br>    exchange_rates;</pre><p>Here is the result:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/344/1*fk0Jf4uRFcGjIh_-7AQrEg.png" /></figure><p>And that’s it! You have successfully populated a table with data from the “<a href="https://api.exchangerate.host/latest">https://api.exchangerate.host/latest</a>&quot; REST API using Oracle SQL and PL/SQL. Of course, you may need to modify the code to fit your specific use case, but this should give you a good starting point for</p><h4>Step 4. Create a job to execute the procedure on schedule</h4><p>Also you can make a job which would execute this procedure every night.</p><pre>BEGIN<br>  DBMS_SCHEDULER.CREATE_JOB (<br>    job_name           =&gt; &#39;my_exchange_rate_job&#39;,<br>    job_type           =&gt; &#39;PLSQL_BLOCK&#39;,<br>    job_action         =&gt; &#39;BEGIN TRUNCATE TABLE exchange_rates; GET_EXCHANGE_RATE; END;&#39;,<br>    start_date         =&gt; SYSTIMESTAMP AT TIME ZONE &#39;UTC&#39;,<br>    repeat_interval    =&gt; &#39;FREQ=DAILY;BYHOUR=0;BYMINUTE=0;BYSECOND=0&#39;,<br>    enabled            =&gt; TRUE,<br>    comments           =&gt; &#39;Job to truncate exchange_rates table and refresh exchange rates every night at midnight.&#39;<br>  );<br>END;<br>/</pre><h3>Other use cases</h3><p>This approach can be used to fill in data from REST APIs in a variety of scenarios, including but not limited to:</p><ol><li>Financial data: Populate a table with financial data such as stock prices, exchange rates, or economic indicators.</li><li>Social media data: Populate a table with social media data such as user information, posts, or comments.</li><li>eCommerce data: Populate a table with eCommerce data such as product information, prices, and availability.</li><li>Weather data: Populate a table with weather data such as temperature, humidity, and wind speed.</li><li>Sports data: Populate a table with sports data such as scores, statistics, and rankings.</li><li>Geographical data: Populate a table with geographical data such as population, area, or latitude and longitude.</li><li>News data: Populate a table with news data such as headlines, summaries, and authors.</li><li>Health data: Populate a table with health data such as disease outbreaks, treatment options, or health outcomes.</li></ol><p>In each of these scenarios, the data received from the REST API can be used to populate a database table for further analysis, reporting, or integration with other systems. This approach can also be used to automate data collection and updates, allowing you to stay up-to-date with the latest information from the REST API.</p><p>Please feel free to share your thoughts on the above in the comments, and don’t hesitate to connect with me on <a href="https://www.linkedin.com/in/yevgeniysamoilenko/">LinkedIn</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e99a3f167ee" width="1" height="1" alt=""><hr><p><a href="https://medium.com/codex/a-step-by-step-guide-to-populating-a-table-with-data-from-a-rest-api-using-oracle-sql-and-pl-sql-e99a3f167ee">A Step-by-Step Guide to Populating a Table with Data from a REST API Using Oracle SQL and PL/SQL</a> was originally published in <a href="https://medium.com/codex">CodeX</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Fears and problems when choosing a low-code solution]]></title>
            <link>https://medium.com/codex/fears-and-problems-when-choosing-a-low-code-solution-ad218eec8b64?source=rss-cd22af8da646------2</link>
            <guid isPermaLink="false">https://medium.com/p/ad218eec8b64</guid>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[low-code]]></category>
            <category><![CDATA[low-code-development]]></category>
            <category><![CDATA[opinion]]></category>
            <dc:creator><![CDATA[Yevgeniy Samoilenko]]></dc:creator>
            <pubDate>Sat, 02 Apr 2022 15:25:44 GMT</pubDate>
            <atom:updated>2022-04-02T15:25:44.344Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-vhYOdCM0Ia_4Qh4jquLGw.png" /></figure><p>If you thinking about using a low-code solution, probably you asked some of these questions already.</p><ul><li>Is it worth being afraid of low-code solutions and problems with their scaling?</li><li>Can low-code solutions pump money out of a business for development?</li><li>Does low-code cost programmers and how to minimize costs?</li><li>Does low-code bind to its creator and how to achieve independence?</li></ul><p>I’d like to present to you my opinion, confirmed by more than 20 years of successful work in IT companies, fintech companies, and IT team, as well as experience in using low-code platforms in the fintech company.</p><p>Choosing a low-code platform, many companies face some concerns related to switching to it. This is both the fear to become dependent on the vendor, and the fear to acquire the wrong solution: wasted money without the expected result. Also, developers often worry that they will cease to be in demand, and will lose their jobs.</p><p>An objective and logical approach will help to get rid of fears of choosing the wrong platform. This requires small but thorough work.</p><p>First, you need to decide on the selection criteria. What exactly will the platform be used for? For backend development, API, websites, mobile applications, routine automation, ETL\ELT, or for something else? This will narrow down the range of potential platforms and vendors.</p><blockquote>Don’t choose a platform that you will grow into that has a large set of functions that may come in handy only sometime in the future. Start with a highly specialized solution.</blockquote><p>Then create criteria that are important for your company. What will be the weight of each criterion, metric that you will use to evaluate platforms? When studying platforms, involve a fair number of employees who will use it for development in the future.</p><blockquote>Attract those who will use the development results, that is, applications created on this platform.</blockquote><p>It’s best to work with each platform independently — you shouldn’t rely on advertising slogans, popularity, or attractive prices. Use a free trial period that almost all vendors provide to figure out if a platform meets your needs. Only with this approach, the choice will be objective, and fears will dissipate.</p><p>Now I will answer the questions highlighted at the beginning of this article.</p><h4><strong>Is it worth being afraid of low-code solutions and problems with their scaling?</strong></h4><p>No, there is nothing to be afraid of. The right platform will allow you to scale your solutions flexibly enough. For example, in our company we were choosing the platform according to the following criteria: it should work equally efficiently on any servers — cloud (AWS, GC, Azure) and on-premises, scale horizontally, and have balancing tools. Check the availability of scaling. Indeed, it’s not available on all platforms. Carefully consider how the platform works: in what language it is developed, what technologies it uses, what restrictions are stated (several technologies have their limitations and special requirements), and other features. Some platforms provide the source code of the resulting solution — this is a major asset.</p><h4><strong>Can low-code solutions pump money out of a business for development?</strong></h4><p>Such outcomes are possible. Therefore, it is important to carefully approach the choice of the platform. Pay attention to the following points: licensing and billing model, how rich the platform is in functionality that you need. Also, the interface should be user-friendly enough to do without consultations from the platform vendors. Study carefully the licensing policy and try to calculate the potential costs for 3–5 years, taking your growth into account.</p><h4><strong>Does low-code cost programmers and how to minimize costs?</strong></h4><p>Again, it strongly depends on the characteristics of the platform. There are expensive products that cost hundreds of thousands of dollars per year and that provide very extensive features, functionality, outstanding performance, and flexibility. Still, some platforms cost one thousand dollars per year and have a narrow specialization, while they quite adequately perform specific tasks. Let me use our company as an example once again. We figured out the following criteria when choosing a platform — Total Cost of Ownership (including the cost of licenses, the cost of people who maintain it, etc.) must be approximately 10% of the classic development. And we managed to achieve this.</p><h4><strong>Does low-code bind to its creator and how to achieve independence?</strong></h4><p>Again, this depends on the platform. For example, we use a platform that generates source code that can be downloaded and used at your discretion. What’s more, the code is decent, it’s written in GO. So if at some point we have decided to break off relations with this platform provider, we could have continued to exploit the received code. If necessary, we could have refined it on our own.</p><h3><strong>Conclusion</strong></h3><p>Do not be afraid to switch to low-code platforms — it is worth making a deliberate choice. Introduce the platform into your IT infrastructure gradually. Prepare yourself and the team for the fact that low-code platforms have some limitations. Most often they are not critical. Choose the platform based on the criteria that are important to you and it’s going to be fine!</p><p>I’d love to hear your thoughts on the above in the comments and feel free to connect with me on <a href="https://www.linkedin.com/in/yevgeniysamoilenko/">LinkedIn</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ad218eec8b64" width="1" height="1" alt=""><hr><p><a href="https://medium.com/codex/fears-and-problems-when-choosing-a-low-code-solution-ad218eec8b64">Fears and problems when choosing a low-code solution</a> was originally published in <a href="https://medium.com/codex">CodeX</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Oracle: How to Mask Sensitive Data with the Views — Quickly and Free]]></title>
            <link>https://medium.com/codex/oracle-how-to-mask-sensitive-data-with-the-views-quickly-and-free-aff9175146b0?source=rss-cd22af8da646------2</link>
            <guid isPermaLink="false">https://medium.com/p/aff9175146b0</guid>
            <category><![CDATA[oracle]]></category>
            <category><![CDATA[how-to]]></category>
            <category><![CDATA[development]]></category>
            <dc:creator><![CDATA[Yevgeniy Samoilenko]]></dc:creator>
            <pubDate>Mon, 14 Feb 2022 15:32:41 GMT</pubDate>
            <atom:updated>2022-02-14T15:32:41.122Z</atom:updated>
            <content:encoded><![CDATA[<h3>Oracle: How to Mask Sensitive Data with the Views — Quickly and Free</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qlLUYGyqltwd_gPfN5KB4g.jpeg" /></figure><p>Sometimes you can suddenly identify data that must be urgently hidden from everybody in the system that is up and running with a variety of components, services, and processes.</p><p>For example, you have found out that the system has clients’ e-mails accessible in one or multiple applications, including mobile, desktop, web applications, and reporting services. Now, the development team faces the task: to urgently disable access to viewing these data. You are aware that the applications accessing these data make up an unknown set. And some applications/services work with the table directly, while others interact with it through procedures/batches.</p><p>How to solve the task quickly without breaking anything? Of course, there are great Oracle products (Data Redaction, Virtual Private Database) that solve this task in a comprehensive reliable manner and offer a variety of other benefits. However these are paid options, and you need specific skills to implement them. What I suggest is to take the simple way of solving the task fast and free of charge.</p><p>The gist of the solution is to create the View with the same name as that of the table, whose data we need to protect. At the same time, we will rename the source table so that no one could refer to it anymore.</p><p><strong>The difficulty many people trip over</strong> is that, even though the View solves the data sampling task easily enough, as soon as we try to insert or modify data through the View, we will get the <em>ORA-01733: virtual column not allowed here error</em>. It means that there are fields we modify in the view. This issue is easy to bypass with triggers. I will use the following example to show how to do it.</p><p>For this experiment, let’s use the free Oracle Autonomous Database available for everybody. It’s convenient for experiments.</p><p>Data Masking Steps:</p><ol><li>Create a test table and fill it with data</li><li>Create the view with data masking</li><li>Enable the data insert and update through the View</li><li>Rename the table and the view</li></ol><h3>Step 1. Create the table and fill it with data</h3><p>Create a simple table named Cards that consists of two columns — the identifier and the varchar column that stores the PAN of the credit card.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/93bb436acad65bac91ce2e1a2798385d/href">https://medium.com/media/93bb436acad65bac91ce2e1a2798385d/href</a></iframe><p>Insert several rows:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/763028cfe7d2d64ab92141e48ae74fb5/href">https://medium.com/media/763028cfe7d2d64ab92141e48ae74fb5/href</a></iframe><p>Check, whether the data are accessible:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/76b22174662c43341d3c66887b1e0104/href">https://medium.com/media/76b22174662c43341d3c66887b1e0104/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*c8qRyR6Up40aPaPZ" /></figure><p>Good, we can see exactly what we just inserted.</p><h3>Step 2. Create the view with data masking</h3><p>Now let’s create the view that queries our table with data masking using the REGEXP_REPLACE regular expression function:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/b8ec59df16677e5c7f704c95904a5798/href">https://medium.com/media/b8ec59df16677e5c7f704c95904a5798/href</a></iframe><p>Check that the returned output is what we expect:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/39b3e95b10e95ffc76e345dadb9e5739/href">https://medium.com/media/39b3e95b10e95ffc76e345dadb9e5739/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/948/0*m2FrfUBp1Dm0ssBZ" /></figure><p>Now, this is what we need — the data are masked.</p><h3>Step 3. Enable the data insert and update through the View</h3><p>So far the View doesn’t allow to insert of new data. Have a look:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/e0c991e778b2f15fb92fc834d12e6244/href">https://medium.com/media/e0c991e778b2f15fb92fc834d12e6244/href</a></iframe><p>We get the error <em>ORA-01733: virtual column not allowed here</em></p><p>That’s because the field is changed when we create the View.</p><p>We can make sure of it by checking USER_UPDATABLE_COLUMNS:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/176605b35b1652237b25602f3ad4bcb0/href">https://medium.com/media/176605b35b1652237b25602f3ad4bcb0/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*4-qXoSIPUZfe4c11" /></figure><p>It’s not actually a problem since we can bypass this restriction with the simple trigger mechanism.</p><p>Let’s create the trigger that will be executed when attempting to make an insert in the View. In the trigger itself we will make actions directly with the source table:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/232f9989ddc4a84e2ac1278815b19d4b/href">https://medium.com/media/232f9989ddc4a84e2ac1278815b19d4b/href</a></iframe><p>Now let’s try to insert the value in our View:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/e0c991e778b2f15fb92fc834d12e6244/href">https://medium.com/media/e0c991e778b2f15fb92fc834d12e6244/href</a></iframe><p>There is no error. Let’s check if the row has been added.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/39b3e95b10e95ffc76e345dadb9e5739/href">https://medium.com/media/39b3e95b10e95ffc76e345dadb9e5739/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/900/0*ve1fY2l0stXAPUpf" /></figure><p>Here it is, with card_id = 6</p><p>Now let’s check that the source table also has the data and that they are not masked:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/76b22174662c43341d3c66887b1e0104/href">https://medium.com/media/76b22174662c43341d3c66887b1e0104/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/814/0*M1Unjw0A3INSlb8Y" /></figure><p>Great!</p><p>Let’s repeat this magic with data update through the View.</p><p>Without creating the trigger Оracle will threaten us with the same error:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/37b0e5659605dab3105c441a0f5100b4/href">https://medium.com/media/37b0e5659605dab3105c441a0f5100b4/href</a></iframe><p>Yes, here it is:</p><p><em>ORA-01733: virtual column not allowed here</em></p><p>But we already know what to do and just create the trigger:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/4b2fc157a2f669b6ca49330cf934a142/href">https://medium.com/media/4b2fc157a2f669b6ca49330cf934a142/href</a></iframe><p>Let’s try to update the data once again:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/37b0e5659605dab3105c441a0f5100b4/href">https://medium.com/media/37b0e5659605dab3105c441a0f5100b4/href</a></iframe><p>No errors. Let’s check if the data were updated:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/bf8eac7848f31dea76dfa7ac4cefefbf/href">https://medium.com/media/bf8eac7848f31dea76dfa7ac4cefefbf/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/782/0*MG_bN4sRmK3GkkuS" /></figure><p>Now we have made sure that we can both read from the View and get the masked data, and insert and update data through the View.</p><h3>Step 4. Rename the table and the View</h3><p>Now what we have to do is to rename the View so that its name would be the same as that of the source table, and to give any other name to the table. We will also have to recreate the triggers, and in the end, recompile the invalidated objects. As we rename the table, all references (foreign keys) to it will retain.</p><p>Drop the triggers, and then create them again with the right references to the table:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/52560688a87eb011490702aa9bf88412/href">https://medium.com/media/52560688a87eb011490702aa9bf88412/href</a></iframe><p>Change the table name from cards to cards_tbl:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/129ba8e5993efa0fb1de06eaa781980a/href">https://medium.com/media/129ba8e5993efa0fb1de06eaa781980a/href</a></iframe><p>The objects that worked with this table have become invalid. We need to recreate the View so that all objects would become valid.</p><p>Change the View name from cards_v to cards:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/3598511d24355669ead1e77e381a321b/href">https://medium.com/media/3598511d24355669ead1e77e381a321b/href</a></iframe><p>Recreate the triggers:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/bb7819597eb699348eaebb3b095e9650/href">https://medium.com/media/bb7819597eb699348eaebb3b095e9650/href</a></iframe><p>Now we can recompile the objects that became invalid:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/856902223aafe0775cab5b0886267518/href">https://medium.com/media/856902223aafe0775cab5b0886267518/href</a></iframe><p>Let’s check if everything is up and running:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/76b22174662c43341d3c66887b1e0104/href">https://medium.com/media/76b22174662c43341d3c66887b1e0104/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/850/0*sLKPGIuR85RuaIu8" /></figure><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/69c13110c8b20fbf0536c5033ac0c7d3/href">https://medium.com/media/69c13110c8b20fbf0536c5033ac0c7d3/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/816/0*g2yaWQ-Z3AmL0XRF" /></figure><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/1087048aa6b9d60d3f88b837db537b32/href">https://medium.com/media/1087048aa6b9d60d3f88b837db537b32/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/820/0*xiZf9bul_JnwAspO" /></figure><p>The code runs as expected.</p><h3>Conclusion</h3><p>In this article, we have handled how to replace the table with the view adding data masking. We have also learned how to bypass Oracle’s restriction of data modification through the View. This trick is helpful when we need to mask sensitive data without major fixes.</p><p>I’d love to hear your thoughts on the above in the comments and feel free to connect with me on <a href="https://www.linkedin.com/in/yevgeniysamoilenko/">LinkedIn</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=aff9175146b0" width="1" height="1" alt=""><hr><p><a href="https://medium.com/codex/oracle-how-to-mask-sensitive-data-with-the-views-quickly-and-free-aff9175146b0">Oracle: How to Mask Sensitive Data with the Views — Quickly and Free</a> was originally published in <a href="https://medium.com/codex">CodeX</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Extending the SQL and PL/SQL with custom external functions and procedures]]></title>
            <link>https://medium.com/codex/extending-the-sql-and-pl-sql-with-custom-external-functions-and-procedures-214067761061?source=rss-cd22af8da646------2</link>
            <guid isPermaLink="false">https://medium.com/p/214067761061</guid>
            <category><![CDATA[oracle]]></category>
            <category><![CDATA[development]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Yevgeniy Samoilenko]]></dc:creator>
            <pubDate>Mon, 31 Jan 2022 14:47:17 GMT</pubDate>
            <atom:updated>2022-01-31T14:47:17.160Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*t03BYJeezIAEIEOjU9XazQ.png" /></figure><p>Many DBMSs have distinctive features. For example, one can extend with custom functions that can be used both in procedures and directly in SQL queries.</p><p>When do we need custom functions? For instance, when we need to implement non-standard calculations, encoding, encryption, conversion. Such a function might also be helpful to interact with external systems.</p><p>It will look like this:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/b984e5ae59270830420b88374ad9b6c8/href">https://medium.com/media/b984e5ae59270830420b88374ad9b6c8/href</a></iframe><p>I have had to repeatedly extend in this way in different DBMSs. Today I will tell and show you how to do it in Oracle. It proved to be working in 11g to 19c Oracle versions.</p><blockquote>I’d like to note that it is not a hidden function. It is legal and developed by Oracle. Moreover, Oracle itself recommends applying it in some cases.</blockquote><h3>How to extend SQL and PL/SQL with custom functions</h3><p>The whole process is quite simple, you only need to follow four steps:</p><ol><li>Write and compile a library that implements this function.</li><li>Set up Oracle so it can detect the library.</li><li>Create a Library object in Oracle that will refer to our library.</li><li>Create a procedure \ function wrapper in Oracle that you will use to access the written library in SQL queries.</li></ol><p>For experiments, I usually use ready-made instances of virtual machines, which already have a configured Linux, an Oracle database, and everything necessary for work. Download the image <a href="https://www.oracle.com/downloads/developer-vm/community-downloads.html">here</a>, import it into VirtualBox (download <a href="https://www.virtualbox.org/">here</a>) and after 5 minutes we get a ready-to-use virtual machine with Oracle 19c.</p><p>First, let’s go to our virtual machine.</p><h3>Step 1: Writing the Library</h3><p>This is probably the most difficult part of all that we must do. But if you follow my instructions, everything will work out.</p><p>The library for this example is written in C for the Linux platform, as we have a virtual machine with Linux on board.</p><blockquote>Note: Oracle allows you to include libraries written in C, C++, COBOL, Java, .NET, etc.</blockquote><p>The terminal is already open for us, let’s start.</p><p>We check that we are in the /home/oracle directory:</p><pre>[oracle@localhost ~]$ pwd</pre><pre>/home/oracle</pre><p>Create a directory where we will place our library. Let’s call it, for example, mask. Then open it:</p><pre>[oracle@localhost ~]$ mkdir mask</pre><pre>[oracle@localhost ~]$ cd mask</pre><pre>[oracle@localhost mask]$</pre><p>Create a file with the extension “c” — this is a file with source code in C. Let’s call it just as simple — mask.c:</p><pre>[oracle@localhost mask]$ nano mask.c</pre><p>The editor will open. Then you can type your text or paste mine. My code might not be elegant, but is an example of how to implement two functions and one procedure:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/ad17f129f8c21b2c7502c6de135aacbe/href">https://medium.com/media/ad17f129f8c21b2c7502c6de135aacbe/href</a></iframe><p>The <strong>echo</strong> function simply returns the string that had been passed to it. We will use it to check that the function works before we include it in Oracle.</p><p>The <strong>mask</strong> function takes a string and returns it masked, replacing the characters from 7th to the last one with the character “X”.</p><p>The <strong>logit</strong> procedure will write the passed string to the log.txt file. Yes, you shouldn’t do this. We do it for the sake of demonstration.</p><p>After inserting, close the editor (press Ctrl-X, then Y, Enter) and agree to save it under this name (press Enter).</p><p>Create another mask.h file:</p><pre>[oracle@localhost mask]$ nano mask.h</pre><p>The same as before, type your own text or copy the text below and save:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/26145ea7fedca85bcb1b750f9525895d/href">https://medium.com/media/26145ea7fedca85bcb1b750f9525895d/href</a></iframe><p>Make sure both files have been created:</p><pre>[oracle@localhost mask]$ ls -l</pre><pre>total 8</pre><pre>-rw-r — r — . 1 oracle oinstall 456 Jan 20 09:46 mask.c</pre><pre>-rw-r — r — . 1 oracle oinstall 141 Jan 20 09:57 mask.h</pre><pre>[oracle@localhost mask]$</pre><p>Compile the source files into a dynamic library:</p><pre>[oracle@localhost mask]$ gcc -shared -o libmask.so -fPIC mask.c</pre><pre>[oracle@localhost mask]$</pre><p>No errors, great! Make sure the library has been created. It should be named <strong>libmask.so</strong> :</p><pre>[oracle@localhost mask]$ ls -l</pre><pre>total 20</pre><pre>-rwxr-xr-x. 1 oracle oinstall 8416 Jan 20 10:02 <strong>libmask.so</strong></pre><pre>-rw-r — r — . 1 oracle oinstall 458 Jan 20 10:02 mask.c</pre><pre>-rw-r — r — . 1 oracle oinstall 143 Jan 20 10:02 mask.h</pre><pre>[oracle@localhost mask]$</pre><p>Now we need to check that the library we wrote and compiled operates. To do this, we will write a small program that will call our library:</p><pre>[oracle@localhost mask]$ nano testlib.c</pre><p>Paste this code:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/ac51f0e531dc892bf502a9f8e79d5cfb/href">https://medium.com/media/ac51f0e531dc892bf502a9f8e79d5cfb/href</a></iframe><p>Close, save, compile:</p><pre>[oracle@localhost mask]$ gcc -Wall -L/home/oracle/mask -o testlib testlib.c -lmask</pre><pre>[oracle@localhost mask]$</pre><p>It’s ok so far. We make sure that the file we need has been created:</p><pre>[oracle@localhost mask]$ ls -l</pre><pre>total 36</pre><pre>-rwxr-xr-x. 1 oracle oinstall 8416 Jan 20 10:02 libmask.so</pre><pre>-rw-r — r — . 1 oracle oinstall 458 Jan 20 10:02 mask.c</pre><pre>-rw-r — r — . 1 oracle oinstall 143 Jan 20 10:02 mask.h</pre><pre>-rwxr-xr-x. 1 oracle oinstall 8472 Jan 20 10:14 <strong>testlib</strong></pre><pre>-rw-r — r — . 1 oracle oinstall 83 Jan 20 10:11 testlib.c</pre><pre>[oracle@localhost mask]$</pre><p>Don’t forget to set the environment variable:</p><pre>[oracle@localhost mask]$ export LD_LIBRARY_PATH=/home/oracle/mask:$LD_LIBRARY_PATH</pre><pre>[oracle@localhost mask]$</pre><p>Next, run:</p><pre>[oracle@localhost mask]$ ./testlib</pre><pre>[oracle@localhost mask]$</pre><p>Check the execution result. A log.txt file should have been created with the line “echo testing…” in it:</p><pre>[oracle@localhost mask]$ ls -l</pre><pre>total 40</pre><pre>-rwxr-xr-x. 1 oracle oinstall 8416 Jan 20 10:02 libmask.so</pre><pre>-rw-r — r — . 1 oracle oinstall 16 Jan 20 10:18 <strong>log.txt</strong></pre><pre>-rw-r — r — . 1 oracle oinstall 458 Jan 20 10:02 mask.c</pre><pre>-rw-r — r — . 1 oracle oinstall 143 Jan 20 10:02 mask.h</pre><pre>-rwxr-xr-x. 1 oracle oinstall 8472 Jan 20 10:14 testlib</pre><pre>-rw-r — r — . 1 oracle oinstall 83 Jan 20 10:11 testlib.c</pre><pre>[oracle@localhost mask]$ cat log.txt</pre><pre><strong>echo testing…</strong></pre><pre>[oracle@localhost mask]$</pre><p>Here it is. This means that our library works, and we can now start setting up Oracle.</p><h3>Step 2: Set up Oracle so that it can detect the library</h3><p>To do this, write the path to our library in a special configuration file — extproc.ora. To do this, open it:</p><pre>[oracle@localhost mask]$ nano $ORACLE_HOME/hs/admin/extproc.ora</pre><p>In the opening window, add a line with the path to our library at the end:</p><pre>SET EXTPROC_DLLS=ONLY:/home/oracle/mask/libmask.so</pre><p>Close and save.</p><p>Now you can move on to creating the necessary objects directly in the Oracle database itself.</p><h3>Step 3: Create objects in the database</h3><p>For clarity, we will use the Oracle SQL Developer application, which is already installed on the virtual machine. Let’s run it.</p><blockquote>The password is the standard “oracle”.</blockquote><p>Open a blank SQL Worksheet (Alt-F10) and paste the command to create the Library:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/4612fe4dd39e01de05cd47a606848a24/href">https://medium.com/media/4612fe4dd39e01de05cd47a606848a24/href</a></iframe><p>Execute (Ctrl-Enter). We see the message that the library has been created:</p><pre>Library LIB_MASK compiled</pre><h3>Step 4: Create PL\SQL functions and procedures that we will use to call our functions and procedures written in C.</h3><p>To do this, we create the pkg_mask package, in which we describe one function and one procedure. Paste the code into the worksheet and execute:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/78aba282ed972c2a53b409068c689876/href">https://medium.com/media/78aba282ed972c2a53b409068c689876/href</a></iframe><p>Check that the package has been created:</p><pre>Package PKG_MASK compiled</pre><p>Now we can check how everything works. To do this, we execute the ordinary select, which call the package function:</p><pre>select pkg_mask.mask(‘1234567890ABCD’) from dual;</pre><p>Here is the result — a masked string.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/682/0*R79__fbtv4JGgNh7" /></figure><p>Everything works fine ))</p><blockquote><em>Note: If suddenly an error occurs, try to close sql developer and open it again.</em></blockquote><p>Let’s also check how the second logit procedure works, which writes text to the file:</p><pre>call pkg_mask.logit(‘Test write to log’);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/558/0*OeGpe10TSwMPR6-9" /></figure><p>The procedure was completed successfully. Let’s check that there is a log entry. To do this, go back to the terminal and see:</p><pre>[oracle@localhost mask]$ cat log.txt</pre><pre>‘Test write to log’</pre><p>So we got the expected result. Great!</p><h3>Conclusion</h3><p>We have discussed with you an example of how to create and include external libraries to Oracle and easily use them in queries and procedures. If you decide to implement something with this approach yourself, then be sure to check the Oracle documentation — there may be minor nuances in various versions.</p><p>I love helping organizations to empower their businesses with data, so I’d love to hear your thoughts on the above in the comments, and feel free to connect with me on <a href="https://www.linkedin.com/in/yevgeniysamoilenko/">LinkedIn</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=214067761061" width="1" height="1" alt=""><hr><p><a href="https://medium.com/codex/extending-the-sql-and-pl-sql-with-custom-external-functions-and-procedures-214067761061">Extending the SQL and PL/SQL with custom external functions and procedures</a> was originally published in <a href="https://medium.com/codex">CodeX</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Which DBMS to Choose and Why? (Part 2)]]></title>
            <link>https://medium.com/codex/which-dbms-to-choose-and-why-part-2-1883795486b1?source=rss-cd22af8da646------2</link>
            <guid isPermaLink="false">https://medium.com/p/1883795486b1</guid>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[rdbms]]></category>
            <category><![CDATA[nosql]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Yevgeniy Samoilenko]]></dc:creator>
            <pubDate>Tue, 12 Oct 2021 13:18:04 GMT</pubDate>
            <atom:updated>2022-04-03T08:42:35.241Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SWuZC_N2sRR3lnt1fTErMg.jpeg" /></figure><p>After posting “<a href="https://medium.com/@yevgeniy.sam/how-to-%D1%81hoose-a-dbms-to-solve-your-tasks-943039388756">How to Сhoose a DBMS to Solve Your Tasks (Part 1)</a>” I received fair comments about not mentioning DBMS types such as Time Series and Spatial. In this article, I’ll briefly describe them and add two more types –Search engines and Object-oriented.</p><h4>Let me remind you that the previous article described:</h4><ul><li>Relational</li><li>Key-value</li><li>Document</li><li>Graph</li><li>Column-oriented</li></ul><h4>This post touches upon:</h4><ul><li>Time Series</li><li>Spatial</li><li>Search engines</li><li>Object-oriented</li></ul><p>Just like in the previous article, you can find the summary table at the end of the text. The table contains all types of DBMS we talked about earlier, plus the ones we will discuss today. If you’re only interested in a compact compilation (type, when to choose, popular DBMS of this type) — scroll through the text to the very end.</p><h3>Time Series DBMS</h3><p>This DBMS is optimized for storing time-stamped or time-series data. Time series data can contain measurements or events that are tracked, collected, or combined over a period of time. This could be data collected from motion tracking sensors, JVM metrics from Java applications, market trading data, network data, API responses, process uptime, etc.</p><p>Data is stored with timestamps — this is a key feature. Also, data is indexed and written so that it is possible to query this time series data much faster than when used in a classic relational database.</p><blockquote><strong>The most well-known DBMS of this type are: </strong><a href="https://www.influxdata.com/products/influxdb-overview/">InfluxDB</a>, <a href="https://kx.com/">Kdb+</a>, <a href="https://prometheus.io/">Prometheus</a>, <a href="https://www.timescale.com/">TimescaleDB</a>, <a href="https://questdb.io/">QuestDB</a>, <a href="https://aws.amazon.com/timestream/">AWS Timestream</a>, <a href="http://opentsdb.net/">OpenTSDB</a>, <a href="https://griddb.net/">GridDB</a>.</blockquote><h4>When to choose Time Series DBMS</h4><p>The primary field to apply such DBMS are monitoring, telemetry handling, and financial systems.</p><h4>When you shouldn’t choose Time Series DBMS</h4><p>Refrain from using such a DBMS for tasks that do not involve time series and timestamps.</p><h3>Object-Oriented DBMS</h3><p>As the name suggests, this DBMS is optimized for storing and handling objects. As in the case with OOP (Object-oriented Programming), these objects have properties and methods in the DBMS. They also implement encapsulation and polymorphism. The main goal of using object DBMS is to ease life for developers who use the object programming model. Developers won’t have to transform objects into tables, rows with their relationships and back.</p><blockquote><strong>Bright representatives of this type of DBMS are: </strong><a href="https://www.mongodb.com/realm">MongoDB Realm</a>, <a href="https://www.intersystems.com/">InterSystems Caché</a>, <a href="https://ignitetech.com/softwarelibrary/objectstore">ObjectStore</a>, <a href="https://www.actian.com/data-management/nosql-object-database/">Actian NoSQL DB</a>, <a href="https://objectivity.com/resources/objectivitydb-10-product-introduction-and-strengths/">Objectivity/DB</a>.</blockquote><h4>When to choose Object DBMS</h4><p>Honestly, I haven’t seen many successful implementations that use object DBMS. However, object databases are generally recommended for high-performance object manipulation that has a complex structure. At the same time, development involves object-oriented programming languages. Some of the common applications that use object databases are real-time systems, architectural &amp; engineering for 3D modeling, telecommunications, and scientific products, molecular science, and astronomy.</p><h4>When you shouldn’t choose Object DBMS</h4><p>Do not choose an object DBMS if you plan to use a classic SQL language, if you don’t use OOP, or if there are any plans to migrate from this database to another one. If you don’t have a deep understanding of OOP, in most cases you should opt for document-oriented DBMS.</p><h3>Search Engine DBMS</h3><p>This type of DBMS is used to provide full-text searches. Moreover, searches can be made on different data such as data from other DBs, e-mail, RSS-feed, text, JSON, XML, CSV, and even PDF and MS Office documents. Search engine DBMS has its own optimized approaches to indexing data. This includes the use of so-called inverted and faceted indexes to provide near real-time searches. Different DBMS of this type may use different query languages.</p><blockquote><strong>Well-known DBMS of this type are: </strong><a href="https://solr.apache.org/">Apache Solr</a>, <a href="https://www.elastic.co/">Elasticsearch</a>, <a href="https://www.splunk.com/">Splunk</a>.</blockquote><h4>When to choose Search Engine DBMS</h4><p>Suitable for fast full-text searches across a variety of data sources, structured, semi-structured, and unstructured data Log collection and log search systems are perfect examples.</p><h4>When you shouldn’t choose Search Engine DBMS</h4><p>If you search across a limited number of structured data fields.</p><h3>Spatial DBMS</h3><p>This type of DBMS is optimized to work with objects defined in geometric space. These can be simple objects (points, curves, polygons) or complex objects (3D objects, topological coverage, linear networks). This DBMS has a set of special functions to process creation, transformation, measurement (distance, area, volume), computation (intersection/contact), and selections based on certain criteria. In such DBMS, there are special indexes that optimize the handling of objects and a special standardized SQL/MM language.</p><blockquote><strong>Notable representatives of this type of DBMS: </strong><a href="https://www.oracle.com/ru/database/spatial/">Oracle Spatial</a>,<strong> </strong><a href="https://docs.microsoft.com/ru-ru/sql/relational-databases/spatial/spatial-data-sql-server?view=sql-server-ver15">Microsoft SQL</a>,<strong> </strong><a href="https://postgis.net/">PostGIS</a>, <a href="https://www.gaia-gis.it/fossil/libspatialite/index">Spatiallite</a>.</blockquote><h4>When to choose Spatial DBMS</h4><p>If you’re building GIS solutions, you plan to not just store, but also work with geometry objects at the DBMS level.</p><h4>When you shouldn’t choose Spatial DBMS</h4><p>If you plan to simply store geometry objects as coordinates.</p><h3>Conclusion</h3><p>We have added four more DBMS types to our list: Time series, Object-oriented, Search Engines, and Spatial. This is still not a complete list, and we will continue working on it in one of the upcoming articles. In future articles, we will consider several large vendors that offer many types of DBMS at once.</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fsheetsu.com%2Ftables%2F5ce8483524&amp;dntp=1&amp;display_name=Sheetsu&amp;url=https%3A%2F%2Fsheetsu.com%2Ftables%2F5ce8483524&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=sheetsu" width="700" height="766" frameborder="0" scrolling="no"><a href="https://medium.com/media/971ed5100520dc68975bb80e8446151c/href">https://medium.com/media/971ed5100520dc68975bb80e8446151c/href</a></iframe><p>Here is the link to a previous post:</p><p><a href="https://medium.com/@yevgeniy.sam/how-to-%D1%81hoose-a-dbms-to-solve-your-tasks-943039388756">How to Сhoose a DBMS to Solve Your Tasks</a></p><p>I love helping organizations to empower their businesses with data, so I’d love to hear your thoughts on the above in the comments below, and feel free to connect with me on <a href="https://www.linkedin.com/in/yevgeniysamoilenko/">LinkedIn</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1883795486b1" width="1" height="1" alt=""><hr><p><a href="https://medium.com/codex/which-dbms-to-choose-and-why-part-2-1883795486b1">Which DBMS to Choose and Why? (Part 2)</a> was originally published in <a href="https://medium.com/codex">CodeX</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Сhoose a DBMS to Solve Your Tasks]]></title>
            <link>https://medium.com/codex/how-to-%D1%81hoose-a-dbms-to-solve-your-tasks-943039388756?source=rss-cd22af8da646------2</link>
            <guid isPermaLink="false">https://medium.com/p/943039388756</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[nosql]]></category>
            <category><![CDATA[technology]]></category>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Yevgeniy Samoilenko]]></dc:creator>
            <pubDate>Tue, 28 Sep 2021 20:52:20 GMT</pubDate>
            <atom:updated>2022-04-03T07:53:23.994Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*1eftSOiz1fIXJU6k" /><figcaption>Photo by <a href="https://unsplash.com/@kaleidico?utm_source=medium&amp;utm_medium=referral">Kaleidico</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>Hi, my name is Yevgeny Samoilenko, and I am the Head of RnD. I work for a European Fintech company. I have over 15 years of experience in Fintech, in the software development for banks, central banks, insurance organizations, funds, money market, payment providers е.t.с. We developed almost every software using a database, and from time to time I saw developers confused when they were choosing the right database. Sometimes it is not obvious what type of DBMS would be the best choice.</p><p>This is the first article in a series about database management systems, in which I will try to tell you in simple words what solutions exist on the DBMS market. Also, I’ll explain what products to choose from and how to make the right choice according to your tasks.</p><p>My professional activities include job interviews of new hires. I have noticed that when asked “What types of DBMS exist?” many recall NoSQL and relational databases. But people often forget about DBMS types or can’t convey the idea of the difference between them. Here is the list of the most commonly used DBMS:</p><p>● Relational</p><p>● Graph</p><p>● Document</p><p>● Column-oriented</p><p>● Key-value</p><p>Check out the table at the end of the text where I sum up the article, namely, in which cases a particular type of DBMS is suitable and what popular solutions you might find applicable. If you’d like to skip a long explanation, you’re welcome to move on straight to the table. I should point out that some major vendors are equipped with several types of DBMS, both as standalone products and as internal implementations. As for Oracle, there isn’t a thing they haven’t got: a classic relational database; a separate Oracle NoSQL Database product that can be used as a column, document, and key-value; Autonomous Data Warehouse, specialized for data warehouses; Oracle Graph Server for graphs, and many other products. One can write a series of articles on it — I guess I’ll do this. For now, I’ll go on and tell you more about each type of DBMS.</p><h3>Types of Database Management Systems. How to Choose the Right Option</h3><h3>Relational DBMS</h3><p>These are classic systems used most often to build OLTP solutions (Online Transaction Processing). With these solutions, the DBMS works with many small transactions. The system requires a short response time and the ability to undo any changes made during the transaction under certain conditions. If you are building a system where you’d like to store a significant number of entities (tables) with different types of relations between them (one-to-one, one-to-many, many-to-many), then most likely you need a relational DBMS.</p><h4>Most well-known relational DBMS</h4><p>● Oracle</p><p>● Microsoft SQL</p><p>● PostgreSQL</p><p>● MySQL</p><p>● IBM DB2</p><h4>When to choose a relational database</h4><p>The main sign that indicates you need a relational DBMS is high data normalization. An additional hint is the need to process many short transactions with a large percentage of insertion transactions.</p><h4>When you shouldn’t choose a relational DBMS</h4><p>If you’d like to store unstructured data, it’s better to consider a document DBMS. If you need to store very simple key-value structures, choose specialized key-value DBMS.</p><p>Another sign that it makes sense not to opt for relational DBMS is the need to update values in the same rows frequently. This might be costly to relational DBMS — you have to use “advanced magic” to do everything correctly. In cases when there is an expert who wields this very magic, ignore this recommendation. Or if you know exactly how to do it.</p><h3>Graph DBMS</h3><p>It is a specific type of DBMS designed to deal with graphs, their nodes and properties, and arbitrary relationships between nodes. A simple example is building applications like social networks. There you need to store connections between users (nodes) according to different criteria: common interests, colleagues, relatives.</p><h4>Most well-known graph DBMS</h4><p>● Neo4j</p><p>● Amazon Neptune</p><p>● InfiniteGraph</p><p>● InfoGrid</p><h4>When to choose a graph database</h4><p>It’s worth paying attention to graph DBMS if you’re building some kind of a social network or implementing a recommendation and rating system. Also use it, when you have a deep understanding of what graphs are and what they are for.</p><h4>When you shouldn’t choose a graph DBMS</h4><p>In almost all other cases except those mentioned above.</p><h3>Document DBMS</h3><p>Document or document-oriented DBMS is one of the most popular NoSQL DBMS varieties, where the basic unit of the logical data model is a structured text with a specific syntax (document).</p><p>It is believed that the data model in document DBs is similar to the data model in object-oriented DBs. There is some truth to this, but the difference between them is that document databases only store state, but not objects’ behavior. Document DBMS are actively developing, and some of them even support schema validation.</p><h4>Most well-known document DBMS</h4><p>● CouchDB</p><p>● MongoDB</p><p>● Amazon DocumentDB</p><h4>When to choose a document database</h4><p>The application scope of document DBMS is wide. It can be used as a compact DB for a single microservice or large-scale solutions as a state store for something. It is best if you want to store objects in the same entity but with different structures. It is also of great use when you need to store structures (including objects, lists, and dictionaries), especially in a format similar to JSON.</p><h4>When you shouldn’t choose a document DBMS</h4><p>A document system is not the right solution to implement a transactional model, and certainly not the best option to render accounts.</p><h3>Column-oriented DBMS</h3><p>They are very unlike relational DBMS. Yes, they also consist of rows that have attributes and are grouped in tables. The differences in logical models are minor. But at the physical storage level, the differences are significant.</p><p>Relational DBMS stores data row-by-row. This means that to read the value of a particular column, you will have to read almost the entire row, at least from the first column to the desired column. In a column-oriented DBMS, data is stored column-by-column. That is, a column appears as a separate table. Reading will be performed right from a particular column. In practice, it works very fast — I’ve tested it on several implemented data warehouses.</p><h4>Advantages of column-oriented DBMS:</h4><p>● efficiently execute complex analytical queries for a large amount of data</p><p>● simple and almost instantaneous restructure of data tables</p><p>● considerable compression that saves space significantly</p><h4>Most well-known column-oriented DBMS</h4><p>● Sybase IQ (SAP IQ)</p><p>● Vertica</p><p>● ClickHouse</p><p>● Google BigQuery</p><p>● InfoBright</p><p>● Apache Druid</p><h4>When to choose a column-oriented database</h4><p>The main reason to use a column-based DBMS is that you want to build a data warehouse and plan to make selections with sophisticated analytical calculations. An indirect indicator is if the number of rows to be queried exceeds hundreds of millions.</p><h4>When you shouldn’t choose a column-oriented DBMS</h4><p>If the number of rows in the table from which you are querying is less than hundreds of millions of rows, a column-oriented DBMS will probably not have many advantages over a relational one.</p><p>Given the specifics of the system, a column-oriented DBMS will be inefficient if your queries are simple enough and the parameters of each query are static.</p><p>Keep in mind that column-oriented DBMS may have other limitations. For example, the query language may be different from classic SQL, or there may be no transactional support.</p><h3>Key-value DBMS</h3><p>This is probably one of the simplest types of DBMS. It’s a kind of a table with a unique key and a bound value that can have anything in it. Such DBMS are most often used for caching because they are fast — it is because there is a unique key and a query returns only one value.</p><p>Some key-value DBMS can completely run in memory and some have an option to set a time to live (TTL) for a record after which the record is automatically deleted.</p><h4>Most well-known key-value DBMS</h4><p>● Redis</p><p>● Memcached</p><h4>When to choose a key-value database</h4><p>This system is ideal if you need a DBMS for caching data or message brokers. It’s also great for databases where you need to keep fairly simple structures and have quick access to them.</p><h4>When you shouldn’t choose a key-value DBMS</h4><p>Don’t choose this one if you store a lot of entities (tables) in the database, and the entities have complex structures with different data types. Also, consider another option if you expect to make complex queries that return lots of rows.</p><h3>Conclusion</h3><p>There are a lot of different types of DBMS and choosing the right one could be a headache sometimes. I believe I made this matter a little bit clearer and now you can easily choose the right DBMS in a minute if not a second.</p><p>If you are planning to build a complex solution and feel that you need many types of DBMS — go for it, that’s fine. Also, don’t try to immediately decide on a DBMS vendor, usually, this is a secondary topic.</p><p>Pick a DBMS type based on these three things:</p><p>● type of tasks you need to solve</p><p>● types of data to be processed</p><p>● growth prospects and scaling</p><p>Pay attention to the popularity of DBMS — it guarantees a wide range of developers and development tools to help you find a quick answer to your question.</p><p>In the table below, I concisely compiled everything I’ve covered in this article.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*utEW-uf-zpO1UvoUuIs-7A.png" /></figure><p>I hope I was helpful. In the next articles of the DBMS series, you’ll learn how to choose between cloud and on-premise solutions, paid and free options.</p><p>I love helping organizations to empower their businesses with data, so I’d love to hear your thoughts on the above in the comments below, and feel free to connect with me on <a href="https://www.linkedin.com/in/yevgeniysamoilenko/">LinkedIn</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=943039388756" width="1" height="1" alt=""><hr><p><a href="https://medium.com/codex/how-to-%D1%81hoose-a-dbms-to-solve-your-tasks-943039388756">How to Сhoose a DBMS to Solve Your Tasks</a> was originally published in <a href="https://medium.com/codex">CodeX</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>