Update Existing SQL Records Using the UPDATE Statement

Previously we looked at finding data in a SQL database and The UPDATE statement is used to update records in a table. You can also use the UPDATE statement to update existing records in a table. When using the SQL UPDATE statement, we’ll also use the WHERE clause, as we used previously to constrain output of a SELECT statement. The WHERE locates the record(s) to be updated with syntax as follows: UPDATE table SET column=value,column=value,... WHERE column=value; The WHERE clause indicates the record(s) to update. I’ve forgotten to put it in in the past and updated every record of a database. That’s bad (unless you mean to do it). So let’s run a SELECT on our sample database from earlier, so we can see the “Customers” table from our first article: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US Now, let’s update the Address of the Apple record. To update the Address “spaceship” with the contents of “1 Infinite Loop” we’ll UPDATE the Customers table and SET the Address to equal UPDATE Customers SET Address='1 Infinite Loop' WHERE Site='Apple'; The selection from the “Customers” table will now look like this: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook 1 Infinite Loop Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US You can also update multiple records by separating each item with a comma. For example, let’s say we wanted to update the Microsoft record to get the address correct: UPDATE Customers SET Address='One Microsoft Way',Zip="98052-7329" WHERE Site='Microsoft'; Note that in the above example, I quoted Zip as I didn’t use an actual integer. The selection from the “Customers” table will now look like this: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook 1 Infinite Loop Cupertino 95014 US 3 Microsoft Satya Nadella One Microsoft Way Redmond 98052-7329 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US

Add New Records To MySQL Databases With The INSERT Statement

Sometimes you need to write a record into a table in a SQL database. The INSERT INTO statement creates new records in a table and can work in one of two ways. The first form does not specify the column names where the data will be inserted, only their values. When doing so, each value needs to be inserted in the columned order they appear, here the table being the name of the table you’re adding a record into and each value would be replaced with the contents of your value (don’t insert the string ‘value’ into each!): INSERT INTO table VALUES (value,value,value,...); If you don’t have every value to insert, you can also list the columns to insert data in and then include the values in the same order that the columns are listed in. The second form specifies both the column names and the values to insert: INSERT INTO table (column,column,column,...) VALUES (value,value,value,...); Below is a selection from the “Customers” table that I put in the first article. We will use it to run some SQL statements using the INSERT keyword so that we can add data to our little database: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US To insert a new row in the “Customers” table, we’ll add a row for Huffington Post with Arianna Huffington as the owner, her address as “The Library, with a city of Los Angeles, a zip of 90077, and a country of the US. INSERT INTO Customers (Site, Contact, Address, City, Zip, Country) VALUES ('Huffington Post','Arianna Huffington','The Library','Los Angeles','90077','US'); The selection from the “Customers” table will now look like this, after the ID record incremented on its own, taking the next available integer: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US 6 Huffington Post Arianna Huffington The Library Los Angeles 90077 US As mentioned, you can also Insert Data Only in Specified Columns It is also possible to only insert data in specific columns. The following SQL statement will insert a new row, but only insert data in the Site, Contact, and Country columns: INSERT INTO Customers (Site, Contact, Country) VALUES ('Spotify', 'Daniel Elk', 'SE'); The selection from the “Customers” table will now look like this: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US 6 Huffington Post Arianna Huffington The Library Los Angeles 90077 US 7 Spotify Daniel Elk SE Overall, adding rows to SQL tables is really straight forward. I mean, you’re not replacing anything… Yet…

Compound Searches With SQL Using AND && OR

Previously, we looked at the SQL SELECT and WHERE options to obtain output and then constrain what would be displayed in a query. The AND & OR operators are used to add additional logic so you can filter records based on more than one condition. Which is to say to search based on the contents of multiple columns within a single table. AND is used to show information if two different conditions are true and OR is used to show information if either condition is true. Below is a selection from the “Customers” table that showed in our first article an we will use it to run some SQL sorting statements using the ORDER BY keyword: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US The following example SQL statement outputs customers from the country “US” AND the city “55418”, in the “Customers” table from above: SELECT * FROM Customers WHERE Country='US' AND Zip='55418'; The following SQL statement selects all customers from the city “Minneapolis” OR the city “Cupertino”, in the “Customers” table: SELECT * FROM Customers WHERE City='Cupertino' OR City='Minneapolis'; As the logic of your searches expands, you can combine AND and OR by nesting logic within parenthesis (who said pre-algebra would be useless?!?!). For example, the following SQL statement selects all customers from the country “US” AND the city must be equal to “Minneapolis” OR “Cupertino”, in the “Customers” table: SELECT * FROM Customers WHERE Country='US' AND (City='Minneapolis' OR City='Cupertino');

