args = cli.parse_args()
args.truncate = bool(args.truncate)
limit = int(args.limit)
sourcefile = Path(args.input)
if not sourcefile.exists():
print("The file %s does not exist" % sourcefile)
exit(1)
dbconn = sqlite3.connect(args.input)
dbconn.row_factory = sqlite3.Row
cursor = dbconn.cursor()
db = Database(logger=Logger())
db.execute(open("database.sql").read())
if args.truncate:
db.execute("TRUNCATE posts_usenet")
db.execute("TRUNCATE threads_usenet")
db.execute("TRUNCATE groups_usenet")
db.commit()
post_to_threads = {}
posts = cursor.execute("SELECT * FROM postsdata")
print("Loading posts....")
done = 0
while posts:
post = posts.fetchone()
if not post or (limit and done > limit):
break
# this should have been done in the 1.9 -> 1.10 migration script, but alas...
from backend.lib.database import Database
from backend.lib.logger import Logger
import psycopg2
import config
log = Logger(output=True)
db = Database(logger=log, dbname=config.DB_NAME, user=config.DB_USER, password=config.DB_PASSWORD, host=config.DB_HOST, port=config.DB_PORT, appname="4cat-migrate")
for datasource in ("4chan", "8kun", "8chan"):
print(" Checking for %s database tables... " % datasource, end="")
test = db.fetchone("SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_schema = %s AND table_name = %s )", ("public", "posts_%s" % datasource))
if not test["exists"]:
print("not available, nothing to upgrade!")
continue
print(" Checking if required columns exist... ", end="")
columns = [row["column_name"] for row in db.fetchall("SELECT column_name FROM information_schema.columns WHERE table_name = %s", ("posts_%s" % datasource,))]
if "image_url" in columns:
print("yes!")
else:
print(" adding 'image_url' column to %s posts table" % datasource)
db.execute("ALTER TABLE posts_%s ADD COLUMN image_url TEXT DEFAULT NONE" % datasource)
from backend.lib.logger import Logger
import psycopg2
import config
log = Logger(output=True)
db = Database(logger=log,
dbname=config.DB_NAME,
user=config.DB_USER,
password=config.DB_PASSWORD,
host=config.DB_HOST,
port=config.DB_PORT,
appname="4cat-migrate")
print(" Checking for 4chan database tables... ", end="")
try:
test = db.fetchone("SELECT * FROM posts_4chan LIMIT 1")
except psycopg2.ProgrammingError:
print("not available, nothing to upgrade!")
exit(0)
print("\n Adding 'board' column to 4chan posts table")
db.execute("ALTER TABLE posts_4chan ADD COLUMN board TEXT DEFAULT ''")
print(" Filling 'board' column")
db.execute(
"UPDATE posts_4chan SET board = ( SELECT board FROM threads_4chan WHERE id = posts_4chan.thread_id )"
)
print(" Creating index")
db.execute("CREATE UNIQUE INDEX posts_4chan_id ON posts_4chan ( id, board )")
print(" Checking for %s database tables... " % datasource, end="")
test = db.fetchone(
"SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_schema = %s AND table_name = %s )",
("public", "posts_%s" % datasource))
if not test["exists"]:
print("not available, nothing to upgrade!")
continue
print(" Checking if required columns exist... ", end="")
columns = [
row["column_name"] for row in db.fetchall(
"SELECT column_name FROM information_schema.columns WHERE table_name = %s",
("posts_%s" % datasource, ))
]
if "board" in columns:
print("yes!")
else:
print(" adding 'board' column to %s posts table" % datasource)
db.execute("ALTER TABLE posts_%s ADD COLUMN board TEXT DEFAULT ''" %
datasource)
print(" Filling 'board' column (this can take a while)")
db.execute(
"UPDATE posts_%s SET board = ( SELECT board FROM threads_%s WHERE id = posts_%s.thread_id )"
% (datasource, datasource, datasource))
print(" Creating index")
db.execute(
"CREATE UNIQUE INDEX IF NOT EXISTS posts_%s_id ON posts_%s ( id, board )"
% (datasource, datasource))
else:
if thread["timestamp"] < exists["timestamp"]:
thread["is_sticky"] = exists["is_sticky"]
thread["is_closed"] = exists["is_closed"]
thread["post_last"] = max(int(thread.get("post_last") or 0),
int(exists.get("post_last") or 0))
thread["timestamp_modified"] = max(
int(thread.get("timestamp_modified") or 0),
int(exists.get("timestamp_modified") or 0))
thread["timestamp_modified"] = max(
int(thread.get("timestamp_archived") or 0),
int(exists.get("timestamp_archived") or 0))
thread["timestamp"] = min(int(thread.get("timestamp") or 0),
int(exists.get("timestamp") or 0))
db.update("threads_" + args.datasource,
data=thread,
where={"id": thread_id})
print("Updating thread statistics.")
db.execute(
"UPDATE threads_" + args.datasource +
" AS t SET num_replies = ( SELECT COUNT(*) FROM posts_" + args.datasource +
" AS p WHERE p.thread_id = t.id) WHERE t.id IN %s",
(tuple(threads.keys()), ))
db.execute(
"UPDATE threads_" + args.datasource +
" AS t SET num_images = ( SELECT COUNT(*) FROM posts_" + args.datasource +
" AS p WHERE p.thread_id = t.id AND image_file != '') WHERE t.id IN %s",
(tuple(threads.keys()), ))
print("\n Checking if required table exists... ", end="")
test = db.fetchone(
"SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_schema = %s AND table_name = %s )",
("public", "posts_%s_deleted" % datasource))
columns = [
row["column_name"] for row in db.fetchall(
"SELECT column_name FROM information_schema.columns WHERE table_name = %s",
("posts_%s" % datasource, ))
]
if test["exists"]:
print("yes!")
continue
print("no, creating table posts_%s_deleted... " % datasource, end="")
db.execute(
"CREATE TABLE IF NOT EXISTS posts_%s_deleted ( id_seq BIGINT PRIMARY KEY, timestamp_deleted BIGINT DEFAULT 0 )"
% datasource)
print("done")
print(" Filling table (this can take a while)... ", end="")
db.execute(
"INSERT INTO posts_%s_deleted ( SELECT id_seq, timestamp_deleted FROM posts_%s WHERE timestamp_deleted != 0 )"
% (datasource, datasource))
print("done")
print(" Dropping column timestamp_deleted from main table...", end="")
db.execute("ALTER TABLE posts_%s DROP COLUMN timestamp_deleted" %
datasource)
print("done!")