воскресенье, 12 июля 2009 г.

Расширение UNDO для СУБД SQLite

Недавно Simon Naunton анонсировал расширение для SQLite, реализующие функционал undo/redo. Описание принципа работы соответствующего функционала известно давно и может быть найдено на страничке
http://www.sqlite.org/cvstrac/wiki?p=UndoRedo

Модуль предоставляет возможность отменять один запрос или набор запросов и повторять их после отмены. Таким образом, программирование действий "Отменить" и "Повторить" в приложении существенно упрощается.

Непосредственно реализация мне понравилась, хотя и содержала неекоторые странности - например, привязку к primary keys вместо использования rowid и проч. К счастью, автор быстро откликается на пожелания и предложения и уже подготовил новый релиз, список изменений можно посмотреть в файле ChangeLog архива исходного кода.

Остается еще несколько вещей, которые мне сильно не нравятся. В том числе:
- неявный вызов транзакции в функциях - требует переписывания существующих приложений без использования транзакций (sic!),
- функция undoable_table требует "лишний" параметр, который принципиально вообще не нужен, а на практике бесполезен и при неправильном использовании даже вреден (из-за возможности создания огромного числа триггеров на многоколоночных таблицах)
- функция undoable для выполнение SQL-строки - явно следует ее вынести на уровень приложения.

Не уверен, примет ли автор соответствующий патч - пока аргументирует отказ тем, что текущий вариант ему удобен. Автору-то, может быть, и удобно, если он писал приложения именно под свое расширение, а вот для новых или уже существующих приложений выкинуть транзакции явно неприемлемо. Посему выкладываю в репозиторий свою версию расширения, независимо от того, войдут ли эти изменения в апстрим.

Моя аргументация на данный момент выглядит следующим образом:

1. I think that existing application logic must be always saved. So I did remove hidden calls of BEGIN and COMMIT from sqlite_undo_undoable_begin_do and sqlite_undo_undoable_end functions.
Now I can using savepoints as nested transactions and all functionality of extension is saved.

2. I did remove second parameter from undoable_table('x', y) because for temp undo/redo stack size of undo log is not critical but a lot of triggers is not good solution by performance reasons.
Param value '0' is strange because is not usefull and doesn't perform any optimization really.

3. undoable('x') can be replaced by
select undoable_begin();
select perform ('x');
select undoable_end();

Where "perform" is your application function for evaluating SQL strings. I don't understand why you are include this functionality into public extension. I think more better to create application
function "undoable(x)" as described above. Of cource this is IMHO but more simple extension with same functionality is best.

Please remember that SQLite slogan is "Small. Fast. Reliable.Choose any three."! I think undo extension with these simplifications is more corresponded to this title. Patched file is attached.


Пример использования:

sqlite> CREATE TABLE test(name text, title text);
sqlite> select undoable_table('test');

sqlite> select undoable_begin();

sqlite> insert into test values ('master','admin');
sqlite> insert into test values ('veter','developer');
sqlite> select undoable_end();
UNDO=1
REDO=0
sqlite> select undo();
UNDO=0
REDO=1
SQL=DELETE FROM test WHERE rowid=1;DELETE FROM test WHERE rowid=2


Тот же пример, где добавлен вывод undo/redo информации из служебной таблицы _undo :

sqlite> CREATE TABLE test(name text, title text);
sqlite> select undoable_table('test');

sqlite> select undoable_begin();

sqlite> insert into test values ('master','admin');
sqlite> insert into test values ('veter','developer');
sqlite> select undoable_end();
UNDO=1
REDO=0

sqlite> select * from _undo;
U
DELETE FROM test WHERE rowid=1
DELETE FROM test WHERE rowid=2

sqlite> select undo();
UNDO=0
REDO=1
SQL=DELETE FROM test WHERE rowid=1;DELETE FROM test WHERE rowid=2

sqlite> select * from _undo;
R
INSERT INTO test(rowid,name,title) VALUES(1,'master','admin')
INSERT INTO test(rowid,name,title) VALUES(2,'veter','developer')


Настройки моего репозитория вы можете найти на следующей странице
http://mobigroup.ru/page/debian
или вручную скачать оригинальные исходники SQLite и патч-файл с моими изменениями здесь:
http://mobigroup.ru/debian/pool/main/s/sqlite3/

P.S. Аналогично можно реализовать ведение истории изменений БД. Приведу обсуждение этой возможности, вдруг кто из читателей захочет реализовать такой модуль. Впрочем, если будет время, постараюсь сам наконец добраться и сделать, поскольку иногда хочется иметь такой функционал.

> "How about changes logging extension by same way? ;-)"
>
> Do you mean having a _log table where all updates to the table are logged?
>
> e.g.
>
> create myloggedtable(data text);
> select
> logged_table('myloggedtable');
> insert into myloggedtable(data) ('test');
> insert into myloggedtable(data) ('test2');
> select rowid,* from _log
> > 1, INSERT INTO test(data) VALUES('test');
> > 2, INSERT INTO test(data) VALUES('test2');

Yes, but _log table must have table_name, table_rowid, save_date and
operation_name columns and consists _only insert_ commands. For history
review we may create temp table same as original and populate it with
all logged versions of data of original table or of the single row of this
table. Values of operation_name are insert|update|delete. Log records
for delete sql commands may have no sql. Thus _log table will let to
restore state of any database record of any time.

четверг, 9 июля 2009 г.

Новая книга по СУБД SQLite - "The SQL Guide to SQLite"

