codemonth blog codemonth blog - One project every month - making stuff better. Fri, 21 Jul 2017 08:41:19 +0700 Fri, 21 Jul 2017 08:41:19 +0700 30 PLSQL Test data generators with auto back reference fields 52B627AADF8417C6E050007F010042DA Sun, 25 Jun 2017 22:02:14 +0700 So in another entry I described how to <a href=""> manually reference back to other fields</a> to use as input values in other columns for the row generator. What could be a bit more user friendly than that? Maybe using auto reference fields. So if you are using a generator in one of your columns that outputs something that can be used as input to another generator, it is now done automatically. Recursive test data using plsql and the testdata_ninja package 52B210E69A3F516BE050007F010037EE Sat, 24 Jun 2017 17:09:41 +0700 Just the other day I was running a test of a system at work. I have already run several tests against this system, so I already have a test data generator to generate clients in the system. This time though I had to generate some orders within the system. So a really quick way to do that is to reuse the existing generator for the client id reference (a foreign key in the real system) from the orders data rows. This way I don't have to create an actual client table. I can just reference the plsql test data generator directly in my definition of my order generator. PLSQL test data from cursor to table or from cursor to csv 52527FEBC9838E7DE050007F01007DC5 Mon, 19 Jun 2017 23:08:47 +0700 When you are creating test data it is usually either to create a quick test table for a piece of code, or because you need to simulate data extraction to another system. That is why any generator created with testdata_ninja includes methods to do that quickly. Every generator created includes a to_table method and a to_csv table function. Even more test data generated using the testdata_ninja plsql package 519A2E2EE2359580E050007F010074F7 Sat, 10 Jun 2017 19:14:23 +0700 Last entry to the testdata_ninja blog roll, was about how to create iterators that were unique when you create test data rows. In this entry I will show how to use back references to other column values to use them as input to other row generator functions. One example could be that you were creating a table of employees and you wanted the name to be more realistic. So we could have a country column in the row as well and use that as an input to create the name, so it looks like a name from that country. Extending Codemonth search to include Oracle Text fuzzy and stem searches 4F273D15A90FB8B6E050007F010064DE Wed, 10 May 2017 15:16:22 +0700 My previous entry showed how the contains operator of Oracle Text made it possible to build a search function for my website. It was a basic text search with no additional capability. For quick recap of how the query looked, this is what I could run to search the description of my projects for the word package. Oracle text and implementing codemonth website search 4F05A541DF061EBAE050007F01000BEF Mon, 08 May 2017 23:11:46 +0700 I promised on the ODTUG codetalk series to implement search on the codemonth website. So this entry is a short description of the first steps I have done to add simple search capability to the site. PLSQL promises vs dbms_scheduler chains 4ED68FB41473CD23E050007F01007873 Sat, 06 May 2017 15:01:32 +0700 10 lines of code vs 80 lines of code! When implementing JS promises in PLSQL, one of the main use cases is the ability that promises gives, to easily chain together dependent steps defined as functions. Also the ability to easily pass output from one step along to the next step in the chain. Adding support for Edition Based Redefinition for PROMISES_NINJA 4E61B48C078C1354E050007F01007F1B Sun, 30 Apr 2017 19:36:45 +0700 As part of another project, where I need to support the Edition Based Redefinition feature, I needed to add support inside the promise class for the dbms_scheduler job_class parameter. The job_class parameter can point to a service, and a service can be assigned a default edition to run in. This allows me to execute code inside a promise, and automatically support any edition that has been created in the database, as well as assigned to a service. Version your Oracle ORDS REST API using Edition Based Redefinition 4DD602D1F1385ACEE050007F01000E47 Sun, 23 Apr 2017 20:56:00 +0700 <strong style="font-style: italic;">Updated, May 4 2017: New approach</strong> One of the last entries in my blog described how I could <a href="">quickly publish all random_ninja functions as rest endpoints</a>. While I am still waiting for the ORDS team at Oracle to come out with automatic publish of functions and procedures including input parameters, I wanted to add another layer of functionality to my REST endpoints. Versioning; And for that we can use the cool Oracle feature called Edition Based Redefinition. Completing JWT_NINjA for full JWT support 4CBC2BC376CF83D6E050007F01003814 Sun, 09 Apr 2017 09:08:11 +0700 <p>In the single <a href="">blog post</a> I wrote on the JWT_NINJA package back in June last year, I ended the post by saying that I needed to implement both a verify and a parse function for the package.</p><p>Well, it took me a year, but I finally had a reason to go and implement it. As I am getting into Oracle ORDS and trying to get some experience in building REST services, I want to be able to use <a href="">JWT</a> as the token generator for the application that I am building.</p><p>So a quick refresh on how to generate a token using the package:</p> <pre><code class="language-sql"> select jwt_ninja.jwt_generate(p_signature_key => 'my secret password', p_reg_claim_issuer => 'My Application', p_reg_claim_subject => '147654') as token from dual; TOKEN ------------------------------------------------------------------------------------------------------------------------------------- eyAiYWxnIjogIkhTMjU2IiwgInR5cCI6ICJKV1QiIH0.eyAiaWF0IjogMTQ5MTcxNzIxODAwMCwgImlzcyI6ICJNeSBBcHBsaWNhdGlvbiIs ICJzdWIiOiAiMTQ3NjU0In0.Uy6KeKeO1/52+URTbhk1+spOIO3OYamLhViPJ1ZmjUU </code></pre> <p>So now that we have a token, we would store that on the client side, and every request from the client would include this token for us to validate. So to validate the token there is now a procedure jwt_ninja.jwt_verify_and_decode that will do just that. The third argument (p_do_parse) indicates if you only want the token to be verified or if you want to parse the fields inside the payload. The default is to only verify the validity of the signature and the message and not do the parse. If it is set to true, the values of the different <a href="">JWT claims</a> will be set and found in the output parameters of the procedure.</p><p> <pre><code class="language-sql"> declare mysecret varchar2(4000) := 'my secret password'; mytoken varchar2(4000) := jwt_ninja.jwt_generate(p_signature_key => 'my secret password', p_reg_claim_issuer => 'My Application', p_reg_claim_subject => '147654'); doparse boolean := false; myresult boolean; l_reg_claim_issuer varchar2(4000) := null; l_reg_claim_subject varchar2(4000) := null; l_reg_claim_audience varchar2(4000) := null; l_reg_claim_expiration date := null; l_reg_claim_notbefore date := null; l_reg_claim_issuedat date := null; l_reg_claim_jwtid varchar2(4000) := null; begin jwt_ninja.jwt_verify_and_decode(mytoken, mysecret, doparse, myresult, l_reg_claim_issuer, l_reg_claim_subject, l_reg_claim_audience, l_reg_claim_expiration, l_reg_claim_notbefore, l_reg_claim_issuedat, l_reg_claim_jwtid); if myresult then dbms_output.put_line('Verified'); dbms_output.put_line('Iss: ' || l_reg_claim_issuer); dbms_output.put_line('Sub: ' || l_reg_claim_subject); else dbms_output.put_line('Not verified'); end if; end; / </code></pre></p> <p>So know I can "close" this codemonth for now, since I can start to use it in my other project.</p> Take a random REST 4CA6C3C984F556E6E050007F01006CD5 Sat, 08 Apr 2017 10:07:37 +0700 <p>I needed to test <a href="">ORDS</a> for some webservice related projects at work. So I decided to build a rest interface for all the different random data functions in the <a href="">RANDOM_NINJA</a> package to test out, just how easy it is.</p><p>First order is to setup ORDS. Like always Tim over at <a href="">oracle-base</a> have a couple of really good articles on installing and setting up ORDS itself. So no need for me to duplicate that.</p><p>After setting up ORDS, and enabling my schema for rest services like in this <a href="">oracle-base article</a>, it is time to enable the functions in the different data domain packages. I decided to keep the URL scheme simple, so that the path would be /[package_name]/[function_name]/ for any of the functions that are part of the RANDOM_NINJA package.</p> Generating randomized incremental values 4AB10279DD66941AE050007F01003083 Tue, 14 Mar 2017 10:35:06 +0700 <p>In the previous entries, I explained the <a href="">basics of testdata_ninja</a> and then I explained how to <a href="">create foreign key relations</a> between your test data and existing tables. This time I will show how you can create data that are random but always increasing, either to create time patterns or to create unique id columns for primary keys. I will extend the format that I have already used in the previous entries, so the code will be familiar. If we look at the output of the test_days generator we have created, it currently looks like this: <pre><code class="language-sql"> SQL> select * from table(tdg_test_days.test_days); NAME BIRTHDAY MY_PK_REF -------------------------------------------------- ------------------ ---------- Arianna Turner 02-MAR-93 00:24:58 1 Sofie Dam 17-MAY-91 17:21:09 1 Camila King 27-OCT-62 18:16:56 10 Malik Henriksen 24-NOV-65 06:23:51 10 .... [more rows] .... </code></pre> There is no clear column with possible unique values, so I will create a new column where the data generator is an "incrementor". Like all the other different data fields, it is in the 3 field definition of the block that we define how to generate the data. So far the format looks like this: <pre><code class="language-sql"> name#varchar2(50)#person_random.r_name@birthday#date#person_random.r_birthday@my_pk_ref#number#£t_people¤person_num_pk¤simple¤5 </code></pre> So let us add a column in the beginning, that increments randomly but is unique nonetheless: <pre><code class="language-sql"> people_pk#number#^numiterate~1~3¤5 </code></pre> and here is the explanation of the fields, which are separated by the "~" character: <ul> <li>incrementor type (numiterate for numbers and datiterate for dates)</li> <li>start from (number or date to start increment from)</li> <li>increment range (for numbers min and max separated by the "¤" character and dates interval type (seconds, minutes etc), min and max separated by the "¤" character for example: seconds¤1¤5)</li> </ul> So if we now take a look at the full format in the code, it would look like this: <pre><code class="language-sql"> declare the_format varchar2(4000) := 'people_pk#number#^numiterate~1~3¤5@name#varchar2(50)#person_random.r_name@birthday#date#person_random.r_birthday@my_pk_ref#number#£t_people¤person_num_pk¤simple¤5'; begin testdata_ninja.generator_create('test_days', the_format); end; / </code></pre> And would create a result like the following: <pre><code class="language-sql"> 1 Sigurd Andreasen 20-NOV-63 12:33:49 1 6 Ella Madsen 30-JAN-80 07:09:39 1 9 Alexandra White 16-JUL-53 16:32:07 1 13 Benjamin Holst 08-APR-88 13:39:38 1 17 Lea Jepsen 24-MAY-88 17:21:01 1 21 Eli Morgan 07-AUG-86 15:54:27 10 26 Oskar Bruun 20-JUL-69 22:40:56 10 31 Jens Friis 26-APR-63 16:32:21 10 35 Camila Hall 05-DEC-78 23:30:19 10 </code></pre> Test data and foreign keys 4A9DA728FCF0404EE050007F010074D7 Mon, 13 Mar 2017 10:16:59 +0700 In my <a href="">last entry</a> on testdata_ninja I showed the basics on how to create your own custom test data generators.</p><p>Once you have created the simple ones you probably want to extend it a little bit, and create test tables that has relations. So you want to create random data, but random data that actually can join to another table. The way to do this, is with a special data generator construct in the testdata_ninja format. If you read the old entry, you can see that testdata_ninja format is a series of columns seperated by the "@" sign. Each column has at least 3 parts (divided by the "#" character), where the third is the data generator.</p><p>So in the previous example I wrote about we used functions from the <a href="">random_ninja</a> package. You can however, reference existing tables instead. First we want to create a master table that we can reference in our new generator. We will use the basic data generators that are already part of the testdata_ninja package to create this: <pre><code class="language-sql"> create table t_people as select * from table(testdata_generator.people(100)); </code></pre> This statement will create a table with a hundred rows in. This table is what we will use to create our other test data table. One of the rows is called person_num_pk, and that is the column with the relation to our other table. The way we define that is with a special character as the first character in our generator field. So in my last entry I defined the test table with the following format: <pre><code class="language-sql"> name#varchar2(50)#person_random.r_name@birthday#date#person_random.r_birthday </code></pre> So let us say that I wanted to add another column here, that linked to my primary key in the new table I just created. If I wanted to do that I simply create a third column definition like the following: <pre><code class="language-sql"> name#varchar2(50)#person_random.r_name@birthday#date#person_random.r_birthday@my_pk_ref#number#£t_people¤person_num_pk¤simple¤5 </code></pre> Notice the "£" sign. This is what specifies this generator as a reference to another table and not a function. The fields in the reference generator (divided with the "¤" character) are the following:</p><p> <ul> <li>table name that we are referencing (t_people)</li> <li>column in that table we are referencing (person_num_pk)</li> <li>distribution rule (simple or range)</li> <li>distribution count. If simple just one number, and every ref will this many rows in the new generator or range and you specify 2,5 then every reference will have between 2 and 5 rows in the new generator</li> </ul> So to create this new table we would simply run: <pre><code class="language-sql"> declare the_format varchar2(4000) := 'name#varchar2(50)#person_random.r_name@birthday#date#person_random.r_birthday@my_pk_ref#number#£t_people¤person_num_pk¤simple¤5'; begin testdata_ninja.generator_create('test_days', the_format); end; / </code></pre> and we can now select from the generator and see that each value in the my_pk_ref column will be a real reference back to the t_people table and there will be 5 "child" rows per real row from the t_people table: <pre><code class="language-sql"> select a.first_name , b.birthday from t_people a , (select name, birthday, my_pk_ref from table(tdg_test_days.test_days)) b where a.person_num_pk = b.my_pk_ref; FIRST_NAME BIRTHDAY ---------------------------------------------------------------------------------------------------- ------------------ Parker 14-SEP-64 13:35:56 Parker 18-APR-62 05:02:35 Parker 05-SEP-93 12:35:50 Parker 16-AUG-82 21:19:14 Parker 11-MAR-85 15:51:54 Bryson 03-SEP-84 19:56:20 Bryson 08-JUL-96 14:33:07 Bryson 30-DEC-62 06:28:59 .......... [more rows] </code></pre> Creating real test data - Fast and Efficiently 4A88304CCF5766B8E050007F01004878 Sun, 12 Mar 2017 09:10:18 +0700 <p>When I build <a href="">RANDOM_NINJA</a> I knew already one of things I wanted to use that library for. Building good valid and life-like test data has and is always a really big problem. The base for good test data is good and valid looking random data. Without that, most tests are not really valid, as data can be clustered wrongly, indexes act strange because data does not mimic real life and relations and constraints are hard to define or validate on data that is created using most available randomizing packages. That is why it was important to me that <a href="">RANDOM_NINJA</a> would be able to create random data from as many different domains as possible. As of today it can create more than 185 different data points from as many as 14 different data domains.</p><p>But having good random data is only half of it. You still need something that can define and create those tables. You also need something that can still maintain relations between those test tables, and make sure that foreign key distributions are real as well. So I created <a href="">TESTDATA_NINJA</a>. This package has some generic generators to create simple tables of people, <a href="">populations</a> which are true according to UN demographics data, CDR records and credit card transactions. The real function in this package is the custom generation procedure. This procedure can parse a string representation of what your data looks like and from that create pipelined functiones that can create thousands of test rows extremely fast and efficiently.</p><p>The testdata_ninja.generator_create procedure takes 2 arguments. The name of the new generator and the format of the test data. Below is a short description on how the format looks like.</p> plsql promises - promise complete solution 495B501DA98CB9A8E050007F0100141E Sat, 25 Feb 2017 08:28:28 +0700 <p>Finally. I got the 2 last methods completed. promise.all and promise.race is finished. So now the plsql promises library is feature complete. <b>promise.all</b> method takes a list of promises, built with the help of the <b>promises_ninja</b> package, and once the entire list of promises is fulfilled the top promise is set to fulfilled. Here is an example:</p> <p><pre><code class="language-sql">-- Test all method declare test_promise promise := promise(); a_promise1 promise; a_promise2 promise; a_promise3 promise; all_promises promises_ninja.promise_list_type; begin a_promise1 := promise('p_multiplier', 10); promises_ninja.build_promise_list(all_promises, a_promise1); a_promise2 := promise('p_multiplier', 20); promises_ninja.build_promise_list(all_promises, a_promise2); a_promise3 := promise('p_multiplier', 30); promises_ninja.build_promise_list(all_promises, a_promise3); test_promise.all_p(promises_ninja.convert_promise_list(all_promises)); dbms_lock.sleep(5); test_promise.check_and_set_value(); dbms_output.put_line('State is: ' || test_promise.state); end; /</code></pre></p><p>The output is the list of the promises with the fulfilled values. So you can loop through the output and display individual values like this:</p> <p><pre><code class="language-sql">declare test_promise promise := promise(); a_promise1 promise; a_promise2 promise; a_promise3 promise; all_promises promises_ninja.promise_list_type; promise_list_o promises_list_obj; promise_val promise; begin a_promise1 := promise('p_multiplier', 10); promises_ninja.build_promise_list(all_promises, a_promise1); a_promise2 := promise('p_multiplier', 20); promises_ninja.build_promise_list(all_promises, a_promise2); a_promise3 := promise('p_multiplier', 30); promises_ninja.build_promise_list(all_promises, a_promise3); test_promise.all_p(promises_ninja.convert_promise_list(all_promises)); dbms_lock.sleep(5); test_promise.check_and_set_value(); dbms_output.put_line('State is: ' || test_promise.state); if test_promise.val.getObject(promise_list_o) = dbms_types.success then -- We converted the object successfully for i in 1..promise_list_o.promise_list.count loop if promise_list_o.promise_list(i).getObject(promise_val) = dbms_types.success then -- We have an individual promise output value dbms_output.put_line('Value of promise is: ' || promise_val.getanyvalue); end if; end loop; end if; end; /</code></pre></p><p>Same code and technique can be used for the race method. The only difference is that the output in the race call is a normal promise and not a list of promises, as only the first promise to complete is returned.</p>