map_specific_topics.py

tags = {
  'accessibility': 'Accessibility',
  'activism': 'Activism',
  'agenda': 'Agenda Formation',
  'animal': 'Animal Protection & Welfare',
  'architecture': 'Architecture & Design',
  'capacity': 'Capacity Building',
  'civic_infra': 'Civic Infrastructure',
  'civic_roles': 'Civic Roles & Powers',
  'civil': 'Civil Infrastructure',
  'conflict': 'Conflict Resolution',
  'decision': 'Decision-Making',
  'democratic': 'Democratic Innovation',
  'dialogue': 'Dialogue & Deliberation',
  'digital': 'Digital/New Technologies',
  'direct': 'Direct Action',
  'educational': 'Educational Resources & Opportunities',
  'empowerment': 'Empowerment',
  'environment': 'Environment',
  'formal': 'Formal/Structured Participation',
  'gender': 'Gender',
  'global': 'Global Affairs',
  'human': 'Human Rights',
  'inclusiveness': 'Inclusiveness',
  'indigenous': 'Indigenous Issues',
  'informal': 'Informal Participation',
  'internal': 'Internal- & Self-Management',
  'initiative': 'Initiative Process',
  'mapping': 'Mapping & Analysis',
  'online': 'Online',
  'oversight': 'Oversight & Monitoring',
  'participatory': 'Participatory Budgeting',
  'planning': 'Planning & Development',
  'political': 'Political Institutions',
  'public_opinion': 'Public Opinion',
  'public_services': 'Public Services',
  'race': 'Race',
  'research': 'Research/Study',
  'rural': 'Rural',
  'science': 'Science Communication',
  'social_media': 'Social Media',
  'social_welfare': 'Social Welfare',
  'stateholder': 'Stakeholder Engagement',
  'storytelling': 'Storytelling',
  'sustainability': 'Sustainability & Green Living',
  'transparency': 'Transparency & Accountability',
  'urban': 'Urban',
  'youth': 'Youth & Student Engagement'
}

# ensure these are absolutely in the same order
tag_keys = [tag[0] for tag in sorted(tags.items())]
tag_values = [tag[1] for tag in sorted(tags.items())]

