def postgres_bin_folder_set(self, filechooser): path = filechooser.get_filename() filechooser.set_tooltip_text(path) buf = self.builder.get_object('postgres_bin_path_buffer') sqlite = get_apsw_connection() for row in sqlite.cursor().execute("SELECT user, password, host, port " "FROM postgres_conn;"): sql_user = row[0] sql_password = row[1] sql_host = row[2] sql_port = row[3] sqlite.close() command = ["%s/pg_isready" % path, "-h", sql_host, "-p", sql_port] try: p = Popen(command, stdout=PIPE, stderr=PIPE) stdout, stderr = p.communicate() buf.set_text(stdout.decode('utf-8') + stderr.decode('utf-8')) except Exception as e: buf.set_text(str(e)) filechooser.set_filename(self.bin_path) return sqlite = get_apsw_connection() sqlite.cursor().execute( "UPDATE settings SET value = ? " "WHERE setting = 'postgres_bin_path'", (path, )) sqlite.close()
def save_window_layout_activated (self, menuitem): sqlite = get_apsw_connection() c = sqlite.cursor() width, height = self.window.get_size() c.execute("REPLACE INTO open_invoices (widget_id, value) " "VALUES ('window_width', ?)", (width,)) c.execute("REPLACE INTO open_invoices (widget_id, value) " "VALUES ('window_height', ?)", (height,)) tuple_ = self.get_object('open_invoice_store').get_sort_column_id() sort_column = tuple_[0] if sort_column == None: sort_column = 0 sort_type = 0 else: sort_type = tuple_[1].numerator c.execute("REPLACE INTO open_invoices (widget_id, value) " "VALUES ('sort_column', ?)", (sort_column,)) c.execute("REPLACE INTO open_invoices (widget_id, value) " "VALUES ('sort_type', ?)", (sort_type,)) for column in ['number_column', 'invoice_column', 'contact_column', 'date_created_column', 'items_column']: try: width = self.get_object(column).get_width() except Exception as e: self.show_message("On column %s\n %s" % (column, str(e))) continue c.execute("REPLACE INTO open_invoices (widget_id, value) " "VALUES (?, ?)", (column, width)) sqlite.close()
def set_window_layout_from_settings(self): sqlite = get_apsw_connection() c = sqlite.cursor() c.execute("SELECT value FROM open_invoices " "WHERE widget_id = 'window_width'") width = c.fetchone()[0] c.execute("SELECT value FROM open_invoices " "WHERE widget_id = 'window_height'") height = c.fetchone()[0] self.window.resize(width, height) c.execute("SELECT value FROM open_invoices " "WHERE widget_id = 'sort_column'") sort_column = c.fetchone()[0] c.execute("SELECT value FROM open_invoices " "WHERE widget_id = 'sort_type'") sort_type = Gtk.SortType(c.fetchone()[0]) store = self.get_object('open_invoice_store') store.set_sort_column_id(sort_column, sort_type) c.execute("SELECT widget_id, value FROM open_invoices WHERE " "widget_id IN ('number_column', " "'invoice_column', " "'contact_column', " "'date_created_column', " "'items_column')") for row in c.fetchall(): column = self.get_object(row[0]) width = row[1] if width == 0: column.set_visible(False) else: column.set_fixed_width(width) sqlite.close()
def __init__(self, automatic=False): Gtk.Builder.__init__(self) self.add_from_file(UI_FILE) self.connect_signals(self) self.bin_path = get_postgres_bin_path() self.terminal = Vte.Terminal() self.terminal.set_scroll_on_output(True) self.terminal.set_scrollback_lines(-1) self.automatic = automatic self.get_object('backup_scrolled_window').add(self.terminal) day = time.strftime("%Y-%m-%d-%H:%M") name = DB_NAME + "_" + day + ".pbk" dialog = self.get_object('backup_dialog') dialog.set_current_name(name) sqlite = get_apsw_connection() cursor = sqlite.cursor() cursor.execute("SELECT value FROM settings " "WHERE setting = 'backup_path'") for row in cursor.fetchall(): backup_path = row[0] dialog.set_current_folder(backup_path) sqlite.close() if automatic and os.path.exists(backup_path): full_filepath = backup_path + "/" + name self.get_object('status_label').set_label(full_filepath) self.backup_database(full_filepath) else: result = dialog.run() if result == Gtk.ResponseType.APPLY: filename = dialog.get_filename() dialog.hide() self.backup_database(filename) dialog.destroy()
def test_connection_clicked(self, widget): sql_user = self.builder.get_object("entry2").get_text() sql_password = self.builder.get_object("entry3").get_text() sql_host = self.builder.get_object("entry4").get_text() sql_port = self.builder.get_object("entry5").get_text() try: self.db = psycopg2.connect(host=sql_host, user=sql_user, password=sql_password, port=sql_port) except Exception as e: print(e) self.message_error() self.builder.get_object("textbuffer1").set_text(str(e)) self.builder.get_object("box2").set_sensitive(False) self.builder.get_object("grid2").set_sensitive(False) return sqlite = get_apsw_connection() sqlite.cursor().execute( "UPDATE postgres_conn SET " "(user, password, host, port) = " "(?, ?, ?, ?)", (sql_user, sql_password, sql_host, sql_port)) sqlite.close() self.message_success() self.retrieve_dbs() self.builder.get_object("textbuffer1").set_text('') self.builder.get_object("box2").set_sensitive(True) self.builder.get_object("grid2").set_sensitive(True)
def backup_database(self, filename): self.window = self.get_object('backup_window') self.window.show_all() sqlite = get_apsw_connection() for row in sqlite.cursor().execute("SELECT " "user, password, host, port " "FROM postgres_conn;"): sql_user = row[0] sql_password = row[1] sql_host = row[2] sql_port = row[3] sqlite.close() pty = Vte.Pty.new_sync(Vte.PtyFlags.DEFAULT) self.terminal.set_pty(pty) backup_command = [ "%s/pg_dump" % self.bin_path, "-Cwv", "-F", "c", "-U", sql_user, "-h", sql_host, "-p", sql_port, "-d", DB_NAME, "-f", filename ] pty.spawn_async( None, backup_command, None, GLib.SpawnFlags.DEFAULT, None, None, -1, None, self.spawn_finished_callback, )
def save_window_layout_activated(self, menuitem): sqlite = get_apsw_connection() c = sqlite.cursor() width, height = self.window.get_size() c.execute( "REPLACE INTO vendor_history (widget_id, size) " "VALUES ('window_width', ?)", (width, )) c.execute( "REPLACE INTO vendor_history (widget_id, size) " "VALUES ('window_height', ?)", (height, )) for column in [ 'date_column', 'invoice_name_column', 'description_column', 'amount_column', 'paid_column', 'posted_column', 'qty_column', 'product_name_column', 'product_ext_name_column', 'remark_column', 'price_column', 'ext_price_column', 'order_number_column', 'po_column', 'po_date_column', 'vendor_column' ]: try: width = self.builder.get_object(column).get_width() except Exception as e: self.show_message("On column %s\n %s" % (column, str(e))) continue c.execute( "REPLACE INTO vendor_history (widget_id, size) " "VALUES (?, ?)", (column, width)) sqlite.close()
def retrieve_dbs(self): sqlite = get_apsw_connection() db_name_store = self.builder.get_object('db_name_store') db_name_store.clear() for row in sqlite.cursor().execute("SELECT user, password, host, port " "FROM postgres_conn;"): sql_user = row[0] sql_password = row[1] sql_host = row[2] sql_port = row[3] sqlite.close() cursor = self.db.cursor() cursor.execute( "SELECT b.datname FROM pg_catalog.pg_database b ORDER BY 1;") for db_tuple in cursor.fetchall(): try: db_name = db_tuple[0] db = psycopg2.connect(database=db_name, host=sql_host, user=sql_user, password=sql_password, port=sql_port) cursor = db.cursor() cursor.execute("SELECT version FROM settings" ) # valid pygtk posting database version = cursor.fetchone()[0] db_name_store.append([version, db_name]) db.close() except Exception as e: pass cursor.close()
def __init__(self, db_name, parent): Gtk.Builder.__init__(self) self.add_from_file(UI_FILE) self.connect_signals(self) self.parent = parent self.db_name = db_name self.bin_path = get_postgres_bin_path() self.terminal = Vte.Terminal() self.terminal.set_scroll_on_output(True) self.terminal.set_scrollback_lines(-1) self.get_object('restore_scrolled_window').add(self.terminal) self.dialog = self.get_object('restore_dialog') sqlite = get_apsw_connection() cursor = sqlite.cursor() cursor.execute("SELECT value FROM settings " "WHERE setting = 'backup_path' AND setting != ''") for row in cursor.fetchall(): backup_path = row[0] self.dialog.set_current_folder(backup_path) sqlite.close() result = self.dialog.run() if result == Gtk.ResponseType.APPLY: self.dialog.hide() self.restore_database() self.dialog.destroy()
def load_window_layout(self): sqlite = get_apsw_connection() c = sqlite.cursor() c.execute("SELECT value FROM product_search " "WHERE widget_id = 'window_width'") width = c.fetchone()[0] c.execute("SELECT value FROM product_search " "WHERE widget_id = 'window_height'") height = c.fetchone()[0] self.window.resize(width, height) c.execute("SELECT value FROM product_search " "WHERE widget_id = 'sort_column'") sort_column = c.fetchone()[0] c.execute("SELECT value FROM product_search " "WHERE widget_id = 'sort_type'") sort_type = Gtk.SortType(c.fetchone()[0]) store = self.builder.get_object('product_store') store.set_sort_column_id(sort_column, sort_type) c.execute("SELECT widget_id, value FROM product_search WHERE " "widget_id NOT IN ('window_width', " "'window_height', " "'sort_column', " "'sort_type')") for row in c.fetchall(): column = self.builder.get_object(row[0]) width = row[1] if width == 0: column.set_visible(False) else: column.set_fixed_width(width) sqlite.close()
def save_window_layout_activated(self, menuitem): sqlite = get_apsw_connection() c = sqlite.cursor() width, height = self.window.get_size() c.execute( "REPLACE INTO product_search (widget_id, value) " "VALUES ('window_width', ?)", (width, )) c.execute( "REPLACE INTO product_search (widget_id, value) " "VALUES ('window_height', ?)", (height, )) tuple_ = self.builder.get_object('product_store').get_sort_column_id() sort_column = tuple_[0] if sort_column == None: sort_column = 0 sort_type = 0 else: sort_type = tuple_[1].numerator c.execute( "REPLACE INTO product_search (widget_id, value) " "VALUES ('sort_column', ?)", (sort_column, )) c.execute( "REPLACE INTO product_search (widget_id, value) " "VALUES ('sort_type', ?)", (sort_type, )) treeview = self.builder.get_object('treeview1') columns = treeview.get_columns() for column in columns: widget_name = column.get_name() width = column.get_width() c.execute( "REPLACE INTO product_search (widget_id, value) " "VALUES (?, ?)", (widget_name, width)) sqlite.close()
def get_postgre_settings(self, widget): sqlite = get_apsw_connection() cursor = sqlite.cursor() for row in cursor.execute("SELECT user, password, host, port " "FROM postgres_conn;"): self.builder.get_object("entry2").set_text(row[0]) self.builder.get_object("entry3").set_text(row[1]) self.builder.get_object("entry4").set_text(row[2]) self.builder.get_object("entry5").set_text(row[3]) cursor.execute("SELECT value FROM settings " "WHERE setting = 'postgres_bin_path'") self.bin_path = cursor.fetchone()[0] chooser = self.builder.get_object('bin_path_chooser') if os.path.exists(self.bin_path): chooser.set_current_folder(self.bin_path) chooser.set_tooltip_text(self.bin_path) else: chooser.set_tooltip_text(self.bin_path + ' does not exist') cursor.execute("SELECT value FROM settings " "WHERE setting = 'backup_path'") path = cursor.fetchone()[0] chooser = self.builder.get_object('backup_folder_chooser') if os.path.exists(path): chooser.set_current_folder(path) chooser.set_tooltip_text(path) else: chooser.set_tooltip_text(path + ' does not exist') sqlite.close()
def save_window_layout_activated(self, button): sqlite = get_apsw_connection() c = sqlite.cursor() width, height = self.window.get_size() c.execute( "REPLACE INTO product_overview (widget_id, size) " "VALUES ('window_width', ?)", (width, )) c.execute( "REPLACE INTO product_overview (widget_id, size) " "VALUES ('window_height', ?)", (height, )) for column in [ 'name_column', 'ext_name_column', 'description_column', 'barcode_column', 'unit_column', 'weight_column', 'tare_column', 'manufacturer_sku_column', 'expense_account_column', 'inventory_account_column', 'revenue_account_column', 'sellable_column', 'purchasable_column', 'manufactured_column', 'job_column', 'stocked_column' ]: try: width = self.get_object(column).get_width() except Exception as e: self.show_message("On column %s\n %s" % (column, str(e))) continue c.execute( "REPLACE INTO product_overview (widget_id, size) " "VALUES (?, ?)", (column, width)) sqlite.close()
def get_postgres_bin_path(): sqlite = get_apsw_connection() cursor = sqlite.cursor() cursor.execute("SELECT value FROM settings " "WHERE setting = 'postgres_bin_path'") bin_path = cursor.fetchone()[0] sqlite.close() return bin_path
def backup_folder_path_set(self, filechooserbutton): path = filechooserbutton.get_filename() filechooserbutton.set_tooltip_text(path) sqlite = get_apsw_connection() sqlite.cursor().execute( "UPDATE settings SET value = ? " "WHERE setting = 'backup_path'", (path, )) sqlite.close()
def __init__(self, main): self.k = Keybinder self.k.init() Gtk.Builder.__init__(self) self.add_from_file(UI_FILE) self.connect_signals(self) self.store = self.get_object('keybindings_store') self.sqlite_conn = get_apsw_connection()
def save_window_layout_clicked (self, button): sqlite = get_apsw_connection () c = sqlite.cursor() width, height = self.window.get_size() c.execute("REPLACE INTO product_edit (widget_id, size) " "VALUES ('window_width', ?)", (width,)) c.execute("REPLACE INTO product_edit (widget_id, size) " "VALUES ('window_height', ?)", (height,)) sqlite.close()
def set_active_database(self): sqlite = get_apsw_connection() for row in sqlite.cursor().execute( "SELECT db_name FROM postgres_conn;"): sql_database = row[0] self.builder.get_object('combobox1').set_active_id(sql_database) self.builder.get_object('label10').set_text("Current database : " + sql_database) self.builder.get_object('label14').set_text(sql_database) sqlite.close()
def accel_cleared(self, cellrendereraccel, path): sqlite_conn = get_apsw_connection() cursor = sqlite_conn.cursor() key, modifier = Gtk.accelerator_parse('') menu_path = self.store[path][0] self.store[path][1] = key self.store[path][2] = modifier cursor.execute("DELETE FROM keybindings " "WHERE widget_id = ?", (menu_path, )) sqlite_conn.close()
def create_db(self): self.db_name_entry = self.builder.get_object("entry1") db_name = self.db_name_entry.get_text() if db_name == "": print("No database name!") self.status_update("No database name!") return sqlite = get_apsw_connection() for row in sqlite.cursor().execute("SELECT user, password, host, port " "FROM postgres_conn;"): sql_user = row[0] sql_password = row[1] sql_host = row[2] sql_port = row[3] pysql = psycopg2.connect(database="postgres", host=sql_host, user=sql_user, password=sql_password, port=sql_port) self.cursor = pysql.cursor() pysql.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) try: self.cursor.execute("""CREATE DATABASE "%s";""" % db_name) except Exception as e: print(e) if (e.pgcode == "42P04"): self.status_update("Database already exists!") return while Gtk.events_pending(): Gtk.main_iteration() self.db = psycopg2.connect(database=db_name, host=sql_host, user=sql_user, password=sql_password, port=sql_port) #self.db_name = db_name if not self.create_tables(): self.close_db(db_name) return if not self.add_primary_data(): self.close_db(db_name) return if not self.update_tables_major(): self.close_db(db_name) return if not self.update_tables_minor(): self.close_db(db_name) return self.db.commit() sqlite.cursor().execute("UPDATE postgres_conn SET db_name = ?", (db_name, )) self.db_name_entry.set_text("") self.status_update("Done!") subprocess.Popen(["./src/main.py"]) GLib.timeout_add_seconds(1, Gtk.main_quit)
def set_window_layout_from_settings (self): sqlite = get_apsw_connection () c = sqlite.cursor() c.execute("SELECT size FROM product_edit " "WHERE widget_id = 'window_width'") width = c.fetchone()[0] c.execute("SELECT size FROM product_edit " "WHERE widget_id = 'window_height'") height = c.fetchone()[0] self.get_object('window').resize(width, height) sqlite.close()
def save_hotkey_preference(self, path): sqlite_conn = get_apsw_connection() cursor = sqlite_conn.cursor() menu_path = self.store[path][0] key = self.store[path][1] modifiers = self.store[path][2] accelerator = Gtk.accelerator_name(key, modifiers) cursor.execute( "REPLACE INTO keybindings (keybinding, widget_id) " "VALUES (?, ?) ", (accelerator, menu_path)) sqlite_conn.close()
def login_single_clicked(self, widget): self.db.close() selected = self.builder.get_object('combobox-entry').get_text() if selected != None: sqlite = get_apsw_connection() sqlite.cursor().execute("UPDATE postgres_conn SET db_name = '%s'" % (selected)) self.error = False self.window.close() subprocess.Popen( ["./src/main.py", "database %s" % selected, str(LOG_FILE)]) Gtk.main_quit()
def connect_to_db(name): import constants sqlite = sqlite_utils.get_apsw_connection() cursor = sqlite.cursor() sqlite_utils.create_apsw_tables(cursor) sqlite_utils.update_apsw_tables(cursor) sqlite.close() # unlock file after updating sqlite = sqlite_utils.get_apsw_connection() constants.sqlite_connection = sqlite cursor = sqlite.cursor() if name == None: for row in cursor.execute("SELECT db_name FROM postgres_conn;"): sql_database = row[0] else: sql_database = name for row in cursor.execute("SELECT user, password, host, port " "FROM postgres_conn;"): sql_user = row[0] sql_password = row[1] sql_host = row[2] sql_port = row[3] cursor.close() try: constants.DB = psycopg2.connect(dbname=sql_database, host=sql_host, user=sql_user, password=sql_password, port=sql_port) constants.db_name = sql_database constants.start_broadcaster() import accounts accounts.populate_accounts() return True except psycopg2.OperationalError as e: print(e.args[0]) constants.db_name = 'False' return False
def save_window_layout_activated(self, menuitem): sqlite = get_apsw_connection() c = sqlite.cursor() position = self.builder.get_object('pane1').get_position() c.execute( "REPLACE INTO resource_calendar (widget_id, size) " "VALUES ('pane1', ?)", (position, )) position = self.builder.get_object('pane2').get_position() c.execute( "REPLACE INTO resource_calendar (widget_id, size) " "VALUES ('pane2', ?)", (position, )) active = self.builder.get_object( 'show_detail_checkbutton').get_active() c.execute( "REPLACE INTO resource_calendar (widget_id, size) " "VALUES ('show_details_checkbutton', ?)", (active, )) value = self.builder.get_object('row_height_spinbutton').get_value() c.execute( "REPLACE INTO resource_calendar (widget_id, size) " "VALUES ('row_height_value', ?)", (value, )) value = self.builder.get_object('row_width_spinbutton').get_value() c.execute( "REPLACE INTO resource_calendar (widget_id, size) " "VALUES ('row_width_value', ?)", (value, )) width, height = self.builder.get_object('popover_window').get_size() c.execute( "REPLACE INTO resource_calendar (widget_id, size) " "VALUES ('edit_window_width', ?)", (width, )) c.execute( "REPLACE INTO resource_calendar (widget_id, size) " "VALUES ('edit_window_height', ?)", (height, )) for column in [ 'subject_column', 'qty_column', 'type_column', 'contact_column', 'category_column' ]: try: width = self.builder.get_object(column).get_width() except Exception as e: self.show_message("On column %s\n %s" % (column, str(e))) continue c.execute( "REPLACE INTO resource_calendar (widget_id, size) " "VALUES (?, ?)", (column, width)) sqlite.close()
def set_widget_sizes(self): sqlite = get_apsw_connection() c = sqlite.cursor() c.execute("SELECT size FROM resource_calendar " "WHERE widget_id = 'pane1'") self.builder.get_object('pane1').set_position(c.fetchone()[0]) c.execute("SELECT size FROM resource_calendar " "WHERE widget_id = 'pane2'") self.builder.get_object('pane2').set_position(c.fetchone()[0]) c.execute("SELECT size FROM resource_calendar " "WHERE widget_id = 'show_details_checkbutton'") active = bool(c.fetchone()[0]) self.builder.get_object('show_detail_checkbutton').set_active(active) c.execute("SELECT size FROM resource_calendar " "WHERE widget_id = 'row_height_value'") value = c.fetchone()[0] self.builder.get_object('row_height_spinbutton').set_value(value) c.execute("SELECT size FROM resource_calendar " "WHERE widget_id = 'row_width_value'") value = c.fetchone()[0] self.builder.get_object('row_width_spinbutton').set_value(value) c.execute("SELECT size FROM resource_calendar " "WHERE widget_id = 'edit_window_width'") width = c.fetchone()[0] c.execute("SELECT size FROM resource_calendar " "WHERE widget_id = 'edit_window_height'") height = c.fetchone()[0] self.builder.get_object('popover_window').resize(width, height) c.execute("SELECT widget_id, size FROM resource_calendar WHERE " "widget_id IN " "('subject_column', " "'qty_column', " "'type_column', " "'contact_column', " "'category_column')") for row in c.fetchall(): width = row[1] column = self.builder.get_object(row[0]) if width == 0: column.set_visible(False) else: column.set_fixed_width(width) sqlite.close() GLib.timeout_add(20, self.center_calendar_horizontal_scroll)
def restore_database(self): self.window = self.get_object('restore_window') self.window.show_all() sqlite = get_apsw_connection() for row in sqlite.cursor().execute("SELECT " "user, password, host, port " "FROM postgres_conn;"): self.sql_user = row[0] self.sql_password = row[1] self.sql_host = row[2] self.sql_port = row[3] sqlite.close() pty = Vte.Pty.new_sync(Vte.PtyFlags.DEFAULT) self.terminal.set_pty(pty) create_command = [ "%s/createdb" % self.bin_path, "-e", "-U", self.sql_user, "-h", self.sql_host, "-p", self.sql_port, "-Ttemplate0", self.db_name ] pty.spawn_async(None, create_command, None, GLib.SpawnFlags.DEFAULT, None, None, -1, None, self.create_spawn_callback)
def set_window_layout_from_settings(self): sqlite = get_apsw_connection() c = sqlite.cursor() c.execute("SELECT size FROM contact_overview " "WHERE widget_id = 'window_width'") width = c.fetchone()[0] c.execute("SELECT size FROM contact_overview " "WHERE widget_id = 'window_height'") height = c.fetchone()[0] self.window.resize(width, height) c.execute("SELECT widget_id, size FROM contact_overview WHERE " "widget_id NOT IN ('window_height', 'window_width')") for row in c.fetchall(): width = row[1] column = self.get_object(row[0]) if width == 0: column.set_visible(False) else: column.set_fixed_width(width) sqlite.close()
def save_window_layout_activated(self, menuitem): sqlite = get_apsw_connection() c = sqlite.cursor() width, height = self.window.get_size() c.execute( "REPLACE INTO contact_overview (widget_id, size) " "VALUES ('window_width', ?)", (width, )) c.execute( "REPLACE INTO contact_overview (widget_id, size) " "VALUES ('window_height', ?)", (height, )) for column in [ 'name_column', 'ext_name_column', 'address_column', 'city_column', 'state_column', 'zip_column', 'fax_column', 'phone_column', 'email_column' ]: try: width = self.get_object(column).get_width() except Exception as e: self.show_message("On column %s\n %s" % (column, str(e))) continue c.execute( "REPLACE INTO contact_overview (widget_id, size) " "VALUES (?, ?)", (column, width)) sqlite.close()