Web Sheets

Current version: 0.1

A web-based python spreadsheet app.

Each book has multiple sheets a pre and post script. Each sheet has a 2D array of cells. Each cell contains a string of python code which is evaluated and the result is displayed. The pre script runs before cells are evaluated and the globals dictionary from the script is used in evaluating the cells. The post-script runs after cell evaluation and has access to cell values.

Documentation

GitHub

Deployment

pip3 install ws_web_aiohttp

# install aditional static files for web server
wget <link to static files>
tar -zxvf web_sheets_static.tar.gz
mkdir -p /var/www/web_sheets
mv web_sheets_static /var/www/web_sheets/static

# install systemd services
ws_storage install
ws_sheets_server install
ws_web_server install

By default, services will run as root.

Development

git clone git@github.com:chuck1/web_sheets
cd web_sheets
bash install_apt_pkg.bash
bash setup_venv.bash
bash test.bash

Also need to build handsontable:

cd handsontable
npm install
grunt --force

Other Setup

Postgres setup:

createuser websheets
createdb websheets
psql
ALTER USER "user_name" WITH PASSWORD 'new_password';

Philosophy

For now, the goal is to keep the ws_sheets module as small as possible. So features that would require additional code but could be implemented by the user will not be added. I will, however, provide as many examples of ideas for user-implemented features as possible.

Modules

sheets

Contains the data and methods for spreadsheets. The basic structure is shown in the following pseudocode.:

Book:
  sheets = dict of Sheet objects
  script_pre = Script object
  script_post = Script object

Sheet:
  cells = Cells object

Cells:
  cells = numpy.array of Cell objects

Cell:
  string = string which gets passed to eval()

Script:
  string = string which gets passed to exec()

The steps to recalculate the entire book are as follows

• Reset globals - Construct a new globals dict which contains

  • __builtins__: a dict which contains approved python builtin functions and custom implementations of certain other python builtin function
  • sheets: a dict with pairs of sheet keys and numpy arrays. the arrays contain the cell strings.

• Execute script_pre - script_pre is passed the globals object from above

• Evaluate cells - For cell evaluation, a shallow copy is made of the globals object from above.

  Therefore, the cell can alter non-trivial objects in the globals.

## sheets_backend

## web_sheets_django

The website itself is powered by Django. The sheet app handles the viewing and modification of spreadsheets.

# Accessing Sheet Data

sheet = sheets_backend.sockets.SheetProxy(sheet_id, port) sheet.set_cell(0, 0, 'hello') ret = sheet.get_sheet_data()

This code will only work if there is a sockets server running at the port specified and a sheet with id sheet_id exists.

# Production Server

• change folder and file permission to allow database write
• set WSGI python path for my custom modules
• remove references to my environment variables

Development

Install all module from source with:

pip3 install .

Using the -e option does not work because it does not properly handle subdirectory modules.

Process Layout

We will think about layout in terms of processes. Processes can be on the same machine or on opposite sides of the Earth, it makes no difference here. A plus sign will indicate one or more processes.

Simplest

The simplest setup has a single server with storage included.:

django+
 |
 V
Server(Storage)

Router

A server router can route connections from django processes to one of multiple server processes.:

django+
 |
 V
ServerRouter(Storage) --> Server+

Storage Server

We can take multiple instances of the simplest setup and move storage to a single storage server.:

___________
(         )
( django+ )
(  |      )
(  V      )
( Server  )
(_________)+
   |
   V
StorageServer

Migration

We need to make sure that when new versions are released, existing books still load properly from storage.

We will create a test case which will load all books from storage and possible test some of their functionality.

Testing

In the event that testing takes place on a machine that is also used for deployment, testing must utilize virtualenv and socket communication must take place on different ports.

For tests requiring a running sheets_backend Server, a server will be started using a port that is designated for testing.

Processes

Django

• source
• settings - wsgi.py and manage.py can load different settings files - settings reads secrets.json

Sheets Backend Server

• sheets_backend python module
• script file - installed to /usr/local/bin
• settings module - script loads from default location and has command line input for location
• storage location - if storage is included, setting should set storage location

Security

Core class access

We must protect the core objects while allowing the user to access certain methods (like __getitem__).

Class methods contain a __globals__ attribute with the context in which the function was created. If the user can call the method, they can also access this attribute. One possible solution is to use a pattern like this:

class Foo(object): pass

safe_func = create_func_object_with_custom_globals()

Foo.__getitem__ = safe_func

We must guarantee that safe_func is indeed safe, meaning that it is save for the user to have unrestricted access to it.

We can try to restrict access to member attributes by writing a custom __getattribute__ function. However, this is easily circumvented with code like object.__getattribute__(obj, 'name'). I am currently working on implementing my own code execution module that will allow me to detect situations like this.

Release Process

• Be on a development branch that is a descendant of the master branch.
• Run bash test.bash
• If you are not the maintainer, submit a pull request.

Maintainer

• Run bash tests.bash
• Follow steps above for deployment and development install and make sure they work.
• Edit VERSION.txt files to reflect desired release versions
• Run upload_pypi.bash

More Deployment Stuff

Untracked Config File

The following configuration files must be stored by the user and copied into the installation config directory.

• google_oauth.py
• web_sheets_postgresql.py