Python Scaffolding SQL tables

Scaffolding support in a framework is a rapid means of implementing crud, i.e. a means of maintaining the database on which we will base our application

Crud is an acronym for Create, read, update and delete, operations that may need to be applied to rows in a table.

Scrudsie could be an acronym adding sorting, selecting, importing and exporting.

If we map out a database for an application, or we are looking to access data that already exists, and have come across dozens or hundreds of table formats, scaffolding is a means of coding crud functions for a table, or even for all tables, in just a few lines of code.

Scaffolding should occur within an Auth framework, especially a utility crud function where any table could have records added, deleted or modified.

Additionally, scaffolding could use a template that defines the entire DOM or identified DIV elements within a DOM (DOM definition wikipedia w3c)

DOM, in this context, stands for Document Object Model, and refers to all the html from the initial doctype definition tag through the final ending tag. Simply “view source” and the chaos of header, body, javascript, meta, link, css, divs, tables, lists and such things is very well known and defined

Python Crud with MySQL

Let’s do the same thing with MySql

pip install MySQL-python
import MySQLdb

class mydbconn():
    def __init__(self):
        self.con = MySQLdb.connect('x', 'x', 'x', 'x')

    def get_columns(self,table):
        cuc = self.con.cursor()
        cursData = cuc.execute("SHOW columns FROM " + table)
        HEADERS = cuc.fetchall()
        keyName = HEADERS[0][0]
        keyLen = self.lenFunction(HEADERS[0][1])
        return keyName, keyLen, HEADERS

    def lenFunction(self,varCharString):
        x = varCharString.find("(")
        varCharString = varCharString[x+1:]
        x = varCharString.find(")")
        varCharString = varCharString[:x]
        return int(varCharString)

if __name__=="__main__":
    con = mydbconn()
    keyName, keyLen, h = con.get_columns("customer")
    for i in h:
        print i

On the fifth line of the program, use your MySql credentials. Sometimes the first parameter is localhost, the second x is the username for the database, the password is third and the name of the database is fourth.

You also have to change the parameter in line 2 of the __main__ section to the name of the table you are using. This routine also has been used with databases that had a key other than named id for the first field, and it was important to have the name of the key and its length to proceed with scaffolding

In the Sqlite3 sqlalchemy example, we got a list of field names. In this example, we get a tuple of tuples. At this point, tuples might be a preferred format, they are immutable and hashable, and a utility crud function would not need to add and remove things from the tuple. However, in a more sophisticated scaffolding environment, you would add things like a field for password verification or especially remove things, like the ability to change certain fields.

Open Source ERP and CRM

The entire Linux operating system including source code can be downloaded from the internet.

In the open source world the most famous piece of software is Linux.  The entire Linux operating system including source code can be downloaded from the internet.  The Apache web server that is responsible for serving up a substantial amount of internet web pages is normally layered on top of the Linux operating system.

Apache may be available for Windows, kind of like Eva Gabor riding in the back of a 1948 Dodge pickup truck, and that may be where the average user most often interacts with the open source side of software.  A further note to understand in the open source software world is the notion in free as in open vs free as in beer.  If you get a free beer you do not have to pay for it, but open source software usually requires a costly agreement with the consultants and engineers that have the knowledge to work with the complex code in an open source application.

Open Source ERP is not a free software concept.  These complex business databases could in theory be used without the computer scientists and engineers that know their inner workings, but the costs of acquiring and maintaining the highly educated workers reqiuired to do this is greater than the cost of a service agreement.

In the Linux market, two examples of open source knowledge suppliers are Redhat and Novell.  Some may argue that other examples inclide Sun and IBM.

Solutions

Manufacturing, Service, and Distribution Solutions for Business Applications.

MSInc provides services and consulting in all areas of small and medium size business computing.

Network and centralized database operations are exploited to handle accounting and operations resource planning and execution for manufacturers, distributors, and service companies.

Manufacturing, Service, and Distribution Solutions for Business Applications. Data recovery, exploit protection, on-line backup services, portals, secure login internet collaboration tools. Linux PHP, PERL, APACHE, bash, crontab, sendmail. Ninja class database normalization.

Our modern focus on cloud computing and software as a service has led us to be a leader in browser based database systems. Sometimes called web applications, we use a lean approach compatible with firefox, opera, safari, ie and chrome. Our approach yields 20-40 percent savings in IT over conventional windows applications. In a broad sense, Facebook is a comparable to our approach, and AOL is the old Windows IT approach.

