Click here to Skip to main content
15,888,351 members
Search within:


Articles / Web Development / HTML
 

Wizard and CRUD Applications to Build other CRUD Applications

Rate me:
Please Sign up or sign in to vote.
4.65/5 (41 votes)
8 Jun 2010GPL37 min read 110.9K   4.1K   146  
Looking at the possibility of using CRUD applications to manage other CRUD applications inside a web browser and without any hand-coding. Also, presenting a step wizard deriving the database structure from the UI rather than the opposite.
 
��/*** www.evolutility.com - (c) 2008 Olivier Giulieri  ***/

/*    EvoDico - Evolutility database dictionary    */

 

/****** Object:  Table [EvoDico_Field]    Script Date: 03/23/2008 20:21:18 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [EvoDico_Field](

	[ID] [int] IDENTITY(1,1) NOT NULL,

	[FormID] [int] NOT NULL,

	[UserID] [int] NULL CONSTRAINT [DF_EvoDico_Field_UserID]  DEFAULT ((1)),

	[Label] [nvarchar](100) NOT NULL CONSTRAINT [DF_EvoDico_Field_Caption]  DEFAULT (''),

	[LabelEdit] [nvarchar](100) NULL CONSTRAINT [DF_EvoDico_Field_CaptionEdit]  DEFAULT (''),

	[LabelList] [nvarchar](50) NULL CONSTRAINT [DF_EvoDico_Field_CaptionList]  DEFAULT (''),

	[TypeID] [int] NOT NULL CONSTRAINT [DF_EvoDico_Field_TypeID]  DEFAULT ((5)),

	[dbcolumn] [nvarchar](100) NOT NULL CONSTRAINT [DF_EvoDico_Field_dbcolumn]  DEFAULT (''),

	[dbcolumnread] [nvarchar](100) NOT NULL CONSTRAINT [DF_EvoDico_Field_dbcolumnread]  DEFAULT (''),

	[dbcolumnimg] [nvarchar](100) NULL CONSTRAINT [DF_EvoDico_Field_dbcolumnimg]  DEFAULT (''),

	[script] [nvarchar](50) NULL,

	[min] [int] NULL,

	[max] [int] NULL,

	[maxlength] [smallint] NULL CONSTRAINT [DF_EvoDico_Field_maxlength]  DEFAULT ((100)),

	[regexp] [nvarchar](30) NULL  CONSTRAINT [DF_EvoDico_Field_regexp]  DEFAULT (''),

	[dbtablelov] [nvarchar](100) NULL CONSTRAINT [DF_EvoDico_Field_lovtable]  DEFAULT (''),

	[dborderlov] [nvarchar](100) NULL CONSTRAINT [DF_EvoDico_Field_lovsort]  DEFAULT (''),

	[dbcolumnreadlov] [nvarchar](250) NULL CONSTRAINT [DF_EvoDico_Field_lovcolumnread]  DEFAULT (''),

	[dbcolumndetails] [nvarchar](100) NULL CONSTRAINT [DF_EvoDico_Field_dblovcolumn]  DEFAULT (''),

	[dbwherelov] [nvarchar](250) NULL CONSTRAINT [DF_EvoDico_Field_lovcondition]  DEFAULT (''),

	[lovmany] [smallint] NULL CONSTRAINT [DF_EvoDico_Field_lovmany]  DEFAULT ((0)),

	[lovsplist] [nvarchar](100) NULL CONSTRAINT [DF_EvoDico_Field_lovsplist]  DEFAULT (''),

	[img] [nvarchar](100) NULL CONSTRAINT [DF_EvoDico_Field_img]  DEFAULT (''),

	[imglist] [nvarchar](100) NULL CONSTRAINT [DF_EvoDico_Field_imglist]  DEFAULT (''),

	[defaultvalue] [nvarchar](100) NULL CONSTRAINT [DF_EvoDico_Field_defaultvalue]  DEFAULT (''),

	[readonly] [smallint] NULL CONSTRAINT [DF_EvoDico_Field_readonly]  DEFAULT ((0)),

	[required] [bit] NULL CONSTRAINT [DF_EvoDico_Field_required]  DEFAULT ((0)),

	[optional] [bit] NULL CONSTRAINT [DF_EvoDico_Field_optional]  DEFAULT ((0)),

	[format] [nvarchar](30) NULL CONSTRAINT [DF_EvoDico_Field_format]  DEFAULT (''),

	[PanelID] [int] NOT NULL CONSTRAINT [DF_EvoDico_Field_PanelID]  DEFAULT ((0)),

	[PanelIndex] [smallint] NULL CONSTRAINT [DF_EvoDico_Field_PanelIndex]  DEFAULT ((1)),

	[fpos] [smallint] NULL CONSTRAINT [DF_EvoDico_Field_fpos]  DEFAULT ((10)),

	[link] [nvarchar](150) NULL CONSTRAINT [DF_EvoDico_Field_link]  DEFAULT (''),

	[linkcaption] [nvarchar](150) NULL CONSTRAINT [DF_EvoDico_Field_linkcaption]  DEFAULT (''),

	[linktarget] [nvarchar](20) NULL CONSTRAINT [DF_EvoDico_Field_linktarget]  DEFAULT (''),

	[search] [bit] NULL CONSTRAINT [DF_EvoDico_Field_search]  DEFAULT ((1)),

	[searchadv] [bit] NULL CONSTRAINT [DF_EvoDico_Field_searchadv]  DEFAULT ((1)),

	[searchlist] [bit] NULL CONSTRAINT [DF_EvoDico_Field_searchlist]  DEFAULT ((1)),

	[searchquick] [bit] NULL CONSTRAINT [DF_EvoDico_Field_searchquick]  DEFAULT ((0)),

	[lookup] [bit] NULL CONSTRAINT [DF_EvoDico_Field_lookup]  DEFAULT ((0)),

	[cssclass] [nvarchar](50) NULL CONSTRAINT [DF_EvoDico_Field_cssclass]  DEFAULT (''),

	[height] [smallint] NULL CONSTRAINT [DF_EvoDico_Field_height]  DEFAULT ((1)),

	[width] [smallint] NULL CONSTRAINT [DF_EvoDico_Field_width]  DEFAULT ((100)),

	[help] [nvarchar](500) NULL CONSTRAINT [DF_EvoDico_Field_help]  DEFAULT (''),

	[options] [text] NULL CONSTRAINT [DF_EvoDico_Field_options]  DEFAULT (''),

	[Publish] [smallint] NULL CONSTRAINT [DF_EvoDico_Field_Publish]  DEFAULT ((1)),

 CONSTRAINT [PK_EvoDico_Field] PRIMARY KEY CLUSTERED 

(

	[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

/****** Object:  StoredProcedure [EvoSP_PagedItem]    Script Date: 03/23/2008 20:20:50 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [EvoSP_PagedItem]

	(

	@Select  varchar(1000),

	@Table varchar(200),

	@TableS varchar(300),

	@WhereClause  nvarchar(2000),

	@OrderBy  varchar(200),

	@Page int,

	@RecsPerPage int	

	)

AS



SET NOCOUNT ON

CREATE TABLE #TempItems ( IDt int IDENTITY, IDo int)

IF (@WhereClause='')

  	INSERT INTO #TempItems (IDo) EXEC('SELECT T.ID FROM '+@TableS+'  ORDER BY ' +@OrderBy)

ELSE

	EXEC( 'INSERT INTO #TempItems (IDo) SELECT T.ID FROM '+@TableS+'  WHERE ' + @WhereClause+ ' ORDER BY '+@OrderBy)

DECLARE @FirstRec int, @LastRec int

SELECT @FirstRec = (@Page - 1) * @RecsPerPage

SELECT @LastRec = (@Page * @RecsPerPage + 1)

IF (@WhereClause='')

	EXEC( 'SELECT    '+@Select + ', MoreRecords = ( SELECT COUNT(*)  FROM #TempItems Temp  WHERE Temp.IDt >= ' 

+ @LastRec + ')  FROM #TempItems Temp,  ' + @TableS  

	+ ' WHERE T.ID = Temp.IDo AND Temp.IDt > '+ @FirstRec + ' AND Temp.IDt < '+ @LastRec + '  ORDER BY '+  @OrderBy)

ELSE

	EXEC( 'SELECT    '+@Select + ', MoreRecords = ( SELECT COUNT(*)  FROM #TempItems Temp  WHERE Temp.IDt >= ' 

+ @LastRec + ')  FROM #TempItems Temp,  ' + @TableS  

	+ ' WHERE T.ID = Temp.IDo AND Temp.IDt > '+ @FirstRec + ' AND Temp.IDt < '+ @LastRec + ' AND ' + @WhereClause+ ' ORDER BY '+  @OrderBy)

SET NOCOUNT OFF

GO

/****** Object:  Table [EvoDico_App]    Script Date: 03/23/2008 20:20:55 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [EvoDico_App](

	[ID] [int] IDENTITY(1,1) NOT NULL,

	[UserID] [int] NULL CONSTRAINT [DF_EvoDico_App_UserID]  DEFAULT ((1)),

	[Name] [nvarchar](100) NOT NULL CONSTRAINT [DF_EvoDico_App_Name]  DEFAULT (''),

	[SID] [int] NULL CONSTRAINT [DF_EvoDico_App_SecurityKey]  DEFAULT ((1)),

	[ApplicationKey] [nvarchar](50) NULL,

	[Version] [nvarchar](12) NULL,

	[Description] [nvarchar](500) NULL CONSTRAINT [DF_EvoDico_App_Description]  DEFAULT (''),

	[publish] [bit] NULL,

	[Cdate] [datetime] NULL CONSTRAINT [DF_EvoDico_App_Cdate]  DEFAULT (getdate()),

	[Weight] [int] NULL,

 CONSTRAINT [PK_EvoDico_App] PRIMARY KEY CLUSTERED 

(

	[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  Table [EvoDico_Form]    Script Date: 03/23/2008 20:21:41 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [EvoDico_Form](

	[ID] [int] IDENTITY(1,1) NOT NULL,

	[AppID] [int] NULL CONSTRAINT [DF_EvoDico_Form_AppID]  DEFAULT ((1)),

	[UserID] [int] NULL CONSTRAINT [DF_EvoDico_Form_UserID]  DEFAULT ((1)),

	[GroupID] [int] NULL,

	[Class] [int] NULL CONSTRAINT [DF_EvoDico_Form_Class]  DEFAULT ((1)),

	[Title] [nvarchar](100) NULL CONSTRAINT [DF_EvoDico_Form_Title]  DEFAULT (''),

	[icon] [nvarchar](50) NULL CONSTRAINT [DF_EvoDico_Form_dbpix]  DEFAULT ('cube.gif'),

	[entity] [nvarchar](50) NULL CONSTRAINT [DF_EvoDico_Form_itemname]  DEFAULT ('item'),

	[entities] [nvarchar](50) NULL,

	[dbtable] [nvarchar](100) NULL CONSTRAINT [DF_EvoDico_Form_dbtable]  DEFAULT (''),

	[dbtableread] [nvarchar](100) NULL CONSTRAINT [DF_EvoDico_Form_dbtableread]  DEFAULT (''),

	[dbwhere] [nvarchar](150) NULL CONSTRAINT [DF_EvoDico_Form_dbwhere]  DEFAULT (''),

	[dbwherelock] [nvarchar](50) NULL CONSTRAINT [DF_EvoDico_Form_dbwherelock]  DEFAULT (''),

	[dborder] [nvarchar](200) NULL CONSTRAINT [DF_EvoDico_Form_dborderby]  DEFAULT (''),

	[dbcolumnlead] [nvarchar](100) NULL,

	[dbpixcolumn] [nvarchar](50) NULL,

	[dbtablecomments] [nvarchar](50) NULL,

	[dbtableusers] [nvarchar](50) NULL,

	[Publish] [smallint] NULL CONSTRAINT [DF_EvoDico_Form_Publish]  DEFAULT ((0)),

	[SecuID] [int] NULL CONSTRAINT [DF_EvoDico_Form_SID]  DEFAULT ((1)),

	[CommentsID] [int] NULL,

	[script] [nvarchar](50) NULL,

	[Description] [nvarchar](250) NULL,

	[sppaging] [nvarchar](300) NULL CONSTRAINT [DF_EvoDico_Form_sppaging]  DEFAULT (''),

	[splogin] [nvarchar](200) NULL CONSTRAINT [DF_EvoDico_Form_splogin]  DEFAULT (''),

	[spget] [nvarchar](200) NULL CONSTRAINT [DF_EvoDico_Form_spget]  DEFAULT (''),

	[spdelete] [nvarchar](200) NULL CONSTRAINT [DF_EvoDico_Form_spdelete]  DEFAULT (''),

	[useTabs] [bit] NULL,

	[dbDetails] [bit] NULL,

	[xmlfile] [nvarchar](200) NULL,

	[url] [nvarchar](200) NULL,

	[help] [nvarchar](500) NULL,

	[wcreatedate] [smalldatetime] NULL CONSTRAINT [DF_EvoDico_Form_wcreatedate]  DEFAULT (getdate()),

	[wmodifdate] [smalldatetime] NULL,

	[wpublishdate] [smalldatetime] NULL,

 CONSTRAINT [PK_EvoDico_Form] PRIMARY KEY CLUSTERED 

(

	[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  View [EvoDico_lovForm]    Script Date: 03/23/2008 20:22:12 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [EvoDico_lovForm]

AS

SELECT     ID, Title AS name, dbtable, icon

FROM         EvoDico_Form

GO 

/****** Object:  StoredProcedure [EvoDico_X_PagedItems]    Script Date: 03/23/2008 20:20:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [EvoDico_X_PagedItems]

	(

	@Select  nvarchar(1000),

	@Table nvarchar(100),

	@WhereClause  nvarchar(1000),

	@OrderBy  nvarchar(1000),

	@Page int,

	@RecsPerPage int	

	)

AS



SET NOCOUNT ON



--Create a temporary table

CREATE TABLE #TempItems ( IDt int IDENTITY, IDo int)



-- Insert the rows from @Table into the temp. table

IF (@WhereClause='')

  	INSERT INTO #TempItems (IDo) EXEC('SELECT ID FROM '+@Table+' (nolock)  ORDER BY ' +@OrderBy)

ELSE

	EXEC( 'INSERT INTO #TempItems (IDo) SELECT ID FROM '+@Table+' (nolock)  WHERE ' + @WhereClause+ ' ORDER BY ' +@OrderBy)

-- Find out the first and last record we want

DECLARE @FirstRec int, @LastRec int

SELECT @FirstRec = (@Page - 1) * @RecsPerPage

SELECT @LastRec = (@Page * @RecsPerPage + 1)



-- Now, return the set of paged IDs and corresponding records, + number of extra records

EXEC( 'SELECT '+@Select + ', MoreRecords = ( SELECT COUNT(*)  FROM #TempItems Temp  WHERE Temp.IDt >= ' + @LastRec + ') '

	+ ' FROM #TempItems Temp,  ' + @Table + ' (nolock) '

	+ ' WHERE ' +@Table + '.ID = Temp.IDo AND Temp.IDt > '+ @FirstRec + ' AND Temp.IDt < '+ @LastRec

	+ ' ORDER BY '+  @OrderBy)



SET NOCOUNT OFF

GO

/****** Object:  Table [EvoDico_Selection]    Script Date: 03/23/2008 20:21:53 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [EvoDico_Selection](

	[ID] [int] IDENTITY(1,1) NOT NULL,

	[FormID] [int] NULL,

	[UserID] [int] NULL,

	[Label] [nvarchar](200) NULL,

	[QuerySQL] [nvarchar](500) NULL,

 CONSTRAINT [PK_EvoDico_Selection] PRIMARY KEY CLUSTERED 

(

	[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  Table [EvoDico_FieldType]    Script Date: 03/23/2008 20:21:24 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [EvoDico_FieldType](

	[ID] [int] IDENTITY(1,1) NOT NULL,

	[ftPos] [int] NULL CONSTRAINT [DF_EvoDico_FieldType_ftPos]  DEFAULT ((1000)),

	[name] [nvarchar](255) NOT NULL CONSTRAINT [DF_EvoDico_FieldType_name]  DEFAULT (''),

	[smallname] [nvarchar](25) NULL CONSTRAINT [DF_EvoDico_FieldType_smallname]  DEFAULT (''),

	[TypeID] [int] NULL CONSTRAINT [DF_EvoDico_FieldType_TypeID]  DEFAULT ((0)),

	[pix] [nvarchar](20) NULL CONSTRAINT [DF_EvoDico_FieldType_pix]  DEFAULT (''),

	[xmlname] [nvarchar](30) NULL CONSTRAINT [DF_EvoDico_FieldType_xmlname]  DEFAULT (''),

	[sqlname] [nvarchar](30) NOT NULL CONSTRAINT [DF_EvoDico_FieldType_sqlname]  DEFAULT (''),

	[maxlength] [smallint] NULL,

	[description] [nvarchar](100) NOT NULL CONSTRAINT [DF_EvoDico_FieldType_desc]  DEFAULT (''),

	[publish] [bit] NOT NULL CONSTRAINT [DF_EvoDico_FieldType_publish]  DEFAULT ((0)),

 CONSTRAINT [PK_EvoDico_FieldType] PRIMARY KEY CLUSTERED 

(

	[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  Table [Evol_Comment]    Script Date: 03/23/2008 20:22:11 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [Evol_Comment](

	[ID] [int] IDENTITY(1,1) NOT NULL,

	[FormID] [int] NULL,

	[ItemID] [int] NULL,

	[UserID] [int] NULL,

	[creationdate] [smalldatetime] NULL CONSTRAINT [DF_Evol_Comments_cdate]  DEFAULT (getdate()),

	[message] [nvarchar](1000) NULL,

 CONSTRAINT [PK_Evol_Comment] PRIMARY KEY CLUSTERED 

(

	[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  Table [EvoDico_Panel]    Script Date: 03/23/2008 20:21:50 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [EvoDico_Panel](

	[ID] [int] IDENTITY(1,1) NOT NULL,

	[FormID] [int] NULL,

	[UserID] [int] NULL CONSTRAINT [DF_EvoDico_Panel_UserID]  DEFAULT ((1)),

	[TypeID] [int] NULL CONSTRAINT [DF_EvoDico_Panel_PanelTypeID]  DEFAULT ((1)),

	[Readonly] [smallint] NULL,

	[ModeID] [int] NULL CONSTRAINT [DF_EvoDico_Panel_PanelModeID]  DEFAULT ((1)),

	[TabID] [int] NULL CONSTRAINT [DF_EvoDico_Panel_TabID]  DEFAULT ((0)),

	[Label] [nvarchar](100) NOT NULL CONSTRAINT [DF_EvoDico_Panel_Caption]  DEFAULT (''),

	[Width] [nvarchar](10) NULL CONSTRAINT [DF_EvoDico_Panel_Width]  DEFAULT ('100'),

	[CSSClass] [nvarchar](20) NULL CONSTRAINT [DF_EvoDico_Panel_CSSClass]  DEFAULT (''),

	[CSSClassTitle] [nvarchar](20) NULL,

	[Optional] [bit] NULL CONSTRAINT [DF_EvoDico_Panel_Optional]  DEFAULT ((1)),

	[pPos] [smallint] NULL CONSTRAINT [DF_EvoDico_Panel_ppos]  DEFAULT ((1)),

	[DBtableDetails] [nvarchar](100) NULL,

	[DBcolumnDetails] [nvarchar](100) NULL,

	[DBWhere] [nvarchar](200) NULL,

	[DBOrder] [nvarchar](100) NULL,

	[Pix] [nvarchar](50) NULL,

	[Pix2] [nvarchar](50) NULL,

	[Summary] [nvarchar](250) NULL,

 CONSTRAINT [PK_EvoDico_Panel] PRIMARY KEY CLUSTERED 

(

	[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  Table [EvoDico_User]    Script Date: 03/23/2008 20:22:08 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [EvoDico_User](

	[ID] [int] IDENTITY(1,1) NOT NULL,

	[UserID]  AS ([ID]),

	[MemberShipID] [int] NULL CONSTRAINT [DF_EvoDico_User_MemberShipID]  DEFAULT ((0)),

	[pix] [nvarchar](20) NULL,

	[Publish] [int] NULL CONSTRAINT [DF_EvoDico_User_Publish]  DEFAULT ((1)),

	[Login] [nvarchar](50) NOT NULL CONSTRAINT [DF_EvoDico_User_Login]  DEFAULT (''),

	[Password] [nvarchar](50) NOT NULL CONSTRAINT [DF_EvoDico_User_Password]  DEFAULT (''),

	[TagLine] [nvarchar](150) NULL,

	[lastvisit] [datetime] NULL,

	[Firstname] [nvarchar](50) NULL CONSTRAINT [DF_EvoDico_User_Firstname]  DEFAULT (''),

	[Lastname] [nvarchar](50) NULL,

	[Company] [nvarchar](120) NULL,

	[Title] [nvarchar](120) NULL,

	[Phonew] [nvarchar](50) NULL,

	[Phonem] [nvarchar](50) NULL,

	[Fax] [nvarchar](50) NULL,

	[email] [nvarchar](255) NULL,

	[url] [nvarchar](255) NULL,

	[appurl] [nvarchar](250) NULL,

	[nbusers] [int] NULL CONSTRAINT [DF_EvoDico_User_nbusers]  DEFAULT ((1)),

	[imid] [nvarchar](100) NULL,

	[Photo] [nvarchar](100) NULL,

	[Address] [nvarchar](300) NULL,

	[City] [nvarchar](100) NULL,

	[State] [nvarchar](3) NULL,

	[Zip] [nvarchar](20) NULL,

	[Country] [nvarchar](50) NULL,

	[Intro] [nvarchar](1000) NULL,

	[Project] [nvarchar](500) NULL,

	[maCDate] [datetime] NULL CONSTRAINT [DF_EvoDico_User_maCDate]  DEFAULT (getdate()),

	[NBVisits] [smallint] NULL CONSTRAINT [DF_EvoDico_User_NBVisits]  DEFAULT ((0)),

	[APLogin] [datetime] NULL,

	[PostCount] [int] NULL CONSTRAINT [DF_EvoDico_User_PostCount]  DEFAULT ((0)),

	[CommentCount] [int] NULL CONSTRAINT [DF_EvoDico_User_CommentCount]  DEFAULT ((0)),

	[Creationdate] [datetime] NULL CONSTRAINT [DF_EvoDico_User_Creationdate]  DEFAULT (getdate()),

 CONSTRAINT [PK_EvoDico_User] PRIMARY KEY CLUSTERED 

(

	[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  StoredProcedure [EvoDico_Form_GetColumns]    Script Date: 03/23/2008 20:20:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE  [EvoDico_Form_GetColumns] 

(

    	@TableName  nvarchar(200)  

)

AS

 

SELECT   syscolumns.id, syscolumns.colid, syscolumns.name,

CASE 

	WHEN syscolumns.xtype = 231 THEN syscolumns.length/2

	ELSE syscolumns.length

END AS length, 

systypes.name AS typename, syscolumns.isnullable  

          FROM systypes (nolock), syscolumns (nolock),  sysobjects (nolock)

              WHERE sysobjects.id=syscolumns.id 

		 AND syscolumns.xtype=systypes.xtype  

                 AND systypes.length<>256

		AND sysobjects.name=@TableName

GO

/****** Object:  StoredProcedure [EvoDico_Form_Clone]    Script Date: 03/23/2008 20:20:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [EvoDico_Form_Clone] (

    @FormID int,

	@UserID int 

)

AS

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

	SET NOCOUNT ON;



    -- =============================================

    -- CLONE form

    -- =============================================

    

    DECLARE @nFormID int;   

     

    insert into EvoDico_Form (Title, publish, class, icon, entity, entities, Help, script, dbtable, dbwhere, dborder, dbcolumnlead, dbwherelock, dbpixColumn, spPaging, spLogin, spGet, spDelete)

    select Title, 0, class, icon, entity, entities, Help, script, dbtable, dbwhere, dborder, dbcolumnlead, dbwherelock, dbpixColumn, spPaging, spLogin, spGet, spDelete 

    from EvoDico_Form where id=@FormID  

     

    SET @nFormID=(SELECT @@IDENTITY);



    update EvoDico_Form set Title=Title + ' (' + cast(@nFormID as nvarchar) + ')' WHERE ID=@nFormID and len(rtrim(title))<90;



    -- =============================================

    -- CLONE panels & fields

    -- =============================================

	

	DECLARE @PanelID int;

	DECLARE @nPanelID int;

	

	DECLARE c1 CURSOR READ_ONLY

	FOR

		SELECT ID

		FROM EvoDico_Panel 

		where formid=@FormID 



	OPEN c1



	FETCH NEXT FROM c1 INTO @PanelID



	WHILE @@FETCH_STATUS = 0

	BEGIN



		INSERT INTO EvoDico_Panel (FormID, Label, ppos, Width, Optional, cssclass, Summary)

		select @nFormID, Label, ppos, Width, Optional, cssclass, Summary 

		from EvoDico_Panel where formid=@FormID AND ID=@PanelID;



		SET @nPanelID=(SELECT @@IDENTITY);

		

		INSERT INTO EvoDico_Field (formID, panelid, Label, labeledit, labellist, TypeID, maxlength, readonly, required, fpos, Width, Height, format, cssclass, searchlist, Optional, search, searchadv, script, link, linkcaption, linktarget, dbcolumn, dbcolumnread, dbcolumnimg, dbtablelov, dbcolumnreadlov, dbwherelov, dborderlov)

		select @nFormID, @nPanelID, Label, labeledit, labellist, TypeID, maxlength, readonly, required, fpos, Width, Height, format, cssclass, searchlist, Optional, search, searchadv, script, link, linkcaption, linktarget, dbcolumn, dbcolumnread, dbcolumnimg, dbtablelov, dbcolumnreadlov, dbwherelov, dborderlov

		from EvoDico_Field where formid=@FormID AND PanelID=@PanelID;

		

		FETCH NEXT FROM c1 INTO @PanelID;



	END



	CLOSE c1

	DEALLOCATE c1

 

    print @nFormID;



END

GO

/****** Object:  View [EvoDico_xField]    Script Date: 03/23/2008 20:22:13 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [EvoDico_xField]

AS

SELECT     EvoDico_Field.ID, EvoDico_Field.FormID, EvoDico_FieldType.xmlname AS Type, EvoDico_FieldType.pix AS typepix, EvoDico_Field.TypeID, 

                      EvoDico_Field.UserID, EvoDico_Field.Label, EvoDico_Field.LabelEdit, EvoDico_Field.LabelList, EvoDico_Field.dbcolumn, 

                      EvoDico_Field.dbcolumnread, EvoDico_Field.dbcolumnimg, EvoDico_Field.maxlength, EvoDico_Field.dbtablelov, EvoDico_Field.dborderlov, 

                      EvoDico_Field.dbcolumnreadlov, EvoDico_Field.dbcolumndetails, EvoDico_Field.dbwherelov, EvoDico_Field.img, EvoDico_Field.imglist, 

                      EvoDico_Field.defaultvalue, EvoDico_Field.readonly, EvoDico_Field.required, EvoDico_Field.optional, EvoDico_Field.format, 

                      EvoDico_Field.PanelID, EvoDico_Field.PanelIndex, EvoDico_Field.fpos, EvoDico_Field.link, EvoDico_Field.linkcaption, 

                      EvoDico_Field.linktarget, EvoDico_Field.search, EvoDico_Field.searchadv, EvoDico_Field.searchlist, EvoDico_Field.lookup, 

                      EvoDico_Field.cssclass, EvoDico_Field.height, EvoDico_Field.width, EvoDico_Field.help, EvoDico_Field.lovsplist

FROM         EvoDico_Field INNER JOIN

                      EvoDico_FieldType ON EvoDico_Field.TypeID = EvoDico_FieldType.ID

GO

 

/****** Object:  View [EvoDico_xFormPanels]    Script Date: 03/23/2008 20:22:13 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [EvoDico_xFormPanels]

AS

SELECT     EvoDico_Field.ID AS fieldID, EvoDico_Panel.ID, EvoDico_Panel.FormID, EvoDico_Panel.UserID, EvoDico_Panel.TypeID, 

                      EvoDico_Panel.Readonly, EvoDico_Panel.ModeID, EvoDico_Panel.TabID, EvoDico_Panel.Label, EvoDico_Panel.Width, 

                      EvoDico_Panel.CSSClass, EvoDico_Panel.CSSClassTitle, EvoDico_Panel.Optional, EvoDico_Panel.pPos, EvoDico_Panel.DBtableDetails, 

                      EvoDico_Panel.DBcolumnDetails, EvoDico_Panel.DBWhere, EvoDico_Panel.DBOrder, EvoDico_Panel.Pix, EvoDico_Panel.Pix2, 

                      EvoDico_Panel.Summary, EvoDico_Panel.DBOrder AS Expr1

FROM         EvoDico_Panel INNER JOIN

                      EvoDico_Field ON EvoDico_Panel.FormID = EvoDico_Field.FormID

WHERE     (EvoDico_Panel.TypeID = 1)

GO

 

/****** Object:  View [EvoDico_vFieldType]    Script Date: 03/23/2008 20:22:12 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [EvoDico_vFieldType]

AS

SELECT     ID, ftPos, name, smallname, TypeID, pix, xmlname, sqlname, description, publish

FROM         EvoDico_FieldType

WHERE     (publish = 1) AND (TypeID = 3)

GO

 

/****** Object:  View [EvoDico_vHolderType]    Script Date: 03/23/2008 20:22:13 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [EvoDico_vHolderType]

AS

SELECT     TOP (100) PERCENT ID, name, pix

FROM         EvoDico_FieldType

WHERE     (TypeID = 2)

ORDER BY ftPos

GO

 

/****** Object:  View [EvoDico_vPanel]    Script Date: 03/23/2008 20:22:14 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [EvoDico_vPanel]

AS

SELECT     EvoDico_Form.Title + ' - ' + EvoDico_Panel.Label AS FormName, EvoDico_Panel.ID, EvoDico_Panel.pPos, EvoDico_Panel.FormID, 

                      EvoDico_Panel.Label, EvoDico_Panel.Width, EvoDico_Panel.CSSClass, EvoDico_Panel.Optional, EvoDico_Panel.Summary

FROM         EvoDico_Panel INNER JOIN

                      EvoDico_Form ON EvoDico_Panel.FormID = EvoDico_Form.ID

WHERE     (EvoDico_Panel.TypeID = 1)

GO

 

/****** Object:  View [EvoDico_xPanel]    Script Date: 03/23/2008 20:22:14 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [EvoDico_xPanel]

AS

SELECT     ID, FormID, UserID, TypeID, pPos, Label + ' - ' + Label AS Label, Width, CSSClass, Optional, TabID, Summary

FROM         EvoDico_Panel

WHERE     (TypeID = 1)

GO

 

/****** Object:  StoredProcedure [EvoDico_Login_SP]    Script Date: 03/23/2008 20:20:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [EvoDico_Login_SP]  (@Login  nvarchar(100),	@Password nvarchar(100))

AS



DECLARE @userid INT

SELECT @userid =  ID FROM [EvoDico_User] WHERE login= @Login AND password= @Password  

IF (@userid>0)

  BEGIN

    	UPDATE [EvoDico_User] SET aplogin=lastvisit,lastvisit=getdate(), nbvisits=nbvisits+1  WHERE  ID= @userid

    	SELECT ID, login, firstname,

		'Welcome ' + firstname AS welcome  

	FROM [EvoDico_User]  WHERE  ID= @userid

  END

GO

/****** Object:  StoredProcedure [EvoSP_Login]    Script Date: 03/23/2008 20:20:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [EvoSP_Login] (@Login nvarchar(50),	@Password nvarchar(50))

AS



DECLARE @userid INT

SELECT @userid =  ID FROM [Evol_User] WHERE login= @Login AND password= @Password

IF (@userid>0)

  BEGIN

    	UPDATE [Evol_User] SET lastvisit=getdate(), nbvisits=nbvisits+1  WHERE  ID= @userid

    	SELECT ID, login, firstname,

		'Welcome ' + firstname AS welcome  

	FROM [Evol_User]  WHERE  ID= @userid

  END

GO

/****** Object:  StoredProcedure [EvoDico_Form_Get]    Script Date: 03/23/2008 20:20:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- (c) Evolutility.com 2003-2008 --



CREATE PROCEDURE  [EvoDico_Form_Get] 

(

    	@FormID int,

	@UserID int 

)

AS 



SELECT    ID, Title, icon, dbpixcolumn, entity, entities, Description, help 

FROM         EvoDico_Form  WHERE ID=@FormID;



SELECT ID, dbtable, dbtableread, dbwhere, dborder, entity, entities, icon, 

		script, dbtableusers, dbtablecomments,

		sppaging, splogin, spget, spdelete

	FROM EvoDico_Form  WHERE ID=@FormID;



SELECT     ID, FormID, UserID, TypeID, pPos, Label, Width, CSSClass, Optional, TabID, Summary

FROM       EvoDico_Panel WHERE FormID=@FormID ORDER BY ppos, ID;



SELECT * FROM EvoDico_xField WHERE FormID=@FormID ORDER BY fpos, ID;



-- SELECT * FROM EvoDico_xPanelDetails  WHERE FormID=@FormID ORDER BY ppos, ID;

-- SELECT * FROM EvoDico_xFieldDetails  WHERE FormID=@FormID ORDER BY fpos, ID;

GO







 



CREATE TRIGGER EvoDico_Form_Delete ON EvoDico_Form

FOR DELETE 

AS



DECLARE @FormID  int



SELECT @FormID=ID FROM deleted;



DELETE  

FROM EvoDico_field

WHERE EvoDico_field.formid = @FormID;

 

DELETE  

FROM EvoDico_panel

WHERE EvoDico_panel.formid = @FormID;



GO

 



/*

CREATE TRIGGER [EvoDico_Field_TR_Add] ON [EvoDico_Field] 

FOR INSERT 

AS  



DECLARE  @dbtable nVARCHAR( 100), @dbcolumn  nVARCHAR( 100), @maxlength INT,@typeID INT , @TypeSQL nvarchar(20)



SELECT @dbcolumn=dbcolumn, @maxlength=INSERTED.maxlength, @dbtable=dbtable, @TypeSQL=EvoDico_FieldType.sqlname 

FROM INSERTED, EvoDico_Form, EvoDico_FieldType (nolock)

WHERE  INSERTED.formID=EvoDico_Form.id AND EvoDico_FieldType.ID=INSERTED.TypeID 



select @typeID=id  from sysobjects where name=@dbtable and xtype='u'

if @typeID>0  

	begin

	if not exists (select id from syscolumns where name = @dbcolumn and id=@typeID)

		if @TypeSQL='nvarchar' 

			exec('ALTER TABLE  ['+@dbtable+']  ADD ['+@dbcolumn+'] nvarchar('+@maxlength+') NULL')

		else  

			exec('ALTER TABLE  ['+@dbtable+']  ADD ['+@dbcolumn+'] '+@TypeSQL+' NULL')  

		end

	 

*/

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)


Written By
Olivier_Giulieri
United States United States
I'm a UI engineer with an eye for UX and a passion for model-driven UIs.

I usually build UIs for startups in the San Francisco Bay Area.

My hobby open source project is Evolutility, a minimalist low-code platform with a model-driven UI, a model-driven backend, and a set of models to play with.

More about me on my GitHub page.

Comments and Discussions

Permalink
Advertise
Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Posted 24 Aug 2008

Article Copyright 2008 by Olivier_Giulieri
Everything else Copyright © CodeProject, 1999-2024

Web02 2.8:2024-04-02:1