The DAL allows multiple connections with the same database or with different databases, even databases of different types. For now, we will assume the presence of a single database since this is the most common situation.
You are free to give it a different name. The constructor of DAL requires a single argument, the connection string. The connection string is the only web2py code that depends on a specific back-end database.
Here are examples of connection strings for specific types of supported back-end databases in all cases, we assume the database is running from localhost on its default port and is named "test" :. Notice that in SQLite the database consists of a single file. If it does not exist, it is created. This file is locked every time it is accessed. Once the connection is established, web2py will create, alter, and drop tables appropriately.
In the MySQL connection string, the? NDB uses a Memcache buffer to read data that is accessed often. This is completely automatic and done at the datastore level, not at the web2py level. It is also possible to set the connection string to None.
Some times you may need to generate SQL as if you had a connection but without actually connecting to the database. This can be done with. Notice that by default web2py uses utf8 character encoding for databases.
As it is rather slow to establish a new database connection for each request, web2py implements a mechanism for connection pooling. Once a connection is established and the page has been served and the transaction completed, the connection is not closed but goes into a pool. When the next http request arrives, web2py tries to recycle a connection from the pool and use that for the new transaction. If there are no available connections in the pool, a new connection is established.
When web2py starts, the pool is always empty. Connections in the pools are shared sequentially among threads, in the sense that they may be used by two different but not simultaneous threads. There is only one pool for each web2py process. Connection pooling is ignored for SQLite, since it would not yield any benefit. If web2py fails to connect to the database it waits 1 second and by default tries again up to 5 times before declaring a failure. In case of connection pooling it is possible that a pooled connection that stays open but unused for some time is closed by the database end.
Thanks to the retry feature web2py tries to re-establish these dropped connections. The number of attempts is set via the attempts parameter. See below: lazy tables. Using web2py's model directory for your application models is very convenient and productive. With lazy tables and conditional models, performance is usually acceptable even for large applications.
Many experienced developers use this is production environments. However, it is possible to define DAL tables on demand inside controller functions or modules. This may make sense when the number or complexity of table definitions overloads the use of lazy tables and conditional models. This is referred to as "model-less" development by the web2py community. It means less use of the automatic execution of Python files in the model directory. It does not imply abandoning the concept of models, views and controllers.
Models also make for useful interactive shell sessions when web2py is started with the -M commandline option. Also, remember maintainability: other web2py developers expect to find model definitions in the model directory. To use the "model-less" approach, you take responsibility for doing these two housekeeping tasks. You call the table definitions when you need them, and provide necessary access to global scope via the current object as described in Chapter 4.
For example, a typical model-less application may leave the definitions of the database connection objects in the model file, but define the tables on demand per controller function. The typical case is to move the table definitions to a module file a Python file saved in the modules directory. The first argument of DAL In this case web2py tries to connect to each of them. The main purpose for this is to deal with multiple database servers and distribute the workload among them.
Here is a typical use case:. In this case the DAL tries to connect to the first and, on failure, it will try the second and the third. This can also be used to distribute load in a database master-slave configuration. We will talk more about this in Chapter 13 in the context of scalability. The adapter name is the same as used in the DAL connection string. There are two extra options "all" and "common".
If you specify all, it will check against all known SQL keywords. For supported back-ends you may also specify if you would like to check against the non-reserved SQL keywords as well. At SQL level keywords and unquoted identifiers are case insensitive, thus quoting an SQL identifier makes it case sensitive.
Notice that unquoted identifiers should always be folded to lower case by the back-end engine according to SQL standard but not all engines are compliant with this for example PostgreSQL default folding is upper case. To be sure of using the same names in python and in the DB schema, you must arrange for both settings above. Here is an example:. Sometimes it is necessary and advised to connect to your database using secure connection, especially if your database is not on the same server as your application.
In this case you need to pass additional parameters to the database driver. You should refer to database driver documentation for details.
It is also used for SQLite databases. Automatically set within web2py. Set a path when using DAL outside web2py. You need to choose an application to run the shell on, mind that database changes may be persistent. So be carefull and do NOT exitate to create a new application for doing testing instead of tampering with an existing one.
Start by creating a connection. For the sake of example, you can use SQLite. Nothing in this discussion changes when you change the back-end engine. It accepts a mandatory table name and an optional number of Field instances even none. You can also pass a Table or subclass object instead of a Field one, this clones and adds all the fields but the "id" to the defining table. It defines, stores and returns a Table object called "person" containing a field column "name". This object can also be accessed via db.
Do not declare a field called "id", because one is created by web2py anyway. Every table has a field called "id" by default. It is an auto-increment integer field usually starting at 1 used for cross-reference and for making every record unique, so "id" is a primary key.
Note: the id counter starting at 1 is back-end specific. This is not recommended except when accessing legacy database tables which have a primary key under a different name. With some limitation, you can also use different primary keys using the primarykey parameter. Smartgrid objects may need to know the singular and plural name of the table. The defaults are smart but these parameters allow you to be specific. Smartgrid is described in Chapter 7.
It is optional but recommended to specify a format representation for records with the format parameter. This makes the web2py table name an alias, and rname is the real name used when constructing the query for the backend. See Legacy databases and keyed tables section in this chapter. Refer to Migrations section in this chapter for details. If you define your own Table class as a sub-class of pydal. Table, you can provide it here; this allows you to extend and override methods. The name of a custom table sequence if supported by the database.
Relevant for some backends which do not support auto-increment numeric fields. This allows dynamic changes to the table without losing the advantages of delayed instantiation. The simple requires values could be added to the Field definitions and the table would still be lazy. Not all tables are needed to handle each request, so it is possible that some of the time spent defining tables is wasted.
This feature means that table creation is deferred until the table is actually referenced. Enabling lazy tables is made when initialising a database via the DAL constructor.
You can do it with tables too but they must be preceded by an underscore to avoid naming conflicts with fields:. Not all of them are relevant for every field. While they sometimes may seem redundant, it is important to maintain the distinction when programming with the DAL. You need to have PyFileSystem installed for this to work. Decimal requires and returns values as Decimal objects, as defined in the Python decimal module.
SQLite does not handle the decimal type so internally we treat it as a double. The n,m are the number of digits in total and the number of digits after the decimal point respectively. The big-id and, big-reference are only supported by some of the database engines and are experimental. On relational databases lists are stored as a text field. The items are separated by a and each in string item is escaped as a. The json field type is pretty much explanatory.
It can store any json serializable object. It is designed to work specifically for MongoDB and backported to the other database adapters for portability. By default, binary data is encoded in base64 before being stored into the actual database field, and it is decoded when extracted. A field also has methods. Some of them are used to build queries and we will see them later. A special method of the field object is validate and it calls the validators for the field.
If the table does exist but differs from the one being defined, it generates the SQL to alter the table and executes it. If a field has changed type but not name, it will try to convert the data If you do not want this, you need to redefine the table twice, the first time, letting web2py drop the field by removing it, and the second time adding the newly defined field so that web2py can create it.
If the table exists and matches the current definition, it will leave it alone. In all cases it will create the db. We refer to this behavior as a "migration". You can change this setting the folder argument to DAL.
To set a different log file name, for example "migrate. The other unnamed arguments are the fields Field. The function also takes an optional keyword argument called "migrate":. The value of migrate is the filename where web2py stores internal migration information for this table. These files are very important and should never be removed while the corresponding tables exist.
In cases where a table has been dropped and the corresponding file still exist, it can be removed manually. By default, migrate is set to True. This causes web2py to generate the filename from a hash of the connection string.
For example,. Notice that web2py only migrates new columns, removed columns, and changes in column type except in SQLite. This is the recommended behavior when two apps share the same database. Only one of the two apps should perform migrations, the other should disabled them.
One problem is specific with SQLite. SQLite does not enforce column types and cannot drop columns. This means that if you have a column of type string and you remove it, it is not really removed. If you add the column again with a different type for example datetime you end up with a datetime column that contains strings junk for practical purposes.
If web2py returns an error in some parse function when selecting records, most likely this is due to corrupted data in a column because of the above issue. The solution consists in updating all records of the table and updating the values in the column in question with None. Ensure that the Row Source property contains a value list. The items in a value list are surrounded by double quotation marks and separated by semicolons.
For example, you might see a list like this: " Good";"Fair";"Poor ". ItemData n. If you don't know the name of the control, look at the value in the Name property, located at the top of the property sheet. For example, suppose you have a combo box control named Owner , and you want to use the third value in the list as the default value. You type the following in the Default Value property box:.
You type 2 instead of 3 because ItemData is zero-based, meaning it starts counting at zero, not one. Save your changes, and then switch back to Form view. Your selected default value should appear in the list or combo box when you add a record to your database.
Note: By default, Access hides primary and foreign key fields. If you don't see the key field, right-click any cell in the header row of the table, click Unhide Columns , and in the Unhide Columns dialog box, select your key field and click Close.
In the Navigation Pane, right-click the table that contains your lookup field, and then click Design View. Select the lookup field, and on the General tab, in the Default Value property box, type the key value that you noted in steps 1 and 2. This is the value that corresponds to the list item that you want to make the default. For example, suppose you have 10 suppliers, and you want the name of your most-used supplier to appear by default. To do so, you locate the key value that uniquely identifies the supplier, and you enter that key value in the Default Value property box of the foreign key field.
Save your changes, switch to Datasheet view, and enter a new record. Your default value appears in your lookup field when you add the record. You can define a custom format for a Memo field. Purpose Use to store a numeric value that isn't a monetary value. If you might use the values in the field to perform a calculation, use the Number data type. Byte — Use for integers that range from 0 to Storage requirement is 1 byte.
Integer — Use for integers that range from , to 32, Storage requirement is 2 bytes. Long Integer — Use for integers that range from -2,,, to 2,,, Storage requirement is 4 bytes.
Tip: Use Long Integer when you create a foreign key to relate to another table's AutoNumber primary key field. Single Use for numeric floating point values that range from Double Use for numeric floating point values that range from Storage requirement is 8 bytes. Replication ID Use for storing a globally unique identifier required for replication.
Storage requirement is 16 bytes. Note that replication is not supported using the. Decimal Use for numeric values that range from Storage requirement is 12 bytes. Tip: For best performance, always specify the smallest sufficient Field Size. Purpose Use to store a Large numeric value that isn't a monetary value. If you might use the values in the field to perform a calculation, use the Large Number data type.
OLE Object fields support fewer file types than Attachment fields support. In addition, OLE Object fields do not let you attach multiple files to a single record. Purpose Use to store up to characters of text.
Note, beginning in Access the Text data type has been renamed to Short Text. Enter a value from 1 to Text fields can range from 1 to characters.
For larger text fields, use the Memo data type. For example, if you are storing postal codes of a known length, you should specify that length as the Field Size. You can define a custom format for a Text field. Data types. Introduction to data types and field properties. Tip: An effective caption is usually brief.
Need more help? Expand your skills. Get new features first. Was this information helpful? Yes No. Thank you! Any more feedback? The more you tell us the more we can help. Can you help us improve? Resolved my issue. Clear instructions. Easy to follow. No jargon. Pictures helped. Didn't match my screen.
Incorrect instructions. Too technical. Not enough information. Not enough pictures. Any additional feedback? Submit feedback. Thank you for your feedback! Number, Large Number. Numeric values, such as distances. Note that there is a separate data type for currency. Yes and No values and fields that contain only one of two values.
Rich Text. Text or combinations of text and numbers that can be formatted using color and font controls. Calculated Field. Text or combinations of text and numbers stored as text and used as a hyperlink address. Short Date. Medium Date. Display the date in medium format. For example, 3-Apr for USA. Long Date. Medium Time. PeopleTools Answer First Prev Next Last Showing Answers 1 - 3 of 3 Answers rama Jul 7th, If we specify one field as a set control field then we will retrieve data based on this when we are using prompt table.
Based on this field only we can have valid prompt list at the time of prompt button clicking. Set Control id is used when you want to share tables in Peopletool applications.
This key identifies the sets of information in the table that are shared by multiple companies or business units under your corporate umbrella.
You then specify a set control field, which identifies which fields map between the original key and the TableSets. You can specify any field that logically identifies the TableSet.
0コメント