[vidalia-svn] r2274: initial commit; initial db schema, and data class setup -- c (in weblinguist/trunk: . www www/include)

[opello@xxxxxxxxxxxxxxxxxxx]

Author: opello
Date: 2008-01-05 00:23:56 -0500 (Sat, 05 Jan 2008)
New Revision: 2274

Added:
   weblinguist/trunk/generate-db.sh
   weblinguist/trunk/schema.sql
   weblinguist/trunk/www/
   weblinguist/trunk/www/include/
   weblinguist/trunk/www/include/db.php
Log:
initial commit; initial db schema, and data class setup -- current plan is to use php5, php5-sqlite3 (can change, of course)

Added: weblinguist/trunk/generate-db.sh
===================================================================
--- weblinguist/trunk/generate-db.sh	                        (rev 0)
+++ weblinguist/trunk/generate-db.sh	2008-01-05 05:23:56 UTC (rev 2274)
@@ -0,0 +1,3 @@
+#!/bin/sh
+
+sqlite3 www/translate.db < schema.sql


Property changes on: weblinguist/trunk/generate-db.sh
___________________________________________________________________
Name: svn:executable
   + *

Added: weblinguist/trunk/schema.sql
===================================================================
--- weblinguist/trunk/schema.sql	                        (rev 0)
+++ weblinguist/trunk/schema.sql	2008-01-05 05:23:56 UTC (rev 2274)
@@ -0,0 +1,34 @@
+CREATE TABLE user (
+	id integer not null primary key autoincrement,
+	username text (255),
+	password text (32),
+	lastip text (9) not null,
+	lastlogin text not null default 'CURRENT_TIMESTAMP'
+);
+
+CREATE TABLE message (
+	id integer not null primary key autoincrement,
+	contextid integer not null,
+	message text not null
+);
+
+CREATE TABLE context (
+	id integer not null primary key autoincrement,
+	name text not null
+);
+
+CREATE TABLE language (
+	id integer not null primary key autoincrement,
+	name text not null,
+	code text (2) not null
+);
+
+CREATE TABLE translation (
+	id integer not null primary key autoincrement,
+	messageid integer not null,
+	languageid integer not null,
+	userid integer not null,
+	approved integer (1) not null default 0,
+	message text not null,
+	notes text
+);

Added: weblinguist/trunk/www/include/db.php
===================================================================
--- weblinguist/trunk/www/include/db.php	                        (rev 0)
+++ weblinguist/trunk/www/include/db.php	2008-01-05 05:23:56 UTC (rev 2274)
@@ -0,0 +1,104 @@
+<?php
+
+// base database class
+class translateDB {
+	private $db;
+
+	// {{{ constructor
+	public function __construct() {
+		$msg = $this->open();
+
+		if(! $this->db) {
+			die('Error opening the database: ' . $msg);
+		}
+	}
+	// }}}
+
+	// {{{ open
+	public function open() {
+		if(! $this->db) {
+			$msg = '';
+			$this->db = sqlite3_open('translate.db', 0666, $msg);
+
+			return $msg;
+		}
+
+		return TRUE;
+	}
+	// }}}
+
+	// {{{ close
+	public function close() {
+		sqlite_close($this->db);
+	}
+	// }}}
+
+	// {{{ query($query)
+	public function query($query) {
+		$msg = '';
+		$ret = sqlite_query($this->db, $query, SQLITE_ASSOC, $msg);
+		$amsg = array('message' => $msg);
+
+		if($ret !== FALSE) {
+			return array_merge(sqlite_fetch_array($ret), $amsg);
+		}
+
+		return $amsg;
+	}
+	// }}}
+
+	// {{{ exec($query)
+	public function exec($query) {
+		$msg = '';
+		$ret = sqlite_exec($this->db, $query, $msg);
+
+		if($ret === FALSE) {
+			return $msg;
+		} else {
+			return TRUE;
+		}
+	}
+	// }}}
+}
+
+// class to interact with the user table
+class user extends translateDB {
+	private function _hash($password) {
+		return md5('vidalia' . $password);
+	}
+
+	public function create($username, $password, $lastip, $lastlogin) {
+		$msg = '';
+		$query = sprintf('INSERT INTO user (username, password, lastip, lastlogin)'
+			. ' VALUES("%s", "%s", "%s", NOW())',
+			sqlite_escape_string($username),
+			$this->_hash($password),
+			sqlite_escape_string($lastip)
+		);
+
+		return $this->query($query)
+	}
+
+	public function getById($id) {
+		$msg = '';
+		$query = sprintf('SELECT id, username, lastip, lastlogin FROM user WHERE'
+			. ' id = %d',
+			$id
+		);
+
+		return $this->query($query);
+	}
+
+	// make verify -- true/false -- not return user?
+	public function verify($username, $password) {
+		$query = sprintf('SELECT id, username, lastip, lastlogin FROM user WHERE'
+			. ' username = "%s" AND password = "%s"',
+			sqlite_escape_string($username),
+			$this->_hash($password)
+		);
+
+		return $this->query($query);
+	}
+}
+
+?>