Wayback Machine
SEP SEP Oct
Previous capture 4 Next capture
2008 2010 2011
2 captures
7 Sep 08 - 4 Sep 10
sparklines
Close Help
 

PCOPY!
covering all the BASICs...

Issue 30  ~  February 16, 2007
In This Issue:


Contributors:

E.K.Virtanen,  MystikShadows,  Hartnell,  Richard D.Clark,  TheMusicalAbuser






From The Editors

This time we did it in time it was ment to be done. Bit over a month from PCopy! #20 and here we are now, PCopy! #30 is in our screens.

What has happened between these two issues? To say it honestly, seems to be pretty quiet in everywhere. However, we believe there is something for everyone in this issue. But if not, sorry, we try to fix our habbits :)

Feedback after last issue was, well, it was practically what we did expected it to be. We wana say thanks to those who did gave us feedback with common sense. Feedback dont need to be positive to be a good feedback. You can say "hey, your issue is full of spelling errors. you should do something for that", but when you say "you suck, magazine sucks, grammar sucks and you cant even write properly", it seems to be only a way to pull out some bad mood. But if it drives someones bad mood away, when they spoil us with sentences like that, then, keep on doing it. Naturally those ones are skipped when reading feedbacks.

Spellcheck is now, or it atleast should be better than last time. For sure, there is still missing dots and letters but hopefully everyone can see that we have tried to succeed better now in this issue than last time. Grammar is a harder bite. Everyone does have their unique way to speak and write. And since we live in a rock that is this big as Earth, for sure no one cant please everybody.

Anyway, here it is. Editors goes to take some coffee and leaves you to read the issue itself.

- The Editors of PCOPY!

P.S. the complaint departement is now open for business! ;-)





Letters

Letter from Hartnell.


Slap Hartnell. He Forgot!

I promised an OO B2D tutorial for this issue. I also promised that I would go over the syntax oddities (the whackstick) of Basic4GL. I'm sorry, they won't make it. As I write this, I will barely make the deadline. Feel free to slap me. Both will be in next issue, on time, and probably turned in very early.

-hartnell

  • From The Editors

    You want me to spank you with my 6 feet leather whip? Sorry buddy, you got to be way more evil to deserve that honor ;)

    -E.K.Virtanen





Quote of the Month

E.K.Virtanen + Mennonite + Pete


Background of quote: in here
  • E.K.Virtanen: Dont you guys ever get tired about this issue? Simple solution: QB'rs stays here and FB'rs goes to fb.net.

  • mennonite: i'll be tired of the issue when the issue has been resolved...but yes, sometimes.

  • Pete: mennonite to English translator: That means: No! he'll never be tired of it.




News and Neat BASIC Projects

Weiltmeister is working with an cool looking RPG. Status in this CoolBASIC project is "under developement" and there seems to be lot's of work to do but by looking some screenshots, we can expect this one to be cool when finished. Finnish topic about this game is here and we also have one screenshot right here to show for you. Just click this to see it.


Gambas 1.9.47 released at 17'th Jan. 2007. See the Changelog for the changes and bug fixes.


sir_mud posted about FB Online Editor wich actually allows you to try FreeBASIC with out installing or even downloading compiler itself.


Sebe at GLBASIC forums posted this, "I've decided to give away my texturepack with normalmaps for free. It's 19 MB in size"


January 16, 2007: HBasic-2007-01: After many months without publishing new versions this release should become a new start for testing new features. It is an instable version because many changes have been inserted for the ERP-Forms. More at HBASIC website


HotBASIC 5.1c came out at 2'nd February. 2007


New open-source programming launguage Game BASIC is under development.


RunBASIC was announced some time ago. Its a neat online BASIC compiler that is based on Liberty BASIC programming language.


PureBasic 4.00 Beta 3 for Linux Feb 13, 2007


thinBasic preview version is 1.2.0.1 updated at 11'th February. 2007. More at thinBasic.com


PlayBasic IDE updates 10th Feb 2007.


BBC BASIC for Windows version 5.50b released 4'th January. 2007.



Brutus2D gets its first tank demo. Tanks


Basic4GL 3D puzzle game, Twigris




Fruity Caves Beta

Fruity Caves Beta, written with BlitzBASIC released at 2'nd February. 2007. Download it from here.


And if you visit at BlitzBASIC, check also Jet Set Willy Online


The BASICs Page now includes 79 different BASIC alternatives. One more and full 80 is there. Three latest entries are CoolBASIC, Emergence BASIC and QuickerBasic, dated at, January 5th 2007.


GW-BASIC website, started by KindlyRat seems to be gone. Let's hope it comes back.


Seb McClouth keeps on working with he's QBInux project. We can only admire hes determination with this one. Keep your attitude Seb, *thumbs up*


zoasterboy posted about Qbasic Wikia Jan 08, 2007.


Roy Scott did review Emergence BASIC. We are bit late with this one, but better now than never. Emergence BASIC, by Roy Scott, (review), 12/21/2006.





Game Competition: Asses of Fire

Hartnell


Yes, it's an odd name. Asses of Fire. It certainly misleads people into thinking something dirty, or malicious, or both. Such is not the case. Asses of Fire is a basic Asteroids-esqe sprite sheet I finished on a no-sleep bender. Being silly due to the lack of sleep, I dubbed it "Asses of Fire" and the name has stuck.

I have several such sprite sheets on my harddrive that are just sitting there. So, I give them to you, the readers of PCopy, but with a catch. In the future, this will be a great chance to snag some free sprites for your game. I only require that there be three entries each month. If things work out, I'll start making sprites for the competition by request. But, you have to compete.

The rules are simple. Make a game in a BASIC language using this sprite set. You may add your own, but you must incorporate all the sprites in the set.
Well, have fun.



Send your entires to : shawn.hartnell@gmail.com





Travellin in a BASIC land

E.K.Virtanen


What i do have have here in my bookmarks? Bit over 50 different bookmarks for different BASIC compiler websites. Naturally part of those compilers are not developed anymore but they are very usable, at least for me and for my needs. Do i use them all, or even visit in those sites daily? No, i mean who could get that much time from real life? After all, im regular guy with regular day job and...well, nearly normal life too.

So what compiler i do use and where i do visit when i can? Its not relevant question since where ever i go, i see the same sad view. Peoples are treating their favourite compiler as it would be somekind of holy cow. Dont you dare to say that Compiler X is better on certain thing than Compiler Y. For sure, you are diggin your own grave. Takes 5 to 10 minutes and Compiler Y fanatics are behind you, "dont you dare to say that!!! Compiler Y ROCKS and X sucks you stupid lame freak jerk. Gay faggot, get a life you moron!!!"

Actually im wondering is there some organized groups that does spy on forums of other compilers, looks opportunities to pull the "Our compiler is better" than yours- trigger?

Second issue that a guy like me wonders is way how compiler works. I mean at least 100 threads in different forums are made only to dispute is Compiler X an real BASIC compiler since it does c++ first and then turns it to binary. Or is it right or wrong if compiler can also compile some other programming languages? Can BASIC compiler also handle a c++, c, python or other alternative languages and still be a BASIC compiler?

Is that actually significant question at all? I mean as long as classic keywords such as print, input, screen, sleep, for...next, if...then...else, rem, data etc. works, who cares does Compiler X do its work to create .exe files in wich way?

Anyway, as i told there is lot's of websites and compilers for us all. If you like Compiler X, stay at www.compilerx.something and be happy.

E.K.Virtanen





Free Brutus2D

Hartnell


The best things in life are free, including Brutus2D. Now that it's going to be free, that is. Yes, I said free! No charge! Gratis. Believe it or not, the BASIC-based game programming language that has taken GDN by storm is going to be free! Well, not entirely.

The developer of Brutus2D is finishing the details of a new model familiar to both Game Maker fans and Microsoft language fans. Brutus2D will have both a free and registered version. The kicker of it is that the registered version will still only cost 10 USD! Can you believe it?

With the free version you will get features not often (if ever) found in a free game programming language like sprite rotation and scaling, alpha transparency, and built in tile map methods. This, not to mention EASY OBJECT ORIENTED BASIC. Your player will have to see a very short "loading screen", similar to Game Maker's that says "Made with Brutus2D". Unlike Game Maker, you won't have to see it while you are developing games.

The registered version will include all the things that serious developers will require. Support for playing movies, a particle engine, and GUI. And of course, the "Made with Brutus2D" loading screen will be removed. It may be optional for die hard B2D supporters.

Unfortunately, the math object may become a part of the registered version. It's certainly worth the 10 bucks, but I worry that some essential methods will be taken away from newcomers. math.GetDirection, math.GetDistance, math.DegToRad, and others are so essential to so many basic game types, it make hurt more than help to keep it out of the registered version.

The free version *should* be out before the next issue of PCopy!, buy why wait? Brutus2D currently has a demo with no time limit (but no ability to make exes) , which you can get here :

http://pewtersoftware.com/wordpress/?page_id=6

If you need some tutorials, you can find quite a few in my own Brutus2D tutorial section :

http://www.gamedesignnovice.com/wiki/Brutus2D_tutorials

-hartnell of Game Design Novice





mafSOFT - Mapixelator Preview

TheMusicalAbuser





mafSOFT's Mapixelator is a tile and map editor tool for editing graphics and maps for 2d games.

I've been working on this project for about a year and a half, I've made alot of changes on the way and have had several complete restarts, but I am coming close to having a version stable enough to release.

You may be wondering what this has to do with BASIC? The entire program is being written in FreeBasic, using several libraries, Win32, OpenGL, Zlib, and Lua.

Let me get to the point and show you what sets this editor far and above any other map or tile editor.

  • 1. OpenGL rendered editing - the entire editor is rendered in OpenGL this means great performance, even better with newer graphics cards.

  • 2. Unique Animation Editing - Can build animations fast and easily, able to set milliseconds for each frame. The more amazing thing, I have yet to see in any other editor, is real-time preview of animations in the map and tile editor while editing.

  • 3. Lua Scripting - Can export and manipulate data from Lua Scripts. This will allow custom map formats to be exported, and will function a lot like the map scripting in my favorite tile editor "Tile Studio". Scripts can be written to directly access tileset pixel data and map data offering a similar concept to the "CodePaint FB" idea talked about on the Freebasic forums but using Lua instead of Freebasic.

  • 4. Tile Editing - Tiles are edited in full 32-bit RGBA, there are 3 separate modes you can edit pixel data in RGB+transparent pixel, RGBA, and Alpha only.

  • 5. Hotkey driven interface - Almost everything can be done directly from the keyboard, without having to dig deep in some menu to activate things.

Those are the major things that really make the Mapixelator stand out, but here are some more of the more basic features you would expect from a tile and map editor.

  • -Maps can have up to 9 layers with parallax scrolling.

  • -Fast Zooming on Tiles, Tilesets, Maps and Animations.

  • -Import and Export of tilesets in BMP and PNG formats.

  • -Projects support up to 256 Tilesets and 256 Maps.

  • -Projects are small files thanks to zlib compression.

I also sent some pictures to show off the Mapixelator.

  • The first image is of the map editor mode.

  • The second image is of the tile editor mode.

  • The third image is of the animation editor mode. Frames and animations are added easily by just moving the arrows down and to the right, quickly delete unneeded frames with the delete key.

  • The fourth image is of the map editor again, showing off how you could use the map editor to edit ASCII graphics using an ASCII font as the tileset.

Expect the first release in a month or two if all goes well.
Anyone interested in helping beta test the Mapixelator can email me at TheMusicalAbuser@themaf.com

Thanks for your time! And now I need to get back to work on the editor...

The Musical Abuser





Lesser-Known Basic Languages, Part 2

Richard D. Clark


This lists some lesser-known Basic languages that can be found on the Internet. All of these languages are free or Open Source, or are freely available.


Gambas is a Visual Basic-like Basic language designed to run under Linux. It is not a VB clone, but does incorpoate some VB-like features such as an integrated editor and debugger. Like VB.Net, Gambas uses an object model for the code elements. Not a Linux user, I have no idea how this system performs, but the screenshots on the website look promising.


Chipmunk Basic is an old-fashioned console Basic. If you are old enough to remember the Commodore or Atari computers, you will feel right at home with this Basic language. This is what you see when you start the Windows version:

v3.6.3b2pcw0601h
Chipmunk BASIC v3.6.3(b2)
>

You can then type in a command that will be executed immediately, or type in line numbers and use the familiar run command. The language seems to be quite extensive and contains all the familiar language constructs. One interesting feature of this language is the use of classes which can inherit from other classes.


Script Basic is a Basic interpreter with a rich command set. One of the main targets of the interpreter is as a CGI replacement using the The Eszter SB Application Engine that interprets Script Basic natively. It supports most of the common Basic commands, and some not-so-common such as associative arrays, network functions and pattern matching. There doesn’t appear to be any graphics functions, which should be expected with a language oriented toward text manipulation. The nice set of text functions in the language would make this a perfect candidate for text games.


A few years ago I cam across a Basic programming environment called Envelop. It was a Visual Basic like language, which some really nice extensions. It was planned as a commercial product, but Visual basic was at its height and Envelop eventually was sold to Janus software. The Windows version is identical to the older Envelop, and the Linux version is called Phoenix Object Basic. Even though the language development appears to be stalled, there is still a dedicated user base that has come up with tweaks and work-arounds for the language limitations. It is very close to VB in syntax, but has some nice features not in VB such as subclassing existing controls. It is an interpreted language and the runtime, like VB is quite large. It is an interesting language but probably not that useful due to the lack of development.


O'Basic is another commercial Basic that has gone freeware due to lack of development. This is an interesting language since it is COM-based and encapsulates the Windows API into an easy-to-use package. It comes with a forms editor that contains the standard controls along with a code editor and debugger. Again, probably not a useful language, but it would server as a nice scripting language for common tasks that you might need to perform.


MBasic is a classic console-based interpreter based on Mini Basic (Sylvain Bizoirre's), which was based on Tiny Basic (Li-Chen Wang's). It has all the expected commands, dynamic strings, arrays and a good set of math and string functions. The package also includes the ASM source, which should be instructive to those who dabble in ASM and have considered writing an interpreter.

In this installment I presented some of the more interesting Basic languages, although a few of them may have limited utility. It is interesting nonetheless to see how an author envisions his or her version of Basic, and how they tried to implement that vision.

Lesser-Known Basic Languages part one at PCopy 20.

Rick Clark





Hartnell's Lost Article

Hartnell


This is an article I wrote for FreeBASIC Insight. Currently, the wrong version of this article appears there. No matter how much I pleaded to get the orignal article put there, the early, very incomplete draft remains. It even contains the disclaimer :

(note to management, this is just a draft as I collect my thoughts for this article, it shouldn't be published anytime soon)

And yet it was still not noticed that this was a draft. I'm not trying to say anything about FB Insight. I'm publishing this to get the article read.
Well, here it is, my lost article :

Stepping Onto The Shaky Noob Ladder

I write about noobs. As I write this, about 90% of of the articles I'm working on has something to do with noobs. How to help noobs, the profile of the noob, a list of common mistakes for noobs to avoid. I could be spending my time working on my dream game, a Street Bike Fury-esque game, but I choose to take alot of time out for noobs. You'd think that I was stuck in noobdom. And here, in this article, I will confess I am a noob.

I've been programming off and on for close to thirty years. I began typing away on a now ancient Vic-20 and moved up to a Tandy 1000. Eventually, I got a solid handle on core programming and easily program a 2d game provided a good library. Most of you might think "Well, that's not quite noob programming."

But it is. My math skills are still not up to par. When it comes to trigonometry, I'm utterly lost. I couldn't program 3d to save my life. The math used in my favorite game (SBF) completely mystifies me. Like all noobs, my dream game is currently unreachable because of my lack of skill and knoweldge.

While I consider that, I have to consider how I got where I am today. I once was a complete noob. People who had a better grasp on programming were kind enough to show me the basics, give me strong hints, and point me in the right direction. In turn, I can only express my gratitude by doing the same thing for the complete noobs I happen across.

I've gone up the ladder, but, in fact, I've gone nowhere. SBF is still competely out of my reach. As I learn what I need to know to reach my higher mark I will have to depend on the help from people on a higher rung of the ladder. When I finally step up, I fully expect to look below and see people like I used to be. There will still be people above who have the skills and knowlege I want to learn.

This is the Noob Ladder, and it is eternal. Noobs don't just come in the "complete noob" variety. There's several stages, and we are all eternal noobs. If we already knew what we needed to know to make the best game we could ever make, most of us would just quit. It's not the end product that we produce that makes programming fun for us --- it's climbing the Noob Ladder for new, better, and more interesting things to learn.

This is the precise reason I write about noobs so much. You may have not noticed, but every programming community is full of eternal noobs. When I write about noobs, I write for noobs of all levels (mostly.)

The FreeBASIC Noob Ladder is just now forming. As I noted in my previous article, we're still doing a poor job helping people reach that first critical rung in the ladder. The first rung is where most of the new members to the community will come from. By helping people onto the ladder, we increase the size and scope of the FreeBASIC qmmunity. The next relsoft or Cha0s is waiting at the bottom, looking up at us, and wondering where to put his foot on the first rung.

One thing keeping alot of us from helping him is that FreeBASIC is not at a stable first version and will change over time. This is true, but it doesn't effect the first rung of the ladder. If FreeBASIC changes the way it supports more advanced programming, For-Next and If-Then will continue to work the way they do now. The FreeBASIC team isn't going to remove support for strings or integers either. We won't wake up to find that the basic string data type has been replaced by a C-like char array.

The first rung is stable, so lets mark it with a large sign that says "Step Here."

-hartnell





Interview with Richard D. Clark

E.K.Virtanen


No matter where you've been in the QMunity no matter which forum you visit or why you visit them. One name always seems to appear there. A name that always seems to find the time to help you with your questions, to guide your programming projects in the right direction. That name always seems to be willing to set time aside to help in more than one way. He's created documents of all kinds concerning programming in more than one language. That name is rdc.

Just who is the man behind this three letter nick name? Find out exactly what makes him tick (so to speak) as we engage in an interview with him right here for you, the readers. So sit back and discover, as I did, who rdc is and what makes him such an awesome guy to talk to and work with.

  • E.K.V: Ok, lets start with the "specs". Who, what and where?

    R.C: My name is Richard Clark. I live in Texas, USA (which is the only state in the union that used to be its own country btw: The Republic of Texas). I was a professional programmer for quite a number of years working as a consultant for companies like Mobil Oil, J.C. Penny, Form Motor Credit and ACS, along with a host of medium to small businesses. I primarily developed Windows application in Visual Basic using SQL Server. Fairly boring stuff, but it did have it moments.

    With the IT downturn here in Texas, I have been working in non-computer related fields and have been concentrating on my writing. I write both fiction and non-fiction, with a lot of my nonfiction writing geared toward the programming community.

    I started the book FBeginner: A Beginner's Guide to FreeBasic but had to pass it on to the community since I could not keep up with the changes to the compiler. Right now, I am working on a book about game design on the nonfiction side and working on a novel on the fiction side.

    I also keep my programming skills up to date by working on small projects that don't take up too much time. Recently, I discovered the demo scene and have written a couple of demos and I am trying to learn some 2D graphics effects along the way.

  • E.K.V: As an professional or retired programmer, how you see amateur scene of programming today?

    R.C: There doesn't seem to be much going on, at least in the programming circles I am a part of on a daily basis. It seems in the forums I frequent, there is a lot of talking about programming, but little product being produced. It is sad that the majority of posts in many programming forums are in the off-topic sections.

  • E.K.V: You have also experience for different commercial and free programming softaware. What are those important differences between these two in use of normal "home programmer" and professional application developer?

    R.C: Generally as a professional programmer you don't get to choose your tools, you use whatever the shop uses. You may like mySQL but if the office uses SQL Server, you have to learn and program against SQL Server. This is both good and bad. The good side is that there is a consistent set of tools to use which make large projects easier to manage. The bad side is that you are stuck with those tools, even if something better is available.

    For the hobbyist, professional tools are generally cost prohibitive. Who can afford $1000+ for a development package for just a hobby? I am sure there are people who can, but for most people the professional tools are out of reach.

    However, there are a multitude of free or low cost tools available nowadays that the hobbyist can use that work just as good as most professional packages. Even Microsoft has released Express versions of their development tools that are free to download and use.

    It is a strange situation. There are many more quality free tools available today then when I was programming for a living, and yet there is much less product available. I attribute this to the decline in professional programmers (at least here in the U.S.), which to large extent drive the hobby and open source market. I don't expect the situation here to improve anytime soon.

  • E.K.V: Since you have programmed for so long now, it would be nice to hear when was the "golden era" of programming scene, in your opinion?

    R.C: For the hobbyist, the golden age was during the late 1970's and 1980's. There was an explosion of microcomputers on the market, the Vic 20, the C64 the Atari XL/XE/ST, the Amiga and Apple computers. There was also an explosion of software, everything from spreadsheets to games that ran on these small computers.

    The games were of special interest. Since the computers had limited graphics capability, the programmers had to make the games interesting in order to be successful. Some of my favorite games appeared during this time like Lords of Conquest, M.U.L.E. and the Infocom series of text adventures. I bought just about every SSI game they made and all were of high quality.

    When the IBM PC came out it was actually inferior to the microcomputer systems, but it paved the way for the computer to become a real office appliance. When the IBM compatibles appeared, the cost was low enough that the homer user could purchase one and it meant the death of the microcomputers, as well as the IBM PC. Most people nowadays that don't have an Apple computer are using what we once referred to as an IBM compatible computer. With Apple switching over to the Intel chips, they too are now part of the IBM compatible group.

    There was a real sense of community back then. People would actually get together and meet face-to-face at SIG (Special Interest Group) meetings and information was freely exchanged. I was part of the Atari SIG in Dallas, and I really miss those get-togethers. The "communities" that exist today on the web today are pale imitations.

    The BBS was king back then, and I remember how much fun it was to connect to the local BBS and chat with folks, download software and play the door games. People think the massive online games are something new, but there was a plethora of door games on the BBS systems that were ongoing and had hundreds of players. They were all ansi based, but that didn't detract from the immersiveness of the games. Most online, multi-player door games had much more content than the online games of today.

    I remember connecting to the internet when it became available on Delphi, a dial-up service provider. It was text back then and we didn't have to worry about spammers, identity theft and viruses. Most of the information on the web was hosted at universities and all of it was free. And there was a wealth of information, both text and binary. For me, it was a sad day when corporations discovered the internet and had the idea to try and make a buck on it.

    On the professional side, the golden age was during the 1990s, especially during the late '90s up to the year 2000. The web was taking off and companies were leaping on the .Com bandwagon in droves. The year 2000 was fast approaching and people were realizing that many, maybe most, of their software had the "Year 2000 Bug". There was more work than anyone could handle, and salaries were at an all-time high.

    It is ironic that the web, which fueled the IT explosion, also brought about the beginning of its demise. When the .Com bubble burst and the stock market crashed, companies blamed the IT departments and the backlash was swift and devastating. I lived in Dallas at the time and I remember how many companies were folding up at an alarming pace. At one point there was a 60% vacancy rate in office space in Dallas; a sign of just how severe the crash was in the business world.

    In my opinion, the country still has not recovered from the .Com crash, and never will. Outsourcing has become the method of operation for companies now, and although there are a few pockets of activity in the country, it isn't and never will again, be the the robust business it once was. The U.S. was once the leader in IT, but that is slowly shifting to Europe and Asia and that is where it will remain in the coming years.

  • E.K.V: When you think about your programming career, is there anything you would change if you had the opportunity to? Anything you would have learned that you didn't or something?

    R.C: Not much I think, other than maybe doing more web programming and more graphics programming. Visual Basic, while not taken seriously by some programmers, was (and is) the best development system for creating business applications.

    I managed to create some very sophisticated systems using VB, such as a rule-based expert system to score apartment applications and a distributed system that had components across the country all communicating with each other via a dedicated comm line. And everything in between.

    VB was quite good to me and if I had to do it again, I'd probably stick with VB and application development.

  • E.K.V: If you look at present day and maybe a bit of tomorrow, where you do see yourself as an programmer?
    Is programming only a hobby for you now on or do you have plans or even open doors to some professional projects?

    R.C: I don't expect to do any professional programming in the near future at least. Right now it is a hobby and I am enjoying working on my own projects, silly as they are. :)

  • E.K.V: So, after years of programming career, coding can be fun with out intention to fill freeze with food? You havent had "ok, thats enough, no more computers" moments?

    R.C: No not really. It is my hobby, so I enjoy messing around with programs, games and of course I use it for writing. I'll probably keel over the keyboard one day and they'll have to bury me with it. :)

  • E.K.V: When you are experienced programmer, is learning new things harder or easier than it is for beginners?

    R.C: I think it is much easier. No matter what programming language you use, the basic ideas are the same. The key to programming isn't understanding the syntax of a language, it is the ability to look at a problem, break it into its component parts and formulate a solution based on the different components. Once you understand the problem, then you should select the language that will make solving this problem as easy as possible.

    A programmer should have more than one language in his or her toolkit. An arcade game may be easier to create using FreeBasic, but a GUI application is easier to create using Delphi or Visual Basic. A programming language is just a tool, and you should use the right tool to solve the problem at hand.

    There is a tendency in programming circles for a language to become a religion. If you don't use XYZ language then you're anathema. The reality is that there isn't a single language that can do it all, and there never will be.

    The beginner should start out, not with learning a particular language, but learning how to analyze the problem. Once they understand the problem, that is, understand the components that need to be built that when put together solve the problem, they can then shop around for the best language to help them solve that problem. You never start with the programming language; you always start with the problem. The language is just the means to the end.

  • E.K.V: So newbie who's asking wich language would be best to learn, is kind of bit out of track allready? Maybe he should reform hes question?

    R.C: In my opinion, yes. How many times do we see a post on a forum about the next great game that someone is going to make (newbie or seasoned), and that is the last of it? The problem isn't the programming language, it is that the programmer doesn't know how to analyze the problem well enough to create a programming solution.

    I would even say that this lack of analysis and clear objectives extend to the design and creation of the languages themselves in some cases. If the language designer doesn't have a clear plan on what they are trying to achieve, how can the programmer apply that language to a problem?

    If I were to teach a class on programming, I wouldn't even introduce a programming language until the second semester. The first semester would be about how to think logically, how to analyze a problem, how to build and maintain an action plan and how to organize a project. Once you understand these basic concepts, using a programming language to build a solution will be much easier. This is the approach I am taking in my game design book.

    Programming has always been 80% planning and 20% coding. The reason people spend so much time programming, and fail more times than they succeed, is that they do not have a clear plan when they build their solution. And often times when they do actually write the program, it is such a buggy, disorganized, unmaintainable mess, that you never see version 2 of the program.

  • E.K.V: What do you think of the corporate world's evolution of software development tools? As in do you believe that the compiler makers (and other software development/analysis) makers have done a good job at keeping up with the new upcoming needs of the IT industry?

    R.C: The problem is that corporations, by and large, do not know what they need. You have desk monkeys that can barely check their email making IT decisions. The idea of proper analysis applies here as well. If you can't analyze your companies needs, you shouldn't be making the technology decisions, but that is the case in a lot of companies I worked for, especially the large corporations.

    However, I think that by and large, the companies producing the tools have done a good job at extending the capabilities of the tools. As I said before, there is a wealth of programming tools available today that weren't available when I made my living as a programmer, and many of them are free or low cost.

    In some cases the intent, such as the Express versions of Microsoft's programming tools, are intended to build brand awareness, but that doesn't detract from the fact that they are available for free with few or no strings attached. It is paradoxical that so many quality tools are available, and yet the industry is declining at an alarming rate. However, for the hobbyist programmer, these are good times indeed.

  • E.K.V: We have those desk monkeys here as politicians too so i do know what youre talking about.
    Do you see any chance that amateur programming scene could really challenge professional side in future?

    R.C: I think it already has as evidenced by the Open Source movement. Even though many, maybe most FOSS software is created by professional programmers, most of it is outside the corporate channel and many companies such as Microsoft have felt the impact of the products.

    The US lags behind Europe in OS adoption, but I think that corporations will look at OS much harder here as a way to cut costs which is always what companies want to do to boost the bottom line. They have cut the staffing to the bare bones and outsourced what they can, so the next step will be to adopt low-cost and free OS software to lessen the impact of the overbearing license schemes software companies have created here in the US.

    One thing that is interesting in this respect is game software. The indie game producers are entering a renaissance I believe since many game developers have abandoned the PC market for consoles. This is a real opportunity for indie game developers on the PC if they can offer good game play at a reasonable cost. It is quite easy now to get into game development with some free and low-cost game engines available on the internet.

    Even in the console arena, indie game developers have made a mark, such as in the XBox online marketplace. Many of the indie games are just as popular, maybe more popular than the commercial games.

    The next big crash in the tech industry will be in the game market. What many people don't realize is that the game industry, especially the consoles, is cyclical. I have been around since the beginning of the console industry and have seen it happen several times.

    The new breed of console hardware and software are so expensive to produce that the price points are reaching the limit on what the general public (where the companies really make their money) are willing to spend. Right now the shelf-life of an average game is about a week. Not nearly enough time to get a decent return. I expect the WII to do well, but I have doubts about the others.

    One area of the PC market that will grow however is the online multi-player game. It is relatively cheap for the gamer to enter and offers extended game play. As long as the online game companies keep adding new content to their games, they will continue to do well.

    Another area is the hand-held market, especially mobile phones. Since most phones are powered by Java or embedded Windows, and the games are easy and cheap to produce, this market will continue to expand. If I were to start a game company, it would be making games for the mobile market.

  • E.K.V: What you would now say to yourself if you could give some advice to yourself in that age when you started learn programming?

    R.C: Just that as a programmer you need to expand your horizons. Nothing stays the same very long in the tech industry and the more diverse you the better it will be in the long run.

