Derek Sivers

Entrepreneur, programmer, avid student of life. I make useful things, and share what I learn.

Projects » MusicThoughts

A collection of inspiring thoughts about music. Launched in June 1999. Rewritten in 2009, translated into 10 languages.

Status: live

CODE: musicthoughts-api, musicthoughts-gem, musicthoughts-web

Schema

Everything revolves around a thought. It's a quote, by a famous author, that was contributed to musicthoughts.com by a contributor, and has at least one category.

Every thought has 10 thought_translations (en es fr de pt it ru ar ja zh). Even English is a translation. So all text comes from the thought_translations table, except the un-translatable things like author/contributor name and URL.

Thoughts contributed on the website do not appear in the list until an administrator approves them. The best succinct ones are marked “as random”, a flag that means it can be shown whenever a random thought is requested, like at the footer of every page.

The contributors are saved in my central “people” database, and linked back to that ID.

Site

Simple browsing and searching of thoughts, categories, authors, and contributors. The target audience is a musician looking for a little inspiration.

There's a form for adding a new thought to the database. A cookie remembers the contributor, to make it easy to add more thoughts without retyping their own info.

Search looks for that phrase in the categories themselves first, and if found redirects to that category page. If the phrase is found in author or contributor names, all that author's or contributor's thoughts are shown. Otherwise, thoughts with that phrase (in the current browsing language) are shown.

The entire site is multi-lingual, switchable by adding the language code to the end of the URL, or by form-post to set a language cookie, or by browser preference. The translations for all site text and thoughts are at http://lang.pro

Any time a list of thoughts is shown, each thought is linked back to its own URL, where the full info (categories, contributor) can be shown.

Currently (but optionally), the site has a few radically different CSS designs that can be switched between.

URLs

/home
links to all ways to browse the site
/t
fetch a random thought and 303 redirect to its URL
/t/123
show thought ID# 123 and all of its info, linked to category, author, and contributor pages
/cat/5
show category ID# 5 and all of its thoughts
/author
sorted list of authors with the most thoughts
/author/33
all thoughts by author ID#33
/contributor
sorted list of contributors with the most thoughts
/contributor/33
all thoughts by contributor ID#33
/search
empty search form
/search?q=search+term
search results plus empty search form
/add
GET: form to add a new thought (after guidelines)
POST: add thought and redirect to thanks
/language
POST-only: set language cookie and redirect to referring page
/style
switch between CSS designs by setting a cookie

Requirements

