##|TYPE Template
##|UNIQUEID 91678e47-f988-4621-860e-ccb7e6c486b9
##|TITLE Turbogears Crud
##|NAMESPACE
##|SOURCE_TYPE Source
##|OUTPUT_LANGUAGE C#
##|GUI_ENGINE Microsoft Script
##|GUI_LANGUAGE VBScript
##|GUI_BEGIN
Dim lblAdd
Dim cmbDatabases
Dim cmbTables
Dim lblColumns
Dim lstColumns
Dim lblOrderBy
Dim cmbOrderDir
Dim lblEditColumns
Dim lstEditColumns
Dim lblSearchColumns
Dim chkWebConfig
Dim lblCulture
Dim cmbCulture
Sub setup()
If Not input.Contains("cmbTables") Or Not input.Contains("txtPath") Then
ui.Title = "Turbogears Crud Grid"
ui.Width = 480
ui.Height = 480
' Grab default output path
Dim sOutputPath
DIM sOutputProject
sOutputProject = "project1"
sOutputPath = ""
If input.Contains("defaultOutputPath") Then
sOutputPath = input.Item("defaultOutputPath")
End If
ui.AddLabel "lblPath", "Output file path: ", "Select the output path."
ui.AddTextBox "txtPath", sOutputPath, "Select the Output Path."
ui.AddLabel "lblProject", "ProjectName: ", "Select the Project Name."
ui.AddTextBox "txtProjectName", sOutputProject, "Define your Project Name."
ui.AddFilePicker "btnPath", "Select Path", "Select the Output Path.", "txtPath", true
' List Databases in a ComboBox
ui.AddLabel "lblDatabases", "Select a database:", "Select a database in the dropdown below."
Set cmbDatabases = ui.AddComboBox("cmbDatabase", "Select a database.")
' List Tables in a ComboBox
ui.AddLabel "lblTables", "Select a table:", "Select a table in the dropdown below."
Set cmbTables = ui.AddComboBox("cmbTables", "Select a table.")
cmbTables.Item("Alerts") = "Alerts"
cmbTables.SelectedValue = "Alerts"
cmbTables.AttachEvent "onchange", "cmbTables_onchange"
' List Columns in a listbox
Set lblColumns = ui.AddLabel("lblColumns", "Columns in Grid:", "")
Set lstColumns = ui.AddListBox ("lstColumns", "Select which columns you wish to see in the DataGrid")
' List Columns in a listbox
Set lblEditColumns = ui.AddLabel("lblEditColumns", "Columns for viewing/editing:", "")
Set lstEditColumns = ui.AddListBox ("lstEditColumns", "Select which columns are editable when adding/updating records, or which are viewed when not in admin mode.")
' set list dimensions
lstColumns.Height = 120
lstColumns.Width = 225
lblColumns.Width = 225
lstEditColumns.Height = 120
lstEditColumns.Width = 225
lblEditColumns.Width = 225
' position edit columns list
lstEditColumns.Top = lstColumns.Top
lstEditColumns.Left = lstColumns.Left + lstColumns.Width
lblEditColumns.Top = lblColumns.Top
lblEditColumns.Left = lblColumns.Left + lblColumns.Width
' checkbox for web.config
'Set chkWebConfig = ui.AddCheckBox("chkWebConfig", "Custom Web.Config", true, "Use custom setting in Web.Config to connect to database (not the default dbConnection under appSettings)")
'chkWebConfig.Width = 150
' Attach the onchange event to the cmbDatabases control.
setupDatabaseDropdown cmbDatabases
cmbDatabases.AttachEvent "onchange", "cmbDatabases_onchange"
ui.ShowGUI = true
Else
ui.ShowGUI = false
End if
End Sub
Sub setupDatabaseDropdown(cmbDatabases)
cmbDatabases.BindData MyMeta.Databases
If IsObject(MyMeta.DefaultDatabase) Then
cmbDatabases.SelectedValue = MyMeta.DefaultDatabase.Name
bindTables cmbDatabases.SelectedValue
End If
End Sub
Sub bindTables(sDatabase)
Set db = MyMeta.Databases.Item(sDatabase)
cmbTables.BindData(db.Tables)
End Sub
' Event Handler
Sub cmbDatabases_onchange(control)
Set cmbDatabases = ui.item("cmbDatabase")
bindTables cmbDatabases.SelectedText
End Sub
' Event Handler
Sub cmbTables_onchange(control)
Dim table
Set cmbDatabases = ui.item("cmbDatabase")
Set cmbTables = ui.item("cmbTables")
Set table = MyMeta.Databases.Item(cmbDatabases.SelectedText).Tables.Item(cmbTables.SelectedText)
lstColumns.BindData(table.Columns)
lstEditColumns.BindData(table.Columns)
End Sub
##|GUI_END
##|BODY_MODE Markup
##|BODY_ENGINE Microsoft Script
##|BODY_LANGUAGE VBScript
##|BODY_TAG_START <%
##|BODY_TAG_END %>
##|BODY_BEGIN
<%
' -----------------------------------------------------------------------------
' Turbogears Crud
' Last Update: 18. Juli 2008
' Author: Ivo Looser
'
'-----------------------------------------------------------------------------
Dim cultureFormat
Dim dateRegExp
Dim dateFormat
Dim database
Dim tableName
Dim columnNames
Dim objTable
Dim columnName
Dim editColumnNames
Dim objColumn
Dim tableAlias
Dim primaryKey
Dim primaryKeyAlias
Dim webConfig
Dim orderDir
Dim orderBy
Dim orderByAlias
Dim intLp
Dim foreignTable
Dim buffer
Dim projectName
' get database from combo list
Set database = MyMeta.Databases.Item(input.Item("cmbDatabase"))
' get table name from combolist
tableName = input.Item("cmbTables")
' get list of columns
Set columnNames = input.Item("lstColumns")
Set editColumnNames = input.Item("lstEditColumns")
' get table
Set objTable = database.Tables.Item(tableName)
' get table alias
tableAlias = objTable.Alias
' get primary key for table
primaryKey = objTable.PrimaryKeys.Item(0).Name
' primary key alias
primaryKeyAlias = objTable.PrimaryKeys.Item(0).Alias
projectName = input.item("txtProjectName")
strProcNameInsert = "usp_Insert" & objTable.Name
strProcNameUpdate = "usp_Update" & objTable.Name
strProcNameDelete = "usp_Delete" & objTable.Name
strProcLoadByPrimaryKey = "usp_Get" & objTable.Name & "LoadByPrimaryKey"
For Each objColumn in objTable.Columns
If objColumn.IsInPrimaryKey Then
Dim cPrimaryKey
cPrimaryKey = objColumn
End If
Next
SaveOutput FilePath(CamelCase(tableAlias) + "Controller/templates/__init__.py")
%>from controllers import <%=CamelCase(tableAlias)%>Controller
<%
SaveOutput FilePath(CamelCase(tableAlias) + "Controller/__init__.py")
%>
from turbogears import controllers, expose, redirect
from turbogears import validate, validators, flash, error_handler
from turbogears.database import session
from <%=projectName%>.model import <%=CamelCase(tableAlias)%>
from tw.api import WidgetsList
from tw.forms import DataGrid, TableForm
# fields for Widgets
from tw.forms.fields import *
# Requirements for Logging
import logging
log = logging.getLogger("<%=projectName%>")
# Sample for Date Localization
#from turbogears import i18n
#def getdate(self):
# return i18n.format.format_date(self.date)
# Sample User Callable
#def getuser(self):
# if self.users:
# return self.users.user_name
# else:
# return ""
def FieldFrom<%=CamelCase(tableAlias)%>():
return [
<%
Dim cListString
cListString = ""
' Loop through columns in table
For Each objColumn in objTable.Columns
If InColumns(objColumn) Then
cListString = cListString & " ('" & GetAlias(objColumn) & "','" & objColumn & "')," & vbCrLf
End If
Next
output.write Left(cListString, Len(cListString)-3)
%>
]
class <%=CamelCase(tableAlias)%>Fields(WidgetsList):
<%
For Each objColumn in objTable.Columns
'output.write vbCrLf & " # Column: " & GetAlias(objColumn) & " -> Type: " & objColumn.DataTypeName & "MaxLength: " & objColumn.CharacterMaxLength & vbCrLf
If objColumn.IsInPrimaryKey Then
output.write " " & objColumn & " = HiddenField(name='" & objColumn & "')" & vbCrLf
elseif InEditColumns(objColumn) Then
'output.write "Column Name - " & GetName(objColumn) & ":" & objColumn.DataTypeName & ":len" & objColumn.CharacterMaxLength & vbCrLf
if objColumn.DataTypeName = "int" or objColumn.DataTypeName = "smallint" Then 'and (not objColumn.IsInForeignKey) then 'objColumn.IsNullable
If objColumn.IsNullable Then
output.write " " & objColumn & " = TextField('" & objColumn & "', label='" & GetAlias(objColumn) & ":', attrs={'size':3})" & vbCrLf
else
output.write " " & objColumn & " = TextField('" & objColumn & "', label='" & GetAlias(objColumn) & ":', validator=validators.NotEmpty, attrs={'size':3})" & vbCrLf
End if
elseIf objColumn.DataTypeName = "int" and objColumn.IsInForeignKey Then
' Is ForeignKey
Set objForeignTable = objColumn.ForeignKeys.Item(0).PrimaryTable
output.write " " & objColumn & " = SingleSelectField(""" & objColumn & """, label='" & GetAlias(objColumn) & ":', validator=validators.NotEmpty,"
output.write "options = get" & objForeignTable.Name & "options)" & vbCrLf
ElseIf objColumn.DataTypeName = "DATETIME" Then
If objColumn.IsNullable Then
output.write " " & objColumn & " = CalendarDatePicker(label='" & GetAlias(objColumn) & ":') #CalendarDateTimePicker(label='Datum:', format=""%d.%m.%Y %H:%M"")" & vbCrLf
else
output.write " " & objColumn & " = CalendarDatePicker(label='" & GetAlias(objColumn) & ":',validator=validators.NotEmpty()) #CalendarDateTimePicker(label='Datum:', format=""%d.%m.%Y %H:%M"")" & vbCrLf
End if
ElseIf objColumn.DataTypeName = "TINYINT" then
output.write " " & objColumn & " = CheckBox(label='" & objColumn & ":',validator=validators.Bool())" & vbCrLf
ElseIf objColumn.CharacterMaxLength > 255 Then
If objColumn.IsNullable Then
output.write " " & objColumn & " = TextArea('" & objColumn & "', label='" & GetAlias(objColumn) & ":')" & vbCrLf
Else
output.write " " & objColumn & " = TextArea('" & objColumn & "', label='" & GetAlias(objColumn) & ":', validator=validators.NotEmpty)" & vbCrLf
End if
ElseIf objColumn.CharacterMaxLength <= 255 Then
If objColumn.IsNullable Then
output.write " " & objColumn & " = TextField('" & objColumn & "', label='" & GetAlias(objColumn) & ":')" & vbCrLf
Else
output.write " " & objColumn & " = TextField('" & objColumn & "', label='" & GetAlias(objColumn) & ":', validator=validators.NotEmpty)" & vbCrLf
End if
End If
End If
Next
%>
class <%=CamelCase(tableAlias)%>Schema(WidgetsList):
"""
separate validation schema from the fields definition
make it possible to define a more complex schema
that involves field dependency or logical operators
"""
#group = validators.String(not_empty=True, max=16)
#url = validators.URL(add_http=True, check_exists=True)
class <%=CamelCase(tableAlias)%>Form(TableForm):
#name="Group"
fields = <%=CamelCase(tableAlias)%>Fields()
#validator = <%=CamelCase(tableAlias)%>Schema() # define schema outside of GroupFields
#method="post"
submit_text = "Create"
model_form = <%=CamelCase(tableAlias)%>Form()
class <%=CamelCase(tableAlias)%>Controller(controllers.Controller):
"""Basic model admin interface"""
modelname="<%=CamelCase(tableAlias)%>"
@expose()
def default(self, tg_errors=None):
"""handle non exist urls"""
raise redirect("list")
#require = identity.in_group("admin")
@expose()
def index(self):
raise redirect("list")
@expose(template='genshi:<%=projectName%>.<%=CamelCase(tableAlias)%>Controller.templates.list')
def list(self):
"""List records in model"""
log.debug("List <%=CamelCase(tableAlias)%> ...")
grid = DataGrid("Grid<%=CamelCase(tableAlias)%>",
template='<%=projectName%>.<%=CamelCase(tableAlias)%>Controller.templates.datagrid',
fields=FieldFrom<%=CamelCase(tableAlias)%>())
records = [g for g in session.query(<%=CamelCase(tableAlias)%>)]
return dict(modelname = self.modelname, records = records, context=grid)
@expose(template='genshi:<%=projectName%>.<%=CamelCase(tableAlias)%>Controller.templates.new')
def new(self, **kw):
"""Create new records in model"""
return dict(modelname = self.modelname, context = model_form)
@expose(template='genshi:<%=projectName%>.<%=CamelCase(tableAlias)%>Controller.templates.edit')
def edit(self, id, **kw):
"""Edit record in model"""
try:
record = session.query(<%=CamelCase(tableAlias)%>).get(int(id))
except:
flash = "Not valid edit"
return dict(modelname = self.modelname,
record = record,
context = model_form)
@expose()
def delete(self, id=0):
session.begin()
<%=LCase(tableAlias)%> = session.query(<%=CamelCase(tableAlias)%>).get(id)
session.delete(<%=LCase(tableAlias)%>)
session.commit()
flash("<%=CamelCase(tableAlias)%> was successfully destroyed.")
raise redirect("list")
#@validate(model_form)
@error_handler(new)
@expose()
def saveadd(self, **kw):
"""Create record to model"""
#create
session.begin()
<%=CamelCase(tableAlias)%>(**kw)
session.commit()
flash("<%=CamelCase(tableAlias)%> was successfully created.")
raise redirect("list")
#@validate(model_form)
@error_handler(new)
@expose()
def saveedit(self, id=None, **kw):
"""Save record to model"""
#update
record = session.query(<%=CamelCase(tableAlias)%>).get(int(id))
for attr in kw:
setattr(record, attr, kw[attr])
flash("<%=CamelCase(tableAlias)%> was successfully updated.")
raise redirect("list")
<%
SaveOutput FilePath(CamelCase(tableAlias) + "Controller/controllers.py")
%>
list
<%
SaveOutput FilePath(CamelCase(tableAlias) + "Controller/templates/datagrid.html")
%>
<%
' this function saves the output to disk
Function SaveOutput(fpath)
' save output to file
output.save fpath, false
buffer = buffer & output.text
output.clear
End Function
' return the full path to a file
Function FilePath(filename)
' Save the output file for this Table
Dim fpath
fpath = input.item("txtPath")
' length of file path
Dim length
' position of last \
Dim pos
' get length from input
length = Len(fpath)
' get position of last \
pos = InStrRev(fpath, "\")
' if last \ is not at the end of the file path, it needs adding
If Not pos = length Then
' append \ to path
fpath = fpath & "\"
End If
' ruturn file path with file name appended
FilePath = fpath & filename
End Function
' this function removes characters that are invalid in file names
Function FormatStringForSaving(str)
Dim tname
Dim name
Dim char
Dim j
Dim l
Dim invalid
Dim k
' invalid characters in filename
invalid = "\/:*?""<>|"
name = ""
tname = str
l = Len(tname)
For j = 1 To l
char = Mid(tname, j, 1)
' if current character is not in the invalid list, then it is safe to output
If InStr(invalid,char) = 0 Then
name = name & char
End If
Next
FormatStringForSaving = TrimSpaces(name)
End Function
Function GetAlias(objColumn)
Dim name
name = TrimSpaces(objColumn.Alias)
GetAlias = UCase(Left(name, 1)) & Right(name, Len(name) -1)
End Function
' convert string to camel case - MyTest: myTest
Function CamelCase(str)
CamelCase = UCase(Left(str, 1)) & Right(str, Len(str) -1)
End Function
Function GetName(objColumn)
Dim name
name = objColumn.Name
GetName = UCase(Left(name, 1)) & Right(name, Len(name) -1)
End Function
' in grid column list
Function InColumns(objColumn)
InColumns = False
' Loop through columns selected
For intLp = 0 To columnNames.Count - 1
' current column name from list box
columnName = columnNames(intLp)
' if column name in dropdownlist
If objColumn.Name = columnName Then
InColumns = True
End If
Next
End Function
' in edit column list
Function InEditColumns(objColumn)
InEditColumns = False
' Loop through columns selected
For intLp = 0 To editColumnNames.Count - 1
' current column name from list box
columnName = editColumnNames(intLp)
' if column name in dropdownlist and not primary key
If objColumn.Name = columnName AND objColumn.Name <> primaryKey Then
InEditColumns = True
End If
Next
End Function
' in search column list
Function InSearchColumns(objColumn)
InSearchColumns = False
' Loop through columns selected
For intLp = 0 To searchColumnNames.Count - 1
' current column name from list box
columnName = searchColumnNames(intLp)
' if column name in dropdownlist and not primary key
If objColumn.Name = columnName AND objColumn.Name <> primaryKey Then
InSearchColumns = True
End If
Next
End Function
Function TrimSpaces(str)
Dim tname
Dim name
Dim char
Dim l
name = ""
tname = str
l = Len(tname)
For j = 1 To l
char = Mid(tname, j, 1)
If Not char = " " Then
name = name & char
End If
Next
TrimSpaces = name
End Function
Function BuildCreateAlterStatement(strProcName, alterStatement)
Dim statement
statement = ""
If Not alterStatement Then
' Drop and recreate
statement = statement & "IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('" & strProcName & "') AND sysstat & 0xf = 4)" & vbCRLF
statement = statement & " DROP PROCEDURE [" & strProcName & "];" & vbCRLF
statement = statement & "GO" & vbCRLF & vbCRLF
statement = statement & "CREATE PROCEDURE [" & strProcName & "]"
Else
statement = statement & "ALTER PROCEDURE [" & strProcName & "]"
End If
BuildCreateAlterStatement = statement
End Function
Function GetParameterName(objColumn)
Dim name
name = TrimSpaces(objColumn.Name)
GetParameterName = name
End Function
'Functionname: IIF
'Objective: The IIF Function from Visual Basic.
'Input: cInputBooleanExpression - Boolean Expression
' cReturnIfTrue - Result if i is True
' cReturnIfFalse - Result if i is False
'Output: Return True or False
Function IIf(cInputBooleanExpression,cReturnIfTrue,cReturnIfFalse)
if cInputBooleanExpression Then IIf = cReturnIfTrue Else IIf = cReturnIfFalse
End function
Sub AppendToArray(rg, var)
On Error Resume Next
Dim sizeNew
If IsArray(rg) Then
sizeNew = UBound(rg) + 1
ReDim Preserve rg(sizeNew)
Else
sizeNew = 0
ReDim rg(sizeNew)
End If
If IsObject(var) Then
Set rg(sizeNew) = var
Else
rg(sizeNew) = var
End If
End Sub
%>
##|BODY_END