Intro to database week 3 - SQL gets real

Week 3 - SQL gets real

Up until this point we've mostly been talking about the theory of databases and how they generally work. This week was nice because we started to chug along on a real database. Since I have no experience writing SQL commands the first thing I did was hit the ground running by downloading an SQL training app on my phone so I could do problems on BART. 

My phone runs Android so there was a nice knowledify SQL app to run through lots of practice problems. The video below shows how this app works if people are interested. This app is nice for a few reasons. First of all it has auto-complete so you don't feel like you're wasting your life typing things out in full for no good reason. Overall the user interface is very nice and it has a quick guide to let you brush up on basic commands as you learn them. The app looks like it runs out of questions pretty quickly but I think it is a good free way to get some repetition in for someone just getting started like me.  

With basic SQL queries in hand the week's homework was filled with setting up example databases and querying them. I think if we stopped here I could do some useful things in my side projects. For example just with what we learned in this weeks homework we can download the cx_Oracle python library and start parsing files and loading the information into a a database. This let's us scrape data from social media sites to harvest information about people and automate tasks based on that information. Without the information from this weeks topics I would be storing that information in a comma delimited file which is a bit of a mess. Having the information in a database makes searching for data like last profile view data simple in python. For example after harvesting used id links you could search for pages you haven't visited yet by doing this:

import cx_Oracle
db = cx_Oracle.connect('linkspider', 'password', 'localhost:1521')
cursor = db.cursor()
cursor.execute("select User_ID from linkedINUsers where LastViewed is null and rownum < 10")
userIDs = cursor.fetchall()

cx_Oracle wants you to get a cursor object which let's you act on the database after you connect to a database and get a database object. From there you can use the cursor to start performing the commands that we learned this week. Select rows, modify them, add new ones, etc. By using rownum in your query you can limit the results returned because if your bot tries to view too many pages too quickly you'll get caught! 

Before you send your bot out to start harvesting you'll want to get some data of course. The example I wrote below shows how to do that for a basic linkedin html file taken from the people you may know page. By seatching for "/view?id=" we can scrape a list of user names and store them in the database. The critical part here is that you must perform a commit on the database. Otherwise your results won't be stored! You also probably don't want to waste time trying to commit duplicate user ID's, so it is nice to keep track of them as you go along. Another important aspect is that when you try to add rows to the database that already exist then cx_Oracly will throw an integrity error. So make sure to catch that and do whatever you like. 

import sys
import cx_Oracle
db = cx_Oracle.connect('linkspider','password', 'localhost:1521')
cursor = db.cursor()
sourceFileName = sys.argv[1]
sourceFile = open(sourceFileName, "r", errors='ignore')
buffer = sourceFile.read()
index = 0
done = False
urls = []
OK = 1
matchString = "https://www.linkedin.com/profile/view?id="
matchStringLen = len(matchString)
while OK != 0:
   index = buffer.find(matchString, index)
   if(index == -1):
      OK = 0
      continue
   index += matchStringLen
   index2 = buffer.find("&", index)
   match = substring = buffer[index:index2]
 
   duplicate = 0
   for url in urls:
     if(match == url):
           duplicate = 1
   
   if(duplicate == 0):
      urls.append(match)
      try:
        cursor.execute("insert into linkedInUsers values('" + match + "', NULL, NULL, NULL, NULL)")
      except cx_Oracle.IntegrityError :
        print("Already in database")
       
   index = index2
db.commit()

Overall I think this was a solid week. We learned some really useful tricks to create and use databases. Now we just have to make sure to keep going otherwise we could end up thinking we know what we're doing, which would end up creating a real mess! At least now we could do basics like monitor home automation systems, aquaponics system sensors, and other simple projects. Just don't go off trying to apply for a database administrator job at a bank! 

Intro to databases week 2 - A relationship with Algebra

This week was serious meat and potatoes business: relational algebra and some SQL.

I know what you're thinking: what's relational algebra? Well it's like your logic & computation class met SQL and decided it didn't want to have a good structure and be a real language. So relational algebra gives us some example ways to perform operations on database tables in a bit of pseudo code. Useful keywords in this area include the following including simplified description:

SELECT - pull some columns out of a table.
PROJECT - pull some rows out of a table.
UNION - join two tables that share columns
INTERSECT - join two tables that share columns only on the rows that match
DIFFERENCE - subtract one table from another, giving you the first table with the values in the second removed from it when they match
PRODUCT - makes a super table that is the combination of the two multiplied out in almost every combination. it generated a bit of a mess but is used as part of the join operation.
JOIN - add some columns from one table to another to make a super table where the keys match.
DIVIDE - this one is kind of weird and hard to describe without a picture, but it's included for keyword completeness.

After getting all the keywords loaded up we had some exercises to do. Those exercises really served to provide the syntax for using relational algebra as if it was SQL in the final homework for the week. That seemed a bit akward like we should have just finished loading up SQL commands and then wrote it like that. But it is what it is.

