thegu / fin-ally Goto Github PK

A wiki page should be created to outline basic code style guidelines.

The reason I raise this issue is that the initial files have a mixture of
tabs and spaces.

When I launch FINally.py, a database is created, but it has the name
SQLiteExpenses.db.  The instructions don't mention this.  

Running SQLiteDrivers.py does not seem to create a new database at all.

To support troubleshooting, and because it's convenient, I'd like to display 
the database version and the tool version somewhere in the GUI frame. I would 
prefer in a Windows Explorer-like "task bar" at the bottom of the GUI, below 
the "New Expense" button.

Maybe something like:

app: v1.0, db: v2.0

Create a README file that describes installation and getting started 
processes. This should be checked into the source code, possibly in a 
BASE/DOCS folder.

The New Expense dialog is incredibly ugly and needs a coat of polish :) 
Investigate using wxGlade to generate the dialog class and stitching in the 
necessary components. Store the wxGlade model and the generated code in a 
directory under revision control.

Currently, the tool is built to display expenses from the current month only. 
The tool should be modified to allow the user to specify a start month and a 
month range, which will be applied to the data pull from the database. 
Additionally, these filter variables should be available to all modules in the 
tool - they will be useful for graphics and statistics pages.

I assume that the code that was loaded to the repo is stable.  A tag of
this version should be made to establish the baseline.

A method for creating new database entries exists in database.py, including an 
example in the sample "main()" section of database.py. This method requires as 
an input a set of Elixir class instances representing each pertinent table. 

The entry functionality will eventually be integrated into a wxPython dialogue 
that will be accessible from the Graphics page. An even simpler form of this 
will be to integrate some text/number entry fields into the "button panel" at 
the bottom of the Graphics page. This will allow us to test out how the data 
transfer works from some form (dialogue or fields) to the database.

the steps in this issue are:

(1) create a set of Elixir classes as members of the Graphics page object. 
These classes will be populated when a user enters data into the dialogue or 
text forms and will eventually be pushed into the database layer.
(2) create the set of text/number entry controls. The entry controls should be: 
user (pull down), type (pull down), amount (numerical), date(date), and 
description(text field). Initially, user and type should be pull-down menus 
with pre-existing values. Down the road, we will expand this to allow the 
creation of new users/types.
(3) bind the classes and the text fields together 
(4) create a "done" button, which pushes data from the local classes into the 
database
(5) create a database method to accept a complete set of Elixir class 
instances. Initially this method will need to accept a reference to a User and 
Type object (since we will not be creating new objects yet) and a completed 
Expense object. It will compile them together and use the appropriate 
Elixir/SQLAlchemy calls to get them into the database. 

When the grid is initialized, all existing rows have custom editors applied to 
them. For example, any row containing a number will use the 
GridCellFloatEditor, which prevent users from typing letters into those grid 
elements when editing. The other prominent editors is for combo boxes and 
supports things like User of ExpenseType. This works fine and is contained 
primarily in the method GraphicsGrid.InitialTableFormat().

However, when new rows are entered using the expense entry fields, these rows 
are added to the table without the custom editors, which is bad. We cannot just 
re-call InitialTableFormat() because wxPython doesn't allow re-initialization 
of certain editor objects (ie: instance variables userChoiceEditor and 
typeChoiceEditor)

Each file loaded to the repository should include a file header that
indicates the copyright owner and what license applies.

The attached file shows an example of GPL licensing in a different
open-source project.

Grid-based editing of Users/ExpenseTypes would be nice. During this process, we 
must search the database and modify all Expenses with that ExpenseType before 
making the database change. For example:

(1) create new User in database
(2) search database, replacing all instances of old user with new user
(3) remove old User from database

The User/ExpenseType edit page should prevent the user from entering a 
duplicate User/ExpenseType.

We need to create a database object to store things like database name, size, 
and potentially things like table formatting issues, etc.. 

Initially this object should contain meta-data about the database we're using, 
as opposed to data from the database itself.

I noticed this while looking at fileCheck.py (a utility script for locating a 
.db file). The function SetupInitialDatabase( ) needs to identify the database 
name, but once it does so it does not place this information into an object for 
future use. 

It does push the database name into a genericExpense object, which is good, but 
not what we're after. 

editing the date column in the grid causes a strange formatting result, in 
which the time shows up with all zeros.

The Issues tab does not contain a "Pending" state which will allow any
project member to make an issue as resolved, without actually closing it.

Dan should customize the entire Issues tab so that it meets the needs of
the project.

