trash/k42/psql_initial.sql

   1 -- MySQL dump 10.13  Distrib 5.5.24, for Linux (x86_64)
   2 --
   3 -- Host: localhost    Database: kyberia
   4 -- ------------------------------------------------------
   5 -- Server version       5.5.24-log
   6 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
   7 /*!40103 SET TIME_ZONE='+00:00' */;
   8 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
   9 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
  10 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO,POSTGRESQL' */;
  11 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
  12
  13 --
  14 -- XXX checks keys everywhere
  15 --
  16
  17 --
  18 -- Table structure for table "mail"
  19 --
  20
  21 DROP TYPE IF EXISTS "menum" CASCADE;
  22 CREATE TYPE menum AS ENUM ('no','to','from','both');
  23
  24 DROP TABLE IF EXISTS "mail";
  25 /*!40101 SET @saved_cs_client     = @@character_set_client */;
  26 /*!40101 SET character_set_client = utf8 */;
  27 CREATE TABLE "mail" (
  28   "mail_id" int NOT NULL,
  29   "mail_from" int NOT NULL DEFAULT '0',
  30   "mail_to" int NOT NULL DEFAULT '0',
  31   "mail_timestamp" timestamp,
  32   "mail_read" menum DEFAULT 'no',
  33   "mail_deleted" menum DEFAULT 'no',
  34   "mail_text" text
  35 -- CREATE UNIQUE INDEX mail_id ON mail (mail_id);
  36 --  PRIMARY KEY ("mail_id"),
  37 --  KEY "mail_id" ("mail_id"),
  38 --  KEY "mail_user_from_to" ("mail_from","mail_to"),
  39 --  KEY "mail_user_to_read" ("mail_to","mail_read")
  40 );
  41 /*!40101 SET character_set_client = @saved_cs_client */;
  42
  43 --
  44 -- Table structure for table "neurons"
  45 --
  46
  47 DROP TYPE IF EXISTS "link_enum" CASCADE;
  48 CREATE TYPE link_enum AS ENUM ('hard','soft','bookmark','synapse');
  49
  50 DROP TABLE IF EXISTS "neurons";
  51 /*!40101 SET @saved_cs_client     = @@character_set_client */;
  52 /*!40101 SET character_set_client = utf8 */;
  53 CREATE TABLE "neurons" (
  54 --  "synapse_id" serial NOT NULL,
  55   "dst" int DEFAULT NULL,
  56   "src" int DEFAULT NULL,
  57   "synapse_creator" int NOT NULL DEFAULT '0',
  58   "synapse_weight" double precision NOT NULL DEFAULT '1',
  59   "link" link_enum DEFAULT 'soft',
  60   "last_impulse" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  61   "synapse_created" timestamp DEFAULT NULL
  62 --  UNIQUE KEY "relation" ("dst","src"),
  63 --  KEY "central_link" ("dst","link"),
  64 --  KEY "src" ("src")
  65 );
  66 /*!40101 SET character_set_client = @saved_cs_client */;
  67
  68 --
  69 -- Table structure for table "node_access"
  70 --
  71 DROP TYPE IF EXISTS "acc_enum" CASCADE;
  72 CREATE TYPE acc_enum AS ENUM ('master','op','access','silence','ban','execute');
  73
  74 DROP TABLE IF EXISTS "node_access";
  75 /*!40101 SET @saved_cs_client     = @@character_set_client */;
  76 /*!40101 SET character_set_client = utf8 */;
  77 CREATE TABLE "node_access" (
  78   "node_id" int NOT NULL DEFAULT '0',
  79   "user_id" int NOT NULL DEFAULT '0',
  80   "node_permission" acc_enum DEFAULT NULL,
  81   "last_visit" timestamp DEFAULT NULL,
  82   "visits" int DEFAULT '0',
  83   "given_k" boolean DEFAULT false
  84 --  UNIQUE KEY "node_access" ("node_id","user_id"),
  85 );
  86
  87 /*!40101 SET character_set_client = @saved_cs_client */;
  88
  89
  90 --
  91 -- Table structure for table "nodes"
  92 --
  93
  94 DROP TABLE IF EXISTS "nodes";
  95 /*!40101 SET @saved_cs_client     = @@character_set_client */;
  96 /*!40101 SET character_set_client = utf8 */;
  97 CREATE TABLE "nodes" (
  98   "node_id" serial NOT NULL,
  99   "node_template_id" int DEFAULT NULL,
 100   "node_parent" int NOT NULL DEFAULT '0',
 101   "node_creator" int NOT NULL DEFAULT '0',
 102   "node_external_access" boolean DEFAULT false,
 103   "node_created" timestamp DEFAULT NULL,
 104   "node_lastchild_created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 105   "node_lastdescendant_created" timestamp DEFAULT NULL,
 106   "node_updated" timestamp DEFAULT NULL,
 107   "node_children_count" int DEFAULT '0',
 108   "node_k" int DEFAULT '0',
 109   "node_views" int DEFAULT NULL,
 110   "node_descendant_count" int DEFAULT NULL,
 111   "node_name" varchar(128) DEFAULT NULL,
 112   "node_external_link" varchar(128) DEFAULT NULL,
 113   "node_content" text
 114 --  PRIMARY KEY ("node_id"),
 115 --  KEY "node_name" ("node_name"),
 116 --  KEY "node_creator" ("node_creator"),
 117 --  KEY "external_link" ("node_external_link"),
 118 --  KEY "node_parent" ("node_parent")
 119 );
 120 /*!40101 SET character_set_client = @saved_cs_client */;
 121
 122 --
 123 -- Table structure for table "tiamat" (XXX sync with nodes)
 124 --
 125
 126 DROP TABLE IF EXISTS "tiamat";
 127 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 128 /*!40101 SET character_set_client = utf8 */;
 129 CREATE TABLE "tiamat" (
 130   "tiamat_node_id" int NOT NULL DEFAULT '0'
 131 );
 132 /*!40101 SET character_set_client = @saved_cs_client */;
 133
 134 --
 135 -- Table structure for table "users"
 136 -- XXX session string
 137 -- XXX register_hash
 138 -- XXX header_id vs node template_id
 139 -- XXX guild_id hardcoded 4738121
 140 -- XXX login == select node_name from nodes where node_id=user_id ??? => sync?
 141 -- "bookstyle" int(1) NOT NULL DEFAULT '0',
 142 -- "password_change_period" int DEFAULT NULL,
 143 --
 144
 145 DROP TYPE IF EXISTS "l_order" CASCADE;
 146 CREATE TYPE l_order AS ENUM ('asc','desc');
 147
 148
 149 DROP TABLE IF EXISTS "users";
 150 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 151 /*!40101 SET character_set_client = utf8 */;
 152 CREATE TABLE "users" (
 153   "user_id" int NOT NULL DEFAULT '0',
 154   "login" varchar(128) DEFAULT NULL,
 155   "xmpp" varchar(256) DEFAULT NULL,
 156   "password" varchar(230) NOT NULL,
 157   "register_hash" varchar(128) DEFAULT NULL,
 158   "email" varchar(256) DEFAULT NULL,
 159   "user_gpg_prv" text DEFAULT NULL,
 160   "user_gpg_pub" text DEFAULT NULL,
 161   "last_action" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 162   "session_string" varchar(64) DEFAULT NULL,
 163   "user_action" varchar(128) DEFAULT NULL,
 164   "user_action_id" int DEFAULT NULL,
 165   "listing_amount" int DEFAULT '32', -- XXX constant
 166   "listing_order" l_order DEFAULT 'desc',
 167   "user_k" int DEFAULT '0',
 168   "k_wallet" int DEFAULT '0',
 169   "header_id" int DEFAULT NULL,
 170   "mail_notify" boolean DEFAULT false,
 171   "acc_lockout" timestamp DEFAULT NULL,
 172   "moods" varchar(128) DEFAULT NULL,
 173   "invisible" boolean DEFAULT false,
 174   "login_retry" int NOT NULL DEFAULT '0',
 175   "date_last_login" timestamp,
 176   "date_password_changed" timestamp NOT NULL,
 177   "date_login_failed" timestamp NOT NULL,
 178   "user_setting_metadata" text NOT NULL,
 179   "guild_id" int
 180 --  PRIMARY KEY ("user_id"),
 181 --  UNIQUE KEY "session_string" ("session_string"),
 182 --  UNIQUE KEY "login" ("login")
 183 );
 184
 185 /*!40101 SET character_set_client = @saved_cs_client */;
 186
 187 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 188
 189 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
 190 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
 191 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
 192 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 193
 194 -- Dump completed on 2012-07-30 15:14:05