Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Delete (cruD) Using Ruby-OCI8

DZone's Guide to

Delete (cruD) Using Ruby-OCI8

As we make our way to the final letter in CRUD, see how you can use Ruby (and a handy driver) to delete data from your tables.

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

In this post, we're going to take a look at the D in CRUDDelete.

We use the Ruby-oci8 driver to delete some data in the database tables, using the connection object created in the Initial Setup section of the first post in this series.

PLEASE REVIEW ALL EXAMPLE CODE AND ONLY RUN IT IF YOU ARE SURE IT WILL NOT CAUSE ANY PROBLEMS WITH YOUR SYSTEM.

Helper Function

My helper function get_all_rows() encapsulates a select statement used to verify that the deletes worked. The select functionality is covered in the R part of this series, so I won't go into the details here.

def get_all_rows (label, data_type = 'people')
 connectString = ENV['db_connect']
 con = OCI8.new(connectString)

 # Query all rows
 statement = 'select id, name, age, notes from lcs_people order by id';

 if data_type == 'pets'
   statement = 'select id, name, owner, type from lcs_pets order by owner, id'
 end

 cursor = con.parse(statement)
 cursor.exec
 printf " %s:\n", label
 cursor.fetch() {|row|
 if data_type == 'people'
   printf " Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
 else
   printf " Id: %d, Name: %s, Owner: %d, Type: %s\n", row[0], row[1], row[2], row[3]
 end
 }
 printf "\n"
end

Add this function to the top of your file.

Resetting the Data

To keep the examples clean and precise, I will reset the data at times.

Create a new file called reset_data.rb with the following code and run it whenever you would like to reset the data. (Notice this version adds people and pet data not included in other sections.)

require 'oci8'
connectString = ENV['db_connect']
con = OCI8.new(connectString)

# Delete rows
cursor = con.parse("delete from lcs_pets")
cursor.exec

# Reset Identity Coulmn
cursor = con.parse("alter table lcs_pets modify id generated BY DEFAULT as identity (START WITH 8)")
cursor.exec

# Delete rows
cursor = con.parse("delete from lcs_people")
cursor.exec

# Reset Identity Coulmn
cursor = con.parse("alter table lcs_people modify id generated BY DEFAULT as identity (START WITH 8)")
cursor.exec

# Insert default people rows
cursor = con.parse("INSERT INTO lcs_people(id, name, age, notes) VALUES (:id, :name, :age, :notes)")
cursor.max_array_size = 7
cursor.bind_param_array(:id, [1, 2, 3, 4, 5, 6, 7])
cursor.bind_param_array(:name, ["Bob", "Kim", "Cheryl", "Bob", "Stacey", "Pete", "Pat"])
cursor.bind_param_array(:age, [35, 27, 23, 27, 45, 23, 36])
cursor.bind_param_array(:notes, ["I like dogs", "I like birds", "I like horses", "I like rabbits", "I like snakes", "I like cats", "I like dogs"])
people_row_count = cursor.exec_array
printf " %d people rows inserted\n", people_row_count

# Insert default pet rows
cursor = con.parse("INSERT INTO lcs_pets(id, name, owner, type) VALUES (:id, :name, :owner, :type)")
cursor.max_array_size = 7
cursor.bind_param_array(:id, [1, 2, 3, 4, 5, 6, 7])
cursor.bind_param_array(:name, ["Duke", "Dragon", "Sneaky", "Red", "Red", "Buster", "Fido"])
cursor.bind_param_array(:owner, [1, 2, 5, 2, 3, 1, 7])
cursor.bind_param_array(:type, ["dog", "bird", "snake", "bird", "horse", "dog", "cat"])
pet_row_count = cursor.exec_array
printf " %d pet rows inserted\n", pet_row_count

con.commit

Boilerplate Template

The template we will be using is:

require 'oci8'
connectString = ENV['db_connect']

def get_all_rows (label, data_type = 'people')
 connectString = ENV['db_connect']
 con = OCI8.new(connectString)

 # Query all rows
 statement = 'select id, name, age, notes from lcs_people order by id';

 if data_type == 'pets'
   statement = 'select id, name, owner, type from lcs_pets order by owner, id'
 end

 cursor = con.parse(statement)
 cursor.exec
 printf " %s:\n", label
 cursor.fetch() {|row|
 if data_type == 'people'
   printf " Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
 else
   printf " Id: %d, Name: %s, Owner: %d, Type: %s\n", row[0], row[1], row[2], row[3]
 end
 }
 printf "\n"
end

con = OCI8.new(connectString)

get_all_rows('Original Data', 'pets')

# Your code here

get_all_rows('New Data', 'pets')

For each exercise, replace the "# Your code here" line with your code.

Reset the data

First, let's run reset_data.rb to set up our data.

Simple Delete

We will perform a simple delete that removes a single record from the lcs_people table.  These are the steps performed in the code snippet below.

  • Prepare a SQL DELETE statement, deleting the record with an id of 1.
  • Parse the statement to create a cursor.
  • Bind the id value. (See the R part of this series for an explanation of bind variables.)
  • Execute the statement.
  • Commit the transaction.