Now, we do know more about that name and thoughts behind of it.
I wana thank rdc about this interview.

E.K.Virtanen





20 Minutes into the Future...

Hartnell


I love that line. "20 Minutes into the Future." Every episode of Max Headrom began with that line, suggesting that the "future" predicted by the show was already here even before the show was over. Max Headrom made many futuristic predictions. The internet has happened. Video phones didn't quite catch on the way they were supposed to but webcams have. People do place bets on the outcome of television shows, at least in Las Vegas.

I'm going to try a similar trick. I'm going to predict whats coming for us amateur programmers in two years. Well, let's get started with some WILD predictions.

2 Years Into The Future
  • "Real" programming languages will be seen as simple, even by noobs.
  • "Game Maker" programs will be seen as overly slow and bloated, even by noobs.
  • So-called noobs will be few and far between because new programmers will learn so fast.
  • It will be normal to see someone create their own working programming language.
  • It will be normal to see someone making their own working operating systems.
  • There will be a rise of "virtual machine elitists".
  • The Atari 2600 and NES homebrew will be easy and accessible.
  • The C64 is going to make a HUGE comeback.

Some wild predictions, eh? I know, the last seems completely improbable. Surely someone stuck a fork in the C64 a long time ago. Not exactly. I'll explain how I justify making these predictions later. Let's get started with .tut.

