JScript quick development framework EDK: DAO Database Connection

Http://code.google.com/p/naturaljs/ access
On Google code, you can get the source code of the JScript rapid development framework EDK.

 

I believe that the first step for many people to learn web is to connect to data, and the encapsulation process is also like this. It seems unnecessary to talk about connecting to the database. It is only a few steps, but it is still necessary to talk about it. Therefore, before introducing the Code, review the general steps of calling data. 1. Connect to the database; 2. Execute SQL operations. 3. return data set. Here is a common technique for optimization. The connected database should not be destroyed immediately and should be retained for reuse. Because there will be more than one query in one request, you can repeat the linked object after saving it. But why not use the pool )? Because JScript is not a daemon, a single request closes a thread and immediately recovers the resources occupied by the code, the connection pool algorithm may be useless.

 

After learning Java, I think of the concepts of Java. Therefore, even the class names are called Dao. Dao = Data Access Object: the data access object is readable and writable. The current Dao does not contain read and write operations, but is provided by $. SQL. Writer. Writer and $. SQL. Reader. reader to write and read database data.

 

The core method of Dao is execute ():

/** <Br/> * execute the SQL statement and return a set of results. <Br/> * @ Param {string} the SQL statement to be executed (optional ). <Br/> * @ Param {objcec} CFG configuration object, which can contain database connection strings (optional) <br/> * @ return {ADODB. recordset} database record set. <Br/> */<br/> execute: (function () {<br/> var connectobj; // database connection object, which is set only once without repeated creation. Multiple creation is a waste. </P> <p> function connectdb (CFG) {<br/> var <br/> dbtype // type of the connected database. What about access? What about MySQL? Or sqlserver? <Br/>, dbpath // path of the database file, if accessed through a disk. <Br/>, connectstr // string connecting to the database. <Br/> // database object, same as the preceding var connectobj; object <br/>, connectobj = new activexobject ("ADODB. connection "); </P> <p> If (CFG) {<br/> dbtype = cfg. dbtype; <br/>} else if (! CFG & $. CFG) {<br/> dbtype =$ $. cfg. edk_dbtype.tolowercase (); <br/>} else {<br/> dbtype = 'access '; // The default value is MS Access database <br/>}</P> <p> switch (dbtype) {<br/> case 'access ': <br/> If (CFG & cfg. dbpath) {<br/> dbpath = cfg. dbpath; <br/>} else if (! CFG & $. cfg) {<br/> dbpath = $. cfg. edk_isdebugging <br/>? $. Cfg. edk_dbfilepath_test <br/>: Server. mappath ($. cfg. edk_dbfilepath); <br/>}< br/> connectstr = "DBQ = {0}; defaultdir =; driver = {Microsoft Access Driver (*. MDB )};". format (dbpath); <br/> break; <br/> case 'sqlservver': <br/> throw' not implemented '; <br/> case 'mysql ': <br/> // use the driver mode without spaces in the connection string <br/> connectstr = "driver = {MySQL ODBC 5.1 Driver }; "<br/> +" Server = {0}; "<br/> +" Port = {1}; "<br/> +" uid = {2 }; "< Br/> + "Password = {3};" <br/> + "database = {4};" <br/> + "option = 3 "; </P> <p> connectstr = connectstr. format ('localhost', 3306, 'root', '123', 'test'); <br/> break; <br/> case 'sqlite ': <br/> dbpath = 'd: // test. db'; <br/> connectstr = "driver = {sqlite3 ODBC driver}; database =" + dbpath; <br/> break; <br/> default: <br/> throw' illegal database connection type! '; <Br/>}; <br/> connectobj. open (connectstr); </P> <p> return connectobj; <br/>}< br/> return function (SQL, CFG) {<br/> If (! Connectobj) {<br/> connectobj = this.exe cute. connectobj = connectdb (CFG); // call only once. <br/>}</P> <p> return SQL? Connectobj.exe cute (SQL): connectobj; <br/>}< br/> })()

 

Have you found that connect object is created only once? This example will be called repeatedly later. But it is worth mentioning that we define the reference of the database connection object to the execute () method! -- There is nothing strange to think about it. Using JavaScript's strong dynamic binding, you can even define new elements in methods and create new references. Now we use this method to design the data structure of Dao. (Of course, in some cases, it is not "no problem". For example, in client programming, do not bind DOM objects with other content. Otherwise, it is very easy to use memory leak, keep its dom api in mind ). In this case, the connection object of our data database is not directly stored on the thisobject, but accessible only through this.exe cute. connectobj. The purpose is to prevent this API from being too messy.

 

Static attributes

The above definition defines a connect object that can be said to have a static attribute allocation. Why is it static instead of dynamic? The reason for static data is that the value is assigned once, And the instance attributes have different values based on each instantiation. However, the OO feature of JavaScript is not obvious. It is difficult to explain from the code that I want to use static attributes. How can this problem be solved? I started to write a getstaticproerty () function to return the property instead of getting it directly. However, although this method is encapsulated, I later rejected this idea because of a problem. The logic for implementing static data is simple, that is, setting a variable and determining whether it is undefined, for example, if undefined is used, the static value is written, and the static value is returned after the function is continuously accessed. It's easy. There's nothing complicated. In particular, writing a getstaticproerty () function brings additional maintenance costs, and it is not necessarily obvious about reusability. Therefore, you can decide to implement the idea of "static attribute" in the specific implementation in the future. Do not call getstaticproerty ().

 

However, to better illustrate the problem, I listed the original solution to help you understand what I said:

// Define the new method of the function object, that is, the function that obtains the attribute. <Br/> function. prototype. getstaticproperty = function (sethandler) {<br/> function getfunctionname (FN) {<br/> return (/function/S + (/W +)/S */(/) /. exec (FN + '') [1]; <br/>}< br/> var propertyname = getfunctionname (sethandler ); </P> <p> If (typeof (this [propertyname]) = 'undefined') {<br/> This [propertyname] = sethandler. call (this); <br/> return this [propertyname]; <br/>}else {<br/> return this [propertyname]; <br />}< Br/>}< br/> // compare the execute () <br/> this.exe cute = function (SQL, CFG) {<br/> // call only once. <br/> var connectobj = this.exe cute. getstaticproperty (function connectobj () {<br/> var <br/> dbpath <br/>, dbtype = ($. CFG? $. Cfg. edk_dbtype.tolowercase (): NULL) <br/> | (CFG? Cfg. dbtype: NULL) <br/> | 'access' // if not specified, the default value is access database. <Br/>, connectstring <br/>, connectobj; </P> <p> switch (dbtype) {<br/> case 'mysql ': <br/> // use the driver mode without spaces in the connection string <br/> connectstring = "driver = {MySQL ODBC 5.1 Driver }; "<br/> +" Server = {0}; "<br/> +" Port = {1}; "<br/> +" uid = {2 }; "<br/> +" Password = {3}; "<br/> +" database = {4}; "<br/> +" option = 3 "; </P> <p> connectstring = connectstring. format ('localhost', 3306, 'root', '123', 'test'); <br/> break; <br/> case 'ac Cess ': <br/> If (CFG & cfg. dbpath) {<br/> dbpath = cfg. dbpath; <br/>}else {<br/> if ($. CFG) {<br/> dbpath =$ $. cfg. edk_isdebugging <br/>? $. Cfg. edk_dbfilepath_test <br/>: Server. mappath ($. cfg. edk_dbfilepath); <br/>}< br/> connectstring = "DBQ = {0}; defaultdir =; driver = {Microsoft Access Driver (*. MDB )};". format (dbpath); <br/> break; <br/> case 'sqlite ': <br/> connectstring = "driver = {sqlite3 ODBC driver}; database = D: // test. DB "; <br/> break; <br/> default: <br/> throw' illegal database connection type! '; <Br/>}; <br/> connectobj = new activexobject ("ADODB. connection "); <br/> connectobj. open (connectstring); </P> <p> return connectobj; <br/>}); </P> <p> return SQL? Connectobj.exe cute (SQL): connectobj; <br/>}

 

Currently, Dao supports access, MySQL (ODBC driver to be installed), and SQLite (driver to be installed as well) character connection strings.

 

Is it necessary to process the queried record set? Yes. If we are a JS language, convert it to a natural JSON structure. The process is very simple, that is, dual-loop recordset. Note that the number type of ADO recordset is changed to JS type, that is, the sql2json (value, datatype) function. Currently, I select a simple conversion. for complex and comprehensive ADO type detection, please refer to the file adovbs. inc. Learn more about constants. The default path of this file in the system is: C:/program files/common files/system/ADO/adovbs. INC, or directly look at the functions that I post. This function is very comprehensive, but I can't use many places, so it is simplified and the current sql2json is obtained.

/** <Br/> * converts the queried record set to the JSON structure of JavaScript. <Br/> * @ Param {recroedset} Rs ADO object set. <Br/> * @ return {array} the output JSON array. <Br/> */<br/>, row2json: (function () {<br/>/** <br/> * the ADO type of the conversion value is JavaScript. <Br/> * @ private <br/> * @ Param {number} adofield <br/> * @ Param {Any} value <br/> * @ return {Any} <br/> */<br/> function sql2json (value, datatype) {<br/> switch (datatype) {<br/> case "string": <br/> Case 129: // 'Char ': <br/> case 130: // 'nschar': <br/> case 200: // 'varchar ': <br/> case 201: // 'text ': <br/> case 202: // 'nvarchar ': <br/> case 203: // 'text': <br/> return string (value ); <br/> case "Number": <B R/> case 20: // 'bigint': <br/> case 2: // 'smallint': <br/> case 16: // 'tinyint ': <br/> case 14: // 'decimal': <br/> case 5: // 'double': <br/> return number (value ); <br/> case 11: // "Boolean": <br/> case 'bol': <br/> return Boolean (value ); <br/> case "date": <br/> case "object": <br/> Case 135: // 'datetime': <br/> case 64: // 'filetime': <br/> case 134: <br/> case 137: <br/> return new date (value ). format ("yyyy- Mm-dd "); <br/>}</P> <p> return function (RS) {<br/> var <br/> output = [] // JSON array output by array <br/>, rawarr = Rs. getrows (). array returned by toarray () // arraygetrows () <br/>, fields = Rs. fields // comobjrs. fields Field object. Each column is different. <Br/>, FLEN = fields. count // The total number of int fields <br/>, Len = rawarr. length/FLEN // int records <br/>, SP // int digits <br/>, row; // The object is a JSON row object </P> <p> // The recordset set is automatically disabled to release the resources occupied by the recordset object as soon as possible. <Br/> Rs. close (); </P> <p> for (VAR I = 0; I <Len; I ++) {<br/> sp = I * FLEN <br/>, row = {}; </P> <p> for (VAR J = 0; j <FLEN; j ++) {<br/> row [fields (j ). name] = sql2json (rawarr [SP + J], fields (j ). type); <br/>}< br/> output. push (ROW); <br/>}</P> <p> return output; <br/>}< br/> })()

At last, keep in mind: Close the database connection in the program writing stage, that is, $. SQL. Close ();

Appendix:

/** <Br/> * the ADO type of the converted value is JavaScript. <Br/> * for more constant information, see the file adovbs. inc. The default path of the file in the system is: <br/> * C:/program files/common files/system/ADO/adovbs. INC <br/> * search for other systems by yourself. <Br/> * @ private <br/> * @ Param {Any} value <br/> * @ Param {recordset. field} adofield <br/> * @ return {Any} <br/> */<br/> sql2json = function (value, adofield) {<br/> // base types <br/> var base_type_unknown = 0; <br/> var base_type_number = 1; <br/> var base_type_date = 2; <br/> var base_type_boolean = 3; <br/> var base_type_text = 4; <br/> // ADO types <br/> var ado_type_int = 3; <br/> var ado_type_float = 5; <br/> var ado_type_money = 6; <br/> var ado_type_datetime = 7; <br/> var ado_type_bit = 11; <br/> var ado_type_varchar = 200; <br/> var ado_type_text = 201; <br/> var ado_type_nvarchar = 202; <br/> var ado_type_ntext = 203; <br/> VaR _ adofieldtypes ={< br/> 20: {name: "adbigint", type: base_type_number },< br/> 128: {Name: "adbinary", type: base_type_unknown}, <br/> 11: {name: "adboolean", type: base_type_boolean}, <br/> 8: {name: "adbstr ", type: base_type_unknown}, <br/> 136: {name: "adchapter", type: base_type_unknown}, <br/> 129: {name: "adchar", type: base_type_text },< br/> 6: {name: "adcurrency", type: base_type_number },< br/> 7: {name: "addate", type: base_type_date }, <br/> 133: {name: "addbdate", type: base_type_date}, <br/> 137: {name: "addbfiletime", type: base_type_date }, <br/> 134: {name: "addbtime", type: base_type_date}, <br/> 135: {name: "addbtimestamp", type: base_type_date }, <br/> 14: {name: "addecimal", type: base_type_number}, <br/> 5: {name: "addouble", type: base_type_number }, <br/> 0: {name: "adempty", type: base_type_unknown}, <br/> 10: {name: "aderror", type: base_type_unknown }, <br/> 64: {name: "adfiletime", type: base_type_date}, <br/> 72: {name: "adguid", type: base_type_unknown }, <br/> 9: {name: "adidispatch", type: base_type_unknown}, <br/> 3: {name: "adinteger", type: base_type_number }, <br/> 13: {name: "adiunknown", type: base_type_unknown}, <br/> 205: {name: "adlongvarbinary", type: base_type_unknown }, <br/> 201: {name: "adlongvarchar", type: base_type_text}, <br/> 203: {name: "adlongvarwchar", type: base_type_text }, <br/> 131: {name: "adnumeric", type: base_type_number}, <br/> 138: {name: "adpropvariant", type: base_type_unknown }, <br/> 4: {name: "adsingle", type: base_type_number}, <br/> 2: {name: "adsmallint", type: base_type_number }, <br/> 16: {name: "adtinyint", type: base_type_number}, <br/> 21: {name: "adunsignedbigint", type: base_type_number }, <br/> 19: {name: "adunsignedint", type: base_type_number}, <br/> 18: {name: "adunsignedsmallint", type: base_type_number }, <br/> 17: {name: "adunsignedtinyint", type: base_type_number}, <br/> 132: {name: "aduserdefined", type: base_type_unknown }, <br/> 204: {name: "advarbinary", type: base_type_unknown}, <br/> 200: {name: "advarchar", type: base_type_text }, <br/> 12: {name: "advariant", type: base_type_unknown}, <br/> 139: {name: "advarnumeric", type: base_type_number }, <br/> 202: {name: "advarwchar", type: base_type_text}, <br/> 130: {name: "adwchar", type: base_type_text }, <br/> 8192: {name: "adarray", type: base_type_unknown} <br/>}; </P> <p> // _ getvaluebytype () <br/> function _ getvaluebytype (type, value) {<br/> If (typeof value = "undefined") return NULL; <br/> switch (type) {<br/> case "string": <br/> case base_type_text: <br/> return string (value); <br/> case "Number ": <br/> case base_type_number: <br/> return number (value); <br/> case "Boolean": <br/> case base_type_boolean: <br/> return Boolean (value); <br/> case "date": <br/> case "object": <br/> case base_type_date: <br/> return new date (value ). format ("yyyy-mm-dd"); <br/> default: <br/> return value; <br/>}</P> <p> return _ getvaluebytype (_ adofieldtypes [adofield. type]. type, value); <br/>}