6 files modified
1 files added
New file |
| | |
| | | CREATE TABLE [dbo].[cache] (
|
| | | [cache_id] [int] IDENTITY (1, 1) NOT NULL ,
|
| | | [user_id] [int] NOT NULL ,
|
| | | [session_id] [varchar] (32) COLLATE Latin1_General_CI_AI NULL ,
|
| | | [cache_key] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [created] [datetime] NOT NULL ,
|
| | | [data] [text] COLLATE Latin1_General_CI_AI NOT NULL |
| | | ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
|
| | | GO
|
| | |
|
| | | CREATE TABLE [dbo].[contacts] (
|
| | | [contact_id] [int] IDENTITY (1, 1) NOT NULL ,
|
| | | [user_id] [int] NOT NULL ,
|
| | | [changed] [datetime] NOT NULL ,
|
| | | [del] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [email] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [firstname] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [surname] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [vcard] [text] COLLATE Latin1_General_CI_AI NULL |
| | | ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
|
| | | GO
|
| | |
|
| | | CREATE TABLE [dbo].[identities] (
|
| | | [identity_id] [int] IDENTITY (1, 1) NOT NULL ,
|
| | | [user_id] [int] NOT NULL ,
|
| | | [del] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [standard] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [organization] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [email] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [reply-to] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [bcc] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [signature] [text] COLLATE Latin1_General_CI_AI NOT NULL |
| | | ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
|
| | | GO
|
| | |
|
| | | CREATE TABLE [dbo].[messages] (
|
| | | [message_id] [int] IDENTITY (1, 1) NOT NULL ,
|
| | | [user_id] [int] NOT NULL ,
|
| | | [del] [tinyint] NOT NULL ,
|
| | | [cache_key] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [created] [datetime] NOT NULL ,
|
| | | [idx] [int] NOT NULL ,
|
| | | [uid] [int] NOT NULL ,
|
| | | [subject] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [from] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [to] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [cc] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [date] [datetime] NOT NULL ,
|
| | | [size] [int] NOT NULL ,
|
| | | [headers] [text] COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [structure] [text] COLLATE Latin1_General_CI_AI NULL |
| | | ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
|
| | | GO
|
| | |
|
| | | CREATE TABLE [dbo].[session] (
|
| | | [sess_id] [varchar] (32) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [created] [datetime] NOT NULL ,
|
| | | [changed] [datetime] NULL ,
|
| | | [ip] [varchar] (15) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [vars] [text] COLLATE Latin1_General_CI_AI NOT NULL |
| | | ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
|
| | | GO
|
| | |
|
| | | CREATE TABLE [dbo].[users] (
|
| | | [user_id] [int] IDENTITY (1, 1) NOT NULL ,
|
| | | [username] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [mail_host] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [alias] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [created] [datetime] NOT NULL ,
|
| | | [last_login] [datetime] NULL ,
|
| | | [language] [varchar] (5) COLLATE Latin1_General_CI_AI NOT NULL ,
|
| | | [preferences] [text] COLLATE Latin1_General_CI_AI NOT NULL |
| | | ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
|
| | | GO
|
| | |
|
| | | ALTER TABLE [dbo].[cache] WITH NOCHECK ADD |
| | | PRIMARY KEY CLUSTERED |
| | | (
|
| | | [cache_id]
|
| | | ) ON [PRIMARY] |
| | | GO
|
| | |
|
| | | ALTER TABLE [dbo].[contacts] WITH NOCHECK ADD |
| | | CONSTRAINT [PK_contacts_contact_id] PRIMARY KEY CLUSTERED |
| | | (
|
| | | [contact_id]
|
| | | ) ON [PRIMARY] |
| | | GO
|
| | |
|
| | | ALTER TABLE [dbo].[identities] WITH NOCHECK ADD |
| | | PRIMARY KEY CLUSTERED |
| | | (
|
| | | [identity_id]
|
| | | ) ON [PRIMARY] |
| | | GO
|
| | |
|
| | | ALTER TABLE [dbo].[messages] WITH NOCHECK ADD |
| | | PRIMARY KEY CLUSTERED |
| | | (
|
| | | [message_id]
|
| | | ) ON [PRIMARY] |
| | | GO
|
| | |
|
| | | ALTER TABLE [dbo].[session] WITH NOCHECK ADD |
| | | CONSTRAINT [PK_session_sess_id] PRIMARY KEY CLUSTERED |
| | | (
|
| | | [sess_id]
|
| | | ) ON [PRIMARY] |
| | | GO
|
| | |
|
| | | ALTER TABLE [dbo].[users] WITH NOCHECK ADD |
| | | CONSTRAINT [PK_users_user_id] PRIMARY KEY CLUSTERED |
| | | (
|
| | | [user_id]
|
| | | ) ON [PRIMARY] |
| | | GO
|
| | |
|
| | | ALTER TABLE [dbo].[cache] ADD |
| | | CONSTRAINT [DF_cache_user_id] DEFAULT ('0') FOR [user_id],
|
| | | CONSTRAINT [DF_cache_session_id] DEFAULT (null) FOR [session_id],
|
| | | CONSTRAINT [DF_cache_cache_key] DEFAULT ('') FOR [cache_key],
|
| | | CONSTRAINT [DF_cache_created] DEFAULT (getdate()) FOR [created]
|
| | | GO
|
| | |
|
| | | CREATE INDEX [IX_cache_user_id] ON [dbo].[cache]([user_id]) ON [PRIMARY]
|
| | | GO
|
| | |
|
| | | CREATE INDEX [IX_cache_cache_key] ON [dbo].[cache]([cache_key]) ON [PRIMARY]
|
| | | GO
|
| | |
|
| | | CREATE INDEX [IX_cache_session_id] ON [dbo].[cache]([session_id]) ON [PRIMARY]
|
| | | GO
|
| | |
|
| | | ALTER TABLE [dbo].[contacts] ADD |
| | | CONSTRAINT [DF_contacts_user_id] DEFAULT (0) FOR [user_id],
|
| | | CONSTRAINT [DF_contacts_changed] DEFAULT (getdate()) FOR [changed],
|
| | | CONSTRAINT [DF_contacts_del] DEFAULT ('0') FOR [del],
|
| | | CONSTRAINT [DF_contacts_name] DEFAULT ('') FOR [name],
|
| | | CONSTRAINT [DF_contacts_email] DEFAULT ('') FOR [email],
|
| | | CONSTRAINT [DF_contacts_firstname] DEFAULT ('') FOR [firstname],
|
| | | CONSTRAINT [DF_contacts_surname] DEFAULT ('') FOR [surname],
|
| | | CONSTRAINT [CK_contacts_del] CHECK ([del] = '1' or [del] = '0')
|
| | | GO
|
| | |
|
| | | CREATE INDEX [IX_contacts_user_id] ON [dbo].[contacts]([user_id]) ON [PRIMARY]
|
| | | GO
|
| | |
|
| | | ALTER TABLE [dbo].[identities] ADD |
| | | CONSTRAINT [DF_identities_user] DEFAULT ('0') FOR [user_id],
|
| | | CONSTRAINT [DF_identities_del] DEFAULT ('0') FOR [del],
|
| | | CONSTRAINT [DF_identities_standard] DEFAULT ('0') FOR [standard],
|
| | | CONSTRAINT [DF_identities_name] DEFAULT ('') FOR [name],
|
| | | CONSTRAINT [DF_identities_organization] DEFAULT ('') FOR [organization],
|
| | | CONSTRAINT [DF_identities_email] DEFAULT ('') FOR [email],
|
| | | CONSTRAINT [DF_identities_reply] DEFAULT ('') FOR [reply-to],
|
| | | CONSTRAINT [DF_identities_bcc] DEFAULT ('') FOR [bcc],
|
| | | CHECK ([standard] = '1' or [standard] = '0'),
|
| | | CHECK ([del] = '1' or [del] = '0')
|
| | | GO
|
| | |
|
| | | CREATE INDEX [IX_identities_user_id] ON [dbo].[identities]([user_id]) ON [PRIMARY]
|
| | | GO
|
| | |
|
| | | ALTER TABLE [dbo].[messages] ADD |
| | | CONSTRAINT [DF_messages_user_id] DEFAULT (0) FOR [user_id],
|
| | | CONSTRAINT [DF_messages_del] DEFAULT (0) FOR [del],
|
| | | CONSTRAINT [DF_messages_cache_key] DEFAULT ('') FOR [cache_key],
|
| | | CONSTRAINT [DF_messages_created] DEFAULT (getdate()) FOR [created],
|
| | | CONSTRAINT [DF_messages_idx] DEFAULT (0) FOR [idx],
|
| | | CONSTRAINT [DF_messages_uid] DEFAULT (0) FOR [uid],
|
| | | CONSTRAINT [DF_messages_subject] DEFAULT ('') FOR [subject],
|
| | | CONSTRAINT [DF_messages_from] DEFAULT ('') FOR [from],
|
| | | CONSTRAINT [DF_messages_to] DEFAULT ('') FOR [to],
|
| | | CONSTRAINT [DF_messages_cc] DEFAULT ('') FOR [cc],
|
| | | CONSTRAINT [DF_messages_date] DEFAULT (getdate()) FOR [date],
|
| | | CONSTRAINT [DF_messages_size] DEFAULT (0) FOR [size]
|
| | | GO
|
| | |
|
| | | CREATE INDEX [IX_messages_user_id] ON [dbo].[messages]([user_id]) ON [PRIMARY]
|
| | | GO
|
| | |
|
| | | CREATE INDEX [IX_messages_cache_key] ON [dbo].[messages]([cache_key]) ON [PRIMARY]
|
| | | GO
|
| | |
|
| | | CREATE INDEX [IX_messages_idx] ON [dbo].[messages]([idx]) ON [PRIMARY]
|
| | | GO
|
| | |
|
| | | CREATE INDEX [IX_messages_uid] ON [dbo].[messages]([uid]) ON [PRIMARY]
|
| | | GO
|
| | |
|
| | | ALTER TABLE [dbo].[session] ADD |
| | | CONSTRAINT [DF_session_sess_id] DEFAULT ('') FOR [sess_id],
|
| | | CONSTRAINT [DF_session_created] DEFAULT (getdate()) FOR [created],
|
| | | CONSTRAINT [DF_session_ip] DEFAULT ('') FOR [ip]
|
| | | GO
|
| | |
|
| | | ALTER TABLE [dbo].[users] ADD |
| | | CONSTRAINT [DF_users_username] DEFAULT ('') FOR [username],
|
| | | CONSTRAINT [DF_users_mail_host] DEFAULT ('') FOR [mail_host],
|
| | | CONSTRAINT [DF_users_alias] DEFAULT ('') FOR [alias],
|
| | | CONSTRAINT [DF_users_created] DEFAULT (getdate()) FOR [created],
|
| | | CONSTRAINT [DF_users_language] DEFAULT ('en') FOR [language]
|
| | | GO
|
| | |
|
| | |
| | | |
| | | // update user's record |
| | | $DB->query("UPDATE ".get_table_name('users')." |
| | | SET last_login=now() |
| | | SET last_login=".$DB->now()." |
| | | WHERE user_id=?", |
| | | $user_id); |
| | | } |
| | |
| | | |
| | | $DB->query("INSERT INTO ".get_table_name('users')." |
| | | (created, last_login, username, mail_host, alias, language) |
| | | VALUES (now(), now(), ?, ?, ?, ?)", |
| | | VALUES (".$DB->now().", ".$DB->now().", ?, ?, ?, ?)", |
| | | $user, |
| | | $host, |
| | | $user_email, |
| | |
| | | switch($this->db_provider) |
| | | { |
| | | case 'pgsql': |
| | | // PostgreSQL uses sequences |
| | | $result = &$this->db_handle->getOne("SELECT CURRVAL('$sequence')"); |
| | | |
| | | case 'mssql': |
| | | $result = &$this->db_handle->getOne("SELECT @@IDENTITY"); |
| | | |
| | | if (DB::isError($result)) |
| | | { |
| | | raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__, |
| | | 'message' => $result->getMessage()), TRUE, FALSE); |
| | | } |
| | | |
| | | return $result; |
| | | |
| | |
| | | } |
| | | |
| | | |
| | | /* |
| | | * Return SQL function for current time and date |
| | | * |
| | | * @return string SQL function to use in query |
| | | * @access public |
| | | */ |
| | | function now() |
| | | { |
| | | switch($this->db_provider) |
| | | { |
| | | case 'mssql': |
| | | return "getdate()"; |
| | | |
| | | default: |
| | | return "now()"; |
| | | } |
| | | } |
| | | |
| | | |
| | | /** |
| | | * Return SQL statement to convert a field value into a unix timestamp |
| | | * |
| | |
| | | { |
| | | case 'pgsql': |
| | | return "EXTRACT (EPOCH FROM $field)"; |
| | | break; |
| | | |
| | | case 'mssql': |
| | | return "datediff(s, '1970-01-01 00:00:00', $field)"; |
| | | |
| | | default: |
| | | return "UNIX_TIMESTAMP($field)"; |
| | |
| | | case 'mysqli': |
| | | case 'mysql': |
| | | case 'sqlite': |
| | | return "FROM_UNIXTIME($timestamp)"; |
| | | return sprintf("FROM_UNIXTIME(%d)", $timestamp); |
| | | |
| | | default: |
| | | return date("'Y-m-d H:i:s'", $timestamp); |
| | |
| | | { |
| | | $this->db->query( |
| | | "UPDATE ".get_table_name('cache')." |
| | | SET created=now(), |
| | | SET created=".$this->db->now().", |
| | | data=? |
| | | WHERE user_id=? |
| | | AND cache_key=?", |
| | |
| | | $this->db->query( |
| | | "INSERT INTO ".get_table_name('cache')." |
| | | (created, user_id, cache_key, data) |
| | | VALUES (now(), ?, ?, ?)", |
| | | VALUES (".$this->db->now().", ?, ?, ?)", |
| | | $_SESSION['user_id'], |
| | | $key, |
| | | $data); |
| | |
| | | $this->db->query( |
| | | "INSERT INTO ".get_table_name('messages')." |
| | | (user_id, del, cache_key, created, idx, uid, subject, ".$this->db->quoteIdentifier('from').", ".$this->db->quoteIdentifier('to').", cc, date, size, headers, structure) |
| | | VALUES (?, 0, ?, now(), ?, ?, ?, ?, ?, ?, ".$this->db->fromunixtime($headers->timestamp).", ?, ?, ?)", |
| | | VALUES (?, 0, ?, ".$this->db->now().", ?, ?, ?, ?, ?, ?, ".$this->db->fromunixtime($headers->timestamp).", ?, ?, ?)", |
| | | $_SESSION['user_id'], |
| | | $key, |
| | | $index, |
| | |
| | | session_decode($vars); |
| | | $DB->query("UPDATE ".get_table_name('session')." |
| | | SET vars=?, |
| | | changed=now() |
| | | changed=".$DB->now()." |
| | | WHERE sess_id=?", |
| | | $vars, |
| | | $key); |
| | |
| | | { |
| | | $DB->query("INSERT INTO ".get_table_name('session')." |
| | | (sess_id, vars, ip, created, changed) |
| | | VALUES (?, ?, ?, now(), now())", |
| | | VALUES (?, ?, ?, ".$DB->now().", ".$DB->now().")", |
| | | $key, |
| | | $vars, |
| | | $_SERVER['REMOTE_ADDR']); |
| | |
| | | // get all expired sessions |
| | | $sql_result = $DB->query("SELECT sess_id |
| | | FROM ".get_table_name('session')." |
| | | WHERE ".$DB->unixtimestamp('now()')."-".$DB->unixtimestamp('changed')." > ?", |
| | | WHERE ".$DB->unixtimestamp($DB->now())."-".$DB->unixtimestamp('changed')." > ?", |
| | | $maxlifetime); |
| | | |
| | | $a_exp_sessions = array(); |
| | |
| | | if (sizeof($a_write_sql)) |
| | | { |
| | | $DB->query("UPDATE $contacts_table |
| | | SET changed=now(), ".join(', ', $a_write_sql)." |
| | | SET changed=".$DB->now().", ".join(', ', $a_write_sql)." |
| | | WHERE contact_id=? |
| | | AND user_id=? |
| | | AND del<>1", |
| | |
| | | if (sizeof($a_insert_cols)) |
| | | { |
| | | $DB->query("INSERT INTO $contacts_table |
| | | (user_id, changed, del, ".join(', ', $a_insert_cols).") |
| | | VALUES (?, now(), 0, ".join(', ', $a_insert_values).")", |
| | | (user_id, changed, del, ".join(', ', $a_insert_cols).") |
| | | VALUES (?, ".$DB->now().", 0, ".join(', ', $a_insert_values).")", |
| | | $_SESSION['user_id']); |
| | | |
| | | $insert_id = $DB->insert_id(get_sequence_name('contacts')); |
| | |
| | | { |
| | | $DB->query("INSERT INTO ".get_table_name('contacts')." |
| | | (user_id, changed, del, name, email) |
| | | VALUES (?, now(), 0, ?, ?)", |
| | | VALUES (?, ".$DB->now().", 0, ?, ?)", |
| | | $_SESSION['user_id'], |
| | | $contact['name'], |
| | | $contact['mailto']); |