SQLite Database First Model generation for web2py
Found a great tool to model datbases, the project is here. Interestingly they can be exported to different formats including web2py.
The below script creates the XML for the web tool from a SQLite DB. It uses the sqlite.exe's -csv parameter and Powershell's ConvertFrom-CSV function to easily use the returned data.
[xml] $XMLdocument = @" <?xml version="1.0" encoding="utf-8" ?> <sql> <datatypes db="web2py"> <group label="Numeric" color="rgb(238,238,170)"> <type label="Integer" length="1" sql="integer" re="INTEGER" quote=""/> <type label="Double precision" length="1" sql="double" re="DOUBLE" quote=""/> </group> <group label="Character" color="rgb(255,200,200)"> <type label="String" length="1" sql="string" quote="'"/> <type label="Text" length="1" sql="text" quote="'"/> <type label="BLOB" length="1" sql="blob" quote="'"/> </group> <group label="Date & Time" color="rgb(200,255,200)"> <type label="Time" length="0" sql="time" quote="'"/> <type label="Date" length="0" sql="date" quote="'"/> <type label="Datetime" length="0" sql="datetime" quote="'"/> </group> <group label="Miscellaneous" color="rgb(200,200,255)"> <type label="Boolean" length="0" sql="boolean" quote=""/> <type label="Upload" length="0" sql="upload" quote=""/> <type label="Password" length="0" sql="password" quote=""/> </group> </datatypes> </sql> "@ sqlite3.exe -csv test.mmb "select tbl_name from sqlite_master where type = 'table';" | ConvertFrom-Csv -Header tbl_name | % { #"$($_.tbl_name)"; # create a table node $table_node = $XMLdocument.CreateElement("table") $name = $XMLdocument.CreateAttribute("name") $name.Value = $($_.tbl_name) $table_node.Attributes.Append($name) sqlite3.exe -csv test.mmb "PRAGMA table_info($($_.tbl_name))" | ConvertFrom-Csv -header cid,name,type,notnull,dflt_value,pk | %{ #"`t $($_.name) " # create and append a row node $row_node = $XMLdocument.CreateElement("row") $name = $XMLdocument.CreateAttribute("name") $name.Value = $($_.name) $row_node.Attributes.Append($name) #notnull $null_node = $XMLdocument.CreateAttribute("null") $null_node.Value = $([int] -not ($([int] $_.pk) -or $([int] $_.notnull))) $row_node.Attributes.Append($null_node) $datatype_node = $XMLdocument.CreateElement("datatype") $datatype_node.InnerText = $($_.type) $row_node.AppendChild($datatype_node) $default_node = $XMLdocument.CreateElement("default") if ($($_.dflt_value) -eq "") { $default_value = "NULL" } else { $default_value = ($_.dflt_value) } $default_node.InnerText = $default_value $row_node.AppendChild($default_node) $table_node.AppendChild($row_node) if ($_.pk -eq 1) { # set the primary key value $key_node = $XMLdocument.CreateElement("key") $name = $XMLdocument.CreateAttribute("type") $name.Value = "PRIMARY" $key_node.Attributes.Append($name) $part_node = $XMLdocument.CreateElement("part") $part_node.InnerText = $($_.name) $key_node.AppendChild($part_node) $table_node.AppendChild($key_node) } } $XMLdocument.sql.AppendChild($table_node) } $XMLdocument.Save("d:\model.xml") #ii d:\model.xml'