dodetails
remove users table
set thought_translations.thought to "body" in PHP version too
convert PHP version to person_id for contributor
set Rails config + contributor.rb model to use person.rb
Author model validationsname=127 unique & exists, url=255 max. url is real url
Author.thoughts(lang) gets thoughts by that author in that language
Author.fetch_by_name works with exact name, case insensitive, works with Chinese, etc
Author:tops(limit) gets authors and count"SELECT authors.*, COUNT(*) AS howmany FROM thoughts, authors WHERE thoughts.author_id=authors.id AND name NOT IN ('', 'anonymous', 'unknown') GROUP BY authors.id, authors.name, authors.url ORDER BY COUNT(*) DESC LIMIT %d"
Author:search finds even bits of namesearch for '近' will find '最近更'. 'and' will find 'Randy Newman'
Author:all_howmany authors with howmany() attribute, showing howmany approved thoughts that author has"SELECT authors.*, COUNT(thoughts.id) AS howmany FROM thoughts LEFT JOIN authors ON thoughts.author_id=authors.id WHERE thoughts.approved=TRUE AND authors.name NOT IN ('unknown', '', 'proverb') GROUP BY authors.id, authors.name, authors.url ORDER BY COUNT(thoughts.id) DESC"
Category.name returns translated name
Category.thoughts(:lang) returns all thoughts in that category and language
Category:search finds even bits of translated category namesearch for '樂' will find '音樂生意'. 'häf' will find 'Musikgeschäft'. (instead of database search I looped through parsed translated names just doing multibyte regex search)
Category:all_howmany() returns translated category + howmany thoughts it has"SELECT categories.*, COUNT(*) AS howmany FROM categories LEFT JOIN categories_thoughts ON categories.id=categories_thoughts.category_id LEFT JOIN thoughts ON categories_thoughts.thought_id=thoughts.id WHERE thoughts.approved=TRUE GROUP BY categories.id, categories.description ORDER BY description ASC";
Contributor model validationsperson_id required & exists in people.persons table
Contributor.person returns person. Contributor.name, Contributor.email. Contributor.url
Contributor:get_by_email
Contributor:tops(:limit) = Contributors with most thoughts + howmany"SELECT contributors.*, COUNT(*) AS howmany FROM thoughts, contributors WHERE thoughts.contributor_id=contributors.id AND name NOT IN ('', 'anonymous', 'unknown') AND thoughts.approved=TRUE GROUP BY contributors.id, contributors.shared_id, contributors.name, contributors.email, contributors.url, contributors.place ORDER BY COUNT(*) DESC LIMIT %d"
Contributor:search finds even bits of namesearch for '近' will find '最近更'. 'and' will find 'Randy Newman'
Contributor:all_howmany"SELECT contributors.*, COUNT(thoughts.id) AS howmany FROM thoughts LEFT JOIN contributors ON thoughts.contributor_id=contributors.id WHERE thoughts.approved=TRUE AND contributors.name NOT IN ('unknown', '', 'proverb') GROUP BY contributors.id, contributors.shared_id, contributors.name, contributors.email, contributors.url, contributors.place ORDER BY COUNT(thoughts.id) DESC"
clean URL helperIN='http://www.something.net/blog/2009/08/10/tralala.html?page=vuew' OUT='something.net'. Used for displaying URLs on screen.
languages menu:'en' => 'English', 'es' => 'Español', 'fr' => 'Français', 'de' => 'Deutsch', 'it' => 'Italiano', 'pt' => 'Português', 'ru' => 'Русский', 'zh' => '中文', 'ja' => '日本語', 'ar' => 'ﺎﻠﻋﺮﺒﻳﺓ'
sitemap.xml makersee PHP sitemap-maker.php
lang.pro thought_translation syncsee PHP sync-translations.php
lang.pro words syncsee PHP sync-words.php
Thought validationssource_url = 255 max & is real URL, author_id & contributor_id match, approved & as_rand = false. language in ('en', 'es', 'fr', 'ja', 'zh', 'de', 'it', 'pt', 'ru', 'ar')
Thought.delete cascades"DELETE FROM categories_thoughts WHERE thought_id={$this->id}" "DELETE FROM thought_translations WHERE thought_id={$this->id}" ("DELETE FROM thoughts WHERE id={$this->id}")
("SELECT id FROM thoughts WHERE author_id=" . $this->me['author_id']) IF NONE: "DELETE FROM authors WHERE id=" . $this->me['author_id']
("SELECT id FROM thoughts WHERE contributor_id=" . $this->me['contributor_id']) IF NONE: "DELETE FROM contributors WHERE id=" . $this->me['contributor_id']
Thought:add, in one post, adds thought then thought_translation with lang
Thought.author returns Author
Thought.contributor returns Contributor
Thought.categories returns array of its Categories
Thought.categories syncs to both remove and addIf it has 3, then boxes are unchecked to make it only 1, it deletes 3, adds 1. Maybe delete all each time, and add them back, to ensure.
Thought.intro(:words) shows this many words, no line breaks, then elipses.IMPORTANT: language-sensitive. if Chinese there will be no spaces ('資訊不等於學問。學問不等於智慧。智慧不等於真理。真理不等於美。美不等於愛。愛不等於音樂。音樂')
Thought.translations shows all ThoughtTranslations
Thought.send2langpro POSTs to lang.proPARAMS: 'project_id=1&remote_id=%d&lang=%s&original=' . urlencode($t->thought()) .'&context_url=' . urlencode('http://musicthoughts.com/t/' . $this->id)
POST: 'curl -s -i -X POST -d "' . $post . '" -u user:pass http://lang.pro/original'
Thought:find(:id, :lang) falls back to English if that :lang not found
Thought:random1(:lang) returns one thought in lang where as_rand=TRUE"SELECT thoughts.*, thought_translations.thought FROM thoughts LEFT JOIN thought_translations ON thoughts.id=thought_translations.thought_id WHERE as_rand=TRUE AND thought_translations.lang='$lang' ORDER BY RANDOM() LIMIT 1"
Thought:unapproved1 returns next unapproved thought, for admins:"SELECT * FROM thoughts WHERE approved=FALSE ORDER BY id ASC LIMIT 1"
Thought:in_category(:id, :lang)"SELECT thoughts.*, thought_translations.thought FROM thoughts, categories_thoughts, thought_translations WHERE thoughts.id=thought_translations.thought_id AND thoughts.id=categories_thoughts.thought_id AND thought_translations.lang='$lang' AND categories_thoughts.category_id=$id AND approved=TRUE ORDER BY id DESC"
Thought:for_author(:id, :lang)"SELECT thoughts.*, thought_translations.thought FROM thoughts, thought_translations WHERE thoughts.id=thought_translations.thought_id AND thought_translations.lang='$lang' AND author_id=:id AND approved=TRUE ORDER BY id DESC"
Thought:for_contributor(:id, :lang)"SELECT thoughts.*, thought_translations.thought FROM thoughts, thought_translations WHERE thoughts.id=thought_translations.thought_id AND thought_translations.lang='$lang' AND contributr_id=:id AND approved=TRUE ORDER BY id DESC"
Thought.newest(:limit, :lang)
Thought:search_for(:query, :lang)"SELECT thoughts.*, thought_translations.thought FROM thoughts, thought_translations WHERE thoughts.id=thought_translations.thought_id AND thought_translations.lang='$lang' AND approved=TRUE AND LOWER(thought) LIKE '%" . pg_escape_string(strtolower(:query)) . "%' ORDER BY id DESC"
Thought:search(:query, :lang) = big search of Thought, Author, ContributorUsed for big search box. Example: someone searching "Miles Davis" would need to see thoughts where Miles Davis is Author, but also thoughts where "Miles Davis" is part of thought string, even if said by someone else. And just in case: where Miles Davis is contributor. All thoughts that meet this are shown, categorized.
Thought:unapproved"SELECT * FROM thoughts WHERE approved=FALSE ORDER BY id DESC"
ThoughtTranslation:by_id_lang(:thought_id, :lang)"SELECT * FROM thought_translations WHERE thought_id=%d AND lang='%s'"
ThoughtTranslation validationslang exists, thought exists, translation not empty
GET: / = links to all ways to browse the site
GET: /t = select random thought and 303-redirect to its URL
GET: /t/123 = show that thought + its info
PUT: /t/123 = update that thought (if admin) (treat all info like one: categories, author, contributor)
POST: /t = add new thought (+ its related info)
DELETE: /t/123 = delete a thought (+ its related info) - (if admin)
GET: /cat = list of categories
POST: /cat = add new category (if admin)
GET: /cat/4 = all thoughts in this category
PUT: /cat/4 = update category language-key (if admin)
DELETE: /cat/4 = delete a category (if admin): refuse if it still has any thoughts
GET: /author = all authors, sorted with most thoughts at top
GET: /author/123 = this author + all his thoughts
PUT: /author/123 = update an author's info (if admin)
GET: /contributor = all contributors, sorted with most thoughts at top
GET: /contributor/123 = this contributor + all his thoughts
PUT: /contributor/123 = update a contributor's info (if admin)
for REST interface, author or contributor not separateadded by adding them with a thought. deleted when last thought with them is removed.
GET: /search?q=search+term = list of thoughts, categories, authors with that search result
GET: /add = xhtml form to add new thought (posts to /t)
GET: /thanks = thanks for adding thought
GET: /style shows list of css styles, shows current one
PUT: /style sets 'css' cookie, redirecting to referer if in musicthoughts.com
GET: /language shows list of languages, shows current one
PUT: /language sets 'lang' cookie, redirecting to referer if in musicthoughts.com
REST server language-switching. DISCUSS.1st priority = lang set at end of GET URL (musicthoughts.com/t/123/zh)
2nd priority = lang set in COOKIE ['lang'] (or just in HTTP request somehow?)
client-site language-switching1st priority = lang set at end of GET URL (musicthoughts.com/t/123/zh)
2nd priority = lang set in COOKIE ['lang']
3rd priority = language mapped from their IP location
4th priority = lang set in their browser
site goes rtl for Arabicmight need to give it alternate stylesheet when in Arabic, too. check for existence of $style-$lang.css first. if none, default to $style.css
CSS switching between approved stylesif(isset($_POST['css']) && in_array($_POST['css'], $style_whitelist)) { setcookie('css', $_POST['css'], time() + 36000, '/'); $qq->redirect(''); }
/add POSTed form remembers these as cookies for future: ('contributor', 'contributor_place', 'contributor_email', 'contributor_url')
/add POSTed redirects back to /add with flash warning unless validations:strlen($_POST['thought']) > 10
strlen($_POST['author']) >= 2
is_valid_email_address($_POST['contributor_email'])
/add POSTed searches people.person for contributor_email, uses it if found, adds if not
/add POSTed searches for author name (case insensitive), uses it if found, adds if not.
GET: /unapproved = (if admin) list of unapproved thoughtsgoes either to GET /edit/%d or DELETE /t/%d
GET: /edit/123 = (if admin) form to edit all aspects of a thought.submits as PUT to /t/123

