Skip to content

Instantly share code, notes, and snippets.

@bklein01
Last active January 28, 2025 18:53
Show Gist options
  • Save bklein01/0b80320ab19491fe4827c4e555c5c7c7 to your computer and use it in GitHub Desktop.
Save bklein01/0b80320ab19491fe4827c4e555c5c7c7 to your computer and use it in GitHub Desktop.
The following quick script will generate a model given a table name or all models given a database name, with all getters and setters. This is a big help for those us who develop using the new version of Symfony that removed the reverse engineering feature. Just modify two places. Working on a way to do all tables in a schema in one shot..
SET SESSION group_concat_max_len = 10000;
SELECT
CONCAT_WS(
'\n',
'namespace App\\Entity;',
'\n',
CONCAT(
'class ',
replace(replace(replace(initcap(replace(CONCAT(UCASE(SUBSTRING(s.table_name, 1, 1)), LOWER(SUBSTRING(s.table_name, 2))), '_', ' ')), ' ', ''), 'Edu', ''), 'Gen', ''),
' {'
),
GROUP_CONCAT(q.details SEPARATOR '\n'),
'}',
'\n'
) details
FROM information_schema.tables s
JOIN (
SELECT
t.table_name, t.table_schema,
CONCAT_WS(
'\n',
CONCAT(
'protected $',
initlower(replace(initcap(replace(col.column_name, '_', ' ')), ' ', '')),
';'
),
'\n',
CONCAT(
'public function get',
replace(initcap(replace(col.column_name, '_', ' ')), ' ', ''),
'() {'
),
CONCAT(
' return $this->',
initlower(replace(initcap(replace(col.column_name, '_', ' ')), ' ', '')),
';'
),
'}',
'\n',
CONCAT(
'public function set',
replace(initcap(replace(col.column_name, '_', ' ')), ' ', ''),
'($value) {'
),
CONCAT(
' $this->',
initlower(replace(initcap(replace(col.column_name, '_', ' ')), ' ', '')),
' = $value;'
),
'}',
'\n'
) details
FROM
information_schema.columns col
INNER JOIN information_schema.tables t
ON col.table_schema = t.table_schema
AND col.table_name = t.table_name) q ON s.table_name = q.table_name AND s.table_schema = q.table_schema
WHERE s.table_schema = 'DATABASE_NAME'
GROUP BY s.table_name
SELECT
CONCAT_WS(
'\n',
'namespace App\\Entity;',
'\n',
CONCAT(
'class ',
IFNULL('<CLASSNAME>', ''),
' {'
)
)
UNION
ALL
SELECT
CONCAT_WS(
'\n',
CONCAT(
'protected $',
IFNULL(col.column_name, ''),
';'
),
'\n',
CONCAT(
'public function get_',
IFNULL(col.column_name, ''),
'() {'
),
CONCAT(
' return $this->',
IFNULL(col.column_name, ''),
';'
),
'}',
'\n',
CONCAT(
'public function set_',
IFNULL(col.column_name, ''),
'($value) {'
),
CONCAT(
' $this->',
IFNULL(col.column_name, ''),
' = $value;'
),
'}',
'\n'
)
FROM
information_schema.columns col
INNER JOIN information_schema.tables t
ON col.table_schema = t.table_schema
AND col.table_name = t.table_name
WHERE col.table_name = '<TABLE NAME>' AND col.table_schema = '<DATABASENAME>'
UNION
ALL
SELECT
'}'
CREATE FUNCTION `initcap`(x text) RETURNS text CHARSET utf8
READS SQL DATA
DETERMINISTIC
BEGIN
SET @str='';
SET @l_str='';
WHILE x REGEXP ' ' DO
SELECT SUBSTRING_INDEX(x, ' ', 1) INTO @l_str;
SELECT SUBSTRING(x, LOCATE(' ', x)+1) INTO x;
SELECT CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(@l_str,1,1)),LOWER(SUBSTRING(@l_str,2)))) INTO @str;
END WHILE;
RETURN LTRIM(CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(x,1,1)),LOWER(SUBSTRING(x,2)))));
END
CREATE FUNCTION `initlower`(x text) RETURNS text CHARSET utf8
READS SQL DATA
DETERMINISTIC
BEGIN
SET @str='';
SET @l_str='';
WHILE x REGEXP ' ' DO
SELECT SUBSTRING_INDEX(x, ' ', 1) INTO @l_str;
SELECT SUBSTRING(x, LOCATE(' ', x)+1) INTO x;
SELECT CONCAT(@str, ' ', CONCAT(LOWER(SUBSTRING(@l_str,1,1)),LOWER(SUBSTRING(@l_str,2)))) INTO @str;
END WHILE;
RETURN LTRIM(CONCAT(@str, ' ', CONCAT(LOWER(SUBSTRING(x,1,1)),LOWER(SUBSTRING(x,2)))));
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment