/// <summary> /// Process /// </summary> /// <returns>message</returns> protected override String DoIt() { int viewNum = 0; //get SQLs //InputStream iin = null; StreamReader inn = null; String targetViewName = null; _AD_Table_ID = (GetProcessInfo() != null) ? GetRecord_ID() : 0; if (_AD_Table_ID > 0) { MTable targetTable = MTable.Get(Env.GetCtx(), _AD_Table_ID); targetViewName = targetTable.GetTableName(); entityType = targetTable.GetEntityType(); } try { inn = new StreamReader(SQLfile);// FileInputStream(SQLfile); } catch (Exception e) { log.Severe(e.Message); throw new Exception("SQL file error. file name = " + SQLfile); } if (inn == null) { throw new Exception("SQL file error. file name = " + SQLfile); } List <String> commands = ViewUtil.ReadSqlFromFile(SQLfile); if (commands == null) { throw new Exception("SQL file error. file name = " + SQLfile); } //put SQL into AD // Initialization String tableName = null; List <String> colName = new List <String>(); List <String> colSQL = new List <String>(); List <String> colType = new List <String>(); Trx myTrx = Trx.Get("ImportView"); String Acommand = null; // endSQL: try { foreach (String command in commands) { Acommand = command; bool colNameDone = false; int ir = command.IndexOf('\t'); while (ir > 0) { Acommand = Replace(Acommand, '\t', ' '); ir = command.IndexOf('\t'); } // Not care about "exit" if (command.Equals("exit") || command.Equals("EXIT")) { // endSQL; break; } if (command.StartsWith("DROP VIEW ")) { continue; } tableName = null; colName.Clear(); int iView = command.IndexOf(" VIEW "); //eachSQL: if (command.StartsWith("CREATE") && iView > 0) { int iSel = command.IndexOf("SELECT "); String tc = command.Substring(iView + 6, iSel); int ileft = tc.IndexOf('('); int iright = tc.IndexOf(')'); if (ileft > 0) { tableName = tc.Substring(0, ileft); tc = tc.Substring(ileft + 1, iright); String[] colNames = tc.Split(new Char[] { ',' });// tc.Substring(ileft + 1, iright).Split(","); foreach (String cn in colNames) { colName.Add(Trim(cn)); } colNameDone = true; } else { tableName = tc.Substring(0, tc.IndexOf(" AS ")); } if (tableName != null) { //jz String trim() doesn't remove tail space tableName.trim(); tableName = Trim(tableName); } if (tableName == null || tableName.Length == 0) { log.Severe("No view name from the SQL: " + command); continue; } if (_AD_Table_ID != 0 && !(targetViewName.Equals(tableName) || targetViewName.Equals(tableName.ToUpper()))) { log.Fine("Skipping view " + targetViewName); continue; } //int itc = tc.substring(tc.length()-1).hashCode(); //insert into/update ad_table for each view MTable mt = MTable.Get(Env.GetCtx(), tableName); if (mt != null && !mt.IsView()) { log.Severe("Duplicated view name with an existing table for the SQL: " + command); //myTrx.rollback(); // eachSQL; break; } if (mt == null) //mt.delete(true, null); { mt = new MTable(Env.GetCtx(), 0, null); //mt.delete(true, myTrx.getTrxName()); //mt = new MTable(Env.getCtx(), 0, myTrx.getTrxName()); //MTable mt = MTable.get(Env.getCtx(), 0); mt.SetTableName(tableName); //mt.setAD_Org_ID(0); //mt.setAD_Client_ID(0); also updatedby, createdby. jz: all default is 0 in PO mt.SetAccessLevel(X_AD_Table.ACCESSLEVEL_ClientPlusOrganization); mt.SetEntityType(entityType); mt.SetIsActive(true); mt.SetIsView(true); mt.SetName("View_" + tableName); mt.SetLoadSeq(900); mt.SetImportTable(null); if (!mt.Save()) { log.Severe("Unable to insert into AD_Table for the SQL: " + command); //myTrx.rollback(); //eachSQL; break; } log.Info("Add " + tableName + " into AD_Table for the SQL: " + command); } //clean view components and their columns String vcdel = "DELETE FROM AD_ViewComponent WHERE (AD_Table_ID, AD_Client_ID) IN (SELECT AD_Table_ID, AD_Client_ID FROM AD_Table WHERE TableName = '" + tableName + "')"; try { DataBase.DB.ExecuteQuery(vcdel, null); } catch (Exception e) { log.Log(Level.SEVERE, vcdel, e); } //insert into ad_viewComponent for each union part Acommand = command.Substring(iSel); //String[] selects =command.Split(new char[]{','});//UNION'},0); // currently does not handle other set operators (e.g. INTERSECT) Regex reg = new Regex(Acommand); String[] selects = reg.Split("UNION"); for (int i = 0; i < selects.Length; i++) { int iFrom = FindNext(selects[i], " FROM "); if (iFrom < 0) { log.Severe("No from clause from the SQL: " + command); //eachSQL; break; } colSQL.Clear(); colType.Clear(); int isel = selects[i].IndexOf("SELECT "); String colstr = selects[i].Substring(isel + 7, iFrom); colstr = colstr + ",";// concat(","); int iComma = FindNext(colstr, ","); if (iComma == 0) { log.Severe("No view column from the SQL: " + command); ///eachSQL; break; } int iPrevComma = -1; while (iComma != iPrevComma) { String column = colstr.Substring(iPrevComma + 1, iComma); int iAS = column.LastIndexOf(" AS "); String cs = null; if (iAS > 0) { if (i == 0 && !colNameDone) { colName.Add(column.Substring(iAS + 4, column.Length).Trim()); } cs = column.Substring(0, iAS); //colSQL.add(cols[j].substring(0, iAS)); } else { cs = column; if (i == 0 && !colNameDone) { int iDot = column.IndexOf('.'); String cn = column; if (iDot > 0) { cn = column.Substring(iDot + 1, column.Length).Trim(); } colName.Add(cn); } } cs = cs.Trim(); if (cs.Equals("NULLIF(1,1)") || cs.Equals("nullif(1,1)")) { cs = null; colType.Add("I"); } else if (cs.Equals("NULLIF('A','A')") || cs.Equals("nullif('A','A')")) { cs = null; colType.Add("V"); } else { colType.Add(null); } colSQL.Add(cs); iPrevComma = iComma; if (iPrevComma + 1 < colstr.Length) { iComma = iPrevComma + 1 + FindNext(colstr.Substring(iPrevComma + 1), ","); } }// while (iComma != iPrevComma) String from = selects[i].Substring(iFrom + 1, selects[i].Length); int iWH = from.IndexOf(" WHERE "); String where = null; String others = null; int iGROUP = -1; int iORDER = -1; if (iWH > 0) { where = from.Substring(iWH + 1, from.Length); from = from.Substring(0, iWH); iGROUP = where.IndexOf(" GROUP BY "); iORDER = where.IndexOf(" ORDER BY "); if (iORDER > 0 && iGROUP == -1) { iGROUP = iORDER; } if (iGROUP > 0) { others = where.Substring(iGROUP + 1, where.Length); where = where.Substring(0, iGROUP); } } else { iGROUP = from.IndexOf(" GROUP BY "); iORDER = from.IndexOf(" ORDER BY "); if (iORDER > 0 && iGROUP == -1) { iGROUP = iORDER; } if (iGROUP > 0) { others = from.Substring(iGROUP + 1, from.Length); from = from.Substring(0, iGROUP); } } if (from == null || from.Length == 0) { log.Severe("No from clause from the SQL: " + command); //myTrx.rollback(); // eachSQL; break; } //insert into AD_ViewComponent //MViewComponent mvc = new MViewComponent(Env.getCtx(), 0, myTrx.getTrxName()); MViewComponent mvc = new MViewComponent(Env.GetCtx(), 0, null); mvc.SetName("VC_" + tableName); mvc.SetAD_Table_ID(mt.Get_ID()); mvc.SetSeqNo((i + 1) * 10); mvc.SetIsActive(true); mvc.SetEntityType(entityType); //mvc.setAD_Org_ID(0); //mvc.setReferenced_Table_ID(mt.get_ID()); String from1 = from.Substring(5); from1 = Trim(from1); int rtix = from1.IndexOf(' '); if (rtix < 0) { rtix = from1.Length; } String refTab = from1.Substring(0, rtix); refTab = Trim(refTab); MTable rt = MTable.Get(Env.GetCtx(), refTab); if (rt != null) { mvc.SetReferenced_Table_ID(rt.Get_ID()); } else { mvc.SetReferenced_Table_ID(0); } mvc.SetFromClause(from); mvc.SetWhereClause(where); mvc.SetOtherClause(others); if (!mvc.Save()) { log.Severe("unable to create view component " + i + ": " + command); //myTrx.rollback(); //eachSQL; break; } //insert into AD_ViewColumn MViewColumn mvcol = null; for (int j = 0; j < colName.Count; j++) { //mvcol = new MViewColumn(Env.getCtx(), 0, myTrx.getTrxName()); mvcol = new MViewColumn(Env.GetCtx(), 0, null); //mvcol.setAD_Org_ID(0); mvcol.SetAD_ViewComponent_ID(mvc.Get_ID()); mvcol.SetIsActive(true); mvcol.SetEntityType(entityType); log.Info("Importing View " + tableName + "(i,j) = (" + i + ", " + j + ")"); mvcol.SetDBDataType(colType[j]); //.get(j)); mvcol.SetColumnName(colName[j]); //.get(j)); mvcol.SetColumnSQL(colSQL[j]); //.get(j)); if (!mvcol.Save()) { log.Severe("unable to create view component " + i + " column: " + colName[j] + " in " + command); //myTrx.rollback(); break; //eachSQL; } } }//for selects //myTrx.commit(); log.Info("Impored view: " + tableName); }//handle create view else { log.Warning("Ignore non create view SQL: " + command); continue; } viewNum++; } // for (String command : commands) } catch (Exception e) { log.Severe("Error at importing view SQL: " + Acommand + " \n " + e); } finally { if (myTrx != null && myTrx.IsActive()) { myTrx.Rollback(); myTrx.Close(); } } if (_AD_Table_ID > 0) { if (viewNum == 0) { return("Not able to import view " + targetViewName + " from " + SQLfile); } else { return("Created view " + targetViewName); } } return("Imported View #" + viewNum); }