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?

You'll need to enable foreign key constraints. [See this](http://stackoverflow.com/questions/2545558/foreign-key-constraints-in-android-using-sqlite-on-delete-cascade/3266882#3266882) — Jacob Eggers, Jun 16 '11 at 16:06
Didn't I do that with db.execSQL("PRAGMA foreign_keys = ON;"); ?? — Stuck, Jun 16 '11 at 16:21
yeah. sorry missed that. Can you check, does ON DELETE **RESTRICT** do anything? (Also, did you add the records to activity somewhere? The foreign key there might prevent insertion.) — Jacob Eggers, Jun 16 '11 at 16:38
yes I added it to an activity which is inserted before. With RESTRICT I get a ERROR/Database(6958): Failure 19 (foreign key constraint failed) on 0x24f408 when executing 'DELETE FROM log WHERE user_id != 1 AND log._id NOT IN (... — Stuck, Jun 16 '11 at 17:32
OK dont know why, but debbugging it on my cellphone device does output the correct values. Whereas the emulator does not... — Stuck, Jun 16 '11 at 17:35
maybe it is an issue at platform 2.1-update1? because my emulator runs 2.1-update1 and my device 2.2.1... — Stuck, Jun 17 '11 at 00:46

Jacob Eggers · Answer 1 · 2011-06-15T23:31:25.303

1

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.

edited Jun 15 '11 at 23:31

answered Jun 15 '11 at 23:00

Jacob Eggers

9,062
2
25
43

I will try that.. but I wonder if this is efficiently and what the delete query is looking like to keep 100 rows. – Stuck Jun 15 '11 at 23:07
what is old.id? it does neither work with old._id nor with _id (the id field is `_id`) : ERROR/Database(312): Failure 1 (near "_id": syntax error) on 0x1350d8 when preparing 'CREATE TRIGGER [delete_log_activity_join] BEFORE DELETE ON [log] FOR EACH ROW BEGIN DELETE FROM log_activity WHERE log_activity.log_id = old._id'. – Stuck Jun 15 '11 at 23:25
try old.id instead of old._id. _old row from log that is being deleted_ – Jacob Eggers Jun 15 '11 at 23:35
FWIW: [performant: a performer](http://dictionary.reference.com/browse/performant) ;-) – Jun 15 '11 at 23:41
@Jacob Eggers: the field name is _id. But to be sure I tried with old.id: ERROR/AndroidRuntime(462): Caused by: android.database.sqlite.SQLiteException: near "id": syntax error: CREATE TRIGGER [delete_log_activity_join] BEFORE DELETE ON [log] FOR EACH ROW BEGIN DELETE FROM log_activity WHERE log_activity.log_id = old.id – Stuck Jun 15 '11 at 23:44
try plafond's delete cascade. that would be better anyway. – Jacob Eggers Jun 15 '11 at 23:47
the delete * from is almost okay.. some syntax-errors and I found it can be easilier done. But the foreign key constraint does not work. Please see my update of the question. – Stuck Jun 16 '11 at 10:51

score 1 · Answer 2 · answered Jun 15 '11 at 23:42

1

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.

answered Jun 15 '11 at 23:42

plafond

101
2

Good idea.. I want to do it with constraints, now. I searched a bit on the internet and tried several things with the cascading, but it is not working. Please see my update of the question. – Stuck Jun 16 '11 at 10:52
Not sure why you're getting the latest issue (different behaviour between phone/emulator). Watch out for your last Constraint though : "FOREIGN KEY(activity_id) REFERENCES activity(_id) ON DELETE CASCADE" - I thought you wanted the activity records NOT to be deleted?! – plafond Jun 16 '11 at 21:40
an activity will not be deleted if a log or log_activity gets deleted. The constraint says that if an activity gets deleted it will delete all log_activity rows which referenced this activity. – Stuck Jun 16 '11 at 23:36

score 0 · Answer 3 · answered Jun 15 '11 at 23:01

0

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.

answered Jun 15 '11 at 23:01

zbugs

591
2
10

setting them null would result in trash in the log_activity. Additionally.. I told that activity table shouldn't be changed... please review the question and your answer. Both, log_id and activity_id are not nullable, because its a join table. Thanks. – Stuck Jun 15 '11 at 23:04

score 0 · Accepted Answer · answered Jun 20 '11 at 19:06

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.

SQL delete from one table + a jointable?

4 Answers4