CodeIgniter Forums

Full Version: How to install dx_auth schema in postgres
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]coffak[/eluser]
Hi all,

I've downloaded the dx_auth library for user authentification.

SO, I create a new database, and call it "dx_auth". Then, I am triying to execute the schema script provided in the library (schema.sql) but Im getting this error:

Quote:ERROR: Syntax error close to ┬źNOT┬╗
LINE 15: CREATE TABLE IF NOT EXISTS `ci_sessions` (


My database is in postgres, and frankly I have never used postgres before, actually I have only two basic tables.... anyway, I am very sure that I am not doing it in the correct way because I haven't found this issue in others posts..

So, thanks in advance to anybody who can help me to install the schema in the database

Regards

PD: I will paste only the beggining of the script, i am pretty sure that the script is not the problem
Code:
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

-- --------------------------------------------------------

--
-- Table structure for table `ci_sessions`
--

CREATE TABLE IF NOT EXISTS `ci_sessions` (
  `session_id` varchar(40) collate utf8_bin NOT NULL default '0',
  `ip_address` varchar(16) collate utf8_bin NOT NULL default '0',
  `user_agent` varchar(150) collate utf8_bin NOT NULL,
  `last_activity` int(10) unsigned NOT NULL default '0',
  `user_data` text collate utf8_bin NOT NULL,
  PRIMARY KEY  (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `login_attempts`
--

CREATE TABLE IF NOT EXISTS `login_attempts` (
  `id` int(11) NOT NULL auto_increment,
  `ip_address` varchar(40) collate utf8_bin NOT NULL,
  `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

El Forum

[eluser]stuckinphp[/eluser]
MySQL syntax is a bit different to postgres. The error you posted is caused because in postgres there is no "if not exist" part in the create table statements available.

Note there is going to be more errors than just that one, for instance the engine line after the query needs to be comepletely removed, along with changing most of the variables / syntax.

https://secure.wikimedia.org/wikibooks/e..._And_Dirty

Has a good quick conversion table, but some things you'll need to google around for.

I've converted Tank Auth to be usable with postgres in the past, there was also a few nuisances in the auth php code it self that needed dealing to as well.

If you're not very experienced with php, CI, or postgres I'd suggest finding an auth plugin with native support for postgres. From memory there isn't many around. Looks like I'll be starting a postgres port of XTA tonight.

El Forum

[eluser]coffak[/eluser]
thanks stuckinphp

I'm going to use Tank_auth library and I've found the equivalent code for the database schema in postgres and here it is for those who might need it:


Code:
-- --------------------------------------------------------

--
-- Table structure for table ci_sessions
--

CREATE TABLE ci_sessions (
  session_id varchar(40)  NOT NULL DEFAULT '0',
  ip_address varchar(16)  NOT NULL DEFAULT '0',
  user_agent varchar(150)  NOT NULL,
  last_activity integer NOT NULL DEFAULT '0',
  user_data text  NOT NULL,
  PRIMARY KEY (session_id)
);

-- --------------------------------------------------------

--
-- Table structure for table login_attempts
--

CREATE TABLE login_attempts (
  id serial,
  ip_address varchar(40)  NOT NULL,
  login varchar(50)  NOT NULL,
  time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- --------------------------------------------------------

--
-- Table structure for table user_autologin
--

CREATE TABLE user_autologin (
  key_id char(32)  NOT NULL,
  user_id integer NOT NULL DEFAULT '0',
  user_agent varchar(150)  NOT NULL,
  last_ip varchar(40)  NOT NULL,
  last_login timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (key_id,user_id)
);

-- --------------------------------------------------------

--
-- Table structure for table user_profiles
--

CREATE TABLE user_profiles (
  id serial,
  user_id integer NOT NULL,
  country varchar(20)  DEFAULT NULL,
  website varchar(255)  DEFAULT NULL
);

-- --------------------------------------------------------

--
-- Table structure for table users
--

CREATE TABLE users (
  id serial,
  username varchar(50)  NOT NULL,
  password varchar(255)  NOT NULL,
  email varchar(100)  NOT NULL,
  activated smallint NOT NULL DEFAULT '1',
  banned smallint NOT NULL DEFAULT '0',
  ban_reason varchar(255)  DEFAULT NULL,
  new_password_key varchar(50)  DEFAULT NULL,
  new_password_requested timestamp DEFAULT NULL,
  new_email varchar(100)  DEFAULT NULL,
  new_email_key varchar(50)  DEFAULT NULL,
  last_ip varchar(40)  NOT NULL,
  last_login timestamp DEFAULT CURRENT_TIMESTAMP,
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- --------------------------------------------------------

--
-- Update datetime columns on update
--

CREATE OR REPLACE FUNCTION update_modified_column_time()
    RETURNS TRIGGER AS $$
    BEGIN
       NEW.time = now();
       RETURN NEW;
    END;
    $$ LANGUAGE 'plpgsql';

CREATE TRIGGER update_login_attempts_time BEFORE UPDATE
    ON login_attempts FOR EACH ROW EXECUTE PROCEDURE
    update_modified_column_time();

CREATE OR REPLACE FUNCTION update_modified_column_user_autologin()
    RETURNS TRIGGER AS $$
    BEGIN
       NEW.last_login = now();
       RETURN NEW;
    END;
    $$ LANGUAGE 'plpgsql';

CREATE TRIGGER update_login_attempts_user_autologin BEFORE UPDATE
    ON user_autologin FOR EACH ROW EXECUTE PROCEDURE
    update_modified_column_user_autologin();


CREATE OR REPLACE FUNCTION update_modified_column_users()
    RETURNS TRIGGER AS $$
    BEGIN
       NEW.modified = now();
       RETURN NEW;
    END;
    $$ LANGUAGE 'plpgsql';

CREATE TRIGGER update_login_attempts_users BEFORE UPDATE
    ON users FOR EACH ROW EXECUTE PROCEDURE
    update_modified_column_users();

Regards