working example or tutorial about creating a sql kata

[opensas]

I have more than 1500 reputation and already created a couple of Javascript kata

I´d like to create a sql kata, but I have no clue about how to start with

Is there a tutorial or a complete example that I could have a look at?

I can only see the test cases but I don't know how to setup the initial data (create table, inserts, etc) or the expected result.

thanks a lot

[Blind4Basics]

You can forfeit an existing kata, then open a fork of an existing solution to get the full example/structure

[ggorlen]

If you do use an existing SQL kata, be sure to avoid exposing the solution in the preloaded code file. It's easy for a code warrior to read the file in the sample tests and view the solution. Also, drop and recreate the database per it block so that there's no surprising state shared across cases.

Here's an opinionated suggestion of how to test SQL which is similar to the "insert example" button in the new kata editor. In both cases, sample tests are explicitly visible to the code warrior rather than buried in the interface with compare_with, which many challenges use and abuse. Even if the challenger is not familiar with Ruby, it's less magical and easier to debug when exposed. They're always free to ignore the test cases. I prefer a close to 1:1 ratio between it block and expect calls so the challenger can localize their problem easily.

Disadvantages of this approach are that the challenger might be surprised or confused by the Ruby code, so offering an explanatory note at the end of the instructions can go a long way to mitigating this. Another possible downside is that the test file can become verbose, so you can strategically move some of the content to helper functions in the preloaded file.

Other than Codewars' run_sql function, the optional diff table code and interactions with the DB object, the code uses normal RSpec comparisons on an array of hashes.

Random tests are important to include for the submission to prevent cheating and can use Faker and Ruby's Random class.

Example instructions:

Write a query to retrieve all rows from the widgets table where the varchar widget name column starts with the substring "foo". ...Include examples, schema images, etc...

Complete solution:

SELECT * FROM widgets WHERE widgets.name LIKE 'foo%';

Initial solution:

-- Write your query here

Preloaded code:

def show_diff_table(actual, expected)
  if actual.empty?
    puts "<LOG::Results: Actual>No rows returned"
    puts "<TAB:TABLE:Results: Expected>#{expected.to_json()}"
  else
    daff_data = DaffWrapper.new(
      actual, 
      expected, 
      index: true
    ).serializable
    Display.daff_table(
      daff_data, 
      label: "Diff", 
      tab: true, 
      allow_preview: true
    )
  end
end

Test cases and example test cases:

describe "Query tests" do
  after(:each) {DB.drop_table?(:widgets)}
  before(:each) do
    DB.create_table(:widgets) do
      primary_key(:id)
      varchar(:name)
    end
  end
  
  it "should work on an example test" do
    DB[:widgets].insert(name: "foo")
    DB[:widgets].insert(name: "quux")
    DB[:widgets].insert(name: "foobar")
    expected = [{:id => 1, :name => "foo"}, 
                {:id => 3, :name => "foobar"}]
    actual = run_sql.to_a()
    show_diff_table(actual, expected)
    expect(actual).to eq(expected)
  end
end

[Blind4Basics]

@ggorlen : could you provide an example of random tests with this, plz?

[ggorlen]

Sure, either Faker or rand/sample works to generate mock data. Then run the reference query on the database and assert the comparison of the result arrays as normal:

# ...
  it "should work on a random test" do
    40.times do
      name = (1..rand(1..4)).map {"foo".chars.sample}.join
      DB[:widgets].insert(name: name)
    end
    
    ref_soln_query = "SELECT * FROM widgets WHERE widgets.name LIKE 'foo%';"
    expected = DB[ref_soln_query].to_a()
    actual = run_sql.to_a()
    show_diff_table(actual, expected)
    expect(actual).to eq(expected)
  end
# ...

Happy to hear any improvements/suggestions you might have.

[Blind4Basics]

thx! 👍 (unforunately, I cannot suggest anything since I didn't ever write a sql test site... ;/ )

PR: #105. If wanna take a quick look to it. ;)

cheers & thx again.

[ggorlen]

There's a problem with the approach as I originally posted. The way show_diff_table and run_sql interact causes the table to fail to render when the select statement returns no rows. I've updated the example to handle this case. Hopefully it's the only edge case but we might want to hold off on #105 for a bit until it can be better validated. Luckily, the approach is being exercised in the field so I have reason to believe any issues will surface soon.

[Blind4Basics]

thx! I updated the PR.