statement = "delete from lcs_pets where id = :id"
cursor = con.parse(statement)
cursor.bind_param(:id,1)
cursor.exec
con.commit

When I run this code in my Ruby session, I see:

Image title

 Original Data:
 Id: 1, Name: Duke, Owner: 1, Type: dog
 Id: 6, Name: Buster, Owner: 1, Type: dog
 Id: 2, Name: Dragon, Owner: 2, Type: bird
 Id: 4, Name: Red, Owner: 2, Type: bird
 Id: 5, Name: Red, Owner: 3, Type: horse
 Id: 3, Name: Sneaky, Owner: 5, Type: snake
 Id: 7, Name: Fido, Owner: 7, Type: cat

 New Data:
 Id: 6, Name: Buster, Owner: 1, Type: dog
 Id: 2, Name: Dragon, Owner: 2, Type: bird
 Id: 4, Name: Red, Owner: 2, Type: bird
 Id: 5, Name: Red, Owner: 3, Type: horse
 Id: 3, Name: Sneaky, Owner: 5, Type: snake
 Id: 7, Name: Fido, Owner: 7, Type: cat

Extra Fun 1

Delete all the birds.

Your results should be:

Image title

 Original Data:
 Id: 6, Name: Buster, Owner: 1, Type: dog
 Id: 2, Name: Dragon, Owner: 2, Type: bird
 Id: 4, Name: Red, Owner: 2, Type: bird
 Id: 5, Name: Red, Owner: 3, Type: horse
 Id: 3, Name: Sneaky, Owner: 5, Type: snake
 Id: 7, Name: Fido, Owner: 7, Type: cat

 New Data:
 Id: 6, Name: Buster, Owner: 1, Type: dog
 Id: 5, Name: Red, Owner: 3, Type: horse
 Id: 3, Name: Sneaky, Owner: 5, Type: snake
 Id: 7, Name: Fido, Owner: 7, Type: cat

Answer:

statement = "delete from lcs_pets where type = :type"
cursor = con.parse(statement)
cursor.bind_param(:type,"bird")
cursor.exec
con.commit

Reset the Data

Now is a good time to run reset_data.rb.

Boilerplate Change

Change the boilerplate get_all_rows statements to get people and pet data.

get_all_rows('Original People Data', 'people')
get_all_rows('Original Pet Data', 'pets')

# Your code here

get_all_rows('New People Data', 'people')
get_all_rows('New Pet Data', 'pets')

Deleting Records Referenced by Foreign Keys

If you are using integrity constraints in your database (of course you are, because then you let the database do some heavy lifting for you), you will sometimes need to change the way you process your changes.

In our design, we have a Foreign Key constraint in lcs_pets that ensures if a pet has an owner, that owner exists.

This is the statement that creates the constraint in the Creating the Database Objects section of the Initial Setup post.

ALTER TABLE LCS_PETS ADD CONSTRAINT FK_LCS_PETS_OWNER FOREIGN KEY ("OWNER") REFERENCES "LCS_PEOPLE" ("ID")
/

If we attempt to delete a record in lcs_people that is referenced in lcs_pets (Person has a pet,) we get an error.

statement = "delete from lcs_people where id = :id"
cursor = con.parse(statement)
cursor.bind_param(:id,1)
cursor.exec
con.commit

When I run this code in my Ruby session, I see:

Image title

stmt.c:243:in oci8lib_230.so: ORA-02292: integrity constraint (BLAINE.FK_LCS_PETS_OWNER) violated - child record found (OCIError)
 from /home/bcarter/.rvm/gems/ruby-2.3.1/gems/ruby-oci8-2.2.2/lib/oci8/cursor.rb:129:in `exec'
 from delete2.rb:36:in `<main>'

Before deleting the person you have to handle the pet (watch out for claws and teeth).

There are a few options here, depending on your database design:

  • If: pets are not required to have an owner and you only want to delete the person, not the pets.  Then: you can update the pets and set their owner to null.
  • If: pets are required to have an owner.  Then: you can delete the pets for the owner.

In either of the above scenarios, you can update the pets and set their owner to another person.

Bob is moving out of our area and his new apartment doesn't allow pets, so he's giving them to Kim.  Let's use that last option here.

  • Prepare a SQL UPDATE statement, changing owner to 2 (Kim) for the records with an owner of 1 (Bob).  Updating is covered in the U part of this series.
  • Parse the statement to create a cursor.
  • Bind the new and old owner values.
  • Execute the statement.
  • Prepare a SQL DELETE statement, deleting records with an id of 1 (Bob).
  • Parse the statement to create a cursor.
  • Bind the id value.
  • Execute the statement.
  • Commit both transactions.
statement = "update lcs_pets set owner = :newOwner where owner = :oldOwner"
cursor = con.parse(statement)
cursor.bind_param(:newOwner,2)
cursor.bind_param(:oldOwner,1)
cursor.exec