While we won’t replace all internal applications, our implementation speed is unmatched in operational areas of service and manufacturing enterprises.
Examples of cloud applications include dispatching, vehicle and crew recording, booking, document management, build to order, capacity planning, eBusiness, eCommerce.

Examples of features available in cloud applications include user tracking and usage logging, user privileges and application access security, entry forms in modern html format, surveys, search, email interfaces and automation, PDF generation.

Benefits of database and business logic implementation of the cloud include inherent multi-site access, lean IT, email triggers, central upgrade, mobile access, real time data and updates, daily email reports, server-less offices, excel compatible reporting, open source platforms. We can implement and maintain all client to client or client server environments for customers in the Atlanta Metropolitan area, including but not limited to Rockdale County, Dekalb County, Newton County, Conyers, Lithonia, Covington, Oxford and surrounding areas. BBX Thoroughbred Providex Business Basic IDOL-IV Database Customization, installation, configuration, maintenance, sales. MRP, ERP, CRM, Demand Flow, Financial, Accounts Payable, Accounts Receivable, Purchasing, Inventory Control, Production Control Sales Order.

Quickbooks Pricing

15 Users – $ 6,000

Server stack – Dell/Intel/Redhat/Postgres – $ 1,500
Serverless – workstation PostgresSQL install – $250

Includes access to support, pdf report writer, service packs, and choice of Postgres Plus Advanced Server or Oracle Standard Edition

Quickbooks Enterprise – free trial – yes, server required – no.

5 Users – $ 3,000 (per year for updates)

30 Users – $ 8,000 (per year for updates)

Freshbooks

QuickBooks alternatives, cloud invoicing

Freshbooks

MSInc is not affilliated with FreshBooks, but is providing this information for those that may need an alternative to old fashioned PC and Server based billing programs like Quickbooks, Peachtree or MAS-90.

Freshbooks is a billing oriented package that uses cloud database technologies to automatically manage the storage, security and backup of data, eliminating IT costs associated with maintaining company and departmental servers to store and manage data.

  • Invoicing
  • Tablet and phone capable
  • Time Billing
  • Recurring Invoices, subscription billing
  • Paypal Integration
  • No server required, cloud based
  • Sales Tax accounting
  • Expense Tracking
  • Time tracking
  • Export to Excel

Zoho Invoice

Billing and Accounts Receivable, Cloud Invoice

For a small business, we a have just looked at Zoho Invoice. Zoho Invoice is a cloud based accounting system much like QuickBooks OnLine for small business invoicing. We are really impressed with the user interface. The initial sign up phase for a thirty day trial required e-mail verification, so it actually was several days ago that we first considered doing a review for Zoho accounting. But this morning, after digging through my e-mail for the verification links, we completed the process and began to evaluate the invoicing process for a simple time billing scenario such as a consultant would use.

First, we looked for a way to create an invoice. Zoho could not have made the link to begin creating an invoice any easier to pick out of the computer screen. This is a sharp contrast to QuickBooks which, even with it’s navigation screen, leaves us sometimes staring at all the things we can do and wondering where we went to do invoices.

Next, we know that there is really no such thing as an invoicing system without a customer file. And we had no customers entered into the system. Zoho could not have made this step easier. Just below the customer drop down box is a link to create a new customer. And upon adding a new customer, also a little easier than QB, was an option to save the customer and continue entering the invoice we had started.

Very smooth and intuitive, within three minutes, we had created an invoice for a customer, This left me wondering how I could e-mail and print the invoice. With a little glancing around, on the right side of the screen are options to email and print the invoice. Viola!

Having consulted for dozens of companies for 25 years, I wish I had this resource years ago. We will have more information on Zoho invoicing and accounts receivable coming up.

Lean Manufacturing and Web design

Lean manufacturing zealots have declared that web-site design and development can be done faster with the help of Lean manufacturing techniques. They contend that even copywriting is faster, don’t ask us how. Lean manufacturing is a promising practice in the delivery of goods that eliminates pointless and wasteful elements of the production and delivery cycle for elimination. Somehow, this is done by hiring wasteful and self absorbed Lean Manufacturing consultants, because you were too stupid to do this. But let’s examine the Lean Manufacturing premises and promises to see if we can just apply some common sense to our operation. We can even ponder the creative process of copy-writing and look for an application for these principles.

First, widely we hear claims for Lean, these I will call the promises;

  • 1/2 the man hours
  • 1/2 the defects (quality improvements )
  • 1/3 the engineering man hours
  • 1/2 the floor space for the same factory/distribution productivity
  • 1/10th or less of in-process inventories, i.e. Just in Time or JIT WIP management

