This is really basic, but it drove me crazy. Suppose you want to add a row of data to a MySQL database, but not if there’s already an entry in it with the essence of that data. For example, suppose you have a table of notes and the titles they’re clustered under, and don’t want to allow the same note to appear more than once for any particular title. Assume also that that table is also recording an identifier of which book the note is about.
Here’s a SQL statement that works (line breaks don’t matter):
INSERT IGNORE into
With the exception of the mysterious “IGNORE” (see below), this is a straightforward command that inserts a row into the table “playlistentries” with a series of values (‘fiction’, ‘1234’,’5678′) mapped to the series of fields (title, noteid, bookid). If there’s already a row with those title and noteid values, the table will be left unchanged. Otherwise, a new row will be added.
But, this will not work unless you set up your MySQL table so that it has a unique key based on the fields you’re testing for (title and noteid in this example). That way when you go to insert a row that has an already-existing title and noteid, it will be automatically rejected. The “IGNORE” in the SQL statement means it will be rejected without creating an error message that just gets in the way.
To set up your table so that it has the right unique key, use a SQL statement like this:
ALTER TABLE playlistentries
ADD UNIQUE KEY noteidtitle (noteid,title(100))
This tells MySQL to create a unique key (named “noteidtitle”) based on the fields noteid and title. The “(100)” is there to tell MySQL that it should only look at the first 100 characters of the title field; if you’ve set up your table with that field as “text,” you’ll get an error unless you put a limit on it. A hundred characters is probably 75 more than I need.
Note also that you only run the “alter table” command once in the lifetime of your database.
Finally, please note that there is a high probability that what I’m telling you is wildly inefficient, non-robust, and suboptimal. On a scale of 1-100, I am about 3 points past raw beginner. But these commands work for me, and I am assuming as always that if you’re reading this, you are an amateur like me engaged in some small project designed primarily for your own use. Improvements and do-overs will be gladly accepted.
Tagged with: mysql
Date: October 30th, 2012 dw
Just when you thought copyright extremism couldn’t get any more absurd, the Supreme Court may uphold a decision that would prevent us from reselling goods we’ve legally bought that have parts or content with foreign copyrights (which most goods of any complexity do have). Marvin Ammori has a good explanation of how this would “work” and why it is Kafka-esque in its implications.
I would say that Marvin’s opening examples would never be enforced, but I also thought that Citizens United wouldn’t trash our political system like Godzilla(c) walking through a cheap cardboard city.
Tagged with: copyright
Date: October 29th, 2012 dw
And one more thing about my previous post: I understand that when Heidegger was writing Being and Time in the 1920s, it was important to try to relax our culture’s commitment to scientific objectivity in order to allow more types of truths to appear – more ways that the world shows itself to us.
Almost a hundred years later, with a brand new medium for knowledge, truth, and disclosure, it is time to re-assert science’s privileged (yet still human and imperfect) position as we try to come to agreement across cultures about what we need to do in order to live together on this earth.
In my opinion.
, too big to know
Tagged with: 2b2k
Date: October 28th, 2012 dw
Last night I gave a talk at the Festival of Science in Genoa (or, as they say in Italy, Genova). I was brought over by Codice Edizioni, the publisher of the just-released Italian version of Too Big to Know (or, as they say in Italy “La Stanza Intelligente” (or as they say in America, “The Smart Room”)). The event was held in the Palazzo Ducale, which ain’t no Elks Club, if you know what I mean. And if you don’t know what I mean, what I mean is that it’s a beautiful, arched, painted-ceiling room that holds 800 people and one intimidated American.
After my brief talk, Serena Danna of Corriere della Serra interviewed me. She’s really good. For example, her first question was: If the facts no longer have the ability to settle arguments the way we hoped they would, then what happens to truth?
Yeah, way to pitch the ol’ softballs, Serena!
I wasn’t satisfied with my answer, which had three parts. (1) There are facts. The world is one way and not all the other ways that it isn’t. You are not free to make up your own facts. [Yes, I'm talking to you, Mitt!] (2) The basing of knowledge primarily on facts is a relatively new phenomenon. (3) I explicitly invoked Heidegger’s concept of truth, with a soupçon of pragmatism’s view of truth as a tool intended to serve a purpose.
Meanwhile, I’ve been watching The Heidegger Circle mailing list contort itself trying to understand Heidegger’s views about the world that existed before humans entered the scene. Was there Being? Were there beings? It seems to me that any answer has to begin by saying, “Of course the world existed before we did.” But not everyone on the list is comfortable with a statement that simple. Some seem to think that acknowledging that most basic fact somehow diminishes Heidegger’s analysis of the relation of Being and disclosure. Yo, Heideggerians! The world shows itself to us as independent of us. We were born into it, and it keeps going after we’ve died. If that’s a problem for your philosophy, then your philosophy is a problem. And for all of the problems with Heidegger’s philosophy, that just isn’t one. (To be fair, no one on the list suggests that the existence of the universe depends upon our awareness of it, although some are puzzled about how to maintain Heidegger’s conception of “world” (which does seem to depend on us) with that which survives our awareness of it. Heidegger, after all, offers phenomenological ontology, so there is a question about what Being looks like when there is no one to show itself to.)
So, I wasn’t very happy with what I said about truth last night. I said that I liked Heidegger’s notion that truth is the world showing itself to us, and it shows itself to us differently depending on our projects. I’ve always liked this idea for a few reasons. First, it’s phenomenologically true: the onion shows itself differently whether you’re intending to cook it, whether you’re trying to grow it as a cash crop, whether you’re trying to make yourself cry, whether you’re trying to find something to throw at a bad actor, etc. Second, because truth is the way the world shows itself, Heidegger’s sense contains the crucial acknowledgement that the world exists independently of us. Third, because this sense of truth look at our projects, it contains the crucial acknowledgement that truth is not independent of our involvement in the world (which Heidegger accurately characterizes not with the neutral term “involvement” but as our caring about what happens to us and to our fellow humans). Fourth, this gives us a way of thinking about truth without the correspondence theory’s schizophrenic metaphysics that tells us that we live inside our heads, and our mental images can either match or fail to match external reality.
But Heidegger’s view of truth doesn’t do the job that we want done when we’re trying to settle disagreements. Heidegger observes (correctly in my and everybody’s opinion) that different fields have different methodologies for revealing the truth of the world. He speaks coldly (it seems to me) of science, and warmly of poetry. I’m much hotter on science. Science provides a methodology for letting the world show itself (= truth) that is reproducible precisely so that we can settle disputes. For settling disputes about what the world is like regardless of our view of it, science has priority, just as the legal system has priority for settling disputes over the law.
This matters a lot not just because of the spectacular good that science does, but because the question of truth only arises because we sense that something is hidden from us. Science does not uncover all truths but it uniquely uncovers truths about which we can agree. It allows the world to speak in a way that compels agreement. In that sense, of all the disciplines and methodologies, science is the closest to giving the earth we all share its own authentic voice. That about which science cannot speak in a compelling fashion across all cultures and starting points is simply not subject to scientific analysis. Here the poets and philosophers can speak and should be heard. (And of course the compulsive force science manifests is far from beyond resistance and doubt.)
But, when we are talking about the fragmenting of belief that the Internet facilitates, and the fact that facts no longer settle arguments across those gaps, then it is especially important that we commit to science as the discipline that allows the earth to speak of itself in its most compelling terms.
Finally, I was happy that last night I did manage to say that science provides a model for trying to stay smart on the Internet because it is highly self-aware about what it knows: it does not simply hold on to true statements, but is aware of the methodology that led us to see those statements as true. This type of meta awareness — not just within the realm of science — is crucial for a medium as open as the Internet.
I won the World Technology Award in the field of Media & Journalism for Too Big to Know last night in NYC. Awards are given from among a set of 5-7 nominees in each field, nominated by fellows of the World Technology Summit, which is to say, by previous nominees and winners. So, I’m proud ‘n’ happy. Thank you, WTA!
At the gala, I got to sit with Cathy Davidson and David Theo Goldberg of HASTAC and the MacArthur Foundation, who won in the Education category, so I can say with confidence that at least one award last night was well deserved!
Actually, two. I also briefly saw Tim Wu, who won in the Law category. Not only was that richly deserved, but I can report that Tim looks like he was born to wear a tux. I’m a Tim Wu fan. (I inadvertently let out a “Woohoo!” when his name was announced and then was embarrassed.)
Here’s a complete list of winners.
(Just to be clear, and also to re-boast, this is different from the Get Abstract International Book of the Year award 2b2k won a couple of weeks ago. Please allow me to woohoo myself!)
Categories: too big to know
Tagged with: 2b2k
• what have I become?
Date: October 24th, 2012 dw
Gary Price from Infodocket is moderating a panel on what’s new in search. It’s a panel of vendors
NOTE: Live-blogging. Getting things wrong. Missing points. Omitting key information. Introducing artificial choppiness. Over-emphasizing small matters. Paraphrasing badly. Not running a spellpchecker. Mangling other people’s ideas and words. You are warned, people.
The first speaker is from Blekko.com, which he says “is thought of as the third search engine” in the US market. It features info from authoritative sources. “You don’t want your health information to come from some blog.” When you search for “kate spade” you get authenticated Kate Spade fashion stuff. Slashtags let you facet within a topic, based on expert curation. Users can create their own slashtags. At /webgrep you can ask questions about the corpus that if upvoted the techies at Blekko will answer.
Weblib.com describes itself on its site as “Natural Language Processing Tools and Customizable Knowledge Bases for Semantic Search and Discovery Applications.” Thomas talks about OntoFind and semantic search, which is a search that produces “meaningful results even when the retrieved pages” contain none of the search terms [latent semantic search!]. He points to Google’s Freebase, which has info about 500M entities and their relationships. In a week you’ll be able to try OntoFind at ezu.com, I believe. Searching for big brother and privacy first asks you to disambiguate and then pulls together results.
ScienceScape.com is designed to help scientists follow science. It diagrams publications on a topic, and applies article-level metrics. It’s focused on the undergrad and graduate research markets. It integrates genomic knowledge plus much more. It lets you see the history of science top down, and browse e.g. by date. You can share what you’ve found.
[I couldn't hear the Q&A well enough to blog it.]
Tagged with: internetlibrarian
Date: October 22nd, 2012 dw
If I don’t blog much over the next week, it’s because I’m doing everything I can to become an incubator of airplane-borne diseases. This morning I spoke at the excellent Internet Librarian conference in Monterey, then I’m on to NYC for the World Technology Awards, where Too Big to Know is shortlisted, then on Wednesday I’m in Chicago for Learning 3.0, where I’m keynoting plus arguing with Andrew Keen, then Thursday at Northeastern U. where I’m giving a breakfast talk on Open Access, and finally on Thursday night I’m going to the Genoa Science Festival for two days to give a talk and to support the Italian publisher of Too Big to Know. Ah, yes, just a typical week.
I’m on the first leg, and I’m already exhausted.
(And sorry that this is a braggy, self-centered, low-value post. I’ve decided I should do these occasionally. For example, it matters to me that because of 2b2k I’m one of five finalists for a technology communication award. I’m not proud that it matters to me — I’d like to be above it all — but it does.)
Tagged with: ego
Date: October 22nd, 2012 dw
Last week I paid a visit to my old PC store, ICG Computers, in Brookline. I hadn’t been there in maybe 5 years because I switched to Macs and thus spend time at the Computer Loft in Brighton. Also, when I was a PC, I was building my own computers out of components, so lots more went wrong (= I did lots more wrong). And, yes, I wish I could compile my own hardware and install the Mac OS on it. (Hackintosh scares me. Someday.) But, my remaining Windows machine crapped out last week, so I carried it to ICG’s small storefront.
Ray greeted me by name. Because no one else was there, we took the opportunity to catch up.
Ray comes from China and runs a quintessential American small business. He’s honest as the day is long, and could teach any bigger company about customer service. But it’s been a lean few years for ICG. Ray says that the recession hurt his primary customer base, small businesses. There haven’t been a lot of new businesses formed, so they’re not coming in to equip their offices. And, of course, the PC business has gotten commoditized. So, ICG relies on repairs and aggressively trying to beat the Internet on prices.
The walls of the store are lined with components. Then there are a few tables of new and used machines. He prices his used machines against eBay, and his new machines against Net low-ballers. As a result, you can get a power-packed laptop for $250 or $300. And you can do so knowing that Ray knows the tech and stands behind what he sells.
ICG is a great place to buy a computer. It’s also a great place to hang out and talk about tech. Ray knows my own level of expertise and talks at that level. No condescension, no salesmanship, no BS. I always learn something talking with Ray. In this case it turns out that my PC needed a new power supply, and the one I’d put in was under-powered. So, yeah, Ray upsold me, but I have complete confidence that he also right-sold me, so to speak.
Bunches of small, locally-owned computer stores have gone out of business here over the past few years. So have most of the larger stores. Remember EggHead? CompUSA? Me neither. And much as I love the Internet, I hate what it’s doing to the Rays of our town, who epitomize the best of small business. ICG is surviving and will continue to serve our community. But I want Ray’s business to do more than that. It seems unfair that honesty, expertise, friendliness, and low, fair prices aren’t enough for a business to go gangbusters.
Am I plugging ICG? Damn straight.
Tagged with: brighton
Date: October 20th, 2012 dw
I’m of course loving the Binder Women meme. Hilarious.
But let me for the moment take all the fun out of it:
1. The first thing President Obama did upon taking office was sign the Lilly Ledbetter Act. Meanwhile Romney refuses to commit to equal pay for women.
2. Romney’s story about his Binder Women was utter BS. In fact, according to The Phoenix
in 2002 — prior to the election, not even knowing yet whether it would be a Republican or Democratic administration — a bipartisan group of women in Massachusetts formed MassGAP to address the problem of few women in senior leadership positions in state government. There were more than 40 organizations involved with the Massachusetts Women’s Political Caucus (also bipartisan) as the lead sponsor.
They did the research and put together the binder full of women qualified for all the different cabinet positions, agency heads, and authorities and commissions. They presented this binder to Governor Romney when he was elected.
His administration had fewer women than the national average.
For those keeping score, it’s now: Bullshit 3, Truth 0.
3. Joe Biden today wonders why Mitt had to search for “qualified women.” (If I weren’t on a train with crappy wifi, I’d post the meme: “Some of my best friends are qualified women. I have a binder full of them.”)
4. [Added an hour later] As plenty have pointed out, Mitt’s idea of supporting women last night was to give them flex time (wow, there’s an idea that was innovative 30 years ago) so they can go home and cook dinner. See ladies, you can? have it all! (Some brilliant analysis — and writing — by Binder Person Amy Davidson.)
5. I think this tweet from the Obama campaign sums it up well: @BarackObama: Last night, the President talked about women as breadwinners. Romney talked about them as resumes in ‘binders’. http://OFA.BO/AEWhjM
This hilarious meme is deadly serious.
Tagged with: campaign
Date: October 17th, 2012 dw
David Wood of 3RoundStones.com is talking about Callimachus, an open source project that is also available through his company. [NOTE: Liveblogging. All bets on accuracy are off.]
We’re moving from PCs to mobile, he says. This is rapidly changing the Internet. 51% of Internet traffic is non-human, he says (as of Feb 2012). 35hrs of video are uploaded to YouTube every minute. Traditionally we dealt with this type of demand via data warehousing: put it all in one place for easy access. But that’s not true: we never really got it all in one place accessible through one interface. Jeffrey Pollock says we should be talking not about data integration but interoperability because the latter implies a looser coupling.
He gives some use cases:
BBC wanted to have a Web presence for all of its 1500 broadcasts per day. They couldn’t do it manually. So, they decided to grab data from the linked open data data cloud and assemble the pages automatically. They hired fulltime editors to curate Wikipedia. RDF enabled them to assuemble the pages.
O’Reilly Media switched to RDF reluctantly but for purely pragmatic reasons.
BestBuy, too. They used RDFa to embed metadata into their pages to improve their SEO.
Elsevier uses Linked Data to manage their assets, from acquisition to delivery.
This is not science fiction, he says. It’s happening now.
Then two negative examples:
David says that Microsoft adopted RDF in the late 90s. But Netscape came out a portal tech based on RDF that scared Microsoft out of the standards effort. But they needed the tech, so they’ve reinvented it three times in proprietary ways.
Borders was too late in changing its tech.
Then he does a product pitch for Callimachus Enterperise: a content management system for enterprises.
Next Page »