1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
|
use rusqlite;
use yaml_rust::yaml;
fn main() {
println!("Hello, world!");
// Get column names from SQLite
let mut dbconn = rusqlite::Connection::open("./test.db").unwrap();
let table_columns = get_column_names(&dbconn);
dbg!(&table_columns);
let text_data = std::fs::read_to_string("test2.yml").unwrap();
let mut yaml_data = yaml::YamlLoader::load_from_str(&text_data).unwrap();
for mut doc in &mut yaml_data {
let tx = dbconn.transaction().unwrap();
yaml_extract(&mut doc, &tx, &table_columns);
tx.commit().unwrap();
}
dbg!(yaml_data);
dbconn.close().unwrap();
}
fn yaml_extract(
doc: &mut yaml::Yaml,
tx: &rusqlite::Transaction,
table_columns: &HashMap<String, rusqlite::types::Type>,
) {
match doc {
yaml::Yaml::Array(ref mut array) => {
for yaml_value in array {
yaml_extract(yaml_value, tx, table_columns);
}
}
yaml::Yaml::Hash(ref mut hash) => {
// Begin transaction
// for (k, v) in hash {
// // TODO: Put k,v in a HashMap prepared for SQLite interfacing
// // Each hash is a new record for SQLite insertion
//
// // If key matches a column name, add it to the insert statement
//
// if table_columns.contains_key(k.as_str().unwrap()) {
// dbg!(k, v);
// }
// }
// let keys: Vec<&str> = hash
// .keys()
// .map(|k| k.as_str().unwrap())
// .collect();
let keys: Vec<yaml::Yaml> = hash.keys().map(|k| k.clone()).collect();
let columns_as_yaml: Vec<yaml::Yaml> = table_columns.keys()
.map(|c| yaml::Yaml::from_str(c))
.collect();
for key in keys.iter() {
if !columns_as_yaml.contains(key) {
hash.remove(key);
}
}
let mut stmt = tx.prepare(
&format!(
r#"
INSERT INTO "people"
({})
VALUES
({});
"#,
// Wrap column names in quotes.
hash.keys()
.map(|k| format!(r#""{}""#, k.as_str().unwrap()))
.collect::<Vec<String>>()
.join(", "),
// TODO: get len "?"s
format!("{}?", "?, ".repeat(hash.len() - 1)),
)
).unwrap();
let values = hash.values().map(|v| yaqlite::yaml::Yaml(v));
stmt.insert(rusqlite::params_from_iter(values)).unwrap();
// tx.execute(
// r#"
// INSERT INTO "people"
// ()
// VALUES
// ();
// "#,
// []
// ).unwrap();
}
_ => {}
}
}
#[derive(Debug)]
struct Zero {}
use std::collections::HashMap;
fn get_column_names(dbconn: &rusqlite::Connection) -> HashMap<String, rusqlite::types::Type> {
let mut column_names = HashMap::new();
let mut stmt = dbconn.prepare(r#"
SELECT
"name",
"type"
FROM pragma_table_info("people");
"#).unwrap();
let rows = stmt.query_map(
[],
|row| Ok((row.get(0).unwrap(), row.get(1).unwrap())),
).unwrap();
for row_result in rows {
// TODO: Get the type of the column.
// $ sqlite3 -header test.db "select type from pragma_table_info(\"people\");"
// type
// text
// text
// text
// integer
// $ sqlite3 -header test.db "select type from pragma_table_info(\"test\");"
// type
// INTEGER
// TEXT
// DATETIME
// INTEGER
let row = row_result.unwrap();
let type_name: String = row.1;
let type_affinity = yaqlite::sqlite::affinity(&type_name);
column_names.insert(row.0, type_affinity);
}
column_names
}
|