Editing the date cell of an expense in the grid triggers a call to the datetime 
function strptime, which tries to parse the date string the user entered. For 
example, if the user enters "1/2/2010", this gets passed to strptime and will 
be turned into a datetime timestamp and pushed into the database.

The mechanism must be coded so that the user can enter a date in one several 
standard formats. For example 1/2/2010, 1-2-2010, 1.2.2010, Jan 2, 2010, etc... 
If the user enters a string that does not match anything, a default date should 
be applied (perhaps 1/1/1).

The date and time columns of the grid component can be entered using a calendar 
control, and they can be edited by hand-editing the datetime string that is 
displayed. We want to explore the option of adding a "pop up" calendar element 
to the grid. This would probably be a custom editor that would lay hidden until 
the operator triggered an edit event on one of these entries, and then it would 
pop up and allow calendar based entry. 

SQLite_drivers.py successfully creates a starter database when necessary,
but this database is called "dummy.db". Fin-ally is expecting the database
to have the name "FINally_data.db". A more cohesive design will be required
to resolve this. 

The preferences dialog is unlabeled and ugly. Fix it. Use wxGlade if necessary 
- storing the model and generated code in the /wxGlade directory. 

The preference table should be expanded to include the GUI and column sizes. 
These should be stored during closure of the tool and they should store the 
current values. For example, if the GUI is expanded to size (900,850), these 
sizes should be stored in the Preferences and applies on startup.

The grid columns should be 'click-sortable', meaning clicking on the column 
header should re-load the underlying data in ascending or descending order 
based on the column clicked. This should be a matter of re-loading the grid's 
table base using an Elixir/SQLAlchemy sort command as per the Elixir tutorial 
instead of just grabbing all data sorted by the primary key.

The delete button in the grid view doesn't currently require any sort of 
confirmation - it just removes the object. A Dialogue box should pop up asking 
for such confirmation as deletes are not so common.

Version 2.0 of the database contains a Preferences table, which stores user 
preferences such as default User, default ExpenseType, default Expense 
description and amount, etc... As more analysis and graphics content is added, 
this table will be expanded to include more information. This issue is to 
create 'R', and 'U' of the CRUD. Preferences cannot be deleted or created, but 
they can be updated and displayed.

We want preferences to be update-able in two ways: (1) explicitly from a menu 
and (2) automatically when critical variables are changed by the user. 

* An example of (1) would be to allow the user to explicitly configure the 
default Expense description value (ie: "Pants from Express"). 

* An example of (2) would be (assuming the graphics page has a graph of some 
sort) to update the date range of the graph automatically when the user changes 
it. This would be stored in the db automatically when modified so the user 
doesn't have to reset these parameters.

The menu will require a new menu bar, and the auto-expenses might have to wait 
until we have a graphics/analysis page.

Add a menu bar based on the FlatMenu in the wxPython AUI package. Look into the 
wxPython demo for an example.

Attached is a simple example that mimics some of the content we want for 
FINally.

During initial fin-ally startup, or whenever a database file is not
present, the SQLite_drivers.py file is supposed to create a blank dummy
database for the tool to operate on.

It appears the this functionality does not work, giving an error message
that only 4 arguments are presented, but five are required. This must be
resolved.

As per issue 46, a date-based filter has been added, but it does not currently 
handle the years correctly. There are many different ways to implement this in 
terms of the date controls, and they need to be evaluated. The simplest method 
is probably to add a year drop-down selector, but this isn't very elegant.

(ideally, a google finance-esque slider that encompasses both start date and 
range would be used - but this doesn't exist in wxPython, and it is non-trivial 
to implement)

While it's current possible to edit dates from the grid perspective, you have 
to enter a date in the specific format of the Python datetime object, which is 
unnecessary because:

(1) it's a very unnatural format to type
(2) we don't even use the time portions of the format

The operator should be able to enter a date in any of a set of "standard 
formats" (mm/dd/yyyy, mm/dd/yy, mmm dd, yyyy, etc...) and the tool should be 
able to convert that into a datestamp and push it into the database.

This will involve:

(1) a regex to parse the input string and determine if it matches an understood 
format.
(2) use of a Python function to convert from string to datestamp. This is 
already used in the FINally code (datetime.strptime(...)). The string format 
for this function will be determined by the particulars of the regex match. For 
example, if the regex matches yy-mm-dd then the input to the strptime function 
will need to match (%Y-%m-%d).

