Create sample database export

[stefankoegl]

For getting started with contributing to gpodder.net, we need a sample database export.

• Only podcast/episode related data, no user-specific data
• Sample of the whole dataset
• Consistent data

[stefankoegl]

SQL to create tables from which to export

create table exp_directory_examplepodcast as select * from directory_examplepodcast;

create table exp_categories_category as select * from categories_category where random() < 0.01;

create table exp_podcasts_podcast as select * from podcasts_podcast where random() < 0.01;

create table exp_podcasts_podcastgroup as select * from podcasts_podcastgroup;

insert into exp_podcasts_podcast select * from podcasts_podcast where id in (select podcast_id from exp_directory_examplepodcast) ON CONFLICT DO NOTHING;

create table exp_categories_categoryentry as select * from categories_categoryentry where category_id in (select id from exp_categories_category) and podcast_id in (select id from exp_podcasts_podcast);

create table exp_categories_categorytag as select * from categories_categorytag where category_id in (select id from exp_categories_category);

create table exp_data_podcastupdateresult as select * from data_podcastupdateresult where podcast_id in (select id from exp_podcasts_podcast);

create table exp_podcasts_episode as select * from podcasts_episode where podcast_id in (select id from exp_podcasts_podcast);

create table exp_podcasts_mergeduuid as select * from podcasts_mergeduuid where object_id in (select id from exp_podcasts_podcast union select id from exp_podcasts_episode);

create table exp_podcasts_podcast_related_podcasts as select * from podcasts_podcast_related_podcasts where from_podcast_id in (select id from exp_podcasts_podcast) and to_podcast_id in (select id from exp_podcasts_podcast);

create table exp_podcasts_slug as select * from podcasts_slug where object_id in (select id from exp_podcasts_podcast union select id from exp_podcasts_episode);

create table exp_podcasts_tag as select * from podcasts_tag where object_id in (select id from exp_podcasts_podcast union select id from exp_podcasts_episode);

create table exp_podcasts_url as select * from podcasts_url where object_id in (select id from exp_podcasts_podcast union select id from exp_podcasts_episode);

Export with

sudo -u postgres pg_dump -t django_content_type --data-only --column-inserts mygpo | gzip > /tmp/mygpo-sample-export-00.sql.gz
sudo -u postgres pg_dump -t exp_categories_category --data-only --column-inserts mygpo | sed 's/exp_//g' | gzip > /tmp/mygpo-sample-export-01.sql.gz
sudo -u postgres pg_dump -t exp_podcasts_podcastgroup --data-only --column-inserts mygpo | sed 's/exp_//g' | gzip > /tmp/mygpo-sample-export-02.sql.gz
sudo -u postgres pg_dump -t exp_podcasts_podcast --data-only --column-inserts mygpo | sed 's/exp_//g' | gzip > /tmp/mygpo-sample-export-03.sql.gz
sudo -u postgres pg_dump -t exp_podcasts_podcast_related_podcasts --data-only --column-inserts mygpo | sed 's/exp_//g' | gzip > /tmp/mygpo-sample-export-04.sql.gz
sudo -u postgres pg_dump -t exp_categories_categoryentry --data-only --column-inserts mygpo | sed 's/exp_//g' | gzip > /tmp/mygpo-sample-export-05.sql.gz
sudo -u postgres pg_dump -t exp_directory_examplepodcast --data-only --column-inserts mygpo | sed 's/exp_//g' | gzip > /tmp/mygpo-sample-export-06.sql.gz
sudo -u postgres pg_dump -t exp_categories_categorytag --data-only --column-inserts mygpo | sed 's/exp_//g' | gzip > /tmp/mygpo-sample-export-07.sql.gz
sudo -u postgres pg_dump -t exp_data_podcastupdateresult --data-only --column-inserts mygpo | sed 's/exp_//g' | gzip > /tmp/mygpo-sample-export-08.sql.gz
sudo -u postgres pg_dump -t exp_podcasts_episode --data-only --column-inserts mygpo | sed 's/exp_//g' | gzip > /tmp/mygpo-sample-export-09.sql.gz
sudo -u postgres pg_dump -t exp_podcasts_mergeduuid --data-only --column-inserts mygpo | sed 's/exp_//g' | gzip > /tmp/mygpo-sample-export-10.sql.gz
sudo -u postgres pg_dump -t exp_podcasts_slug --data-only --column-inserts mygpo | sed 's/exp_//g' | gzip > /tmp/mygpo-sample-export-11.sql.gz
sudo -u postgres pg_dump -t exp_podcasts_tag --data-only --column-inserts mygpo | sed 's/exp_//g' | gzip > /tmp/mygpo-sample-export-12.sql.gz
sudo -u postgres pg_dump -t exp_podcasts_url --data-only --column-inserts mygpo | sed 's/exp_//g' | gzip > /tmp/mygpo-sample-export-13.sql.gz

