ReSQL.ru
     
powered by simpleCommunicator - 2.0.37     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / SQLite [игнор отключен] [закрыт для гостей] / INSERT OR REPLACE + trigger
5 сообщений из 5, страница 1 из 1
INSERT OR REPLACE + trigger
    #39143920
Фотография PPA
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PPA
Участник
Привет.

Кто знает почему тут срабатывает 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, 21:11
| Ответить | Цитировать | Написать  
INSERT OR REPLACE + trigger
    #39143939
Фотография VSVLAD
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VSVLAD
Участник
Может это такая "фича"... в документации что-то пишут про (не могу грамотно перевести):

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:03
| Ответить | Цитировать | Написать  
INSERT OR REPLACE + trigger
    #39143940
Фотография PPA
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PPA
Участник
хм. если поставить

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
| Ответить | Цитировать | Написать  
INSERT OR REPLACE + trigger
    #39143941
Фотография VSVLAD
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VSVLAD
Участник
+ 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
09.01.2016, 22:06
| Ответить | Цитировать | Написать  
INSERT OR REPLACE + trigger
    #39150518
Фотография PPA
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PPA
Участник
Провел тест на таблице из 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
19.01.2016, 13:05
| Ответить | Цитировать | Написать  
5 сообщений из 5, страница 1 из 1
Форумы / SQLite [игнор отключен] [закрыт для гостей] / INSERT OR REPLACE + trigger
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=54&msg=39143939&tid=2008628]:
 0ms
get settings:
 12ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 46ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 48ms
get tp. blocked users:
 2ms
others: 13ms
total:  157ms
созд. / загр.: 157ms
x
x
Закрыть


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