On 16 Apr 2014, at 6:24pm, Joseph Yesselman <jyess...@stanford.edu> wrote:
> I would like to use the > WITHOUT ROWID statement as my primary key is a string in the format of 6 > coordinates. > > example: > "0.0 1.0 3.0 1.6 2.4 0.0" Why not use six REAL columns in a table, with an index consisting of all six columns ? > I get a SQL error near WITHOUT. The 'WITHOUT ROWID" facility was introduced only very recently, in SQLite version 3.8.2. Check to see which version you're using. But I don't think you need WITHOUT ROWID at all. > VARCHAR SQLite does not have a VARCHAR type. I should warn you that any limit on the column length (e.g. VARCHAR(2000)) will be ignored. All TEXT values stored are variable-length values. > When I need to update a row I need to take the previous values from > names,rs,ds and sugs and add the new values I have to the end of the > strings seperated by a ;. > > example: > if names is currently "motif_1,motif_2;motif_5,motif6" > and the new data I have is "motif_10,motif_11", after the update i need > names to be: > "motif_1,motif_2;motif_5,motif6;motif_10,motif_11" This is technically possible using the concatenation operator (||) UPDATE myTable SET names=names||';'||'motif_10,motif_11' WHERE theKey='whatever' However, your proposed data structure is horrible and will lead to slow update-times and numerous difficulties in manipulation in SQL. I (and lots of people here) would recommend that you instead store your data in normal form. One method would be to create another table of motifs like so: TABLE motifs (theID TEXT, theValue TEXT UNIQUE, motif TEXT, PRIMARY KEY (theID, theValue)) To add a motif for a key INSERT a new row in this table. To list motifs for a key use SELECT group_concat(motif,';') FROM motifs WHERE theID = 'whatever' AND theValue = 'whatever' Also you are using a method of paired motifs separated with commas which I don't understand. If you think out what you are really trying to represent with these pairs you'll probably come up with a better data structure yourself. Remember: you're using SQL as a tool to hold your data, you're not trying to squeeze your data into something you think looks good in SQL. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
