Saleor Products Data Model

I’m looking to replace our Magento Ecommerce Server with something “Nice” – Something that is not complicated, not over abstracted, something that is well … fun to work with.

Saleor is an elegant solution. It is an Ecommerce app written in Python and Django.  I was able to download it and get it up and running in about 2 hours.    That says a lot.

Now, I need to write a product upload script.  I could not find a diagram of the Product Schema, so I drew one.  I did this by looking at products/models/ and and the postgresql database tables.  It is open for comments and corrections.  Here it is:



Data is King, is KING!

Good Data makes life easy; If we model all our the business logic in the database then the application layer is trivial.

Case in point:  I’m implementing a User Access Module.   First I define a list of Access terms:  Things like dashboard (allow view dashboard),  orders (allow view orders report), download (allow CSV downloads), etc.

Then I come up with a list of Roles: CS Agent, and Executive, will do for now.

Then I create a role_access many-to-many join table with just id, role_id, and access_id.

Then I add a role_id to the users table.

Then I painfully craft all the static data in access, roles, and access_roles tables in csv files that gets loaded when the tables are created.

When that’s done it looks like this.


From there the work in the application layer is easy.    The caller simply looks like this

if self.user.has_access('download'):
    output += self._getDownloadCSVButton()

Implementation of this functionality is also trival.  We add the following two lightweight methods to the user model class:

class User(object):
    def access(self):
        '''Return users access list'''
        sql = '''
           select a.code as access
           from   users u
                  join role_access ra on ra.role_id = u.role_id
                  join access a on ra.access_id =
           where = %s'''
       results = self.db.query(sql, params=(,))
       return [r['access'] for r in results]

   def has_access(self, access_name):
       '''Return True or False if this user has the given access'''

       if access_name in self.access:
           return True
       return False

And that’s all there is to, it.  Express your business logic in the database and the rest follows easy.


Make is Simple

Complexity is only Simplicity x 1000 — A mathematician friend once told me that. The idea struck a chord with me and has stuck with me ever since.

Everything in this world is complex.   When we design computer systems our goals should be to encapsulate that complexity into simple business level concepts.  They in turn can be broken down into smaller and smaller simple systems – and then no part of the system will be complex on its own.

Here is an example.  Say you need to download and update pricing information on a regular bases.   The new process should simple be called ‘Update Prices’.

I – The process could have the following simple breakdown:

  • Update Prices
    • Download Pricing
    • Load New Pricing Data
    • Update Pricing Data

II  – The first one could further be broken down into

  • Download Pricing:
    •  Get External Resource URI from config
    • Get External Source Dir from config
    • Get Local Destination Dir, from config
    • SFTP Download files

III – The first one here could further be broken down into

  • Get External Resource URI from config
    • Create a Config Module that reads YAML config file
    • Create environment variable to set config file name
    • Provide getter methods for each config value
    • Create YAML config files, (one for dev, staging, and prod)

IV – The first one here again, can be further be boken down into

  • Create a Config Module that reads YAML config file
    • Load YAML parser
    • Set config filename = config.basedir + / + ENV(CONFIG)
    • yaml.parse(read filename)

And nothing in it is complicated. So break it down.  Make it simple!   Everyone will be happy.



Successful Computer Programmer

So we’re all computer programmers, but what makes you successful?   What makes you an excellent contributor to your team or company?

I discovered the importance of these traits little by little over the years.  They are progressively more important as you move down.

1. You’ve got to be smart,  that’s the first step –   Love math, enjoy implementing crazy algorithms, etc.  We’re all smart, or we wouldn’t be programming computers.

2. Be organized.  It is not enough that you can write code, you need to be organize.   This includes standard practices, documentation, and good habits.  This improves productivity many fold over the long haul.

3. Planning.   The more you can plan the more really hard stuff you can get done in ever shorter time periods.  Get the scoop directly from users, design, get sign off on everything, plan your time – and then bingo – just two days of coding, and you’re done.

4. People skills.   Finally to really become successful you need to develop strong collaborative relationships with everyone involved.   Programming computers is often the easy part.  Dealing with everyone else on the project requires real talent, and may take up more time than coding.

So, in a nutshell:  Be Smart …, Be Organized …, Plan …, and … last but not least, Be a People Person.   Have fun!




Safety Belts Off

When should we use secure practices and when not to?  Like all good advice secure practices need to be weighed against practicality.

In a perfect world your system will have full test coverage, automated testing, database migration scripts, automated deployments, adhere to common coding practices, have identical dev and test environments, as well as excellent documentation.

