tags: python net clipboard net-clipboard
title: Simple Network Clipboard using MariaDB

This is another LAN-wide clipboard. This time using a MariaDB server. Unlike NetworkClipboard, this
one is persistent, and maintains history for each clip name. See [NetworkClipbaord3](/lang/php/db/NetworkClipboard3) for a description
of a web version of this (basically an HTTP frontend using PHP rather than the Python you see here).

# Source
There are two tables.
```
MariaDB [clip]> describe pointers;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| namespace | varchar(256) | NO   | MUL | NULL    |                |
| name      | varchar(256) | NO   |     | NULL    |                |
| clipid    | int(11)      | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
4 rows in set (0.064 sec)

MariaDB [clip]> describe clips;
+-----------+--------------+------+-----+---------------------+-------------------------------+
| Field     | Type         | Null | Key | Default             | Extra                         |
+-----------+--------------+------+-----+---------------------+-------------------------------+
| id        | int(11)      | NO   | PRI | NULL                | auto_increment                |
| namespace | varchar(256) | NO   | MUL | NULL                |                               |
| name      | varchar(256) | NO   |     | NULL                |                               |
| value     | text         | NO   | MUL | NULL                |                               |
| time      | timestamp    | NO   |     | current_timestamp() | on update current_timestamp() |
+-----------+--------------+------+-----+---------------------+-------------------------------+
5 rows in set (0.247 sec)
```
so these are created with
```sql
CREATE TABLE `pointers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `namespace` varchar(256) NOT NULL,
  `name` varchar(256) NOT NULL,
  `clipid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `namespace` (`namespace`,`name`),
  KEY `clipids` (`namespace`,`name`)
);
CREATE TABLE `clips` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `namespace` varchar(256) NOT NULL,
  `name` varchar(256) NOT NULL,
  `value` text NOT NULL,
  `time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `clipindex` (`namespace`,`name`),
  FULLTEXT KEY `clipcontent` (`value`)
);
```

Credentials for MariaDB are in `clipdb.py`.

These are not all the scripts, just get, put, and list, but illustrate things.
Basically they are wrappers around a handful of SQL statements.
## cget
```py
#!/usr/bin/env python
import sys
import os
import mysql.connector
from icecream import ic; ic.configureOutput(includeContext=True)
from mysql.connector.errors import DatabaseError
from clipdb import HOST, USER, PASS, DB

args = sys.argv[1:]
def eprint(*xs,**kw):
  kw['file'] = sys.stderr
  print(*xs,**kw)

if len(args) == 0:
  eprint(f"{sys.argv[0]} <name> [<name>...]")
  exit(1)

def getname(arg):
  if ":" in arg:
    namespace, name = arg.split(":",1)
  else:
    name = arg
    namespace = os.getenv("CLIPNS","_")
  return (namespace,name)
  
try:
  mydb = mysql.connector.connect(host=HOST,user=USER,password=PASS,database=DB)
except DatabaseError as e:
  eprint(f"DatabaseError(connect): {e}",)
  exit(1)

sql = """SELECT pointers.clipid, clips.value, clips.time 
  FROM pointers JOIN clips 
  ON (pointers.clipid = clips.id) 
  WHERE (
    pointers.namespace = %s AND
    pointers.name = %s
  )"""

cursor = mydb.cursor()
error = False
for arg in args:
  namespace,name = getname(arg)
  cursor.execute(sql,(namespace,name,))
  a = cursor.fetchall()
  if len(a) > 0:
    clipid,value,cliptime = a[0]
    value = value.replace("\r","")
    if value[-1] != "\n":
      print(value)
    else:
      print(value,end="")
  else:
    error = True

exit(1 if error else 0)
```

## cput
```py
#!/usr/bin/env python
import sys
import os
import re
import mysql.connector
from icecream import ic; ic.configureOutput(includeContext=True)
from mysql.connector.errors import DatabaseError
from clipdb import HOST, USER, PASS, DB

def eprint(*xs,**kw):
  kw['file'] = sys.stderr
  print(*xs,**kw)

args = sys.argv[1:]
if len(args) == 0:
  eprint(f"{sys.argv[0]} <name> [content filenames]")
  eprint(f"filename - reads from stdin")
  exit(1)
name, *args = args
if ":" in name:
  namespace, name = name.split(":",1)
else:
  namespace = os.getenv("CLIPNS","_")

if not re.match(r"^[A-Za-z0-9_]+$",name):
  eprint(f"Invalid name: {name}")
  exit(1)
