List:MySQL++« Previous MessageNext Message »
From:Warren Young Date:February 1 2010 7:38am
Subject:Re: How to do a large template query?
View as plain text  
On Jan 31, 2010, at 8:18 PM, Adam Nielsen wrote:

> Of course, but my point was "it might break these compilers" can't really be used as
> an argument against a new feature, when those same compilers aren't tested with day to day
> changes that could just as easily break things.

MySQL++ does regularly break old compilers, but not without good cause and only when there
is no alternative.  There are alternatives in this case.

I thought more about your problem, and I believe the best solution is for you to contact
the person who maintains the MySQL++ package for your OS distro and ask them to generate
these headers so they're as large as supported on that system.  Binary packages play by
different rules than the core MySQL++ source; you can get away with baking in large
column counts in this case.

I've added an item to the Wishlist that will let package maintainers apply such changes to
their binaries without patching lib/*.pl.  I'll probably slide this change into 3.1.

> Well it's not really human readable

Compared to MARC records, it is!  (Yes, my work brings me into contact with library DBs,
too.)

Since you've kindly provided the schema, I'll comment on it, in the interest of general
design discussion rather than to try and continue to persuade you to change it.

> 	`depositor` VARCHAR(255) NOT NULL,
> 	`depositor_affiliation` VARCHAR(255),

There should probably be a depositor table, with an ID linking the two.

> 	`title` TEXT NOT NULL,
> 	`formatted_title` TEXT NOT NULL,

If one of these can be calculated from the other, both shouldn't be stored.

> 	`issue_number` VARCHAR(255),
> 	`volume_number` VARCHAR(255),
> 	`series` VARCHAR(255),
> 	`sequence` INT NOT NULL,

One wonders if all of these are used for even a single entry in the DB.  It looks like a
literal move from the card catalog world, without considering whether a little
abstraction would be helpful in the computerization effort.

> 	`language` VARCHAR(3) NOT NULL,
> 	`language_of_title` VARCHAR(3),
> 	`translated_title` TEXT,
> 	`phonetic_title` TEXT,

Ditto.

> 	`download_count` INT,
> 	`view_count` INT,

I wonder if these are actually needed frequently.  If not, the web server statistics model
might make more sense: store access logs in a separate table, then periodically crawl that
table to assemble statistics.  

> Well in this case I went back to the MySQL C library, as its prepared statements are
> exactly what I am after.  Generate the query and parse it once, then use bind variables to
> provide different data for each row. No messy escaping mechanisms and should be very
> fast.

Well, if that's what you're after, SSQLS and template queries still wouldn't have helped,
because they do generate plain old SQL text, not prepared statements.  We do already have
a Wishlist entry covering this, but no one's wanted it enough to do it.

I still think you should have gone with LOAD DATA INFILE.  If it had to be done remotely,
I would have looked at using some file transfer mechanism (scp, say) to get the file over
there rather than transmit the data over a DB channel.
Thread
How to do a large template query?Adam Nielsen27 Jan
  • Re: How to do a large template query?Warren Young27 Jan
    • Re: How to do a large template query?Adam Nielsen28 Jan
      • Re: How to do a large template query?Joel Fielder28 Jan
        • Re: How to do a large template query?Warren Young28 Jan
          • Re: How to do a large template query?Adam Nielsen29 Jan
            • Re: How to do a large template query?Joel Fielder29 Jan
              • Re: How to do a large template query?Joel Fielder29 Jan
              • Re: How to do a large template query?Adam Nielsen1 Feb
            • Re: How to do a large template query?Warren Young29 Jan
              • Re: How to do a large template query?Adam Nielsen1 Feb
                • Re: How to do a large template query?Warren Young1 Feb
                  • Re: How to do a large template query?Warren Young7 Feb
          • Re: How to do a large template query?Warren Young7 Feb