statement = "delete from lcs_people where id = :id"
cursor = con.parse(statement)
cursor.bind_param(:id,1)
cursor.exec
con.commit

When I run this code in my Ruby session, I see:

Image title

 Original People Data:
 Id: 1, Name: Bob, Age: 35, Notes: I like dogs
 Id: 2, Name: Kim, Age: 27, Notes: I like birds
 Id: 3, Name: Cheryl, Age: 23, Notes: I like horses
 Id: 4, Name: Bob, Age: 27, Notes: I like rabbits
 Id: 5, Name: Stacey, Age: 45, Notes: I like snakes
 Id: 6, Name: Pete, Age: 23, Notes: I like cats
 Id: 7, Name: Pat, Age: 36, Notes: I like dogs

 Original Pet Data:
 Id: 1, Name: Duke, Owner: 1, Type: dog
 Id: 6, Name: Buster, Owner: 1, Type: dog
 Id: 2, Name: Dragon, Owner: 2, Type: bird
 Id: 4, Name: Red, Owner: 2, Type: bird
 Id: 5, Name: Red, Owner: 3, Type: horse
 Id: 3, Name: Sneaky, Owner: 5, Type: snake
 Id: 7, Name: Fido, Owner: 7, Type: cat

 New People Data:
 Id: 2, Name: Kim, Age: 27, Notes: I like birds
 Id: 3, Name: Cheryl, Age: 23, Notes: I like horses
 Id: 4, Name: Bob, Age: 27, Notes: I like rabbits
 Id: 5, Name: Stacey, Age: 45, Notes: I like snakes
 Id: 6, Name: Pete, Age: 23, Notes: I like cats
 Id: 7, Name: Pat, Age: 36, Notes: I like dogs

 New Pet Data:
 Id: 1, Name: Duke, Owner: 2, Type: dog
 Id: 2, Name: Dragon, Owner: 2, Type: bird
 Id: 4, Name: Red, Owner: 2, Type: bird
 Id: 6, Name: Buster, Owner: 2, Type: dog
 Id: 5, Name: Red, Owner: 3, Type: horse
 Id: 3, Name: Sneaky, Owner: 5, Type: snake
 Id: 7, Name: Fido, Owner: 7, Type: cat

When you change data it's a good idea to verify the number of affected rows. This is covered in the R part of this series.

Extra Fun 2

Due to a zoning change, snakes are no longer allowed in our area.  Stacey has decided to move and take Sneaky with her.

Let's fix our data.

Your results should be:

Image title

 Original People Data:
 Id: 2, Name: Kim, Age: 27, Notes: I like birds
 Id: 3, Name: Cheryl, Age: 23, Notes: I like horses
 Id: 4, Name: Bob, Age: 27, Notes: I like rabbits
 Id: 5, Name: Stacey, Age: 45, Notes: I like snakes
 Id: 6, Name: Pete, Age: 23, Notes: I like cats
 Id: 7, Name: Pat, Age: 36, Notes: I like dogs

 Original Pet Data:
 Id: 1, Name: Duke, Owner: 2, Type: dog
 Id: 2, Name: Dragon, Owner: 2, Type: bird
 Id: 4, Name: Red, Owner: 2, Type: bird
 Id: 6, Name: Buster, Owner: 2, Type: dog
 Id: 5, Name: Red, Owner: 3, Type: horse
 Id: 3, Name: Sneaky, Owner: 5, Type: snake
 Id: 7, Name: Fido, Owner: 7, Type: cat

 New People Data:
 Id: 2, Name: Kim, Age: 27, Notes: I like birds
 Id: 3, Name: Cheryl, Age: 23, Notes: I like horses
 Id: 4, Name: Bob, Age: 27, Notes: I like rabbits
 Id: 6, Name: Pete, Age: 23, Notes: I like cats
 Id: 7, Name: Pat, Age: 36, Notes: I like dogs

 New Pet Data:
 Id: 1, Name: Duke, Owner: 2, Type: dog
 Id: 2, Name: Dragon, Owner: 2, Type: bird
 Id: 4, Name: Red, Owner: 2, Type: bird
 Id: 6, Name: Buster, Owner: 2, Type: dog
 Id: 5, Name: Red, Owner: 3, Type: horse
 Id: 7, Name: Fido, Owner: 7, Type: cat

Answer:

statement = "delete from lcs_pets where owner = :owner"
cursor = con.parse(statement)
cursor.bind_param(:owner,5)
cursor.exec

statement = "delete from lcs_people where id = :id"
cursor = con.parse(statement)
cursor.bind_param(:id,5)
cursor.exec
con.commit

Some Other Things You Could Try

  • Change the database constraints to delete or Null the child record on delete (a cascading delete). Delete a person and let the database handle the children.
  • Remove the people who don't have any pets.

Series Sections

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:
crud ,ruby ,database

Published at DZone with permission of Blaine Carter. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}