Change the repo from SVN to Mercurial.  You know you want to.

Google even posted some helpful instructions:
http://code.google.com/p/support/wiki/ConvertingSvnToHg

we need to be able to delete data entries. Here are a few ways:

(1) adding a "button" to the grid rows that will allow deletion.
(2) adding an "icon" to the grid rows that will allow deletion. This is similar 
to #1 but instead of a button it would probably just be a click sensitive cell 
with an icon inside.
(3) add checkboxes to each grid row and have a master delete button

Pick one and implement. Go!

Alphabetize the User/ExpenseType lists so it's easy to find things

As it currently stands, there are two hard-coded "users" for this tool: 
Rachel and Daniel (duh). This is probably not going to work for people 
that are not named Rachel or Daniel. The functionality of this component 
should be as follows:

1) In the entry page, the user should be able to type his or her name as 
a "new user"
2) When a name is entered, it is checked against a table in the database, 
if it does not already exist, it is added as a stored user.
3) In the entry page, the user should be given the option to select any 
stored user from a pull down menu and commit expenses under that name
4) This functionality should also be ported to the Import page

As additional functionality, if the user begins typing a previously stored 
name, Fin-ally should attempt to auto-complete that name.

Trying to edit the ID column throws an error. This column should not be 
editable. Additionally, it appears to "lock" the database. This can be 
seen if a delete is attempted after trying to edit the ID column.

The grid update methods (triggered when a grid is re-sized, rows/cols are 
added/deleted, or cell data is edited) needs to be cleaned and probably 
re-done. Some trigger instances work fine while other cause tool in-stability 
or undesired update behavior.

<add more here>

These methods do not appear in formal documentation, but there are several help 
threads covering them:

http://groups.google.com/group/wxpython-users/msg/f02fb345ef0b35cf

because there doens't appear to be a way to display certain buttons for certain 
notebook views, I want to change the notebook view to match the button that is 
pressed. For example, clicking "New Expense" should move you to the Grid view.

The Fin-ally Software Design Document needs to be brought up to date
because as of the initial checking it does not match the source code.

The "Create" portion of the CRUD app appears to be functioning, but now we want 
to edit existing data. The medium for data editing will initially be the grid 
itself. Modification of a value in the grid should feed back into the database. 
This will involve:

(1) include the primary key IDs in the grid as hidden data. This will allow us 
to look up a database record based on the primary key when the user clicks on a 
row/cell
(2) create an "on edit" binding that allows the grid to capture edited data and 
get it ready for a database update
(3) create a "SetData" or "EditData" property in database.py. 

The line between #2 and #3 can be blurry - figure out how it best fits into the 
existing codebase

We need to integrate a new object and set of methods to handle basic 
expense management within Fin-ally. This object should handle basic 
add/delete/edit operations for expense entries. It should be written as 
two classes:

1) generic expense operations
2) SQLite specific operations

These seperate classes will allow us to easily modify the expense object 
to handle other persistence methods. 

Integrating this object will allow us to strengthen the application and 
better utilize OOP principals.

There are several Object Relational Mapper (ORM) frameworks out there to 
simplify development of applications with a database back end.

At their best, the ORMs eliminate the need for raw SQL in applications and 
provide an abstraction layer that allows apps to run on top of any database 
engine.

There are issues with their use however:
  1) An abstraction layer means that powerful queries may be more difficult.
  2) An abstraction layer typically means worse performance.

