This project has begun! - Page 2

Village Idiot · 12-29-2008, 01:59 AM

Are there any objection to my folder structure? I will implement it tomorrow afternoon if there are none.

Tanax · 12-29-2008, 12:55 PM

You wrote "Src" in the list, but not in the code snippet you provided. I assume you just forgot it?

Other than that, it looked great!

Village Idiot · 12-29-2008, 04:51 PM

Correct, I'll fix that.

Village Idiot · 12-29-2008, 05:56 PM

Here is a dump of the database I made. This file is also in the main directory of our code.

Code:

-- phpMyAdmin SQL Dump
-- version 2.11.9.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 29, 2008 at 05:54 PM
-- Server version: 5.0.67
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `justanot_phlox`
--

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

--
-- Table structure for table `friends`
--

CREATE TABLE IF NOT EXISTS `friends` (
  `friend_id` int(9) NOT NULL auto_increment,
  `friend_user_id` int(9) NOT NULL,
  `friend_other_user_id` int(9) NOT NULL,
  `status` int(1) NOT NULL,
  PRIMARY KEY  (`friend_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `friends`
--


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

--
-- Table structure for table `gallery`
--

CREATE TABLE IF NOT EXISTS `gallery` (
  `gallery_id` int(9) NOT NULL auto_increment,
  `gallery_user_id` int(9) NOT NULL,
  `gallery_name` int(9) NOT NULL,
  `gallery_createdate` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`gallery_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `gallery`
--


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

--
-- Table structure for table `gallery_comment`
--

CREATE TABLE IF NOT EXISTS `gallery_comment` (
  `gallery_comment_id` int(9) NOT NULL auto_increment,
  `gallery_comment_user_id` int(9) NOT NULL,
  `gallery_comment_gallery_id` int(9) NOT NULL,
  `gallery_comment_text` text collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`gallery_comment_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `gallery_comment`
--


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

--
-- Table structure for table `gallery_rating`
--

CREATE TABLE IF NOT EXISTS `gallery_rating` (
  `gallery_rating_id` int(9) NOT NULL auto_increment,
  `gallery_rating_user_id` int(9) NOT NULL,
  `gallery_rating_gallery_id` int(9) NOT NULL,
  `gallery_rating_score` int(1) NOT NULL,
  PRIMARY KEY  (`gallery_rating_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `gallery_rating`
--


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

--
-- Table structure for table `gallery_subscriptions`
--

CREATE TABLE IF NOT EXISTS `gallery_subscriptions` (
  `galSub_id` int(9) NOT NULL auto_increment,
  `galSub_user_id` int(9) NOT NULL,
  `galSub_gallery_id` int(9) NOT NULL,
  PRIMARY KEY  (`galSub_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `gallery_subscriptions`
--


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

--
-- Table structure for table `items`
--

CREATE TABLE IF NOT EXISTS `items` (
  `item_id` int(11) NOT NULL auto_increment,
  `item_user_id` int(11) NOT NULL,
  `item_name` varchar(255) collate latin1_general_ci NOT NULL,
  `item_link` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `items`
--


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

--
-- Table structure for table `item_comments`
--

CREATE TABLE IF NOT EXISTS `item_comments` (
  `item_comment_id` int(9) NOT NULL auto_increment,
  `item_comment_user_id` int(9) NOT NULL,
  `item_comment_text` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`item_comment_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `item_comments`
--


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

--
-- Table structure for table `item_ratings`
--

CREATE TABLE IF NOT EXISTS `item_ratings` (
  `item_rating_id` int(9) NOT NULL auto_increment,
  `item_rating_user_id` int(9) NOT NULL,
  `item_rating_item_id` int(9) NOT NULL,
  `item_rating_score` int(1) NOT NULL,
  PRIMARY KEY  (`item_rating_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `item_ratings`
--


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

--
-- Table structure for table `options`
--

CREATE TABLE IF NOT EXISTS `options` (
  `option_id` int(9) NOT NULL auto_increment,
  `option_name` int(9) NOT NULL,
  `option_value` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`option_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `options`
--


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

--
-- Table structure for table `templates`
--

CREATE TABLE IF NOT EXISTS `templates` (
  `template_id` int(9) NOT NULL auto_increment,
  `template_name` varchar(255) collate latin1_general_ci NOT NULL,
  `template_dir` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`template_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `templates`
--


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

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(9) NOT NULL auto_increment,
  `user_name` varchar(255) collate latin1_general_ci NOT NULL,
  `user_pass` varchar(255) collate latin1_general_ci NOT NULL,
  `user_rank` int(9) NOT NULL,
  `user_regdate` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `users`
--


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

--
-- Table structure for table `user_subscriptions`
--

CREATE TABLE IF NOT EXISTS `user_subscriptions` (
  `userSub_id` int(9) NOT NULL auto_increment,
  `userSub_user_id` int(9) NOT NULL,
  `userSub_target_user_id` int(9) NOT NULL,
  PRIMARY KEY  (`userSub_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `user_subscriptions`
--

Salathe · 12-29-2008, 06:09 PM

Hate to be annoying, but could you make the charset and collation for the tables UTF8? If we keep everything UTF8 then it'll safe a million headaches later on.

Also, please (briefly) document the changes made with each commit

Tanax · 12-29-2008, 06:09 PM

Looks great!
One question though.. what's the difference between gallery subscription, and user subscription?

Also, I just came up with something.
How about each user with a gallery, is allowed to create albums? For a better way of categorizing his/her photos?

Village Idiot · 12-29-2008, 06:16 PM

Quote:

Originally Posted by Salathe

Hate to be annoying, but could you make the charset and collation for the tables UTF8? If we keep everything UTF8 then it'll safe a million headaches later on.

I'll do that when I have a chance.

Quote:

Originally Posted by Salathe

Also, please (briefly) document the changes made with each commit

My apologies, I always forget to do that (it annoys people at work too)

Quote:

Originally Posted by Tanax

Looks great!
One question though.. what's the difference between gallery subscription, and user subscription?

Gallery subscription notifies you when something is added to a particular gallery, user subscription notifies you when that user creates a new gallery.

Quote:

Originally Posted by Tanax

Also, I just came up with something.
How about each user with a gallery, is allowed to create albums? For a better way of categorizing his/her photos?

I'll consider that, I'll get back to you later about this.

Aaron · 12-29-2008, 06:19 PM

Err... I think the database should be more dynamic. In other words: be able to change to work seamlessly with Vbulletin, PHPBB, SMF, etc.

Village Idiot · 12-29-2008, 06:20 PM

I know virtually nothing of the innards of any of those scripts, how would you suggest we do that? If there is a time to change the database, now is the time since so little code is written around it.

I have to go now, I will be back in a few hours.

Orc · 12-29-2008, 06:30 PM

Quote:

Originally Posted by Aaron

Err... I think the database should be more dynamic. In other words: be able to change to work seamlessly with Vbulletin, PHPBB, SMF, etc.

What do you mean by dynamic? I've worked with a lot of vBulletin scripts before, and I have modified them, and all I would have done was make a complete new table, or alter an existing table to fit the scripts requirements.

We could release the script, and then do it later, or set up a developers place for people to modify our script for themselves, and convert it to vb, phpbb, smf. w/e

Salathe · 12-29-2008, 07:04 PM

Quote:

Originally Posted by Aaron

Err... I think the database should be more dynamic. In other words: be able to change to work seamlessly with Vbulletin, PHPBB, SMF, etc.

Feel free to quote me on this later, but I think that would be a huge waste of time and effort. If it comes to it, later (much, much later) we could build some bridges between our application and other applications but I don't see it as an essential, or even useful, thing to work towards right now.

Other SQL comments:

For the primary keys, use unsigned ints. Why allow for, but never use, numbers from -2147483648 to zero?
users.user_pass doesn't need to be VARCHAR(255). Depending on what hashing we'll be using, it can be CHAR(32) or something like that.
It might be worth making item_comments.item_comment_text bigger, 255 chars might seem more than enough (cf. Twitter's 140 limit) but should we really rule out those commenters who like to write long, thought-out messages?
Maybe I'm just being blind, but I don't see a common key between the items and item_comments tables.
Perhaps consolidate multiple tables. A subscription is a subscription, why have two tables for essentially the same thing? If we later want to allow many different kinds of subscriptions (follow comments for a user, item, album, new items from a user, in a album or gallery...) would we need a new table for each of those? The same goes for the *_comments tables.
Standardise the naming. Some tables are plural, others not. Some columns underscore-delimited while others mix with camelcase. Personally I'd drop the table name prefixes for the columns too: gallery_rating.gallery_rating_gallery_id vs gallery_rating.gallery_id

Sorry for being so nitpicky!

Tanax · 12-29-2008, 07:36 PM

Quote:

Originally Posted by Village Idiot

Gallery subscription notifies you when something is added to a particular gallery, user subscription notifies you when that user creates a new gallery.

I see..
So a gallery, is like an album? Then ignore my previous suggestion.

Orc · 12-29-2008, 08:10 PM

I'm going with InnoDB as my Database Engine since it's faster, sound good? :P

Village Idiot · 12-29-2008, 10:15 PM

Quote:

Originally Posted by Salathe

Feel free to quote me on this later, but I think that would be a huge waste of time and effort. If it comes to it, later (much, much later) we could build some bridges between our application and other applications but I don't see it as an essential, or even useful, thing to work towards right now.

Other SQL comments:

For the primary keys, use unsigned ints. Why allow for, but never use, numbers from -2147483648 to zero?
users.user_pass doesn't need to be VARCHAR(255). Depending on what hashing we'll be using, it can be CHAR(32) or something like that.
It might be worth making item_comments.item_comment_text bigger, 255 chars might seem more than enough (cf. Twitter's 140 limit) but should we really rule out those commenters who like to write long, thought-out messages?
Maybe I'm just being blind, but I don't see a common key between the items and item_comments tables.
Perhaps consolidate multiple tables. A subscription is a subscription, why have two tables for essentially the same thing? If we later want to allow many different kinds of subscriptions (follow comments for a user, item, album, new items from a user, in a album or gallery...) would we need a new table for each of those? The same goes for the *_comments tables.
Standardise the naming. Some tables are plural, others not. Some columns underscore-delimited while others mix with camelcase. Personally I'd drop the table name prefixes for the columns too: gallery_rating.gallery_rating_gallery_id vs gallery_rating.gallery_id

Sorry for being so nitpicky!

Nitpicking is the road to perfection, perfectly ok.

For the primary keys, use unsigned ints. Why allow for, but never use, numbers from -2147483648 to zero?
Good idea

users.user_pass doesn't need to be VARCHAR(255). Depending on what hashing we'll be using, it can be CHAR(32) or something like that.
When we decide on which one we will use, I will change it accordingly.

It might be worth making item_comments.item_comment_text bigger, 255 chars might seem more than enough (cf. Twitter's 140 limit) but should we really rule out those commenters who like to write long, thought-out messages?
I meant to make that text, we will have a custom settable comment char limit.

Maybe I'm just being blind, but I don't see a common key between the items and item_comments tables.
My bad, will fix.

Perhaps consolidate multiple tables. A subscription is a subscription, why have two tables for essentially the same thing? If we later want to allow many different kinds of subscriptions (follow comments for a user, item, album, new items from a user, in a album or gallery...) would we need a new table for each of those? The same goes for the *_comments tables.
I'm neutral on this issue, It's normally my style to separate tables that server different purposes, but I see no reason against consolidating them.

Quote:

Originally Posted by Orc

I'm going with InnoDB as my Database Engine since it's faster, sound good? :P

Sure, I'll make the revision to the SQL file tonight.

Standardise the naming. Some tables are plural, others not. Some columns underscore-delimited while others mix with camelcase. Personally I'd drop the table name prefixes for the columns too: gallery_rating.gallery_rating_gallery_id vs gallery_rating.gallery_id
The naming is standardized, top level names (users, items) are plural while their supporting tables (item_comments) are singular. This is because the top level table hold multiple items, while the supplementary tables hold data for a single item of their parent.

This is again a difference in our style, I like to name all my columns with the table name in them (perhaps I should camelcase the table names opposed to underscore them). I am, once again, neutral on this issue since it is a matter of style.

sketchMedia · 12-30-2008, 12:40 AM

Quote:

I'm going with InnoDB as my Database Engine since it's faster, sound good? :P

Do we really need a transactional DB engine? or row level locking?

Orc · 12-30-2008, 12:43 AM

Quote:

Originally Posted by sketchMedia

Do we really need a transactional DB engine? or row level locking?

What's wrong with rolling back?

sketchMedia · 12-30-2008, 12:51 AM

Nothing is wrong with rolling back, do we need it though (talking from the project POV)?

I was always told that if the table was going to receive more SELECT queries than INSERTS, UPDATES, DELETES .... then MyISAM was the engine to choose, I could be wrong I'm no DB Admin.

Also I don't think innoDB supports full-text indexing.

Village Idiot · 12-30-2008, 12:58 AM

After doing some research, I have decided that we will stick with MyISAM as a default. We can add a feature to allow webmasters to change it to InnoDB. My reasoning is that MyISAM is faster for most average stuff, which is fine since we have nothing complicated in our script. It is also more reliable.

We really don't need row-level locking or transaction based queries due to simplicity. If we had numbers of very complex queries, transaction based queries would be a must. Row level locking would only be necessary if we were getting many requests a second.

Orc · 12-30-2008, 01:01 AM

Quote:

Originally Posted by Village Idiot

After doing some research, I have decided that we will stick with MyISAM as a default. We can add a feature to allow webmasters to change it to InnoDB. My reasoning is that MyISAM is faster for most average stuff, which is fine since we have nothing complicated in our script. It is also more reliable.

We really don't need row-level locking or transaction based queries due to simplicity. If we had numbers of very complex queries, transaction based queries would be a must. Row level locking would only be necessary if we were getting many requests a second.

Will do, besides MyISAM allows Full Text searching, and it comes to a search engine, then we can use that.

Haris · 12-31-2008, 11:05 PM

Hello Village,

I'm not an active user of TalkPHP but It would be an honour If I
can get a chance to use my PHP skills to help this project.

I'm PMing my google ID to you so that you can add me to the google
code project.

Kindest Regards
-H