tags_used = '''1st amendment
Accessibility
Achitect
active citizens
activism
Activism
Agenda Formation
Agriculture
airesis
alpine environment
'Americans Elect' 'Colton Schweitzer' 'Tony Elia'
Animal Protection & Welfare
applied innovation
Architecture & Design
arquitectura
art
assembly
association
australia
Australia DirectDemocracy
autonomous regions
Autonomy
Backbone Campaign
big data
bilancio partecipativo
Blasphemy Laws
Bürgerbeteiligung
Bürgerhaushalt
California Citizens Redistrciting Commission Proposition 11 Voters First Act Senate Assembly Board of Equalization
Canada
canadian
capacity building
Capacity Building
capacity planning
change
changeagent
changeagents
children
CIR
Citizen Deliberation
citizen engagement
citizen panel
Citizen Participation
citizens
citizens assembly
Citizens for Democracy
Citizens Initiative Review
citizens jury
citizens'' jury
Citizen Wisdom Council
city council
Civic Council
civic engagement
civic engagement digital participation planning public
Civic Infrastructure
civic participation
Civic Roles & Powers
civic studies
civic tech
Civil Infrastructure
civil society group
climate
coalition
co-creation
co-design
collaboration
collaborative governance
Communities
community
community-academic partnership
Community and Voluntray Sector
community based organization
Community-based participatory research
community capacity building
community development
Community Development
community-engaged scholarship
community engagement
community engagements
Community Places
Community Planning
comune
comune italiano
Conflict Resolution
Consciousness-raising
consensus conference
conservatism
Constitution
coopertion
copyright
corporate social responsibility
corruption
Creative Insight council
critical reporting
Crowdmapping
Crowdsourcing
culture
david mathews center for civic live engagement deliberation alabama
decision making
Decision-Making
deeper democracy
deliberation
Deliberative
deliberative democracy
democracy
Democracy
democratic accountability
Democratic Experimentation
Democratici Diretti
Democratic Innovation
democrazia partecipativa
demopart
Development
development projects
Dialog Digital
dialogue
Dialogue & Deliberation
digital divide
digitalengagement
digital engagement
Digital/New Technologies
Direct Action
Direct Democracy
diseño Colaborativo
Diseño participativo
diseño social
dissent
Dynamic Facilitation
dynamic governance
easw
economic empowerment
Economic Justice
edem
edemocracy
e-democracy
education
Educational Resources & Opportunities
egovernment
e-government
Empowerment
energy
Engagement
Environment
environmental conflicts
environmental impact assessment studies
environmental justice
Environmental Justice
eparticipation
e-participation
Equality
Europe
european awarness scenario workshop
evolution
EZLN
Facebook application
facilitation
facilitator
federalism
film
finance
Finland
Formal/Structured Participation
foundation
freedom of expression
freedom of speech
Freerz
Freerz Community
Freerz Manifesto
gamification
Gender
Global Affairs
global warming
Government
grassroots
grassroots democracy
health
health care
http://www.studycircles.net.au/
Human Rights
ICT
idea management
IKEK
Inclusive
inclusive decision making
Inclusiveness (from DemocracySpot)
Inclussiveness
india
indigenous
Indigenous Issues
Informal Participation
Initiative Process
Inovacción ciudadana
Internal- & Self-Management
international education
internet
interplay
#IPDConf2018
ISEK
italia
italy
journalism
languages
large scale consultations
Latin America
law
lawmaking
law-making
legal affairs
legislative information
local authority
local economy
local government
local self-reliance
Love
Mapping & Analysis
maps
media
medicine
meta-theory
minorities
mission humanitaire
mission humanitaire afrique
mockery
money
Movement
MoveOn Move On
nanotechnology
net neutrality
network
NGO
non-governmental
Occupy
Occupy Wall Street
OCCUPY Wall Street
Öffentlichkeitsbeteiligung
online
Online
online deliberation
Online Democracy
online discussion
online forum
open access
open democracy
open government
Open Policy Making
open space technology
organization
Oversight & Monitoring
Pakistan
parliamentary informatics
partecipazione
participación ciudadana
participación comunitaria
participation
Participatory
participatory budget
participatory budgeting
Participatory Budgeting
Participatory democracy
particpatory democracy
Partizipation
patient-centred health care
performance monitoring
personality
pirate parties
planner
Planning & Development
platform
police accountability
policy development
Political Institutions
politics
Portugal
privacy
procedural competencies
Proces
process designer
processi decisionali inclusivi
professional organization
project Cycle management
protest
public consultation
public education
public engagement
publicengmt
Public Opinion
public participation
Public Services
Race
racism
Radical Feminism
regional authority
republicanism
research
Research/Study
responsabilità sociale
reunion
revolution
Revoution
Rural
Science Communication
science public engagement participation
SDD
service-learning
shop local
Shoreline Master Program SMP SMA Update
small business
social innovation
social justice
Social Justice
Social Media
social movement
social research
social ventures
Social Welfare
society
sociocracy
socio-economic research
software
Stadtplanung
stakeholder engagement
Stakeholder Engagement
Statewide convener
Storytelling
structured dialogic design
stub
student-led
Super PAC
sussidiarietà
sustainability
Sustainability & Green Living
sweden
Teaching Tool Student Engagement
Technology
temporary project syndicate
theory on procedures
toolbox
toscana
town council
town meeting
Transparency & Accountability
Tunisia
tuscan
tuscany
Umbrella Group
Unemployment
Union
Urban
urban politics
US politics
video
voluntary
Vote
Voters
Voting
voting rights
voyage humanitaire
white supremacist
wiki
Wisdom Council
wise democracy
WWViews
www.deborda.org
young people community participation UK
youth
youth development
youth engagement
Youth Participation
#youth #politics #civicengagement #canada
#youth #politics #civicengagement #kenya
Youth & Student Engagement
Youth & Student Engagment'''.splitlines()