database schema

BEGIN;
CREATE SCHEMA musicthoughts;
SET search_path = musicthoughts;

CREATE TABLE categories (
	id serial primary key,
	description varchar(64) UNIQUE
);

CREATE TABLE authors (
	id serial primary key,
	name varchar(127) UNIQUE,
	url varchar(255)
);

CREATE TABLE contributors (
	id serial primary key,
	shared_id integer UNIQUE, 
	name varchar(127),
	email varchar(127) UNIQUE,
	url varchar(255),
	place varchar(255)
);

CREATE TABLE thoughts (
	id serial primary key,
	approved boolean default false not null,
	author_id integer not null REFERENCES authors(id),
	contributor_id integer not null REFERENCES contributors(id),
	created_at date not null default CURRENT_DATE,
	as_rand boolean not null default false,
	source_url varchar(255)
);

CREATE TABLE thought_translations (
	id serial primary key,
	thought_id integer not null REFERENCES thoughts(id),
	lang char(2) not null default 'en',
	thought text,
	UNIQUE (thought_id, lang)
);

CREATE TABLE categories_thoughts (
	thought_id integer not null REFERENCES thoughts(id),
	category_id integer not null REFERENCES categories(id),
	PRIMARY KEY (thought_id, category_id)
);
CREATE INDEX ctti ON categories_thoughts(thought_id);
CREATE INDEX ctci ON categories_thoughts(category_id);

COMMIT;