Constrain SQL Queries Using WHERE and LIKE

Previously, we covered the SQL SELECT Statement, to pull data from a SQL database. Here, we’ll constrain our search for items that match a given string, or pattern using the WHERE clause to filter search results, rather than getting all of the records and parsing the output. The WHERE clause extracts records that fulfill a specified string and follows the general syntax as follows, replacing the word column with the name of the column in one of your tables and the word table with the name of a table that you’d like to search within: SQL WHERE Syntax SELECT column,column FROM table WHERE column operator value; Below is a selection from the “Customers” table that showed in our first article an we will use it to run some SQL sorting statements using the ORDER BY keyword: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US The following SQL statement selects all the customers with the zip code (Zip column) matching “55418”, in the “Customers” table: SELECT * FROM Customers WHERE Zip=55418; In the above search, I didn’t have to quote what I was looking for. The reason is that an integer doesn’t require quoting; however, if we were searching for a name, or any other text record, we should use quotes. So to repeat the search for Site, looking for Krypted, we would use the following: SELECT * FROM Customers WHERE Site=Krypted; We used an = operator, but it’s worth noting that there are a number of others that can be super-helpful. The following operators are available when using a WHERE clause:
    • = Equal
    • <>  or != Not equal to
    • > Greater than
    • IN Indicates multiple potential values for a column
    • < Less than
    • >= Greater than or equal
    • <= Less than or equal
    • BETWEEN Between an inclusive range
    • LIKE Looks for a provided pattern
So using another operator from above, we can also search for all sites that do not contain Krypted, using the following: SELECT * FROM Customers WHERE Site!=Krypted; So far, we’ve looked at searching for exact matches. We can also fuzzy our logic up by looking for items that contain a pattern AND something else, using the LIKE operator in a WHERE clause to search for items that contain part of a pattern in a search. The syntax for a SQL LIKE is similar, it begins with the SELECT statement that we’ve used throughout these articles so far, and then continues with the WHERE and then a LIKE to define the pattern, as follows, replacing column with the name of one of your columns, table with the name of the table that column is in and pattern with the actual search you’re looking to run: SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern; The following SQL statement selects all customers with a City starting with the letter “M” with the % inside a single quote to show where the wildcard data is (in this case, anything that appears after the letter M in a city name): SELECT * FROM Customers WHERE City LIKE 'M%'; The following SQL statement selects all customers with a City containing the pattern “Park (e.g. “Menlo Park”): SELECT * FROM Customers WHERE Country LIKE '%Park%'; You can also add NOT in front of Like to search for records that do not match a pattern (note that you don’t have all the same operators available, so this helps to get a little more logic in searches when needed). The following SQL statement selects all customers with Country NOT containing the pattern “US” (of which there are none in our sample data set): SELECT * FROM Customers WHERE Country NOT LIKE '%US%'; Or if we weren’t sure that we wanted to work in a spaceship, we could search for all addresses that weren’t spaceship: SELECT * FROM Customers WHERE Address NOT LIKE '%spaceship%'; Overall, the SELECT statement has some pretty basic logic and operations, because searching for data within a given table is a pretty straight forward task, you have a pattern, you look for items that match that pattern. As these articles continue, we’ll get into slightly more complex operations, but much of the work that we do with SQL is done after a query with another tool in order to get our data to display or be manipulated in ways that SQL doesn’t do on its own. One of the beauties of SQL, and why it can be so fast, is that it’s simple.

Us the ORDER BY Keyword With Your SQL SELECT Statements

The ORDER BY keyword in a SQL SELECT statement is used to sort a given result-set based on the contents of one or more columns of data. By default, results are in ascending order, but you can use either ASC or DESC to indicate that you’d like results sorted in ascending or descending order, respectively. Below is a selection from the “Customers” table that we will use to run some SQL sorting statements using the ORDER BY keyword: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US The syntax we’ll use for these commands is: SELECT column_name, column_name FROM table ORDER BY column ASC|DESC, column ASC|DESC; Here is a basic SQL statement that selects all of the customers from the “Customers” table and sorts the results in ascending order (as it’s the default operation) based on the contents of the “Country” column: SELECT * FROM Customers ORDER BY Country; Now we’ll flip that into descending order: SELECT * FROM Customers ORDER BY Country DESC; Then ascending based on first the country, then the Site: SELECT * FROM Customers ORDER BY Country, Site; Finally, you can do ascending for the country and descending for the site: SELECT * FROM Customers ORDER BY Country ASC, Site DESC;