SQLAlchemy (http://www.sqlalchemy.org) is one such ORM.  Let's see how it works.

session.commit() seems to work once per launch of the FA tool. That is, if some 
data is pushed into an object and then commit() is called, the data will be 
displayed in the grid. Presumably this means that the data has actually made it 
into the database - but this may not be accurate. If new data is pushed into 
the objects and they are committed - the old data seems to be replaced. Closing 
and re-opening the tool reveals that only the latest set of data is present.

Elixir creates a default session for you to use - but you can override that by 
creating a "regular" session just like SQLAlchemy. This can be seen here:

http://elixir.ematia.de/trac/browser/elixir/trunk/tests/test_options.py

One idea is to try and create a session, explicitly add some objects to it, 
commit the session, and then close the session. This would take some of the 
control out of Elixir's hands.

The import function is currently written to accept a single type of file, 
a massaged variant on a Quicken .qif file. This variant is created using a 
Perl script (becuase regexes (regexi?) rock in Perl), but this needs to be 
converted to Python. 

A module to convert from Quicken .qif files to the attached .txt data 
format should be written in a seperate Python file. Eventually this will 
be one of many conversion script - each accepting a different type of 
finance data file (Microsoft Money, Quicken, etc..) Conversion scripts 
could be in seperate files, or in a common file.

Module integration will be tracked in another issue.

Occurs during startup - probably something happening with the Version value in 
the database. The tool continues to run after this error.

Traceback (most recent call last):
  File "migrate.py", line 117, in <module>
    v = session.query(Version).one()
  File "C:\Python26\lib\site-packages\sqlalchemy-0.6.3-py2.6.egg\sqlalchemy\orm\
query.py", line 1528, in one
    raise orm_exc.NoResultFound("No row was found for one()")
sqlalchemy.orm.exc.NoResultFound: No row was found for one()

The search mechanism only supports searching the description field, but it 
should also support searching the expenseType field. This could be done with 
regular text, or it could be done with a drop-down control.

The benefit of regular text matching would be to allow the user to search on 
multiple types, whereas the drop-down would be a single type.

The default icon is boring and makes the app look unfinished - let's add an 
icon so the app looks more "finished", even thought it's completely unfinished.

Currently, clicking on a grid column applies a sort term to the database 
content - as described in issue 29. The sort term is basically alphabetically 
ascending, numerically ascending, or ascending by date. Clicking again on the 
column should invert the sort and making it descending alphabetically, 
numerically, or by date.

Also, it would be nice to integrate an image into the header that shows which 
column is being sorted and in which direction.

In order to facilitate SQL table changes once the tool is 'in the wild' A 
version number table must be added to the SQLite database. This table will 
contain a version number identified the set of tables in the database and there 
will be a single instance of this table created in the database. 

For example, the original database layout (User, ExpenseType, and Expense 
tables) will cause the Version table instance to be set to (say) 1.0.0. If the 
User layout is updated to include (say) user age, the single Version table 
instance will be updated to 1.1.0. If the ExpenseType table is updated to 
include/modify a field, the Version table instance may be raised to 1.2.0.

The tool's init sequence will also have to change. The current init sequence is:

(1) determine if a *.db file is present in the current directory
(2) prompt operator and create a new file if necessary
(3) load all data from *.db and display in grid
(4) wait for operator interaction

the new sequence will need to be:

(1) determine if a*.db file is present in the current dir
(2) prompt operator and create a new file if necessary
(3) read version from database file
(4) use version compatibility dict in application to determine if migration is 
required
(5) execute applicable migration routine as stored in migrate.py
(6) load all data from *.db and display in grid
(7) wait for operator insteraction

The application will be asked to add two new features:

(1) a version compatibility dict as described above. The keys will be the 
FINally version numbers and the values will be the desired database level
(2) a mechanism to dump the appropriate database tables, convert them into the 
correct format, and then dump them into a new database. This method should 
store the *.db as a backup copy before it perform any actions on the *.db to 
prevent any sort of accident. Also, the version in the database should be the 
last thing updated. 

Grid columns should be sortable by clicking on the column header.

The data containing table that behind the grid object does not update when new 
data is pushed into the database. This result in the following behavior: (1) 
FINally is launched and the table base gets whatever data is in the database, 
(2) the grid takes this data and displays it, (3) the operator adds a new 
expense to the database, (4) the grid is not updated and retains the original 
data.

The desired behavior is to refresh the grid - but this is slightly more complex 
that it seems. From a thread in the wxPython mailing list the following should 
work:

(1) load the self.localData component of the table base (CustomDataTable class) 
with new data. This might be something like self.grid.tableBase.localData = data
(2) create a grid method that calls the two method in the attached py file. 
ResetView handles the addition or removal of any rows/cols, and UpdateValues 
ensure that the grid has the latest values for all rows/cols

The Fin-ally project code-base needs to be uploaded to GoogleCode and
project members need to be added. Additionally, issue tracking should be
explored.

As described in issue 29, the database contains five sort terms, to be applied 
in order. Currently only the first sort term is applied, using the order_by( ) 
SQLAlchemy operation.

There are several Object Relational Mapper (ORM) frameworks out there to 
simplify development of applications with a database back end.

At their best, the ORMs eliminate the need for raw SQL in applications and 
provide an abstraction layer that allows apps to run on top of any database 
engine.

There are issues with their use however:
  1) An abstraction layer means that powerful queries may be more difficult.
  2) An abstraction layer typically means worse performance.

SQLObject (http://www.sqlobject.org) is one such ORM.  Let's see how it works.