Introducing .tut

The problem with noobs is a lack of noob material, not that they are unwilling to learn. It's just that they have nothing to learn from. Experienced programmers in various communities are very helpful, but can't think of any tutorials to write. I've created .tut as a solution.

.tut is a tutorial framework. It's a list of concepts. It's a recipe for complete for all the tutorials anyone could ever possibly need. Just add a programming community and watch the complete noobs disappear. And the more programming communities that implement .tut, the faster and better .tut becomes. The tutorial writer no longer writes alone.

Before going on, let me define some concepts. First, when I speak about .tut, I am referring to the .tut framework itself. If i use the term .tut implementation, I am speaking of a single project using .tut to create tutorials. Lastly, when I speak of .tuts, I am speaking of all the projects using .tut. Ok, lets move on.

Let's look a little closer at what .tut is. .tut is a tutorial framework that is an outline of concepts. A recipe if you will. To illustrate this, take a minute and think of the concepts you would include in a simple tutorial about comments. Really, take a minute, write these down and then come back to this article.

Ok, got it? Even a simple commenting tutorial is sometimes hard to write because even though you know all about comments, you don't know how to put all of it together in a tutorial. That's where .tut comes in. Let's look at the *current* section on comments :

    Comments
  • A comment is a reminder.
  • A comment is documentation.
  • Comments are ignored by the interpretor or compiler.
  • There are three kinds of comments.
    • Single Line
    • End of Line
    • Multi-line
    • If applicable, REM can be used to comment.
  • When to comment.
    • Commenting on easily understood code is over-commenting.
    • Only comment if :
    • You need to know how the code interacts with another part.
    • It is very cryptic

And there it is. Everything you need to write a first rate tutorial on comments. All you have to do is adapt the concepts to your programming language. In this way, .tut is simply a fill-in-the-blanks framework. The hard part of figuring out what to write and how to structure it has already been done for you.

.tut is also networked, meaning that all .tuts are wikis and they all know about each other. If you need help in a specific part, you can ask and get help from the people working on your .tut implementation. Because .tuts know about each other, they "copy off each other" as it were. If you're having trouble with the specific wording of something you can look at how the other .tuts did it. It's all open source, and its all based on .tut, so nobody can claim that you "stole their work". This not only helps established .tuts , but new .tuts will have a head start simply because there are other .tuts. True freedom of information in action.

Finally, .tut's center is the .tut framework. When a new concept is thought of that is not already in .tut, it is simply added. I'm sure there's something about comments that isn't in the current .tut framework that needs to be addded. Since each .tut implementation uses .tut as a guide, new things are being added all the time and nobody has to reinvent the wheel.

Currently, there are two .tuts with a third coming soon, probably by the time you read this. Here are they are :

.tut: http://www.gamedesignnovice.com/wiki/Dot_tut
FreeBASIC Game: http://fb.gamedesignnovice.com/w/FreeBASIC_Game
Brutus2D Game: http://b2d.gamedesignnovice.com/w/Brutus2D_Game
Basic4GL Game is coming soon.

.tut is growing fast. In a year you should be able to look at these .tuts and be able to read tutorials that advance at a comfortable curve from complete noob to making very good 2d games. In two years, the sky is the limit.

If you are interested in wiki hosting for a .tut implementation, you can email me a shawn.hartnell@gmail.com . I will gladly help you get it started.

Introducing .docs

Another problem with eliminating noobdom is poor documentation. My solution to this is .docs, a documentation framework similar to .tut. At this point, I'm focusing mostly on .tut, but .docs is just around the corner. I'll update you next issue.

The Effect of .tut

.tut will accomplish my first three predictions :

  • "Real" programming languages will be seen as simple, even by noobs.
  • "Game Maker" programs will be seen as overly slow and bloated, even by noobs.
  • So-called noobs will be few and far between because new programmers will learn so fast.

Now, for the other five.

6502 : ASM for Everyone

I like to talk about the old days of computers. It was a time when the amateur had "bare metal" access and wasn't afraid to use it. If you wanted to poke around in your computer's memory, you could mess things up, but who cared? Rebooting your computer took all of two seconds.

Today, poking around in your computer's memory is not only dangerous, but extremely annoying. Windows takes much longer than two seconds to load. Another problem with getting down to bare metal is that its hard for a beginner to understand new computers work on a machine language level. The fundamentals are the same, but the end result is much more complex. The processor of the C64 had three registers. The processor of my computer has so many registers I've never bothered to count them all.

Wouldn't it be nice if we could, at least in the beginning, work with ASM with the same simplicity that C64 programmers had? Well, we can. Not only are we capable, but we have better tools than they had to work with. It just isn't that well known --- yet.

Before I go on, let me give a hint at what you will be able to do. Getting down to bare metal in 6502 allows you to master a microprocessor. You will be able to write your own programming languages, compilers, operating systems, etc. In short, everything that us computer geeks have always wanted to do but never have had the information or tools available to do it. You will also be able to develop for the Atari 2600, NES, and Super NES. The basic bare metal of the 6502 carries over. Mastering 6502 will give you a huge head start in modern ASM as well.

We can do this because of the evolution of the emulator. In the beginning, the mission of most emulator programmers was to get the emulation as perfect as possible. Overall, they've accomplished that. Now, the focus is on creating emulators that are good for developing new games and applications for these systems. Tools that we could only dream about yesterday are now coming down the pipe. These tools will revolutionize ASM and homebrew as we know it.

Let's take No$64. It emulates the C64, which has been nothing special for a long time. However, when you open up No$64, it looks like no emulator you've ever seen.




This may look like the landscape of Venus to some. Let me explain what you are seeing. The big window is the disassembler. It shows the memory as ASM, similar to what you would see if you were programming in ASM. This can be updated in realtime, so you can see exactly what your program is running. You can even pause the emulator, assemble new instructions into memory, and then start the emulation where you left off.

Below that something like a hex editor for the memory. Here you can poke around in the memory here like you could with a hex editor. You can monitor and change memory locations with it.

The top box to the right shows what's in the program registers and below that is the stack. Don't worry if you don't understand my explanation. Just know that you can learn what all this means in about 10 minutes. It's really that easy. What it all adds up to is a complete bare metal development environment for the C64. You can go in and figure out how the BASIC interpretor works and examine the OS kernel directly.

In case you're wondering there's a No$ emulator available for the GameBoy, NES, and Atari2600.

The tools are here now and this article is about the future. Let's get back to the future.

With this kind of access to "bare metal" it will naturally follow that binary madmen will start working on their dream projects. Some already have and there's quite a few websites dedicated to it. In short term future, you'll see the first of these projects wrapping up. Expect about 10 new OSes for the 64 and other 6502 related processors. Expect about 50 new programming languages, including dialects. At the end of two years, you should see the first signs of this same spirit coming to native assembly and virtual machines.

