<pre style='margin:0'>
Umesh Singla (umeshksingla) pushed a commit to branch gsoc17-migrate
in repository macports-base.

</pre>
<p><a href="https://github.com/macports/macports-base/commit/09631351968694cd5d9937bd764468731a55d8d0">https://github.com/macports/macports-base/commit/09631351968694cd5d9937bd764468731a55d8d0</a></p>
<pre style="white-space: pre; background: #F8F8F8"><span style='display:block; white-space:pre;color:#808000;'>commit 09631351968694cd5d9937bd764468731a55d8d0
</span>Author: Umesh Singla <umeshksingla@macports.org>
AuthorDate: Mon Jun 26 01:23:25 2017 +0530

<span style='display:block; white-space:pre;color:#404040;'>    Add primary and foreign keys separately
</span><span style='display:block; white-space:pre;color:#404040;'>    
</span><span style='display:block; white-space:pre;color:#404040;'>    Primary key will serve as autoincremented rowid while foreign key to
</span><span style='display:block; white-space:pre;color:#404040;'>    link to a snapshot or snapshot_port. Also, add cascade delete so that
</span><span style='display:block; white-space:pre;color:#404040;'>    on deleting a snapshot, corresponding information is also removed.
</span>---
 src/cregistry/entry.c |  2 +-
 src/cregistry/sql.c   | 26 +++++++++++++++++---------
 2 files changed, 18 insertions(+), 10 deletions(-)

