tables.txt

CREATE TABLE users (uid INT(11) AUTO_INCREMENT PRIMARY KEY, username VARCHAR(20), password VARCHAR(100), email VARCHAR(50), cash INT(11), bank INT(11), paytm INT(11), amazon INT(11), owe_in INT(11), owe_out INT(11));


CREATE TABLE passbook (pid INT(11) AUTO_INCREMENT , uid INT(11) NOT NULL, date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, amount INT(11), val BOOLEAN, mode VARCHAR(20), PRIMARY KEY (pid), CONSTRAINT FK_users FOREIGN KEY (uid) REFERENCES users(uid) ON DELETE CASCADE);

c.execute("CREATE TABLE IF NOT EXISTS " + username + "_passbook" + """ (eid INT(11) AUTO_INCREMENT PRIMARY KEY, date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, expenses INT(11), val BOOLEAN, wallet VARCHAR(20))""")

CREATE TABLE passbook_owe (oid INT(11) AUTO_INCREMENT , uid INT(11) NOT NULL, date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, amount INT(11), name VARCHAR(100), PRIMARY KEY (oid), CONSTRAINT FK_users_o FOREIGN KEY (uid) REFERENCES users(uid) ON DELETE CASCADE);

CREATE TABLE queries (qid INT(11) AUTO_INCREMENT , uid INT(11) NOT NULL, username VARCHAR(20) NOT NULL, date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, query VARCHAR(100), PRIMARY KEY (qid), CONSTRAINT FK_users_q FOREIGN KEY (uid) REFERENCES users(uid) ON DELETE CASCADE);

ALTER TABLE users ADD img MEDIUMBLOB AFTER username;
ALTER TABLE users ADD sex VARCHAR(12) AFTER username;
ALTER TABLE users ADD full_name VARCHAR(20) AFTER username;
ALTER TABLE users ADD phone VARCHAR(20) AFTER email;