Гость
Войти | Профиль | Очистить
Действия ...
Форумы / SQLite [игнор отключен] [закрыт для гостей] / INSERT OR REPLACE + trigger / 5 сообщений из 5, страница 1 из 1
09.01.2016, 21:11
    #39143920
PPA
PPA
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
INSERT OR REPLACE + trigger
Привет.

Кто знает почему тут срабатывает 4 раза триггер на Insert?
ведь при последующих INSERT OR REPLACE insert-а не происходит
по идее должно быть или один Insert + 3 Update
или insert + последовательности delete+insert

Это баг?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
CREATE TABLE IF NOT EXISTS userinfo (nick VARCHAR(64) NOT NULL,last_updated DATETIME NOT NULL,ip_address VARCHAR(39) NOT NULL,share VARCHAR(24) NOT NULL,description VARCHAR(192),tag VARCHAR(192),connection VARCHAR(32),email VARC
AR(96),UNIQUE (nick COLLATE NOCASE));
CREATE TABLE IF NOT EXISTS userinfo_log(time_operation DATETIME, operation VARCHAR(1));
CREATE TRIGGER tr_u_userinfo BEFORE UPDATE ON userinfo FOR EACH ROW
BEGIN
   INSERT into userinfo_log(time_operation, operation) VALUES(DATETIME('now'),'U');
END;
CREATE TRIGGER tr_d_userinfo BEFORE DELETE ON userinfo FOR EACH ROW
BEGIN
   INSERT into userinfo_log(time_operation, operation) VALUES(DATETIME('now'),'D');
END;
CREATE TRIGGER tr_i_userinfo BEFORE INSERT ON userinfo FOR EACH ROW
BEGIN
   INSERT into userinfo_log(time_operation, operation) VALUES(DATETIME('now'),'I');
END;
INSERT OR REPLACE INTO userinfo (nick, last_updated, ip_address, share, description, tag, connection, email) VALUES('nick1',DATETIME('now'),'ip','share','description','tag','connection','email');
INSERT OR REPLACE INTO userinfo (nick, last_updated, ip_address, share, description, tag, connection, email) VALUES('nick1',DATETIME('now'),'ip','share','description','tag','connection','email');
INSERT OR REPLACE INTO userinfo (nick, last_updated, ip_address, share, description, tag, connection, email) VALUES('nick1',DATETIME('now'),'ip','share','description','tag','connection','email');
INSERT OR REPLACE INTO userinfo (nick, last_updated, ip_address, share, description, tag, connection, email) VALUES('nick1',DATETIME('now'),'ip','share','description','tag','connection','email');
select * from userinfo_log;
2016-01-09 18:00:04|I
2016-01-09 18:00:04|I
2016-01-09 18:00:05|I
2016-01-09 18:00:05|I
select * from userinfo;
nick1|2016-01-09 18:00:05|ip|share|description|tag|connection|email
update userinfo set ip_address = 'x';
select * from userinfo_log;
2016-01-09 18:00:04|I
2016-01-09 18:00:04|I
2016-01-09 18:00:05|I
2016-01-09 18:00:05|I
2016-01-09 18:00:05|U
delete from userinfo;
select * from userinfo_log;
2016-01-09 18:00:04|I
2016-01-09 18:00:04|I
2016-01-09 18:00:05|I
2016-01-09 18:00:05|I
2016-01-09 18:00:05|U
2016-01-09 18:00:05|D
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.01.2016, 22:03
    #39143939
VSVLAD
VSVLAD
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
INSERT OR REPLACE + trigger
Может это такая "фича"... в документации что-то пишут про (не могу грамотно перевести):

When the REPLACE conflict resolution strategy deletes rows in order to satisfy a constraint, delete triggers fire if and only if recursive triggers are enabled

типо если REPLACE для удовлетворения конфиктов, всё же удаляет строки, тогда, чтобы сработал триггер на удаление, должно быть включено использование "рекурсивных триггеров". Но что-то всё равно не то
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.01.2016, 22:06
    #39143940
PPA
PPA
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
INSERT OR REPLACE + trigger
хм. если поставить

PRAGMA recursive_triggers = true;
то ловится скрытая операция delete

1|2016-01-09 18:58:36|I
2|2016-01-09 18:58:36|I
3|2016-01-09 18:58:36|D
4|2016-01-09 18:58:36|I
5|2016-01-09 18:58:36|D
6|2016-01-09 18:58:36|I
7|2016-01-09 18:58:36|D

