Note:

If you want to create a new page for developers, you should create it on the Moodle Developer Resource site.

XMLDB column types: Difference between revisions

From MoodleDocs
(FIELD TYPE "date with time" changed to "datetime")
 
(10 intermediate revisions by 3 users not shown)
Line 1: Line 1:
[[XML database schema]] > XMLDB column types
[[XMLDB Documentation|XMLDB Documentation]] > XMLDB column types
----
----
This page will show, once finished, one compatibility matrix of column types for each of the RDBMS under which Moodle is expected to work.
This page will show, once finished, one compatibility matrix of column types for each of the RDBMS under which Moodle is expected to work.
Line 69: Line 69:
   </tr>
   </tr>
   <tr>
   <tr>
     <td>char</td>
     <td>char
<small>size limit</small>
    </td>
     <td>
     <td>
:'''VARCHAR'''
:'''VARCHAR'''
<small><5.0.3 0-255 chars, >=5.0.3 0-65535 chars</small>
     </td>
     </td>
     <td>
     <td>
:'''VARCHAR'''
:'''VARCHAR'''
<small>about 1GB ot text</small>
     </td>
     </td>
     <td>
     <td>
:'''VARCHAR2'''
:'''VARCHAR2'''
<small>4000 bytes</small>
     </td>
     </td>
     <td>
     <td>
:'''NVARCHAR'''
:'''NVARCHAR'''
<small>4000 chars</small>
     </td>
     </td>
   </tr>
   </tr>
Line 118: Line 124:
   </tr>
   </tr>
   <tr>
   <tr>
     <td>datetime</td>
     <td>datetime<sup>(*)</sup></td>
     <td>
     <td>
:'''DATETIME'''
:'''DATETIME'''
Line 133: Line 139:
   </tr>
   </tr>
</table>
</table>
(*) Note that, although datetime types are supported by XMLDB, '''all datetime/timestamp columns''' under Moodle are declared as '''integer(10)''' and filled with '''Unix timestamps''', so '''this type of column shouldn't be used at all''' (in fact, the XMLDB Editor doesn't show them as an available option).


== Some official links ==
== Some official links ==
Line 140: Line 148:
* SQL*Server: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_7msw.asp
* SQL*Server: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_7msw.asp
* Oracle: http://www.cs.umbc.edu/help/oracle8/server.815/a68003/01_04blt.htm
* Oracle: http://www.cs.umbc.edu/help/oracle8/server.815/a68003/01_04blt.htm


[[Category:XMLDB]]
[[Category:XMLDB]]

Latest revision as of 18:06, 18 December 2007

XMLDB Documentation > XMLDB column types


This page will show, once finished, one compatibility matrix of column types for each of the RDBMS under which Moodle is expected to work.

The matrix

XMLDB Type MySQL PostgreSQL Oracle MSSQL
int
BIGINT (>9 digits)
INT (>6 digits)
MEDIUMINT (>4 digits)
SMALLINT (>2 digits)
TINYINT (<=2 digits)
BIGINT (>9 digits)
INTEGER (>4 digits)
SMALLINT (<=4 digits)
NUMBER
BIGINT (>9 digits)
INTEGER (>4 digits)
SMALLINT (<=4 digits)
number
NUMERIC
NUMERIC
NUMBER
DECIMAL
float
FLOAT (< 6 dec. positions)
DOUBLE (>= 6 dec. positions)
REAL (< 6 dec. positions)
DOUBLE PRECISION (>= 6 dec. positions)
NUMBER
REAL (< 6 dec. positions)
FLOAT (>= 6 dec. positions)
char

size limit

VARCHAR

<5.0.3 0-255 chars, >=5.0.3 0-65535 chars

VARCHAR

about 1GB ot text

VARCHAR2

4000 bytes

NVARCHAR

4000 chars

text
LONGTEXT
MEDIUMTEXT
TEXT
TEXT
CLOB
NTEXT
binary
LONGBLOB
MEDIUMBLOB
BLOB
BYTEA
BLOB
IMAGE
datetime(*)
DATETIME
TIMESTAMP
DATE
DATETIME

(*) Note that, although datetime types are supported by XMLDB, all datetime/timestamp columns under Moodle are declared as integer(10) and filled with Unix timestamps, so this type of column shouldn't be used at all (in fact, the XMLDB Editor doesn't show them as an available option).

Some official links