From there we went on to reading but not much homework related to SQL. That's where it gets to the serious business part with real structure to send commands to a DBMS.  The big take away for me from this weeks section on SQL is that it seems like something that is straightforward to get the basics of but then tricky to become really good at. Being able to ask questions of a database is fine but the trick seems like it will be figuring out what questions to ask and how to organize the data to generate useful information. Then there is the ever looming problem of creating a database that turns into a mess so it seems important to follow along closely with these beginning sections.

Next week should be interesting as we continue our exercises but start to use an Oracle DMBS to run databases on our own computer. I am looking forward to the hands on work coming up.

Intro Database class week 1!

Hello internet,

This week's in-school learning was really about pre-loading students with all of the important vocabulary necessary for us to discuss databases. There were different key types, integrity between tables, and lots of examples of how you can go terribly wrong by knowing "just enough to be dangerous" with databases. I think the most important aspect of this was that just because you know how to use some database management software that doesn't mean you know what you're doing. Worse yet, if you actually convinced people you knew what you were doing you could create a massive time suck that becomes difficult to maintain. Hopefully though students don't head off on their own immediately and convince someone they can run a database.

From the homework we saw the importance of unique keys to identify rows within a table, and how to neatly link tables together with those keys. Things like SKU's on items in a store, student ID numbers, store codes, and all the other random numbers that don't seem to really give any info become magic numbers inside the database. This of course depends on being smart about how you set up your tables and which information you put inside of them. For example you probably don't want to list names unnecessarily when you can refer to people by number. Although they might feel a bit alienated during conversation, this actually ensures that you can update their name in only one location and have it updated correctly in all tables, without creating redundant names that have different spelling.

So overall I think it makes sense to continue this course and learn how to make useful databases instead of just trying to convince people I know a few key words related to database development. After all, it doesn't really matter what people think you know unless you're a shifty salesman. If you want to be part of an agile team that can manipulate matter and information to develop devices and harvest information to generate knowledge then you have to be able to do things.

Also I have some kind of fever & cold this week which makes me feel a bit dumb. It's always interesting being sick and seeing how it affects your head.

Have a good week.

CST205 week 7 - Dictionaries are great

This week was really all about the dictionary. There was some file IO and that was nice, but I played with that a bit the week before working on parsing linkedin pages. 

Week 6: Pair Programming++

This week was centered around two questions:

1. How do you summon captain planet? 
2. What happens when you do? 

The answers are 1) Teamwork and 2) He kicks ass. That's what we're talking about this week with Pair Programming++. Sure there was some good programming action that we had going on this week. I wrote a python script to parse out a thousand user ids from linked in and setup a script to automatically browse them which had linkedin boot me out and threaten me. We made a game too. But that's not much new. The real theme for the week was learning about how to work together with a few people in real-time on a small script without stepping on each others toes.

So here is my preferred method of teamwork, and like any good plan or get rich quick strategy it has 4 key elements.

1. Google hangout - This is your bread and butter. Talk about stuff.
2. Google drawings - What are you talking for if you could be drawing 
3. Codeshare.io - This is where you all work together on one file
4. Github - Dump things here when you are done or if people work on the project during off peak hours have them submit pull requests with their code

Using this deadly combination of technology our group had a good time this week working on our adventure game. We co-ordinated via google hangouts, made drawings of our game map, collaborated in real-time on codeshare.io, and checked things in on github. For a while there I was worried we wouldn't get on track, but teamwork prevailed and by our powers combined... we made something cool. 

My favorite python package of the week is PyAutoGui found here: https://pypi.python.org/pypi/PyAutoGUI

This lets you automate your PC in neat ways so you can interact with programs without having to learn their APIs or figure out other ways to hack them. Just pretend a person is using the PC. It can search the streen for images and such too.

CST205 Week 5 - go big or go home

Mr Rogers under the sea

Putin and his horse on an adventure

This week was all about midterm project 1(source code). Sure, there was some other stuff posted, and I will get as much of that done as I can. What matters most though is seizing an opportunity to blow up a project and try to make it awesome, even if you fail in the process and don't have time to implement generateKelpForest() or generateFishSchool(). My goal for this project was dynamic generation of two instagram style filters without the resolution restriction the instagram regime imposes on it's users. The filters in question were scuba and fungi themed.

So how do you make a scuba themed filter?

• First you should generally shift the colors to be ocean themed, because water absorbs different colors at different rates, apply different factors to each color.
• Then you need lighting effects of course. But not just any lighting effects. You need an array of lighting effects to select from at random which all have different resolutions, but which you would like to resize to be approximately the same size as your original image to apply the filter to. Then if there is a bit of extra hanging off the sides of the image you need to crop it.
• Then you should throw up a border just in case your scaling functions messed up and you want to try to hide defects. This won't work.

The same applies for a fungi theme, but instead of lighting effects use geometric patterns fed through the same kind of matching routine.