To back up these goals, the Lean evangelists ask us to envision a ‘fictitious Boat Factory.’ In their workshop they ask teams to for a production line to make as many cardboard sailboats as possible in 30 minutes. They say that the workers struggled to produce 20 good boats and produced 30 defective boats. They then reorganize the divisions of labor in an improved manner to help the team produce 36 boats with only one defective. They hope their dupes miss the point that the initial crappy production line was designed to be unwieldy by the creators of the workshop. They instead only stress the results.

  • 80% output improvement
  • 70% reduction in lead time
  • 97% reduction in defects

All because the assigned method forming the assembly line was optimized. Their assumption is that manufacturers are too stupid to do this, and their data is whatever they say. This is not to say that a production line exists that cannot be improved. My only point is that this little workshop and its purported lessons are rather pedantic.

The relevant facts of Lean Manufacturing consist of looking at the production line in terms of bottlenecks, length, capacity and people. They squeeze those terms into the word FLOW by calling this Faults, Length, opening and workforce. I think they wasted a lot of time doing that. My first suggestion for the Lean Manufacturing people is to quit wasting their time on silly acronyms and stick to the point. A line consists of capacity and operations, and is never perfect. Wow, C-O-NP. Whatever. One claim of the Lean Manufacturing people is that Toyota has seven to ten times the productivity of American companies. Any experienced manufacturing professional knows the utilization of KanBan by Toyota, which can help any manufacturer improve throughput at the expense of WIP. Not surprisingly, when you optimize one thing, something else has to be loosened up to accommodate.

No better example of Lean Manufacturing in practice would be the delivery of water to a fire. Dozens of firemen may spend hours and days idly at the firehouse, and then when called upon all traffic on the road should yield to the firetrucks. Sacrifices made in the productivity in some areas to get water on a fire most quickly.

But even more fascinating is the application of this cheerleader driven logic excercize to web design

The Lean people start with the word FLOW. F stands for faults, and they assume first that clients know what they want. In the Lean Manufacturing model of ideal web-design they encourage lots of meetings to determine the exact number of pages and design of the web-site required. Those meetings of course, are roughly a waste of 75 to 90 percent of meeting time. The purpose of a website is to present information, via the internet and search engines, to an audience of information consumers. Some meetings are required but for the most part only delay the publishing of the website to the internet and search engines. But never mind that, isn’t it just extraordinary how they work the word FLOW into things. At Web-Atlanta we have published web-sites with copywriting in 72 hours. Atlanta web design doesn’t get leaner than that.

At Web-Atlanta, one difficulty we do have is with the graphics department. They insist that a certain amount of ping-pong, fuse-ball and fresh fruit juice are components of a happy workforce. In our copy-writing department, I can assure you that a whip and a cattle prod are quite sufficient. Seriously, the rapid publishing affords us interaction with our clients supplementing the design. The budget is not spent on a myriad of wasteful workshops cutting out cardboard boats with blunt scissors, the real interaction begins when the prototype hits the clients desk and conference calls begin with the clients and Atlanta web design.

The next letter is L, and as you can see, Atlanta web design from Web Atlanta can bring forth a published web site before ten percent of the scheduled meetings have occurred for other web design firms. Then we come to the letter ‘O’ which pertains to the capacity to produce web-sites. Not including graphics, Atlanta web design firm Web-Atlanta produces three sites per day, technically and in copy-writing. In business for fifteen years, we are still waiting for the graphics arts department to decide on the logo for our stationary.

Web Atlanta is a great advocate of incorporating existing EPS graphics into the web design. If you have a public relations graphics package then we can save countless weeks of waiting by allowing our graphics arts department to just play fuse-ball and drink juice while they think about our Logo at Web-Atlanta – our technical department can do the manipulations required to use your existing graphics.

The Lean Manufacturing folks would be a little peeved if I finished without mentioning the W in the word FLOW. As I have mentioned, our use of cattle prods and corporal punishment is all we need to mention in dealing with our technical and copy-writing departments. But another invention of the Lean Manufacturing world are the 5 esses.

  • remove clutter
  • Organize
  • maintain
  • organize
  • measure

Yes, those are the five esses of Lean Manufacturing. Ess means the letter ‘S’ and they make one thing into two because a number 5 looks like a letter ‘S’. Wonder how much time they wasted on that? Don’t waste your time wondering – too much.

At Web Atlanta website design firm, tools that help us quickly produce a website also optimize Page Load Time. It is widely accepted that fast-loading pages improve the user experience. In recent years, many sites have started using AJAX techniques to present the user with the webpage before having to wait for every element of a complex page to load. However the majority of the page load time is spent in separate HTTP requests for images, JavaScript, and style-sheets. Speeding up these separate HTTP requests might help, and the Rails world has shown examples of this form of optimization.