[stefankoegl]

To import

• set up an empty mygpo instance (ie run all Django migrations)
• Import data

gunzip -c mygpo-sample-export.sql.gz | psql --username=mygpo -W mygpo

• Set sequences with the following SQL

  
  BEGIN;
  LOCK TABLE categories_category IN EXCLUSIVE MODE;
  SELECT setval('categories_category_id_seq', COALESCE((SELECT MAX(id)+1 FROM categories_category), 1), false);
  COMMIT;

BEGIN; LOCK TABLE categories_categorytag IN EXCLUSIVE MODE; SELECT setval('directory_examplepodcast_id_seq', COALESCE((SELECT MAX(id)+1 FROM categories_categorytag), 1), false); COMMIT;

BEGIN; LOCK TABLE directory_examplepodcast IN EXCLUSIVE MODE; SELECT setval('categories_categorytag_id_seq', COALESCE((SELECT MAX(id)+1 FROM directory_examplepodcast), 1), false); COMMIT;

BEGIN; LOCK TABLE podcasts_mergeduuid IN EXCLUSIVE MODE; SELECT setval('podcasts_mergeduuid_id_seq', COALESCE((SELECT MAX(id)+1 FROM podcasts_mergeduuid), 1), false); COMMIT;

BEGIN; LOCK TABLE podcasts_podcast_related_podcasts IN EXCLUSIVE MODE; SELECT setval('podcasts_podcast_related_podcasts_id_seq', COALESCE((SELECT MAX(id)+1 FROM podcasts_podcast_related_podcasts), 1), false); COMMIT;

BEGIN; LOCK TABLE podcasts_url IN EXCLUSIVE MODE; SELECT setval('podcasts_url_id_seq', COALESCE((SELECT MAX(id)+1 FROM podcasts_url), 1), false); COMMIT;

BEGIN; LOCK TABLE podcasts_tag IN EXCLUSIVE MODE; SELECT setval('podcasts_tag_id_seq', COALESCE((SELECT MAX(id)+1 FROM podcasts_tag), 1), false); COMMIT;

BEGIN; LOCK TABLE podcasts_slug IN EXCLUSIVE MODE; SELECT setval('podcasts_slug_id_seq', COALESCE((SELECT MAX(id)+1 FROM podcasts_slug), 1), false); COMMIT;

* Fix foreign keys of content types

update podcasts_slug set content_type_id = (select id from django_content_type where app_label = 'podcasts' and model = 'podcast') where scope = '';

update podcasts_slug set content_type_id = (select id from django_content_type where app_label = 'podcasts' and model = 'episode') where scope != '';

update podcasts_url set content_type_id = (select id from django_content_type where app_label = 'podcasts' and model = 'podcast') where scope = '';

update podcasts_url set content_type_id = (select id from django_content_type where app_label = 'podcasts' and model = 'episode') where scope != '';

update podcasts_mergeduuid set content_type_id = (select id from django_content_type where app_label = 'podcasts' and model = 'podcast') where content_type_id = 16;

update podcasts_mergeduuid set content_type_id = (select id from django_content_type where app_label = 'podcasts' and model = 'episode') where content_type_id = 15;

[SiqingYu]

Download all sample data with:

for i in $(seq -w 1 11); do wget http://koegl.io/~stefan/tmp/mygpo-sample-export-$i.sql.gz; done

[clementsjosh]

Under the Fix foreign keys of content types block, all statements that end in where scope != ''; produce the error:

ERROR: canceling statement due to statement timeout

Is this a big deal/problem?

[SiqingYu]

In my case, only one with error:

mygpo=> update podcasts_slug set content_type_id = (select id from django_content_type where app_label = 'podcasts' and model = 'episode') where scope != '';
ERROR:  canceling statement due to statement timeout

[SiqingYu]

Run the "set the sequences" and "fix the foreign keys" before importing the SQL files.

[stefankoegl]

Just for reference, to drop the temporary tables:

DROP TABLE exp_categories_category;
DROP TABLE exp_categories_categoryentry;
DROP TABLE exp_categories_categorytag;
DROP TABLE exp_data_podcastupdateresult;
DROP TABLE exp_directory_examplepodcast;
DROP TABLE exp_podcasts_episode;
DROP TABLE exp_podcasts_mergeduuid;
DROP TABLE exp_podcasts_podcast;
DROP TABLE exp_podcasts_podcast_related_podcasts;
DROP TABLE exp_podcasts_podcastgroup;
DROP TABLE exp_podcasts_slug;
DROP TABLE exp_podcasts_tag;
DROP TABLE exp_podcasts_url;