Another MySQL Question

[John Nagle]

MRAB wrote:
> Victor Subervi wrote:
>> Hi;
>> I have this code:
>>
>>     options = ''
>>     our_options = []
>>     our_options_string = ''
>>     for op in ops:
>>       options += '%s varchar(40) not null, ' % (op[0].upper() + op[1:])
>>       our_options.append('%s' % form.getfirst('%s' % op))
>>       our_options_string += '%s", "' % op
>>     cursor.execute('''create table if not exists tmp%s (
>>         Store varchar(40) not null,
>>         PatientID varchar(40) not null,
>>         ProdID varchar(40) not null,
>>         Pkg varchar(10) not null,
>>         %s)''' % (tmpTable, options[:-2]))
>>     sql_string = 'insert into tmp%s values (%s, %s, %s, %s, "%s")' % 
>> (tmpTable, store, patientID, prodid, pkg, our_options_string[:-4])
>>     print sql_string
>>     sql = 'insert into tmp%s values (%s, %s, %s, %s, %%s)' % 
>> (tmpTable, store, patientID, prodid, pkg)
>>     cursor.execute(sql, (our_options,))

    The real problem with this is not the Python.  It's the approach
to SQL.  What's going on here is that you have some collection of
named options that come in from some external source, and you're
trying to handle that by dynamically constructing the table schema.

    It would be easier to store the "option" values in a separate
table, with something like

	CREATE TABLE temp01 (
		entryid BIGINT NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,
	        Store VARCHAR(40) NOT NULL,
	        PatientID VARCHAR(40) NOT NULL,
	        ProdID VARCHAR(40) NOT NULL,
	        Pkg VARCHAR(10) NOT NULL)
		ENGINE=INNODB;

	CREATE TABLE ouroptions (
		FOREIGN KEY (parententry) REFERENCES temp01(entryid)
			ON DELETE CASCADE,
		optionname VARCHAR(40) NOT NULL,
		optionvalue VARCHAR(40) NOT NULL)
		ENGINE=INNODB;

    This creates two tables which are locked together.  For any entry
in "temp01", you can create any "option" entries you need in "ouroptions".
If you delete the entry in "temp01", the related "options" entries will go away.
(That's what ON DELETE CASCADE does.) When inserting, insert the record in
temp01 first, then the recrods in "ouroptions", in a single transaction.

    Incidentally, if you don't specify some indices, lookups will
take a very long time.

				John Nagle