delete_tags = [
  "1st amendment",
  "airesis",
  "alpine environment",
  "$_$'Americans Elect' 'Colton Schweitzer' 'Tony Elia'$_$",
  "applied innovation",
  "art",
  "assembly",
  "association",
  "autonomous regions",
  "Autonomy",
  "australia",
  "Australia DirectDemocracy",
  "Backbone Campaign",
  "big data",
  "bilancio partecipativo",
  "Blasphemy Laws",
  "Bürgerbeteiligung",
  "Bürgerhaushalt",
  "California Citizens Redistrciting Commission Proposition 11 Voters First Act Senate Assembly Board of Equalization",
  "Canada",
  "canadian",
  "change",
  "changeagent",
  "changeagents",
  "CIR",
  "Citizen Deliberation",
  "citizen engagement",
  "citizen panel",
  "Citizen Participation",
  "citizens",
  "citizens assembly",
  "Citizens for Democracy",
  "Citizens Initiative Review",
  "citizens jury",
  "citizens'' jury",
  "Citizen Wisdom Council",
  "city council",
  "Civic Council",
  "climate",
  "coalition",
  "co-creation",
  "co-design",
  "collaboration",
  "collaborative governance",
  "Communities",
  "community",
  "community-academic partnership",
  "Community and Voluntray Sector",
  "community based organization",
  "Community-based participatory research",
  "community capacity building",
  "community development",
  "Community Development",
  "community-engaged scholarship",
  "community engagement",
  "community engagements",
  "Community Places",
  "Community Planning",
  "comune",
  "comune italiano",
  "Conflict Resolution",
  "Consciousness-raising",
  "consensus conference",
  "conservatism",
  "Constitution",
  "coopertion",
  "copyright",
  "corporate social responsibility",
  "corruption",
  "Creative Insight council",
  "critical reporting",
  "Crowdmapping",
  "Crowdsourcing",
  "culture",
  "david mathews center for civic live engagement deliberation alabama",
  "decision making",
  "Decision-Making",
  "deeper democracy",
  "deliberation",
  "Deliberative",
  "Democratici Diretti",
  "democrazia partecipativa",
  "demopart",
  "Development",
  "development projects",
  "diseño Colaborativo",
  "Diseño participativo",
  "diseño social",
  "dissent",
  "Dynamic Facilitation",
  "dynamic governance",
  "easw",
  "Economic Justice",
  "edem",
  "edemocracy",
  "e-democracy",
  "egovernment",
  "e-government",
  "energy",
  "Engagement",
  "eparticipation",
  "e-participation",
  "Equality",
  "Europe",
  "european awarness scenario workshop",
  "evolution",
  "EZLN",
  "Facebook application",
  "facilitation",
  "facilitator",
  "federalism",
  "film",
  "finance",
  "Finland",
  "foundation",
  "freedom of expression",
  "freedom of speech",
  "Freerz",
  "Freerz Community",
  "Freerz Manifesto",
  "gamification",
  "global warming",
  "Government",
  "grassroots",
  "grassroots democracy",
  "health",
  "health care",
  "http://www.studycircles.net.au/",
  "ICT",
  "idea management",
  "IKEK",
  "india",
  "Initiative Process",
  "Inovacción ciudadana",
  "international education",
  "internet",
  "interplay",
  "#IPDConf2018",
  "ISEK",
  "italia",
  "italy",
  "journalism",
  "languages",
  "large scale consultations",
  "Latin America",
  "law",
  "lawmaking",
  "law-making",
  "legal affairs",
  "legislative information",
  "local authority",
  "local economy",
  "local government",
  "local self-reliance",
  "Love",
  "media",
  "medicine",
  "meta-theory",
  "minorities",
  "mission humanitaire",
  "mission humanitaire afrique",
  "mockery",
  "money",
  "Movement",
  "MoveOn Move On",
  "nanotechnology",
  "net neutrality",
  "network",
  "NGO",
  "non-governmental",
  "Occupy",
  "Occupy Wall Street",
  "OCCUPY Wall Street",
  "Öffentlichkeitsbeteiligung",
  "open access",
  "open democracy",
  "open government",
  "Open Policy Making",
  "open space technology",
  "organization",
  "Pakistan",
  "parliamentary informatics",
  "partecipazione",
  "participación ciudadana",
  "participación comunitaria",
  "participation",
  "Participatory",
  "Participatory democracy",
  "particpatory democracy",
  "Partizipation",
  "patient-centred health care",
  "performance monitoring",
  "personality",
  "pirate parties",
  "planner",
  "platform",
  "police accountability",
  "policy development",
  "politics",
  "Portugal",
  "privacy",
  "procedural competencies",
  "Proces",
  "process designer",
  "processi decisionali inclusivi",
  "professional organization",
  "project Cycle management",
  "protest",
  "public education",
  "public engagement",
  "publicengmt",
  "Public Opinion",
  "public participation",
  "Radical Feminism",
  "regional authority",
  "republicanism",
  "responsabilità sociale",
  "reunion",
  "revolution",
  "Revoution",
  "science public engagement participation",
  "SDD",
  "service-learning",
  "shop local",
  "Shoreline Master Program SMP SMA Update",
  "small business",
  "social innovation",
  "social justice",
  "Social Justice",
  "social movement",
  "social research",
  "social ventures",
  "society",
  "sociocracy",
  "socio-economic research",
  "software",
  "Stadtplanung",
  "Statewide convener",
  "structured dialogic design",
  "stub",
  "student-led",
  "Super PAC",
  "sussidiarietà",
  "sweden",
  "Teaching Tool Student Engagement",
  "Technology",
  "temporary project syndicate",
  "theory on procedures",
  "toolbox",
  "toscana",
  "town council",
  "town meeting",
  "Tunisia",
  "tuscan",
  "tuscany",
  "Umbrella Group",
  "Unemployment",
  "Union",
  "US politics",
  "video",
  "voluntary",
  "Vote",
  "Voters",
  "Voting",
  "voting rights",
  "voyage humanitaire",
  "white supremacist",
  "wiki",
  "Wisdom Council",
  "wise democracy",
  "WWViews",
  "www.deborda.org",
  "young people community participation UK",
  "youth development",
  "#youth #politics #civicengagement #canada",
  "#youth #politics #civicengagement #kenya"
]

