1. #1
    Touch Premium Member
    Join Date
    Sep 2011
    Location
    Tamworth, NSW, Australia
    Posts
    426
    Vote Rating
    8
    marc.fearby will become famous soon enough

      0  

    Thumbs up MSSQL Stored Procedure to generate an Ext JS Model

    MSSQL Stored Procedure to generate an Ext JS Model


    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:

    Code:
    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.

  2. #2
    Sencha - Support Team scottmartin's Avatar
    Join Date
    Jul 2010
    Location
    Houston, Tx
    Posts
    8,868
    Vote Rating
    438
    scottmartin has a brilliant future scottmartin has a brilliant future scottmartin has a brilliant future scottmartin has a brilliant future scottmartin has a brilliant future scottmartin has a brilliant future scottmartin has a brilliant future scottmartin has a brilliant future scottmartin has a brilliant future scottmartin has a brilliant future scottmartin has a brilliant future

      0  

    Default


    Thank you for the contribution.

    Regards,
    Scott.

  3. #3
    Sencha Premium Member
    Join Date
    Dec 2010
    Posts
    12
    Vote Rating
    0
    sinclas is on a distinguished road

      0  

    Default Sencha Architect version?

    Sencha Architect version?


    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

  4. #4
    Sencha - Ext JS Dev Team evant's Avatar
    Join Date
    Apr 2007
    Location
    Sydney, Australia
    Posts
    16,645
    Vote Rating
    583
    evant has a reputation beyond repute evant has a reputation beyond repute evant has a reputation beyond repute evant has a reputation beyond repute evant has a reputation beyond repute evant has a reputation beyond repute evant has a reputation beyond repute evant has a reputation beyond repute evant has a reputation beyond repute evant has a reputation beyond repute evant has a reputation beyond repute

      0  

    Default


    100 fields, in a grid? Ewww :P
    Evan Trimboli
    Sencha Developer
    Twitter - @evantrimboli
    Don't be afraid of the source code!

  5. #5
    Sencha Premium Member
    Join Date
    Dec 2010
    Posts
    12
    Vote Rating
    0
    sinclas is on a distinguished road

      0  

    Default


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

  6. #6
    Touch Premium Member
    Join Date
    Sep 2011
    Location
    Tamworth, NSW, Australia
    Posts
    426
    Vote Rating
    8
    marc.fearby will become famous soon enough

      0  

    Default


    Quote Originally Posted by sinclas View Post
    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 :-)

  7. #7
    Sencha Premium Member
    Join Date
    Dec 2010
    Posts
    12
    Vote Rating
    0
    sinclas is on a distinguished road

      0  

    Default


    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!

  8. #8
    Touch Premium Member
    Join Date
    Sep 2011
    Location
    Tamworth, NSW, Australia
    Posts
    426
    Vote Rating
    8
    marc.fearby will become famous soon enough

      0  

    Default


    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.

  9. #9
    Sencha User
    Join Date
    Jan 2010
    Location
    Northern Ireland
    Posts
    58
    Vote Rating
    2
    Frith is on a distinguished road

      0  

    Default Loving your work!

    Loving your work!


    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.

    Code:
    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

  10. #10
    Touch Premium Member
    Join Date
    Sep 2011
    Location
    Tamworth, NSW, Australia
    Posts
    426
    Vote Rating
    8
    marc.fearby will become famous soon enough

      0  

    Default


    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.