Second CUBRID competition. Search for a solution

Many probably heard that the open source project CUBRID decided to organize a contest, and since the time for submitting work has already ended, I will tell you about how I solved the competition task, which method I used and what features DBMS CUBRID encountered.

Assignment (approximate)

A database is given, which consists of tables containing strictly defined types of columns:

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

It is required to find the most frequently encountered non-numerical value in the database (one that consists not only of numbers) and the number of uses. The answer must be written to the results table. And that’s it (in short, read in more detail on the contest page).

Task analysis

A solution will be able to stand out from the rest only if it is faster and less demanding on RAM (there can be a lot of + values). Therefore, I entrusted all the maintenance of statistics with the sub-table itself using a temporary table. And I decided to update the counters with the help of the prefix on dublicate key update key update "count"="count"+1for all insert requests. I did not use hashing, since tests showed that it was not useful (most likely, hashing for string keys is already used in CUBRID). Multithreading will also have no effect - at one point in time only one process can change the data in the table, while others have to wait (you can remove the lock, but data integrity will be violated).

Decision

The following solution comes to mind:

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

It remains to implement only 4 functions:

1. init- a function that creates a temporary table.
   Sample code:
   

   
   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- a function that receives a list of tables in the current database.
   Since CUBRID is similar to MySql, the first thing you can look at is SHOW TABLESone that is not supported by version 8.3.1 (alas, the documentation has little to do with getting a list of tables in the database), so we all go to the Cubrid WebQuery project page and study the code. For the most impatient, I immediately quote the required sql query:
   

   	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;

   
   And use it in our code.
   
   You can use the function cubrid_schema, but, there you still need to filter the type of classes and do extra. processing.
3. process_tables- the most interesting function in the whole solution, which accordingly makes statistics.
   
   There are many possible implementation options:
   
   • select * + setFetchSize/cubrid_unbuffered_query
     As my tests showed (anyone interested - the java solution is also contained in the archive at the bottom of the post) is not the fastest solution.
   • select *+ condition with calling the stored function in java type:
     where "int"<0 and counter("int") or length(translate("str",'0123456789',''))>0 and counter("str")
     where it counterstores the value in the database and returns false.
     However, such a request simply led to the freezing of my system (apparently in CUBRID the functions in java were not completely honed).
   • insert from select ... on dublicate key update "count"="count"+1
     It is this option that I position as the main one (though in view of errors in the php driver, I had to come up with my crutches when processing double values).
   
   Now we need to somehow get a list of columns and their types, since it is obvious that for each type of column its own processing can take place:
   
   • For a type integer(or decimal without decimal places), a sign check is sufficient.
     
   • For all string values, remove all digits and then check the length of the string.
     
   • For all other values, no checks are needed.
   
   The question also arises - in what format to convert the values ​​to a string. After much discussion on the forum, I came to the conclusion that the most ideal option is to use the same format as in cubrid manager (when it shows the result of the select request).
   
   Since the same type of verification can be applied to different types of columns (+ different names for the same data type), I put all the conditions and “aliases” into separate arrays:
   

   
   $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))',
   	)
   );
   

   
   And finally, the final cycle:
   

   
   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- saves the result and deletes the temporary table.
   In order not to overload the article, I quote only the sql-request to save the result
   

   
   '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'
   

   
   It is used replacebecause during testing the code is run many times, and in the case of insert, an error will occur for the same set of test data.

conclusions

Although I sent a solution that was not completely finished up to my mind (there was a problem with char and the name of the temporary table was not random, there was also no check for null values) and, at the very last moment, during this time I figured out the cubrid performance, mastered java at an initial level, practiced in English and gained a lot of valuable experience. Using an experimental method, I found out that CUBRID has a very fast query interpreter, works very quickly with tables, but it lacks support for temporary tables in RAM and good documentation (now this looks more like a brief help). Since CUBRID turned out to be a very productive DBMS, in the future (when some of the errors I found are corrected), I will use it in high-load projects.

References

1. Curid it - contest page.
2. Cubrid it forum - talk page.
3. Cubrid Webquery - an analogue of cubrid manager, contains useful sql queries.
4. Cubrid solution - my solution, fixed (java + php + code to populate the test database).

PS: This is my first article on the hub, I tried to take into account all the rules (but if something is wrong - write, I will correct it, I will learn from mistakes). I decided to transfer the topic to the sql blog (it will not work in the cubrid company blog), since there it belongs.
PS2: Habrauser from the company cubrid advised a suitable blog. Moved there.
PS3: Just Habré issued winner solution - look