diff options
| -rw-r--r-- | backend/web2py/datatypes.xml | 22 | ||||
| -rw-r--r-- | backend/web2py/output.xsl | 176 | ||||
| -rw-r--r-- | backend/web2py/web2pydesigner.xml | 145 | ||||
| -rw-r--r-- | db/postgresql/output.xsl | 81 | ||||
| -rw-r--r-- | js/config.js | 4 |
5 files changed, 392 insertions, 36 deletions
diff --git a/backend/web2py/datatypes.xml b/backend/web2py/datatypes.xml new file mode 100644 index 0000000..9933b52 --- /dev/null +++ b/backend/web2py/datatypes.xml @@ -0,0 +1,22 @@ +<?xml version="1.0"?>
+<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>
diff --git a/backend/web2py/output.xsl b/backend/web2py/output.xsl new file mode 100644 index 0000000..cf05da0 --- /dev/null +++ b/backend/web2py/output.xsl @@ -0,0 +1,176 @@ +<?xml version="1.0" encoding="us-ascii"?> +<!-- License public domain --> +<!-- Created by Boris Manojlovic <boris DOT manojlovic AT steki DOT net> --> +<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> + <xsl:output method="text"/> + <xsl:template name="replace-substring"> + <xsl:param name="value"/> + <xsl:param name="from"/> + <xsl:param name="to"/> + <xsl:choose> + <xsl:when test="contains($value,$from)"> + <xsl:value-of select="substring-before($value,$from)"/> + <xsl:value-of select="$to"/> + <xsl:call-template name="replace-substring"> + <xsl:with-param name="value" select="substring-after($value,$from)"/> + <xsl:with-param name="from" select="$from"/> + <xsl:with-param name="to" select="$to"/> + </xsl:call-template> + </xsl:when> + <xsl:otherwise> + <xsl:value-of select="$value"/> + </xsl:otherwise> + </xsl:choose> + </xsl:template> + <!-- return length of element--> + <xsl:template name="getsize"> + <xsl:param name="invalue"/> + <xsl:choose> + <xsl:when test="contains($invalue,'(')"> + <xsl:variable name="part" select="substring-after($invalue,'(')"/> + <xsl:value-of select="substring-before($part,')')"/> + </xsl:when> + </xsl:choose> + </xsl:template> + <!-- return web2py python representation of table --> + <xsl:template name="tableparser"> + <xsl:param name="tabledata"/> + <xsl:text>dbOBJECT.define_table("</xsl:text> + <xsl:value-of select="@name"/> + <xsl:text>",</xsl:text> + <xsl:for-each select="row"> + <xsl:choose> + <!-- ignore id fields as web2py needs it for building relations and will create them automatically --> + <xsl:when test="not(@name = 'id')"> + <xsl:text>
 Field("</xsl:text> + <xsl:choose> + <xsl:when test="not (relation)"> + <xsl:value-of select="@name"/> + <xsl:text>", </xsl:text> + <xsl:choose> + <xsl:when test="contains(datatype,'(')"> + <xsl:text>"</xsl:text> + <xsl:value-of select="substring-before(datatype,'(')"/> + <xsl:text>", length=</xsl:text> + <xsl:call-template name="getsize"> + <xsl:with-param name="invalue" select="datatype"/> + </xsl:call-template> + <xsl:text>, </xsl:text> + </xsl:when> + <xsl:otherwise> + <xsl:text>"</xsl:text> + <xsl:value-of select="datatype"/> + <xsl:text>", </xsl:text> + </xsl:otherwise> + </xsl:choose> + <xsl:if test="@null = 0"> + <xsl:text>notnull=True, </xsl:text> + </xsl:if> + <xsl:choose> + <xsl:when test="default"> + <xsl:text>default=</xsl:text> + <xsl:choose> + <xsl:when test="default = 'NULL'"> + <xsl:text>None</xsl:text> + </xsl:when> + <xsl:otherwise> + <xsl:value-of select="default"/> + </xsl:otherwise> + </xsl:choose> + </xsl:when> + <xsl:otherwise> + <xsl:text>default=None</xsl:text> + </xsl:otherwise> + </xsl:choose> + </xsl:when> + <xsl:otherwise> + <xsl:value-of select="@name"/> + <xsl:text>", dbOBJECT.</xsl:text> + <xsl:for-each select="relation"> + <xsl:value-of select="@table"/> + </xsl:for-each> + </xsl:otherwise> + </xsl:choose> + </xsl:when> + </xsl:choose> + <xsl:if test="not (@name = 'id')"> + <xsl:text>)</xsl:text> + </xsl:if> + <xsl:if test="not (position()=last())"> + <xsl:if test="not (@name = 'id')"> + <xsl:text>,</xsl:text> + </xsl:if> + </xsl:if> + </xsl:for-each> + <!-- keys --> + <!-- maybe something else except unique??--> + <xsl:for-each select="key"> + <xsl:choose> + <xsl:when test="@type = 'UNIQUE'">unique=True</xsl:when> + </xsl:choose> + </xsl:for-each> + <xsl:text>)</xsl:text> + <xsl:text>

</xsl:text> + </xsl:template> + <!-- parsing db xml file --> + <xsl:template match="/sql"> + <xsl:text>""" database class object creation (initialization) """
</xsl:text> + <xsl:text>if request.env.web2py_runtime_gae: # if running on Google App Engine 
</xsl:text> + <xsl:text> db = DAL('gae') # connect to Google BigTable 
</xsl:text> + <xsl:text> session.connect(request, response, db=db) # and store sessions and tickets there 
</xsl:text> + <xsl:text>else: # else use a normal relational database 
</xsl:text> + <xsl:text> dbOBJECT = SQLDB("sqlite://dbOBJECT.db")

</xsl:text> + <!-- doing two pass: first ignore tables with relations as they will raise exception if table referenced still does not exist (not instantiated) --> + <!-- this is not bullet proff but should be sufficient for small projects will be in TODO :) --> + <xsl:for-each select="table"> + <xsl:if test="not (row/relation)"> + <xsl:call-template name="tableparser"> + <xsl:with-param name="tabledata" select="table"/> + </xsl:call-template> + </xsl:if> + </xsl:for-each> + <!-- calling second pass ignore non relation tables --> + <xsl:for-each select="table"> + <xsl:if test="row/relation"> + <xsl:call-template name="tableparser"> + <xsl:with-param name="tabledata" select="table"/> + </xsl:call-template> + </xsl:if> + </xsl:for-each> + <!-- end of for-each select="table" --> + <!-- fk --> + <xsl:text>""" Relations between tables (remove fields you don't need from requires) """
</xsl:text> + <!-- dbOBJECT.druga.prva_id.requires=IS_IN_DB(db, 'auth_user.id','%(id)s: %(first_name)s %(last_name)s') --> + <xsl:for-each select="table"> + <xsl:for-each select="row"> + <xsl:for-each select="relation"> + <xsl:variable name="tablename"> + <xsl:value-of select="@table"/> + </xsl:variable> + <xsl:text>dbOBJECT.</xsl:text> + <xsl:value-of select="../../@name"/> + <xsl:text>.</xsl:text> + <xsl:value-of select="../@name"/> + <xsl:text>.requires=IS_IN_DB( dbOBJECT, '</xsl:text> + <xsl:value-of select="@table"/> + <xsl:text>.id', '</xsl:text> + <!-- hardcoded as this is expected from web2py every table to have anyway :) --> + <xsl:for-each select="//table"> + <!-- have to do this way to find our table->row->names--> + <xsl:if test="@name = $tablename"> + <xsl:for-each select="row"> + <xsl:if test="not(@name = 'id') and not(datatype = 'password')"> + <!-- do not show password and id fields by default in select box --> + <xsl:text> %(</xsl:text> + <xsl:value-of select="@name"/> + <xsl:text>)s</xsl:text> + </xsl:if> + </xsl:for-each> + </xsl:if> + </xsl:for-each> + <xsl:text>')
</xsl:text> + </xsl:for-each> + </xsl:for-each> + </xsl:for-each> + </xsl:template> +</xsl:stylesheet> diff --git a/backend/web2py/web2pydesigner.xml b/backend/web2py/web2pydesigner.xml new file mode 100644 index 0000000..76a7e73 --- /dev/null +++ b/backend/web2py/web2pydesigner.xml @@ -0,0 +1,145 @@ +<?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>
+ <table x="498" y="174" name="projects">
+ <row name="id" null="1" autoincrement="1">
+ <datatype>integer</datatype>
+ <default>NULL</default>
+ </row>
+ <row name="name" null="1" autoincrement="0">
+ <datatype>string(50)</datatype>
+ <default>'newproject'</default>
+ </row>
+ <row name="created" null="1" autoincrement="0">
+ <datatype>datetime</datatype>
+ <default>'request.now'</default>
+ </row>
+ <row name="users_id" null="1" autoincrement="0">
+ <datatype>integer</datatype>
+ <default>NULL</default>
+ <relation table="users" row="id"/>
+ </row>
+ <row name="categories_id" null="1" autoincrement="0">
+ <datatype>integer</datatype>
+ <default>NULL</default>
+ <relation table="categories" row="id"/>
+ </row>
+ <row name="locked" null="1" autoincrement="0">
+ <datatype>boolean</datatype>
+ <default>False</default>
+ </row>
+ <key type="PRIMARY" name="">
+ <part>id</part>
+ </key>
+ </table>
+ <table x="244" y="64" name="data">
+ <row name="id" null="1" autoincrement="1">
+ <datatype>integer</datatype>
+ <default>NULL</default>
+ </row>
+ <row name="id_projects" null="1" autoincrement="0">
+ <datatype>integer</datatype>
+ <default>NULL</default>
+ <relation table="projects" row="id"/>
+ </row>
+ <row name="created" null="1" autoincrement="0">
+ <datatype>datetime</datatype>
+ <default>'request.now'</default>
+ </row>
+ <row name="data" null="1" autoincrement="0">
+ <datatype>blob</datatype>
+ </row>
+ <key type="PRIMARY" name="">
+ <part>id</part>
+ </key>
+ </table>
+ <table x="518" y="63" name="tags">
+ <row name="id" null="1" autoincrement="1">
+ <datatype>integer</datatype>
+ <default>NULL</default>
+ </row>
+ <row name="name" null="1" autoincrement="0">
+ <datatype>string</datatype>
+ </row>
+ <key type="PRIMARY" name="">
+ <part>id</part>
+ </key>
+ </table>
+ <table x="721" y="65" name="projects_tags">
+ <row name="id" null="1" autoincrement="1">
+ <datatype>integer</datatype>
+ <default>NULL</default>
+ </row>
+ <row name="tags_id" null="1" autoincrement="0">
+ <datatype>integer</datatype>
+ <default>NULL</default>
+ <relation table="tags" row="id"/>
+ </row>
+ <row name="projects_id" null="1" autoincrement="0">
+ <datatype>integer</datatype>
+ <default>NULL</default>
+ <relation table="projects" row="id"/>
+ </row>
+ <key type="PRIMARY" name="">
+ <part>id</part>
+ </key>
+ </table>
+ <table x="735" y="236" name="categories">
+ <row name="id" null="1" autoincrement="1">
+ <datatype>integer</datatype>
+ <default>NULL</default>
+ </row>
+ <row name="name" null="1" autoincrement="0">
+ <datatype>string</datatype>
+ </row>
+ <key type="PRIMARY" name="">
+ <part>id</part>
+ </key>
+ </table>
+ <table x="265" y="291" name="users">
+ <row name="id" null="1" autoincrement="1">
+ <datatype>integer</datatype>
+ <default>NULL</default>
+ </row>
+ <row name="firstname" null="1" autoincrement="0">
+ <datatype>string(50)</datatype>
+ </row>
+ <row name="lastname" null="1" autoincrement="0">
+ <datatype>string(50)</datatype>
+ </row>
+ <row name="email" null="1" autoincrement="0">
+ <datatype>string</datatype>
+ <datatype>string(80)</datatype>
+ <default>'email@domain.com'</default>
+ </row>
+ <row name="username" null="1" autoincrement="0">
+ <datatype>string(50)</datatype>
+ </row>
+ <row name="password" null="1" autoincrement="0">
+ <datatype>password</datatype>
+ </row>
+ <key type="PRIMARY" name="">
+ <part>id</part>
+ </key>
+ </table>
+</sql>
diff --git a/db/postgresql/output.xsl b/db/postgresql/output.xsl index 4ca7866..39cc978 100644 --- a/db/postgresql/output.xsl +++ b/db/postgresql/output.xsl @@ -26,14 +26,14 @@ <!-- tables --> <xsl:for-each select="table"> - <xsl:text>CREATE TABLE "</xsl:text> + <xsl:text>CREATE TABLE </xsl:text> <xsl:value-of select="@name" /> - <xsl:text>" ( + <xsl:text> ( </xsl:text> <xsl:for-each select="row"> - <xsl:text>"</xsl:text> + <xsl:text> </xsl:text> <xsl:value-of select="@name" /> - <xsl:text>" </xsl:text> + <xsl:text> </xsl:text> <xsl:choose> <xsl:when test="@autoincrement = 1"> @@ -43,23 +43,23 @@ column with nextval(). see: http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL --> - <xsl:text> SERIAL</xsl:text> + <xsl:text>SERIAL</xsl:text> </xsl:when> <xsl:otherwise> <xsl:value-of select="datatype" /> </xsl:otherwise> </xsl:choose> - <xsl:text> </xsl:text> + <xsl:text></xsl:text> <xsl:if test="@null = 0"> - <xsl:text>NOT NULL </xsl:text> + <xsl:text> NOT NULL</xsl:text> </xsl:if> <xsl:if test="default"> <xsl:if test=" default != 'NULL' "> - <xsl:text>DEFAULT </xsl:text> + <xsl:text> DEFAULT </xsl:text> <xsl:value-of select="default" /> - <xsl:text> </xsl:text> + <xsl:text></xsl:text> </xsl:if> </xsl:if> @@ -75,10 +75,21 @@ </xsl:if> </xsl:for-each> +<xsl:text> +); +</xsl:text> +<xsl:text> + +</xsl:text> <!-- keys --> <xsl:for-each select="key"> - <xsl:text>, -</xsl:text> + <xsl:text>ALTER TABLE </xsl:text> + <xsl:text></xsl:text> + <xsl:value-of select="../@name" /> + <xsl:text> </xsl:text> + <xsl:text>ADD CONSTRAINT </xsl:text> + <xsl:value-of select="../@name" /> + <xsl:text>_pkey </xsl:text> <xsl:choose> <xsl:when test="@type = 'PRIMARY'">PRIMARY KEY (</xsl:when> <xsl:when test="@type = 'UNIQUE'">UNIQUE (</xsl:when> @@ -86,18 +97,38 @@ </xsl:choose> <xsl:for-each select="part"> - <xsl:text>"</xsl:text><xsl:value-of select="." /><xsl:text>"</xsl:text> + <xsl:text></xsl:text><xsl:value-of select="." /><xsl:text></xsl:text> <xsl:if test="not (position() = last())"> <xsl:text>, </xsl:text> </xsl:if> </xsl:for-each> - <xsl:text>)</xsl:text> + <xsl:text>); +</xsl:text> </xsl:for-each> - - <xsl:text> -); + + +<!-- fk --> + <xsl:for-each select="row"> + <xsl:for-each select="relation"> + <xsl:text>ALTER TABLE </xsl:text> + <xsl:value-of select="../../@name" /> + <xsl:text> ADD CONSTRAINT </xsl:text> + <xsl:value-of select="../../@name" /> + <xsl:text>_</xsl:text> + <xsl:value-of select="../@name" /> + <xsl:text>_fkey</xsl:text> + <xsl:text> FOREIGN KEY (</xsl:text> + <xsl:value-of select="../@name" /> + <xsl:text>) REFERENCES </xsl:text> + <xsl:value-of select="@table" /> + <xsl:text>(</xsl:text> + <xsl:value-of select="@row" /> + <xsl:text>); </xsl:text> + </xsl:for-each> + </xsl:for-each> + <xsl:if test="comment"> <xsl:text>COMMENT ON TABLE "</xsl:text> @@ -134,23 +165,5 @@ </xsl:text> </xsl:for-each> -<!-- fk --> - <xsl:for-each select="table"> - <xsl:for-each select="row"> - <xsl:for-each select="relation"> - <xsl:text>ALTER TABLE "</xsl:text> - <xsl:value-of select="../../@name" /> - <xsl:text>" ADD FOREIGN KEY ("</xsl:text> - <xsl:value-of select="../@name" /> - <xsl:text>") REFERENCES "</xsl:text> - <xsl:value-of select="@table" /> - <xsl:text>" ("</xsl:text> - <xsl:value-of select="@row" /> - <xsl:text>"); -</xsl:text> - </xsl:for-each> - </xsl:for-each> - </xsl:for-each> - </xsl:template> </xsl:stylesheet> diff --git a/js/config.js b/js/config.js index afcd8b0..09cf99c 100644 --- a/js/config.js +++ b/js/config.js @@ -1,11 +1,11 @@ var CONFIG = { - AVAILABLE_DBS: ["mysql", "sqlite", "web2py", "mssql", "postgresql", "oracle", "sqlalchemy", "vfp9", "cubrid"], + AVAILABLE_DBS: ["mysql", "sqlite", "web2py", "mssql", "postgresql", "oracle", "sqlalchemy", "vfp9", "cubrid", "web2py"], DEFAULT_DB: "mysql", AVAILABLE_LOCALES: ["ar", "cs", "de", "el", "en", "eo", "es", "fr", "hu", "it", "ja", "nl", "pl", "pt_BR", "ro", "ru", "sv", "zh"], DEFAULT_LOCALE: "en", - AVAILABLE_BACKENDS: ["php-mysql", "php-blank", "php-file", "php-sqlite", "php-mysql+file", "php-postgresql", "php-pdo", "perl-file", "php-cubrid", "asp-file"], + AVAILABLE_BACKENDS: ["php-mysql", "php-blank", "php-file", "php-sqlite", "php-mysql+file", "php-postgresql", "php-pdo", "perl-file", "php-cubrid", "asp-file", "web2py"], DEFAULT_BACKEND: ["php-mysql"], RELATION_THICKNESS: 2, |