FuelBand Bug or Feature?

I’ve been using a FuelBand on and off for about a year. I upgraded to the newer FuelBand SE as soon as it became available, and immediately noticed that Nike built a bunch of new badges in for users of the newer devices. As you obtained more Fuel, you can unlock the ability to earn these ever increasingly difficult to earn badges. But, I noticed something kinda’ strange. Not all of the badges were available to me that should be. In fact, only a few badges were available. But, as I continued to obtain more Fuel, all the new badges unlocked. All of the badges from the Nike Running app were available. Upon further inspection it turned out that the only ones of the new FuelBand SE badges available were those that required the number of Fuel that I’d had when I got my first FuelBand. Basically, a loop where you need 1,000,000 plus the total that a badge is supposed to be unlocked at in order to unlock the badge. Screen Shot 2013-12-08 at 4.12.18 PM Seeing a bug and a phone number for support on my Nike+ account, I called Nike. They were very nice and didn’t make fun of me for bothering to call them about something so trivial as badges from their app. The guy who answered the phone was very nice. I have to admit, I was totally surprised that there was someone on the other end of the phone to help me deal with an app issue. Then, today (about a week later) I received this email, restating the problem I explained to them:
Hi Charles, We have researched this issue a little further and I believe we have determined what is going on here. I appreciate your patience while we looked into this! Any trophies or milestones earned before the update on November 1st, such as the 500k NikeFuel milestone, are not awarded retroactively. This means that though you are nearly to 1 million NikeFuel, you have only earned 700k and above. Fortunately, you can start earning these earlier milestones again after you hit 1 million NikeFuel. It looks to me like those should be unlocked for you very soon. This is definitely expected behavior, and I am glad to determine that things are working correctly for you. Please let me know if there is anything else that I can help you with, Charles.
Holy crap, they actually bothered to research my utterly trivial issue. So I guess if you ask Nike phone support, this is a feature. But, I still kinda’ think it’s a bug. Reason I think that is because Fuel added to my account by the Nike+ Running app since I started using my FuelBand actually contributes towards my ability to unlock new badges. And it’s not like absolutely none of the Fuel from before works, just not a lot of it. I’m sure there’s a field in SQL that’s not in use until you use a FuelBand and it keeps a running total. Makes sense from a database standpoint. It’s not a big deal, and the whole point of this stuff is to be more healthy, so it’s not like I’m going to stop when I hit a million Fuel at some point this week anyway. The whole purpose behind this post is to actually express my gratitude that there was someone at Nike on the other end of the phone to support of all things an app they make available for free (granted, you need a FuelBand to basically take any use out of it but there’s a whole ecosystem of apps and you don’t actually need any of their products to use them). I am constantly impressed by the folks at Nike for the little things just like this. Thanks ya’ll!

Working with Postgres from the Command Line in Lion Server