Название книги "The SQL Guide to SQLite", автор Rick F. van der Lans. Продается здесь (можно просмотреть оглавление): http://www.lulu.com/ Скоро появится в продаже и на Amazon.

Самого издания у меня пока нет, но по той версии, что готовилась в печать, могу сказать, что читается легко, сложных английских оборотов и редко встречаемых слов я не заметил. Книга ориентирована непосредственно на пользователей, в т.ч. php-программистов.

Если есть вопросы, можете обращаться ко мне, постараюсь ответить или уточню у автора.


SQLite build with some extra modules you can get from my debian repository:
deb http://mobigroup.ru/debian/ lenny main contrib non-free
deb-src http://mobigroup.ru/debian/ lenny main contrib non-free

Install repository keyring as
sudo aptitude install debian-mobigroup-keyring

Also you can get sources directly from
http://mobigroup.ru/debian/pool/main/s/sqlite3/
The patch file for current SQLite release is
http://mobigroup.ru/debian/pool/main/s/sqlite3/sqlite3_3.6.16-2.diff.gz


Upd.

Получил от автора бумажный вариант, оформлен даже более красиво, чем я ожидал. Язык простой, в словарь лезть не нужно. Во введении обнаружился довольно занимательный исторический экскурс.

суббота, 4 июля 2009 г.

Счетчики в SQLite

Недавно ко мне обратился прикладной программист, работающий с моим фрэймворком для AOLServer и SQLite с вопросом, как ему сделать счетчик записей, возвращенных запросом. Разумеется, можно создать временную таблицу или забиндить тиклевскую функцию, но есть и более простой способ - использовать написанный мною модуль key или tempkey (отличаются только тем, что последний использует временную таблицу, таким образом, при хранении временных объектов в ОЗУ не требуется запись на диск). Модуль key создает при инициализации таблицу keys (а модуль tempkey - временную таблицу tempkeys), в которой и будут храниться все ключи; таким образом, можно использовать практически неограниченное количество ключей без ущерба для производительности и не нуждаясь в большом количестве ОЗУ.

Рассмотрим на примере неименованного временного счетчика:

create table colors(name text);
insert into colors values ('Red');
insert into colors values ('Green');
insert into colors values ('Blue');

select tempkey_install();
select tempkey_increment('','',1) as counter, name from colors;
1|Red
2|Green
3|Blue

select tempkey_get('','');
3

--select tempkey_delete('','');
select tempkey_uninstall();


Первым аргументом функции ключей принимают имя группы ключей (unit), а вторым - имя самого ключа. Если нам нужен временный счетчик, можно имя группы и имя ключа вовсе не указывать, работая с неименованным ключом.

По завершению обработки в качестве бонуса мы можем узнать количество строк, обработанных запросом, с помощью функции tempkey_get.

Вызовом tempkey_delete можно удалить указанный ключ, а tempkey_uninstall удалит все ключи (по техническим причинам таблица с ключами не удаляется, чистится только ее содержимое).

Как видите, используя именованные или неименованные ключи, мы получаем возможность работать со счетчиками в запросах и сохранять результаты между запросами.

Для справки приведу свой сценарий для тестирования модуля "key":

select 'ERROR' where key_install() is not null; --Create "keys" table.
select 'ERROR' where key_exists('common','nds%')!=0; --Check is param exists
select 'ERROR' where key_get('common','nds%') is not null; --Get _non-existing_ value _without_ default_value
select 'ERROR' where key_get('common','nds%',18)!='18'; --Get _non-existing_ value _with_ default_value
select 'ERROR' where key_set('common','nds%',10)!='10'; --Set value
select 'ERROR' where key_get('common','nds%')!='10'; --Get _existing_ value
select 'ERROR' where key_add('common','nds%',18) is not null; --Add _existing_ key
select 'ERROR' where key_replace('common','nds%',20)!='20'; --Replace _existing_ key
select 'ERROR' where key_delete('common','nds%')!=1; --Delete existing key
select 'ERROR' where key_delete('common','nds%') is not null; --Delete non-existing key
select 'ERROR' where key_delete('common') is not null; --Delete all keys in unit
select 'ERROR' where key_replace('common','nds%',20) is not null; --Replace _non-existing_ key
select 'ERROR' where key_add('common','nds%',18)!='18'; --Add _non-existing_ key
select 'ERROR' where key_increment('common','num',10)!='10'; --Increment _non-existing_ value
select 'ERROR' where key_increment('common','num',10)!='20'; --Increment _existing_ value
select 'ERROR' where key_prepend('common','string','prepend-')!='prepend-'; --Prepend value
select 'ERROR' where key_append('common','string','-append')!='prepend--append'; --Append value
select 'ERROR' where key_flush('common','string')!=1; --Flush existing key
select 'ERROR' where key_delete('common','string') is not null; --Flush non-existing key
select 'ERROR' where key_uninstall() is not null; -- Drop all keys from "keys" table.


Вышеприведенный набор SQL-запросов демонстрирует все возможности модуля "key".

Upd

См. Ticket 4004: Implement a "counter" SQL function. Разумеется, для подсчета строк в запросе функция counter() намного более эффективна, нежели использование дополнительного модуля. Патч к вышеназванному тикету приложен мной, после добавления функции в мою сборку.

Таким образом, теперь можно использовать модуль key по назначению - передача параметров между разными запросами и/или подключениями.

(C) Alexey Pechnikov aka MBG, mobigroup.ru