With the roll out of HTML5 and the cacheing technologies in practice at Web Atlanta website design the delays in serving webpages is kept to a minumum. While we typically avoid a rails application for mundane web design, we respect the slip-streaming cleverly developed for massive css and js library delivery while keeping the number of http requests to a reasonable level. With more then nine out of ten internet users connected assymetrically, i.e. their download speed is much higher than upload, interruptions for http requests should be carefully considered, even when AJAX may seem sexy. It is simplistic to assume that bandwidth is rapidly expanding when in fact some users still may even use dial-up or share with many users a slower T1 or T1 speed DSL connection.  If you conditionally gzip HTML, javascript, or CSS, you probably want to add a “Cache-Control: private” if you set an Expires header. This will prevent problems with caching by proxies that won’t understand that your gzipped content can’t be served to everyone. (The Vary header was designed to do this more elegantly, but you can’t use it because of IE brokenness.) For anything where you always serve the exact same content when given the same URL (e.g. static images), add “Cache-Control: public” to give proxies explicit permission to cache the result and serve it to different users. If a caching proxy local to the user has the content, it is likely to have much less latency than you On pages where your users are often sent the exact same content over and over, such as your home page or RSS feeds, implementing conditional GETs can substantially improve response time and save server load and bandwidth in cases where the page hasn’t changed. The idea is pretty simple: When you generate a page, you give the browser a little extra information about exactly what was on the page you sent. When the browser asks for the same page again, it gives you this information back. If it matches what you were going to send, you know that the browser already has a copy and send a much smaller 304 (Not Modified) reply instead of the contents of the page again. And if you are clever about what information you include in an ETag, you can usually skip the most expensive database queries that would’ve gone into generating the page.

Load times for five style sheets of 10kb can be more than double that of one stylesheet of 50kb. Thus, the railsish optimizations can double the speed of RoR webpage delivery. Many designers are afraid that cached webpages may lead to content served but not statistically captured. In the GA model though, the use of an asynchronous JS client side seems to insure that each client reports each web page at least exactly once per cookie-life. However, in distributed content environmnets the server managers such as Amazon and Yahoo suggest that if your files have changed, use a new file name. You can trick browsers into thinking the file URL has changed by adding a useless query string, e.g. index.php?moonjuice=fryed-okra.

Atlanta Web Design

Production Planning

Resource Planning in the traditional sense originally included planning the material requirements for producing batches of parts or discrete finished goods or sub-assemblies. In the original MAPICS Material Requirements Planning stages the fundamental bottleneck to be addressed was the Material Requirements for varied discrete requirements and varied process requirements. Requirements were the sum of firm customer orders and the Master Production Schedule, a top level forecast of requirements not yet on firm orders.

When the IBM mainframe was the computer of choice, (mainframes also came from Burroughs Corporation, and there were the Sperry Univac computers) prior to the dumb terminal, the mini-computer (now called the server) and computer networks, computer room operations were done entirely with punch cards. Also called the Hollerith Card and the 96 column card, computer punch cards were roughly the same as one line of computer instructions, e.g. <h3>A Minor Heading of a webpage</h3>

In the above example, we see the computer language, html, and some date. This was also the case in stacks of computer cards. A stack of computer cards in an IBM mainframe job would include some JCL or Job Control Language, a computer program, and then the data to be processed, followed by a card that would signify that there was no more data.

Thus when the Basic computer language came on the scene, we had data statements that were part of the computer program. This seems odd, but it is both an evolution of the old stacks of computer cards and also demonstrated by the simple html example above as a real world situation of programming computers.

Back to the Material Requirements Planning process, the data involved is the Master Production Schedule and the Order Backlog, followed by the Product Structure which would need to be ‘exploded’ to calculate low level demand. The final output of the computer program run would be a new stack of computer cards with part numbers and the total requirements for that material. Next, this stack of computer punch cards was combined with the current On Hand Inventory to calculate Purchase Requirements.

Thus, many processes in the punch card days of computing might be more than one step. A computer operator would assemble the stack of cards and process them to produce a new stack of cards that would be combined with other cards and a different computer program (which is on cards) to produce another stack of cards, that might then be processed by an RPG program to produce a report for the purchasing department.

Besides RPG, Fortran, LISP and Cobol made their way into computer programming before the interactive computer terminal and cathode ray tube, that is when computers were programmed with a stack of cards. The computer card actually came before the digital computer, in the end of the 19th century (around 1900) with the need for accurate counting of Census information, among other things. Herman Hollerith was a statistician that was also a bit of a mechanical engineer who designed ways of punching cards and reading cards to speed up the manual processing of statistical information. Hollerith first convinced the government to use his card system in 1890 for the census, speeding tabulation be almost ten times.