In that perfect world the best way to protect production systems is not to allow logins – and to do everything through automation.

But what if that is not the case?  What if you’re supporting a legacy system that is hacky, poorly designed – and the business is relying on it at the same time?

In this case your best line of defense is think creatively and have full access to all production systems – so you can react quickly when things go wrong.  If automation can not be relied upon – then its hands on test, tweak if you have to, retest, and commit.   Until you can replace the system with something more stable.

Sometimes you need to take your safety belt off, if only for a short time.

DB, Config and Logging

The Database, Config and Logging are at the core of all application development.  Everything starts there.

A Database System stores all of the applications data.  The storage itself can be anything from a loose collection of files like jpegs, a nosql db like mongodb,  or a full fledge relational database like mysql or postgresql.   This subsystem sits right below any Model Objects.

A Configuration System allows you to configure your application without changing code.  It primarily determines where things are, such as: subdirectories, external web services, database credentials, log files etc.    You will have a different configuration file for each type of environment: dev, stage, and prod

A Logging System should be built in from the word go.   All backend tasks should be logged.  One philosophy is that all events write a single line entry of Success or Failure.   Each event can optionally also write multiple Warning or Debug logging messages.  When done this way your logs are themselves databases of all system events.   You can can easily answer questions like how many transactions processed today, and how many of them failed.

These three subsystems are needed for all application development. Here is a link to a library I have written that provides these functions.  Which I reuse it all the time.

Python Application Development – Core Library Classes –



Always diff before you commit

If you don’t diff before you commit you don’t know what you’re committing.

When programming anything even remotely interesting, we’re making changes all the time.   Change, change, change.  Phase 1, phase 2,  experiment 1, experiment 2, etc.  And we’re using Version Control Systems, like git.  (If not, stop reading and learn git)

If you believe as I do, you should only commit working code, then we must safeguard our git repos from danger from ourselves.  That’s why you should always run git diff and scrutinize the changes before git commit, to make sure debug, merge conflicts, and other unwanted changes are not making into the big time.

It’s like look both ways before you cross.   Diff before you commit – make it a part of your stitch in time.

Less Is More

You know you’re doing a great job when, adding new functionality to a code base, and you wind up removing more code then you’ve added.  Less is More.

I once heard boast a programmer that he’d written million lines of C code.  That seemed undesirable to me.  Better you should boast that you’ve written an entire system in under 1,000 lines of code.

Doing more with less, requires good design and a lot of reusable code.   Doing more with less means you have less to maintain, and less that can go wrong.

So pride yourself on how small your code base is, not how large it is.

SQL Formatting

Let’s standardize how we write SQL.  What joy.   Rarely, have I seen consistency in how SQL is formatted, and often it is not even considered.

Here is the standard I use which makes it  easy to read both simple and complex queries.  Here is an example:

select                                       as book_id,
   b.title                                    ,
   group_concat(a.first_name, ' ', 
                a.last_name, separator ', ')  as authors,
   b.published                                ,
   books b
   left join book_authors ba on = ba.book_id
   left join authors a on ba.author_id = 
   b.published < '1900' and
   a.nationality = 'English'
group by
order by
   b.published desc,


  1. Lower case all keywords. See Why Shout in your SQL?
  2. Keywords that group sections – select, from, where, group by, having, order by, and limit should be on their own line with no leading spaces.
  3. Code between the keyword groupings should be indented
  4. Indentation should be 3 spaces.
  5. No line should exceed 79 characters
  6. All table names must have an alias, if more than one table is used
  7. All field names should be prefixed with an alias,  if more than one table is used
  8. Select, group by, and order by clauses should have each field on it’s own line, but may not if the list is small.
  9. Select fields should be broken up into multiple properly indented lines when long or complicated.  For example, when using functions like concat_ws, substring, etc.
  10. Select clauses may horizontally align ‘as’ names
  11. From clause should have each table on its own line
  12. From join clauses may want to have each join condition on its own line indented in, if they are long
  13. Where and having clauses should have each ‘and’ condition on its own line
  14. Conditions may horizontally align on =, <, <=, >, >=, !=
  15. Joining keywords on, and, and or should be never start a new line.
  16. Nested queries should be formated the same way with additional indentation necessary to line them up
  17. Semicolon should be on it’s own line with no leading spaces.

More Examples

Line up field ‘as’ names
   a.col              as name1
   a.long_column_name as name2,
   func(a.other)      as name3
