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.075074495458.py/auto.py
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
-~----------~----~----~----~------~----~------~--~---