Taking any lighting effect and apply it to your source image by first trying to rescale and crop it to be as close as possible

Now I know what you're thinking, if my rescaling function didn't work perfectly how did I get some cool photos? That's where numbers come in. I generated 50 randomized pictures with each of my filters. These were broken down as follows:

6 Images to apply the filters to

testPics     = ["mrrogers.jpg", "hq.jpg", "mattreg.jpg", "mattShirt.jpg", "mattTesla.jpg", "putin.jpg"] 

5 Lighting effects for ocean scenes

oceanLights  = ["lightrays1.jpg", "lightrays2.jpg", "lightrays3.jpg", "lightrays4.jpg", "lightrays5.jpg"]

5 Geometric patterns to apply to fungal scenes

fungalLights = ["geometric1.jpg", "geometric2.jpg", "geometric3.jpg", "geometric4.png", "geometric5.jpg"]

By using test functions that accepted a counter for test samples I was able to generate as many pictures as I wanted, save them to my laptop, and pick the best ones. For example:

def fungusTest(testSamples):

   for count in range(0,testSamples):

      source = testPics[random.randint(0,len(testPics) - 1)]

      testPic = makePicture(getMediaPath(source))

      testPic = fungalFilter(testPic)

      writePictureTo(testPic, getMediaPath("_fungalResult_" + str(count) + ".jpg")) 

This kind of test capability also allowed me to find problems with my software, find ways that it would crash, find index problems, cropping problems, etc, which lead me to the conclusion that my combination of scaleMatch, blend, and smartBlend (which uses scale matching and blending) has big problems. Primarily there are cut off sections that appear given certain constraints, for example:

But when you apply the same setting to Putin you are fine:

So clearly some refinement of scaleMatch is needed based on the results of my 100 image test set. A lot of good stuff was learned from spending this much time chugging along on python though. I got some good experience with datatype conversion and rounding, exception handling in case people don't copy my exact directory location for images, test generation, and image manipulation. Overall I am happy with how things went but I think this week could knock my grade down a bit due to poor time management but that's ok. 

CST205 Week 4

So here we are, week 4. What's new? This week was mostly about organizing our existing code and providing sample images in a gallery. There was a new line tracing function reminiscent of a cellular automata program that used a simple rule to emphasize the outline of an object. The code for this is described in my previous post on my image manipulation library. 

                           

So this week was more organizing and getting ready to review other students than much new material for me personally. We setup our git organization at the beginning of the class so we are good to go on that front. What is exciting to me personally is our mid term assignment. I have chosen to do two filters, one that has a scuba diving theme and another that will have a microryzal theme. I am going to approach this project with the following action plan which is destined to be killed off as time goes on but reserve a minimum viable product:

• Minimum viable product
• Create each filter using static assets and resize the input photograph to fit a dedicated frame size
• Create base functions that are flexible but not overkill
• Add dynamic features
• Continue to use a dedicated frame size
• Apply weighted and bounded randomization to asset colorization and scaling
• Randomize asset distribution (mushrooms, kelp, etc) based on the generated array of randomized assets
• Implement key based scene generation 
• Before performing asset generation or scene modification generate a randomized key of sufficient length
• Use that key to repeatably generate a scene so you can select from an array of scene presets or use your personal past favorite preset
• Key based dynamic scene generation with variable input image
• Variable input image size drives output image size
• Key determines quantity, location, colorization, and scaling of generated assets

If our scene assets are 

kelp_01.jpg

kelp_02.jpg

kelp_03.jpg

fish_01.jpg

fish_02.jpg

fish_03.jpg

bubble_01.jpg

bubble_02.jpg

bubble_03.jpg

Then we have 9 fixed assets. We can use these to generate a ton of dynamic assets for sure by changing their size, position, rotation, reflection, etc. 

asset1_minScale, asset1_maxScale, asset1_xCenter, asset1Weight, asset1ReflectionProb, .... assetN_minScale, assetN_maxScale

I'm not sure what all I would like to have in my key, but it would look something like "70,110,50, 10, 10..." Yielding a 70% minimum scale, 110% max scale, x center 50% of image width, 10% max deviation from the half way point, 10% chance of vertical reflection, and so on. Ideally you could just feed in an array of assets and this kind of key string to generate your scene based on key values. So without having to change the program I could figure out what my favorite setting is. But if I felt really crazy I could randomize that input string a bit too so that the randomization of assets is based on a randomized initial condition set. Then you could run some kind of normalization based on scene configuration variables. 

So maybe my scuba scene and the microrizal scenes would be generated by overlapping functions with different assets and configuration parameters fed into them. Kelp for example may be on the sides of the scene because they are tall and you don't want to block the focus of the picture. But if you have small mushrooms they could be on running across the whole bottom of the scene if they aren't tall.

The trick in the end is to not force the user to use a fixed picture size. I hate that. I also don't want to just plop down a clown head on the person in the center of the frame and call it a done day. Hopefully that makes sense.