Получается sqlite команду INSERT OR REPLACE выполняет не эффективно (чуть позже проведу тесты на больших объемах)
т.е. сначала удаляет строку, а потом делает новую вставку
при delete ведь выполняется ребилд индексов?
почему он не делает update?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.01.2016, 22:06
    #39143941
VSVLAD
VSVLAD
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
INSERT OR REPLACE + trigger
+ The update hook is not invoked for rows that are deleted by the REPLACE conflict resolution strategy. Nor does REPLACE increment the change counter. The exceptional behaviors defined in this paragraph might change in a future release.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.01.2016, 13:05
    #39150518
PPA
PPA
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
INSERT OR REPLACE + trigger
Провел тест на таблице из 150 тыс записей.
INSERT OR REPLACE INTO userinfo - 15 сек
UPDATE userinfo set - 10 сек.

Код теста
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
				for (int j = 0; j < 2; ++j)
				{
					{
						sqlite3_connection l_DB;
						File::deleteFile("users.sqlite");
						File::copyFile("users-orig.sqlite", "users.sqlite");
						l_DB.open("users.sqlite");
						auto_ptr<sqlite3_command> l_load_all(new sqlite3_command(l_DB, "select nick from userinfo"));
						sqlite3_reader l_q = l_load_all.get()->executereader();
						std::vector<string> l_nick;
						l_nick.reserve(160000);
						while (l_q.read())
						{
							l_nick.push_back(l_q.getstring(0));
						}
						CFlySQLCommand l_sql;
						sqlite3_transaction l_trans(l_DB);
						{
							CFlyLockProfiler l_log;
							for (auto i = l_nick.cbegin(); i != l_nick.cend(); ++i)
							{
								switch (j)
								{
									case 1:
										l_sql.init(l_DB,
										           "INSERT OR REPLACE INTO userinfo (nick, last_updated, ip_address, share, description, tag, connection, email) VALUES(?,DATETIME('now'),'ip','share','description','tag','connection','email')");
										break;
									case 0:
										l_sql.init(l_DB,
										           "UPDATE userinfo set last_updated = DATETIME('now'), ip_address == 'ip', share = 'share', description = 'description', tag = 'tag', connection = 'connection', email = 'email' where nick = ?");
										break;
								}
								l_sql->bind(1, *i, SQLITE_STATIC);
								l_sql->executenonquery();
							}
							l_trans.commit();
							l_log.log("D:\\time.txt", j);
						}
					}
				}



К сожалению получается, если запись в таблице существует использовать INSERT OR REPLACE не эффективно.
пока заменил у себя критичные места на такой код

1. делаю update
2. считаю sqlite3_changes
3. если sqlite3_changes = 0 зову insert or replace
replace - костыль для защиты от вставки такой записи другим процессом

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
			m_update_last_ip.init(m_flySQLiteDB,
			                      "update user_db.user_info set last_ip=? where dic_hub=? and nick=?");
			m_update_last_ip->bind(1, __int64(p_last_ip.to_ulong()));
			m_update_last_ip->bind(2, __int64(p_hub_id));
			m_update_last_ip->bind(3, p_nick, SQLITE_STATIC);
			m_update_last_ip->executenonquery();
			if (m_update_last_ip.sqlite3_changes() == 0)
			{
				m_insert_last_ip.init(m_flySQLiteDB,
				                      "insert or replace into user_db.user_info(nick,dic_hub,last_ip) values(?,?,?)");
				m_insert_last_ip->bind(1, p_nick, SQLITE_STATIC);
				m_insert_last_ip->bind(2, __int64(p_hub_id));
				m_insert_last_ip->bind(3, __int64(p_last_ip.to_ulong()));
				m_insert_last_ip->executenonquery();
			}



У кого будут другие идеи - как сделать красивше?

В идеале очень хотелось-бы получить от sqlite нативную команду "INSERT OR UPDATE" или "MERGE"
которая не делает этот лишний Delete и всю модификацию выполняет атомарно
без побочных эффектов в много поточной среде...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / SQLite [игнор отключен] [закрыт для гостей] / INSERT OR REPLACE + trigger / 5 сообщений из 5, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 151ms
Закрыть
start [/forum/topic.php?fid=54&mobile=1&tid=2008628]:
 0ms
get settings:
 8ms
get forum list:
 12ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 49ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 43ms
get tp. blocked users:
 2ms
others: 14ms
total:  151ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]