The main difference between tabulation around 1900 and computing around 1960 with the digital computer was that originally card processing was primarily a sorting operation. If in 1900 one asked the question of how many nuns lived in Colorado, the Colorado stack of cards would need be be sorted for employees of religious institutions, then for females, and then often it would be hand inspected to publish a final result.

Because of the rich history of tabulating, when the original digital computers came about, in which a series of vacuum tubes would store a zero or one, and another series of tubes would perform simple operations such as fetching a byte, storing a byte, or nanding a byte with another byte, quickly we interfaced the crude methods of getting data in and out of the limited amount of computer memory with the tabulating equipment that had been in use for more than half a century. The stack of cards now served as computer memory itself, an important breakthrough, because in the early 1960’s even a large IBM 360 computer might have less than 8k of memory. Eight thousand bytes of electronic memory in 1960 required several racks of transistors,where as one million times that amount of storage now fits on a USB flash drive.

Of course, this difference in the memory hierarchy had keen ramifications for the design and implementation of computing projects such as a Gross Requirements Explosion for Material Requirements when planning production at at manufacturer or chemical processing industrial facility, such as a refinery or a maker of laundry detergent. Today, however, we are back to the same dichotomies, because of an entirely different set of computing resources. We no longer use the Hollerith Card, but we also make little use of the USB flash drive in computing, that is we may use a flash drive to store images or videos, maybe even documents and procedures, but in a computational format we make little use of this sort of ‘off-line’ memory that has so many similarities to the Hollerith Card.

However, we do engage in a process most similar to the JCL language of the early IBM mainframes. In modern Enterprise Resource Planning, we knit together a database from one server with html and php served from another node of the network, where our desktop with the aid of Java and the JBoss stack provides the computational power to produce the new stack of punch cards, which in this case is actually a stream of updates to the database, MySql or more often PostgresSql which then has captured a new state of the planning and implementation process. Where we often spent a month or more between time when we would combine demand with a Master Production Schedule and the current purchasing schedule complete with scheduled deliveries, we now look whenever we need at these data, combine them, recalculate our needs and assess whether we need to call a vendor to expedite or delay a specific delivery release.

Carrier Class, reliable ERP and CRM

Small and Medium Enterprises in Atlanta interested in an open source solution for CRM and ERP can rely on the groundwork laid at MSInc for LAMP based products in the Enterprise Resource Planning and Customer Relationship Management arenas. MSInc has a rich background developing and implementing LAMP and unix or Linux based Material Requirements Planning, Resource Planning, Customer service and core accounting functions. MSInc built the famous Support Series II application that flourished on Zilog, Arix and SCO Unix based systems in the 1990’s. With it’s IEEE based Y2K initiative, S2I2 provides to this day a flawless if somewhat legacy in technological terms orented solution covering core areas of ERP such as Customer Relationship Management with core modules like LQDE, Material Planning with IQDT and JQDA / SQDK core accounting foundations.

Despite the robust nature of S2I2 applications, MSInc is now developing in the cloud, using Software as a Service in such applications as FMCSA compliance with their leased WRbil product for dispatched service enterprises. Even plumbers, local movers and retail companies with a delivery fleet can now manage hours of service without dependence on a finicky spreadsheet and a full time harried DOT compliance clerk ever hoping  their complex excel formula not fail them.

WRbil is a LAMP application, that is it is based on Linux, Apache, MySql and PHP/Perl. It is implemented on the cloud model and provides a limited scope of functions compared to the more robust S2I2 application. WarBil is a part of the mix in modern SME application environments which also house accounting environments like QuickBooks, Peachtree Accounting or MAS 200/500. With this product in hand at MSInc we have then sought out alternatives to closed technologies and found three interesting alternatives to the formerly named solutions. At MSInc we have begun evalutating Compiere, OpenBravo and last but not least, perhaps our favorite, XTuple.

Pronounced with a long ‘U’, XTuple is a solution out of the Southeastern United States, which we at MSInc find attractive in itself. Like Rehat, this application comes in the free as in open (not free as in beer) open source model. While we see the depth and complexity of these applications to be sufficient that client modifications should be considered carefully. Our investigations are partly centering on the seperations of the format, template, gui and business logic layers. One common requirements in modifications are the additions of fields to the database and the addition of business logic to provide extra funtionality.

Information on LAMP ERP is available from MSInc.