# This set of examples demonstrates the use of sql templates.

# Method "describe" opens up an example group.
describe 'My database' do
  # Simple inline query example.
  it 'contains system tables' do
    # Make sure we have at least one system object in the database.
    expect(query("select * from sysobjects where xtype = 'S';").count).to be > 0
  end

  # The same query, this time using a SQL template located in file "sql/template_examples/sysobject.sql.erb".
  it 'contains system tables (take two)' do
    # Every (*.sql.erb) file in folder "sql" can be called as a method on object "sql".
    # Sub-folders of folder "sql" appear as children of object "sql" with their (*.sql.erb) files automatically available as methods.
    expect(sql.template_examples.sysobjects.count).to be > 0
  end

  # This time we'll use a parameterized template.
  it 'contains system tables (take three)' do
    # Every template can accept parameters; see file "sql/template_examples/sysobject_with_params.sql.erb".
    expect(sql.template_examples.sysobjects_with_params(:xtype => 'S').count).to be > 0
  end

  # SQL Templates can contain multiple statements and can return multiple resultsets.
  it 'can play with numbers' do
    # Note that this time we're calling the template with a block which receives the results as a block parameter.
    sql.template_examples.play_with_numbers(:x => 2, :y => 12) do |results|
      # The results object contains an array of all the resultsets generated by the query script.
      # A resultset contains an array of records. Each record is a hash of field => value pairs.
      
      # First resultset; First record; Column "product".
      expect(results[0][0][:product]).to be == 24

      # A resultset can be matched directly against an array of hashes using method "match".
      expect(results[1]).to match([{:x => 2, :y => 12, :sum => 14}])

      # Or against a CSV file stored in project's "data" folder (see file "data/template_examples/numbers_expected_output.csv").
      expect(results[2]).to match('template_examples/numbers_expected_output.csv')

      # A resultset's values can be matched one-by-one.
      expect(results[2][0][:p]).to be == 2
      expect(results[2][0][:s]).to be == 34
      expect(results[2][1][:p]).to be == 12
      expect(results[2][1][:s]).to be == 44
    end
  end

  # Every "it" (example) is executed in a T-SQL transaction which is rolled back once the example is complete.
  # No example can ever interfere with the results of another example.
  # 
  # CSV files can be used to load data directly into a table.
  # In this example, we will create a table, populate it with data,
  # calculate the exponentiation of one column based on another column
  # and verify the results against an expected resultset stored in a CSV file.
  it 'can play with numbers (take two)' do
    # Create the table - see file "sql/template_examples/create_my_table.sql.erb".
    sql.template_examples.create_my_table
    # We can populate any table with data from the "data" folder by calling method "load_csv".
    # See file "data/template_examples/my_table_initial_data.csv".
    load_csv('template_examples/my_table_initial_data.csv', 'MyTable')

    # Now let's test the system scalar function Power.
    # We will use it in a query expression executed agaings MyTable and we
    # will compare the results against a CSV file - we should expect them to match.
    # See files "sql/template_examples/my_table_on_power.sql.erb" and "data/template_examples/my_table_expected_power_results.csv".
    expect(sql.template_examples.my_table_on_power).to match('template_examples/my_table_expected_power_results.csv')
  end
end