Mac OS X Server 10.7, Lion Server, comes with a few substantial back-end changes. One of these is the move from SQLite3 to PostgreSQL for many of the back-end databases, including Wiki and Podcast Producer (collab), Webmail (roundcubemail), iCal Server and Address Book Server (caldav) and as the back-end to the newest service in Lion Server, Profile Manager (device_management). As such, it’s now important to be able to use PostgreSQL the way we once used SQLite3, when trying to augment the data that these databases contains, as there currently aren’t a lot of options for editing this data (aside from manually of course). Postgres has a number of commands that can be used to interact with databases. The most important is probably psql. Many of the other commands simply provide automated options to psql, and over time I’ve started using psql for most everything. For example, PostgreSQL comes with a command /user/bin/createuser. However, as it’s usually more verbose with errors, I like to use psql to do this. In Lion Server, the only user that can access the Postgres databases is _postgres, installed by default with Lion Server. Because a lot of commands require passwords and we might not always want to provide write access to the databases, we’re going to create a new SuperUser, called krypted with a password of daneel. To do so, we will have to use the _postgres user to invoke psql. Any time you want to invoke psql with a different user than the user you are currently logged in as, use the -U option. To define a database, use the -d option (device_management providing access to Profile Manager data, caldav to iCal Server data roundcubemail to WebMail data and collar to Wiki data). To string this together, for accessing the device_management database as _postgres: psql -U _postgres -d device_management To then create a new user called krypted with a password of daneel we’ll use the create option, defining a user as the type of object to create, followed by the user name and then with password followed by the password (single quoted) and then createuser; as follows: device_management=# create user krypted with password 'daneel' create user; Now that there’s a valid user, let’s see what else we can do. To see all of the tables, use d: device_management=# d As you can tell, there are a bunch of them. Run the help command to see a list of SQL commands that can be run and ? for a list of psql options. To put some SQL commands into action, we’re going to look at the tasks that have been performed by Profile Manager. These are stored in the tasks table (aptly named), so we’re going to run the following SQL query (note a space followed by a semi-colon is required at the end of this thing): device_management=# select * from "public"."tasks" limit 1000 offset 0 ; Or to make it a bit simpler if you don’t have a lot of data in there yet: device_management=# select * from "public"."tasks" ; After seeing the output, you’ll probably be a little appreciative of Apple’s formatting. Next, let’s look at dumping the databases. We’re going to create a folder on the root of the volume called db_backups first: sudo mkdir /db_backups This is where these backups will end up getting stored. We’ll continue using the _postgres user for now. To do our database dumps, we’re going to use pg_dump, located at /usr/bin. First, we’ll dump the device_management database (but first we’ll stop the service and after we’ll start it – all commands from here on out also assume you’re sudo’d): serveradmin stop devicemgr pg_dump -U _postgres device_management -c -f /db_backups/device_management.sql serveradmin start devicemgr And the other 3 (stopping and starting each in the process): serveradmin stop web pg_dump -U _postgres roundcubemail -c -f /db_backups/roundcubemail.sql serveradmin start web serveradmin stop wiki pg_dump -U _postgres collab -c -f /db_backups/collab.sql serveradmin start wiki serveradmin stop addressbook serveradmin stop calendar pg_dump -U _postgres caldav -c -f /db_backups/caldav.sql serveradmin start addressbook serveradmin start calendar I haven’t had any problems running the dumps with the services running, but it’s better safe than sorry I guess. I’d probably also add some logging and maybe dump the output of full status for each service to try and track if all is well with each. Any time a service didn’t fire back up I’d then build in a sanity check for that event. There’s also a database for postgres itself, so let’s back that up as well since we’re here: pg_dump -U _postgres postgres -c -f /db_backups/postgres.sql These can then be restored using psql with the -d option to define the database being restored into and the -f option to define the file being restored from. For example, to restore collab: psql -U _postgres -d collab -f /db_backups/collab The databases are all dumped daily using pg_dumpall. These are stored in /var/pgsql but can be changed using serveradmin settings (for example, to move them to /var/pgsql1): serveradmin settings postgres:dataDir = "/var/pgsql1" If you mess up the Profile Manager database (before you put any real data into it) you can always use the /usr/share/devicemgr/backend/wipeDB.sh script to trash the database and start anew (although I’d just use a snapshot of a VM for all this and restore from that). You can also connect to Postgres remotely, or locally through a network socket (common in Apache uses) by adding a listener. To do so, we’ll need to restart the Postgres LaunchDaemon. First, back up the file, just in case: cp org.postgresql.postgres.plist org.postgresql.postgres.plist.OLD_CSE Then stop postgres: serveradmin stop postgres Then edit the org.postgresql.postgres.plist file to change the following line: listen_addresses= To read: listen_addresses=127.0.0.1 Then fire up postgres again: serveradmin start postgres And now let’s scan port 5432 (the default TCP and UDP port used for postgres) for localhost: /Applications/Utilities/Network Utility.app/Contents/Resources/stroke 127.0.0.1 5432 5432 We could have used another IP address for the listen_addresses as well, but with that _postgres user not requiring a password it didn’t really seem prudent to do so. Once you’ve enabled a socket, you’ll then be able to use one of the many GUI tools to manage postgres. Navicat is available on the Mac App Store for $5 and PGnJ is a nice, easy to use, free one. There are tons of others, but I don’t spend a lot of time in a SQL GUI and so don’t need more than a cheap app will get me. One nice thing about most of these is that they help you to form SQL queries (or they help me). This can get really nice if you are, for example, trying to get some good reporting on Profile Manager (a feature it’s a bit light on right now). Finally, don’t do any of this stuff on a production box, except maybe if you want more than nightly backups unless you think pretty hard about what you’re doing and know the exact impact of doing something. If you were to edit the databases on a live boxen, then you can safely assume that with how all of the objects in those databases use GUIDs that you’re probably going to break something, if not bring the whole house of cards tumbling down.