Script to generate GreenDao entities from a MySQL procedure

_MySQL2GreenDao.sql

CALL fetchTables('from-this-database');

fetch-tables.sql

DROP PROCEDURE IF EXISTS `fetchTables`;
DELIMITER $$
CREATE PROCEDURE `fetchTables`(IN pDatabase VARCHAR(255))
BEGIN 

	DECLARE v_finished INTEGER DEFAULT 0;
    DECLARE vTableName VARCHAR(255);
    DECLARE vText MEDIUMTEXT DEFAULT '';
    DECLARE vCode MEDIUMTEXT DEFAULT '';
	DECLARE vCursor CURSOR FOR (SELECT table_name FROM information_schema.tables WHERE table_schema = pDatabase);
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
    
	OPEN vCursor;
	get_code: 
		LOOP FETCH vCursor INTO vTableName;
	  
		IF v_finished = 1 THEN LEAVE get_code;
		END IF;
		select  CONCAT(vText, genEntities(vTableName, pDatabase),'\n') INTO vText;
        
	END LOOP get_code;
    CLOSE vCursor;
    
    SET v_finished = 0;
	OPEN vCursor;
	get_code: 
		LOOP FETCH vCursor INTO vTableName;
	  
		IF v_finished = 1 THEN LEAVE get_code;
		END IF;
		select  CONCAT(vText, genRelations(vTableName, pDatabase),'\n') INTO vText;
        
	END LOOP get_code;
    CLOSE vCursor;
    
    SELECT vText;
END;

gen-entities.sql

DROP FUNCTION IF EXISTS genEntities;
DELIMITER 
CREATE FUNCTION genEntities(pTableName VARCHAR(255), pDatabase VARCHAR(255))
RETURNS TEXT
BEGIN

DECLARE vFinished INTEGER DEFAULT 0;

-- for properties fetch
DECLARE v_name VARCHAR(1024);
DECLARE v_type VARCHAR(1024);
DECLARE v_is_null VARCHAR(1024);
DECLARE v_extra VARCHAR(1024);
DECLARE v_key VARCHAR(1024);

DECLARE vPropCursor CURSOR FOR 
SELECT 
	COLUMN_NAME c,
	JTYPE(DATA_TYPE) AS ctype,
	IS_NULLABLE cnull, 
    EXTRA cextra, 
    COLUMN_KEY ckey
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = pTableName AND TABLE_SCHEMA = pDatabase
		AND COLUMN_NAME NOT IN
		(SELECT K.COLUMN_NAME c -- avoid fk attributes
		   FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
		  WHERE K.TABLE_NAME = pTableName AND K.REFERENCED_TABLE_NAME IS NOT NULL);
  
DECLARE CONTINUE HANDLER FOR NOT FOUND SET vFinished = 1;

	SET @class = UC_DELIMETER(pTableName, '_', TRUE,TRUE);-- LCASE(pTableName);
    SET @var = DECAPITALIZE(@class);
	SET @entity = CONCAT('Entity ',@var,' = schema.addEntity("',@class,'DB");\n');
	SET @entity = CONCAT(@entity, @var, '.setTableName(DaoUtil.dbName("',@class,'"));\n');

	SET @prop = '';
	-- SET @prop = CONCAT(@prop, @var,'.implementsInterface("Parcelable");\n');
	-- SET @prop = CONCAT(@prop, @var,'.implementsSerializable();\n');
	SET @prop = CONCAT(@prop, @var,'.addIdProperty().autoincrement();\n');-- sqlite required
    SET vFinished = 0;
    
    OPEN vPropCursor;
	get_prop: 
		LOOP FETCH vPropCursor INTO v_name, v_type, v_is_null, v_extra, v_key;
	  
		IF vFinished = 1 THEN 
			LEAVE get_prop; 
        END IF;
        IF v_name != 'id' THEN
			SET @prop = CONCAT(@prop, @var,'.add', v_type,'Property("', v_name,'")');
			
            IF v_is_null != 'YES' THEN
				SET @prop = CONCAT(@prop,'.notNull()');
			END IF;
            
			SET @prop = CONCAT(@prop,';\n');
            -- SELECT @class;
		END IF;
	  
	END LOOP get_prop;
	CLOSE vPropCursor;
    
	RETURN concat(@entity,@prop);-- ,vRel);
