The last article did introduce SqlAlchemy as a potential tool for developing Audacious.
I'm still looking at Sql Alchemy YouTube video... and I must admit that it looks a great tool.
In the meanwhile, I was also concerned by the PostgresSql database setup and upgrade with SqlAlchemy.
A software developpement live cycle imply regular database update/upgrade in both development and production environment where schema (and data) could be upgraded to the latest version.
Home Made Software
Until know, I was using an Home Made tool that query the database to extract the current schema version number (something written by the home made Upgrade software) and then execute all the update statement over that version. Each update executed one after the other.
This is simple and efficient... you only update your database with this tools (even in developement environnement) and you can be ensured that changes would also been applied smoothly into the production database.
The update files contains a series of SQL statements that are all identified by a unique and increasing number.
Example of db-update.dat [001.001] type=sql descr=first sql to create the first table content= ...sql..statement...here...
[001.002] type=sql descr=add user table content= ....other...sql...statement....
Home Made Software: the limitations
This approach rocks and is reliable. However, it is tuned for only one target database server (eg: either PostgreSql, either MsSqlServer, either ...).
It is still possible to maintain one file per kind of database (or limit ourself to only one database kind).
However, one of the Advandages of SqlAlchemy is to easlisy switch from one database to the other.
So, the best would be to consider database upgrading with SqlAlchemy himself... the response is available in the project Alchemy-migrate
See the
sqlalchemy-migrate-doc-0.7.2.pdf document.This document properly explain how SqlAlchemy-migrate works and how to use it... the principles are similars to the Home Made approach and allows upgrade/downgrade the database with SqlAlchemy code or with SQL Statements.
That's pretty nice... maybe a bit cumbersome to many newcomer but definitively the path to follow when we need to control and tame the upgrade process of the database.
Alternative: Alembic I did also read some thread about Alembic.
This manage revision of schema as revision of source file. It then store the differences between revisions to be reapply them on other database (eg: your production database).
With this approach the user modifies its database with tools... and the Alembic software tries to figure out the changes in the schema.Those changes are collected and registered to be re-applied later on the production database.
If this approach is a good idea, it don't really like it because it do the job "at best" with the changes you applied in your database.
If you accidently drop an unrelated table or rename a field (or table) during your test/development phase, this will also been applied to the production database.
That accidental operation not identified during developement may kill your production setup (because of the number of users using it).
So, I do prefer write the changes up-front. When I do this, it is clear in my mind that changes will be applied to development environnement but also in production environment. So, even if the operation is a bit more cumbersome, you always think twice before doing it. And thinking twice is better when creating softwares that manage "money".
There are many tools and professional tools to document the database.
But I would like the documentation to be as accessible and simple as possible... so avoid the usage of tool is best to offer an easy access to the information.
Regarding database documentation, here some nice recommendation collected on the Net
For my money, some good human-readable documentation (perhaps supplemented with diagrams of smaller portions of the system) will give you the most mileage. This will include, for each table:
* Descriptions of what the table means and how it's functionally used (in the UI, etc.) * Descriptions of what each attribute means, if it isn't obvious * Explanations of the relationships (foreign keys) from this table to others, and vice-versa * Explanations of additional constraints and / or triggers * Additional explanation of major views & procs that touch the table, if they're not well documented already
With all of the above, don't document for the sake of documenting - documentation that restates the obvious just gets in people's way. Instead, focus on the stuff that confused you at first, and spend a few minutes writing really clear, concise explanations. That'll help you think it through, and it'll massively help other developers who run into these tables for the first time.
Tooling or not tooling
One of the most recommended approach (after use of Tools) is to use the database feature allowing to include comment within the database schema.
If this is efficient and probably one of my favourite, this approach requires the user to know this feature to access the information.
The usage of tools also requires the user to have this tools prior to access the information.
So usage of tools is perhaps not the best approach for the audacious project which have to be open-source and KISS (keep is simple stupid) even for the technical documentation
Word processing document
Another approach than I used to document project is the usage of a word processing document.
I usually reverse engineering databases and maintain a Word documentation with database structure and all the useful information about it.
The problem is that "Word" is a non-open-source tools! If I do use Libre-Office/Open-Office, the Windows user would have trouble to open-it.
An option would be to publish a PDF version on regular base... but that's not very efficient, and still include binary file (Word or Libre-Office) within the sources of Audacious project.
A Simple text base file
That would be definitively a good option. Not specific software needed, easily available, can be included within the source code.
As issue we have:
No document indexes
No images inclusion (sometime quite useful).
A Wiki?
A wiki is not my prefered approach. It requires to be stored somewhere on Internet and that someone pay for it. No money = No service = No wiki = No documentation.
If the documentation could be organized and modified more easily (and more nicely), it would also been more difficult to consolidate the information to create a PDF/Word like document.
On the other hand, the "Audacious User Guide" should also been available... a Wiki is the best tool to build and organize such documentation/information.
So why not integrate developer (and database) documentation within the same wiki?
The back-end database choice is clear: that will be PostgreSql.
But a question remain... how will we access the data from the application?
Client-Server Model
One of the option is to use a database access library like PsycoPG2 to access the database.
By doing so, we are going to use a Client-Server model. PsycoPG2 does fully implement the Python DB API 2.0 specification.
The advantage is the simplicity of the approah, fews layers between the code and the database means that the code thighly control what's happening within the database. There is a better control on what's happen in the code.
Object Relational Mapping
On the other side, using an ORM approche allows you to concentrate efforts on the Business logic without worry (or lesser) about how to access/extract the data from the database.
Don't need to write your own in-memory data storage layer, lists, sorting, etc... its already done by the ORM.
One very popular an robust tools in Python is SQLAlchemy which also provide ORM support.
I don't know if this would work like a charm on Windows or Raspberry-Pi.
Any options simplifying the development tasks should always be taken in consideration.
Python SQLAlchemy Tutorial - on the Python Central website
Author: Xiaonuo Gantan
A series of beginner-focused SQLAlchemy tutorials covering a wide range
of basic topics. While a lot of the information here is derived from
the main documentation, the pace is slower and there are also details
culled from other sources, including performance tips, comparison to other
ORMs, and design philosophies. A very good effort by author Xiaonuo Gantan.
Hi everybody,
This blog will be written english because it is intended to a larger audiance.
Please excuse the mistyping and error, english is not my mother tongue.
Clever view... nice music
Every important act/decision should be taken with calm and with a clear view of paths and ways to follow.
For the first time, I will propose you to read this article in music... let's discover Sungha Jung.
Missing open-source software
When starting the activities of MCHobby (see shop.mchobby.be) we decided to work open-source/open-hardware material and to only use open-source software to run the company.
We are open-source addicted!
We noticed 2 importants leaks in our needs for small to medium company :
There is no commercial management software for small company's activities
There is no accounting software to our local Belgian regulation (Audacious will not handle this now)
Yes, there is some massive option like "OpenErp" renamed Odoo but can you figure out the energy to inject in such "software" when starting a commercial company with one or two person. As we said in french, it is an "artillery cannon" to kill a fly!
Do you have such kind of energy to inject before starting your sales ( inventory management, orders, invoicing, commercial offer, etc) ?
Well documented for users and that should be efficient but simple enough to be handled and understanded by only one person (you!, the initial boss of your company).
Which is fully open-sourced... any developer should be able to dig into the source code and adapt or extend the software to it's needs. Developer documentation should exists!
Which should be robust and bulletproof and build from a robust experience
Should be suitable for small company (one person) to medium size company (10 persons).
Build with simple principle... things that everybody should be able to understand. Keep It Simple Stupid! No need for software that is supposed to be clever than you!
Should allow you to do the best OR the worste BUT YOU CAN DO!
Should be able to control it only with the keyboard! You'd like the mouse? that's nice, but coding undred of orders without the need of the mouse is more than confortable and incredibly efficient (that's rock the life)
Stock... an existing software going to open-source rewrite
The software already exists. It's name is "stock" and he has 20 years old!
3 Years ago, I did contacted the initial developer wich provides us with the source code. This software was written in... Clipper.
It has been recompiler with Harbour Project (open-source compiler) and we manage the it to support PostgreSql database. The "Stock" software did knew many releases this last 3 years, and
now it supports amazing features like "Catalog Photo Shooting +
integration" within the system.
Scope of the software
Nowadays, this software is able to consistently handle the needs of a company having an annual turn-over from 2.000.000 Eur to 5.000.000 Eur, 15.000 articles references, 3.000 articles within paper catalogue, about +120.000 items in stock, from 50 to 60 orders a day (about 15.000 orders/year), +5000 shipping by year, etc. Company running from "single man" to 10 peoples.
Distributor centric vs E-commerce centric
There are still many mid-range distributors companies that doesn't need yet to evolve to Internet sales, electronic B2B and all those advanced business promises.
Distributor activities doesn't relies upon "E-Commerce software" but on "operational management software", the root of the business is the inventory... E-Commerce may be an extension of your business. Keep in mind that E-Commerce activities are not exactly managed in the same way and with the same rules than distributor activities. The e-commerce customers and their expectation are not the same... and then the way you manage the overall process is different.
e-commerce centric : pick-up center where the staff runs to prepare as many orders as possible. Source: TheNextWeb.com
Lot of nowadays software are e-commerce centric and not necessarily "adapted" to inventory centric business. This usually results in:
Unefficient internal business process. If internal process must be changes or largely adapted to support the software... then the software choice is WORNG. Software is there to help your activity, activity which has been tuned to be efficient in working and satisfy the your customer. As a result, the software should meet your expectation... instead of asking you to modify your way of working to meet the software expectation.
Unefficient order handling (many of the orders comes by e-mail and phone, someone have to encode it)
Waste of time - waiting for the software or using the mouse is a waste of time. Background worker only need a keyboard and KISS (keep It Simple Stupid) software.
There are place where customer support/servicing is more important than E-Commerce sales/internet based relation-ship... etc.
The software is there to help you to be productive and the best is to help you with simple but robust principles.
What are the features of existing STOCK software:
Product management (with PAMP, Margin, inventory, catalog information and product picture shoot, sales stats, stock variation, product familly, inventory localisation, ean generation for internal purpose, etc)
Sales order management (Back Order, customer, headquater and third party invoicing, send notes with integrated barcode and sorten by article localisation in the warehouse, inventory localisation, back-order identification)
Discount/rebate configuration and calculation based on product line (familly of product), customer, product,
Invoicing module (with integrated barcode and storage of payment due date)
Shipping management (identification of preparation and shipping, with simple barcode driven software)
Inventory and article localisation software.
Excel export
PDF Catalog compilation (chapters, product grouping in chapters, support for supporting several tabular layouts, customization within excel file, generation of PDF file via Publisher Merge operation)
Archiving of Send notes, invoices and pictures (within the file-system... simple but incredibly efficient)
Many reporting...
This software does have some leaks that Audacious will address:
Access security to various modules and data. We will follow the principles used for Linux Operating System, those principles works for the OS... so this should also works for software!
Support for many packing of a product (at the piece with EAN, in box with quantity and different ean, ... ).
Inventory Centric
Audacious will not be a Swiss Knife software attempting to do everything (but not necessarily doing it well).
Audacious will be an inventory centric software. Maybe doing less things but doing it well as it rocks nowadays.
Royality free - Licence avoidance
The aim of the Audacious project is provide an open-source solution where it would be possible to work without Licence Fee if you want to.
Most companies are using Windows, so Audacious will work on Windows and support Excel for export (PDF catalog generation would certainly be reworked).
With this approach, software licencing cannot be avoided but Audacious himself will be royality free.
But I'm also targeting Linux environnment (ex: Linux Mint or Ubuntu) and smaller computer systems like Raspberry-Pi (for the software, not the database) or more powerfull embedded computer boxes.
Supported Material
Based on the experience of the current running software, we will used affordable, robust et easily accessible products. It is always possible to find cheaper products but this as also a cost to manage compatibility issue or defectuosity....
I would recommand standard and approuved equipement:
Computer: most of current computer should work. Windows or Debian dérivated (Linux Mint or Ubuntu). The computer must be able to run the Database Serveur Software if not installed on separate server.
17 Inch screen with 1024 * 768 minimal résolution. 15 inch screens are less and less used, most of nowadays screen goes from 19 to 23 inch with 1920 x 1080 résolution.
Zebra LP 2824 Plus (USB) and Zebra GK420t (USB) label printer supporting ZPL langage. Using ZSelect 2000d thermal labels allows you to gain a lot of labelling time. Zebra are wonderful, professional and super efficient printer. The price (about 200 to 300 Eur) was an issue compared to other cheaper models (50 Eur). But once you have tested a Zebra you don't want anymore something else :-)
Datalogic QuickScan Mobile (optional) Wired via USB, this HID peripherals act as a keyboard and sends EAN to your application. This material works pretty fine and is very stable!
All of that would be enough to run a inventory centric company from 1 to 10 persons (you just need to have several computer, when using more than 5 user on the database, I would recommand to use a dedicated server for the database server and file storage.
Not so bad, isn't it?
Software components and development
Until here, I did speak of the software expectation but how we it be developped?
There is no need of complex environnement to create great software.
Here what will be the core of developments:
Python 3 - Programming language This language is very popular, performant and verstatile. It is build with simple concept and allows you to create really complex feature. It starts within the second and have a very high learning curve. Python did become really popular since Raspberry-Pi did choose it as main learning language. Google use this langage for its core processing... so it will also be good for Audacious Project. Python 3 is also Pure Unicode and you do not need a development environment to work with (a simple text editor would do the job!).
TkInter - Graphical interface Python includes a standard graphical interface named TkInter. Even if it isn't the most beautifull, you will be able to create advanced interface and additional graphical components. See Python and Tkinter book from John E Grayson at Manning edition.
Psyco2PG - Python library to deal with PostgreSql database This library supporting Python 3 (in unicode) would allow you to deal with the Powerfull PostgreSql database.
PostgreSql - database engine PostgresSql is one of the most powerful robust and flexible open-source database server. We are currently using it since 2 years with our old software and that rocks. So, when considering a new entreprise software, the best would be to use a robust database engine... PostgresSql is the best suited in this case. It can be run on one of the computer or a server. Rocks on Windows as on Linux.
Why "Audacious"?
Rewrite a company software from scratch,
Writing it in Python,
Making it open-source and freely accessible (the GNU GPL license foreseen),
Making it accessible for developer (published on GitHub),
Providing developper and database documentation,
Giving you all the access on your data and the code,
Making it reliable and oriented toward the final user (those who encode the things),
Thinking efficient (keyboard usage) before shiny & dazzling (lustrated interface and mouse usage),
Providing user documentation and good practice.
Being able to support a Inventory Centric Business for FREE,
Supporting the functionalities roughly described here before,
Isn't it audacious?
Authorization
Hereby, I would like to render thanks to Mister JM Surinx (Belgium) which is the developer of original Stock software written in Clipper.
Mister Surinx did authorize the reuse of Stock software knowledge to spin-off the Audacious project.
