MSSQL Stored Procedure to generate an Ext JS Model

[marc.fearby]

I've been using a stored procedure written by Leon C. Tayson to generate c# objects based on tables in SQL Server and decided to change it to generate an Ext JS model to save typing. Here is the version I've modified spit out an Ext.data.Model:

USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*======================================================================
ExtJS Model Generator
This SP accepts a database object (table, view) name's parameter and 
generates an ExtJS model based on the object's fields
Sample Usage:
USE Northwind
EXEC procGenerateExtJsModel 'Shippers'
Author: Marc Fearby
Based on procGenerateEntityClass by Leon C. Tayson
http://www.planet-source-code.com/vb...=1039&lngWId=5
======================================================================*/
CREATE PROCEDURE [dbo].[procGenerateExtJsModel]
@ObjectName varchar(100)
ASDECLARE @name varchar(35), @type varchar(35)
DECLARE objCursor CURSOR
FOR
SELECT sc.name, st.name type FROM syscolumns sc INNER JOIN systypes st ON st.xusertype = sc.xusertype WHERE Id=OBJECT_ID(@ObjectName)
DECLARE @declarationCodes varchar(8000)
SET @declarationCodes = ''
OPEN objCursor
FETCH NEXT FROM objCursor INTO @name, @type
DECLARE @extType varchar(35) -- ExtJS type
DECLARE @ctorParms varchar(4000)
SET @ctorParms = ''
IF @@FETCH_STATUS <> 0
 BEGIN
 CLOSE objCursor
 DEALLOCATE objCursor
 PRINT 'Error... Please check passed parameter'
 RETURN
