def test_create_table_with_select_and_columns_fails(self):
select = Query.from_(self.existing_table).select(self.existing_table.foo, self.existing_table.bar)
with self.subTest("for columns before as_select"):
with self.assertRaises(AttributeError):
Query.create_table(self.new_table).columns(self.foo, self.bar).as_select(select)
with self.subTest("for as_select before columns"):
with self.assertRaises(AttributeError):
Query.create_table(self.new_table).as_select(select).columns(self.foo, self.bar)
def test_create_table_with_columns(self):
with self.subTest("without temporary keyword"):
q = Query.create_table(self.new_table).columns(self.foo, self.bar)
self.assertEqual('CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100))',
str(q))
with self.subTest("with temporary keyword"):
q = (Query.create_table(self.new_table).temporary().columns(
self.foo, self.bar))
self.assertEqual(
'CREATE TEMPORARY TABLE "abc" ("a" INT,"b" VARCHAR(100))',
str(q))
def test_create_table_with_columns(self):
with self.subTest("with nullable"):
a = Column("a", "INT", True)
b = Column("b", "VARCHAR(100)", False)
q = Query.create_table(self.new_table).columns(a, b)
self.assertEqual('CREATE TABLE "abc" ("a" INT NULL,"b" VARCHAR(100) NOT NULL)', str(q))
with self.subTest("with defaults"):
a = Column("a", "INT", default=ValueWrapper(42))
b = Column("b", "VARCHAR(100)", default=ValueWrapper("foo"))
q = Query.create_table(self.new_table).columns(a, b)
self.assertEqual('CREATE TABLE "abc" ("a" INT DEFAULT 42,"b" VARCHAR(100) DEFAULT \'foo\')', str(q))
with self.subTest("with period for"):
a = Column("id", "INT")
b = Column("valid_from", "DATETIME")
c = Column("valid_to", "DATETIME")
q = Query.create_table(self.new_table).columns(a, b, c).period_for('valid_period', b, c)
self.assertEqual(
'CREATE TABLE "abc" ('
'"id" INT,'
'"valid_from" DATETIME,'
'"valid_to" DATETIME,'
'PERIOD FOR "valid_period" ("valid_from","valid_to"))',
str(q),
)
with self.subTest("without temporary keyword"):
q = Query.create_table(self.new_table).columns(self.foo, self.bar)
self.assertEqual('CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100))', str(q))
with self.subTest("with temporary keyword"):
q = Query.create_table(self.new_table).temporary().columns(self.foo, self.bar)
self.assertEqual('CREATE TEMPORARY TABLE "abc" ("a" INT,"b" VARCHAR(100))', str(q))
with self.subTest("with primary key"):
q = Query.create_table(self.new_table).columns(self.foo, self.bar).primary_key(self.foo, self.bar)
self.assertEqual('CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100),PRIMARY KEY ("a","b"))', str(q))
with self.subTest("with unique keys"):
q = (
Query.create_table(self.new_table)
.columns(self.foo, self.bar)
.unique(self.foo, self.bar)
.unique(self.foo)
)
self.assertEqual('CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100),UNIQUE ("a","b"),UNIQUE ("a"))', str(q))
with self.subTest("with system versioning"):
q = Query.create_table(self.new_table).columns(self.foo, self.bar).with_system_versioning()
self.assertEqual('CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100)) WITH SYSTEM VERSIONING', str(q))
def test_create_table_with_select(self):
select = Query.from_(self.existing_table).select(self.existing_table.foo, self.existing_table.bar)
with self.subTest("without temporary keyword"):
q = Query.create_table(self.new_table).as_select(select)
self.assertEqual('CREATE TABLE "abc" AS (SELECT "foo","bar" FROM "efg")', str(q))
with self.subTest("with temporary keyword"):
q = Query.create_table(self.new_table).temporary().as_select(select)
self.assertEqual(
'CREATE TEMPORARY TABLE "abc" AS (SELECT "foo","bar" FROM "efg")',
str(q),
)
def make_table(table_def, conn_params):
table = Table(table_def["name"])
cols = [Column(k, v) for k, v, in table_def["columns"].items()]
drop = Query.drop_table(table).if_exists()
create = Query.create_table(table).columns(*cols)
execute_query(str(drop) + "\n" + str(create), conn_params)
def create_table(self, table: str) -> int:
target = Table(table, schema=self.schema)
columns = [
Column(name, python_type_to_sql(annotation))
for name, annotation in Entry.__annotations__.items()
]
query = Query.create_table(target).columns(*columns)
sql = str(query)
logging.debug(sql)
with self.transaction() as cursor:
return cast(int, cursor.execute(sql))
def createTableIfNotExists(repo: Dolt, table: str):
query: CreateQueryBuilder = Query.create_table(table=table) \
.columns(
Column("id", "bigint unsigned", nullable=False),
Column("twitter_user_id", "bigint unsigned", nullable=False),
Column("date", "datetime", nullable=False),
Column("text", "longtext", nullable=False),
Column("device", "longtext", nullable=False),
Column("favorites", "bigint unsigned", nullable=False),
Column("retweets", "bigint unsigned", nullable=False),
Column("quoteTweets", "bigint unsigned"),
Column("replies", "bigint unsigned"),
Column("isRetweet", "tinyint", nullable=False),
Column("isDeleted", "tinyint", nullable=False),
Column("repliedToTweetId", "bigint unsigned"),
Column("repliedToUserId", "bigint unsigned"),
Column("repliedToTweetDate", "datetime"),
Column("retweetedTweetId", "bigint unsigned"),
Column("retweetedUserId", "bigint unsigned"),
Column("retweetedTweetDate", "datetime"),
Column("expandedUrls", "longtext"),
Column("json", "longtext"),
Column("json_v1", "longtext"),
Column("notes", "longtext")
).primary_key("id")
# TODO: Figure Out How To Add The Below Parameters
# --------------------------------------------------------------------------------------------------------------
# KEY `twitter_user_id_idx` (`twitter_user_id`),
# CONSTRAINT `twitter_user_id_ref` FOREIGN KEY (`twitter_user_id`) REFERENCES `government` (`twitter_user_id`)
# --------------------------------------------------------------------------------------------------------------
# ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# --------------------------------------------------------------------------------------------------------------
repo.sql(query=query.get_sql(quote_char=None), result_format="csv")
def test_create_table_as_select_not_query_raises_error(self):
with self.assertRaises(TypeError):
Query.create_table(self.new_table).as_select("abc")
def test_create_table_without_columns_or_select_empty(self):
q = Query.create_table(self.new_table)
self.assertEqual("", str(q))
def test_create_table_with_columns(self):
with self.subTest("with nullable"):
a = Column("a", "INT", True)
b = Column("b", "VARCHAR(100)", False)
q = Query.create_table(self.new_table).columns(a, b)
self.assertEqual(
'CREATE TABLE "abc" ("a" INT NULL,"b" VARCHAR(100) NOT NULL)',
str(q))
with self.subTest("with defaults"):
a = Column("a", "INT", default=ValueWrapper(42))
b = Column("b", "VARCHAR(100)", default=ValueWrapper("foo"))
q = Query.create_table(self.new_table).columns(a, b)
self.assertEqual(
'CREATE TABLE "abc" ("a" INT DEFAULT 42,"b" VARCHAR(100) DEFAULT \'foo\')',
str(q))
with self.subTest("with unwrapped defaults"):
a = Column("a", "INT", default=42)
b = Column("b", "VARCHAR(100)", default="foo")
q = Query.create_table(self.new_table).columns(a, b)
self.assertEqual(
'CREATE TABLE "abc" ("a" INT DEFAULT 42,"b" VARCHAR(100) DEFAULT \'foo\')',
str(q))
with self.subTest("with period for"):
a = Column("id", "INT")
b = Column("valid_from", "DATETIME")
c = Column("valid_to", "DATETIME")
q = Query.create_table(self.new_table).columns(a, b, c).period_for(
'valid_period', b, c)
self.assertEqual(
'CREATE TABLE "abc" ('
'"id" INT,'
'"valid_from" DATETIME,'
'"valid_to" DATETIME,'
'PERIOD FOR "valid_period" ("valid_from","valid_to"))',
str(q),
)
with self.subTest("without temporary keyword"):
q = Query.create_table(self.new_table).columns(self.foo, self.bar)
self.assertEqual('CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100))',
str(q))
with self.subTest("with temporary keyword"):
q = Query.create_table(self.new_table).temporary().columns(
self.foo, self.bar)
self.assertEqual(
'CREATE TEMPORARY TABLE "abc" ("a" INT,"b" VARCHAR(100))',
str(q))
with self.subTest("with primary key"):
q = Query.create_table(self.new_table).columns(
self.foo, self.bar).primary_key(self.foo, self.bar)
self.assertEqual(
'CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100),PRIMARY KEY ("a","b"))',
str(q))
with self.subTest("with simple foreign key"):
cref, dref = Columns(("c", "INT"), ("d", "VARCHAR(100)"))
q = (Query.create_table(self.new_table).columns(
self.foo,
self.bar).foreign_key([self.foo, self.bar],
self.existing_table, [cref, dref]))
self.assertEqual(
'CREATE TABLE "abc" ('
'"a" INT,'
'"b" VARCHAR(100),'
'FOREIGN KEY ("a","b") REFERENCES "efg" ("c","d"))',
str(q),
)
with self.subTest("with foreign key reference options"):
cref, dref = Columns(("c", "INT"), ("d", "VARCHAR(100)"))
q = (Query.create_table(self.new_table).columns(
self.foo, self.bar).foreign_key(
[self.foo, self.bar],
self.existing_table,
[cref, dref],
on_delete=ReferenceOption.cascade,
on_update=ReferenceOption.restrict,
))
self.assertEqual(
'CREATE TABLE "abc" ('
'"a" INT,'
'"b" VARCHAR(100),'
'FOREIGN KEY ("a","b") REFERENCES "efg" ("c","d") ON DELETE CASCADE ON UPDATE RESTRICT)',
str(q),
)
with self.subTest("with unique keys"):
q = (Query.create_table(self.new_table).columns(
self.foo, self.bar).unique(self.foo,
self.bar).unique(self.foo))
self.assertEqual(
'CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100),UNIQUE ("a","b"),UNIQUE ("a"))',
str(q))
with self.subTest("with system versioning"):
q = Query.create_table(self.new_table).columns(
self.foo, self.bar).with_system_versioning()
self.assertEqual(
'CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100)) WITH SYSTEM VERSIONING',
str(q))
with self.subTest("with unlogged keyword"):
q = Query.create_table(self.new_table).unlogged().columns(
self.foo, self.bar)
self.assertEqual(
'CREATE UNLOGGED TABLE "abc" ("a" INT,"b" VARCHAR(100))',
str(q))
with self.subTest("with if not exists keyword"):
q = Query.create_table(self.new_table).if_not_exists().columns(
self.foo, self.bar)
self.assertEqual(
'CREATE TABLE IF NOT EXISTS "abc" ("a" INT,"b" VARCHAR(100))',
str(q))
from pypika import Query, Column
from dotenv import load_dotenv
from db import make_connection
def execute(statement):
try:
con.cursor().execute(statement)
except Exception as e:
errors.append(e)
load_dotenv()
con = make_connection()
errors = []
execute(
Query.create_table('distances').columns(
Column('distance', 'REAL', nullable=False),
Column('timestamp', 'INT', nullable=False)).get_sql())
execute(
Query.create_table('settings').columns(
Column('key', 'TEXT', nullable=False),
Column('value', 'TEXT', nullable=False)).unique("key").get_sql())
con.commit()
con.close()
for e in errors:
print(f"error: {e}")