The general purpose virtual machines will come first, and they will be constantly updated to be faster, better, and easier to use. Of course, by virtual machine, I mean a complete environment like No$64, not just the machine itself. Some of these will turn 6502 on its head. Most of these will emulate native PC assembly. One or two will become widely used. In any case, it will be much more common today than it is now.

I plan on doing my part. I have a set of wikis planned to help speed up this process. You'll get the details on the first two of those in the next issue of PCopy. I will also be publishing a new zine, 6502 Madness. It will cover exclusively things happening in the 6502 ASM world and be published offset to PCopy, meaning two weeks after PCopy, 6502 Madness comes out. Two weeks after 6502 Madness, its only two weeks to PCopy. I encourage you to fill in the holes with your own publication.

The Last Predictions :

The last predictions on my list should makes some since now. I will list them, and get started making the future a reality.

  • It will be normal to see someone create their own working programming language.
  • It will be normal to see someone making their own working operating systems.
  • There will be a rise of "virtual machine elitists".
  • The Atari 2600 and NES homebrew will be easy and accessible.
  • The C64 is going to make a HUGE comeback.

See you in two years.

-hartnell





Database Design - A Complete Study (Part Four - Database Reporting)

MystikShadows


INTRODUCTION:

Welcome to this 4th and final part of the complete database study series. As I mentionned in the 3rd part of this series, we will be covering the part of Structured Query Language that can help you draw useful information and reports from your database. We will be using the tables we defined in Database Design - Part 2 for the examples in this final part. These techniques should help you in any database related project you might have. It doesn't matter if your database are for a business management, a list of contacts, a table is a table and as such you can create reports from them just by using Structured Query Language.

The best way to learn is to follow examples and that's just what I'll give you. We'll start with some very basic, but still useful, examples and work our way to more elaborate reports. This will make the SQL statements more and more complex and should show you, gradually more of the things to think about when undertaking the more complex reports. It will also show you more SQL statement clauses that you haven't seen yet in the previous part of this series. With all this you should be pretty well equipped to do what you need with your database projects.

DATABASE REPORTING BASICS:

When you think about it, a report wouldn't give any useful information if the tables you're reporting from didn't have any data to work with. By this I mean that finding out if there's any data is one of the most useful report you can have. Of course, there are many criteria you need to know about when you're trying to find out if there's anything to report on. In many cases, a report will be based on dates. You'll want to know, for example, if there's any records in the database that fall on a specific date, or a specific range of date. If you're working from he inventory table you might want to know how each inventory category that you defined are doing. This is useful, in this case, when you want to know if you're in overstock (if some of the products you bought just aren't selling enough so that you don't buy any of those the next time around. Likewise, there would be a great interest in knowing which products do sell so you can attempt to avoid having to backorder the best sellers. As a business manager you might want to know how much money you made this month, or this year so far. The list of reports you might need can be close to endless.

Alot of books you'd read on SQL would do just what I did and use business examples for reporting. You might also notice that SQL statement and syntax is probably as close to the english language as it can get. The main reason for that is that SQL was originally invented for business executives. It was supposed to be a means of giving these individuals and easy tool to use to get useful information about their business. Once it was created though, many people simply didn't have the time to learn it or learn it well enough to do anything useful with it. So when you think about it, SQL is popular today because it was a failure at what it was supposed to originally do. Today SQL is used by programmers and database managers, but there isn't alot of business executives that actually know SQL. Ironic isn't it? thank you moneo for reporting that fact to me.

Whether it's for a business, or not, when you create a table or a set of tables, you need to ask yourself what can this table give me for information. Typically, the answer to that question can and would end up as a report (a simple or a complex one). Now enough with the theories and reasons, let's get down to the actual reporting. No matter what answer you come up with when you ask yourself what a table can give back for information, since you are retrieving information from the database, you guessed it, all reporting clauses are added to the standard SELECT statement. So let's start shall we?

THE COUNT AND SUM CLAUSES:

I think these two names pretty much speak for themselves, and COUNTing and SUMmarizing are the best aspects of reporting. Very often you'll find yourself asking questions like How many things did I sell? how much money did I make so far? In business, these are essential questions. COUNT and SUM are of course the best way to do it. Unless a simple select which would give you back all the records you're asking for in a list, COUNT will only give you how many of what you asked for. this saves you the task of counting all the records you'd get if you didn't use the COUNT statement. Here's a simple example.

SELECT COUNT(*) FROM InvoiceHeader;

This will tell you how many invoices you've created so far. In this example, I used COUNT(*) because it is acceptable, however, when your tables build up data, this won't be the best way to use the COUNT clause. It will be highly recommended to specific one field instead of the *. Consider a table with 100 fields and 3 million records in it. COUNT(*) is quicker to type but longer to execute than COUNTing a specific field since even if it doesn't do anything with the fields they are still all part of the query. So, to use just one field you need to specify it in a COUNT(FieldName) clause. For example:

SELECT COUNT(InvoiceDate) FROM InvoiceHeader;

wich field you use doesn't really matter as it's contents have no meaning for a COUNT since it only counts records in a table, not values of a field.

If you want to know how much money that represent, you would use the SUM(ColumnName) clause. Here's an example for the SUM clause.

SELECT SUM(GrandTotal) FROM InvoiceHeader;

This will total all of the GrandTotal fields from the InvoiceHeader table and give you the total amount that all the invoices have. Now unlike the COUNT statement, SUM does consider field values and contents, So the Field you choose to put in a SUM clause must be of a numeric nature or it will return an invalid type error. All these short examples had to do with all the records of a table. Typically you can shorten the range of needed records. You might, for example, want to know many invoices you created so far this month or this year. You can do this by simply adding a WHERE clause to your select statement. Our Invoiceheader table has an InvoiceDate field and we'll use just that to determine how many records we need to consider for this report.

Here's an example:

SELECT SUM(GrandTotal)
  FROM InvoiceHeader
 WHERE InvoiceDate >= '2005-01-01';

Simple as that really, this will give you back every record from January 1st to today. Say you wanted to know about the invoices for the month of september only, you would only add a condition to the where clause. Like so:

SELECT SUM(GrandTotal)
  FROM InvoiceHeader
 WHERE InvoiceDate >= '2005-09-01' AND InvoiceDate <= '2005-09-31';

As you can see, filtering out unwanted records is easy to do. It doesn't have to be a date field either. From the inventory table, you could want to know all the products in a given category. In this case, it's the same type of SELECT query. For example:

SELECT SUM(GrandTotal)
  FROM Products
 WHERE CategoryID = 10;

By adding AND and more conditions to this example, you could find out about more than one category at a time. Again it all depends on the answers to the question "what can this table or set of tables give me back for information?". Now you know how to filter the unwanted records. Next comes how to break the records that you do want into signicant parts. This next section will show you a new SQL statement that is perfect for breaking down these records into any kind of workable groups.

THE GROUP BY AND ORDER BY CLAUSES:

A perfect example of this kind of GROUP BY use is our inventory table. Some of the many questions any inventory manager wants to be able to answer are: What do I need to order? How much did I sell each month? What are my hottest selling categories of products? and so on. Let's the the last question for example. As you can see in our table definitions, each product has a CategoryID field. By grouping the report on this field we can quickly find out how many types of products we have in our inventory. The SQL statement for this example would be:

  SELECT CategoryID, COUNT(ProductID)
    FROM Products
GROUP BY CategoryID;

Assuming we have 5 categories of products and typically more than one product for each of these categories, this example would return:

CategoryID
---------- --------
1          121
5          100
3          32
2          58
4          101

You probably noticed that the 2nd column didn't have any name. When you are using a function like COUNT() and SUM() it doesn't assign a name to that column. You can use the AS statement to give it a meaningful name. This is good practice especially when you have more than one of these calculated columns in a report. Likewise, if you were using a programming language like FreeBasic and wanted to get the value of that column, you couldn't do it if it didn't have a name. Here's the same example that will name that column Products:

  SELECT CategoryID, COUNT(ProductID) AS Products
    FROM Products
 
GROUP BY CategoryID;

Which would give you:

CategoryID Products
---------- --------
1          121
5          100
3          32
2          58
4          101

The same technique can be used to give better names to column headers if you feel they are not clear enough for the common user of this statement but if you took the time to name your field names properly, when you defined your table in the first place, you'll hardly need to rename them in a report. Note also that these categories are not in order, and many times your results won't be in the order the should be. The SELECT statement can be added an ORDER BY <FieldName> clause to allow to sort the result in any order you want them in. This Statement will order the categories for you.

  SELECT CategoryID, COUNT(ProductID) AS Products
    FROM Products
GROUP BY CategoryID
ORDER BY CategoryID;
 

As simple as that, adding that ORDER BY Clause essentially sorts the results in the field order you specified.

CategoryID Products
---------- --------
1          121
2          58
3          32
4          101
5          100

When using the GROUP BY clause, you can, add WHERE conditions just like any other SELECT statements. Let's say, for example, that we just wanted the first 3 categories for whichever purpose. We create our SQL statement like this:

  SELECT CategoryID, COUNT(ProductID) AS Products
 
    FROM Products
   WHERE CategoryID <= 3
GROUP BY CategoryID
ORDER BY CategoryID;

The where clause needs to be before the GROUP BY clause as shown in this example. You could use more than one condition too like any other SELECT statements. This particular example would return:

CategoryID Products
---------- --------
1          121
2          58
3          32

As a rule of thumb, it's very important that you remember one thing. If you're going to need to groub by a certain field, that field must appear in your SELECT statement's list of selected fields. If you can remember that alot of unnecessary confusion can be avoided. Now so far it seems pretty simples doesn't it? By applying these techniques to any of the tables (maybe with different fields and querying condition) you can manage to get some useful information out of them. However, these example all concern one table at a time. The real power of SQL reporting is when you need information, presented in an intelligent and workable way, from more than one table. The next section will cover this aspect of reporting. It will combine what you've seen so far and integrate JOIN statements to mix tables together in a way that can't be done by simple SQL statements alone. But with these additions you'll quickly see how useful SQL was meant to be other than to maintain a table and it's data.

REAL WORLD REPORTING TECHNIQUES:

Keep in mind that the simple examples I've shown so far will come in handy because they can present you with information you need to know. However, when you can related 2 or more tables to create a report, things can begin to become even more useful. Once again examples are the best way to teach you reporting so that is just what I'll do. Let's examine a couple situations that are related to our table definitions and see how we can get the information we want out of them. Remember that if a select involves more than one table, in reporting, it will more than likely mean that you'll be using the JOIN statement.

As a first example, let's pretend that we want to know how many products were sold so far. However, to make our report a bit clearer to read, we will want to display the ProductName, it's price, how many of them were sold and the total amount of money it represents. Believe it or not, this type of question is asked very often in business. Anything that can give back how much money anything made is a question you'll hear often in your career as a developer. So then, taking this example, we know we'll need the Products table. since we want to know how many of them were sold we'll also need the InvoiceDetails table where each item sold is listed here along with the quantities sold. We'll want enough information for the report to be clear and easy to read so let's see what the statement can look like:

   SELECT Products.ProductID,
          Products.ProductName,
          Products.CurrentPriceSold,
          SUM(InvoiceDetails.QuantitySold) AS Quantity,
          SUM(InvoiceDetails.LineTotal)    AS AmountTotal
     FROM Products
 
LEFT JOIN (InvoiceDetails) ON (Products.ProductID=InvoiceDetails.ProductID)
 GROUP BY Products.ProductID
 ORDER BY Products.ProductID;

This example only uses two tables to get all the information it needs. The LEFT JOIN statements makes sure that the data gathered is synchronized on the ProductID from the Products table and not the InventoryDetail table (if it was from the InventoryDetail, you could get more than one record for the same product and we don't need that in this particular case). This SELECT statement would return something like this:

ProductID ProductName             CurrentPriceSold Quantity AmountTotal
--------- ----------------------- ---------------- -------- -----------
1         Panasonic KXP-1180      100.00           4        400.00
2         HP 5P Lazer Printer     300.00           2        600.00
3         NEC Accusync 70 17"     250.00           3        750.00
 
...       and so on for all the products in the Products table.

Can you see how much more readable this is compared to manually selecting all kinds of things independantly from these two table to try to get the same results but in a more complicated and less readable way? that's one of the advantages of the reporting part of SQL. As a second example, let's take this from a different perspective in the business. Let's assume that the boss wants to know how much money he made so far this year and that he wants those results on a monthly basis. Basically we'll need the same kind of information to give him those results, but we'll swap the Products table with the InvoiceHeader table since that table has the invoice dates that we can work with to give him the monthly results. Let's see how we can give him just that. The SQL Statement will look like this:

   SELECT MONTH(InvoiceHeader.InvoiceDate) AS MonthNumber,
          COUNT(InvoiceNumber) AS InvoiceCount,
          SUM(InvoiceHeader.GrandTotal) AS InvoiceAmounts
 
     FROM InvoiceHeader
LEFT JOIN (InvoiceDetails) ON (Invoiceheader.InvoiceNumber =InvoiceDetails.InvoiceNumber)
    WHERE InvoiceHeader.InvoiceDate >= '2005-01-01' AND InvoiceHeader.InvoiceDate <= '2005-12-31'
 
 GROUP BY MonthNumber
 ORDER BY MonthNumber;

This particular query return no physical records from any of the tables it uses, it just brings back results. Typically, a boss doesn't care if he sold a NEC 17" monitor, he'll want to know how much money it gave him instead. This example would return something like:

MonthNumber InvoiceCount InvoiceAmounts
----------- ------------ --------------
1           59           2439.43
2           34           1834.22
3           58           2308.43
4           78           4898.32
5           120          6421.15
6           211          11243.24
7           377          15234.42
8           105          5734.52
9           77           4968.76
10          56           3452.42

And there you have it, this is exactly what the boss asked for so to speak. Now this might not seem very useful just looking at numbers. However, to those that want this, one of the crucial information you can extract from this report is that the highest selling months are June (6) and July (7). This can help them make sure they buy enough products to supply the higher demand they can expect this coming year in those months. Now how useful is this report I ask you? And basically that's what reporting is all about. If a report you are asked to create doesn't seem to return any intelligent information to you, or to the one that asked you for this report, it's not worth doing, period.

This brings us to the other question to ask yourself, that question is: "How useful is this report to the one that asked for it". A business manager, an accountant, an inventory manager and a sales department could all want somekind of information based on the inventory and invoices. However, they will typically want that information presented to them in a very different way that is useful to them alone. The first report we did in this section on the products would typically be useful for the Inventory manager. In this last example, well take that first query, and modify it a bit so that it will return, for example, all the sales by category. This is another often asked for report. And as you'll see here, we will need to include a 3rd table in the query to get the name of the category and he Products table will just return how many different products they have in the given category. Here's the SQL Statement:

   SELECT Categories.CategoryID            AS Category,
          Category.CategoryDescription     AS CategoryName,
          COUNT(Products.ProductID)        AS ProductCount,
          SUM(InvoiceDetails.QuantitySold) AS Quantity,
          SUM(InvoiceDetails.LineTotal)    AS AmountTotal
 
     FROM Products
LEFT JOIN (Products)       ON (Categories.CategoryID=Products.CategoryID)
LEFT JOIN (InvoiceDetails) ON (Products.ProductID=InvoiceDetails.ProductID)
 GROUP BY Categories.CategoryID
 
 ORDER BY Categories.CategoryID;

It's as simple as that, we selected the fields and functions we needed from the different tables (Categories, Products and InvoiceDetails) created the LEFT JOINs we needed and the report is ready. Here's a typical output from this statement:

Category CategoryName       ProductCount Quantities AmountTotal
-------- ------------------ ------------ ---------- -------------
1        Monitors           121          59         2439.43
2        Printers           100          45         1834.22
3        Scanners           32           132        2308.43
4        Mices              58           564        4898.32
5        Hard Drives        101          254        6421.15

As you can see, it's all about taking the time to sit down and ask yourself what you or the one that needs the report really wants to know and how you need to present it so that you/he/she gets the exact answer they are looking for. This is in the case of a business. Alot of you might have your own forums for example and you could play with the tables of your forums to get specific information from them too like the average number of posts per month you get and other useful information you might need to know when managing a forum.

Reporting queries do have their limits however. In some very complex cases You might not be able to get your answer from one single SQL statement alone. Therefore, in these situations you'll need a tool that helps you get temporary results that you can work with to finish the reporting task completely.

ENTER THE TEMPORARY TABLES:

SQL does provide a means of temporarily saving a subset of records when needed in order to be able to use these temporary results to complete a reporting task. MySQL differs from other databases in that it doesn't support the SELECT INTO TABLE construct. Instead you can use an INSERT INTO Table construct to get the job done. The idea is that you would create a temporary table (designed to be used then dropped) from which you can execute another SELECT statement that will finish the job you started. As such, this technique will create the table, put the information that your first SELECT query will get into it, and then, in a second SELECT statement you would get information from that temporary table, JOIN it into other tables and get the final results you were hoping to get in the first place.

The example I'll use here could have probably been solved by a single SELECT statement. However, for the sake of clarity and your understanding, it will do the job quite well. Let say that you wanted to know how many new customers you had each month of this year. Let's also assume you wanted to know how many invoices and their totals that they purchased as well as the number of items they purchased for that month. Finally let's assume you want that report broken down by month. This typically involves two steps. The first step is to collect the data will need to work with. We'll put that information into a temporary table called CustomerData Once we have that, we'll use a 2nd query to report on the temporary data and break it down by month. Here's the query to gather the data into the temporary table.

INSERT INTO CustomerData
     SELECT Customers.CustomerID             AS Customer,
            Customers.CustomerSince          AS CustomerDate,
            MONTH(Customers.CustomerSince)   AS CustomerMonth,
            InvoiceHeader.InvoiceDate        AS DateOfInvoice,
            MONTH(InvoiceHeader.InvoiceDate) AS InvoiceMonth,
            SUM(InvoiceDetails.QuantitySold) AS Quantity,
            SUM(InvoiceDetails.LineTotal)    AS AmountTotal
 
       FROM Products
  LEFT JOIN (InvoiceHeader)  ON (Costumers.CustomerID=InvoiceHeader.CustomerID)
  LEFT JOIN (InvoiceDetails) ON (InvoiceHeader.InvoiceNumber=InvoiceDetails.InvoiceNumber)
 
      WHERE Customers.CustomerSince >= '2005-01-01'
        AND Customers.CustomerID = InvoiceHeader.CustomerID
        AND InvoiceHeader.InvoiceDate >= '2005-01-01'
        AND InvoiceMonth = CustomerMonth
 
   GROUP BY Customers.CustomerID
   ORDER BY Customers.CustomerID;

This gives a table with 7 columns that have the temporary results in them. Once we have that in the temporary table, we simply issue a 2nd SQL query based on those temporary results. here's what that might look like:

SELECT CustomerMonth,
 
         COUNT(CustomerMonth) AS NewCustomers,
         COUNT(DateOfInvoice) AS InvoiceCount,
         SUM(Quantity)        AS ItemCount,
 
         SUM(AmountTotal)     AS TotalAmount
    FROM CustomerData
GROUP BY CustomerMonth
ORDER BY CustomerMonth;

As you can see, in very complex situations, this use of temporary table really help keep things into perspective and keep the goal of the report clear. This example would give back a report that could look like this:

CustomerMonth NewCustomers InvoiceCount ItemCount InvoiceAmounts
------------- ------------ ------------ --------- --------------
1             23           59           121       2439.43
2             34           56           97        1834.22
3             58           87           134       2308.43
4             78           128          231       4898.32
5             120          185          335       6421.15
6             211          465          684       11243.24
7             377          624          734       15234.42
8             105          160          342       5734.52
9             77           149          522       4968.76
10            56           134          316       3452.42

This report would show again that June and July are he hot sellers, however, with the added information, we can now deduct that since the ItemCount is closer to the InvoiceCount in July, that they have been buying more of the same thing during those months and bought a more diversified list of items in the remainder of the year. After knowing this they could compare that against the inventory history to make sure they are right in their assumption (which, in most cases, would prove to be right). It's important to remember that the more complex a query gets, the harder it gets to follow when it does not return the required information. Therefore, extra care needs to be invested when building the more complex query as debugging it later will prove a bigger headache than needed. Note that if things get more complex, there is nothing to stop you from breaking the reports into more than one temporary table as you need it. The important thing is to keep the reporting queries as clear as they have to be to be maintainable. This way, when situations occur, you'll be better prepared for the process of following what's going on and knowing where the situation is more likely to occur.

THE OFFICIAL FINAL WORD:

And there you have it. We now conclude the 4th and final part of the database series. In these 4 parts, I believe we covered alot of ground as far as database design and management is concerned. I definitaly hope it is useful to your database projects whatever they may be. As I mentionned before, this series covered the MySQL implementation of the SQL syntax. When you go to other databases, such as Oracle, SQL Server and PostGres You'll notice that alot of what you learned here will definitaly serve you well in these other databases too.

As always, I've tried my best to be clear, concise, to the point as much as could. However, some of you might have questions about what you've learned in this series as perhaps I could have written certain parts differenly and make them even easier to understand. Fell absolutely free to send me comments, ideas and suggestions about this series, or any other tutorial I've written in QB Express so far. If you have specific questions, feel free to email me those questions so I can answer them for you to make sure you understand everything that I tried teaching in this (and other) series. Until I write again, happy learning, designing and coding.

- MystikShadows










Exit Issue

That's all folks. As told earlier we believe were gettin better and better with this one in every issue. We ain't professionals but maybe in future we are atleast good ones in this. Praise or blame, read or dont, next issue comes out about in a month.

And naturally any BASIC related contribution (or news brief) is always more than welcome here. Got an article, a rant, a tutorial, a review that is either BASIC programming related or about things that were created in a BASIC flavor (any BASIC, any OS). If you want it publised in PCOPY! just send it to us email and we'll take care of the rest, no templates, no guidelines per se (cept for the respect of other people's hardwork creating the BASIC dialect you want to talk about.

You found something out about one of the basic program or related projects and you'd like to share that news with us to be published in the next upcoming issue of PCOPY! just send us an email and we'll take care of adding it to our news items for you.

We hope you enjoyed this issue of PCOPY! and be sure to be on the lookout for the next upcoming issue. Until next time, Happy Coding!

The PCOPY! Editing Team.





Copyright © Stéphane Richard (MystikShadows), E.K.Virtanen and contributors, 2007.
All rights reserved. Our layout is based somewhat on QBE.
 


Comic #7 Comic #8, Comic #9, Comic #10