END;

gen-relations.sql

DROP FUNCTION IF EXISTS genRelations;
DELIMITER $$
CREATE FUNCTION genRelations(pTableName VARCHAR(255), pDatabase VARCHAR(255))
RETURNS TEXT
BEGIN
DECLARE vFinished INTEGER DEFAULT 0;

-- for relations fetch
DECLARE v_prop TEXT;
DECLARE v_one TEXT;
DECLARE v_many TEXT;

DECLARE vRelCursor CURSOR FOR
(SELECT 
		CONCAT('Property ',JVAR(tref),'To',JCLASS(t),' = ',JVAR(t),'.add',
			(CASE ctype WHEN 'Int' THEN 'Long' ELSE ctype END)/* Id issue on mysql (Id as Long) */,'Property("',c,'")',
			(CASE cnull WHEN 'NO' THEN '.notNull()' ELSE '' END),'.getProperty();\n') as prop, 
		CONCAT(JVAR(t),'.addToOne(',JVAR(tref),',',JVAR(tref),'To',JCLASS(t),');\n') as toOne,
		CONCAT(JVAR(tref),'.addToMany(',JVAR(t),',',JVAR(tref),'To',JCLASS(t),');\n') as toMany
        -- ,tref, cref, t, c, ctype
	FROM ( SELECT 
				K.TABLE_NAME t, 
				K.COLUMN_NAME c, 
				K.REFERENCED_TABLE_NAME tref, 
				K.REFERENCED_COLUMN_NAME cref, 
				C.IS_NULLABLE cnull, 
				JTYPE(C.DATA_TYPE) ctype
				-- C.COLUMN_KEY, 
				-- C.EXTRA
			FROM 	
				INFORMATION_SCHEMA.KEY_COLUMN_USAGE K  
				INNER JOIN 
				INFORMATION_SCHEMA.COLUMNS C 
				ON K.TABLE_NAME = C.TABLE_NAME AND K.COLUMN_NAME = C.COLUMN_NAME 
			WHERE K.REFERENCED_TABLE_NAME = pTableName AND K.TABLE_SCHEMA = pDatabase) A
);
 
    
DECLARE CONTINUE HANDLER FOR NOT FOUND SET vFinished = 1;

	SET @class = UC_DELIMETER(pTableName, '_', TRUE,TRUE);
    SET @var = DECAPITALIZE(@class);
    
	SET @relcode = CONCAT('\t/*********** Relations pour ', @class, ' table ***********/\n');
    SET vFinished = 0;
    
    OPEN vRelCursor;
	get_rel: LOOP FETCH vRelCursor INTO v_prop, v_one, v_many;
		IF vFinished = 1 THEN 
			LEAVE get_rel; 
        END IF;

		SET @relcode = CONCAT(@relcode, v_prop, v_one, v_many,'\n');
	  
	END LOOP get_rel;
	CLOSE vRelCursor;

	RETURN @relcode;
END;

util_scripts.sql

DROP FUNCTION IF EXISTS SPLIT_STR;
CREATE FUNCTION SPLIT_STR(x TEXT, delim TEXT, pos INT)
RETURNS TEXT
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),delim, '');
       
-- compte le nombre des ocurrences de v dans x
DROP FUNCTION IF EXISTS COUNT_STR;
CREATE FUNCTION COUNT_STR(x TEXT, v TEXT)
RETURNS INT
RETURN (LENGTH(x) - LENGTH(REPLACE(x, v, '')))/LENGTH(v);

-- SELECT COUNT_STR('aloaloaloaa','a')

DROP FUNCTION IF EXISTS DECAPITALIZE;
CREATE FUNCTION DECAPITALIZE(s VARCHAR(255)) RETURNS VARCHAR(255)
  RETURN CONCAT(LCASE(LEFT(s, 1)), SUBSTRING(s, 2));

DROP FUNCTION IF EXISTS UC_FIRST;
CREATE FUNCTION UC_FIRST(oldWord VARCHAR(255)) RETURNS VARCHAR(255)
  RETURN CONCAT(UCASE(SUBSTRING(oldWord, 1, 1)),SUBSTRING(oldWord, 2));

