[jira] [Updated] (CASSANDRA-6328) Allow deleting records with a secondary index lookup

["David Huang (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CASSANDRA-6328?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

David Huang updated CASSANDRA-6328:
-----------------------------------

    Description: 
Currently, clients must query for row keys from a specific secondary index and delete the results. This incurs network traffic whereas a single server operation would be enough.

==== Example ====

In C* we would have to query for all playlist_id for user "JohnSmith" and issue deletes for each key. Deleting by secondary index would allow the entire operation to occur within C* without the client needing to issue multiple statements.

Delete By Secondary Index

CREATE TABLE playlist (
playlist_id text,
user_id text,
song_name set<text>,
PRIMARY_KEY (playlist_id)
);

CREATE INDEX on playlist (user_id);
DELETE FROM playlist WHERE user_id = "JohnSmith";

Delete By Client

// Schema
CREATE TABLE playlist (
playlist_id text,
user_id text,
song_name set<text>,
PRIMARY_KEY (playlist_id)
);

CREATE INDEX on playlist (user_id);

// Client
ResultSet resultSet = session.execute("select playlist_id from playlist where userid = "JohnSmith"");
PreparedStatement delete = session.prepare("DELETE FROM playlist WHERE playlist_id =  ?");
for(Row row : resultSet){
session.execute(delete.bind(row.getString("playlist_id")));
}
  

  was:
Currently, clients must query for row keys from a specific secondary index and delete the results. This incurs network traffic whereas a single server operation would be enough.

==== Example ====

In C* we would have to query for all playlist_id for user_id "JohnSmith" and issue deletes for each key. Deleting by secondary index would allow the entire operation to occur within C* without the client needing to issue multiple statements.

Delete By Secondary Index

CREATE TABLE playlist (
playlist_id text,
user_id text,
song_name set<text>,
PRIMARY_KEY (playlist_id)
);

CREATE INDEX on playlist (user_id);
DELETE FROM playlist WHERE user_id = "JohnSmith";

Delete By Client

// Schema
CREATE TABLE playlist (
playlist_id text,
user_id text,
song_name set<text>,
PRIMARY_KEY (playlist_id)
);

CREATE INDEX on playlist (user_id);

// Client
ResultSet resultSet = session.execute("select playlist_id from playlist where userid = "JohnSmith"");
PreparedStatement delete = session.prepare("DELETE FROM playlist WHERE playlist_id =  ?");
for(Row row : resultSet){
session.execute(delete.bind(row.getString("playlist_id")));
}
  


> Allow deleting records with a secondary index lookup
> ----------------------------------------------------
>
>                 Key: CASSANDRA-6328
>                 URL: https://issues.apache.org/jira/browse/CASSANDRA-6328
>             Project: Cassandra
>          Issue Type: Improvement
>          Components: Core
>            Reporter: David Huang
>            Priority: Minor
>
> Currently, clients must query for row keys from a specific secondary index and delete the results. This incurs network traffic whereas a single server operation would be enough.
> ==== Example ====
> In C* we would have to query for all playlist_id for user "JohnSmith" and issue deletes for each key. Deleting by secondary index would allow the entire operation to occur within C* without the client needing to issue multiple statements.
> Delete By Secondary Index
> CREATE TABLE playlist (
> playlist_id text,
> user_id text,
> song_name set<text>,
> PRIMARY_KEY (playlist_id)
> );
> CREATE INDEX on playlist (user_id);
> DELETE FROM playlist WHERE user_id = "JohnSmith";
> Delete By Client
> // Schema
> CREATE TABLE playlist (
> playlist_id text,
> user_id text,
> song_name set<text>,
> PRIMARY_KEY (playlist_id)
> );
> CREATE INDEX on playlist (user_id);
> // Client
> ResultSet resultSet = session.execute("select playlist_id from playlist where userid = "JohnSmith"");
> PreparedStatement delete = session.prepare("DELETE FROM playlist WHERE playlist_id =  ?");
> for(Row row : resultSet){
> session.execute(delete.bind(row.getString("playlist_id")));
> }
>   



--
This message was sent by Atlassian JIRA
(v6.1#6144)