Calimeron wrote:
> >What do you mean, "save"?
>
> So that when you're done, you have 3 tables "Chinese," "English," "Merged"
> or the original 2, but with data from the one into the other.
>
> I don't know the terms or the procedure, but I'd like to have a new table
> that has the Chinese and English mixed, which I then can continue to
> manipulate.
The answer to that depends strongly on your application. It could be
that you should just use do whatever manipulations you need in your main
language.
It could be that your use case will be simplified using what's called
a "view". This will essentially be a wrapper around your join query.
You can read from the view but you can't write to it. The view is
always consistent with the underlying "real" tables.
But the view, as far as I understand, doesn't provide a performance
benefit over the query that defines it. It only exists for simplified
understanding of your problem space and your code.
So if you think that you will run your JOIN query a lot, and you think
that it will be a performance bottleneck, then a view is probably not
the right way to go.
Here's what a view looks like (using my previous example code):
[hudson:~] $ sqlite3 ./foo.db
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE chChar(id INTEGER PRIMARY KEY, char TEXT);
INSERT INTO "chChar" VALUES(1,'char1');
INSERT INTO "chChar" VALUES(2,'char2');
INSERT INTO "chChar" VALUES(3,'char3');
INSERT INTO "chChar" VALUES(4,'char4');
INSERT INTO "chChar" VALUES(5,'char5');
CREATE TABLE chCharDefn(id INTEGER PRIMARY KEY, chChar REFERENCES
chChar, defn TEXT);
INSERT INTO "chCharDefn" VALUES(1,1,'def1');
INSERT INTO "chCharDefn" VALUES(2,2,'def2');
INSERT INTO "chCharDefn" VALUES(3,2,'def3');
INSERT INTO "chCharDefn" VALUES(4,3,'def4');
INSERT INTO "chCharDefn" VALUES(5,4,'def5');
INSERT INTO "chCharDefn" VALUES(6,4,'def6');
INSERT INTO "chCharDefn" VALUES(7,5,'def7');
COMMIT;
sqlite> create view ChineseCharsAndDefns as select chChar.id, chChar.char,
chCharDefn.id, chCharDefn.defn from chCharDefn join chChar ON
(chCharDefn.chChar = chChar.id);
sqlite> select * from ChineseCharsAndDefns;
1|char1|1|def1
2|char2|2|def2
2|char2|3|def3
3|char3|4|def4
4|char4|5|def5
4|char4|6|def6
5|char5|7|def7
sqlite>
As you can see, I just prefixed my join query with "create view
ChineseCharsAndDefns as". Now watch what happens when I change one of
the underlying tables:
sqlite> begin;
sqlite> update chCharDefn set defn='def7CHANGED' where id=7;
sqlite> select * from ChineseCharsAndDefns;
1|char1|1|def1
2|char2|2|def2
2|char2|3|def3
3|char3|4|def4
4|char4|5|def5
4|char4|6|def6
5|char5|7|def7CHANGED
sqlite> rollback;
sqlite>
Note again that, as far as I know, views in SQLite only exist as
'syntactic sugar' -- they don't provide any functional difference,
just a textual difference in your code. Others can confirm or refute
that.
It could be that you want to create a real table that can be read from
and written to. I would then ask why you think you need to do that.
Eric
--
Eric A. Smith
Sendmail may be safely run set-user-id to root.
-- Eric Allman, "Sendmail Installation Guide"
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
