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
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,
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]
Hey, thanks for that. I read a
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 accessing
> SQLite through the rub
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 Structure
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, but instead uses
parameterized q
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);
>
> or
>
> INSERT INTO Struct
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)
VALUES(nu
> 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 wher
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
> sqlite?
Yes, this would work. A
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' INTE
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
joi
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
documentation. Having hundred
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 want
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,
> i4 integer not null,
> na
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,
f
16 matches
Mail list logo