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

Cubrid
Многие наверняка слышали, что 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 функции:
  1. 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);
  2. 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, но, там еще надо фильтровать тип классов и делать доп. обработку.
  3. 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);
    }
    

  4. 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 оказалась очень производительной СУБД, то в будущем (когда поправят часть найденых мной ошибок), в высоконагруженных проектах я буду использовать имено её.

Ссылки


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


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

Похожие публикации

Профессия Java-разработчик
2 февраля 202182 500 ₽SkillFactory
Cloud Solution Architecture
25 февраля 202144 000 ₽OTUS
MS SQL Server Developer
10 марта 202135 000 ₽OTUS
Java Developer. Professional
11 марта 202160 000 ₽OTUS
Java QA Engineer
16 марта 202160 000 ₽OTUS

Лучшие публикации за сутки