def ensure_db_schema(cls, db: dataset.Database) -> None:
"""
Ensures the db schema exist, creating it if it doesn't.
:param db: the opened `dataset.Database` object
"""
db.query('PRAGMA journal_mode = WAL').close()
device_type: str
log_record_type: Type[LogRecord]
for device_type, log_record_type in LogRecord.get_known_types_classes(
):
table_columns: List[Column] = [Column('device_id', UnicodeText, primary_key=True)] \
+ log_record_type.fields_to_db_columns()
device_type_table: dataset.Table = db.create_table(
device_type, primary_id=False)
device_type_table._sync_table(table_columns)
meta_table: dataset.Table = db.create_table('_meta', primary_id=False)
meta_table._sync_table([
Column('device_type',
UnicodeText,
nullable=False,
primary_key=True),
Column('device_id', UnicodeText, nullable=False, primary_key=True),
Column('log_position', Integer, nullable=False),
Column('log_mtime', Float, nullable=False),
])
def setup(self, db: dataset.Database):
system_table = db.get_table("system")
if None == system_table.find_one(key = "user_00"):
user_table:dataset.Table = db.create_table("users")
user_table.create_column("name",sqlalchemy.VARCHAR(256))
user_table.create_column("email",sqlalchemy.VARCHAR(256))
user_table.create_column("password",sqlalchemy.VARCHAR(256))
#user_table.insert({
# "name":"kyorohiro",
# "email":"*****@*****.**",
# "password": encrypt("password (0_0)")})
system_table.insert({
"key":"user_00",
"value":True})
if None == system_table.find_one(key = "user_01"):
user_table:dataset.Table = db.create_table("users")
user_table.create_column("aws_access_key_id", sqlalchemy.VARCHAR(512))
user_table.create_column("aws_secret_key", sqlalchemy.VARCHAR(512))
user_table.create_column("aws_region", sqlalchemy.VARCHAR(256))
system_table.insert({
"key":"user_01",
"value":True})
def to_dataset(self, db: dataset.Database, drop=False):
if self.name in db.tables and drop:
db[self.name].drop()
if self.name not in db.tables:
db.create_table(self.name, primary_id=self.idx_name)
with db as tx:
for row in self.all():
tx[self.name].insert(row)
def update(self, db: dataset.Database):
user_table: dataset.Table = db.create_table("users")
user_table.upsert({
"email": self._email,
"name": self._name,
"password": encrypt(self._password),
"aws_access_key_id": encrypt(self._aws_access_key_id),
"aws_secret_key": encrypt(self._aws_secret_key),
"aws_region": encrypt(self._aws_region)}, ['email'])
def to_dataset(self,
db: dataset.Database,
chunk_size: int = 1000) -> dataset.Database:
for tablename in self.tables:
pk_id = self._tables[tablename].idx_name
tableobject = db.create_table(tablename, primary_id=pk_id)
tableobject.insert_many(list(self._tables[tablename].all()),
chunk_size=chunk_size)
return db
def setup(cls, db: dataset.Database):
system_table = db.get_table("system")
if None == system_table.find_one(key="instance_info_00"):
instance_info_table: dataset.Table = db.create_table(
"instance_infos")
instance_info_table.create_column("user_id",
sqlalchemy.VARCHAR(256))
instance_info_table.create_column("name", sqlalchemy.VARCHAR(512))
instance_info_table.create_column("vpc_cidr_block",
sqlalchemy.VARCHAR(64))
instance_info_table.create_column("subnet_cidr_block",
sqlalchemy.VARCHAR(64))
instance_info_table.create_column("instance_type",
sqlalchemy.VARCHAR(64))
instance_info_table.create_column("image_type",
sqlalchemy.VARCHAR(64))
instance_info_table.create_column("vpc_id",
sqlalchemy.VARCHAR(128))
instance_info_table.create_column("gateway_id",
sqlalchemy.VARCHAR(128))
instance_info_table.create_column("route_table_id",
sqlalchemy.VARCHAR(128))
instance_info_table.create_column("subnet_id",
sqlalchemy.VARCHAR(128))
instance_info_table.create_column("group_id",
sqlalchemy.VARCHAR(128))
instance_info_table.create_column("key_name",
sqlalchemy.VARCHAR(128))
instance_info_table.create_column("instance_id",
sqlalchemy.VARCHAR(128))
instance_info_table.create_column("associate_id",
sqlalchemy.VARCHAR(128))
instance_info_table.create_column("pem", sqlalchemy.TEXT)
instance_info_table.create_column("status", sqlalchemy.VARCHAR(64))
instance_info_table.create_column("ports", sqlalchemy.VARCHAR(512))
instance_info_table.create_column("git", sqlalchemy.VARCHAR(512))
instance_info_table.create_column("path", sqlalchemy.VARCHAR(512))
instance_info_table.create_column("sh", sqlalchemy.VARCHAR(512))
system_table.insert({"key": "instance_info_00", "value": True})
def find_one_from_email(cls, db: dataset.Database, email: str):
user_table: dataset.Table = db.create_table("users")
print(f"{[u for u in user_table.find()]}")
_user = user_table.find_one(email=email)
if _user == None:
return User.create_empty_user()
user = User()
user._id = _user.get("id", None)
user._name = _user.get("name", "")
user._email = _user.get("email", "")
user._password = decrypt(_user.get("password", ""))
user._aws_access_key_id = decrypt(_user.get("aws_access_key_id", ""))
user._aws_secret_key = decrypt(_user.get("aws_secret_key", ""))
user._aws_region = decrypt(_user.get("aws_region", ""))
return user
def find_all(cls, db: dataset.Database, limit:int=100):
user_table: dataset.Table = db.create_table("users")
print(f"{[u for u in user_table.find()]}")
_users = user_table.find(_limit=limit)
if _users == None:
return []
ret: List = []
for _user in _users:
user = User()
user._id = _user.get("id", None)
user._name = _user.get("name", "")
user._email = _user.get("email", "")
user._password = decrypt(_user.get("password", ""))
user._aws_access_key_id = decrypt(_user.get("aws_access_key_id", ""))
user._aws_secret_key = decrypt(_user.get("aws_secret_key", ""))
user._aws_region = decrypt(_user.get("aws_region", ""))
ret.append(user)
return ret
def setup_db(cls, db: dataset.Database):
instance_info_table: dataset.Table = db.create_table("instance_infos")
instance_info_table.create_column("user_id", sqlalchemy.VARCHAR(256))
instance_info_table.create_column("name", sqlalchemy.VARCHAR(512))
instance_info_table.create_column("vpc_cidr_block",
sqlalchemy.VARCHAR(64))
instance_info_table.create_column("subnet_cidr_block",
sqlalchemy.VARCHAR(64))
instance_info_table.create_column("instance_type",
sqlalchemy.VARCHAR(64))
instance_info_table.create_column("image_type", sqlalchemy.VARCHAR(64))
instance_info_table.create_column("vpc_id", sqlalchemy.VARCHAR(128))
instance_info_table.create_column("gateway_id",
sqlalchemy.VARCHAR(128))
instance_info_table.create_column("route_table_id",
sqlalchemy.VARCHAR(128))
instance_info_table.create_column("subnet_id", sqlalchemy.VARCHAR(128))
instance_info_table.create_column("group_id", sqlalchemy.VARCHAR(128))
instance_info_table.create_column("key_name", sqlalchemy.VARCHAR(128))
instance_info_table.create_column("instance_id",
sqlalchemy.VARCHAR(128))
instance_info_table.create_column("pem", sqlalchemy.TEXT)
instance_info_table.create_column("status", sqlalchemy.VARCHAR(64))
def save(self, db: dataset.Database):
table: dataset.Table = db.get_table("instance_infos")
table.upsert(self.to_dict(), ["user_id", "name"])
def negative_sentence_sampler(db: Database) -> Dict[str, Any]:
while True:
random_sentences = db.query(f'SELECT * FROM sentence ORDER BY RANDOM()')
for sentence in random_sentences:
yield sentence
def delete_from_name(cls, db: dataset.Database, name: str):
instance_table: dataset.Table = db.create_table("instance_infos")
instance_table.delete(name=name)
def find_instance_info(cls, db: dataset.Database, user_id: str):
user_table: dataset.Table = db.create_table("instance_infos")
resp = user_table.find(user_id=user_id)
return [InstanceInfo.create_from_dict(r) for r in resp]