DROP FUNCTION IF EXISTS DEL_CHAR_AND_CAPITALIZE;
DELIMITER //
CREATE FUNCTION DEL_CHAR_AND_CAPITALIZE(x TEXT, v TEXT) RETURNS TEXT
BEGIN
	SET @x = 0;
    SET @c = COUNT_STR(x,v)+1;
    SET @s = '';
    REPEAT
		SET @x = @x + 1;
        SET @t = SPLIT_STR(x,v,@x);
        SET @s = CONCAT(@s,UC_FIRST(@t));
    UNTIL @x >= @c END REPEAT;
    RETURN @s;
END // DELIMITER ;

 
-- SELECT DEL_CHAR_AND_CAPITALIZE('', '_');

DROP FUNCTION IF EXISTS UC_DELIMETER;
DELIMITER //
CREATE FUNCTION UC_DELIMETER(oldName VARCHAR(255), delim VARCHAR(1), trimSpaces BOOL, replaceDelim BOOL) RETURNS VARCHAR(255)
BEGIN
  SET @oldString := oldName;
  SET @newString := "";
 
  tokenLoop: LOOP
    IF trimSpaces THEN SET @oldString := TRIM(BOTH " " FROM @oldString); END IF;
 
    SET @splitPoint := LOCATE(delim, @oldString);
 
    IF @splitPoint = 0 THEN
      SET @newString := CONCAT(@newString, UC_FIRST(@oldString));
      LEAVE tokenLoop;
    END IF;
 
    SET @newString := CONCAT(@newString, UC_FIRST(SUBSTRING(@oldString, 1, @splitPoint)));
    SET @oldString := SUBSTRING(@oldString, @splitPoint+1);
  END LOOP tokenLoop;
  
  IF replaceDelim THEN 
	RETURN REPLACE(@newString,delim,'');
  END IF;
  RETURN @newString;
END// DELIMITER ;


DROP FUNCTION IF EXISTS JTYPE;
DELIMITER //
CREATE FUNCTION JTYPE(pType TEXT) RETURNS TEXT
BEGIN
  RETURN (CASE pType 
				WHEN 'bigint' THEN 'Long'
				WHEN 'binary' THEN 'Byte[]'
				WHEN 'bit' THEN 'Boolean'
				WHEN 'char' THEN 'String'
				WHEN 'date' THEN 'Date'
				WHEN 'datetime' THEN 'Date'
				WHEN 'datetime2' THEN 'Date'
				WHEN 'decimal' THEN 'Double'
				WHEN 'double' THEN 'Double'
				WHEN 'float' THEN 'Float'
				WHEN 'image' THEN 'byte[]'
				WHEN 'int' THEN 'Int'
				WHEN 'longtext' THEN 'String'
				WHEN 'money' THEN 'Double'
				WHEN 'nchar' THEN 'String'
				WHEN 'ntext' THEN 'String'
				WHEN 'numeric' THEN 'Double'
				WHEN 'nvarchar' THEN 'String'
				WHEN 'real' THEN 'Double'
				WHEN 'smalldatetime' THEN 'Date'
				WHEN 'smallint' THEN 'Int'
				WHEN 'mediumint' THEN 'Int'
				WHEN 'smallmoney' THEN 'Double'
				WHEN 'text' THEN 'String'
				WHEN 'time' THEN 'Date'
				WHEN 'timestamp' THEN 'Date'
				WHEN 'tinyint' THEN 'Boolean'
				WHEN 'uniqueidentifier' THEN 'String'
				WHEN 'varbinary' THEN 'byte[]'
				WHEN 'VARCHAR' THEN 'String'
				WHEN 'year' THEN 'Int'
				ELSE 'UNKNOWN_' + pType 
		END);
END// DELIMITER ;

DROP FUNCTION IF EXISTS JVAR;
CREATE FUNCTION JVAR(tname TEXT) RETURNS TEXT
  RETURN DECAPITALIZE(JCLASS(tname));
  
  
DROP FUNCTION IF EXISTS JCLASS;
CREATE FUNCTION JCLASS(tname TEXT) RETURNS TEXT
  RETURN (DEL_CHAR_AND_CAPITALIZE(tname,'_'));