Второе соревнование по 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: Так же на хабре опубликовано решение победителя конкурса — смотрим
    Поделиться публикацией

    Комментарии 1

      –2
      Сперва я хотел опубликовать статью в своем блоге на blogspot, но, потом из-за проблем с оформлением я перенес на хабр. Надеюсь, гугл нормально отреагирует на это и не будет считать статью рерайтом.

      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

      Самое читаемое