Java > Open Source Codes > com > caucho > quercus > lib > db > MysqlModule


1 /*
2  * Copyright (c) 1998-2006 Caucho Technology -- all rights reserved
3  *
4  * This file is part of Resin(R) Open Source
5  *
6  * Each copy or derived work must preserve the copyright notice and this
7  * notice unmodified.
8  *
9  * Resin Open Source is free software; you can redistribute it and/or modify
10  * it under the terms of the GNU General Public License as published by
11  * the Free Software Foundation; either version 2 of the License, or
12  * (at your option) any later version.
13  *
14  * Resin Open Source is distributed in the hope that it will be useful,
15  * but WITHOUT ANY WARRANTY; without even the implied warranty of
16  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE, or any warranty
17  * of NON-INFRINGEMENT. See the GNU General Public License for more
18  * details.
19  *
20  * You should have received a copy of the GNU General Public License
21  * along with Resin Open Source; if not, write to the
22  *
23  * Free Software Foundation, Inc.
24  * 59 Temple Place, Suite 330
25  * Boston, MA 02111-1307 USA
26  *
27  * @author Scott Ferguson
28  */
29
30 package com.caucho.quercus.lib.db;
31
32 import com.caucho.quercus.annotation.NotNull;
33 import com.caucho.quercus.annotation.Optional;
34 import com.caucho.quercus.annotation.ReturnNullAsFalse;
35 import com.caucho.quercus.env.*;
36 import com.caucho.quercus.module.AbstractQuercusModule;
37 import com.caucho.util.L10N;
38 import com.caucho.util.Log;
39
40 import java.sql.Driver ;
41 import java.sql.DriverManager ;
42 import java.sql.ResultSetMetaData ;
43 import java.sql.SQLException ;
44 import java.sql.Statement ;
45 import java.sql.Types ;
46 import java.util.logging.Level ;
47 import java.util.logging.Logger ;
48
49
50 /**
51  * PHP mysql routines.
52  */
53 public class MysqlModule extends AbstractQuercusModule {
54
55   private static final Logger log = Log.open(MysqlModule.class);
56   private static final L10N L = new L10N(MysqlModule.class);
57
58   public static final int MYSQL_ASSOC = 0x1;
59   public static final int MYSQL_NUM = 0x2;
60   public static final int MYSQL_BOTH = 0x3;
61
62   public static final int MYSQL_USE_RESULT = 0x0;
63   public static final int MYSQL_STORE_RESULT = 0x1;
64
65   public MysqlModule()
66   {
67   }
68
69   /**
70    * Returns true for the mysql extension.
71    */
72   public String []getLoadedExtensions()
73   {
74     return new String [] { "mysql" };
75   }
76
77   /**
78    * Returns the number of affected rows.
79    */
80   public int mysql_affected_rows(Env env, @Optional Mysqli conn)
81   {
82     if (conn == null)
83       conn = getConnection(env);
84
85     return conn.affected_rows();
86   }
87
88   /**
89    * Returns the client encoding
90    */
91   public String mysql_client_encoding(Env env, @Optional Mysqli conn)
92   {
93     if (conn == null)
94       conn = getConnection(env);
95
96     return conn.client_encoding();
97   }
98
99   /**
100    * Closes a mysql connection.
101    */
102   public boolean mysql_close(Env env, @Optional Mysqli conn)
103   {
104     if (conn == null)
105       conn = getConnection(env);
106
107     if (conn != null) {
108       if (conn == getConnection(env))
109         env.removeSpecialValue("caucho.mysql");
110
111       conn.close(env);
112
113       return true;
114     }
115     else
116       return false;
117   }
118
119   /**
120    * Creates a database.
121    */
122   public boolean mysql_create_db(Env env, @NotNull String name, @Optional Mysqli conn)
123   {
124     if (name == null)
125       return false;
126
127     if (conn == null)
128       conn = getConnection(env);
129
130     Statement stmt = null;
131
132     // XXX: move implementation
133 try {
134       try {
135         stmt = conn.validateConnection().getConnection().createStatement();
136         stmt.setEscapeProcessing(false);
137         stmt.executeUpdate("CREATE DATABASE " + name);
138       } finally {
139         if (stmt != null)
140           stmt.close();
141       }
142     } catch (SQLException e) {
143       log.log(Level.FINE, e.toString(), e);
144       return false;
145     }
146
147     return true;
148   }
149
150   /**
151    * Moves the intenal row pointer of the MySQL result to the
152    * specified row number, 0 based.
153    */
154   public boolean mysql_data_seek(Env env,
155                                  @NotNull MysqliResult result,
156                                  int rowNumber)
157   {
158     if (result == null)
159       return false;
160
161     if (result.seek(env, rowNumber)) {
162       return true;
163     } else {
164       env.warning(L.l("Offset {0} is invalid for MySQL (or the query data is unbuffered)",
165                       rowNumber));
166       return false;
167     }
168   }
169
170   /**
171    * Retrieves the database name after a call to mysql_list_dbs()
172    */
173   public Value mysql_db_name(Env env,
174                              @NotNull MysqliResult result,
175                              int row,
176                              @Optional("0") Value field)
177   {
178     if (result == null)
179       return BooleanValue.FALSE;
180
181     return mysql_result(env, result, row, field);
182   }
183
184   /**
185    * Returns the value of one field in the result set.
186    */
187   public Value mysql_result(Env env,
188                             @NotNull MysqliResult result,
189                             int row,
190                             @Optional("0") Value field)
191   {
192     if (result == null)
193       return BooleanValue.FALSE;
194
195     return result.getResultField(env, row, field);
196   }
197
198   /**
199    * Drops a database.
200    */
201   public boolean mysql_drop_db(Env env,
202                                @NotNull String databaseName,
203                                @Optional Mysqli conn)
204   {
205     if (databaseName == null)
206       return false;
207
208     Value value = mysql_query(env, "DROP DATABASE " + databaseName, conn);
209
210     return (value != null && value.toBoolean());
211   }
212
213   /**
214    * Returns the error number of the most recent error
215    */
216   public int mysql_errno(Env env, @Optional Mysqli conn)
217   {
218     if (conn == null)
219       conn = getConnection(env);
220
221     return conn.errno();
222   }
223
224   /**
225    * Returns the most recent error.
226    */
227   public String mysql_error(Env env, @Optional Mysqli conn)
228   {
229     if (conn == null)
230       conn = getConnection(env);
231
232     String error = conn.error();
233
234     if (error == null)
235       return "";
236
237     return error;
238   }
239
240   public Value mysql_escape_string(Env env, StringValue unescapedString)
241   {
242     return mysql_real_escape_string(env, unescapedString, null);
243   }
244
245   /**
246    * Escapes special characters.
247    *
248    * @see String QuercusMysqliModule.mysqli_real_escape_string(JdbcConnectionResource, String)
249    *
250    * @return the escaped string
251    */
252
253   public Value mysql_real_escape_string(Env env,
254                                         StringValue unescapedString,
255                                         @Optional Mysqli conn)
256   {
257     if (conn == null)
258       conn = getConnection(env);
259
260     return conn.real_escape_string(unescapedString);
261   }
262
263   /**
264    * Returns a row from the connection
265    */
266   public Value mysql_fetch_array(Env env,
267                                  @NotNull MysqliResult result,
268                                  @Optional("MYSQL_BOTH") int type)
269   {
270     if (result == null)
271       return BooleanValue.FALSE;
272
273     Value value = result.fetch_array(env, type);
274
275     if (value != null)
276       return value;
277     else
278       return BooleanValue.FALSE;
279   }
280
281   /**
282    * Returns a row from the connection
283    */
284   @ReturnNullAsFalse
285   public ArrayValue mysql_fetch_assoc(Env env, @NotNull MysqliResult result)
286   {
287     if (result == null)
288       return null;
289
290     return result.fetch_array(env, MYSQL_ASSOC);
291   }
292
293   /**
294    * Returns an object containing field information.
295    * On success, this method increments the field offset
296    * (see {@link #mysql_field_seek}).
297    *
298    * <h3>ERRATA</h3>
299    * <ul>
300    * <li>quercus returns "string" for BIT type, php returns "unknown"
301    * <li>quercus always returns int(0) for unique_key
302    * <li>quercus always returns int(0) for zerofill
303    * <li>quercus always returns int(0) for multiple_key
304    * </ul>
305    *
306    */
307   public Value mysql_fetch_field(Env env,
308                                  @NotNull MysqliResult result,
309                                  @Optional("-1") int fieldOffset)
310   {
311     /**
312      * ERRATA is also documented in php/142s.qa
313      * There is probably a mysql specific query or API that would be better
314      * for getting this information
315      */
316
317     if (result == null)
318       return BooleanValue.FALSE;
319
320     // php/142v.qa - call must succeed even if some info not available
321
322     try {
323       if (fieldOffset == -1) {
324         fieldOffset = result.field_tell(env);
325         result.setFieldOffset(fieldOffset + 1);
326       }
327
328       ResultSetMetaData md = result.getMetaData();
329
330       if (md.getColumnCount() <= fieldOffset || fieldOffset < 0) {
331         env.invalidArgument("field", fieldOffset);
332         return BooleanValue.FALSE;
333       }
334
335       int jdbcField = fieldOffset + 1;
336       int jdbcColumnType = md.getColumnType(jdbcField);
337
338       String catalogName = md.getCatalogName(jdbcField);
339       String tableName = md.getTableName(jdbcField);
340       String columnName = md.getColumnName(jdbcField);
341
342       // some information is not available from the ResultSetMetaData
343 JdbcColumnMetaData columnMd = null;
344
345       JdbcConnectionResource conn = getConnection(env).validateConnection();
346
347       JdbcTableMetaData tableMd
348         = conn.getTableMetaData(catalogName, null, tableName);
349
350       if (tableMd != null)
351         columnMd = tableMd.getColumn(columnName);
352
353       // XXX: maxlen note from PHP comments:
354 // the length of the longest value for that field in the returned dataset,
355 // NOT the maximum length of data that column is designed to hold.
356
357       int maxLength = 0;
358       int notNull = md.isNullable(jdbcField) == ResultSetMetaData.columnNullable ? 0 : 1;
359       int numeric = JdbcColumnMetaData.isNumeric(jdbcColumnType) ? 1 : 0;
360       int blob = JdbcColumnMetaData.isBlob(jdbcColumnType) ? 1 : 0;
361       String type = JdbcResultResource.getColumnPHPName(jdbcColumnType);
362       int unsigned = md.isSigned(jdbcField) ? 0 : numeric;
363
364       if (jdbcColumnType == Types.BOOLEAN || jdbcColumnType == Types.BIT)
365         unsigned = 0;
366       else if (jdbcColumnType == Types.DECIMAL)
367         numeric = 1;
368
369       int zerofill = 0;
370       int primaryKey = 0;
371       int multipleKey = 0;
372       int uniqueKey = 0;
373
374       if (columnMd != null) {
375         zerofill = columnMd.isZeroFill() ? 1 : 0;
376         primaryKey = columnMd.isPrimaryKey() ? 1 : 0;
377         // XXX: not sure what multipleKey is supposed to be
378 // multipleKey = columnMd.isIndex() && !columnMd.isPrimaryKey() ? 1 : 0;
379 uniqueKey = columnMd.isUnique() ? 1 : 0;
380       }
381       else
382         notNull = 1;
383
384       ObjectValue fieldResult = env.createObject();
385
386       fieldResult.putField("name", columnName);
387       fieldResult.putField("table", tableName);
388       fieldResult.putField("def", "");
389       fieldResult.putField("max_length", maxLength);
390       fieldResult.putField("not_null", notNull);
391       fieldResult.putField("primary_key", primaryKey);
392       fieldResult.putField("multiple_key", multipleKey);
393       fieldResult.putField("unique_key", uniqueKey);
394       fieldResult.putField("numeric", numeric);
395       fieldResult.putField("blob", blob);
396       fieldResult.putField("type", type);
397       fieldResult.putField("unsigned", unsigned);
398       fieldResult.putField("zerofill", zerofill);
399
400       return fieldResult;
401     } catch (SQLException e) {
402       log.log(Level.FINE, e.toString(), e);
403       return BooleanValue.FALSE;
404     }
405   }
406
407   /**
408    * Executes a query and returns a result set.
409    *
410    * Returns true on update success, false on failure, and a result set
411    * for a successful select
412    */
413   public Value mysql_query(Env env, String sql, @Optional Mysqli conn)
414   {
415     if (conn == null)
416       conn = getConnection(env);
417
418     return conn.query(env, sql, MYSQL_STORE_RESULT);
419   }
420
421   /**
422    * Returns an array of lengths.
423    */
424   public Value mysql_fetch_lengths(Env env, @NotNull MysqliResult result)
425   {
426     if (result == null)
427       return BooleanValue.FALSE;
428
429     return result.fetch_lengths();
430   }
431
432   /**
433    * Returns an object with properties that correspond to the fetched row
434    * and moves the data pointer ahead.
435    */
436   public Value mysql_fetch_object(Env env, @NotNull MysqliResult result)
437   {
438     if (result == null)
439       return BooleanValue.FALSE;
440
441     return result.fetch_object(env);
442   }
443
444   /**
445    * Returns a row from the connection
446    */
447   @ReturnNullAsFalse
448   public ArrayValue mysql_fetch_row(Env env, @NotNull MysqliResult result)
449   {
450     if (result == null)
451       return null;
452
453     return result.fetch_row(env);
454   }
455
456   /**
457    * Returns the field flags of the specified field. The flags are reported as
458    * a space separated list of words, the returned value can be split using explode().
459    *
460    * The following flages are reported, older version of MySQL may not report all flags:
461    * <ul>
462    * <li> not_null
463    * <li> primary_key
464    * <li> multiple_key
465    * <li> blob
466    * <li> unsigned
467    * <li> zerofill
468    * <li> binary
469    * <li> enum
470    * <li> auto_increment
471    * <li> timestamp
472    * </ul>
473    */
474   public Value mysql_field_flags(Env env,
475                                  @NotNull MysqliResult result,
476                                  int fieldOffset)
477   {
478     if (result == null)
479       return BooleanValue.FALSE;
480
481     Value fieldName = result.getFieldName(env, fieldOffset);
482
483     if (fieldName == BooleanValue.FALSE)
484       return BooleanValue.FALSE;
485
486     Value fieldTable = result.getFieldTable(env, fieldOffset);
487
488     if (fieldTable == BooleanValue.FALSE)
489       return BooleanValue.FALSE;
490
491     String sql = "SHOW FULL COLUMNS FROM " + fieldTable.toString() + " LIKE \'" + fieldName.toString() + "\'";
492
493     Mysqli conn = getConnection(env);
494
495     Object metaResult = conn.validateConnection().realQuery(sql);
496
497     if (metaResult instanceof MysqliResult)
498       return ((MysqliResult) metaResult).getFieldFlags();
499
500     return BooleanValue.FALSE;
501   }
502
503   /**
504    * Returns field name at given offset.
505    */
506   public Value mysql_field_name(Env env,
507                                 @NotNull MysqliResult result,
508                                 int fieldOffset)
509   {
510     if (result == null)
511       return BooleanValue.FALSE;
512
513     return result.getFieldName(env, fieldOffset);
514   }
515
516   /**
517    * Seeks to the specified field offset, the field offset is
518    * is used as the default for the next call to {@link #mysql_fetch_field}.
519    */
520   public boolean mysql_field_seek(Env env,
521                                   @NotNull MysqliResult result,
522                                   int fieldOffset)
523   {
524     if (result == null)
525       return false;
526
527     return result.field_seek(env, fieldOffset);
528   }
529
530   /**
531    * Returns the table corresponding to the field.
532    */
533   public Value mysql_field_table(Env env,
534                                  @NotNull MysqliResult result,
535                                  int fieldOffset)
536   {
537     if (result == null)
538       return BooleanValue.FALSE;
539
540     return result.getFieldTable(env, fieldOffset);
541   }
542
543   /**
544    * Returns the field type.
545    */
546   public static Value mysql_field_type(Env env,
547                                        @NotNull MysqliResult result,
548                                        Value fieldOffset)
549   {
550     if (result == null) {
551       return NullValue.NULL;
552     }
553
554     if (! fieldOffset.isset())
555       return NullValue.NULL;
556
557     return result.getFieldType(env, fieldOffset.toInt());
558   }
559
560   /**
561    * Deprecated alias for mysql_field_len.
562    */
563   public static Value mysql_fieldlen(Env env,
564                                      @NotNull MysqliResult result,
565                                      int fieldOffset)
566   {
567     return mysql_field_len(env, result, fieldOffset);
568   }
569
570   /**
571    * Returns the length of the specified field
572    */
573   public static Value mysql_field_len(Env env,
574                                       @NotNull MysqliResult result,
575                                       int fieldOffset)
576   {
577     if (result == null)
578       return BooleanValue.FALSE;
579
580     // ERRATUM: Returns 10 for datatypes DEC and NUMERIC instead of 11
581
582     return result.getFieldLength(env, fieldOffset);
583   }
584
585   /**
586    * Frees a mysql result.
587    */
588   public boolean mysql_free_result(MysqliResult result)
589   {
590     if (result != null)
591       result.close();
592
593     return true;
594   }
595
596   /**
597    * Returns the MySQL client version.
598    */
599   public static String mysql_get_client_info(Env env)
600   {
601     try {
602       Driver driver = DriverManager.getDriver("jdbc:mysql://localhost/");
603       
604       return driver.getMajorVersion() + '.' +
605              driver.getMinorVersion() + ".00";
606     }
607     catch (SQLException e) {
608       return "0.00.00";
609     }
610   }
611
612   /**
613    * Returns a string describing the host.
614    */
615   public String mysql_get_host_info(Env env, @Optional Mysqli conn)
616   {
617     if (conn == null)
618       conn = getConnection(env);
619
620     return conn.get_host_info();
621   }
622
623   /**
624    * Returns an integer respresenting the MySQL protocol
625    * version.
626    */
627   public int mysql_get_proto_info(Env env, @Optional Mysqli conn)
628   {
629     if (conn == null)
630       conn = getConnection(env);
631
632     return conn.get_proto_info();
633   }
634
635   /**
636    * Returns the MySQL server version.
637    */
638   public String mysql_get_server_info(Env env, @Optional Mysqli conn)
639   {
640     if (conn == null)
641       conn = getConnection(env);
642
643     return conn.get_server_info();
644   }
645
646   /**
647    * returns ID generated for an AUTO_INCREMENT column by the previous
648    * INSERT query on success, 0 if the previous query does not generate
649    * an AUTO_INCREMENT value, or FALSE if no MySQL connection was established
650    */
651   public Value mysql_insert_id(Env env, @Optional Mysqli conn)
652   {
653     if (conn == null)
654       conn = getConnection(env);
655
656     return conn.insert_id();
657   }
658
659   /**
660    * Returns a result pointer containing the databases available from the current mysql daemon.
661    */
662   public JdbcResultResource mysql_list_dbs(Env env, @Optional Mysqli conn)
663   {
664     if (conn == null)
665       conn = getConnection(env);
666
667     return conn.list_dbs();
668   }
669
670   /**
671    * Retrieves information about the given table name
672    */
673   public Value mysql_list_fields(Env env,
674                                  String databaseName,
675                                  String tableName,
676                                  @Optional Mysqli conn)
677   {
678     if (databaseName == null)
679       return BooleanValue.FALSE;
680
681     if (tableName == null)
682       return BooleanValue.FALSE;
683
684     return mysql_db_query(env,
685                           databaseName,
686                           "SHOW COLUMNS FROM " + tableName,
687                           conn);
688   }
689
690   /**
691    * Returns result set or false on error
692    */
693   public Value mysql_db_query(Env env,
694                               String databaseName,
695                               String query,
696                               @Optional Mysqli conn)
697   {
698     if (conn == null)
699       conn = getConnection(env);
700
701     if (! conn.select_db(databaseName))
702       return BooleanValue.FALSE;
703
704     return conn.query(env, query, 1);
705   }
706
707   /**
708    * Selects the database
709    */
710   public boolean mysql_select_db(Env env,
711                                  String name,
712                                  @Optional Mysqli conn)
713   {
714     if (conn == null)
715       conn = getConnection(env);
716
717     return conn.select_db(name);
718   }
719
720   /**
721    * Retrieves a list of table names from a MySQL database.
722    */
723   public Object mysql_list_tables(Env env,
724                                   String databaseName,
725                                   @Optional Mysqli conn)
726   {
727     return mysql_query(env, "SHOW TABLES FROM " + databaseName, conn);
728   }
729
730   public int mysql_num_fields(Env env, @NotNull MysqliResult result)
731   {
732     if (result == null)
733       return -1;
734
735     return result.num_fields();
736   }
737
738   /**
739    * Retrieves the number of rows in a result set.
740    */
741   public Value mysql_num_rows(Env env, @NotNull MysqliResult result)
742   {
743     if (result == null)
744       return BooleanValue.FALSE;
745
746     return result.num_rows();
747   }
748
749   /**
750    * Undocumented alias for {#link #mysql_num_rows}.
751    */
752   public Value mysql_numrows(Env env, @NotNull MysqliResult result)
753   {
754     return mysql_num_rows(env, result);
755   }
756
757   /**
758    * Returns a new mysql connection.
759    */
760   public Value mysql_pconnect(Env env,
761                               @Optional String server,
762                               @Optional String user,
763                               @Optional String password,
764                               @Optional Value newLinkV,
765                               @Optional Value flagsV)
766   {
767     return mysql_connect(env, server, user, password, newLinkV, flagsV);
768   }
769
770   /**
771    * Returns a new mysql connection.
772    */
773   public Value mysql_connect(Env env,
774                              @Optional String host,
775                              @Optional String userName,
776                              @Optional String password,
777                              @Optional Value newLinkV,
778                              @Optional Value flagsV)
779   {
780     int port = 3306;
781     int length = host.length();
782
783     if (length == 0) {
784       host = env.getIniString("mysql.default_host");
785       if (host == null)
786         host = "localhost";
787     }
788
789     int portIndex = host.lastIndexOf(':');
790
791     // Split host name and port from each other.
792 // Use > 0 because sockets have a ':' at index 0.
793 if (portIndex > 0) {
794       port = 0;
795
796       for (int j = portIndex + 1; j < length; j++) {
797         char ch = host.charAt(j);
798
799         if ('0' <= ch && ch <= '9')
800           port = port * 10 + ch - '0';
801         else
802           break;
803       }
804
805       host = host.substring(0, portIndex);
806     }
807
808     Mysqli mysqli = new Mysqli(env, host, userName, password, "", port, "", 0, null, null);
809
810     if (! mysqli.isConnected())
811       return BooleanValue.FALSE;
812
813     Value value = env.wrapJava(mysqli);
814
815     env.setSpecialValue("caucho.mysql", mysqli);
816
817     return value;
818   }
819
820   /**
821    * Checks if the connection is still valid.
822    */
823   public boolean mysql_ping(Env env, @Optional Mysqli conn)
824   {
825     if (conn == null)
826       conn = getConnection(env);
827
828     return conn.ping();
829   }
830
831   /**
832    * Returns a string with the status of the connection
833    * or NULL if error.
834    */
835   public Value mysql_stat(Env env, Mysqli conn)
836   {
837     if (conn == null)
838       conn = getConnection(env);
839
840     Value result = conn.stat(env);
841
842     return result == BooleanValue.FALSE ? NullValue.NULL : result;
843   }
844
845   /**
846    * Retrieves the table name corresponding to a field, using
847    * a result return by {@link #mysql_list_tables}.
848    */
849   public Value mysql_tablename(Env env,
850                                @NotNull MysqliResult result,
851                                int i)
852   {
853     if (result == null)
854       return BooleanValue.FALSE;
855
856     return result.getResultField(env, i, LongValue.ZERO);
857   }
858
859   /**
860    * Queries the database.
861    */
862   public Object mysql_unbuffered_query(Env env,
863                                        @NotNull String name,
864                                        @Optional Mysqli conn)
865   {
866     return mysql_query(env, name, conn);
867   }
868
869   //@todo mysql_change_user()
870 //@todo mysql_info()
871 //@todo mysql_list_processes()
872 //@todo mysql_thread_id()
873
874   private Mysqli getConnection(Env env)
875   {
876     Mysqli conn = (Mysqli) env.getSpecialValue("caucho.mysql");
877
878     if (conn != null)
879       return conn;
880
881     conn = new Mysqli(env, "localhost", "", "", "", 3306, "", 0, null, null);
882
883     env.setSpecialValue("caucho.mysql", conn);
884
885     return conn;
886   }
887 }
888
889

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags