Ekstazi Jul 8 2011 at 22:34

Второе соревнование по CUBRID. Поиск решения

6 min

1.3K

Многие наверняка слышали, что open source проект CUBRID решил устроить конкурс, а так как время сдачи работ уже завершилось, я расскажу вам о том как решал конкурсное задание, какой способ использовал и с какими особенностями СУБД CUBRID столкнулся.

Задание (примерное)

Дана БД, которая состоит из таблиц, содержащих строго определенные типы столбцов:

VARCHAR, CHAR, STRING, INT, SMALLINT, BIGINT, NUMERIC, FLOAT, DOUBLE, DATE, TIME, DATETIME и TIMESTAMP.

Требуется найти наиболее часто встречаемое в БД не числовое значение (то, которое состоит не только из цифр) и число его использований. Ответ надо записать в таблицу results. И все (это вкратце, более подробно читайте на странице конкурса).

Анализ задачи

Решение сможет выделиться на фоне остальных только если оно будет быстрее и менее требовательно к оперативной памяти (+значений может быть очень много). Поэтому все ведение статистики я поручил самой субд с использованием временной таблицы. А счетчики решил обновлять с помощью приставки on dublicate key update key update "count"="count"+1 для всех insert-запросов. Хеширование я не использовал, так как тесты показали что от него нет пользы (скорей всего в CUBRID уже используется хеширование для строковых ключей). Многопоточность тоже не даст эффекта — в один момент времени только один процесс может менять данные в таблице, а другим приходится ждать (можно убрать блокировку, но, нарушится целостность данных).

Решение

В голову приходит примерно следующее решение:

init();
foreach(list_tables() as $table){
	process_table($table);
}
save_result();

Осталось реализовать всего 4 функции:

init — функция, которая создает временную таблицу.
Примерный код:


global $tempTableName,$conn;
$tempTableName='"temp'.rand(0,32767).'"';
$q='CREATE TABLE '.$tempTableName.'(
	"val" character varying(255) NOT NULL,
	"count" bigint DEFAULT 1 NOT NULL,
	CONSTRAINT pk_temp_hash_val PRIMARY KEY("val")
)';
cubrid_execute($conn,$q);

list_tables — функция, которая получает список таблиц в текущей БД.
Так как CUBRID похож на MySql, то первым делом можно глянуть в сторону SHOW TABLES, который однако не поддерживается версией 8.3.1 (увы, в документации мало чего есть про получение списка таблиц в бд), поэтому все дружно идем на страницу проекта Cubrid WebQuery и изучаем код. Для самых нетерпеливых сразу привожу нужный sql-запрос:
```
	select class_name as table_name
	from db_class
	where class_type='CLASS' and is_system_class='NO' and class_name!='results'
	order by a asc;
```
И используем его в нашем коде.

Можно использовать функцию cubrid_schema, но, там еще надо фильтровать тип классов и делать доп. обработку.
process_tables — самая интересная функция во всем решении, которая соответственно делает статистику.

Тут возможно много вариантов реализации:
- select * + setFetchSize/cubrid_unbuffered_query
  Как показали мои тесты (кому интересно — решение на java так же содержится в архиве внизу поста) — не самый быстрый вариант решения.
- select * + условие с вызовом хранимой функции на java типа:
  where "int"<0 and counter("int") or length(translate("str",'0123456789',''))>0 and counter("str")
  где counter сохраняет значение в бд и возвращает false.
  Однако, такой запрос просто привел к зависанию моей системы (по всей видимости в CUBRID не до конца отточены функции на java).
- insert from select ... on dublicate key update "count"="count"+1
  Именно этот вариант я и позиционирую как основной (правда в виду ошибок в php драйвере при обработке double значений пришлось придумать свои костыли).
Теперь надо как-то получить список столбцов и их типы, так как очевидно что для каждого типа столбцов могут иметь место свои обработки:
- Для типа integer (или decimal без знаков после запятой) достаточно проверки на знак.
- Для всех строковых значений необходимо удалить все цифры и после этого проверить длину строки.
- Для всех остальных значений никаких проверок использовать не нужно.
Так же возникает вопрос — в каком формате преобразовать значения в строку. После долгих обсуждений на форуме я пришел к выводу что самый идеальный вариант — использовать тот же формат что и в cubrid manager (когда тот показывает результат select-запроса).

