Heine

  • Home
  • Drupal
  • About
Home

Drupal 5.x Core tables

Heine —Sun, 2007/07/15 - 04:09

Back in the days, we had *.mysql files which contained SQL commands for the Drupal database layout. Now, Drupal 5 comes with an installer and those SQL commands are hidden in modules/system/system.install.

Someone recently asked for the CREATE table statements of Drupal 5.x. Here's a list with a handy prefix_ you can use to search and replace with your own prefix.

CREATE TABLE prefix_access (
        aid int NOT NULL auto_increment,
        mask varchar(255) NOT NULL default '',
        type varchar(255) NOT NULL default '',
        status tinyint NOT NULL default '0',
        PRIMARY KEY (aid)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_authmap (
        aid int unsigned NOT NULL auto_increment,
        uid int NOT NULL default '0',
        authname varchar(128) NOT NULL default '',
        module varchar(128) NOT NULL default '',
        PRIMARY KEY (aid),
        UNIQUE KEY authname (authname)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_blocks (
        module varchar(64) DEFAULT '' NOT NULL,
        delta varchar(32) NOT NULL default '0',
        theme varchar(255) NOT NULL default '',
        status tinyint DEFAULT '0' NOT NULL,
        weight tinyint DEFAULT '0' NOT NULL,
        region varchar(64) DEFAULT 'left' NOT NULL,
        custom tinyint DEFAULT '0' NOT NULL,
        throttle tinyint DEFAULT '0' NOT NULL,
        visibility tinyint DEFAULT '0' NOT NULL,
        pages text NOT NULL,
        title varchar(64) DEFAULT '' NOT NULL
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_boxes (
        bid int NOT NULL auto_increment,
        body longtext,
        info varchar(128) NOT NULL default '',
        format int NOT NULL default '0',
        PRIMARY KEY (bid),
        UNIQUE KEY info (info)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_cache (
        cid varchar(255) NOT NULL default '',
        data longblob,
        expire int NOT NULL default '0',
        created int NOT NULL default '0',
        headers text,
        PRIMARY KEY (cid),
        INDEX expire (expire)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_cache_filter (
        cid varchar(255) NOT NULL default '',
        data longblob,
        expire int NOT NULL default '0',
        created int NOT NULL default '0',
        headers text,
        PRIMARY KEY (cid),
        INDEX expire (expire)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_cache_menu (
        cid varchar(255) NOT NULL default '',
        data longblob,
        expire int NOT NULL default '0',
        created int NOT NULL default '0',
        headers text,
        PRIMARY KEY (cid),
        INDEX expire (expire)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_cache_page (
        cid varchar(255) BINARY NOT NULL default '',
        data longblob,
        expire int NOT NULL default '0',
        created int NOT NULL default '0',
        headers text,
        PRIMARY KEY (cid),
        INDEX expire (expire)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_comments (
        cid int NOT NULL auto_increment,
        pid int NOT NULL default '0',
        nid int NOT NULL default '0',
        uid int NOT NULL default '0',
        subject varchar(64) NOT NULL default '',
        comment longtext NOT NULL,
        hostname varchar(128) NOT NULL default '',
        timestamp int NOT NULL default '0',
        score mediumint NOT NULL default '0',
        status tinyint unsigned NOT NULL default '0',
        format int NOT NULL default '0',
        thread varchar(255) NOT NULL,
        users longtext,
        name varchar(60) default NULL,
        mail varchar(64) default NULL,
        homepage varchar(255) default NULL,
        PRIMARY KEY (cid),
        KEY lid (nid)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_node_comment_statistics (
        nid int unsigned NOT NULL auto_increment,
        last_comment_timestamp int NOT NULL default '0',
        last_comment_name varchar(60) default NULL,
        last_comment_uid int NOT NULL default '0',
        comment_count int unsigned NOT NULL default '0',
        PRIMARY KEY (nid),
        KEY node_comment_timestamp (last_comment_timestamp)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_files (
        fid int unsigned NOT NULL default 0,
        nid int unsigned NOT NULL default 0,
        filename varchar(255) NOT NULL default '',
        filepath varchar(255) NOT NULL default '',
        filemime varchar(255) NOT NULL default '',
        filesize int unsigned NOT NULL default 0,
        PRIMARY KEY (fid),
        KEY nid (nid)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_file_revisions (
        fid int unsigned NOT NULL default 0,
        vid int unsigned NOT NULL default 0,
        description varchar(255) NOT NULL default '',
        list tinyint unsigned NOT NULL default 0,
        PRIMARY KEY (fid, vid),
        KEY (vid)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_filter_formats (
        format int NOT NULL auto_increment,
        name varchar(255) NOT NULL default '',
        roles varchar(255) NOT NULL default '',
        cache tinyint NOT NULL default '0',
        PRIMARY KEY (format),
        UNIQUE KEY (name)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_filters (
        format int NOT NULL default '0',
        module varchar(64) NOT NULL default '',
        delta tinyint DEFAULT '0' NOT NULL,
        weight tinyint DEFAULT '0' NOT NULL,
        INDEX (weight)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_flood (
        event varchar(64) NOT NULL default '',
        hostname varchar(128) NOT NULL default '',
        timestamp int NOT NULL default '0'
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_history (
        uid int NOT NULL default '0',
        nid int NOT NULL default '0',
        timestamp int NOT NULL default '0',
        PRIMARY KEY (uid,nid)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_menu (
        mid int unsigned NOT NULL default '0',
        pid int unsigned NOT NULL default '0',
        path varchar(255) NOT NULL default '',
        title varchar(255) NOT NULL default '',
        description varchar(255) NOT NULL default '',
        weight tinyint NOT NULL default '0',
        type int unsigned NOT NULL default '0',
        PRIMARY KEY (mid)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_node (
        nid int unsigned NOT NULL auto_increment,
        vid int unsigned NOT NULL default '0',
        type varchar(32) NOT NULL default '',
        title varchar(128) NOT NULL default '',
        uid int NOT NULL default '0',
        status int NOT NULL default '1',
        created int NOT NULL default '0',
        changed int NOT NULL default '0',
        comment int NOT NULL default '0',
        promote int NOT NULL default '0',
        moderate int NOT NULL default '0',
        sticky int NOT NULL default '0',
        PRIMARY KEY  (nid, vid),
        UNIQUE KEY vid (vid),
        KEY node_type (type(4)),
        KEY node_title_type (title, type(4)),
        KEY status (status),
        KEY uid (uid),
        KEY node_moderate (moderate),
        KEY node_promote_status (promote, status),
        KEY node_created (created),
        KEY node_changed (changed),
        KEY node_status_type (status, type, nid),
        KEY nid (nid)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_node_access (
        nid int unsigned NOT NULL default '0',
        gid int unsigned NOT NULL default '0',
        realm varchar(255) NOT NULL default '',
        grant_view tinyint unsigned NOT NULL default '0',
        grant_update tinyint unsigned NOT NULL default '0',
        grant_delete tinyint unsigned NOT NULL default '0',
        PRIMARY KEY (nid,gid,realm)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_node_revisions (
        nid int unsigned NOT NULL,
        vid int unsigned NOT NULL,
        uid int NOT NULL default '0',
        title varchar(128) NOT NULL default '',
        body longtext NOT NULL,
        teaser longtext NOT NULL,
        log longtext NOT NULL,
        timestamp int NOT NULL default '0',
        format int NOT NULL default '0',
        PRIMARY KEY  (vid),
        KEY nid (nid),
        KEY uid (uid)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_node_type (
        type varchar(32) NOT NULL,
        name varchar(255) NOT NULL default '',
        module varchar(255) NOT NULL,
        description mediumtext NOT NULL,
        help mediumtext NOT NULL,
        has_title tinyint unsigned NOT NULL,
        title_label varchar(255) NOT NULL default '',
        has_body tinyint unsigned NOT NULL,
        body_label varchar(255) NOT NULL default '',
        min_word_count smallint unsigned NOT NULL,
        custom tinyint NOT NULL DEFAULT '0',
        modified tinyint NOT NULL DEFAULT '0',
        locked tinyint NOT NULL DEFAULT '0',
        orig_type varchar(255) NOT NULL default '',
        PRIMARY KEY (type)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_url_alias (
        pid int unsigned NOT NULL auto_increment,
        src varchar(128) NOT NULL default '',
        dst varchar(128) NOT NULL default '',
        PRIMARY KEY (pid),
        UNIQUE KEY dst (dst),
        KEY src (src)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_permission (
        rid int unsigned NOT NULL default '0',
        perm longtext,
        tid int unsigned NOT NULL default '0',
        KEY rid (rid)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_role (
        rid int unsigned NOT NULL auto_increment,
        name varchar(64) NOT NULL default '',
        PRIMARY KEY (rid),
        UNIQUE KEY name (name)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_blocks_roles (
        module varchar(64) NOT NULL,
        delta varchar(32) NOT NULL,
        rid int unsigned NOT NULL,
        PRIMARY KEY (module, delta, rid)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_sessions (
        uid int unsigned NOT NULL,
        sid varchar(64) NOT NULL default '',
        hostname varchar(128) NOT NULL default '',
        timestamp int NOT NULL default '0',
        cache int NOT NULL default '0',
        session longtext,
        KEY uid (uid),
        PRIMARY KEY (sid),
        KEY timestamp (timestamp)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_sequences (
        name varchar(255) NOT NULL default '',
        id int unsigned NOT NULL default '0',
        PRIMARY KEY (name)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_node_counter (
        nid int NOT NULL default '0',
        totalcount bigint unsigned NOT NULL default '0',
        daycount mediumint unsigned NOT NULL default '0',
        timestamp int unsigned NOT NULL default '0',
        PRIMARY KEY (nid),
        KEY totalcount (totalcount),
        KEY daycount (daycount),
        KEY timestamp (timestamp)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_system (
        filename varchar(255) NOT NULL default '',
        name varchar(255) NOT NULL default '',
        type varchar(255) NOT NULL default '',
        description varchar(255) NOT NULL default '',
        status int NOT NULL default '0',
        throttle tinyint DEFAULT '0' NOT NULL,
        bootstrap int NOT NULL default '0',
        schema_version smallint NOT NULL default -1,
        weight int NOT NULL default '0',
        PRIMARY KEY (filename),
        KEY (weight)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_term_data (
        tid int unsigned NOT NULL auto_increment,
        vid int unsigned NOT NULL default '0',
        name varchar(255) NOT NULL default '',
        description longtext,
        weight tinyint NOT NULL default '0',
        PRIMARY KEY (tid),
        KEY vid (vid)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_term_hierarchy (
        tid int unsigned NOT NULL default '0',
        parent int unsigned NOT NULL default '0',
        KEY tid (tid),
        KEY parent (parent),
        PRIMARY KEY (tid, parent)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_term_node (
        nid int unsigned NOT NULL default '0',
        tid int unsigned NOT NULL default '0',
        KEY nid (nid),
        KEY tid (tid),
        PRIMARY KEY (tid,nid)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_term_relation (
        tid1 int unsigned NOT NULL default '0',
        tid2 int unsigned NOT NULL default '0',
        KEY tid1 (tid1),
        KEY tid2 (tid2)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_term_synonym (
        tid int unsigned NOT NULL default '0',
        name varchar(255) NOT NULL default '',
        KEY tid (tid),
        KEY name (name(3))
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_users (
        uid int unsigned NOT NULL default '0',
        name varchar(60) NOT NULL default '',
        pass varchar(32) NOT NULL default '',
        mail varchar(64) default '',
        mode tinyint NOT NULL default '0',
        sort tinyint default '0',
        threshold tinyint default '0',
        theme varchar(255) NOT NULL default '',
        signature varchar(255) NOT NULL default '',
        created int NOT NULL default '0',
        access int NOT NULL default '0',
        login int NOT NULL default '0',
        status tinyint NOT NULL default '0',
        timezone varchar(8) default NULL,
        language varchar(12) NOT NULL default '',
        picture varchar(255) NOT NULL DEFAULT '',
        init varchar(64) default '',
        data longtext,
        PRIMARY KEY (uid),
        UNIQUE KEY name (name),
        KEY access (access)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_users_roles (
        uid int unsigned NOT NULL default '0',
        rid int unsigned NOT NULL default '0',
        PRIMARY KEY (uid, rid)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_variable (
        name varchar(48) NOT NULL default '',
        value longtext NOT NULL,
        PRIMARY KEY (name)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_vocabulary (
        vid int unsigned NOT NULL auto_increment,
        name varchar(255) NOT NULL default '',
        description longtext,
        help varchar(255) NOT NULL default '',
        relations tinyint unsigned NOT NULL default '0',
        hierarchy tinyint unsigned NOT NULL default '0',
        multiple tinyint unsigned NOT NULL default '0',
        required tinyint unsigned NOT NULL default '0',
        tags tinyint unsigned NOT NULL default '0',
        module varchar(255) NOT NULL default '',
        weight tinyint NOT NULL default '0',
        PRIMARY KEY (vid)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_vocabulary_node_types (
        vid int unsigned NOT NULL DEFAULT '0',
        type varchar(32) NOT NULL DEFAULT '',
        PRIMARY KEY (vid, type)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
CREATE TABLE prefix_watchdog (
        wid int NOT NULL auto_increment,
        uid int NOT NULL default '0',
        type varchar(16) NOT NULL default '',
        message longtext NOT NULL,
        severity tinyint unsigned NOT NULL default '0',
        link varchar(255) NOT NULL default '',
        location text NOT NULL,
        referer varchar(128) NOT NULL default '',
        hostname varchar(128) NOT NULL default '',
        timestamp int NOT NULL default '0',
        PRIMARY KEY (wid),
        KEY (type)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */
INSERT INTO prefix_system (filename, name, type, description, status, throttle, bootstrap, schema_version) VALUES ('themes/engines/phptemplate/phptemplate.engine', 'phptemplate', 'theme_engine', '', 1, 0, 0, 0)
INSERT INTO prefix_system (filename, name, type, description, status, throttle, bootstrap, schema_version) VALUES ('themes/garland/page.tpl.php', 'garland', 'theme', 'themes/engines/phptemplate/phptemplate.engine', 1, 0, 0, 0)
INSERT INTO prefix_users (uid,name,mail) VALUES(0,'','')
INSERT INTO prefix_role (name) VALUES ('anonymous user')
INSERT INTO prefix_role (name) VALUES ('authenticated user')
INSERT INTO prefix_permission VALUES (1,'access content',0)
INSERT INTO prefix_permission VALUES (2,'access comments, access content, post comments, post comments without approval',0)
INSERT INTO prefix_variable (name,value) VALUES('theme_default', 's:7:"garland";')
INSERT INTO prefix_blocks (module,delta,theme,status,pages) VALUES('user', 0, 'garland', 1, '')
INSERT INTO prefix_blocks (module,delta,theme,status,pages) VALUES('user', 1, 'garland', 1, '')
INSERT INTO prefix_node_access VALUES (0, 0, 'all', 1, 0, 0)
INSERT INTO prefix_filter_formats (name, roles, cache) VALUES ('Filtered HTML',',1,2,',1)
INSERT INTO prefix_filter_formats (name, roles, cache) VALUES ('PHP code','',0)
INSERT INTO prefix_filter_formats (name, roles, cache) VALUES ('Full HTML','',1)
INSERT INTO prefix_filters VALUES (1,'filter',3,0)
INSERT INTO prefix_filters VALUES (1,'filter',0,1)
INSERT INTO prefix_filters VALUES (1,'filter',2,2)
INSERT INTO prefix_filters VALUES (2,'filter',1,0)
INSERT INTO prefix_filters VALUES (3,'filter',3,0)
INSERT INTO prefix_filters VALUES (3,'filter',2,1)
INSERT INTO prefix_variable (name,value) VALUES ('filter_html_1','i:1;')
INSERT INTO prefix_variable (name, value) VALUES ('node_options_forum', '%s')
INSERT INTO prefix_menu (mid, pid, path, title, description, weight, type) VALUES (2, 0, '', 'Primary links', '', 0, 115)
INSERT INTO prefix_variable VALUES ('menu_primary_menu', 'i:2;')
INSERT INTO prefix_variable VALUES ('menu_secondary_menu', 'i:2;')
  • Drupal

Recent posts

  • Teampassword manager's password generator is biased
  • Other vectors for SA-CORE-2014-005?
  • Lazy loading: hook_hook_info is for hook owners only.
  • "Always offline" problem in EA's Origin due to antivirus
  • From bug to exploit - Bakery SSO
more

Security reviews

I provide security reviews of custom code, contributed modules, themes and entire sites via LimoenGroen.

Contact us for a quote.

Follow @ustima

Copyright © 2021 by Heine Deelstra. All rights reserved.

  • Home
  • Drupal
  • About