(mainly oracle) to torque's xml. For some reason, this just does not work for me, so I figured I could update the sql2xml
task and have it generate the xml from my sql scripts.
So, the main changes are:
SQLScanner.java: now reports line number correctly on MS platforms, and recognizes when the minus sign is not used for comments.
SQLToAppData.java: parses ALTER TABLE arguments, CREATE INDEX statements and the CONSTRAINTS keyword in
CREATE TABLE. It now works ok for all my sql scripts+for the scripts that get generated by torque (when given an xml)
The only problem I had was when a column was desclared as LONG VARCHAR, which is easy to fix, but , since I'm new,
I don't know if we should support this type (maybe it's only oracle specific).
Index.java-Foreign.java-Table.java: enable the previous changes + now export xml fields that weren't exported
I hope this can help those that want to convert sql scripts to xml. Respect...
Index: ./src/java/org/apache/torque/engine/database/model/Index.java
===================================================================
RCS file:
/home/cvspublic/jakarta-turbine-torque/src/java/org/apache/torque/engine/database/model/Index.java,v
retrieving revision 1.19
diff -r1.19 Index.java
247a248,255
> * Adds a new column to an index.
> */
> public void addColumn(Column col)
> {
> indexColumns.add(col.getName());
> }
>
> /**
320a329
> The command completed successfully.
Index: ./src/java/org/apache/torque/engine/database/model/ForeignKey.java
===================================================================
RCS file:
/home/cvspublic/jakarta-turbine-torque/src/java/org/apache/torque/engine/database/model/ForeignKey.java,v
retrieving revision 1.9
diff -r1.9 ForeignKey.java
81a82,83
> private final String CASCADE = "CASCADE";
> private final String RESTRICT= "RESTRICT";
116c118,130
< attrib = "SET NULL";
---
> attrib = "setnull";
> }
> else if (attrib.equals(CASCADE))
> {
> attrib = "cascade";
> }
> else if (attrib.equals(NONE))
> {
> attrib = "none";
> }
> else if (attrib.equals(RESTRICT))
> {
> attrib = "restrict";
126c140
< return ! onUpdate.equals(NONE);
---
> return ! (onUpdate==null) && ! onUpdate.equals("none");
134c148
< return ! onDelete.equals(NONE);
---
> return ! (onDelete==null) && ! onDelete.equals("none");
316c330,343
< .append("\">\n");
---
> .append("\"");
> if (hasOnUpdate())
> {
> result.append(" onUpdate=\"")
> .append(getOnUpdate())
> .append("\"");
> }
> if (hasOnDelete())
> {
> result.append(" onDelete=\"")
> .append(getOnDelete())
> .append("\"");
> }
> result.append(">\n");
329a357
>
The command completed successfully.
Index: ./src/java/org/apache/torque/engine/database/transform/SQLToAppData.java
===================================================================
RCS file:
/home/cvspublic/jakarta-turbine-torque/src/java/org/apache/torque/engine/database/transform/SQLToAppData.java,v
retrieving revision 1.7
diff -r1.7 SQLToAppData.java
66a67,68
> import org.apache.torque.engine.database.model.Unique;
> import org.apache.torque.engine.database.model.Index;
76a79,85
> * TODO: does not recognize long varchar as type (maybe assign it to VARCHAR without
>length)
> * FIXED: does not recognize negative values in DEFAULT (assumed as comments) -> in
>SQLScanner.java
> * FIXED: add support for INDEXES
> * FIXED: add support for ALTER TABLE ADD FOREIGN KEY
> * FIXED: supports ALTER TABLE
> * FIXED: supports CONSTRAINTS in CREATE TABLE
> * FIXED: correctly supports UNIQUE
189a199,202
> if (token.getStr().toUpperCase().equals("INDEX"))
> {
> Create_Index();
> }
249a263,268
> // perhaps the next is oracle specific...
> else if(token.getStr().toUpperCase().equals("CONSTRAINT"))
> {
> next();
> Add_Constraint(tbl);
> }
322c341
< c.setUnique(true);
---
> Add_Unique(tbl,c,null);
518c537
< col.setUnique(true);
---
> Add_Unique(tbl,col,null);
530a550,554
> else if(token.getStr().toUpperCase().equals("CHECK"))
> {
> Add_Check(col);
> }
>
550a575,889
> * Parses an ALTER TABLE FOO command.
> *
> * @throws ParseException
> */
> private void Alter() throws ParseException
> {
> next();
> if (token.getStr().toUpperCase().equals("TABLE"))
> {
> Alter_Table();
> }
> }
>
> /**
> * Parses an ALTER TABLE sql command
> */
> private void Alter_Table() throws ParseException
> {
> next();
> String tableName = token.getStr(); // name of the table
> next();
> if (!token.getStr().equals("ADD"))
> {
> err("ADD expected");
> }
> next();
>
> Table tbl=appDataDB.getTable(tableName);
> if (tbl==null)
> {
> err("Invalid table name: " + tableName);
> }
>
> //tbl.setIdMethod("none");
> if (token.getStr().toUpperCase().equals("CONSTRAINT"))
> {
> next();
> Add_Constraint(tbl);
> }
> else if (token.getStr().equals("("))
> {
> next();
> if (token.getStr().toUpperCase().equals("CONSTRAINT"))
> {
> next();
> }
> Add_Constraint(tbl);
> next(); // skip the )
> }
> }
>
> private void Add_Constraint(Table tbl) throws ParseException
> {
> String allConstraints="UNIQUE*PRIMARY*FOREIGN";
> String str=token.getStr();
> String constraintName=null;
>
> if (allConstraints.indexOf(str.toUpperCase())<0)
> {
> constraintName=str;
> next();
> str=token.getStr();
> }
>
> if (str.toUpperCase().equals("UNIQUE"))
> {
> Add_Constraint_Unique(tbl,constraintName);
> }
>
> if (str.toUpperCase().equals("PRIMARY"))
> {
> Add_Constraint_Primary(tbl,constraintName);
> }
>
> if (str.toUpperCase().equals("FOREIGN"))
> {
> Add_Constraint_Foreign(tbl,constraintName);
> }
> }
>
> private void Add_Constraint_Foreign(Table tbl,String constrName) throws
>ParseException
> {
> if (constrName==null)
> constrName="FK_UNDEF_"+token.getLine()+"_"+token.getCol();
>
> next();
> if (!token.getStr().toUpperCase().equals("KEY"))
> {
> err("KEY expected");
> }
> next();
> if (!token.getStr().equals("("))
> {
> err("( expected");
> }
> next();
>
> String colName = token.getStr();
> Column c=tbl.getColumn(colName);
> if (c == null)
> {
> err("Invalid column name: " + colName);
> }
>
> next();
> if (!token.getStr().equals(")"))
> {
> err(") expected");
> }
> next();
> if (!token.getStr().toUpperCase().equals("REFERENCES"))
> {
> err("REFERENCES expected");
> }
> next();
>
> String referenceTable=token.getStr();
> Table refTbl=appDataDB.getTable(referenceTable);
> if (refTbl==null)
> {
> err("Invalid table name: "+referenceTable);
> }
> next();
> String refColumn=colName;
> if (token.getStr().equals("("))
> {
> next();
> refColumn=token.getStr();
> next();
> if (!token.getStr().equals(")"))
> {
> err(") expected");
> }
> }
>
> Column refC=refTbl.getColumn(refColumn);
> if (refC==null)
> {
> err("Invalid column name: " + refColumn);
> }
>
> ForeignKey fk=new ForeignKey();
>
> if (!token.getStr().equals(")"))
> {
> if (!token.getStr().toUpperCase().equals("ON"))
> {
> err("ON expected");
> }
> next();
> if (token.getStr().toUpperCase().equals("DELETE"))
> {
> next();
> String onDelete=token.getStr();
> fk.setOnDelete(onDelete);
> next();
> }
> else if (token.getStr().toUpperCase().equals("UPDATE"))
> {
> next();
> String onUpdate=token.getStr();
> fk.setOnUpdate(onUpdate);
> next();
> }
> }
> //next(); // skip the )
>
> fk.setName(constrName);
> fk.setForeignTableName(referenceTable);
> fk.addReference(colName,refColumn);
> tbl.addForeignKey(fk);
> }
>
> private void Add_Constraint_Primary(Table tbl,String constrName) throws
>ParseException
> {
> if (constrName==null)
> constrName="PK_UNDEF_"+token.getLine()+"_"+token.getCol();
>
> next();
> if (!token.getStr().toUpperCase().equals("KEY"))
> {
> err("KEY expected");
> }
> next();
> if (!token.getStr().equals("("))
> {
> err("( expected");
> }
> next();
> String colName;
> while (!(colName = token.getStr()).equals(")"))
> {
> Column c=tbl.getColumn(colName);
> if (c == null)
> {
> err("Invalid column name: " + colName);
> }
> c.setPrimaryKey(true);
> next();
> if (token.getStr().equals(","))
> next();
> }
> if (!token.getStr().equals(")"))
> {
> err(") expected");
> }
> next(); // skip the )
> }
>
> private void Add_Constraint_Unique(Table tbl,String constrName) throws
>ParseException
> {
> /*if (constrName==null)
> constrName="UNIQUE_UNDEF_"+token.getLine()+"_"+token.getCol();*/
>
> next();
> if (!token.getStr().equals("("))
> {
> err("( expected");
> }
> next();
> String colName=token.getStr();
> Column c=tbl.getColumn(colName);
> if (c == null)
> {
> err("Invalid column name: " + colName);
> }
> Add_Unique(tbl,c,constrName);
> next();
> if (!token.getStr().toUpperCase().equals(")"))
> {
> err(") expected");
> }
> next(); // skip the )
> }
>
> /**
> * Just skip to the end of check
> * @param col
> * @throws ParseException
> */
> private void Add_Check(Column col) throws ParseException
> {
> next();
> int iCount=0;
> while (!token.getStr().equals(";"))
> {
> if (token.getStr().equals("("))
> iCount++;
> else if (token.getStr().equals(")"))
> iCount--;
> next();
> if (iCount==0)
> {
> //next();
> return;
> }
> }
> }
>
> private void Add_Unique(Table tbl,Column col,String constrName)
> {
> col.setUnique(true);
> Unique unique=new Unique();
> unique.setName(constrName);
> unique.addColumn(col);
> tbl.addUnique(unique);
> }
>
> /**
> * Parses a CREATE TABLE sql command
> */
> private void Create_Index() throws ParseException
> {
> next();
> String indexName = token.getStr(); // name of the index
> next();
> if (!token.getStr().toUpperCase().equals("ON"))
> {
> err("on expected");
> }
> next();
> String tableName = token.getStr(); // name of the table
> next();
> if (!token.getStr().equals("("))
> {
> err("( expected");
> }
> next();
> String columnName = token.getStr(); // name of the column
> next();
> String indexType="";
> if (!token.getStr().equals(")"))
> {
> indexType=token.getStr();
> next();
> }
> next(); // skip )
>
> Table tbl=appDataDB.getTable(tableName);
> if (tbl==null)
> {
> err("Invalid table name: "+tableName);
> }
> Column col=tbl.getColumn(columnName);
> if (col==null)
> {
> err("Invalid column name: "+columnName);
> }
> Index index=new Index();
> index.setName(indexName);
> index.addColumn(col);
> tbl.addIndex(index);
> }
>
> /**
578a918,921
> if (token.getStr().toUpperCase().equals("ALTER"))
> {
> Alter();
> }
596a940
>
The command completed successfully.
Index: ./src/java/org/apache/torque/engine/database/model/Table.java
===================================================================
RCS file:
/home/cvspublic/jakarta-turbine-torque/src/java/org/apache/torque/engine/database/model/Table.java,v
retrieving revision 1.34
diff -r1.34 Table.java
1070a1071,1087
> if (indices != null)
> {
>
> for (Iterator iter = indices.iterator(); iter.hasNext(); )
> {
> result.append(iter.next());
> }
> }
>
> if (unices != null)
> {
> for (Iterator iter = unices.iterator(); iter.hasNext(); )
> {
> result.append(iter.next());
> }
> }
>
1170a1188
>
The command completed successfully.
Index: ./src/java/org/apache/torque/engine/sql/SQLScanner.java
===================================================================
RCS file:
/home/cvspublic/jakarta-turbine-torque/src/java/org/apache/torque/engine/sql/SQLScanner.java,v
retrieving revision 1.3
diff -r1.3 SQLScanner.java
120a121
> boolean wasLine=(char)chr=='\r';
125c126,127
< line++;
---
> if (!wasLine || (char) chr != '\n')
> line++;
142c144,162
< tokens.add(new Token(token,line,col));
---
> int start=col-token.length();
> tokens.add(new Token(token,line,start));
> }
>
> /**
> * Scans an identifier.
> */
> private void scanNegativeIdentifier () throws IOException
> {
> token = "-";
> char c = (char) chr;
> while (chr != -1 && white.indexOf(c) == -1 && special.indexOf(c) == -1)
> {
> token = token + (char) chr;
> readChar();
> c = (char) chr;
> }
> int start=col-token.length();
> tokens.add(new Token(token,line,start));
156a177,178
> boolean inNegative;
>
161a184
> inNegative=false;
169a193,197
> else
> {
> inNegative=true;
> c = (char) chr;
> }
211c239,242
< scanIdentifier();
---
> if (inNegative)
> scanNegativeIdentifier();
> else
> scanIdentifier();
225a257
>
The command completed successfully.
-- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
