Changes between Version 3 and Version 4 of REDCap sql fields

Timestamp:

01/29/14 15:45:58 (11 years ago)

Author:

Nick Holden

Comment:

--

REDCap sql fields

@@ -46 +46 @@
 value VARCHAR(100),
 label VARCHAR(100),
-INDEX 'reference' ('project','form','field'),
-KEY 'value_key' ('project','form','field','value'),
-KEY 'label_key' ('project','form','field','label'),
-FOREIGN KEY ('project') REFERENCES redcap_projects (project_name)
-  ON UPDATE CASCADE,
+INDEX reference (project,form,field),
+KEY value_key (project,form,field,value),
+KEY label_key (project,form,field,label)
 );
 }}}
@@ -64 +62 @@
 But I think you can only use a foreign key if the corresponding column holds unique values, and that isn't true for the form name or field name columns in redcap_metadata - fields and forms only have to be unique within a particular project, not across the whole database. So maybe we can't have foreign keys for all three.
 
+And foreign keys cause all sorts of MySQL errors unless the columns are identical in hidden ways. Easier for the time being not to use them.
+
 Need to check with the REDCap data model. I've asked for more information. In the meantime the above would work, but in a limited way, with no updating for field or form names.
 
@@ -73 +73 @@
 
 Of course, sometimes you want to use the same list more than once. In this case, the form and field names in the database should relate to the question which makes FIRST use of the list.
+
+=== Loading data into the table === 
+
+If you have more than a couple of  elements to add, use LOAD DATA LOCAL INFILE. This isn't entirely straightforward.
+
+Prepare the text file. Match up the columns and populate with the correct project, form and field names. Save it from excel as 'tab delimited text' (comma separated doesn't work because there are commas in the text. Then open it in an editor and remove any / all quote marks. Now upload to the server.
+
+Firstly, because it represents a security risk, you have to enable local loading, by starting your mysql client connection with the option " --local-infile".
+
+Secondly, from within mysql connection, do something like:
+
+{{{
+LOAD DATA LOCAL INFILE '/home/nick/sql_insert_medications.txt' INTO TABLE sql_fields 
+FIELDS TERMINATED BY '\t' 
+LINES TERMINATED BY '\n'
+(id,project,form,field,value,label)
+;
+}}}
+