Posted By

sjard on 10/07/10


Tagged


Versions (?)

Create tables for the codeigniter library Tank Auth in PostgreSQL


 / Published in: SQL
 

URL: http://konyukhov.com/soft/tank_auth/

Tank Auth Version 1.0.7\r\nCodeIgniter Version 1.7.2

  1. -- --------------------------------------------------------
  2.  
  3. --
  4. -- Table structure for table ci_sessions
  5. --
  6.  
  7. CREATE TABLE ci_sessions (
  8. session_id varchar(40) NOT NULL DEFAULT '0',
  9. ip_address varchar(16) NOT NULL DEFAULT '0',
  10. user_agent varchar(150) NOT NULL,
  11. last_activity integer NOT NULL DEFAULT '0',
  12. user_data text NOT NULL,
  13. PRIMARY KEY (session_id)
  14. );
  15.  
  16. -- --------------------------------------------------------
  17.  
  18. --
  19. -- Table structure for table login_attempts
  20. --
  21.  
  22. CREATE TABLE login_attempts (
  23. id serial,
  24. ip_address varchar(40) NOT NULL,
  25. login varchar(50) NOT NULL,
  26. time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
  27. );
  28.  
  29. -- --------------------------------------------------------
  30.  
  31. --
  32. -- Table structure for table user_autologin
  33. --
  34.  
  35. CREATE TABLE user_autologin (
  36. key_id char(32) NOT NULL,
  37. user_id integer NOT NULL DEFAULT '0',
  38. user_agent varchar(150) NOT NULL,
  39. last_ip varchar(40) NOT NULL,
  40. last_login timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  41. PRIMARY KEY (key_id,user_id)
  42. );
  43.  
  44. -- --------------------------------------------------------
  45.  
  46. --
  47. -- Table structure for table user_profiles
  48. --
  49.  
  50. CREATE TABLE user_profiles (
  51. id serial,
  52. user_id integer NOT NULL,
  53. country varchar(20) DEFAULT NULL,
  54. website varchar(255) DEFAULT NULL
  55. );
  56.  
  57. -- --------------------------------------------------------
  58.  
  59. --
  60. -- Table structure for table users
  61. --
  62.  
  63. CREATE TABLE users (
  64. id serial,
  65. username varchar(50) NOT NULL,
  66. password varchar(255) NOT NULL,
  67. email varchar(100) NOT NULL,
  68. activated smallint NOT NULL DEFAULT '1',
  69. banned smallint NOT NULL DEFAULT '0',
  70. ban_reason varchar(255) DEFAULT NULL,
  71. new_password_key varchar(50) DEFAULT NULL,
  72. new_password_requested timestamp DEFAULT NULL,
  73. new_email varchar(100) DEFAULT NULL,
  74. new_email_key varchar(50) DEFAULT NULL,
  75. last_ip varchar(40) NOT NULL,
  76. last_login timestamp DEFAULT CURRENT_TIMESTAMP,
  77. created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  78. modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
  79. );
  80.  
  81. -- --------------------------------------------------------
  82.  
  83. --
  84. -- Update datetime columns on update
  85. --
  86.  
  87. CREATE OR REPLACE FUNCTION update_modified_column_time()
  88. RETURNS TRIGGER AS $$
  89. BEGIN
  90. NEW.time = now();
  91. RETURN NEW;
  92. END;
  93. $$ LANGUAGE 'plpgsql';
  94.  
  95. CREATE TRIGGER update_login_attempts_time BEFORE UPDATE
  96. ON login_attempts FOR EACH ROW EXECUTE PROCEDURE
  97. update_modified_column_time();
  98.  
  99. CREATE OR REPLACE FUNCTION update_modified_column_user_autologin()
  100. RETURNS TRIGGER AS $$
  101. BEGIN
  102. NEW.last_login = now();
  103. RETURN NEW;
  104. END;
  105. $$ LANGUAGE 'plpgsql';
  106.  
  107. CREATE TRIGGER update_login_attempts_user_autologin BEFORE UPDATE
  108. ON user_autologin FOR EACH ROW EXECUTE PROCEDURE
  109. update_modified_column_user_autologin();
  110.  
  111.  
  112. CREATE OR REPLACE FUNCTION update_modified_column_users()
  113. RETURNS TRIGGER AS $$
  114. BEGIN
  115. NEW.modified = now();
  116. RETURN NEW;
  117. END;
  118. $$ LANGUAGE 'plpgsql';
  119.  
  120. CREATE TRIGGER update_login_attempts_users BEFORE UPDATE
  121. ON users FOR EACH ROW EXECUTE PROCEDURE
  122. update_modified_column_users();

Report this snippet  

You need to login to post a comment.