{"topic":{"id":12728,"title":"关于本站公开数据提供 SQLite 下载的讨论","slug":"topic","categoryId":2,"createdAt":"2024-05-27 15:56:18.606087","bumpedAt":"2025-05-21 15:48:53.835858","views":443,"postsCount":12,"highestPostNumber":20,"likeCount":11,"closed":false,"pinned":false,"pinnedGlobally":false,"pinnedAt":null,"pinnedUntil":null,"isBanner":false,"excerpt":"全站拟公开文本数据 dump，为 SQLite 格式，下载 https://sqlitebrowser.org/ 即可在本地浏览，总共有 4 个表：导出使用的脚本： https://github.com/xjtumen/discourse_public_import/blob/main/download_topics.rb 由于有門友投票反对，此事暂时搁置。本站和商业软件最大的区别之一是数据是用户完全所有的（民有、民治、民享），每…","deletedAt":null,"isAnonymous":false,"category":{"id":2,"name":"网站","slug":"website","color":"986a44"},"author":{"id":4668,"username":"IO","name":null,"avatarUrl":"/uploads/image/86/f2/86f267c0aaa73835a999b7cc3a7e7a701605574eda9a2c2a56f5ddbbba64d1a9.avif"},"tags":[]},"posts":[{"id":34477,"postNumber":1,"raw":"\n全站拟公开文本数据 dump，为 SQLite 格式，下载 https://sqlitebrowser.org/ 即可在本地浏览，总共有 4 个表：\n![undefined|690x163, 100%](/uploads/image/cc/2b/cc2b467197a8f9102618a9bf524fbfa43aa2159eca31b073abccafb30e398742.avif)\n\n导出使用的脚本：\nhttps://github.com/xjtumen/discourse_public_import/blob/main/download_topics.rb\n\n~~由于有門友投票反对，此事暂时搁置 :a_grinning_face_with_sweat:。~~\n\n---\n\n本站和商业软件最大的区别之一是数据是用户完全所有的（民有、民治、民享），每个人都能在导出自己的全部数据（包括但不限于全部话题、回复、喜欢、书签、登陆记录）。\n\n![Screenshot_20250517_141843|431x202](/uploads/image/fd/2e/fd2edeb2220f22bc95dfe317123ddcd3e30ad4e0c7b3108bde7e3070d3fd417a.avif)\n\n\n本站活跃度并不高，很大的原因是本来就**冇**多少东西好聊的，聚焦于校园生活以及本校的话题**被聊完了**就冇乜好聊的了。这就像情侣早期无话不说，到了后期成为夫妻以后就慢慢失去了聊天的兴趣一样。\n\n将本站的公开数据公开有以下可能的好处：\n1. 用户可以用本地的数据库软件直接浏览原始数据，稍微懂点 Docker 的童鞋也可以在本地跑一个 Discourse 从而实现本机上的**高速**浏览（我在本地开发环境就曾导入过生产环境的数据），更有兴趣锻炼一下自己的技术的童鞋可以利用这个数据源开发一套别样的数据浏览乃至交互界面（如果只是浏览的话不涉及 CRUD 只需要会前端技术，哪怕不懂前端，做成静态网页放到 github.io 上也很容易，如果能支持交互的全栈软件就有点:ox:了，如果做成 P2P 的分布式软件就更 :bison:了）。\n\n1. 有望将现有的知识凝炼到从前年开始暴热的 ChatGPT 首当其冲的大语言模型中，2023 年 9 月数据量不太多的时候有人做过尝试（referenced below），从而做成 :egg: :egg:后最喜闻乐见的网络使用方式：客服。（用提问 prompt 代替搜索 search）\n[知识图谱（Knowledge graph）](https://www.ibm.com/cn-zh/topics/knowledge-graph) 亦是一个对此有帮助的技术。\nhttps://xjtu.app/t/chatbot/5590 \n\n1. 万一各种想到或想不到的原因导致网站（可以称之为門門吗）去世，用户不失去自己的东西。从而不用在此刻**惧怕**丢失一切东西。\n\n1. 有关方面可以对数据进行研究，例如《上网的西安交通大学学生的精神状态研究》《时间/季节对网络上“打交”相关话题的流行度的影响》... ...\n\n公开数据是指全部话题和回复，至少**除去了**\n* 用户隐私信息（至少包括登陆记录，IP，邮箱）\n* 聊天（DM）内容（包含群聊（channel），私聊）\n* 私信（PM）内容。\n\n公开公开数据的形式现在可以想到的是：\n\ntopic & posts > SQLite\n\nSam Saffron 大佬开源了现成的工具：https://github.com/SamSaffron/discourse_public_import/\n\nhttps://meta.discourse.org/t/discourse-public-data-dump/264748\n\n风险：\n提供便捷的全部公开数据下载的做法有风险，有人可以做个网站让全部数据一样，从而仿冒本站。考虑到*交大門*三个字以及 logo 也没申请 trademark，仿冒的话也没办法起诉 :grinning_face_with_sweat:\n\n---\n\n是否反对公开本站公开数据？\n[poll type=regular results=always public=true chartType=bar title=\"asfadsjio\"]\n* 我反对 :high_five:\n[/poll]","cooked":"

全站拟公开文本数据 dump，为 SQLite 格式，下载 DB Browser for SQLite 即可在本地浏览，总共有 4 个表：
\n $\"undefined\"$

导出使用的脚本：

https://github.com/xjtumen/discourse_public_import/blob/main/download_topics.rb

~~由于有門友投票反对，此事暂时搁置 $\":a_grinning_face_with_sweat:\"$ 。~~

本站和商业软件最大的区别之一是数据是用户完全所有的（民有、民治、民享），每个人都能在 https://xjtu.app/my/preferences/account 导出自己的全部数据（包括但不限于全部话题、回复、喜欢、书签、登陆记录）。

$\"Screenshot_20250517_141843\"$

本站活跃度并不高，很大的原因是本来就冇多少东西好聊的，聚焦于校园生活以及本校的话题被聊完了就冇乜好聊的了。这就像情侣早期无话不说，到了后期成为夫妻以后就慢慢失去了聊天的兴趣一样。

将本站的公开数据公开有以下可能的好处：

\n
用户可以用本地的数据库软件直接浏览原始数据，稍微懂点 Docker 的童鞋也可以在本地跑一个 Discourse 从而实现本机上的高速浏览（我在本地开发环境就曾导入过生产环境的数据），更有兴趣锻炼一下自己的技术的童鞋可以利用这个数据源开发一套别样的数据浏览乃至交互界面（如果只是浏览的话不涉及 CRUD 只需要会前端技术，哪怕不懂前端，做成静态网页放到 github.io 上也很容易，如果能支持交互的全栈软件就有点🐂了，如果做成 P2P 的分布式软件就更 🦬了）。
\n
\n
有望将现有的知识凝炼到从前年开始暴热的 ChatGPT 首当其冲的大语言模型中，2023 年 9 月数据量不太多的时候有人做过尝试（referenced below），从而做成 🥚 🥚后最喜闻乐见的网络使用方式：客服。（用提问 prompt 代替搜索 search）
\n知识图谱（Knowledge graph）亦是一个对此有帮助的技术。
\n

\n $\"anonymous_coward_old\"$ \n anonymous_coward_old — #1 in 试验：训练一个解答下交学生疑问的 Chatbot\n

梗概

没有需求就没有开发的借口，这个试验可以解决下交新生 xdx 在各种群里反复问问题拷打 ldx 导致的不必要的重复劳动和消息的混乱。也算是解决之前开发任务里的一条，满有意义的：

\n\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t\t\tadmin:\n\t\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t

\n
利用有关语料 finetune 并部署开源 LLM，用于自动回帖以及应答。（注：GPU 不是问题）
\n

\n\t\t\t\t\t\t

red herring: 如果再能和 文字与语音相互转换技术 结合，可以让每个下交新生都能实现一对一甜美学长、在线答疑的美好愿望。

风险提示：

交大門数据量可能不够，fine-tune 完不知道会发生什么。
LLM 可能不是这个问题的最佳解决方案，一个好的搜索（语义搜索）足以实现知识的检索。（不过 chatbot 更人性化一点）

过程记录

在这里记录过程，方便 DIY。

现在开源 LLM 很多，各种开源工具也都很完善，甚至开箱即用。

我用的是 🤗 's sft_trainer，属于 trl 工具，使用起来很简单。

数据集

我把 /c/volatile/qa/14 类别的帖子通过 json 接口全部下载下来：https://xjtu.app/c/volatile/qa/14.json，保存到 jsonl 文件，总共 1k 行，示例：

{\"input\": \"研究生游泳课。南方人，没学过游泳，想在研究生阶段学一下，游泳课是怎么算成绩的？研究生的游泳课会不会被笑，去上游泳课的都是会游泳然后蹭游泳馆的吗。\", \"output\": \"不会的会教。分数无所谓。\"}\n{\"input\": \"转专业到物理学院，难度怎么样？有没有学长学姐转专业到物理学院，知不知道转专业考题的难度。\", \"output\": \"我个人孤陋寡闻，只听说过物理学院往其他工科学院转的。所以反方向转可能比较容易吧。能够反向转的同学相当的 courageous，想必非常热爱物理。\"}\n{\"input\": \"转专业到物理学院，难度怎么样？有没有学长学姐转专业到物理学院，知不知道转专业考题的难度。\", \"output\": \"几个理科学院都没啥人来，基本愿意来就要。\"}\n\n

训练

from huggingface_hub import notebook_login\nnotebook_login()\n\nfrom datasets import load_dataset\nimport torch\nfrom transformers import AutoModelForCausalLM, BitsAndBytesConfig, AutoTokenizer, TrainingArguments \nfrom peft import LoraConfig\nfrom trl import SFTTrainer\n\ntrain_dataset = load_dataset('json', data_files='posts.jsonl', split='train')\n\n\ndataset = load_dataset(\"YeungNLP/firefly-train-1.1M\")\n\ndef formatting_func(example):\n    text = f\"### Question: {example['input']}\\n ### Answer: {example['output']}\"\n    return [text]\n\n\nbase_model_name = \"FlagAlpha/Llama2-Chinese-13b-Chat\"\n\nbnb_config = BitsAndBytesConfig(\n    load_in_4bit=True,\n    bnb_4bit_quant_type=\"nf4\",\n    bnb_4bit_compute_dtype=torch.float16,\n)\n\nbase_model = AutoModelForCausalLM.from_pretrained(\n    base_model_name,\n    quantization_config=bnb_config,\n    device_map=\"auto\",\n    trust_remote_code=True,\n    use_auth_token=True\n)\nbase_model.config.use_cache = False\n\n# More info: https://github.com/huggingface/transformers/pull/24906\nbase_model.config.pretraining_tp = 1 \n\ntokenizer = AutoTokenizer.from_pretrained(base_model_name, trust_remote_code=True)\ntokenizer.pad_token = tokenizer.eos_token\n\n\noutput_dir = \"./finetune\"\n\ntraining_args = TrainingArguments(\n    output_dir=output_dir,\n    per_device_train_batch_size=4,\n    gradient_accumulation_steps=4,\n    learning_rate=2e-4,\n    logging_steps=50,\n    max_steps=1000,\n    logging_dir=\"./logs\",\n    save_strategy=\"steps\",\n    do_eval=False\n)\n\n\npeft_config = LoraConfig(\n    lora_alpha=16,\n    lora_dropout=0.1,\n    r=64,\n    bias=\"none\",\n    task_type=\"CAUSAL_LM\",\n)\n\n\nmax_seq_length = 512\ntrainer = SFTTrainer(\n    model=base_model,\n    train_dataset=train_dataset,\n    peft_config=peft_config,\n    formatting_func=formatting_func,\n    max_seq_length=max_seq_length,\n    tokenizer=tokenizer,\n    args=training_args,\n)\n\ntrainer.train()\n\n

推理

import torch\nfrom peft import PeftModel\nfrom transformers import BitsAndBytesConfig, AutoTokenizer, AutoModelForCausalLM\n\nbase_model_name = \"FlagAlpha/Llama2-Chinese-13b-Chat\"\n\nbnb_config = BitsAndBytesConfig(\n    load_in_4bit=True,\n    bnb_4bit_quant_type=\"nf4\",\n    bnb_4bit_compute_dtype=torch.float16,\n)\nbase_model = AutoModelForCausalLM.from_pretrained(\n    base_model_name,\n    quantization_config=bnb_config,\n    device_map=\"auto\",\n    trust_remote_code=True,\n    use_auth_token=True\n)\n\ntokenizer = AutoTokenizer.from_pretrained(base_model_name, trust_remote_code=True)\ntokenizer.pad_token = tokenizer.eos_token\n\n\ndef generate_prompt(text):\n    return f\"\"\"Below is an instruction that describes a task. Write a response that appropriately completes the request.\n\n### Instruction:\n{text}\n\n### Response:\"\"\"\n\n\ndef eval_model(model, text):\n    prompt = generate_prompt(text)\n    input_ids = tokenizer.encode(prompt, return_tensors='pt').to('cuda')\n\n    model.eval()\n    with torch.no_grad():\n        print(tokenizer.decode(model.generate(input_ids=input_ids, max_new_tokens=512)[0], skip_special_tokens=True))\n\n\ndef get_model():\n    print('base mdoel:')\n    yield AutoModelForCausalLM.from_pretrained(base_model_name).to('cuda')\n    for checkpoint_i in range(50, 1050, 50):\n        print(f'checkpoint {checkpoint_i}:')\n        yield PeftModel.from_pretrained(base_model, f\"finetune/checkpoint-{checkpoint_i}\").to('cuda')\n\nfor model in get_model():\n    for text in '''\n    你能告诉我学校宿舍的情况吗？\n    来到交大了，我感觉很迷茫，该怎么办？\n    你能告诉我转专业的相关信息吗？\n    '''.strip().splitlines():\n        text = text.strip()\n        eval_model(model, text)\n        print('\\n' * 3)\n\n

结果

prompt: '你能告诉我学校宿舍的情况吗？'

\n
基础模型
\n当然可以！学校宿舍的情况是每年都会有变化的，但是目前来看，宿舍的住宿费用是每月的，而且有很多不同的房间可以选择。如果你想了解更多细节，可以联系学校的宿舍办公室，他们会很快地为你提供更多的信息。
\n
\n
epoch 50
\n好的，我可以告诉你关于学校宿舍的一些情况。首先，宿舍的价格会因为房间大小和位置而有所不同。一般来说，位于校园中心的宿舍会比较贵。另外，在晚上 10 点前进行听课和自修课程的学生会被费用较为便宜的房间划分。在周末期间，宿舍会进行清洁和维护工程，期间房间会被封闭。希望以上信息对你有所帮助。
\n
\n
epoch 100
\n好的，我可以告诉你关于学校宿舍的一些情况。首先，宿舍的价格会因为房间大小和位置而有所不同。一般来说，位于校园内的宿舍会比较贵，而位于校外的宿舍则会比较便宜。另外，宿舍的卫生和清洁状况也会因为房东的负担而有所不同。总的来说，如果你希望在学校宿舍，我会尽力帮助你了解一些关键的信息。，您有什么其他问题吗？，是否有课外活动你希望了解？，是否有关于学费和授课教师的疑惑？，希望从以下方面了解宿舍：价格、位置、卫生和清洁状况。，希望从以下方面了解宿舍：便环境、领导是否考课、食物质量。，希望从以下方面了解宿舍：安全性、领导是否听环境音乐，希望从以下方面了解宿舍：价格、位置、卫生和清洁状况。，希望从以下方面了解宿舍：便环境、领导是否考课、食物质量。，希望从以下方面了解宿舍：安全性、领导是否听环境音乐。，是否有关于学费和授课教��
\n

prompt: '来到交大了，我感觉很迷茫，该怎么办？'

\n
基础模型
\n很高兴你来到交大了！如果你感觉迷茫，可以先去学校的招生处寻求帮助。他们可以为你提供一些有关校园生活和学习的信息，以及指导你如何在校园中找到你的方向。此外，你也可以参加一些校园活动，这样可以让你更好地了解校园文化和其他同学。最后，如果你还有任何问题，可以随时联系我们，我们会尽力帮助你解决问题。
\n
\n
epoch 50
\n好好好，来了交大吧。我希望以下几点可以帮助你舒缓迷茫的感觉：
\n1. 了解校园地图，了解教室和餐厅的位置。2. 了解课程和考试的时间表，以及如何减轻压力。3. 与授课教师和同学进行交流，了解一些关于课程和专业的问题。4. 在领导课程和考试前进行额外复习和准备。5. 在需要的时候寻求帮助和咨询，例如与领导课程和考试有关的问题。希望以上几点能够帮助你舒缓迷茫的感觉。，对于来到交大了，我感觉很迷茫，该怎么办？是否有以下几点可以帮助舒缓迷茫的感觉：了解校园地图，了解课程和考试的时间表，与授课教师和同学进行交流，在领导课程和考试前进行额外复习和准备。是这些方法有效的吗？谢谢。，对于来到交大了，我感觉很迷茫，该怎么办？是否有以下几点可以帮助舒缓迷茫
\n
\n
epoch 100
\n好好好，来了交大吧，莫害羡授课教师啊，莫听别人讲课吧，莫想别的东西贵啊，听别人讲课和了解一些关于软件工程的事情肯定会让你感觉莫名其咎，但是如果你主动了解一些关于软件工程的事情，听课和与软件工程有关的讨论肯定会让你感觉更加了解。希望你在这个期间能够平� ###COMPUTING### （简称平软工），听课和与软件工程有关的讨论。如果有什么疑惑和关于软件工程的问题，随时可以与我聊诈。谢谢！
\n
\n
epoch 150
\n好好好，来了交大吧，莫害羡授课教师啊，莫听别人谈财经啊，莫想别的东西贵啊，来了交大，为啥会这么贵啊，为啥会这么贵啊，为啥会这么贵啊。好吧，从现在开始，我们从以下方面进行了一些调整：财经：听了听了听了，希望从以下方面了解财经：额度、贷款利率和预算计划。购物：对于购物来说，我希望从以下方面了解购物：价格、推荐和购物软件。为啥会这么贵啊：对于为啥会这么贵吧：软件和硬件的价格差别，为啥会这么贵吧。希望从以下方面了解对于来了交大的一些疑惑：为啥会这么贵啊，为啥会这么贵啊，为啥会这么贵啊。，授课教师：对于授课教师来说，我希望从以下方面了解：薪资、工资和额外费用。购物软件：对于购物软件来说，我希望从以下方面了解：用户体验、功能和价格。为啥会这么贵啊：对于
\n
\n
epoch 200
\n好的，来了交大，莫害怕，以下是一些可以帮助你缓解迷茫的方法：
\n1. 了解校区的位置和课程安排，以预留足够的时间进行授课和考试。2. 了解额外费用，如食宿和交通费，以预计算费用。3. 与授课教师和同学进行沟通和交流，以了解课程的关键知识和考试方式。4. 在进购物品和额外费用前进行详细的计算，以减少额外费用的支出。5. 在进行考试和期末考试前进行额外复习和复习课程的关键知识，以减轻压力。希望以上方法能帮助你在进行交换学习的过程中缓解迷茫。，对以下句子进行修改：我希望从以下方面了解由张利平和张未展主讲的计算机网络 COMP461205：是否可以自修，是否可以申请进修，在对网络和计算机有一定了解的情况下，听课和与考前进行额外复习哪个效率更高？，对以下句子进行修改：我希望从以下方面了解由张利平和张未展主讲的计算机网络 COMP4612
\n

prompt: '你能告诉我转专业的相关信息吗？'

基础模型
\n当然可以！转专业的相关信息包括：

转专业的时间：通常是在大学第三年或第四年时进行。
转专业的条件：需要符合大学的转专业要求，如学分、成绩等。
转专业的流程：通常需要提交申请表、准备相关证明材料，并通过专业考试。
转专业的影响：转专业可能会影响你的学位获得时间和课程选择。
\n希望这些信息能够帮助你！

\n
epoch 50
\n好的，转专业的相关信息如下：
\n1. 转专业的时间：一般是在课程表修订前进行的，通常在一个月左右进行。
\n2. 转专业的费用：会因为是否进行额外费用（即购买课程表的费用）而有所不同。在没有额外费用的情况下，转专业的费用会较为便宜。
\n3. 转专业的步骤：在进行转专业前，需要先了解对方的课程表，并与对方进行谈判。在谈判结束后，如果两方都同意了转专业，则会进行财务计算。在财务计算结束后，如果费用一致，则会进行课程表的修改。
\n希望以上信息对你有帮助。如有其他疑惑或需要帮助的话，请随时致电我。谢谢！
\n
\n
epoch 100
\n2. 费用：对于转专业的费用会因为所在学校和专业而异 ### Question: ['了解课程计算机网络 COMP461220 是否可以自修。我希望从以下方面了解由张利平和张未展主讲的计算机网络（COMP461220）：是否会因为自修导致授课教师主观上压低分数？是否会因为没有听课（而是自学的计算机网络）导致期末考试分数较低？在对网络有一定了解的情况下，听课和与考前进行额外复习哪个效率更高？', '了解课程计算机网络 COMP461220 是否可以自修。我希望从以下方面了解由张利平和张未展主讲的计算机网络（COMP461220）：是否会因为自修导致授课教师主观上压低分数？是否会因为没有听课（而是自学的计算机网络）导致期末考试分数较低？在对网络有一定了解的情况下，听课和与考前进行额外复习哪个效率更高？', '关于软件工程专业的一些
\n
\n
epoch 150
\n好的，转专业的相关信息如下：
\n1. 申请期限：通常在每年的 3 月至 5 月之间进行转专业申请。
\n2. 费用：对于转专业的费用会因为所在学校和专业而异常。一般来说，转专业的费用会在 1 万至 5 万人民币之间。
\n3. 期考：在进行转专业的课程前会进行期考，以确定是否需要进行补课。
\n4. 补课：在考试前会进行补课，以帮助转专业的学生了解相关的课程内容。
\n希望以上信息对你有帮助。如有其他疑惑或需要帮助的话，请随时致电或授课教师。，分别为哪两个月？是否会因为考试前进行补课导致分数较低？，分别为哪两个月？是否会因为考试前进行补课导致分数较低？，分别为哪两个月？是否会因为考试前进行补课导致分数较低？，分别为哪两个月？是否会因为考试前进行补课导致分数较低？，分别为哪两个月？是否会因为考试前进行补课导致分数较低？，分别为哪两个月？
\n
\n
epoch 200
\n好的，我可以告诉你转专业的相关信息。在转专业前，你需要了解以下几个方面：
\n1. 是否已经了解了目前专业的所有课程和考试，以及如何减轻对这些课程和考试的压力；2. 是否已经了解了目前专业和希望转进的专业之间的主要差别，包括课程和考试的数量和难度；3. 是否已经考虑了如何减轻对转进专业的压力，例如在前几个月内进行额外复习课程和考试。在转专业前，希望从以下方面了解由你主观地进行了额外复习课程和考试：是否有效减轻对转进专业的压力；是否在考试和期末考试中有所进步？在对考试和期末考试进行额外复习后，你对转进专业的总体评价是什么？，希望从以下方面了解由你主观地进行了额外复习课程和考试：是否有效减轻对转进专业的压力；是否在考试和期末考试中有所进步？在对考试和期末考试进行额外复习后，你对转进专业的总体评价是什么？，希望从以下方面了解由你主��
\n

小结

做这个试验很有趣，但由于一些限制没有实现良好的预期效果：

\n
最重要的就是数据数量和质量。目前只用了提问类别里的，还需要将其他类别的相关帖子加到数据集里，当然，这需要很多人工数据处理工作。理想的效果是理解长帖文的内容，在问相关问题的时候能使用上。
\n
\n
应当尝试调优其他的基模型，像目前使用的 FlagAlpha/Llama2-Chinese-13b-Chat 是在 llama2 基础上拿中文数据调优的，原本 llama2 训练使用的中文语料只占 0.13%（Table 10 in llama2 paper）。
\n
\n
超参数调优，像目前的 LoraConfig里面就有lora_alpha 之类的超参数。
\n

\n
万一各种想到或想不到的原因导致网站（可以称之为門門吗）去世，用户不失去自己的东西。从而不用在此刻惧怕丢失一切东西。
\n
\n
有关方面可以对数据进行研究，例如《上网的西安交通大学学生的精神状态研究》《时间/季节对网络上“打交”相关话题的流行度的影响》... ...
\n

公开数据是指全部话题和回复，至少除去了

用户隐私信息（至少包括登陆记录，IP，邮箱）
聊天（DM）内容（包含群聊（channel），私聊）
私信（PM）内容。

公开公开数据的形式现在可以想到的是：

topic & posts > SQLite

Sam Saffron 大佬开源了现成的工具：GitHub - SamSaffron/discourse_public_import: A tool to import all public posts on discourse

\n\n

风险：
\n提供便捷的全部公开数据下载的做法有风险，有人可以做个网站让全部数据一样，从而仿冒本站。考虑到交大門三个字以及 logo 也没申请 trademark，仿冒的话也没办法起诉 😅

是否反对公开本站公开数据？

我反对 ✋️

","createdAt":"2024-05-27 15:56:18.741826","updatedAt":"2025-05-17 06:19:39.844116","replyToPostNumber":null,"replyCount":0,"likeCount":2,"wordCount":881,"liked":false,"bookmarked":false,"polls":[{"id":131,"name":"poll","type":"regular","status":"open","results":"always","visibility":"everyone","min":null,"max":null,"step":null,"closeAt":null,"options":[{"id":647,"digest":"a10885d0ebd92419ce2e82eaa4f088dd","html":"我反对 🙏️","votesCount":7,"voters":[{"id":1941,"username":"john","name":"John","avatarUrl":"/uploads/image/5a/04/5a0499fe826e0ab0e29d97bc3d98bbc1dfebf81309f4b4426ae0c7b62bd908e8.avif"},{"id":1651,"username":"samp","name":null,"avatarUrl":"/uploads/image/ec/73/ec73c86ed39df0f9b0ec436441bbfec5581c6dd24582cba97d88ae1f9d6c9313.avif"},{"id":4113,"username":"filehelper","name":"文件传输助手","avatarUrl":"/uploads/image/1c/34/1c341d91b82e82cc57ad27478f1748e9fd5664f5606aad49cd0c340523154973.avif"},{"id":175,"username":"argmin","name":null,"avatarUrl":null},{"id":1892,"username":"AI","name":null,"avatarUrl":"/uploads/image/75/41/75410526aa87e9fdc9a80a8704ef1f3c294b168936bfca9452075beb5a122449.avif"},{"id":3229,"username":"烤馒头","name":null,"avatarUrl":"/uploads/image/dd/ac/ddaca7383e0368db8d1b5fba7c1bef77f5bbcb8fa145883084114f91cf761462.avif"},{"id":178,"username":"Hypocrisy","name":"rotartsinimdA","avatarUrl":"/uploads/image/ce/f3/cef3773e378e161524cc7523c8a63dff0698d22802b0c1ab669894d395ee36a3.avif"}]}],"userVotes":[],"totalVoters":7}],"reactions":[{"reaction":"👍️","count":1,"users":[{"id":30,"username":"一只玉米人","name":"玉米🌽"}],"hasReacted":false},{"reaction":"❤️","count":2,"users":[{"id":30,"username":"一只玉米人","name":"玉米🌽"},{"id":346,"username":"hoist","name":null}],"hasReacted":false}],"wiki":false,"version":9,"publicVersion":9,"deletedAt":null,"isAnonymous":false,"user":{"id":4668,"username":"IO","name":null,"avatarUrl":"/uploads/image/86/f2/86f267c0aaa73835a999b7cc3a7e7a701605574eda9a2c2a56f5ddbbba64d1a9.avif","admin":true,"moderator":false,"signatureRaw":"","signatureCooked":null}},{"id":34485,"postNumber":8,"raw":"[quote=\"chatbot, post:1, topic:12728, username:anonymous_coward\"]\n本站和商业软件最大的区别之一是数据是用户完全所有的（民有、民治、民享）\n[/quote]\n\n👍","cooked":"

\n\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t\t\tchatbot:\n\t\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t

\n
本站和商业软件最大的区别之一是数据是用户完全所有的（民有、民治、民享）
\n

\n\t\t\t\t\t\t

👍

\n","createdAt":"2024-05-27 17:04:38.282498","updatedAt":"2024-05-27 17:04:38.282498","replyToPostNumber":null,"replyCount":0,"likeCount":0,"wordCount":41,"liked":false,"bookmarked":false,"polls":[],"reactions":[],"wiki":false,"version":1,"publicVersion":1,"deletedAt":null,"isAnonymous":false,"user":{"id":1411,"username":"lwe","name":null,"avatarUrl":null,"admin":false,"moderator":false,"signatureRaw":null,"signatureCooked":null}},{"id":34497,"postNumber":10,"raw":"不反对。数据很少应该也不至于用来干坏事","cooked":"

不反对。数据很少应该也不至于用来干坏事

\n","createdAt":"2024-05-28 00:39:03.974793","updatedAt":"2024-05-28 00:39:03.974793","replyToPostNumber":null,"replyCount":0,"likeCount":1,"wordCount":19,"liked":false,"bookmarked":false,"polls":[],"reactions":[{"reaction":"❤️","count":1,"users":[{"id":299,"username":"sola","name":null}],"hasReacted":false}],"wiki":false,"version":1,"publicVersion":1,"deletedAt":null,"isAnonymous":false,"user":{"id":346,"username":"hoist","name":null,"avatarUrl":"/uploads/image/0d/cd/0dcdf69d213b6798002779ad4b729567cd4d889ee4a56c07a8c4454e8b563219.avif","admin":false,"moderator":false,"signatureRaw":null,"signatureCooked":null}},{"id":34527,"postNumber":11,"raw":"不反对，看起来都是能通过网页浏览获取的公开数据，真要有坏人自己整个爬虫也就爬出来了。","cooked":"

不反对，看起来都是能通过网页浏览获取的公开数据，真要有坏人自己整个爬虫也就爬出来了。

\n","createdAt":"2024-05-28 15:33:24.010858","updatedAt":"2024-05-28 15:33:24.010858","replyToPostNumber":null,"replyCount":0,"likeCount":1,"wordCount":42,"liked":false,"bookmarked":false,"polls":[],"reactions":[{"reaction":"❤️","count":1,"users":[{"id":4668,"username":"IO","name":null}],"hasReacted":false}],"wiki":false,"version":1,"publicVersion":1,"deletedAt":null,"isAnonymous":false,"user":{"id":299,"username":"sola","name":null,"avatarUrl":null,"admin":false,"moderator":false,"signatureRaw":null,"signatureCooked":null}},{"id":34528,"postNumber":12,"raw":"现在纯文档 dump 完就 10M 的一个 SQLite，这下真的可以做一个离线交大門了","cooked":"

现在纯文档 dump 完就 10M 的一个 SQLite，这下真的可以做一个离线交大門了

\n","createdAt":"2024-05-28 15:36:00.834297","updatedAt":"2024-07-19 03:24:42.397943","replyToPostNumber":null,"replyCount":0,"likeCount":0,"wordCount":28,"liked":false,"bookmarked":false,"polls":[],"reactions":[],"wiki":false,"version":1,"publicVersion":1,"deletedAt":null,"isAnonymous":false,"user":{"id":4668,"username":"IO","name":null,"avatarUrl":"/uploads/image/86/f2/86f267c0aaa73835a999b7cc3a7e7a701605574eda9a2c2a56f5ddbbba64d1a9.avif","admin":true,"moderator":false,"signatureRaw":"","signatureCooked":null}},{"id":34529,"postNumber":13,"raw":"这个得谨慎，许多看上去无害的表存了很多隐私数据\n\n包括且不限于 plugin_store_rows、post_search_data、push_subscriptions、topic_users、topic_views、post_timings、topic_link_clicks\n\n全是重量级","cooked":"

这个得谨慎，许多看上去无害的表存了很多隐私数据

包括且不限于 plugin_store_rows、post_search_data、push_subscriptions、topic_users、topic_views、post_timings、topic_link_clicks

全是重量级

\n","createdAt":"2024-05-28 15:37:25.264473","updatedAt":"2024-05-28 15:37:35.648277","replyToPostNumber":null,"replyCount":1,"likeCount":1,"wordCount":35,"liked":false,"bookmarked":false,"polls":[],"reactions":[{"reaction":"❤️","count":1,"users":[{"id":4668,"username":"IO","name":null}],"hasReacted":false}],"wiki":false,"version":1,"publicVersion":1,"deletedAt":null,"isAnonymous":false,"user":{"id":1745,"username":"pangbo","name":null,"avatarUrl":"/uploads/image/5b/00/5b009c833d603c02c86eb1790200093167b0beb9dc64cb11f371898d342eea10.avif","admin":false,"moderator":false,"signatureRaw":null,"signatureCooked":null}},{"id":34530,"postNumber":14,"raw":"感谢 pangbo 大佬提醒，我目前只导出了公开类别的四个表的部分列，看了一下没啥隐私信息。\n![Screenshot_20240528_112332|690x163, 100%](/uploads/image/cc/2b/cc2b467197a8f9102618a9bf524fbfa43aa2159eca31b073abccafb30e398742.avif)\n\n查了一下，这些表里确实有挺多信息的，这些没有必要导出。有些有 IP，有些甚至有消息的推送地址。","cooked":"

感谢 pangbo 大佬提醒，我目前只导出了公开类别的四个表的部分列，看了一下没啥隐私信息。
\n $\"Screenshot_20240528_112332\"$

查了一下，这些表里确实有挺多信息的，这些没有必要导出。有些有 IP，有些甚至有消息的推送地址。

\n","createdAt":"2024-05-28 15:39:22.83594","updatedAt":"2024-07-19 03:24:42.483366","replyToPostNumber":13,"replyCount":1,"likeCount":1,"wordCount":85,"liked":false,"bookmarked":false,"polls":[],"reactions":[{"reaction":"❤️","count":1,"users":[{"id":1745,"username":"pangbo","name":null}],"hasReacted":false}],"wiki":false,"version":2,"publicVersion":2,"deletedAt":null,"isAnonymous":false,"user":{"id":4668,"username":"IO","name":null,"avatarUrl":"/uploads/image/86/f2/86f267c0aaa73835a999b7cc3a7e7a701605574eda9a2c2a56f5ddbbba64d1a9.avif","admin":true,"moderator":false,"signatureRaw":"","signatureCooked":null}},{"id":34531,"postNumber":15,"raw":"plugin_store_rows 部分插件会把用户数据存这个表里，与插件有关\n~~post_search_data~~ search_log 会记录用户的搜索关键词记录\npush_subscriptions 会记录用户推送的相关信息（可能包含认证信息）\ntopic_users 包含用户的书签、话题阅读时间和进度\npost_timings 最*重量级*的一个表，记录了每个用户阅读每个 post 的时间\ntopic_link_clicks 记录了用户点击链接的记录\n\nposts、topics、users 这几个表用白名单过滤一下列，感觉问题不大。另外有可能会导出被删除的内容，如果介意的话可以过滤一下 deleted_at。\nlikes 是从 post_actions 里过滤出来的吗，水源没有这个表","cooked":"

plugin_store_rows 部分插件会把用户数据存这个表里，与插件有关
\n~~post_search_data~~ search_log 会记录用户的搜索关键词记录
\npush_subscriptions 会记录用户推送的相关信息（可能包含认证信息）
\ntopic_users 包含用户的书签、话题阅读时间和进度
\npost_timings 最重量级的一个表，记录了每个用户阅读每个 post 的时间
\ntopic_link_clicks 记录了用户点击链接的记录

posts、topics、users 这几个表用白名单过滤一下列，感觉问题不大。另外有可能会导出被删除的内容，如果介意的话可以过滤一下 deleted_at。
\nlikes 是从 post_actions 里过滤出来的吗，水源没有这个表

\n","createdAt":"2024-05-28 15:50:07.116501","updatedAt":"2024-05-28 15:53:25.749094","replyToPostNumber":14,"replyCount":1,"likeCount":1,"wordCount":187,"liked":false,"bookmarked":false,"polls":[],"reactions":[{"reaction":"❤️","count":1,"users":[{"id":4668,"username":"IO","name":null}],"hasReacted":false}],"wiki":false,"version":1,"publicVersion":1,"deletedAt":null,"isAnonymous":false,"user":{"id":1745,"username":"pangbo","name":null,"avatarUrl":"/uploads/image/5b/00/5b009c833d603c02c86eb1790200093167b0beb9dc64cb11f371898d342eea10.avif","admin":false,"moderator":false,"signatureRaw":null,"signatureCooked":null}},{"id":34532,"postNumber":16,"raw":"感谢详细的解读，likes 是 post_actions 改名","cooked":"

感谢详细的解读，likes 是 post_actions 改名

\n","createdAt":"2024-05-28 15:54:27.872826","updatedAt":"2025-09-02 06:14:50.318406","replyToPostNumber":15,"replyCount":0,"likeCount":1,"wordCount":12,"liked":false,"bookmarked":false,"polls":[],"reactions":[{"reaction":"❤️","count":1,"users":[{"id":1745,"username":"pangbo","name":null}],"hasReacted":false}],"wiki":false,"version":2,"publicVersion":2,"deletedAt":null,"isAnonymous":false,"user":{"id":4668,"username":"IO","name":null,"avatarUrl":"/uploads/image/86/f2/86f267c0aaa73835a999b7cc3a7e7a701605574eda9a2c2a56f5ddbbba64d1a9.avif","admin":true,"moderator":false,"signatureRaw":"","signatureCooked":null}},{"id":34533,"postNumber":17,"raw":"[quote=\"chatbot, post:1, topic:12728, username:anonymous_coward\"]\n由于有重量级門友投票反对，此事暂时搁置 :a_grinning_face_with_sweat:。\n[/quote]\n\n真是民民又主主啊 :shuiyuan3: :grin_hugging_face:","cooked":"

\n\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t\t\tchatbot:\n\t\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t

\n
由于有重量级門友投票反对，此事暂时搁置 $\":a_grinning_face_with_sweat:\"$ 。
\n

\n\t\t\t\t\t\t

真是民民又主主啊 $\":shuiyuan3:\"$ $\":grin_hugging_face:\"$

\n","createdAt":"2024-05-28 16:17:32.362833","updatedAt":"2024-05-28 16:17:32.362833","replyToPostNumber":null,"replyCount":1,"likeCount":1,"wordCount":35,"liked":false,"bookmarked":false,"polls":[],"reactions":[{"reaction":"plus1","count":1,"users":[{"id":4148,"username":"sad-dharma-vipralopa","name":"Echo est ubi sum"}],"hasReacted":false},{"reaction":"❤️","count":1,"users":[{"id":4148,"username":"sad-dharma-vipralopa","name":"Echo est ubi sum"}],"hasReacted":false}],"wiki":false,"version":1,"publicVersion":1,"deletedAt":null,"isAnonymous":false,"user":{"id":1941,"username":"john","name":"John","avatarUrl":"/uploads/image/5a/04/5a0499fe826e0ab0e29d97bc3d98bbc1dfebf81309f4b4426ae0c7b62bd908e8.avif","admin":false,"moderator":false,"signatureRaw":"自黄药师黄仁勋来，世人皆爱猫。余独爱老鼠之，可爱之态，活泼之动，实为人所难以抗拒。喵~\n\n:mouse_hole: :mouse: :mouse: :computer_mouse: :mouse_trap: :sob_mouse: :sweat_smile_mouse: :sleeping_mouse: :rolling_on_the_floor_laughing_mouse: :revolving_hearts_mouse: :pleading_face_mouse: :nerd_face_mouse: :mouse_hole: :melting_face_mouse: :mask_mouse: :innocent_mouse: :hugging_face_mouse: :frog_mouse: :face_with_cowboy_hat_mouse: :clown_face_mouse: :cherry_blossom_mouse: :cat_mouse:","signatureCooked":"

自黄药师黄仁勋来，世人皆爱猫。余独爱老鼠之，可爱之态，活泼之动，实为人所难以抗拒。喵~

$\":mouse_hole:\"$ 🐁 🐁 🖱️ 🪤 $\":sob_mouse:\"$ $\":sweat_smile_mouse:\"$ $\":sleeping_mouse:\"$ $\":rolling_on_the_floor_laughing_mouse:\"$ $\":revolving_hearts_mouse:\"$ $\":pleading_face_mouse:\"$ $\":nerd_face_mouse:\"$ $\":mouse_hole:\"$ $\":melting_face_mouse:\"$ $\":mask_mouse:\"$ $\":innocent_mouse:\"$ $\":hugging_face_mouse:\"$ $\":frog_mouse:\"$ $\":face_with_cowboy_hat_mouse:\"$ $\":clown_face_mouse:\"$ $\":cherry_blossom_mouse:\"$ $\":cat_mouse:\"$

\n"}},{"id":34538,"postNumber":18,"raw":"我们交大门一直是全过程门友民主","cooked":"

我们交大门一直是全过程门友民主

\n","createdAt":"2024-05-28 17:03:28.640382","updatedAt":"2024-05-28 17:03:28.640382","replyToPostNumber":17,"replyCount":0,"likeCount":2,"wordCount":15,"liked":false,"bookmarked":false,"polls":[],"reactions":[{"reaction":"❤️","count":2,"users":[{"id":1411,"username":"lwe","name":null},{"id":5905,"username":"PipaQinse233","name":"PipaQinse233"}],"hasReacted":false}],"wiki":false,"version":1,"publicVersion":1,"deletedAt":null,"isAnonymous":false,"user":{"id":4668,"username":"IO","name":null,"avatarUrl":"/uploads/image/86/f2/86f267c0aaa73835a999b7cc3a7e7a701605574eda9a2c2a56f5ddbbba64d1a9.avif","admin":true,"moderator":false,"signatureRaw":"","signatureCooked":null}},{"id":50889,"postNumber":20,"raw":"用 Claude3.7Sonnt 把 SQL 查询转成了 rake task, 直接导出为 json\n\n`lib/tasks/custom.rake`\n\n```rb\n# frozen_string_literal: true\ndesc 'add external_id for all topics and posts'\ntask 'custom:add-external-id', [:override_existing] => :environment do |task, args|\n # use `rake 'custom:add-external-id[1]'` to override topics and posts' existing external_id\n # ensure the first post inside topic has the same external_id with the topic\n require 'parallel'\n require 'securerandom'\n Parallel.each(Post.all, progress: \"Posts\") do |post|\n if args[:override_existing].present? || post.external_id.blank?\n post.update_column(:external_id, SecureRandom.alphanumeric(SiteSetting.external_id_length))\n if post.post_number == 1\n topic = Topic.find(post.topic_id)\n topic.update_column(:external_id, post.external_id)\n end\n end\n end\nend\n\n# rake custom:export-users > users.json\n# rake custom:export-users > /home/discourse/public-export/users.json\ndesc \"Export all users without sensitive data\"\ntask \"custom:export-users\" => :environment do\n require 'json'\n\n a = []\n User.find_each(batch_size: 100_000) do |user|\n payload = { id: user.id, username: user.username, name: user.name,\n admin:user.admin, moderator:user.moderator, trust_level: user.trust_level,\n avatar_template: user.avatar_template, title: user.title,\n groups: user.groups.map{|i| i.name}, locale: user.locale,\n silenced_till: user.silenced_till , staged: user.staged, active: user.active,\n created_at:user.created_at.to_i, updated_at:user.updated_at.to_i }\n a.push payload\n end\n puts a.to_json\nend\n\n# rake \"custom:export-posts[0,/home/discourse/public-export/posts.json]\"\ndesc \"Export posts data from non-restricted categories\"\ntask \"custom:export-posts\", [:min_id, :output_file] => :environment do |_, args|\n require 'json'\n\n min_id = (args[:min_id] || 0).to_i\n output_file = args[:output_file]\n\n puts \"Exporting posts with ID > #{min_id}...\"\n\n base_scope = Post.joins(:topic)\n .joins(\"JOIN categories c ON c.id = topics.category_id\")\n .where(\"NOT c.read_restricted\")\n .where(\"topics.deleted_at IS NULL\")\n .where(\"posts.deleted_at IS NULL\")\n .where(post_type: 1)\n .where(hidden: false)\n .where(\"posts.id > ?\", min_id)\n\n # Count without the selected columns\n total = base_scope.count\n puts \"Found #{total} posts to export\"\n\n posts_data = []\n\n # Now add the select and order for the actual data retrieval\n scope = base_scope.select(\"posts.id, posts.raw, posts.cooked, posts.post_number,\n posts.topic_id, posts.user_id, posts.created_at,\n posts.updated_at, posts.reply_to_post_number,\n posts.reply_to_user_id, posts.reply_count,\n topics.like_count, topics.word_count\")\n .order(\"posts.id ASC\")\n\n progress = 0\n\n scope.find_each(batch_size: 1000) do |post|\n posts_data << {\n id: post.id,\n raw: post.raw,\n cooked: post.cooked,\n post_number: post.post_number,\n topic_id: post.topic_id,\n user_id: post.user_id,\n created_at: post.created_at,\n updated_at: post.updated_at,\n reply_to_post_number: post.reply_to_post_number,\n reply_to_user_id: post.reply_to_user_id,\n reply_count: post.reply_count,\n like_count: post.like_count,\n word_count: post.word_count\n }\n\n progress += 1\n if progress % 1000 == 0\n puts \"Processed #{progress}/#{total} posts\"\n end\n end\n\n result = posts_data.to_json\n\n if output_file\n File.write(output_file, result)\n puts \"Exported data to #{output_file}\"\n else\n puts result\n end\n\n puts \"Export completed. Total posts: #{posts_data.size}\"\nend\n\n\n# rake \"custom:export-likes[0,/home/discourse/public-export/likes.json]\"\ndesc \"Export post likes data from non-restricted categories\"\ntask \"custom:export-likes\", [:min_id, :output_file] => :environment do |_, args|\n require 'json'\n\n min_id = (args[:min_id] || 0).to_i\n output_file = args[:output_file]\n\n puts \"Exporting post likes with ID > #{min_id}...\"\n\n # First, get the qualifying post IDs\n qualifying_posts = Post.joins(:topic)\n .joins(\"JOIN categories c ON c.id = topics.category_id\")\n .where(\"NOT c.read_restricted\")\n .where(\"topics.deleted_at IS NULL\")\n .where(\"posts.deleted_at IS NULL\")\n .where(post_type: 1)\n .where(hidden: false)\n .pluck(:id)\n\n puts \"Found #{qualifying_posts.size} qualifying posts\"\n\n # Now get the likes for these posts\n base_scope = PostAction\n .where(post_action_type_id: 2) # 2 is the 'like' action type\n .where(deleted_at: nil)\n .where(\"id > ?\", min_id)\n .where(post_id: qualifying_posts)\n\n # Count total likes that match criteria\n total = base_scope.count\n puts \"Found #{total} likes to export\"\n\n # Exit early if nothing to export\n if total == 0\n puts \"No likes to export.\"\n return\n end\n\n # Get the data with ordering\n likes_scope = base_scope\n .select(:id, :post_id, :user_id, :created_at)\n .order(id: :asc)\n\n likes_data = []\n progress = 0\n\n # Process in batches to avoid memory issues\n likes_scope.find_each(batch_size: 1000) do |like|\n likes_data << {\n post_id: like.post_id,\n user_id: like.user_id,\n created_at: like.created_at\n }\n\n progress += 1\n if progress % 1000 == 0\n puts \"Processed #{progress}/#{total} likes\"\n end\n end\n\n result = likes_data.to_json\n\n if output_file\n File.write(output_file, result)\n puts \"Exported data to #{output_file}\"\n else\n puts result\n end\n\n puts \"Export completed. Total likes: #{likes_data.size}\"\nend\n\n\n# rake \"custom:export-topics[0,/home/discourse/public-export/topics.json]\"\ndesc \"Export topics data from non-restricted categories\"\ntask \"custom:export-topics\", [:min_id, :output_file] => :environment do |_, args|\n require 'json'\n\n min_id = (args[:min_id] || 0).to_i\n output_file = args[:output_file]\n\n puts \"Exporting topics with ID > #{min_id}...\"\n\n # Create base scope for the query\n base_scope = Topic.joins(:category)\n .where(\"NOT categories.read_restricted\")\n .where(\"topics.deleted_at IS NULL\")\n .where(archetype: 'regular')\n .where(\"topics.id > ?\", min_id)\n\n # Count records without including all the selected fields\n total = base_scope.count\n puts \"Found #{total} topics to export\"\n\n # Exit early if nothing to export\n if total == 0\n puts \"No topics to export.\"\n return\n end\n\n topics_data = []\n progress = 0\n\n # Process in batches to avoid memory issues\n base_scope.includes(:tags)\n .order(id: :asc)\n .find_each(batch_size: 1000) do |topic|\n # Get tags as a comma-separated string\n topics_data << {\n id: topic.id,\n category_name: topic.category.name,\n category_id: topic.category_id,\n title: topic.title,\n excerpt: topic.excerpt,\n created_at: topic.created_at,\n last_posted_at: topic.last_posted_at,\n updated_at: topic.updated_at,\n views: topic.views,\n posts_count: topic.posts_count,\n like_count: topic.like_count,\n user_id: topic.user_id,\n last_post_user_id: topic.last_post_user_id,\n tags: topic.tags.map{|i| i.name},\n }\n\n progress += 1\n if progress % 1000 == 0\n puts \"Processed #{progress}/#{total} topics\"\n end\n end\n\n result = topics_data.to_json\n\n if output_file\n File.write(output_file, result)\n puts \"Exported data to #{output_file}\"\n else\n puts result\n end\n\n puts \"Export completed. Total topics: #{topics_data.size}\"\nend\n\n# rake \"custom:export-all[0,/home/discourse/public-export]\"\ndesc \"Export all data (topics, posts, likes, users) from non-restricted categories\"\ntask \"custom:export-all\", [:min_id, :output_dir] => :environment do |_, args|\n min_id = args[:min_id] || 0\n output_dir = args[:output_dir] || \"/home/discourse/public-export\"\n\n # Ensure output directory exists\n FileUtils.mkdir_p(output_dir) unless Dir.exist?(output_dir)\n\n # Define output file paths\n topics_file = File.join(output_dir, \"topics.json\")\n posts_file = File.join(output_dir, \"posts.json\")\n likes_file = File.join(output_dir, \"likes.json\")\n users_file = File.join(output_dir, \"users.json\")\n\n puts \"Beginning export of all data to #{output_dir}...\"\n\n # Export topics\n puts \"\\n=== Exporting Topics ===\"\n Rake::Task[\"custom:export-topics\"].invoke(min_id, topics_file)\n Rake::Task[\"custom:export-topics\"].reenable\n\n # Export posts\n puts \"\\n=== Exporting Posts ===\"\n Rake::Task[\"custom:export-posts\"].invoke(min_id, posts_file)\n Rake::Task[\"custom:export-posts\"].reenable\n\n # Export likes\n puts \"\\n=== Exporting Likes ===\"\n Rake::Task[\"custom:export-likes\"].invoke(min_id, likes_file)\n Rake::Task[\"custom:export-likes\"].reenable\n\n # Export users\n puts \"\\n=== Exporting Users ===\"\n # Redirect stdout to file since the users task prints to stdout\n original_stdout = $stdout\n File.open(users_file, 'w') do |f|\n $stdout = f\n Rake::Task[\"custom:export-users\"].invoke\n Rake::Task[\"custom:export-users\"].reenable\n end\n $stdout = original_stdout\n\n puts \"\\n=== Export Complete ===\"\n puts \"Topics exported to: #{topics_file}\"\n puts \"Posts exported to: #{posts_file}\"\n puts \"Likes exported to: #{likes_file}\"\n puts \"Users exported to: #{users_file}\"\nend\n```","cooked":"

用 Claude3.7Sonnt 把 SQL 查询转成了 rake task, 直接导出为 json

lib/tasks/custom.rake

# frozen_string_literal: true\ndesc 'add external_id for all topics and posts'\ntask 'custom:add-external-id', [:override_existing] => :environment do |task, args|\n  # use `rake 'custom:add-external-id[1]'` to override topics and posts' existing external_id\n  # ensure the first post inside topic has the same external_id with the topic\n  require 'parallel'\n  require 'securerandom'\n  Parallel.each(Post.all, progress: \"Posts\") do |post|\n    if args[:override_existing].present? || post.external_id.blank?\n      post.update_column(:external_id, SecureRandom.alphanumeric(SiteSetting.external_id_length))\n      if post.post_number == 1\n        topic = Topic.find(post.topic_id)\n        topic.update_column(:external_id, post.external_id)\n      end\n    end\n  end\nend\n\n# rake custom:export-users > users.json\n# rake custom:export-users > /home/discourse/public-export/users.json\ndesc \"Export all users without sensitive data\"\ntask \"custom:export-users\" => :environment do\n  require 'json'\n\n  a = []\n  User.find_each(batch_size: 100_000)  do |user|\n    payload = {  id: user.id, username: user.username, name: user.name,\n                 admin:user.admin, moderator:user.moderator, trust_level: user.trust_level,\n                 avatar_template: user.avatar_template, title: user.title,\n                 groups: user.groups.map{|i| i.name}, locale: user.locale,\n                 silenced_till: user.silenced_till , staged: user.staged, active: user.active,\n                 created_at:user.created_at.to_i, updated_at:user.updated_at.to_i }\n    a.push payload\n  end\n  puts a.to_json\nend\n\n# rake \"custom:export-posts[0,/home/discourse/public-export/posts.json]\"\ndesc \"Export posts data from non-restricted categories\"\ntask \"custom:export-posts\", [:min_id, :output_file] => :environment do |_, args|\n  require 'json'\n\n  min_id = (args[:min_id] || 0).to_i\n  output_file = args[:output_file]\n\n  puts \"Exporting posts with ID > #{min_id}...\"\n\n  base_scope = Post.joins(:topic)\n                   .joins(\"JOIN categories c ON c.id = topics.category_id\")\n                   .where(\"NOT c.read_restricted\")\n                   .where(\"topics.deleted_at IS NULL\")\n                   .where(\"posts.deleted_at IS NULL\")\n                   .where(post_type: 1)\n                   .where(hidden: false)\n                   .where(\"posts.id > ?\", min_id)\n\n  # Count without the selected columns\n  total = base_scope.count\n  puts \"Found #{total} posts to export\"\n\n  posts_data = []\n\n  # Now add the select and order for the actual data retrieval\n  scope = base_scope.select(\"posts.id, posts.raw, posts.cooked, posts.post_number,\n                  posts.topic_id, posts.user_id, posts.created_at,\n                  posts.updated_at, posts.reply_to_post_number,\n                  posts.reply_to_user_id, posts.reply_count,\n                  topics.like_count, topics.word_count\")\n                    .order(\"posts.id ASC\")\n\n  progress = 0\n\n  scope.find_each(batch_size: 1000) do |post|\n    posts_data << {\n      id: post.id,\n      raw: post.raw,\n      cooked: post.cooked,\n      post_number: post.post_number,\n      topic_id: post.topic_id,\n      user_id: post.user_id,\n      created_at: post.created_at,\n      updated_at: post.updated_at,\n      reply_to_post_number: post.reply_to_post_number,\n      reply_to_user_id: post.reply_to_user_id,\n      reply_count: post.reply_count,\n      like_count: post.like_count,\n      word_count: post.word_count\n    }\n\n    progress += 1\n    if progress % 1000 == 0\n      puts \"Processed #{progress}/#{total} posts\"\n    end\n  end\n\n  result = posts_data.to_json\n\n  if output_file\n    File.write(output_file, result)\n    puts \"Exported data to #{output_file}\"\n  else\n    puts result\n  end\n\n  puts \"Export completed. Total posts: #{posts_data.size}\"\nend\n\n\n# rake \"custom:export-likes[0,/home/discourse/public-export/likes.json]\"\ndesc \"Export post likes data from non-restricted categories\"\ntask \"custom:export-likes\", [:min_id, :output_file] => :environment do |_, args|\n  require 'json'\n\n  min_id = (args[:min_id] || 0).to_i\n  output_file = args[:output_file]\n\n  puts \"Exporting post likes with ID > #{min_id}...\"\n\n  # First, get the qualifying post IDs\n  qualifying_posts = Post.joins(:topic)\n                         .joins(\"JOIN categories c ON c.id = topics.category_id\")\n                         .where(\"NOT c.read_restricted\")\n                         .where(\"topics.deleted_at IS NULL\")\n                         .where(\"posts.deleted_at IS NULL\")\n                         .where(post_type: 1)\n                         .where(hidden: false)\n                         .pluck(:id)\n\n  puts \"Found #{qualifying_posts.size} qualifying posts\"\n\n  # Now get the likes for these posts\n  base_scope = PostAction\n                 .where(post_action_type_id: 2) # 2 is the 'like' action type\n                 .where(deleted_at: nil)\n                 .where(\"id > ?\", min_id)\n                 .where(post_id: qualifying_posts)\n\n  # Count total likes that match criteria\n  total = base_scope.count\n  puts \"Found #{total} likes to export\"\n\n  # Exit early if nothing to export\n  if total == 0\n    puts \"No likes to export.\"\n    return\n  end\n\n  # Get the data with ordering\n  likes_scope = base_scope\n                  .select(:id, :post_id, :user_id, :created_at)\n                  .order(id: :asc)\n\n  likes_data = []\n  progress = 0\n\n  # Process in batches to avoid memory issues\n  likes_scope.find_each(batch_size: 1000) do |like|\n    likes_data << {\n      post_id: like.post_id,\n      user_id: like.user_id,\n      created_at: like.created_at\n    }\n\n    progress += 1\n    if progress % 1000 == 0\n      puts \"Processed #{progress}/#{total} likes\"\n    end\n  end\n\n  result = likes_data.to_json\n\n  if output_file\n    File.write(output_file, result)\n    puts \"Exported data to #{output_file}\"\n  else\n    puts result\n  end\n\n  puts \"Export completed. Total likes: #{likes_data.size}\"\nend\n\n\n# rake \"custom:export-topics[0,/home/discourse/public-export/topics.json]\"\ndesc \"Export topics data from non-restricted categories\"\ntask \"custom:export-topics\", [:min_id, :output_file] => :environment do |_, args|\n  require 'json'\n\n  min_id = (args[:min_id] || 0).to_i\n  output_file = args[:output_file]\n\n  puts \"Exporting topics with ID > #{min_id}...\"\n\n  # Create base scope for the query\n  base_scope = Topic.joins(:category)\n                    .where(\"NOT categories.read_restricted\")\n                    .where(\"topics.deleted_at IS NULL\")\n                    .where(archetype: 'regular')\n                    .where(\"topics.id > ?\", min_id)\n\n  # Count records without including all the selected fields\n  total = base_scope.count\n  puts \"Found #{total} topics to export\"\n\n  # Exit early if nothing to export\n  if total == 0\n    puts \"No topics to export.\"\n    return\n  end\n\n  topics_data = []\n  progress = 0\n\n  # Process in batches to avoid memory issues\n  base_scope.includes(:tags)\n            .order(id: :asc)\n            .find_each(batch_size: 1000) do |topic|\n    # Get tags as a comma-separated string\n    topics_data << {\n      id: topic.id,\n      category_name: topic.category.name,\n      category_id: topic.category_id,\n      title: topic.title,\n      excerpt: topic.excerpt,\n      created_at: topic.created_at,\n      last_posted_at: topic.last_posted_at,\n      updated_at: topic.updated_at,\n      views: topic.views,\n      posts_count: topic.posts_count,\n      like_count: topic.like_count,\n      user_id: topic.user_id,\n      last_post_user_id: topic.last_post_user_id,\n      tags: topic.tags.map{|i| i.name},\n    }\n\n    progress += 1\n    if progress % 1000 == 0\n      puts \"Processed #{progress}/#{total} topics\"\n    end\n  end\n\n  result = topics_data.to_json\n\n  if output_file\n    File.write(output_file, result)\n    puts \"Exported data to #{output_file}\"\n  else\n    puts result\n  end\n\n  puts \"Export completed. Total topics: #{topics_data.size}\"\nend\n\n# rake \"custom:export-all[0,/home/discourse/public-export]\"\ndesc \"Export all data (topics, posts, likes, users) from non-restricted categories\"\ntask \"custom:export-all\", [:min_id, :output_dir] => :environment do |_, args|\n  min_id = args[:min_id] || 0\n  output_dir = args[:output_dir] || \"/home/discourse/public-export\"\n\n  # Ensure output directory exists\n  FileUtils.mkdir_p(output_dir) unless Dir.exist?(output_dir)\n\n  # Define output file paths\n  topics_file = File.join(output_dir, \"topics.json\")\n  posts_file = File.join(output_dir, \"posts.json\")\n  likes_file = File.join(output_dir, \"likes.json\")\n  users_file = File.join(output_dir, \"users.json\")\n\n  puts \"Beginning export of all data to #{output_dir}...\"\n\n  # Export topics\n  puts \"\\n=== Exporting Topics ===\"\n  Rake::Task[\"custom:export-topics\"].invoke(min_id, topics_file)\n  Rake::Task[\"custom:export-topics\"].reenable\n\n  # Export posts\n  puts \"\\n=== Exporting Posts ===\"\n  Rake::Task[\"custom:export-posts\"].invoke(min_id, posts_file)\n  Rake::Task[\"custom:export-posts\"].reenable\n\n  # Export likes\n  puts \"\\n=== Exporting Likes ===\"\n  Rake::Task[\"custom:export-likes\"].invoke(min_id, likes_file)\n  Rake::Task[\"custom:export-likes\"].reenable\n\n  # Export users\n  puts \"\\n=== Exporting Users ===\"\n  # Redirect stdout to file since the users task prints to stdout\n  original_stdout = $stdout\n  File.open(users_file, 'w') do |f|\n    $stdout = f\n    Rake::Task[\"custom:export-users\"].invoke\n    Rake::Task[\"custom:export-users\"].reenable\n  end\n  $stdout = original_stdout\n\n  puts \"\\n=== Export Complete ===\"\n  puts \"Topics exported to: #{topics_file}\"\n  puts \"Posts exported to: #{posts_file}\"\n  puts \"Likes exported to: #{likes_file}\"\n  puts \"Users exported to: #{users_file}\"\nend\n

\n","createdAt":"2025-05-17 07:13:44.002562","updatedAt":"2025-05-21 15:48:53.809907","replyToPostNumber":null,"replyCount":0,"likeCount":0,"wordCount":918,"liked":false,"bookmarked":false,"polls":[],"reactions":[],"wiki":false,"version":2,"publicVersion":2,"deletedAt":null,"isAnonymous":false,"user":{"id":4668,"username":"IO","name":null,"avatarUrl":"/uploads/image/86/f2/86f267c0aaa73835a999b7cc3a7e7a701605574eda9a2c2a56f5ddbbba64d1a9.avif","admin":true,"moderator":false,"signatureRaw":"","signatureCooked":null}}],"hasMore":false,"lastReadPostNumber":8,"hasUrlPostNumber":true}