From d036b432cb49742e26e66d652f67e54e9a9a8cd2 Mon Sep 17 00:00:00 2001 From: hrcerqueira Date: Sat, 23 Jan 2010 18:20:03 +0000 Subject: Sql file for installing fbconnect table. --- sql_scripts/update_2010_01_23.sql | 9 +++++++++ 1 file changed, 9 insertions(+) create mode 100755 sql_scripts/update_2010_01_23.sql (limited to 'sql_scripts') diff --git a/sql_scripts/update_2010_01_23.sql b/sql_scripts/update_2010_01_23.sql new file mode 100755 index 00000000..621207be --- /dev/null +++ b/sql_scripts/update_2010_01_23.sql @@ -0,0 +1,9 @@ +CREATE TABLE `fbconnect_fbassociation` ( + `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, + `user_id` integer NOT NULL, + `fbuid` varchar(12) NOT NULL UNIQUE +) +; +ALTER TABLE `fbconnect_fbassociation` ADD CONSTRAINT `user_id_refs_id_3534873d` +FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`); +CREATE INDEX `fbconnect_fbassociation_user_id` ON `fbconnect_fbassociation` (`user_id`); -- cgit v1.2.3-1-g7c22 From ad2e22b999b3b795f60e0f95abcaf3b339567294 Mon Sep 17 00:00:00 2001 From: Evgeny Fadeev Date: Sun, 24 Jan 2010 19:53:24 -0500 Subject: recaptcha for conventional registration\n\ simpler email subscription form at registration\n\ fixed urls in rss feed\n\ added experimental remote password login api (cleartext password for remote site entered locally)\n\ included example for Mediawiki Authentication plugin\n\ very simple message to everyone management command --- sql_scripts/100108_upgrade_ef.sql | 4 ++++ sql_scripts/badges.sql | 37 +++++++++++++++++++++++++++++++++++++ 2 files changed, 41 insertions(+) create mode 100644 sql_scripts/100108_upgrade_ef.sql create mode 100644 sql_scripts/badges.sql (limited to 'sql_scripts') diff --git a/sql_scripts/100108_upgrade_ef.sql b/sql_scripts/100108_upgrade_ef.sql new file mode 100644 index 00000000..1c9a5c1c --- /dev/null +++ b/sql_scripts/100108_upgrade_ef.sql @@ -0,0 +1,4 @@ +alter table auth_user add column hide_ignored_questions tinyint(1) not NULL; +update auth_user set hide_ignored_questions=0; +alter table auth_user add column tag_filter_setting varchar(16) not NULL; +update auth_user set tag_filter_setting='ignored'; diff --git a/sql_scripts/badges.sql b/sql_scripts/badges.sql new file mode 100644 index 00000000..f47e067a --- /dev/null +++ b/sql_scripts/badges.sql @@ -0,0 +1,37 @@ +INSERT INTO `badge` ( `id`, `name`, `type`, `slug`, `description`, `multiple`, `awarded_count`) VALUES +(1, 'Disciplined', 3, 'disciplined', 'Deleted own post with score of 3 or higher', 1, 0), +(2, 'Peer Pressure', 3, 'peer-pressure', 'Deleted own post with score of -3 or lower', 1, 0), +(3, 'Nice answer', 3, 'nice-answer', 'Answer voted up 10 times', 1, 0), +(4, 'Nice Question', 3, 'nice-question', 'Question voted up 10 times', 1, 0), +(5, 'Pundit', 3, 'pundit', 'Left 10 comments with score of 10 or more', 0, 0), +(6, 'Popular Question', 3, 'popular-question', 'Asked a question with 1,000 views', 1, 0), +(7, 'Citizen patrol', 3, 'citizen-patrol', 'First flagged post', 0, 0), +(8, 'Cleanup', 3, 'cleanup', 'First rollback', 0, 0), +(9, 'Critic', 3, 'critic', 'First down vote', 0, 0), +(10, 'Editor', 3, 'editor', 'First edit', 0, 0), +(11, 'Organizer', 3, 'organizer', 'First retag', 0, 0), +(12, 'Scholar', 3, 'scholar', 'First accepted answer on your own question', 0, 0), +(13, 'Student', 3, 'student', 'Asked first question with at least one up vote', 0, 0), +(14, 'Supporter', 3, 'supporter', 'First up vote', 0, 0), +(15, 'Teacher', 3, 'teacher', 'Answered first question with at least one up vote', 0, 0), +(16, 'Autobiographer', 3, 'autobiographer', 'Completed all user profile fields', 0, 0), +(17, 'Self-Learner', 3, 'self-learner', 'Answered your own question with at least 3 up votes', 1, 0), +(18, 'Great Answer', 1, 'great-answer', 'Answer voted up 100 times', 1, 0), +(19, 'Great Question', 1, 'great-question', 'Question voted up 100 times', 1, 0), +(20, 'Stellar Question', 1, 'stellar-question', 'Question favorited by 100 users', 1, 0), +(21, 'Famous question', 1, 'famous-question', 'Asked a question with 10,000 views', 1, 0), +(22, 'Alpha', 2, 'alpha', 'Actively participated in the private alpha', 0, 0), +(23, 'Good Answer', 2, 'good-answer', 'Answer voted up 25 times', 1, 0), +(24, 'Good Question', 2, 'good-question', 'Question voted up 25 times', 1, 0), +(25, 'Favorite Question', 2, 'favorite-question', 'Question favorited by 25 users', 1, 0), +(26, 'Civic duty', 2, 'civic-duty', 'Voted 300 times', 0, 0), +(27, 'Strunk & White', 2, 'strunk-and-white', 'Edited 100 entries', 0, 0), +(28, 'Generalist', 2, 'generalist', 'Active in many different tags', 0, 0), +(29, 'Expert', 2, 'export', 'Very active in one tag', 0, 0), +(30, 'Yearling', 2, 'yearling', 'Active member for a year', 0, 0), +(31, 'Notable Question', 2, 'notable-question', 'Asked a question with 2,500 views', 1, 0), +(32, 'Enlightened', 2, 'enlightened', 'First answer was accepted with at least 10 up votes', 0, 0), +(33, 'Beta', 2, 'beta', 'Actively participated in the private beta', 0, 0), +(34, 'Guru', 2, 'guru', 'Accepted answer and voted up 40 times', 1, 0), +(35, 'Necromancer', 2, 'necromancer', 'Answered a question more than 60 days later with at least 5 votes', 1, 0), +(36, 'Taxonomist', 2, 'taxonomist', 'Created a tag used by 50 questions', 1, 0); -- cgit v1.2.3-1-g7c22 From 55303308437e527b3e48cca65ca162bac39437c7 Mon Sep 17 00:00:00 2001 From: Rick Ross Date: Sun, 31 Jan 2010 14:07:28 -0500 Subject: Updated to populate badge table in a way that is compatible with boy MySQL and PostgreSQL --- sql_scripts/badges.sql | 74 +++++++++++++++++++++++++------------------------- 1 file changed, 37 insertions(+), 37 deletions(-) (limited to 'sql_scripts') diff --git a/sql_scripts/badges.sql b/sql_scripts/badges.sql index f47e067a..5fd03d18 100644 --- a/sql_scripts/badges.sql +++ b/sql_scripts/badges.sql @@ -1,37 +1,37 @@ -INSERT INTO `badge` ( `id`, `name`, `type`, `slug`, `description`, `multiple`, `awarded_count`) VALUES -(1, 'Disciplined', 3, 'disciplined', 'Deleted own post with score of 3 or higher', 1, 0), -(2, 'Peer Pressure', 3, 'peer-pressure', 'Deleted own post with score of -3 or lower', 1, 0), -(3, 'Nice answer', 3, 'nice-answer', 'Answer voted up 10 times', 1, 0), -(4, 'Nice Question', 3, 'nice-question', 'Question voted up 10 times', 1, 0), -(5, 'Pundit', 3, 'pundit', 'Left 10 comments with score of 10 or more', 0, 0), -(6, 'Popular Question', 3, 'popular-question', 'Asked a question with 1,000 views', 1, 0), -(7, 'Citizen patrol', 3, 'citizen-patrol', 'First flagged post', 0, 0), -(8, 'Cleanup', 3, 'cleanup', 'First rollback', 0, 0), -(9, 'Critic', 3, 'critic', 'First down vote', 0, 0), -(10, 'Editor', 3, 'editor', 'First edit', 0, 0), -(11, 'Organizer', 3, 'organizer', 'First retag', 0, 0), -(12, 'Scholar', 3, 'scholar', 'First accepted answer on your own question', 0, 0), -(13, 'Student', 3, 'student', 'Asked first question with at least one up vote', 0, 0), -(14, 'Supporter', 3, 'supporter', 'First up vote', 0, 0), -(15, 'Teacher', 3, 'teacher', 'Answered first question with at least one up vote', 0, 0), -(16, 'Autobiographer', 3, 'autobiographer', 'Completed all user profile fields', 0, 0), -(17, 'Self-Learner', 3, 'self-learner', 'Answered your own question with at least 3 up votes', 1, 0), -(18, 'Great Answer', 1, 'great-answer', 'Answer voted up 100 times', 1, 0), -(19, 'Great Question', 1, 'great-question', 'Question voted up 100 times', 1, 0), -(20, 'Stellar Question', 1, 'stellar-question', 'Question favorited by 100 users', 1, 0), -(21, 'Famous question', 1, 'famous-question', 'Asked a question with 10,000 views', 1, 0), -(22, 'Alpha', 2, 'alpha', 'Actively participated in the private alpha', 0, 0), -(23, 'Good Answer', 2, 'good-answer', 'Answer voted up 25 times', 1, 0), -(24, 'Good Question', 2, 'good-question', 'Question voted up 25 times', 1, 0), -(25, 'Favorite Question', 2, 'favorite-question', 'Question favorited by 25 users', 1, 0), -(26, 'Civic duty', 2, 'civic-duty', 'Voted 300 times', 0, 0), -(27, 'Strunk & White', 2, 'strunk-and-white', 'Edited 100 entries', 0, 0), -(28, 'Generalist', 2, 'generalist', 'Active in many different tags', 0, 0), -(29, 'Expert', 2, 'export', 'Very active in one tag', 0, 0), -(30, 'Yearling', 2, 'yearling', 'Active member for a year', 0, 0), -(31, 'Notable Question', 2, 'notable-question', 'Asked a question with 2,500 views', 1, 0), -(32, 'Enlightened', 2, 'enlightened', 'First answer was accepted with at least 10 up votes', 0, 0), -(33, 'Beta', 2, 'beta', 'Actively participated in the private beta', 0, 0), -(34, 'Guru', 2, 'guru', 'Accepted answer and voted up 40 times', 1, 0), -(35, 'Necromancer', 2, 'necromancer', 'Answered a question more than 60 days later with at least 5 votes', 1, 0), -(36, 'Taxonomist', 2, 'taxonomist', 'Created a tag used by 50 questions', 1, 0); +INSERT INTO badge ( id, name, type, slug, description, multiple, awarded_count) VALUES +(1, 'Disciplined', 3, 'disciplined', 'Deleted own post with score of 3 or higher', TRUE, 0), +(2, 'Peer Pressure', 3, 'peer-pressure', 'Deleted own post with score of -3 or lower', TRUE, 0), +(3, 'Nice answer', 3, 'nice-answer', 'Answer voted up 10 times', TRUE, 0), +(4, 'Nice Question', 3, 'nice-question', 'Question voted up 10 times', TRUE, 0), +(5, 'Pundit', 3, 'pundit', 'Left 10 comments with score of 10 or more', FALSE, 0), +(6, 'Popular Question', 3, 'popular-question', 'Asked a question with 1,000 views', TRUE, 0), +(7, 'Citizen patrol', 3, 'citizen-patrol', 'First flagged post', FALSE, 0), +(8, 'Cleanup', 3, 'cleanup', 'First rollback', FALSE, 0), +(9, 'Critic', 3, 'critic', 'First down vote', FALSE, 0), +(10, 'Editor', 3, 'editor', 'First edit', FALSE, 0), +(11, 'Organizer', 3, 'organizer', 'First retag', FALSE, 0), +(12, 'Scholar', 3, 'scholar', 'First accepted answer on your own question', FALSE, 0), +(13, 'Student', 3, 'student', 'Asked first question with at least one up vote', FALSE, 0), +(14, 'Supporter', 3, 'supporter', 'First up vote', FALSE, 0), +(15, 'Teacher', 3, 'teacher', 'Answered first question with at least one up vote', FALSE, 0), +(16, 'Autobiographer', 3, 'autobiographer', 'Completed all user profile fields', FALSE, 0), +(17, 'Self-Learner', 3, 'self-learner', 'Answered your own question with at least 3 up votes', TRUE, 0), +(18, 'Great Answer', 1, 'great-answer', 'Answer voted up 100 times', TRUE, 0), +(19, 'Great Question', 1, 'great-question', 'Question voted up 100 times', TRUE, 0), +(20, 'Stellar Question', 1, 'stellar-question', 'Question favorited by 100 users', TRUE, 0), +(21, 'Famous question', 1, 'famous-question', 'Asked a question with 10,000 views', TRUE, 0), +(22, 'Alpha', 2, 'alpha', 'Actively participated in the private alpha', FALSE, 0), +(23, 'Good Answer', 2, 'good-answer', 'Answer voted up 25 times', TRUE, 0), +(24, 'Good Question', 2, 'good-question', 'Question voted up 25 times', TRUE, 0), +(25, 'Favorite Question', 2, 'favorite-question', 'Question favorited by 25 users', TRUE, 0), +(26, 'Civic duty', 2, 'civic-duty', 'Voted 300 times', FALSE, 0), +(27, 'Strunk & White', 2, 'strunk-and-white', 'Edited 100 entries', FALSE, 0), +(28, 'Generalist', 2, 'generalist', 'Active in many different tags', FALSE, 0), +(29, 'Expert', 2, 'export', 'Very active in one tag', FALSE, 0), +(30, 'Yearling', 2, 'yearling', 'Active member for a year', FALSE, 0), +(31, 'Notable Question', 2, 'notable-question', 'Asked a question with 2,500 views', TRUE, 0), +(32, 'Enlightened', 2, 'enlightened', 'First answer was accepted with at least 10 up votes', FALSE, 0), +(33, 'Beta', 2, 'beta', 'Actively participated in the private beta', FALSE, 0), +(34, 'Guru', 2, 'guru', 'Accepted answer and voted up 40 times', TRUE, 0), +(35, 'Necromancer', 2, 'necromancer', 'Answered a question more than 60 days later with at least 5 votes', TRUE, 0), +(36, 'Taxonomist', 2, 'taxonomist', 'Created a tag used by 50 questions', TRUE, 0); -- cgit v1.2.3-1-g7c22 From 23245d01db7eeee3243d8eaf33129b50b44dcdb0 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 2 Feb 2010 20:25:25 +0000 Subject: added full text search support for postgresql, database setup is done as a response to the post_syncdb signal. search is done against question summary, title and tags with crescent weight respectively. everything works but is still in a early stage of development --- sql_scripts/pg_fts_install.sql | 41 +++++++++++++++++++++++++++++++++++++++++ 1 file changed, 41 insertions(+) create mode 100644 sql_scripts/pg_fts_install.sql (limited to 'sql_scripts') diff --git a/sql_scripts/pg_fts_install.sql b/sql_scripts/pg_fts_install.sql new file mode 100644 index 00000000..345c5934 --- /dev/null +++ b/sql_scripts/pg_fts_install.sql @@ -0,0 +1,41 @@ +ALTER TABLE question ADD COLUMN tsv tsvector; + +CREATE OR REPLACE FUNCTION public.create_plpgsql_language () + RETURNS TEXT + AS $$ + CREATE LANGUAGE plpgsql; + SELECT 'language plpgsql created'::TEXT; + $$ +LANGUAGE 'sql'; + +SELECT CASE WHEN + (SELECT true::BOOLEAN + FROM pg_language + WHERE lanname='plpgsql') + THEN + (SELECT 'language already installed'::TEXT) + ELSE + (SELECT public.create_plpgsql_language()) + END; + +DROP FUNCTION public.create_plpgsql_language (); + +CREATE OR REPLACE FUNCTION set_question_tsv() RETURNS TRIGGER AS $$ +begin + new.tsv := + setweight(to_tsvector('english', coalesce(new.tagnames,'')), 'A') || + setweight(to_tsvector('english', coalesce(new.title,'')), 'B') || + setweight(to_tsvector('english', coalesce(new.summary,'')), 'C'); + RETURN new; +end +$$ LANGUAGE plpgsql; + +CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE +ON question FOR EACH ROW EXECUTE PROCEDURE set_question_tsv(); + +CREATE INDEX blog_entry_tsv ON blog_entry USING gin(body_tsv); + +UPDATE question SET title = title; + +SELECT (ts_rank(p.factors, tsv, p.query)) AS "ranking", "question"."id", "question"."title", +"question"."author_id", "question"."added_at", "question"."wiki", "question"."wikified_at", "question"."answer_accepted", "question"."closed", "question"."closed_by_id", "question"."closed_at", "question"."close_reason", "question"."deleted", "question"."deleted_at", "question"."deleted_by_id", "question"."locked", "question"."locked_by_id", "question"."locked_at", "question"."score", "question"."vote_up_count", "question"."vote_down_count", "question"."answer_count", "question"."comment_count", "question"."view_count", "question"."offensive_flag_count", "question"."favourite_count", "question"."last_edited_at", "question"."last_edited_by_id", "question"."last_activity_at", "question"."last_activity_by_id", "question"."tagnames", "question"."summary", "question"."html" FROM "question" , "(SELECT '{0.2, 0.5, 0.75, 1.0}'::float4[] AS factors, plainto_tsquery(false) AS query) p" WHERE "question"."deleted" = E'robots' AND tsv @@ p.query ORDER BY "ranking" DESC LIMIT 21 \ No newline at end of file -- cgit v1.2.3-1-g7c22 From b319bd04d698fb02d516765938a3625259405f39 Mon Sep 17 00:00:00 2001 From: hrcerqueira Date: Tue, 2 Feb 2010 20:30:47 +0000 Subject: missing files from the last commit --- sql_scripts/pg_fts_install.sql | 80 +++++++++++++++++++++--------------------- 1 file changed, 40 insertions(+), 40 deletions(-) (limited to 'sql_scripts') diff --git a/sql_scripts/pg_fts_install.sql b/sql_scripts/pg_fts_install.sql index 345c5934..82056338 100644 --- a/sql_scripts/pg_fts_install.sql +++ b/sql_scripts/pg_fts_install.sql @@ -1,41 +1,41 @@ -ALTER TABLE question ADD COLUMN tsv tsvector; - -CREATE OR REPLACE FUNCTION public.create_plpgsql_language () - RETURNS TEXT - AS $$ - CREATE LANGUAGE plpgsql; - SELECT 'language plpgsql created'::TEXT; - $$ -LANGUAGE 'sql'; - -SELECT CASE WHEN - (SELECT true::BOOLEAN - FROM pg_language - WHERE lanname='plpgsql') - THEN - (SELECT 'language already installed'::TEXT) - ELSE - (SELECT public.create_plpgsql_language()) - END; - -DROP FUNCTION public.create_plpgsql_language (); - -CREATE OR REPLACE FUNCTION set_question_tsv() RETURNS TRIGGER AS $$ -begin - new.tsv := - setweight(to_tsvector('english', coalesce(new.tagnames,'')), 'A') || - setweight(to_tsvector('english', coalesce(new.title,'')), 'B') || - setweight(to_tsvector('english', coalesce(new.summary,'')), 'C'); - RETURN new; -end -$$ LANGUAGE plpgsql; - -CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE -ON question FOR EACH ROW EXECUTE PROCEDURE set_question_tsv(); - -CREATE INDEX blog_entry_tsv ON blog_entry USING gin(body_tsv); - -UPDATE question SET title = title; - -SELECT (ts_rank(p.factors, tsv, p.query)) AS "ranking", "question"."id", "question"."title", +ALTER TABLE question ADD COLUMN tsv tsvector; + +CREATE OR REPLACE FUNCTION public.create_plpgsql_language () + RETURNS TEXT + AS $$ + CREATE LANGUAGE plpgsql; + SELECT 'language plpgsql created'::TEXT; + $$ +LANGUAGE 'sql'; + +SELECT CASE WHEN + (SELECT true::BOOLEAN + FROM pg_language + WHERE lanname='plpgsql') + THEN + (SELECT 'language already installed'::TEXT) + ELSE + (SELECT public.create_plpgsql_language()) + END; + +DROP FUNCTION public.create_plpgsql_language (); + +CREATE OR REPLACE FUNCTION set_question_tsv() RETURNS TRIGGER AS $$ +begin + new.tsv := + setweight(to_tsvector('english', coalesce(new.tagnames,'')), 'A') || + setweight(to_tsvector('english', coalesce(new.title,'')), 'B') || + setweight(to_tsvector('english', coalesce(new.summary,'')), 'C'); + RETURN new; +end +$$ LANGUAGE plpgsql; + +CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE +ON question FOR EACH ROW EXECUTE PROCEDURE set_question_tsv(); + +CREATE INDEX blog_entry_tsv ON blog_entry USING gin(body_tsv); + +UPDATE question SET title = title; + +SELECT (ts_rank(p.factors, tsv, p.query)) AS "ranking", "question"."id", "question"."title", "question"."author_id", "question"."added_at", "question"."wiki", "question"."wikified_at", "question"."answer_accepted", "question"."closed", "question"."closed_by_id", "question"."closed_at", "question"."close_reason", "question"."deleted", "question"."deleted_at", "question"."deleted_by_id", "question"."locked", "question"."locked_by_id", "question"."locked_at", "question"."score", "question"."vote_up_count", "question"."vote_down_count", "question"."answer_count", "question"."comment_count", "question"."view_count", "question"."offensive_flag_count", "question"."favourite_count", "question"."last_edited_at", "question"."last_edited_by_id", "question"."last_activity_at", "question"."last_activity_by_id", "question"."tagnames", "question"."summary", "question"."html" FROM "question" , "(SELECT '{0.2, 0.5, 0.75, 1.0}'::float4[] AS factors, plainto_tsquery(false) AS query) p" WHERE "question"."deleted" = E'robots' AND tsv @@ p.query ORDER BY "ranking" DESC LIMIT 21 \ No newline at end of file -- cgit v1.2.3-1-g7c22 From c269397bbc0f9716fa47ddb311cdd37d3954859d Mon Sep 17 00:00:00 2001 From: hrcerqueira Date: Tue, 2 Feb 2010 20:35:41 +0000 Subject: small fix --- sql_scripts/pg_fts_install.sql | 79 ++++++++++++++++++++---------------------- 1 file changed, 38 insertions(+), 41 deletions(-) (limited to 'sql_scripts') diff --git a/sql_scripts/pg_fts_install.sql b/sql_scripts/pg_fts_install.sql index 82056338..d0655134 100644 --- a/sql_scripts/pg_fts_install.sql +++ b/sql_scripts/pg_fts_install.sql @@ -1,41 +1,38 @@ -ALTER TABLE question ADD COLUMN tsv tsvector; - -CREATE OR REPLACE FUNCTION public.create_plpgsql_language () - RETURNS TEXT - AS $$ - CREATE LANGUAGE plpgsql; - SELECT 'language plpgsql created'::TEXT; - $$ -LANGUAGE 'sql'; - -SELECT CASE WHEN - (SELECT true::BOOLEAN - FROM pg_language - WHERE lanname='plpgsql') - THEN - (SELECT 'language already installed'::TEXT) - ELSE - (SELECT public.create_plpgsql_language()) - END; - -DROP FUNCTION public.create_plpgsql_language (); - -CREATE OR REPLACE FUNCTION set_question_tsv() RETURNS TRIGGER AS $$ -begin - new.tsv := - setweight(to_tsvector('english', coalesce(new.tagnames,'')), 'A') || - setweight(to_tsvector('english', coalesce(new.title,'')), 'B') || - setweight(to_tsvector('english', coalesce(new.summary,'')), 'C'); - RETURN new; -end -$$ LANGUAGE plpgsql; - -CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE -ON question FOR EACH ROW EXECUTE PROCEDURE set_question_tsv(); - -CREATE INDEX blog_entry_tsv ON blog_entry USING gin(body_tsv); - -UPDATE question SET title = title; - -SELECT (ts_rank(p.factors, tsv, p.query)) AS "ranking", "question"."id", "question"."title", -"question"."author_id", "question"."added_at", "question"."wiki", "question"."wikified_at", "question"."answer_accepted", "question"."closed", "question"."closed_by_id", "question"."closed_at", "question"."close_reason", "question"."deleted", "question"."deleted_at", "question"."deleted_by_id", "question"."locked", "question"."locked_by_id", "question"."locked_at", "question"."score", "question"."vote_up_count", "question"."vote_down_count", "question"."answer_count", "question"."comment_count", "question"."view_count", "question"."offensive_flag_count", "question"."favourite_count", "question"."last_edited_at", "question"."last_edited_by_id", "question"."last_activity_at", "question"."last_activity_by_id", "question"."tagnames", "question"."summary", "question"."html" FROM "question" , "(SELECT '{0.2, 0.5, 0.75, 1.0}'::float4[] AS factors, plainto_tsquery(false) AS query) p" WHERE "question"."deleted" = E'robots' AND tsv @@ p.query ORDER BY "ranking" DESC LIMIT 21 \ No newline at end of file +ALTER TABLE question ADD COLUMN tsv tsvector; + +CREATE OR REPLACE FUNCTION public.create_plpgsql_language () + RETURNS TEXT + AS $$ + CREATE LANGUAGE plpgsql; + SELECT 'language plpgsql created'::TEXT; + $$ +LANGUAGE 'sql'; + +SELECT CASE WHEN + (SELECT true::BOOLEAN + FROM pg_language + WHERE lanname='plpgsql') + THEN + (SELECT 'language already installed'::TEXT) + ELSE + (SELECT public.create_plpgsql_language()) + END; + +DROP FUNCTION public.create_plpgsql_language (); + +CREATE OR REPLACE FUNCTION set_question_tsv() RETURNS TRIGGER AS $$ +begin + new.tsv := + setweight(to_tsvector('english', coalesce(new.tagnames,'')), 'A') || + setweight(to_tsvector('english', coalesce(new.title,'')), 'B') || + setweight(to_tsvector('english', coalesce(new.summary,'')), 'C'); + RETURN new; +end +$$ LANGUAGE plpgsql; + +CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE +ON question FOR EACH ROW EXECUTE PROCEDURE set_question_tsv(); + +CREATE INDEX blog_entry_tsv ON blog_entry USING gin(body_tsv); + +UPDATE question SET title = title; -- cgit v1.2.3-1-g7c22 From 955f0853655cd499a12f69ca35768802ddbbac38 Mon Sep 17 00:00:00 2001 From: Evgeny Fadeev Date: Tue, 2 Feb 2010 20:48:41 -0500 Subject: remove experimental and unused files, small cleanups --- sql_scripts/drop-all-tables.sh | 4 ++++ sql_scripts/drop-auth.sql | 8 ++++++++ 2 files changed, 12 insertions(+) create mode 100644 sql_scripts/drop-all-tables.sh create mode 100644 sql_scripts/drop-auth.sql (limited to 'sql_scripts') diff --git a/sql_scripts/drop-all-tables.sh b/sql_scripts/drop-all-tables.sh new file mode 100644 index 00000000..1e55cb1f --- /dev/null +++ b/sql_scripts/drop-all-tables.sh @@ -0,0 +1,4 @@ +mysql_username='' +mysql_database='' +mysqldump -u $mysql_username -p --add-drop-table --no-data $mysql_database | grep ^DROP +#| mysql -u[USERNAME] -p[PASSWORD] [DATABASE] diff --git a/sql_scripts/drop-auth.sql b/sql_scripts/drop-auth.sql new file mode 100644 index 00000000..bc17dce3 --- /dev/null +++ b/sql_scripts/drop-auth.sql @@ -0,0 +1,8 @@ +drop table auth_group; +drop table auth_group_permissions; +drop table auth_message; +drop table auth_permission; +drop table auth_user; +drop table auth_user_groups; +drop table auth_user_user_permissions; + -- cgit v1.2.3-1-g7c22