can you please email it to me as an attachment?
On May 18, 1:52 pm, dlypka <[email protected]> wrote:
> I have the following patch for the SQL Schema to web2py db schema
> conversion utility where the original code is at:
> http://mdp.cti.depaul.edu/AlterEgo/default/download/document.file.075...
>
> This patch corrects these auto.py issues:
>
> 1. spelling error: reponse.flash should be response.flash
> 2. Need to
> import sys
> 3. strip out CR LF and tabs using
> data = re.sub('[\f\n\r\t\v]', '', data)
> 4. item regular expression incorrectly picks up the next create onto
> the final field of first table
> Need to make it stop at the first ')' NOT the last ')' <------
> This is a major fix ----------------
> 5. data.tolower() loses the original case mix of the field names
>
> To Run: (example):
> python auto.py mySQLSchema.sql > mySQLSchema.py
>
> You must first prepare the input by manually removing any of:
>
> "[dbo]."
> "(255)" and similar lengths following varchar
> "ON [PRIMARY]"
> "USE [SomeTable]"
> "GO"
> "/****** Comments ******/"
> "-- Comments"
> "SET ANSI_NULLS ON"
> "SET QUOTED_IDENTIFIER ON"
> "ALTER TABLE [dbo].[mytable] WITH CHECK ADD CONSTRAINT [contraintname]
> FOREIGN KEY([field1]) REFERENCES [dbo].[othertable] ([otherfield])"
>
> The patch is to replace the "auto(data)" method with the following:
>
> ------------------------------------------------------------------------------------
> For Microsoft SQL Server where the keywords are typically UPPERCASE:
>
> def auto(data):
> regex1=re.compile('\s*(?P<name>\S+\s+\S+).*')
> data = re.sub('[\f\n\r\t\v\[\]]', '', data)
> tables={}
> output=''
> for item in re.compile('CREATE\s+TABLE\s+(?P<tablename>\S+)\s*\((?
> P<fields>[^\);]*)(\))+?;?').findall(data):
> tablename=item[0]
> fields=item[1]
> fields2=[regex1.match(i).group('name').split() for i in
> fields.split(',')]
> lastfieldtuple = fields2[-1:]
> if lastfieldtuple[0][0] == 'CONSTRAINT' : fields2 = fields2
> [:-1]
> sfields=''.join(["db.Field('%s','%s'),\n " % (k,convert(v))
> for k,v in fields2 if not k.lower() in ['INDEX','CONSTRAINT']])
> output+="db.define_table('%s',\n %smigrate='%s.mig')\n" %
> (tablename, sfields, tablename)
> tables[tablename]=[k for k,v in fields2]
> labels=','.join(["'%s':T('%s')"%(f,f) for f in tables
> [tablename]])
> output+='%s_labels={%s}\n\n' % (tablename,labels)
> tablenames=tables.keys()
> tablenames.sort()
> model=output
> output=''
> for tablename in tablenames:
> output+="""
>
> -----------------------------------------------------------------------------------------
>
> For other SQL Engines where the Keywords are typically lowercase:
>
> def auto(data):
> regex1=re.compile('\s*(?P<name>\S+\s+\S+).*')
> data = re.sub('[\f\n\r\t\v\[\]]', '', data)
> tables={}
> output=''
> for item in re.compile('create\s+table\s+(?P<tablename>\S+)\s*\((?
> P<fields>[^\);]*)(\))+?;?').findall(data):
> tablename=item[0]
> fields=item[1]
> fields2=[regex1.match(i).group('name').split() for i in
> fields.split(',')]
> lastfieldtuple = fields2[-1:]
> if lastfieldtuple[0][0] == 'constraint' : fields2 = fields2
> [:-1]
> sfields=''.join(["db.Field('%s','%s'),\n " % (k,convert(v))
> for k,v in fields2 if not k.lower() in ['index','constraint']])
> output+="db.define_table('%s',\n %smigrate='%s.mig')\n" %
> (tablename, sfields, tablename)
> tables[tablename]=[k for k,v in fields2]
> labels=','.join(["'%s':T('%s')"%(f,f) for f in tables
> [tablename]])
> output+='%s_labels={%s}\n\n' % (tablename,labels)
> tablenames=tables.keys()
> tablenames.sort()
> model=output
> output=''
> for tablename in tablenames:
> output+="""
> -----------------------------------------------------------------------------------------
> The following Microsoft SQL Server schema is an example which works:
>
> CREATE TABLE [TMoProject]
> (
> [dtCreated] [datetime] NOT NULL,
> [strProjectType] [varchar] NOT NULL,
> [nTMoProjectId] [int] NOT NULL,
> [strProjectDescription] [varchar] NOT NULL,
> [dtLastupdate] [datetime] NOT NULL,
> [strProjectName] [varchar] NOT NULL,
> CONSTRAINT [JDX_PK_TMoProject] PRIMARY KEY CLUSTERED
> (
> [nTMoProjectId] ASC
> ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
> IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
> ON [PRIMARY]
> )
>
> CREATE TABLE [TMoRegion]
> (
> [nTMoRegionId] [int] NOT NULL,
> [strRegionNameRendered] [varchar],
> [strRegionName] [varchar],
> [strRegionDescription] [varchar],
> CONSTRAINT [JDX_PK_TMoRegion] PRIMARY KEY CLUSTERED
> (
> [nTMoRegionId] ASC
> ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
> IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
> ON [PRIMARY]
> )
> ------------------------------------------------------------------------------------------------------------------
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
