File size: 6,474 Bytes
c481f8a | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 | import sqlite3
import os
import sys
# Add the orchestrator directory to sys.path so we can import config
sys.path.append(os.path.dirname(os.path.abspath(__file__)))
from config import DB_PATH
def init_db():
# Ensure directory exists
os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
# 1. keyword
cursor.execute('''
CREATE TABLE IF NOT EXISTS keyword (
id INTEGER PRIMARY KEY AUTOINCREMENT,
word TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
# 2. competitor_account
cursor.execute('''
CREATE TABLE IF NOT EXISTS competitor_account (
id INTEGER PRIMARY KEY AUTOINCREMENT,
account_name TEXT NOT NULL,
platform TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
# 3. own_account
cursor.execute('''
CREATE TABLE IF NOT EXISTS own_account (
id INTEGER PRIMARY KEY AUTOINCREMENT,
account_name TEXT NOT NULL,
platform TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
# 4. crawl_task
cursor.execute('''
CREATE TABLE IF NOT EXISTS crawl_task (
id INTEGER PRIMARY KEY AUTOINCREMENT,
target_type TEXT,
target_value TEXT,
status TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
# 5. raw_note
cursor.execute('''
CREATE TABLE IF NOT EXISTS raw_note (
id INTEGER PRIMARY KEY AUTOINCREMENT,
source_platform TEXT,
content TEXT,
author TEXT,
url TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
# 6. cleaned_note
cursor.execute('''
CREATE TABLE IF NOT EXISTS cleaned_note (
id INTEGER PRIMARY KEY AUTOINCREMENT,
raw_note_id INTEGER,
cleaned_content TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (raw_note_id) REFERENCES raw_note(id)
)
''')
# 7. content_material
cursor.execute('''
CREATE TABLE IF NOT EXISTS content_material (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT,
body TEXT,
type TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
# 8. prompt_template
cursor.execute('''
CREATE TABLE IF NOT EXISTS prompt_template (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
template_content TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
# Insert default compliance prompt if table is empty
cursor.execute("SELECT count(*) FROM prompt_template")
if cursor.fetchone()[0] == 0:
system_prompt = """Rewrite the following material to be engaging for Xiaohongshu.
IMPORTANT COMPLIANCE GUIDELINES (小红书笔记十不要原则):
1. 严禁低俗擦边（如性暗示、暴露）和引人不适（如血腥、恐怖）的内容。
2. 严禁模板化洗稿，追求原创真实，不高度雷同。
3. 禁止利用特殊身份（如医生、专家）进行无授权的营销背书。
4. 禁止虚假价格、虚构原价或虚假承诺（如发货时效不符）。
5. 禁止过度修图、滤镜失真、P图夸大商品效果，避免货不对板。
6. 禁止向微信、淘宝、抖音、B站、微博等第三方平台导流，禁止留联系方式。
7. 禁止付费互动，禁止通过送福利等方式诱导用户点赞、关注或抽奖。
8. 严禁炫富内容（如晒账单、鼓吹财富自由等超出常人消费能力的行为）。
9. 禁止网络暴力、挂人或挑起对立。
10. 禁止使用极限用语（如国家级、最高级、第一等）、封建迷信用语及虚假医疗功效用语。
Please ensure the rewritten content strictly follows the above guidelines.
Material to rewrite:"""
cursor.execute("INSERT INTO prompt_template (name, template_content) VALUES (?, ?)", ("Compliance Rewrite", system_prompt))
# 9. generated_post
cursor.execute('''
CREATE TABLE IF NOT EXISTS generated_post (
id INTEGER PRIMARY KEY AUTOINCREMENT,
material_id INTEGER,
prompt_id INTEGER,
content TEXT,
status TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (material_id) REFERENCES content_material(id),
FOREIGN KEY (prompt_id) REFERENCES prompt_template(id)
)
''')
# 10. publish_record
cursor.execute('''
CREATE TABLE IF NOT EXISTS publish_record (
id INTEGER PRIMARY KEY AUTOINCREMENT,
post_id INTEGER,
account_id INTEGER,
platform TEXT,
status TEXT,
publish_url TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (post_id) REFERENCES generated_post(id),
FOREIGN KEY (account_id) REFERENCES own_account(id)
)
''')
# 11. interaction_record
cursor.execute('''
CREATE TABLE IF NOT EXISTS interaction_record (
id INTEGER PRIMARY KEY AUTOINCREMENT,
publish_record_id INTEGER,
user_name TEXT,
interaction_type TEXT,
content TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (publish_record_id) REFERENCES publish_record(id)
)
''')
# 12. lead
cursor.execute('''
CREATE TABLE IF NOT EXISTS lead (
id INTEGER PRIMARY KEY AUTOINCREMENT,
interaction_id INTEGER,
contact_info TEXT,
status TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (interaction_id) REFERENCES interaction_record(id)
)
''')
# 13. bd_followup
cursor.execute('''
CREATE TABLE IF NOT EXISTS bd_followup (
id INTEGER PRIMARY KEY AUTOINCREMENT,
lead_id INTEGER,
followup_content TEXT,
followup_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (lead_id) REFERENCES lead(id)
)
''')
# 14. system_error_log
cursor.execute('''
CREATE TABLE IF NOT EXISTS system_error_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
module_name TEXT,
error_message TEXT,
stack_trace TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
conn.commit()
conn.close()
print(f"Database initialized successfully with 14 tables at {DB_PATH}")
if __name__ == "__main__":
init_db()
|