diff options
| -rw-r--r-- | dumpfm/dumpfm.py | 160 | ||||
| -rw-r--r-- | dumpfm/out.py | 23 | ||||
| -rw-r--r-- | dumpfm/postgres_init.sh | 13 | ||||
| -rw-r--r-- | examples/db_interact.py | 18 | ||||
| -rw-r--r-- | remote_host_test.py | 27 |
5 files changed, 236 insertions, 5 deletions
diff --git a/dumpfm/dumpfm.py b/dumpfm/dumpfm.py new file mode 100644 index 0000000..a6138b4 --- /dev/null +++ b/dumpfm/dumpfm.py @@ -0,0 +1,160 @@ +# coding: utf-8 +from sqlalchemy import Boolean, Column, DateTime, ForeignKey, Index, Integer, Table, Text, text +from sqlalchemy.orm import relationship +from sqlalchemy.dialects.postgresql.base import CIDR, INTERVAL +from sqlalchemy.ext.declarative import declarative_base + + +Base = declarative_base() +metadata = Base.metadata + + +class DirectMessage(Base): + __tablename__ = 'direct_messages' + + dm_id = Column(Integer, primary_key=True, server_default=text("nextval('direct_messages_dm_id_seq'::regclass)")) + message_id = Column(ForeignKey(u'messages.message_id'), nullable=False) + author_id = Column(ForeignKey(u'users.user_id'), nullable=False) + recip_id = Column(ForeignKey(u'users.user_id'), nullable=False) + + author = relationship(u'User', primaryjoin='DirectMessage.author_id == User.user_id') + message = relationship(u'Message') + recip = relationship(u'User', primaryjoin='DirectMessage.recip_id == User.user_id') + + +class FeedImage(Base): + __tablename__ = 'feed_images' + + feed_url = Column(Text, nullable=False) + image_url = Column(Text, primary_key=True, nullable=False, index=True) + message_id = Column(ForeignKey(u'messages.message_id')) + queued_on = Column(DateTime, nullable=False, server_default=text("now()")) + room = Column(Text, primary_key=True, nullable=False) + link = Column(Text) + title = Column(Text) + + message = relationship(u'Message') + + +class ImageAltar(Base): + __tablename__ = 'image_altars' + + message_id = Column(Integer, primary_key=True) + user_id = Column(Integer, nullable=False) + + +class ImageUrl(Base): + __tablename__ = 'image_urls' + + url = Column(Text, primary_key=True) + last_posted = Column(DateTime, nullable=False, server_default=text("now()")) + + +t_invalid_feed_images = Table( + 'invalid_feed_images', metadata, + Column('image_url', Text, nullable=False, unique=True), + Column('reason', Text, nullable=False), + Column('added_on', DateTime, nullable=False, server_default=text("now()")) +) + + +class Message(Base): + __tablename__ = 'messages' + __table_args__ = ( + Index('messages_room_id_created_on_idx', 'room_id', 'created_on'), + Index('messages_user_created_on_image_only_idx', 'user_id', 'created_on') + ) + + message_id = Column(Integer, primary_key=True, server_default=text("nextval('messages_message_id_seq'::regclass)")) + user_id = Column(ForeignKey(u'users.user_id'), nullable=False) + room_id = Column(ForeignKey(u'rooms.room_id'), nullable=False) + content = Column(Text, nullable=False) + created_on = Column(DateTime, nullable=False, server_default=text("now()")) + is_image = Column(Boolean) + is_text = Column(Boolean, nullable=False, server_default=text("false")) + + room = relationship(u'Room') + user = relationship(u'User') + + +class Mute(Base): + __tablename__ = 'mutes' + + user_id = Column(ForeignKey(u'users.user_id'), nullable=False) + admin_id = Column(ForeignKey(u'users.user_id'), nullable=False) + set_on = Column(DateTime, nullable=False, server_default=text("now()")) + duration = Column(INTERVAL, nullable=False) + reason = Column(Text, nullable=False) + cancel_admin_id = Column(ForeignKey(u'users.user_id')) + cancelled = Column(Boolean, nullable=False, server_default=text("false")) + cancel_reason = Column(Text) + mute_id = Column(Integer, primary_key=True, server_default=text("nextval('mutes_mute_id_seq'::regclass)")) + + admin = relationship(u'User', primaryjoin='Mute.admin_id == User.user_id') + cancel_admin = relationship(u'User', primaryjoin='Mute.cancel_admin_id == User.user_id') + user = relationship(u'User', primaryjoin='Mute.user_id == User.user_id') + + +class Room(Base): + __tablename__ = 'rooms' + + room_id = Column(Integer, primary_key=True, server_default=text("nextval('rooms_room_id_seq'::regclass)")) + key = Column(Text, nullable=False, unique=True) + created_on = Column(DateTime, nullable=False, server_default=text("now()")) + admin_only = Column(Boolean, nullable=False, server_default=text("false")) + name = Column(Text, nullable=False) + description = Column(Text) + active = Column(Boolean, nullable=False, server_default=text("true")) + history_size = Column(Integer, nullable=False, server_default=text("25")) + max_file_size = Column(Integer, nullable=False, server_default=text("1048576")) + max_image_width = Column(Integer, nullable=False, server_default=text("2000")) + max_image_height = Column(Integer, nullable=False, server_default=text("2000")) + + +class Tag(Base): + __tablename__ = 'tags' + __table_args__ = ( + Index('tags_user_id_created_on_idx', 'user_id', 'created_on'), + ) + + user_id = Column(ForeignKey(u'users.user_id'), primary_key=True, nullable=False, index=True) + message_id = Column(ForeignKey(u'messages.message_id'), primary_key=True, nullable=False, index=True) + created_on = Column(DateTime, nullable=False, server_default=text("now()")) + tag = Column(Text, primary_key=True, nullable=False) + + message = relationship(u'Message') + user = relationship(u'User') + + +class User(Base): + __tablename__ = 'users' + + user_id = Column(Integer, primary_key=True, server_default=text("nextval('users_user_id_seq'::regclass)")) + nick = Column(Text, nullable=False, unique=True) + hash = Column(Text, nullable=False) + email = Column(Text, nullable=False) + created_on = Column(DateTime, nullable=False, server_default=text("now()")) + avatar = Column(Text) + contact = Column(Text) + bio = Column(Text) + is_admin = Column(Boolean) + last_login = Column(DateTime) + created_ip = Column(CIDR, server_default=text("'0.0.0.0/8'::cidr")) + last_ip = Column(CIDR, server_default=text("'0.0.0.0/8'::cidr")) + +from sqlalchemy import Column, Integer, LargeBinary, String, create_engine, sql +from sqlalchemy.orm import sessionmaker +DB_USER="postgres" +DB_HOST="localhost" +DB_NAME="dumpfm" +engine = create_engine('postgresql://{}@{}/{}'.format( + DB_USER, +# DB_PASSWORD, + DB_HOST, + DB_NAME +)) +Session = sessionmaker(bind=engine) + +session = Session() +message_list = session.query(Message).filter_by(is_image=True).all() +print len(message_list) diff --git a/dumpfm/out.py b/dumpfm/out.py new file mode 100644 index 0000000..32e805e --- /dev/null +++ b/dumpfm/out.py @@ -0,0 +1,23 @@ +# -*- coding: utf-8 -*- +## File autogenerated by SQLAutoCode +## see http://code.google.com/p/sqlautocode/ + +from sqlalchemy import * +from sqlalchemy.dialects.postgresql import * + +metadata = MetaData() +messages = Table('messages', metadata,*[ + Column('message_id', INTEGER(), primary_key=True, nullable=False, default=text(u'nextval(\'"public".messages_message_id_seq\'::regclass)')), + Column('user_id', INTEGER(), primary_key=False, nullable=False), + Column('room_id', INTEGER(), primary_key=False, nullable=False), + Column('content', TEXT(), primary_key=False, nullable=False), + Column('created_on', TIMESTAMP(), primary_key=False, nullable=False, default=text(u'now()')), + Column('is_image', BOOLEAN(), primary_key=False), + Column('is_text', BOOLEAN(), primary_key=False, nullable=False, default=text(u'false')), + CheckConstraint('messages.is_image IN (:param_1, :param_2)'), + ForeignKeyConstraint(['user_id'], [u'public.users.user_id'], name=u'messages_user_id_fkey'), + ForeignKeyConstraint(['room_id'], [u'public.rooms.room_id'], name=u'messages_room_id_fkey'), + CheckConstraint('messages.is_text IN (:param_1, :param_2)'),]) +Index('messages_room_id_created_on_idx', messages.c.room_id, messages.c.created_on, unique=False) +Index('messages_user_created_on_image_only_idx', messages.c.user_id, messages.c.created_on, unique=False) + diff --git a/dumpfm/postgres_init.sh b/dumpfm/postgres_init.sh new file mode 100644 index 0000000..7bd6cc8 --- /dev/null +++ b/dumpfm/postgres_init.sh @@ -0,0 +1,13 @@ +initdb --locale en_US.UTF-8 -E UTF8 -D '/mnt/sdb1/DUMPFM_BACKUP/pg' +postgres -D /mnt/sdb1/DUMPFM_BACKUP/pg +#fix whatever issues with permissions +#createdb dumpfm +#createuser postgres +#psql ... + +# +# +# +#REVOKE CONNECT ON DATABASE dumpfm FROM PUBLIC; GRANT CONNECT ON DATABASE database_name TO postgres; +#psql -U postgres -d dumpfm < /mnt/sdb1/DUMPFM_BACKUP/dumpfmpgsql-1444088905.sql + diff --git a/examples/db_interact.py b/examples/db_interact.py index 7ab6810..d60037f 100644 --- a/examples/db_interact.py +++ b/examples/db_interact.py @@ -3,20 +3,23 @@ import sys sys.path.append('./lib') from db import Db, ImCmd import simplejson as json -import re db = Db() + + def set_tag_generate(imcmd, tag="PbGenerate"): dataobj = json.loads(imcmd.dataobj) if dataobj.get("background"): - tag = dataobj.get("compose") + tag = "%s:%s" % ("PbGenerate", dataobj.get("compose")) elif dataobj.get("transparent") != 'false': - tag = dataobj.get("subtract") + tag = "PbGenerate:transparent" imcmd.tag = tag session.add(imcmd) + def set_tag_pattern(imcmd, tag="PbPattern"): imcmd.tag = tag session.add(imcmd) + session = db.Session() @@ -35,5 +38,10 @@ for i in imcmd_list: set_tag_generate(i) session.commit() -#taglist = list(set(map(lambda n: n.tag, imcmd_list))) -#print taglist +imcmd_list = session.query(ImCmd).filter_by(tag="True").all() +for i in imcmd_list: + set_tag_generate(i) +session.commit() + +# taglist = list(set(map(lambda n: n.tag, imcmd_list))) +# print taglist diff --git a/remote_host_test.py b/remote_host_test.py new file mode 100644 index 0000000..d535db7 --- /dev/null +++ b/remote_host_test.py @@ -0,0 +1,27 @@ +from flask import request +from flask import jsonify +from flask import Flask +import re +app = Flask(__name__) + +@app.route("/") +def hello(): + return "Hello World!" + +@app.route("/testme", methods=["GET"]) +def get_my_ip(): + host = request.headers.getlist("X-Forwarded-For")[0] + regex = re.compile(r'[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+') + forwarded_ip = regex.search(host).group() + return jsonify( + { + 'ip1': request.remote_addr, + 'ip2': request.environ['REMOTE_ADDR'], + 'ip3': request.environ.get('HTTP_HOST', "nothing yet"), + 'ip4': forwarded_ip, + 'ip5': request.environ.get('HTTP_X_REAL_IP', request.remote_addr) + } + ), 200 +if __name__ == "__main__": + app.run(port=9994) + |