Geek Answers Handbook

SQL deletes + compatible from one table?

I have three tables: log, activityand the combined (many2many) log_activity(a log_idand activity_id+ additional information data in columns).

I want to remove from logand log_activity.

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

I also want to remove from compatible log_activityall entries related to the logs that are being deleted. The table activityshould be affected not .

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

So can someone come up with an effective solution?

UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATES

Inspired by the answers of Jacob Eggers and the ceiling and further research, I try so hard now, but it still does not work:

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 that the DELETE operation is on its own (I also verified that the correct lines were deleted, which solves the first problem !!), but ON DELETE CASCADE does not work ... why?

+5
source share
4 answers

The problem was solved using a foreign key in the log_activity table as follows:

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

and the delete operator, for example:

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);

Remember to enable foreign keys:

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

, log_activities.. . , .

. .

0

, .

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

, 100 , - :

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
);

, , , - .

+1

( ), SQL DELETE CASCADE ?

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

JDBC delete, , .

+1

, log_id activity_id, .

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

if they are null, you first set the log_id and activity_id to null in the connection table. Then delete the log and activity tables.

0
source

More articles:


All Articles