自动创建INSERT语句来填充结表在pre-创建数据库 [英] Automatically Create Insert Statements to Fill Junction Table in a Pre-Created DB
问题描述
对于一个简单的Android应用程序我创建作为教学工具为自己（使用除其他事项外关系DBS / SQL - 如果你愿意原谅这个问题的简单）。我是pre-创建一个SQLite数据库出货与应用程序。我这样做是基于以下 SO质疑。
我有两个表一个多对多的关系，并结合表来定义这些关系如下：
CREATE TABLE名称（_id INTEGER PRIMARY KEY，
名称文本
）;
CREATE TABLE分类（_id INTEGER PRIMARY KEY，
类别TEXT
）;
CREATE TABLE Name_Category（name_id INTEGER，
CATEGORY_ID INTEGER，
PRIMARY KEY（name_id，CATEGORY_ID）
外键（name_id）引用名称（_id）
外键（CATEGORY_ID）参考目录（_id）
）;
我有套INSERT语句来填充名称和类别表。我现在面临的填充结台的任务。我敢肯定，我可以创建插入语句用手通过查找，我想匹配的名称和类别的ID，但似乎有点傻。
为了能够自动创建插入语句结合表，我想，基于一组的名称和类别对，将搜索相应的ID和倾倒的INSERT语句，我可以创建一个脚本。 （我想出了这个，因为我是问这个问题，并会研究它。难道你不喜欢它，当这种情况发生？）
没有任何人有什么建议的方法来做到这一点？
修改我加的外键，因为，正如下文，他们会帮助保持表之间的完整性。
编辑＃2 要解决这个问题，我创建了一个简单的Perl脚本，将采取一个文本文件的名称 - 类别对和倾倒出来与适当的SQL语句的另一个文件
的名称 - 类文本文件具有格式如下：
名称类别
Perl脚本是这样的：
使用严格的;
使用警告;
开放式（我$ name_category_pair_file，＆LT;，name_category.txt）或死无法打开name_category.txt：$！;
开放式（我$ output_sql_file，＆gt;中，load_name_category_junction_table.sqlite）或死无法打开load_name_category_junction_table.sqlite：$！;
而（小于$ name_category_pair_file＆GT）{
如果（/（'[A-ZA-Z] *）（[A-ZA-Z] *'）/）{
我的$ sql_statement =INSERT INTO Name_Category VALUES（
（选择_id从名称其中name = $ 1），
（选择_id从类别中，类别= $ 2））\; \ñ\ N的;
打印$ output_sql_file $ sql_statement;
}
}
关闭$ name_category_pair_file或死亡：;$ name_category_pair_file $！
关闭$ output_sql_file或死$ output_sql_file：$！;
您可以在您的脚本或code（替换字符串或使用使用此插入？ ）：
插入Name_Category值（
（请从分类_id，其中类别='CAT1'），
（选择名称其中名称='名称1'_id））;
此外，您还可以改变 Name_Category 表约束下，可以插入和/或删除的值：
CREATE TABLE Name_Category（name_id INTEGER NOT NULL，
CATEGORY_ID INTEGER NOT NULL，
PRIMARY KEY（name_id，CATEGORY_ID）
外键（name_id）引用名称（_id）
外键（CATEGORY_ID）参考目录（_id））;
For a simple android app I'm creating as a teaching tool for myself (for using relational dbs/SQL among other things - pardon the simplicity of the question if you will). I'm pre-creating a sqlite db to ship with the application. I'm doing this based on the following SO question.
I've got two tables with a many to many relationship and a junction table to define those relationships as follows:
CREATE TABLE Names (_id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE Categories (_id INTEGER PRIMARY KEY,
category TEXT
);
CREATE TABLE Name_Category (name_id INTEGER,
category_id INTEGER,
PRIMARY KEY (name_id, category_id),
foreign key (name_id) references Names(_id),
foreign key (category_id) references Categories(_id)
);
I've got sets of insert statements to fill the Names and Categories tables. I'm now faced with the task of filling the junction table. I'm sure that I could create the insert statements by hand by looking up the ids of the names and categories that I want to match, but that seems a bit silly.
In order to automatically create the insert statements for the junction table, I imagine that I could create a script based on a set of name and category pairs that will search for the appropriate ids and dump an insert statement. (I came up with this as I was asking the question and will research it. Don't you love it when that happens?)
Does anybody have any suggestions for ways to do this?
EDIT I added the foreign keys because, as pointed out below, they'll help maintain integrity between the tables.
EDIT #2 To solve this, I created a simple Perl script that would take a text file with name - category pairs and dump them out into another file with the appropriate SQL statements.
The name - category text file has a format as follows:
'Name' 'Category'
The Perl script looks like this:
use strict;
use warnings;
open (my $name_category_pair_file, "<", "name_category.txt") or die "Can't open name_category.txt: $!";
open (my $output_sql_file, ">", "load_name_category_junction_table.sqlite") or die "Can't open load_name_category_junction_table.sqlite: $!";
while (<$name_category_pair_file>) {
if (/('[a-zA-Z ]*') ('[a-zA-Z ]*')/) {
my $sql_statement = "INSERT INTO Name_Category VALUES (
(SELECT _id FROM Names WHERE name = $1),
(SELECT _id FROM Categories WHERE category = $2))\;\n\n";
print $output_sql_file $sql_statement;
}
}
close $name_category_pair_file or die "$name_category_pair_file: $!";
close $output_sql_file or die "$output_sql_file: $!";
You can use this insert in your script or code (replacing the strings or using ?):
insert into Name_Category values(
(select _id from Categories where category='CAT1'),
(select _id from Names where name='NAME1'));
Also, you can alter the Name_Category table to constraint on the values that can be inserted and/or deleted:
CREATE TABLE Name_Category ( name_id INTEGER NOT NULL,
category_id INTEGER NOT NULL,
PRIMARY KEY (name_id, category_id),
foreign key (name_id) references Names(_id),
foreign key (category_id) references Categories(_id));
这篇关于自动创建INSERT语句来填充结表在pre-创建数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