reverse_map = {
  "Accessibility": "accessibility",
  "active citizens": "activism",
  "Activism": "activism",
  "Agenda Formation": "agenda",
  "Agriculture": "Agriculture",
  "Animal Protection & Welfare": "animal",
  "Architect": "architecture",
  "Architecture & Design": "architecture",
  "arquitectura": "architecture",
  "capacity building": "capacity",
  "Capacity Building": "capacity",
  "capacity planning": "capacity",
  "civic engagement": "civic_roles",
  "civic engagement digital participation planning public": "civic_roles",
  "Civic Infrastructure": "civic_infra",
  "civic participation": "civic_roles",
  "Civic Roles & Powers": "civic_roles",
  "civic studies": "civic_roles",
  "civic tech": "civic_infra",
  "Civil Infrastructure": "civil",
  "civil society group": "civil",
  "deliberative democracy": "democratic",
  "democracy": "democratic",
  "Democracy": "democratic",
  "democratic accountability": "democratic",
  "Democratic Experimentation": "democratic",
  "Democratic Innovation": "democratic",
  "Dialog Digital": "dialogue",
  "dialogue": "dialogue",
  "Dialogue & Deliberation": "dialogue",
  "digital divide": "digital",
  "digitalengagement": "digital",
  "digital engagement": "digital",
  "Digital/New Technologies": "digital",
  "Direct Action": "direct",
  "Direct Democracy": "direct",
  "economic empowerment": "empowerment",
  "education": "educational",
  "Educational Resources & Opportunities": "educational",
  "Empowerment": "empowerment",
  "Environment": "environment",
  "environmental conflicts": "environment",
  "environmental impact assessment studies": "environment",
  "environmental justice": "environment",
  "Environmental Justice": "environment",
  "Formal/Structured Participation": "formal",
  "Gender": "gender",
  "Global Affairs": "global",
  "Human Rights": "human",
  "Inclusive": "inclusiveness",
  "inclusive decision making": "inclusiveness",
  "Inclusiveness (from DemocracySpot)": "inclusiveness",
  "Inclussiveness": "inclusiveness",
  "indigenous": "indigenous",
  "Indigenous Issues": "indigenous",
  "Informal Participation": "informal",
  "Internal- & Self-Management": "internal",
  "Mapping & Analysis": "mapping",
  "maps": "mapping",
  "online": "online",
  "Online": "online",
  "online deliberation": "online",
  "Online Democracy": "online",
  "online discussion": "online",
  "online forum": "online",
  "Oversight & Monitoring": "oversight",
  "participatory budget": "participatory",
  "participatory budgeting": "participatory",
  "Participatory Budgeting": "participatory",
  "Planning & Development": "planning",
  "Political Institutions": "political",
  "public consultation": "public_opinion",
  "Public Services": "public_services",
  "Race": "race",
  "racism": "race",
  "research": "research",
  "Research/Study": "research",
  "Rural": "rural",
  "Science Communication": "science",
  "Social Media": "social_media",
  "Social Welfare": "social_welfare",
  "stakeholder engagement": "stakeholder",
  "Stakeholder Engagement": "stakeholder",
  "Storytelling": "storytelling",
  "sustainability": "sustainability",
  "Sustainability & Green Living": "sustainability",
  "Transparency & Accountability": "transparency",
  "Urban": "urban",
  "urban politics": "urban",
  "youth": "youth",
  "youth engagement": "youth",
  "Youth Participation": "youth",
  "Youth & Student Engagement": "youth",
  "Youth & Student Engagment": "youth",
  "children": "youth"
}

# write out the valid tag fields (keys) for tags table, in guaranteed order (alpha by key)
print("CREATE TABLE tags_localized (")
print("  language TEXT NOT NULL,")
for tag_name in tag_keys:
    print("  %s TEXT DEFAULT 'Localized %s'," % (tag_name, tag_name))
print(");")
print()

# write out the valid localized values for tags table, in guaranteed order (alpha by key)
print("INSERT INTO tags_localized VALUES(")
print("  'en',")
for tag_value in tag_values:
    print("  '%s'," % tag_value)
print(");")
print()

# write out the tag deletion rules for tags not supported at all, in things.tag
for tag in delete_tags:
    print("update things set tags = array_remove(tags, '%s');" % tag)
print()

# write out the tag mapping rules, from pre-localized to valid tag, in things.tag
for key, value in reverse_map.items():
    print("update things set tags = array_replace(tags, '%s', '%s');" % (key, value))
print()

# write function to map things.keys to localized_values {key: keyLookup: value:} objects, i.e., a specialized form of get_localized_list()
# DONE


# remove any duplicates in things.tags

# Test to make sure all tags fields return valid sets, with no duplicates