Introduce Drupal

Schema API
Drupal Schema API is simple at provides many benefits.
Developer don’t require to write separate CREATE TABLE or ALTER TABLE statements for each database. Module developers only need to create a schema structure and/or use the Schema API functions.
All new database engines such as Oracle, SQLite, or Microsoft SQL Server are supported, modules using the Schema API will automatically work with them(only database drives to be changed).
Advanced capabilities, such as incremental database updates, a simple and consistent CRUD(Create, Read, Update, Delete) API, form scaffolding, simpler CCK and Views, schema and data validation, become much easier to implement in future enhancements.
Defining Schema in .install file
Schema definition is an array structure with one or more tables and their related keys and indexes. A schema is defined by hook_schema(), which usually lives in a my_module.install file. hook_schema() should return an array mapping 'tablename' => array(table definition) for each table that the module defines.

function my_module_schema() {
$schema['table_name_1'] = array(
'description' => t('Description of table'),
'fields' => array(
'id' => array( 'type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE, ),
'name' => array( 'type' => 'varchar', 'not null' => TRUE, 'length' => '50', ),
'unique keys' => array( 'name' => array('name') ),
'primary key' => array('id'),
);
$schema['table_name_1'] = array(
… … … …
);
return $schema;
}

function my_module_install() {
drupal_install_schema(‘my_module’);
}

function my_module_uninstall(){
drupal_uninstall_schema(‘my_module’);
}

Update Table Structure using schema
For adding a new column or some modification in the table we use hook_update_n() function. The n in hook_update_n() represents the version of the update to be done.
If a new column with name “newcol” is to be added to the table “table_name_1”, it has to be updated in the schema structure (my_module_schema()) and an update function has to be written as below.

function my_module_update_1(){
$a = array();
db_add_field($a, ‘table_name_1’, ‘newcol’, array(‘type’=>’int’));
return $a;
}

If another column with name “newcol_1” is to be added to the table “table_name_1” later, you have to update it in the schema structure as done before and an update function has to be written as below.

function my_module_update_2(){
a= array();
db_add_field($a, ‘table_name_1’, ‘newcol’ , array(‘type’=>’int’));
db_add_field($a, ‘table_name_1’, ‘newcol_1’, array(‘type’=>’int’));
return $a;
}

The following table shows all the legal combinations of the 'type' and 'size' fields of a column specification along with the underlying MySQL and PostgreSQL data types used by each combination.
The maximum size or range of each underlying type is also shown. For integer types, the number of storage bytes is given; the maximum range depends on whether the field is signed or unsigned. For varchar, text, and blob types, the maximum size is given.
type
size
mysql type & size/range
serial
tiny
tinyint, 1 B
serial
small
smallint, 2 B
serial
medium
mediumint, 3 B
serial
big
bigint, 8 B
serial
normal
int, 4 B
int
tiny
tinyint, 1 B
int
small
smallint, 2 B
int
medium
mediumint, 3 B
int
big
bigint, 8 B
int
normal
int, 4 B
float
tiny
float, 4 B
float
small
float, 4 B
float
medium
float, 4 B
float
big
double, 8 B
float
normal
float, 4 B
numeric
normal
numeric, 65 digits
varchar
normal
varchar, 255 B or 64 KB (1)
char
normal
char, 255 B
text
tiny
tinytext, 256 B
text
small
tinytext, 256 B
text
medium
mediumtext, 16 MB
text
big
longtext, 4 GB
text
normal
text, 16 KB
blob
big
longblob, 4 GB
blob
normal
blob, 16 KB
datetime
normal
datetime, years 1001 to 9999

Drupal Schema API Functions




Name
Description
db_add_field
Add a new field to a table.
db_add_field
Add a new field to a table.
db_add_index
Add an index.
db_add_index
Add an index.
db_add_primary_key
Add a primary key.
db_add_primary_key
Add a primary key.
db_add_unique_key
Add a unique key.
db_add_unique_key
Add a unique key.
db_change_field

db_change_field
Change a field definition.
db_create_table
Create a new table from a Drupal table definition.
db_create_table_sql
Generate SQL to create a new table from a Drupal schema definition.
db_create_table_sql
Generate SQL to create a new table from a Drupal schema definition.
db_drop_field
Drop a field.
db_drop_field
Drop a field.
db_drop_index
Drop an index.
db_drop_index
Drop an index.
db_drop_primary_key
Drop the primary key.
db_drop_primary_key
Drop the primary key.
db_drop_table
Drop a table.
db_drop_table
Drop a table.
db_drop_unique_key
Drop a unique key.
db_drop_unique_key
Drop a unique key.
db_field_names
Return an array of field names from an array of key/index column specifiers.
db_field_set_default
Set the default value for a field.
db_field_set_default
Set the default value for a field.
db_field_set_no_default
Set a field to have no default value.
db_field_set_no_default
Set a field to have no default value.
db_last_insert_id
Returns the last insert id.
db_rename_table
Rename a table.
db_rename_table
Rename a table.
db_type_map
This maps a generic data type in combination with its data size to the engine-specific data type.
db_type_map
This maps a generic data type in combination with its data size to the engine-specific data type.
db_type_placeholder
Given a Schema API field type, return the correct %-placeholder.
drupal_get_schema
Get the schema definition of a table, or the whole database schema.
drupal_get_schema_unprocessed
Returns the unprocessed and unaltered version of a module's schema.
drupal_install_schema
Create all tables that a module defines in its hook_schema().
drupal_schema_fields_sql
Retrieve a list of fields from a table schema. The list is suitable for use in a SQL query.
drupal_uninstall_schema
Remove all tables that a module defines in its hook_schema().
drupal_write_record
Save a record to the database based upon the schema.
_db_create_field_sql
Create an SQL string for a field to be used in table creation or alteration.
_db_create_field_sql
Create an SQL string for a field to be used in table creation or alteration.
_db_process_field
Set database-engine specific properties for a field.
_db_process_field
Set database-engine specific properties for a field.
_drupal_initialize_schema
Fill in required default values for table definitions returned by hook_schema().