/
Database.py
108 lines (86 loc) · 3.17 KB
/
Database.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
import sqlite3
import os
from Entry import Entry
from os.path import expanduser
class Database:
def __init__(self):
home = expanduser("~")
directory_path = home + "/Library/Application Support/ProClip"
# create the directory if it does not exist
if not os.path.exists(directory_path):
os.makedirs(directory_path)
self.conn = sqlite3.connect(directory_path + "/proclip.db")
self.c = self.conn.cursor()
create = """CREATE TABLE IF NOT EXISTS entries (
id INTEGER PRIMARY KEY,
alias TEXT,
content TEXT not null,
unique (alias)
)"""
# Create table
self.c.execute(create)
# Save (commit) the changes
self.conn.commit()
def push(self, content):
insert = "INSERT INTO entries (content) VALUES (?)"
self.c.execute(insert, [content])
self.conn.commit()
return self.c.lastrowid
def push_with_alias(self, alias, content):
self.c.execute("INSERT INTO entries (alias, content) VALUES (?, ?)", [alias, content])
self.conn.commit()
return self.c.lastrowid
def list(self):
results = self.c.execute("SELECT * FROM entries").fetchall()
entries = []
for result in results:
entries.append(Entry(result))
return entries
def get(self, data):
result = self.c.execute("SELECT * FROM entries WHERE id=?", [data]).fetchone()
if result is not None:
return Entry(result)
else:
result = self.c.execute("SELECT * FROM entries WHERE alias=?", [data]).fetchone()
if result is not None:
return Entry(result)
return None
def get_id(self, identifier):
result = self.c.execute("SELECT * FROM entries WHERE id=?", [identifier]).fetchone()
if result is not None:
return Entry(result)
else:
return None
def get_alias(self, alias):
result = self.c.execute("SELECT * FROM entries WHERE alias=?", [alias]).fetchone()
if result is not None:
return Entry(result)
else:
return None
def pop(self):
top = self.c.execute("SELECT * FROM entries ORDER BY id ASC LIMIT 1").fetchone()
self.delete(0)
return top
def delete(self, offset):
rows = self.c.execute("SELECT * FROM entries ORDER BY id ASC").fetchmany(offset + 1)
length = len(rows)
if length > offset:
row = rows[offset]
self.c.execute("DELETE FROM entries WHERE id=?", [row[0]])
else:
row = rows[length - 1]
self.c.execute("DELETE FROM entries WHERE id=?", [row[0]])
self.conn.commit()
def delete_alias(self, alias):
self.c.execute("DELETE FROM entries WHERE alias=?", [alias])
self.conn.commit()
return self.c.lastrowid
def delete_id(self, id):
self.c.execute("DELETE FROM entries WHERE id=?", [id])
self.conn.commit()
return self.c.lastrowid
def delete_all(self):
self.c.execute("DROP TABLE entries")
self.conn.commit()
def close(self):
self.conn.close()