Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

I have more or less completed this project, so I thought I would post the final script. The script is in ruby and accepts the name of an input file and the name of the database, ruby loader.rb inputFilename databaseName The input file uses metadata rows to identify the database table that the

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

This looks like a more or less complete solution for creating the tables and doing inserts. Primary table: CREATE TABLE Structure( 'id' INTEGER PRIMARY KEY, 'name' TEXT NOT NULL, 'filePath' TEXT NOT NULL, 'iH1' INTEGER NOT NULL, 'iH2' INTEGER NOT NULL, 'iH3' INTEGER NOT NULL,

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

On 15 Nov 2012, at 5:24am, LMHmedchem wrote: > On 13 Nov 2012, at 5:41pm, Simon Slavin-3 wrote: >> By the way, can I ask what iH2, iH3, iH4 are ? I think I've figured out >> iH1. > > Yes iH1 is an intergerized form of the molecular weight rounded to two [snip]

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

LMHmedchem wrote: > Igor, > It seem as if you are saying that string values like a name should be passed > in SQL commands as variables, is that right? Personally, I tend to do this for all values, whether strings or numbers or otherwise. > At the moment, I am

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

Keith, Thank you for the thoughtful and informative response. It is very helpful to have a better background of how the underlying technology works. I gather that I probably don't need the AUTOINCREMENT keyword. Since I am inserting the value of "id" from Structure in to other tables (as

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

LMHmedchem wrote: > As it happens, some of the text strings that will be added to the database > have single quotes, > > N,N'-dimethylethylenediamine > > do I need to do anything different for these Normally, your program shouldn't use string literals directly,

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

On 13 Nov 2012, at 5:41pm, LMHmedchem wrote: > Thanks for the clarification. So my proper syntax for inserts with > AUTOINCREMENT is one of, > > INSERT INTO Structure > VALUES(null,'phosphoserine','phosphoserine.mol',185073,856147,73543,25338,'C3H8NO6P',185.073);

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

Thanks for the clarification. So my proper syntax for inserts with AUTOINCREMENT is one of, INSERT INTO Structure VALUES(null,'phosphoserine','phosphoserine.mol',185073,856147,73543,25338,'C3H8NO6P',185.073); or INSERT INTO Structure(id, name, filePath, iH1, iH2, iH3, iH4, formula, fw)

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

> It looks like my insert syntax should be, > INSERT INTO Structure > VALUES(null,'phosphoserine','phosphoserine.mol',185073,856147,73543,25338,'C3 > H8NO6P',185.073); > > where using null for id invokes AUTOINCREMENT. Is this the right syntax for > sqlite? I have also seen versions of insert

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

LMHmedchem wrote: > It looks like my insert syntax should be, > INSERT INTO Structure > VALUES(null,'phosphoserine','phosphoserine.mol',185073,856147,73543,25338,'C3H8NO6P',185.073); > > where using null for id invokes AUTOINCREMENT. Is this the right syntax for >

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

Hello, I have made some progress. This is what my Structure table looks like now, CREATE TABLE Structure( 'id' INTEGER PRIMARY KEY AUTOINCREMENT, 'name' TEXT NOT NULL, 'filePath' TEXT NOT NULL, 'iH1' INTEGER NOT NULL, 'iH2' INTEGER NOT NULL, 'iH3' INTEGER NOT NULL, 'iH4'

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

On Tuesday, 16 October, 2012, 11:51, LMHmedchem said: You could always create a table JUST for for the compsite key which gives you JUST the single-key which you then use to look up data in the other tables. If you specifically name the rowid columns, then you can use simple and efficient

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

On 16 Oct 2012, at 6:50pm, LMHmedchem wrote: > Some of > the other tables are fairly large (500-2500 cols) A table with 500 columns is itself a bad sign. You should be able to think about the entire table makeup in your head without needing to refer to written

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

Hello Igor, thank you for the information, it is a big help. > If you have a one-to-one relationship between two tables, is there a > reason why you don't simply combine the two into a single, wider table? They way I think about a database is that you subdivide the data based on how you may

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

LMHmedchem wrote: > I have data that I am loading into a sqlite database from a text file. I am > using a composite primary key for four ints for the main table. > > create table Structure ( > i1 integer not null, > i2 integer not null, > i3 integer not null,

[sqlite] inserting record datinto mutliple tables with a composite primary key

Hello, I have data that I am loading into a sqlite database from a text file. I am using a composite primary key for four ints for the main table. create table Structure ( i1 integer not null, i2 integer not null, i3 integer not null, i4 integer not null, name string not null,