END
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @extType = CASE
  WHEN @type LIKE '%char%' OR @type LIKE '%text%' THEN 'string'
  WHEN @type IN ('decimal', 'numeric')            THEN 'float'
  WHEN @type LIKE '%money%'                       THEN 'float'
  WHEN @type = 'bit'                              THEN 'boolean'
  WHEN @type LIKE '%int%'                         THEN 'int'
  WHEN @type LIKE '%uniqueidentifier%'            THEN 'string'
  WHEN @type LIKE '%datetime%'                    THEN 'date'
  ELSE 'auto' END
 -- Remove the 'tb' part of the table name
 IF LEFT(@ObjectName, 2) = 'tb' BEGIN
  SET @ObjectName = SUBSTRING(@ObjectName, 3, LEN(@ObjectName))
 END
 SET @declarationCodes = @declarationCodes + '        { name: ''' + @name + ''', type: ''' + @extType + ''' },' + CHAR(13)
 IF @extType = 'date' BEGIN 
  SET @declarationCodes = LEFT(@declarationCodes, LEN(@declarationCodes) - 4)
  SET @declarationCodes = @declarationCodes + ', dateFormat: ''MS''},' + CHAR(13)
 END
 FETCH NEXT FROM objCursor
 INTO @name, @type
END
PRINT 'Ext.define(''' + @ObjectName + ''', {'
PRINT '    extend: ''Ext.data.Model'','
PRINT '    fields: [' 
PRINT LEFT(@declarationCodes, LEN(@declarationCodes) - 2)
PRINT '    ]'
PRINT '});'
CLOSE objCursor
DEALLOCATE objCursor

Perhaps this might prove useful to somebody using ExtJS in a Microsoft environment.

[scottmartin]

Thank you for the contribution.

Regards,
Scott.

[sinclas]

Will there a version that could be used to create models within Sencha Architect. I have a table with >100 fields and would like to auto-generate the model. Currently I would comma-delineate it but it will not have the nice types and 'MS' date field config that the Stored Procedure creates for me. Any ideas?

Thanks for the work, it works well!

Steve

[evant]

100 fields, in a grid? Ewww :P

[sinclas]

100 Fields in a Model. 5 fields in a grid. The rest will be for entry in a pop-up editor window.

[marc.fearby]

100 Fields in a Model. 5 fields in a grid. The rest will be for entry in a pop-up editor window.

Sounds like you should just use a 5-field model on that grid and use a different model with appropriate fields for the popups.

Sencha Architect generating models from the database does sound like a very nice feature to have, one day :-)

[sinclas]

I created a small lookup version of the model with only five fields linked to a different store that only loads and the displays the data the grid needs. I am still creating the model with the 100 fields. One field at a time. I was able to paste in comma delineated fields, but now I have to enter each field's data type. Sencha Arch, needs a model builder! from SQL, Delineated file, xml or something. Working in the UI for each field is time consuming. Nothing some patients and cool music can't fix. thanks for the reply it validated some decisions I have made this afternoon!

[marc.fearby]

Most of my models reside in an /appname/include/models.js which I manually paste in from the stored procedure and modify accordingly. I generally avoid creating models in SA because I'm often changing the URLs to proxies (with different subfolders, etc) in my stores and then assigning the store back to a grid. I could probably improve the way I'm doing this, but when I started using Ext JS, I wasn't too sure about the new REST proxy; it may have matured/stabilised by now.

[Frith]

Marc,
really appreciated what you've done! It saves no end of time. I have Ext Designer but found it extremely slow so I tend to hand code a lot of my projects. I've expanded on what you've provided to add the store.

The my app structure is as follows:
\MYAPP\app\view
\view\ui
\store
\php\ <-- all get and set methods go in here

I use a lot of views in SQL and I always convert datetime to varchar: CONVERT(varchar, myDate, 120) as dtMyDate

I have therefore added a filter to look for 'dt' at the start of the field name and set that as a date type.

SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ON
GO


/*======================================================================
ExtJS Model Generator
This SP accepts a database object (table, view) name's parameter and 
generates an ExtJS model based on the object's fields
Sample Usage:
USE Northwind
EXEC procGenerateExtJsModel 'Shippers'
Author: Marc Fearby
Based on procGenerateEntityClass by Leon C. Tayson
http://www.planet-source-code.com/vb...=1039&lngWId=5
======================================================================*/
CREATE PROCEDURE [dbo].[procGenerateExtJsModel]
@ObjectName varchar(100)
AS DECLARE @name varchar(35), @type varchar(35)
DECLARE objCursor CURSOR
FOR
SELECT sc.name, st.name type FROM syscolumns sc INNER JOIN systypes st ON st.xusertype = sc.xusertype WHERE Id=OBJECT_ID(@ObjectName)
DECLARE @declarationCodes varchar(8000)
SET @declarationCodes = ''
OPEN objCursor
FETCH NEXT FROM objCursor INTO @name, @type
DECLARE @extType varchar(35) -- ExtJS type
DECLARE @ctorParms varchar(4000)
SET @ctorParms = ''
IF @@FETCH_STATUS <> 0
 BEGIN
 CLOSE objCursor
 DEALLOCATE objCursor
 PRINT 'Error... Please check passed parameter'
 RETURN
END
WHILE @@FETCH_STATUS = 0
BEGIN


 SET @extType = CASE
  WHEN @type LIKE '%char%' OR @type LIKE '%text%' THEN 'string'
  WHEN @type IN ('decimal', 'numeric')            THEN 'float'
  WHEN @type LIKE '%money%'                       THEN 'float'
  WHEN @type = 'bit'                              THEN 'boolean'
  WHEN @type LIKE '%int%'                         THEN 'int'
  WHEN @type LIKE '%uniqueidentifier%'            THEN 'string'
  WHEN @type LIKE '%datetime%' THEN 'date'
  ELSE 'auto' END
 -- Remove the 'tb' part of the table name
 IF LEFT(@ObjectName, 2) = 'tb' OR LEFT(@ObjectName, 2) = 'vw' BEGIN
  SET @ObjectName = SUBSTRING(@ObjectName, 3, LEN(@ObjectName))
 END


 IF LEFT(@name, 2) = 'dt' SET @extType = 'date'


 SET @declarationCodes = @declarationCodes + '        { name: ''' + @name + ''', type: ''' + @extType + ''' },' + CHAR(13)
 IF @extType = 'date' BEGIN 
  SET @declarationCodes = LEFT(@declarationCodes, LEN(@declarationCodes) - 4)
  SET @declarationCodes = @declarationCodes + ', dateFormat: ''Y-m-d H:i:s''},' + CHAR(13)
 END
 FETCH NEXT FROM objCursor
 INTO @name, @type
END
PRINT 'Ext.define(''mdl' + @ObjectName + ''', {'
PRINT '    extend: ''Ext.data.Model'','
PRINT '    fields: [' 
PRINT LEFT(@declarationCodes, LEN(@declarationCodes) - 2)
PRINT '    ]'
PRINT '});'
PRINT ''
PRINT ''
PRINT 'Ext.define(''MYAPP.store.jsStore' + @ObjectName + ''', {'
PRINT '    extend: ''Ext.data.Store'','
PRINT ''
PRINT '    constructor: function(cfg) {'
PRINT '        var me = this;'
PRINT '        cfg = cfg || {};'
PRINT '        me.callParent([Ext.apply({'
PRINT '        	model: ''mdl' + @ObjectName + ''','
PRINT '         storeId: ''jsStore' + @ObjectName + ''','
PRINT '         proxy: {'
PRINT '                type: ''ajax'','
PRINT '                url: ''/MYAPP/php/get' + @ObjectName + '.php'','
PRINT '                reader: {'
PRINT '                    type: ''json'','
PRINT '                    root: ''rows'','
PRINT '                	totalProperty: ''total'''
PRINT '                }'
PRINT ''                
PRINT '            }'
PRINT ''            
PRINT '        }, cfg)]);'
PRINT '    }'
PRINT '});'
PRINT ''
PRINT ''
PRINT '// Save As: jsStore' + @ObjectName + '.js'


CLOSE objCursor
DEALLOCATE objCursor

[marc.fearby]

I'm thinking of using stores and models more appropriately in my next project, so I may just use what you've further elaborated. I have been sending my own AJAX requests with appropriate GET/PUT/POST/DELETE methods but I think I will actually use the REST store/model/proxy whatever it is next time, and a stored proc to do more is just what the doctor ordered. Thanks.