001/**
002 *
003 * Copyright (c) 2014, the Railo Company Ltd. All rights reserved.
004 * Copyright (c) 2015, Lucee Assosication Switzerland
005 *
006 * This library is free software; you can redistribute it and/or
007 * modify it under the terms of the GNU Lesser General Public
008 * License as published by the Free Software Foundation; either 
009 * version 2.1 of the License, or (at your option) any later version.
010 * 
011 * This library is distributed in the hope that it will be useful,
012 * but WITHOUT ANY WARRANTY; without even the implied warranty of
013 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
014 * Lesser General Public License for more details.
015 * 
016 * You should have received a copy of the GNU Lesser General Public 
017 * License along with this library.  If not, see <http://www.gnu.org/licenses/>.
018 * 
019 **/
020package lucee.runtime.tag.util;
021
022import java.sql.Types;
023import java.util.ArrayList;
024import java.util.Iterator;
025import java.util.List;
026import java.util.Map.Entry;
027
028import lucee.commons.lang.StringUtil;
029import lucee.runtime.db.SQL;
030import lucee.runtime.db.SQLCaster;
031import lucee.runtime.db.SQLImpl;
032import lucee.runtime.db.SQLItem;
033import lucee.runtime.db.SQLItemImpl;
034import lucee.runtime.exp.ApplicationException;
035import lucee.runtime.exp.DatabaseException;
036import lucee.runtime.exp.PageException;
037import lucee.runtime.op.Caster;
038import lucee.runtime.op.Decision;
039import lucee.runtime.type.Array;
040import lucee.runtime.type.Collection.Key;
041import lucee.runtime.type.Struct;
042import lucee.runtime.type.scope.Argument;
043import lucee.runtime.type.util.KeyConstants;
044import lucee.runtime.type.util.ListUtil;
045
046public class QueryParamConverter {
047
048        public static SQL convert(String sql, Argument params) throws PageException{
049                // All items of arguments will be key-based or position-based so proxy appropriate arrays
050                Iterator<Entry<Key, Object>> it = params.entryIterator();
051                if (it.hasNext()){
052                        Entry<Key, Object> e = it.next();
053                        if(e.getKey().getString() == new String("1")) {
054                                // This indicates the first item has key == 1 therefore treat as array
055                                return convert(sql,Caster.toArray(params));
056                        }
057                }
058                return convert(sql,Caster.toStruct(params));
059        }
060        
061        public static SQL convert(String sql, Struct params) throws PageException{
062                Iterator<Entry<Key, Object>> it = params.entryIterator();
063                List<SQLItems<NamedSQLItem>> namedItems=new ArrayList<SQLItems<NamedSQLItem>>();
064                Entry<Key, Object> e;
065                while(it.hasNext()){
066                        e = it.next();
067                        namedItems.add(toNamedSQLItem(e.getKey().getString(),e.getValue()));
068                }
069                return convert(sql, new ArrayList<SQLItems<SQLItem>>(), namedItems);
070        }
071        
072        public static SQL convert(String sql, Array params) throws PageException{
073                Iterator<Object> it = params.valueIterator();
074                List<SQLItems<NamedSQLItem>> namedItems=new ArrayList<SQLItems<NamedSQLItem>>();
075                List<SQLItems<SQLItem>> items=new ArrayList<SQLItems<SQLItem>>();
076                Object value,paramValue;
077                while(it.hasNext()){
078                        value = it.next();
079
080                        if(Decision.isStruct(value)) {
081                                Struct sct=(Struct) value;
082                                // name (optional)
083                                String name=null;
084                                Object oName=sct.get(KeyConstants._name,null);
085                                if(oName!=null) name=Caster.toString(oName);
086                                
087                                // value (required)
088                                paramValue=sct.get(KeyConstants._value);
089                                
090                                if(StringUtil.isEmpty(name)) {
091                                        items.add(new SQLItems<SQLItem>(new SQLItemImpl(paramValue, Types.VARCHAR),sct));
092                                } else {
093                                        namedItems.add(new SQLItems<NamedSQLItem>(new NamedSQLItem(name, paramValue, Types.VARCHAR),sct));
094                                }
095                        } else {
096                                items.add(new SQLItems<SQLItem>(new SQLItemImpl(value)));
097                        }
098                }
099                return convert(sql, items, namedItems);
100        }
101
102        private static SQLItems<NamedSQLItem> toNamedSQLItem(String name, Object value) throws PageException {
103                if(Decision.isStruct(value)) {
104                        Struct sct=(Struct) value;
105                        // value (required)
106                        value=sct.get(KeyConstants._value);
107                        return new SQLItems<NamedSQLItem>(new NamedSQLItem(name, value, Types.VARCHAR),sct);
108                }
109                return new SQLItems<NamedSQLItem>(new NamedSQLItem(name, value, Types.VARCHAR));
110        }
111        
112
113        private static SQL convert(String sql, List<SQLItems<SQLItem>> items, List<SQLItems<NamedSQLItem>> namedItems) throws ApplicationException , PageException {
114                //if(namedParams.size()==0) return new Pair<String, List<Param>>(sql,params);
115                
116                StringBuilder sb=new StringBuilder();
117                int sqlLen=sql.length(), initialParamSize=items.size();
118                char c,del=0;
119                boolean inside=false;
120                int qm=0,_qm=0;
121                for(int i=0;i<sqlLen;i++){
122                        c=sql.charAt(i);
123                        
124                        if(c=='"' || c=='\'')   {
125                                if(inside) {
126                                        if(c==del) {
127                                                inside=false;
128                                        }
129                                }
130                                else {
131                                        del=c;
132                                        inside=true;
133                                }
134                        }
135                        else if(!inside) {
136
137                                if(c=='?') {
138                                        if(++_qm>initialParamSize) 
139                                                throw new ApplicationException("there are more question marks in the SQL than params defined");
140                                }
141                                else if(c==':') {
142                                        StringBuilder name=new StringBuilder();
143                                        char cc;
144                                        int y=i+1;
145                                        for(;y<sqlLen;y++){
146                                                cc=sql.charAt(y);
147                                                if(!isVariableName(cc, true))break;
148                                                name.append(cc);
149                                        }
150                                        if(name.length()>0) {
151                                                i=y-1;
152                                                c='?';
153                                                items.add( qm , get( name.toString(),namedItems ) );
154                                        }
155                                }
156                        }
157
158                        if(c=='?') {
159                                int len=items.get(qm).size();
160                                for(int j=1;j<=len;j++) {
161                                        if(j>1)sb.append(',');
162                                        sb.append('?');
163                                }
164                                qm++;
165                        } else {
166                                sb.append(c);
167                        }
168                }
169
170                SQLItems<SQLItem> finalItems=flattenItems( items );
171                
172                return new SQLImpl(sb.toString(),finalItems.toArray(new SQLItem[finalItems.size()]));
173        }
174
175        private static SQLItems<SQLItem> flattenItems( List<SQLItems<SQLItem>> items ) {
176                SQLItems<SQLItem> finalItems = new SQLItems<SQLItem>();
177                Iterator<SQLItems<SQLItem>> listsToFlatten = items.iterator();
178                while(listsToFlatten.hasNext()){
179                        finalItems.addAll(listsToFlatten.next());
180                }
181                return finalItems;
182        }
183        
184        public static boolean isVariableName(char c, boolean alsoNumber) {
185                if((c>='a' && c<='z')||(c>='A' && c<='Z')||(c=='_')) return true;
186                if(alsoNumber && (c>='0' && c<='9')) return true;
187                return false;
188        }
189
190
191        private static SQLItems<SQLItem> get(String name, List<SQLItems<NamedSQLItem>> items) throws ApplicationException {
192                Iterator<SQLItems<NamedSQLItem>> it = items.iterator();
193                SQLItems<NamedSQLItem> item;
194                while(it.hasNext()){
195                        item=it.next();
196                        if(item.get(0).name.equalsIgnoreCase(name)) {
197                                return item.convertToSQLItems();
198                        }
199                }
200                throw new ApplicationException("no param with name ["+name+"] found");
201        }
202
203        private static class NamedSQLItem extends SQLItemImpl {
204                public final String name;
205
206                public NamedSQLItem(String name, Object value, int type){
207                        super(value,type);
208                        this.name=name;
209                }
210                
211                public String toString(){
212                        return "{name:"+name+";"+super.toString()+"}";
213                }
214
215                @Override
216                public NamedSQLItem clone(Object object) {
217                        NamedSQLItem item = new NamedSQLItem(name,object,getType());
218                        item.setNulls(isNulls());
219                        item.setScale(getScale());
220                        return item;
221                }
222        }
223
224        private static class SQLItems<T extends SQLItem> extends ArrayList<T> {
225
226                public SQLItems() {}
227
228                public SQLItems(T item) {
229                        add(item);
230                }
231        
232                public SQLItems(T item,Struct sct) throws PageException {
233                        T filledItem = fillSQLItem(item,sct);
234                        Object oList = sct.get(KeyConstants._list,null);
235                        if(oList!=null && Caster.toBooleanValue(oList)){
236                                Object oSeparator = sct.get(KeyConstants._separator,null);
237                                String separator=",";
238                                T clonedItem;
239                                if(oSeparator!=null){
240                                        separator=Caster.toString(oSeparator);
241                                }
242                                String v = Caster.toString(filledItem.getValue());
243                                Array values = ListUtil.listToArrayRemoveEmpty(v,separator);
244                                int len=values.size();
245                                for(int i=1;i<=len;i++) {
246                                        clonedItem = (T) filledItem.clone(values.getE(i));
247                                        add(clonedItem);
248                                }
249                        } else {
250                                add(filledItem);
251                        }
252                }
253
254                private SQLItems<SQLItem> convertToSQLItems() {
255                        Iterator<T> it = iterator();
256                        SQLItems<SQLItem> p = new SQLItems<SQLItem>();
257                        while(it.hasNext()){
258                                p.add(it.next());
259                        }
260                        return p;
261                }
262
263                private T fillSQLItem(T item,Struct sct) throws PageException, DatabaseException {
264
265                        // type (optional)
266                        Object oType=sct.get(KeyConstants._cfsqltype,null);
267                        if(oType==null)oType=sct.get(KeyConstants._sqltype,null);
268                        if(oType==null)oType=sct.get(KeyConstants._type,null);
269                        if(oType!=null) {
270                                item.setType(SQLCaster.toSQLType(Caster.toString(oType)));
271                        }
272                        
273                        // nulls (optional)
274                        Object oNulls=sct.get(KeyConstants._nulls,null);
275
276                        if(oNulls!=null) {
277                                item.setNulls(Caster.toBooleanValue(oNulls));
278                        }
279                        
280                        // scale (optional)
281                        Object oScale=sct.get(KeyConstants._scale,null);
282                        if(oScale!=null) {
283                                item.setScale(Caster.toIntValue(oScale));
284                        }
285
286                        return item;
287                }
288        }
289
290        /*
291         
292        public static void main(String[] args) throws PageException {
293                List<SQLItem> one=new ArrayList<SQLItem>();
294                one.add(new SQLItemImpl("aaa",1));
295                one.add(new SQLItemImpl("bbb",1));
296                
297                List<NamedSQLItem> two=new ArrayList<NamedSQLItem>();
298                two.add(new NamedSQLItem("susi","sorglos",1));
299                two.add(new NamedSQLItem("peter","Petrus",1));
300                
301                SQL sql = convert(
302                                "select ? as x, 'aa:a' as x from test where a=:susi and b=:peter and c=? and d=:susi",
303                                one,
304                                two);
305                
306                print.e(sql);
307
308                // array with simple values
309                Array arr=new ArrayImpl();
310                arr.appendEL("aaa");
311                arr.appendEL("bbb");
312                sql = convert(
313                                "select * from test where a=? and b=?",
314                                arr);
315                print.e(sql);
316                
317                // array with complex values
318                arr=new ArrayImpl();
319                Struct val1=new StructImpl();
320                val1.set("value", "Susi Sorglos");
321                Struct val2=new StructImpl();
322                val2.set("value", "123");
323                val2.set("type", "integer");
324                arr.append(val1);
325                arr.append(val2);
326                sql = convert(
327                                "select * from test where a=? and b=?",
328                                arr);
329                print.e(sql);
330                
331                // array with mixed values
332                arr.appendEL("ccc");
333                arr.appendEL("ddd");
334                sql = convert(
335                                "select * from test where a=? and b=? and c=? and d=?",
336                                arr);
337                print.e(sql);
338                
339                // array mixed with named values
340                Struct val3=new StructImpl();
341                val3.set("value", "456");
342                val3.set("type", "integer");
343                val3.set("name", "susi");
344                arr.append(val3);
345                sql = convert(
346                                "select :susi as name from test where a=? and b=? and c=? and d=?",
347                                arr);
348                print.e(sql);
349                
350                
351                // struct with simple values
352                Struct sct=new StructImpl();
353                sct.set("abc", "Sorglos");
354                sql = convert(
355                                "select * from test where a=:abc",
356                                sct);
357                print.e(sql);
358                
359                // struct with mixed values
360                sct.set("peter", val1);
361                sct.set("susi", val3);
362                sql = convert(
363                                "select :peter as p, :susi as s from test where a=:abc",
364                                sct);
365                print.e(sql);
366                
367                
368        }*/
369        
370}