if not re.match(r"^[A-Za-z0-9_]+$",namespace):
  eprint(f"Invalid namespace: {namespace}")
  exit(1)

if len(args) == 0:
  args = ["-"]

stdin = None
contents = []
for arg in args:
  if arg == "-":
    if stdin is None:
      stdin = sys.stdin.read()
    contents.append(stdin)
  else:
    try:
      with open(arg) as f:
        contents.append(f.read())
    except Exception:
      eprint(f"#Fail {arg}")
      exit(1)

content = "\n\n".join(contents)
content = content.replace("\r","")
try:
  mydb = mysql.connector.connect(host=HOST,user=USER,password=PASS,database=DB)
except DatabaseError as e:
  eprint(f"DatabaseError(connect): {e}")
  exit(1)

def f(sql,values=None):
  #print(sql,values)
  if values is not None:
    cursor.execute(sql,values)
  else:
    cursor.execute(sql)
  #print("done")

cursor = mydb.cursor()
f("START TRANSACTION")
f("""INSERT INTO clips 
  (namespace,name,value) VALUES ( %s, %s, %s )""",
  (namespace,name,content))
f("""SET @a = (SELECT LAST_INSERT_ID())""")
f("""INSERT INTO pointers (namespace,name,clipid) 
  VALUES ( %s, %s, @a )
  ON DUPLICATE KEY UPDATE clipid=(@a)""",
  (namespace,name))
f("""COMMIT""")
mydb.commit()
```

## clist
```py
#!/usr/bin/env python

"""
list names in namespace matching regexes
"""

import sys
import os
import mysql.connector
import re
from icecream import ic; ic.configureOutput(includeContext=True)
from mysql.connector.errors import DatabaseError
from clipdb import HOST, USER, PASS, DB
try:
  from colors import color
except Exception:
  color = lambda t,*xs,**kw: t
if not sys.stdout.isatty():
  color = lambda x,*xs,**kw: x

def eprint(*xs,**kw):
  kw['file'] = sys.stderr
  print(*xs,**kw)

args = sys.argv[1:]
if len(args) == 0:
  eprint(f"{sys.argv[0]} <pattern> [<pattern>...]")
  exit(1)
  
def getname(arg):
  if ":" in arg:
    namespace, name = arg.split(":",1)
    if namespace == "":
      namespace = os.getenv("CLIPNS","_")
  else:
    name = arg
    namespace = os.getenv("CLIPNS","_")
  return (namespace,name)

try:
  mydb = mysql.connector.connect(host=HOST,user=USER,password=PASS,database=DB)
except DatabaseError as e:
  eprint(f"DatabaseError(connect): {e}")
  exit(1)

sql = "select id, name, time from history;"
sql1 = """SELECT id,name FROM pointers WHERE namespace = %s"""
sql2 = """SELECT id, time FROM clips
  WHERE (
    namespace = %s AND
    name = %s
  )"""
sql_getcount = """SELECT count(*) FROM clips
  WHERE (
    namespace = %s AND
    name = %s
  )"""

cursor = mydb.cursor()
error = False
found = []
ln = 0
lns = 0
maxn = 0
n = 0
namespaces = set()
for arg in args:
  ignorecase = False
  namespace, name = getname(arg)
  if m := re.match(r"(/|@)(.*)\1(i?)",name):
    ignorecase = m.group(3) == "i" 
    name = m.group(2)
  if ignorecase:
    r = re.compile(name,re.I)
  else:
    r = re.compile(name)

  cursor.execute(sql1,(namespace,))
  a = cursor.fetchall()
  a = [ x for x in a if r.search(x[1]) ]
  if len(a) > 0:
    namespaces.add(namespace)
    lns = max(lns,len(namespace))
  for x in a:
    clipid, name = x
    b = cursor.execute(sql_getcount,(namespace,name))
    clipcount = cursor.fetchall()[0][0]
    found.append((clipid,namespace,name,clipcount))
    ln = max(ln,len(x[1]))
    maxn = max(maxn,x[0])
for clipid,namespace,name,clipcount in sorted(found,key=lambda t: f"{namespace}:{name}"):
  nn = len(str(maxn))
  mn = ln-len(name)
  mns = lns-len(namespace)
  pn = " "*mn
  pns = " "*mns
  namec = color(name,fg="#ffff77")+pn
  namespacec = pns+color(namespace,fg="#ff7777")
  print(f"{clipid: >{nn}} {namespacec}:{namec} ({clipcount})")
```