SQL delete from one table + a jointable?

Question

I have three tables: log, activity and the jointable (many2many) log_activity (with log_id and activity_id + additional info data as columns).

I want to delete from log and log_activity.

I want to keep all logs from a specific user and only keep 100 rows from other users. That means I want to delete all rows that match a WHERE log.user_id != 1, but the last 100 (ORDER BY log.timestamp DESC).

I also want to delete from the jointable log_activity all entries that are related to the logs which get deleted. The activity table should not be touched.

I think that db.delete(TABLE_NAME, whereClause , whereArgs); is not helpful in this case..

So is someone able to come up with an efficient solution?

---

UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE

---

Inspired by the answers of Jacob Eggers and plafond and by further research I am trying like this now, but it does not work yet:

CREATE TABLE IF NOT EXISTS log ( 
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    timestamp LONG NOT NULL
);

CREATE TABLE IF NOT EXISTS log_activity ( 
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    log_id INTEGER NOT NULL,
    activity_id INTEGER NOT NULL,
    points INTEGER NOT NULL,
    FOREIGN KEY(log_id) REFERENCES log(_id) ON DELETE CASCADE,
    FOREIGN KEY(activity_id) REFERENCES activity(_id) ON DELETE CASCADE
);

Now for the android part:

SQLiteDatabase db = openHelper.getWritableDatabase();
db.execSQL("PRAGMA foreign_keys = ON;");
db.execSQL(CREATE_LOG); // see sql above
db.execSQL(CREATE_ACTIVITY); // not shown here, but like the sql-creates above
db.execSQL(CREATE_LOG_ACTIVITY); // see sql above

// ... insert some data ...
INSERT INTO "log" VALUES(1,1,1307797289000);
INSERT INTO "log" VALUES(2,1,1307710289000);
INSERT INTO "log" VALUES(3,2,1308089465000);
INSERT INTO "log" VALUES(4,2,1308079465000);

INSERT INTO "log_activity" VALUES(1,1,1,1);
INSERT INTO "log_activity" VALUES(2,1,2,2);
INSERT INTO "log_activity" VALUES(3,2,1,1);
INSERT INTO "log_activity" VALUES(4,2,2,2);
INSERT INTO "log_activity" VALUES(5,3,1,1);
INSERT INTO "log_activity" VALUES(6,3,2,2);
INSERT INTO "log_activity" VALUES(7,4,1,1);
INSERT INTO "log_activity" VALUES(8,4,2,2);

// check count of logs
Cursor c = db.query(false, "log", null, null, null, null, null, "_id asc", null);
android.util.Log.d("TEST", "log count before: "+c.getCount());

// check count of log_activities
Cursor c2 = db.query(false, "log_activity", null, null, null, null, null, "_id asc", null);
android.util.Log.d("TEST", "la count before: "+c2.getCount());

// delete some log-rows
long userId = 1;
int keepXLogsOfOthers = 1;
String del = "DELETE FROM log" +
                " WHERE user_id != " + userId +
                "  AND log._id NOT IN (" +
                "    SELECT _id" +
                "    FROM (" +
                "      SELECT _id" +
                "      FROM log" +
                "      WHERE user_id != " + userId +
                "      ORDER BY timestamp DESC" +
                "      LIMIT " + keepXLogsOfOthers +
                "    ) logs_of_others_to_keep" +
                ");";
db.execSql(del);

// check count of logs
Cursor c3 = db.query(false, "log", null, null, null, null, null, "_id asc", null);
android.util.Log.d("TEST", "log count after: "+c3.getCount());

// check count of log_activities
Cursor c4 = db.query(false, "log_activity", null, null, null, null, null, "_id asc", null);
android.util.Log.d("TEST", "la count after: "+c4.getCount());

output:

06-16 10:40:01.748: DEBUG/TEST(451): log count before: 4
06-16 10:40:01.748: DEBUG/TEST(451): la count before: 8
06-16 10:40:01.828: DEBUG/TEST(451): log count after: 3
06-16 10:40:01.838: DEBUG/TEST(451): la count after: 8

This means the DELETE operation it self is fine (I also checked that the correct rows are deleted which solves the first issue!!), but ON DELETE CASCADE does not work... why?

Answer 1

Solved the problem by using a foreign key on the log_activity table like this:

FOREIGN KEY(log_id) REFERENCES log(_id) ON DELETE CASCADE

and a delete statement like this:

long userId = 1;
int keepXLogsOfOthers = 1;
String del = "DELETE FROM log" +
                " WHERE user_id != " + userId +
                "  AND log._id NOT IN (" +
                "    SELECT _id" +
                "    FROM (" +
                "      SELECT _id" +
                "      FROM log" +
                "      WHERE user_id != " + userId +
                "      ORDER BY timestamp DESC" +
                "      LIMIT " + keepXLogsOfOthers +
                "    ) logs_of_others_to_keep" +
                ");";
db.execSql(del);

Don't forget to enable foreign keys:

db.execSQL("PRAGMA foreign_keys = ON;");

and I had the issue that the emulator did not cascade the log_activities.. but on a device it works. Thanks to the other answerers who gave me some hints.

See my question again for more details.

Answer 2

If you have access to the DB (creating a trigger) then would a SQL DELETE CASCADE not be an option?

ALTER TABLE log DROP CONSTRAINT aa
ALTER TABLE log ADD CONSTRAIN (FOREIGN KEY (log_id) REFERENCES log_activity ON DELETE CASCADE CONSTRAINT ab)

Then just run your normal JDBC delete statement using whichever clause you want.

Answer 3

You can create a trigger to do this automatically.

CREATE TRIGGER [delete_log_joins]
BEFORE DELETE
ON [log]
FOR EACH ROW
BEGIN
DELETE FROM log_activity WHERE log_activity.log_id = old.id;
END

For selecting deleting all but the latest 100 logs you can do something like this:

delete * from log where log.id not in (
  select id
  from (
    select l.id
    from log l
    where l.id in (
      select top 100 l2.id
      from log l2
      where l2.user_id = l.user_id
      order by log.timestamp desc
    )
  ) the_tops
);

I'm not sure how performant this is, maybe someone can improve it.

Answer 4

Depending on the relationship columns such as log_id and activity_id are setup.

if they are not_null, then you need to delete the rows in join table based on your where clause, then delete rows in log and activity tables.

if the are nullable, you will first set the log_id and activity_id values to null in the join table. Then delete the log and activity tables.