Long join statements
   usage u
   left join usage_detail ud on
      u.usage_id = ud.usage_id and
      u.status_id <= 500 and
      (ud.trans_type = 'TD' or ud.position = 'R26')
The case statement
      when t.trans_type is null then
      when t.trans_type in (20, 50) then
         (-1) * qty * amount
          qty * amount
      end as total
select as order_id
   orders o
   left join (
         count(*) as orders
      ) ois on = ois.order_id
   o.order_date       >= '2015-02-01 and
   date(o.order_date) <  '2015-03-01
The insert statement

Same general rules apply

insert into audits
   (table_name, column_name, id, old_value, new_value)
   ('customers', 'status_id', 8056, 10, 200)
The update statement

Same general rules apply

   status_id = 500
   customer_id = 8011
Table creation scripts
set foreign_key_checks = 0;

-- drop table /*! if exists */ parties;

create table parties (
  party_id          integer unsigned  not null  auto_increment primary key ,
  party_type_id     integer unsigned  not null ,
  institution_id    integer unsigned  ,
  name              varchar(255)      ,
  first_name        varchar(255)      ,
  middle_name       varchar(255)      ,
  last_name         varchar(255)      ,
  username          varchar(255)      ,
  email             varchar(255)      ,
  phone             varchar(255)      ,
  phone2            varchar(255)      ,
  source            varchar(255)      ,
  comments          text              ,

  created           datetime          not null ,
  last_updated      timestamp         not null
        default current_timestamp on update current_timestamp ,

  unique key name_institution (name, party_type_id, institution_id),
  foreign key (party_type_id)  references party_types (party_type_id),
  foreign key (institution_id) references institutions (party_id)
engine InnoDB default charset=utf8;

show warnings;

set foreign_key_checks = 1;

desc parties;

Unix utility: sql_pretty

Here is a simple utility that will reformat SQL. It does most of it. It is a work in progress.  It uses regex but needs to do a full SQL parse to do everything: sql_pretty


These conventions have made my life easier. Easy to read code is good code is easy living. So go clean up your SQL scripts!  And have fun.

Unix House Keeping

Where is everything? Where do files go in Unix? There are no bonafide rules. But we’re free to invent some. So here are mine.

The Heap

/data – Everything starts off in this subdirectory. Consider it the Heap from which all other areas are carved out of. We’re using the term data in its most broadest sense.

/data/apps – Installed apps that don’t install themselves into the woodworks.

/data/backups – Back up files

/data/downloads – Downloaded files

/data/www – Website Root dir


We then symlink them to root to make them easier to get to

# ln -s /data/apps /apps
# ln -s /data/backups /backups
# ln -s /data/downloads /downloads
# ln -s /data/www /www

This gives:



/apps/crowfly – Production git master branch

/home/dlink/crowfly – Development version – git feature branches

/www/ –> /apps/crowfly/www – A symlink to the web portion or the public portion of a production application

/www/ –> /home/dlink/crowfly/www – A symlink to the web portion of a development branch.

/www/ –> /apps/purple-bat/www – A symlink to the web portion of an application that does not have its own domain name.   It lives over the parent domain.   The source code of the parent needs to list the symlink in its .gitignore file.


/backups/flower_images_20141203.tgz – A backup of images tarred and zipped – with timestamp in name.

/backups/db/books_20150220.dump.gz – A database dump, zipped – with timestamp in name.  These are created like this:

mysqldump -uuser -ppass -hhost db | gzip - > /backups/db/books/20150220.dump.gz

and they are recovered like this:

alias bookdb='mysql -uuser -ppass -hhost db'
echo 'create database books' | booksdb
gunzip -c /backups/db/books_20150220.dump.gz | booksdb


There is such a long tradition of placing logs in /var/log that we don’t  moved to /data.

Application Logs

/var/log/plant/plant.log – Production log
/var/log/plant/stage_plant.log – Staging Log
/var/log/plant/dev_plant.log – Development Log
/var/log/plant/dlink_plant.log – Alternative Development Log (using username)
/home/dlink/log/dev_plant.log – Alternative Development Log (using user homedir)

/var/log/plant/plant_traceback.log – Production Traceback Log
/var/log/plant/stage_plant_traceback.log – Development Traceback log

Web Logs

/var/log/apache2/ – Apache Log for site.
/var/log/apache2/ – Apache Error Log for site (periods (.) not dashes)


What’s nice about adopting a standard for where files go on all your systems (Your local mac included) is you can easily move files around.  Here is an examples.

cd /backups/db
scp$PWD/world_2014*.dump.gz .