<span style='display:block; white-space:pre;color:#808080;'>diff --git a/src/cregistry/entry.c b/src/cregistry/entry.c
</span><span style='display:block; white-space:pre;color:#808080;'>index b46502f..e43ca49 100644
</span><span style='display:block; white-space:pre;background:#e0e0ff;'>--- a/src/cregistry/entry.c
</span><span style='display:block; white-space:pre;background:#e0e0ff;'>+++ b/src/cregistry/entry.c
</span><span style='display:block; white-space:pre;background:#e0e0e0;'>@@ -1362,7 +1362,7 @@ int snapshot_store_ports(reg_registry* reg, reg_entry* entry, reg_error* errPtr)
</span>             sqlite3_stmt* stmt = NULL;
             if(reg_entry_propget(entries[i], key1, &port_name, &error)
                 && reg_entry_propget(entries[i], key2, &requested, &error)){
<span style='display:block; white-space:pre;background:#ffe0e0;'>-                char* query = "INSERT INTO registry.snapshot_ports (id, port_name, requested) "
</span><span style='display:block; white-space:pre;background:#e0ffe0;'>+                char* query = "INSERT INTO registry.snapshot_ports (snapshots_id, port_name, requested) "
</span>                     "VALUES (?, ?, ?)";
                 // entry->id is snapshot's id
                 if ((sqlite3_prepare_v2(reg->db, query, -1, &stmt, NULL) == SQLITE_OK)
<span style='display:block; white-space:pre;color:#808080;'>diff --git a/src/cregistry/sql.c b/src/cregistry/sql.c
</span><span style='display:block; white-space:pre;color:#808080;'>index 0e00614..1f0b9d4 100644
</span><span style='display:block; white-space:pre;background:#e0e0ff;'>--- a/src/cregistry/sql.c
</span><span style='display:block; white-space:pre;background:#e0e0ff;'>+++ b/src/cregistry/sql.c
</span><span style='display:block; white-space:pre;background:#e0e0e0;'>@@ -204,20 +204,24 @@ int create_tables(sqlite3* db, reg_error* errPtr) {
</span>         /* snapshot ports table */
         /* a complete copy of all the installed ports for a snapshot */
         "CREATE TABLE registry.snapshot_ports ("
<span style='display:block; white-space:pre;background:#ffe0e0;'>-              "id INTEGER"
</span><span style='display:block; white-space:pre;background:#e0ffe0;'>+              "id INTEGER PRIMARY KEY"
</span><span style='display:block; white-space:pre;background:#e0ffe0;'>+            ", snapshots_id INTEGER"
</span>             ", port_name TEXT COLLATE NOCASE"
             ", requested INTEGER"
<span style='display:block; white-space:pre;background:#ffe0e0;'>-            ", FOREIGN KEY(id) REFERENCES snapshots(id))",
</span><span style='display:block; white-space:pre;background:#e0ffe0;'>+            ", FOREIGN KEY(snapshots_id) REFERENCES snapshots(id))"
</span><span style='display:block; white-space:pre;background:#e0ffe0;'>+            " ON DELETE CASCADE",
</span>         "CREATE INDEX registry.snapshot_port ON snapshot_ports"
             "(id, port_name)",
 
         /* snapshot port variants table */
         /* all variants (+, -) of the ports in a snapshot */
         "CREATE TABLE registry.snapshot_port_variants ("
<span style='display:block; white-space:pre;background:#ffe0e0;'>-              "id INTEGER"
</span><span style='display:block; white-space:pre;background:#e0ffe0;'>+              "id INTEGER PRIMARY KEY"
</span><span style='display:block; white-space:pre;background:#e0ffe0;'>+            ", snapshot_ports_id INTEGER"
</span>             ", variant_name TEXT COLLATE NOCASE"
             ", variant_sign TEXT"
<span style='display:block; white-space:pre;background:#ffe0e0;'>-            ", FOREIGN KEY(id) REFERENCES snapshot_ports(id))",
</span><span style='display:block; white-space:pre;background:#e0ffe0;'>+            ", FOREIGN KEY(snapshot_ports_id) REFERENCES snapshot_ports(id))"
</span><span style='display:block; white-space:pre;background:#e0ffe0;'>+            " ON DELETE CASCADE",
</span>         "CREATE INDEX registry.snapshot_port_variant ON snapshot_port_variants(id)",
 
         "COMMIT",
<span style='display:block; white-space:pre;background:#e0e0e0;'>@@ -628,7 +632,7 @@ int update_db(sqlite3* db, reg_error* errPtr) {
</span>                 /* snapshots table */
                 "CREATE TABLE registry.snapshots ("
                       "id INTEGER PRIMARY KEY"
<span style='display:block; white-space:pre;background:#ffe0e0;'>-                    ", created_at DATETIME"
</span><span style='display:block; white-space:pre;background:#e0ffe0;'>+                    ", created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL"
</span>                     ", note TEXT"
                     ")",
 
<span style='display:block; white-space:pre;background:#e0e0e0;'>@@ -636,20 +640,24 @@ int update_db(sqlite3* db, reg_error* errPtr) {
</span> 
                 /* snapshot ports table */
                 "CREATE TABLE registry.snapshot_ports ("
<span style='display:block; white-space:pre;background:#ffe0e0;'>-                      "id INTEGER"
</span><span style='display:block; white-space:pre;background:#e0ffe0;'>+                      "id INTEGER PRIMARY KEY"
</span><span style='display:block; white-space:pre;background:#e0ffe0;'>+                    ", snapshots_id INTEGER"
</span>                     ", port_name TEXT COLLATE NOCASE"
                     ", requested INTEGER"
<span style='display:block; white-space:pre;background:#ffe0e0;'>-                    ", FOREIGN KEY(id) REFERENCES snapshots(id))",
</span><span style='display:block; white-space:pre;background:#e0ffe0;'>+                    ", FOREIGN KEY(snapshots_id) REFERENCES snapshots(id))"
</span><span style='display:block; white-space:pre;background:#e0ffe0;'>+                    " ON DELETE CASCADE",
</span> 
                 "CREATE INDEX registry.snapshot_port ON snapshot_ports"
                     "(id, port_name)",
 
                 /* snapshot port variants table */
                 "CREATE TABLE registry.snapshot_port_variants ("
<span style='display:block; white-space:pre;background:#ffe0e0;'>-                      "id INTEGER"
</span><span style='display:block; white-space:pre;background:#e0ffe0;'>+                      "id INTEGER PRIMARY KEY"
</span><span style='display:block; white-space:pre;background:#e0ffe0;'>+                    ", snapshot_ports_id INTEGER"
</span>                     ", variant_name TEXT COLLATE NOCASE"
                     ", variant_sign TEXT"
<span style='display:block; white-space:pre;background:#ffe0e0;'>-                    ", FOREIGN KEY(id) REFERENCES snapshot_ports(id))",
</span><span style='display:block; white-space:pre;background:#e0ffe0;'>+                    ", FOREIGN KEY(snapshot_ports_id) REFERENCES snapshot_ports(id))"
</span><span style='display:block; white-space:pre;background:#e0ffe0;'>+                    " ON DELETE CASCADE",
</span> 
                 "CREATE INDEX registry.snapshot_port_variant ON snapshot_port_variants(id)",
 
</pre><pre style='margin:0'>

</pre>