Так как один и тот же тип проверки может быть применен к разным типам столбцов (+еще разные названия у одного типа данных), я вынес все условия и «альясы» в отдельные массивы:
```
$aliases=array(
	"STRING"=>"VARCHAR",
	'CHAR'=>'VARCHAR',
	
	"INT"=>'INTEGER',
	"SHORT"=>'INTEGER',
	'SMALLINT'=>'INTEGER',
	'BIGINT'=>'INTEGER',
	
	'DECIMAL'=>'NUMERIC',
	'DEC'=>'NUMERIC',
		
	"REAL"=>'FLOAT',
	"DOUBLE PRECISION"=>'DOUBLE',
);
// column process criteria and/or format
$handlers=array(
	'VARCHAR'=>array(
		'select'=>'cast(%s as varchar(255))',
		'criteria'=>"length(translate(%s,'0123456789',''))>0",
	),
	'INTEGER'=>array(
		'select'=>'cast(%s as varchar(255))',
		'criteria'=>"%s<0"
	),
	'FLOAT'=>array(
		'select'=>"to_char(%s,'9.999999EEee')",
	),
	'DOUBLE'=>array(
		'select'=>"to_char([double],'9.9999999999999999EEee')",
	),
	'DATE'=>array(
		'select'=>"TO_CHAR(%s,'YYYY-MM-DD')",
	),
	'TIME'=>array(
		'select'=>"TO_CHAR(%s,'HH24:MI:SS')",
	),
	'DATETIME'=>array(
		'select'=>"to_char(%s,'YYYY-MM-DD HH24:MI:SS.FF')",
	),
	'TIMESTAMP'=>array(
		'select'=>"to_char(%s,'YYYY-MM-DD HH24:MI:SS')",
	),
	'DEFAULT'=>array(
		'select'=>'cast(%s as varchar(255))',
	)
);
```
Ну и, наконец, финальный цикл:
```
foreach(get_columns($q) as $column){
	//echo "\tProcess column:".$column['column']."\n";
	while(isset(self::$aliases[$column['type']])) $column['type']=self::$aliases[$column['type']];
	// If column is decimal and has no precision then convert type to integer
	if($column['type']==='NUMERIC' && $column['scale']===0)
		$column['type']='INTEGER';
				
	$criteria=(isset(self::$handlers[$column['type']])) ?
		self::$handlers[$column['type']]:
		self::$handlers["DEFAULT"];
				
	$toSelect=(isset($criteria['select'])) ? $criteria['select'] : '%s';

	// Depending of the column type build appropiate criteria
	$q='insert into '.$tempTableName.' ("hash","val") '.
		'SELECT 1,'.$toSelect.' '.
		'FROM `'.$qtable.'` '.
		'where %s is not null and '.$criteria.' '.
		'ON DUPLICATE KEY UPDATE "count"="count"+1';
		
	$q=str_replace('%s','`'.$this->escape($column['column']).'`',$q);

	cubrid_execute($q);
}
```
save_result — сохраняет результат и удаляет временную таблицу
Чтобы не перегружать статью привожу только sql-запрос для сохранения результата
```
'replace into results ("userid","most_duplicated_value","total_occurrences") 
select \''.cubrid_real_escape_string($userid).'\',val,"count" 
from "'.$tempTableName.'"
order by "count" desc 
limit 1'
```
Используется replace так как при тестировании код прогоняется много раз, и в случае с insert-ом будет возникать ошибка для одного и того же набора проверочных данных.

Выводы

Хотя я отправил и не совсем допиленое до ума решение (была проблемка с char и название временой таблицы не было случайным, так же не было проверки на null значения) и, в самый последний момент, за это время я разобрался в производительности cubrid, освоил java на начальном уровне, попрактиковался в английском и получил много ценого опыта. Так же эксперементальным методом я выяснил, что CUBRID имеет очень быстрый интерпретатор запросов, очень быстро работает с таблицами, но ему не хватает поддержки временых таблиц в оперативной памяти и хорошей документации (сейчас это больше похоже на краткую справку). Так как CUBRID оказалась очень производительной СУБД, то в будущем (когда поправят часть найденых мной ошибок), в высоконагруженных проектах я буду использовать имено её.

Ссылки

Curid it — страница конкурса.
Cubrid it forum — страница обсуждений.
Cubrid Webquery — аналог cubrid manager, содержит полезные sql-запросы.
Cubrid solution — мое решение, исправленное (java+php+код для заполнения тестовой БД).

P.S.: Это моя первая статья на хабре, постарался учесть все правила (но если что-то не так — пишите, поправлю, буду учиться на ошибках). Решил перенести топик в блог sql (в блог компании cubrid не получится), так как там ему и место.
P.S.2: Хабраюзер из компании cubrid посоветовал подходящий блог. Перенес туда.
P.S.3: Так же на хабре опубликовано решение победителя конкурса — смотрим

Tags:

Hubs:

Cubrid