-
Notifications
You must be signed in to change notification settings - Fork 39
/
postgresql.go
4726 lines (4443 loc) · 143 KB
/
postgresql.go
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
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
package common
import (
"crypto/md5"
"crypto/sha256"
"encoding/base64"
"encoding/hex"
"errors"
"fmt"
"log"
"net/url"
"os"
"sort"
"strings"
"time"
"github.com/hectane/hectane/email"
"github.com/hectane/hectane/queue"
"github.com/jackc/pgx"
gfm "github.com/sqlitebrowser/github_flavored_markdown"
"golang.org/x/crypto/bcrypt"
)
var (
// PostgreSQL connection pool handle
pdb *pgx.ConnPool
)
// AddDefaultUser adds the default user to the system, so the referential integrity of licence user_id 0 works
func AddDefaultUser() error {
// Add the new user to the database
dbQuery := `
INSERT INTO users (auth0_id, user_name, email, password_hash, client_cert, display_name)
VALUES ($1, $2, $3, $4, $5, $6)
ON CONFLICT (user_name)
DO NOTHING`
_, err := pdb.Exec(dbQuery, RandomString(16), "default", "default@dbhub.io", RandomString(16), "",
"Default system user")
if err != nil {
log.Printf("Error when adding the default user to the database: %v\n", err)
// For now, don't bother logging a failure here. This *might* need changing later on
return err
}
// Log addition of the default user
log.Println("Default user added")
return nil
}
// AddUser adds a user to the system
func AddUser(auth0ID, userName, password, email, displayName, avatarURL string) error {
// Hash the user's password
hash, err := bcrypt.GenerateFromPassword([]byte(password), bcrypt.DefaultCost)
if err != nil {
log.Printf("Failed to hash user password. User: '%v', error: %v.\n", userName, err)
return err
}
// Generate a new HTTPS client certificate for the user
var cert []byte
if Conf.Sign.Enabled {
cert, err = GenerateClientCert(userName)
if err != nil {
log.Printf("Error when generating client certificate for '%s': %v\n", userName, err)
return err
}
}
// If the display name or avatar URL are an empty string, we insert a NULL instead
var av, dn pgx.NullString
if displayName != "" {
dn.String = displayName
dn.Valid = true
}
if avatarURL != "" {
av.String = avatarURL
av.Valid = true
}
// Add the new user to the database
insertQuery := `
INSERT INTO users (auth0_id, user_name, email, password_hash, client_cert, display_name, avatar_url)
VALUES ($1, $2, $3, $4, $5, $6, $7)`
commandTag, err := pdb.Exec(insertQuery, auth0ID, userName, email, hash, cert, dn, av)
if err != nil {
log.Printf("Adding user to database failed: %v\n", err)
return err
}
if numRows := commandTag.RowsAffected(); numRows != 1 {
log.Printf("Wrong number of rows affected when creating user: %v, username: %v\n", numRows, userName)
}
// Log the user registration
log.Printf("User registered: '%s' Email: '%s'\n", userName, email)
return nil
}
// APIKeySave saves a new API key to the PostgreSQL database
func APIKeySave(key, loggedInUser string, dateCreated time.Time) error {
// Make sure the API key isn't already in the database
dbQuery := `
SELECT count(key)
FROM api_keys
WHERE key = $1`
var keyCount int
err := pdb.QueryRow(dbQuery, key).Scan(&keyCount)
if err != nil {
log.Printf("Checking if an API key exists failed: %v\n", err)
return err
}
if keyCount != 0 {
// API key is already in our system
log.Printf("Duplicate API key (%s) generated for user '%s'\n", key, loggedInUser)
return fmt.Errorf("API generator created duplicate key. Try again, just in case...")
}
// Add the new API key to the database
dbQuery = `
INSERT INTO api_keys (user_id, key, date_created)
SELECT (SELECT user_id FROM users WHERE lower(user_name) = lower($1)), $2, $3`
commandTag, err := pdb.Exec(dbQuery, loggedInUser, key, dateCreated)
if err != nil {
log.Printf("Adding API key to database failed: %v\n", err)
return err
}
if numRows := commandTag.RowsAffected(); numRows != 1 {
log.Printf("Wrong number of rows (%d) affected when adding API key: %v, username: %v\n", numRows, key,
loggedInUser)
}
return nil
}
// CheckDBExists checks if a database exists
// If an error occurred, the true/false value should be ignored, as only the error value is valid
func CheckDBExists(loggedInUser, dbOwner, dbFolder, dbName string) (bool, error) {
dbQuery := `
SELECT count(db_id)
FROM sqlite_databases
WHERE user_id = (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($1)
)
AND folder = $2
AND db_name = $3
AND is_deleted = false`
// If the request is from someone who's not logged in, or is for another users database, ensure we only consider
// public databases
if strings.ToLower(loggedInUser) != strings.ToLower(dbOwner) || loggedInUser == "" {
dbQuery += `
AND public = true`
}
var DBCount int
err := pdb.QueryRow(dbQuery, dbOwner, dbFolder, dbName).Scan(&DBCount)
if err != nil {
log.Printf("Checking if a database exists failed: %v\n", err)
return true, err
}
if DBCount == 0 {
// Database isn't in our system
return false, nil
}
// Database exists
return true, nil
}
// CheckDBID checks if a given database ID is available, and returns it's folder/name so the caller can determine if it
// has been renamed. If an error occurs, the true/false value should be ignored, as only the error value is valid
func CheckDBID(loggedInUser, dbOwner string, dbID int64) (avail bool, dbFolder, dbName string, err error) {
dbQuery := `
SELECT folder, db_name
FROM sqlite_databases
WHERE user_id = (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($1)
)
AND db_id = $2
AND is_deleted = false`
// If the request is from someone who's not logged in, or is for another users database, ensure we only consider
// public databases
if strings.ToLower(loggedInUser) != strings.ToLower(dbOwner) || loggedInUser == "" {
dbQuery += `
AND public = true`
}
err = pdb.QueryRow(dbQuery, dbOwner, dbID).Scan(&dbFolder, &dbName)
if err != nil {
if err == pgx.ErrNoRows {
avail = false
} else {
log.Printf("Checking if a database exists failed: %v\n", err)
}
return
}
// Database exists
avail = true
return
}
// CheckDBStarred check if a database has been starred by a given user. The boolean return value is only valid when
// err is nil
func CheckDBStarred(loggedInUser, dbOwner, dbFolder, dbName string) (bool, error) {
dbQuery := `
SELECT count(db_id)
FROM database_stars
WHERE database_stars.user_id = (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($4)
)
AND database_stars.db_id = (
SELECT db_id
FROM sqlite_databases
WHERE user_id = (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($1)
)
AND folder = $2
AND db_name = $3
AND is_deleted = false)`
var starCount int
err := pdb.QueryRow(dbQuery, dbOwner, dbFolder, dbName, loggedInUser).Scan(&starCount)
if err != nil {
log.Printf("Error looking up star count for database. User: '%s' DB: '%s/%s'. Error: %v\n",
loggedInUser, dbOwner, dbName, err)
return true, err
}
if starCount == 0 {
// Database hasn't been starred by the user
return false, nil
}
// Database HAS been starred by the user
return true, nil
}
// CheckDBWatched checks if a database is being watched by a given user. The boolean return value is only valid when
// err is nil
func CheckDBWatched(loggedInUser, dbOwner, dbFolder, dbName string) (bool, error) {
dbQuery := `
SELECT count(db_id)
FROM watchers
WHERE user_id = (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($4)
)
AND db_id = (
SELECT db_id
FROM sqlite_databases
WHERE user_id = (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($1)
)
AND folder = $2
AND db_name = $3
AND is_deleted = false)`
var watchCount int
err := pdb.QueryRow(dbQuery, dbOwner, dbFolder, dbName, loggedInUser).Scan(&watchCount)
if err != nil {
log.Printf("Error looking up watchers count for database. User: '%s' DB: '%s%s%s'. Error: %v\n",
loggedInUser, dbOwner, dbFolder, dbName, err)
return true, err
}
if watchCount == 0 {
// Database isn't being watched by the user
return false, nil
}
// Database IS being watched by the user
return true, nil
}
// CheckEmailExists checks if an email address already exists in our system. Returns true if the email is already in
// the system, false if not. If an error occurred, the true/false value should be ignored, as only the error value
// is valid
func CheckEmailExists(email string) (bool, error) {
// Check if the email address is already in our system
dbQuery := `
SELECT count(user_name)
FROM users
WHERE email = $1`
var emailCount int
err := pdb.QueryRow(dbQuery, email).Scan(&emailCount)
if err != nil {
log.Printf("Database query failed: %v\n", err)
return true, err
}
if emailCount == 0 {
// Email address isn't yet in our system
return false, nil
}
// Email address IS already in our system
return true, nil
}
// CheckLicenceExists checks if a given licence exists in our system
func CheckLicenceExists(userName, licenceName string) (exists bool, err error) {
dbQuery := `
SELECT count(*)
FROM database_licences
WHERE friendly_name = $2
AND (user_id = (
SELECT user_id
FROM users
WHERE user_name = 'default'
)
OR user_id = (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($1)
))`
var count int
err = pdb.QueryRow(dbQuery, userName, licenceName).Scan(&count)
if err != nil {
log.Printf("Error checking if licence '%s' exists for user '%s' in database: %v\n", licenceName,
userName, err)
return false, err
}
if count == 0 {
// The requested licence wasn't found
return false, nil
}
return true, nil
}
// CheckUserExists checks if a username already exists in our system. Returns true if the username is already taken,
// false if not. If an error occurred, the true/false value should be ignored, and only the error return code used
func CheckUserExists(userName string) (bool, error) {
dbQuery := `
SELECT count(user_id)
FROM users
WHERE lower(user_name) = lower($1)`
var userCount int
err := pdb.QueryRow(dbQuery, userName).Scan(&userCount)
if err != nil {
log.Printf("Database query failed: %v\n", err)
return true, err
}
if userCount == 0 {
// Username isn't in system
return false, nil
}
// Username IS in system
return true, nil
}
// ConnectPostgreSQL creates a connection pool to the PostgreSQL server
func ConnectPostgreSQL() (err error) {
pgPoolConfig := pgx.ConnPoolConfig{*pgConfig, Conf.Pg.NumConnections, nil, 2 * time.Second}
pdb, err = pgx.NewConnPool(pgPoolConfig)
if err != nil {
return fmt.Errorf("Couldn't connect to PostgreSQL server: %v\n", err)
}
// Log successful connection
log.Printf("Connected to PostgreSQL server: %v:%v\n", Conf.Pg.Server, uint16(Conf.Pg.Port))
return nil
}
// databaseID returns the ID number for a given user's database
func databaseID(dbOwner, dbFolder, dbName string) (dbID int, err error) {
// Retrieve the database id
dbQuery := `
SELECT db_id
FROM sqlite_databases
WHERE user_id = (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($1))
AND folder = $2
AND db_name = $3
AND is_deleted = false`
err = pdb.QueryRow(dbQuery, dbOwner, dbFolder, dbName).Scan(&dbID)
if err != nil {
log.Printf("Error looking up database id. Owner: '%s', Database: '%s'. Error: %v\n", dbOwner, dbName,
err)
}
return
}
// DB4SDefaultList returns a list of 1) users with public databases, 2) along with the logged in users' most recently
// modified database (including their private one(s))
func DB4SDefaultList(loggedInUser string) (UserInfoSlice, error) {
// Retrieve the list of all users with public databases
dbQuery := `
WITH public_dbs AS (
SELECT db_id, last_modified
FROM sqlite_databases
WHERE public = true
AND is_deleted = false
ORDER BY last_modified DESC
), public_users AS (
SELECT DISTINCT ON (db.user_id) db.user_id, db.last_modified
FROM public_dbs as pub, sqlite_databases AS db
WHERE db.db_id = pub.db_id
ORDER BY db.user_id, db.last_modified DESC
)
SELECT user_name, last_modified
FROM public_users AS pu, users
WHERE users.user_id = pu.user_id
AND users.user_name != $1
ORDER BY last_modified DESC`
rows, err := pdb.Query(dbQuery, loggedInUser)
if err != nil {
log.Printf("Database query failed: %v\n", err)
return nil, err
}
defer rows.Close()
unsorted := make(map[string]UserInfo)
for rows.Next() {
var oneRow UserInfo
err = rows.Scan(&oneRow.Username, &oneRow.LastModified)
if err != nil {
log.Printf("Error list of users with public databases: %v\n", err)
return nil, err
}
unsorted[oneRow.Username] = oneRow
}
// Sort the list by last_modified order, from most recent to oldest
publicList := make(UserInfoSlice, 0, len(unsorted))
for _, j := range unsorted {
publicList = append(publicList, j)
}
sort.Sort(publicList)
// Retrieve the last modified timestamp for the most recent database of the logged in user (if they have any)
dbQuery = `
WITH u AS (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($1)
), user_db_list AS (
SELECT DISTINCT ON (db_id) db_id, last_modified
FROM sqlite_databases AS db, u
WHERE db.user_id = u.user_id
AND is_deleted = false
), most_recent_user_db AS (
SELECT udb.last_modified
FROM user_db_list AS udb
ORDER BY udb.last_modified DESC
LIMIT 1
)
SELECT last_modified
FROM most_recent_user_db`
userRow := UserInfo{Username: loggedInUser}
rows, err = pdb.Query(dbQuery, loggedInUser)
if err != nil {
log.Printf("Database query failed: %v\n", err)
return nil, err
}
defer rows.Close()
userHasDB := false
for rows.Next() {
userHasDB = true
err = rows.Scan(&userRow.LastModified)
if err != nil {
log.Printf("Error retrieving database list for user: %v\n", err)
return nil, err
}
}
// If the user doesn't have any databases, just return the list of users with public databases
if !userHasDB {
return publicList, nil
}
// The user does have at least one database, so include them at the top of the list
completeList := make(UserInfoSlice, 0, len(unsorted)+1)
completeList = append(completeList, userRow)
completeList = append(completeList, publicList...)
return completeList, nil
}
// DBDetails returns the details for a specific database
func DBDetails(DB *SQLiteDBinfo, loggedInUser, dbOwner, dbFolder, dbName, commitID string) error {
// If no commit ID was supplied, we retrieve the latest commit one from the default branch
var err error
if commitID == "" {
commitID, err = DefaultCommit(dbOwner, dbFolder, dbName)
if err != nil {
return err
}
}
// Retrieve the database details
dbQuery := `
SELECT db.date_created, db.last_modified, db.watchers, db.stars, db.discussions, db.merge_requests,
$4::text AS commit_id, db.commit_list->$4::text->'tree'->'entries'->0 AS db_entry,
db.branches, db.release_count, db.contributors, db.one_line_description, db.full_description,
db.default_table, db.public, db.source_url, db.tags, db.default_branch
FROM sqlite_databases AS db
WHERE db.user_id = (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($1)
)
AND db.folder = $2
AND db.db_name = $3
AND db.is_deleted = false`
// If the request is for another users database, ensure we only look up public ones
if strings.ToLower(loggedInUser) != strings.ToLower(dbOwner) {
dbQuery += `
AND db.public = true`
}
// Generate a predictable cache key for this functions' metadata. Probably not sharable with other functions
// cached metadata
mdataCacheKey := MetadataCacheKey("meta", loggedInUser, dbOwner, dbFolder, dbName, commitID)
// Use a cached version of the query response if it exists
ok, err := GetCachedData(mdataCacheKey, &DB)
if err != nil {
log.Printf("Error retrieving data from cache: %v\n", err)
}
if ok {
// Data was in cache, so we use that
return nil
}
// Retrieve the requested database details
var defTable, fullDesc, oneLineDesc, sourceURL pgx.NullString
err = pdb.QueryRow(dbQuery, dbOwner, dbFolder, dbName, commitID).Scan(&DB.Info.DateCreated,
&DB.Info.RepoModified, &DB.Info.Watchers, &DB.Info.Stars, &DB.Info.Discussions, &DB.Info.MRs,
&DB.Info.CommitID,
&DB.Info.DBEntry,
&DB.Info.Branches, &DB.Info.Releases, &DB.Info.Contributors, &oneLineDesc, &fullDesc, &defTable,
&DB.Info.Public, &sourceURL, &DB.Info.Tags, &DB.Info.DefaultBranch)
if err != nil {
log.Printf("Error when retrieving database details: %v\n", err.Error())
return errors.New("The requested database doesn't exist")
}
if !oneLineDesc.Valid {
DB.Info.OneLineDesc = "No description"
} else {
DB.Info.OneLineDesc = oneLineDesc.String
}
if !fullDesc.Valid {
DB.Info.FullDesc = "No full description"
} else {
DB.Info.FullDesc = fullDesc.String
}
if !defTable.Valid {
DB.Info.DefaultTable = ""
} else {
DB.Info.DefaultTable = defTable.String
}
if !sourceURL.Valid {
DB.Info.SourceURL = ""
} else {
DB.Info.SourceURL = sourceURL.String
}
// Fill out the fields we already have data for
DB.Info.Database = dbName
DB.Info.Folder = dbFolder
// Retrieve latest fork count
// TODO: This can probably be folded into the above SQL query as a sub-select, as a minor optimisation
dbQuery = `
SELECT forks
FROM sqlite_databases
WHERE db_id = (
SELECT root_database
FROM sqlite_databases
WHERE user_id = (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($1))
AND folder = $2
AND db_name = $3)`
err = pdb.QueryRow(dbQuery, dbOwner, dbFolder, dbName).Scan(&DB.Info.Forks)
if err != nil {
log.Printf("Error retrieving fork count for '%s%s%s': %v\n", dbOwner, dbFolder, dbName, err)
return err
}
// Cache the database details
err = CacheData(mdataCacheKey, DB, Conf.Memcache.DefaultCacheTime)
if err != nil {
log.Printf("Error when caching page data: %v\n", err)
}
return nil
}
// DBStars returns the star count for a given database
func DBStars(dbOwner, dbFolder, dbName string) (starCount int, err error) {
// Retrieve the updated star count
dbQuery := `
SELECT stars
FROM sqlite_databases
WHERE user_id = (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($1)
)
AND folder = $2
AND db_name = $3
AND is_deleted = false`
err = pdb.QueryRow(dbQuery, dbOwner, dbFolder, dbName).Scan(&starCount)
if err != nil {
log.Printf("Error looking up star count for database '%s/%s'. Error: %v\n", dbOwner, dbName, err)
return -1, err
}
return starCount, nil
}
// DBWatchers returns the watchers count for a given database
func DBWatchers(dbOwner, dbFolder, dbName string) (watcherCount int, err error) {
// Retrieve the updated watchers count
dbQuery := `
SELECT watchers
FROM sqlite_databases
WHERE user_id = (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($1)
)
AND folder = $2
AND db_name = $3
AND is_deleted = false`
err = pdb.QueryRow(dbQuery, dbOwner, dbFolder, dbName).Scan(&watcherCount)
if err != nil {
log.Printf("Error looking up watcher count for database '%s%s%s'. Error: %v\n", dbOwner, dbFolder,
dbName, err)
return -1, err
}
return watcherCount, nil
}
// DefaultCommit returns the default commit ID for a specific database
func DefaultCommit(dbOwner, dbFolder, dbName string) (string, error) {
// If no commit ID was supplied, we retrieve the latest commit ID from the default branch
dbQuery := `
SELECT branch_heads->default_branch->'commit' AS commit_id
FROM sqlite_databases
WHERE user_id = (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($1)
)
AND folder = $2
AND db_name = $3
AND is_deleted = false`
var commitID string
err := pdb.QueryRow(dbQuery, dbOwner, dbFolder, dbName).Scan(&commitID)
if err != nil {
log.Printf("Error when retrieving head commit ID of default branch: %v\n", err.Error())
return "", errors.New("Internal error when looking up database details")
}
return commitID, nil
}
// DeleteComment deletes a specific comment from a discussion
func DeleteComment(dbOwner, dbFolder, dbName string, discID, comID int) error {
// Begin a transaction
tx, err := pdb.Begin()
if err != nil {
return err
}
// Set up an automatic transaction roll back if the function exits without committing
defer tx.Rollback()
// Delete the requested discussion comment
dbQuery := `
WITH d AS (
SELECT db.db_id
FROM sqlite_databases AS db
WHERE db.user_id = (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($1)
)
AND folder = $2
AND db_name = $3
), int AS (
SELECT internal_id AS int_id
FROM discussions
WHERE db_id = (SELECT db_id FROM d)
AND disc_id = $4
)
DELETE FROM discussion_comments
WHERE db_id = (SELECT db_id FROM d)
AND disc_id = (SELECT int_id FROM int)
AND com_id = $5`
commandTag, err := tx.Exec(dbQuery, dbOwner, dbFolder, dbName, discID, comID)
if err != nil {
log.Printf("Deleting comment '%d' from '%s%s%s', discussion '%d' failed: %v\n", comID, dbOwner,
dbFolder, dbName, discID, err)
return err
}
if numRows := commandTag.RowsAffected(); numRows != 1 {
log.Printf("Wrong number of rows (%v) affected when deleting comment '%d' from database '%s%s%s, discussion '%d''\n",
numRows, comID, dbOwner, dbFolder, dbName, discID)
}
// Update the comment count and last modified date for the discussion
dbQuery = `
WITH d AS (
SELECT db.db_id
FROM sqlite_databases AS db
WHERE db.user_id = (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($1)
)
AND folder = $2
AND db_name = $3
), int AS (
SELECT internal_id AS int_id
FROM discussions
WHERE db_id = (SELECT db_id FROM d)
AND disc_id = $4
), new AS (
SELECT count(*)
FROM discussion_comments
WHERE db_id = (SELECT db_id FROM d)
AND disc_id = (SELECT int_id FROM int)
AND entry_type = 'txt'
)
UPDATE discussions
SET comment_count = (SELECT count FROM new), last_modified = now()
WHERE internal_id = (SELECT int_id FROM int)`
commandTag, err = tx.Exec(dbQuery, dbOwner, dbFolder, dbName, discID)
if err != nil {
log.Printf("Updating comment count for discussion '%v' of '%s%s%s' in PostgreSQL failed: %v\n",
discID, dbOwner, dbFolder, dbName, err)
return err
}
if numRows := commandTag.RowsAffected(); numRows != 1 {
log.Printf("Wrong number of rows (%v) affected when updating comment count for discussion '%v' in "+
"'%s%s%s'\n", numRows, discID, dbOwner, dbFolder, dbName)
}
// Commit the transaction
err = tx.Commit()
if err != nil {
return err
}
return nil
}
// DeleteDatabase deletes a database from PostgreSQL
func DeleteDatabase(dbOwner, dbFolder, dbName string) error {
// TODO: At some point we'll need to figure out a garbage collection approach to remove databases from Minio which
// TODO are no longer pointed to by anything
// Begin a transaction
tx, err := pdb.Begin()
if err != nil {
return err
}
// Set up an automatic transaction roll back if the function exits without committing
defer tx.Rollback()
// Check if there are any forks of this database
dbQuery := `
WITH this_db AS (
SELECT db_id
FROM sqlite_databases
WHERE user_id = (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($1)
)
AND folder = $2
AND db_name = $3
)
SELECT count(*)
FROM sqlite_databases AS db, this_db
WHERE db.forked_from = this_db.db_id`
var numForks int
err = tx.QueryRow(dbQuery, dbOwner, dbFolder, dbName).Scan(&numForks)
if err != nil {
log.Printf("Retreving fork list failed for database '%s%s%s': %v\n", dbOwner, dbFolder, dbName, err)
return err
}
if numForks == 0 {
// * There are no forks for this database, so we just remove it's entry from sqlite_databases. The 'ON DELETE
// CASCADE' definition for the database_stars table/field should automatically remove any references to the
// now deleted entry *
// Update the fork count for the root database
dbQuery = `
WITH root_db AS (
SELECT root_database AS id
FROM sqlite_databases
WHERE user_id = (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($1)
)
AND folder = $2
AND db_name = $3
), new_count AS (
SELECT count(*) AS forks
FROM sqlite_databases AS db, root_db
WHERE db.root_database = root_db.id
AND db.is_deleted = false
)
UPDATE sqlite_databases
SET forks = new_count.forks - 2
FROM new_count, root_db
WHERE sqlite_databases.db_id = root_db.id`
commandTag, err := tx.Exec(dbQuery, dbOwner, dbFolder, dbName)
if err != nil {
log.Printf("Updating fork count for '%s%s%s' in PostgreSQL failed: %v\n", dbOwner, dbFolder, dbName,
err)
return err
}
if numRows := commandTag.RowsAffected(); numRows != 1 {
log.Printf("Wrong number of rows (%v) affected (spot 1) when updating fork count for database '%s%s%s'\n",
numRows, dbOwner, dbFolder, dbName)
}
// Do the database deletion in PostgreSQL (needs to come after the above fork count update, else the fork count
// won't be able to find the root database id)
dbQuery = `
DELETE
FROM sqlite_databases
WHERE user_id = (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($1)
)
AND folder = $2
AND db_name = $3`
commandTag, err = tx.Exec(dbQuery, dbOwner, dbFolder, dbName)
if err != nil {
log.Printf("Deleting database entry failed for database '%s%s%s': %v\n", dbOwner, dbFolder, dbName,
err)
return err
}
if numRows := commandTag.RowsAffected(); numRows != 1 {
log.Printf(
"Wrong number of rows (%v) affected when deleting database '%s%s%s'\n", numRows, dbOwner,
dbFolder, dbName)
}
// Commit the transaction
err = tx.Commit()
if err != nil {
return err
}
// Log the database deletion
log.Printf("Database '%s%s%s' deleted\n", dbOwner, dbFolder, dbName)
return nil
}
// * If there are any forks of this database, we need to leave stub/placeholder info for its entry so the fork tree
// doesn't go weird. We also set the "is_deleted" boolean to true for its entry, so our database query functions
// know to skip it *
// Delete all stars referencing the database stub
dbQuery = `
DELETE FROM database_stars
WHERE db_id = (
SELECT db_id
FROM sqlite_databases
WHERE user_id = (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($1)
)
AND folder = $2
AND db_name = $3
)`
commandTag, err := tx.Exec(dbQuery, dbOwner, dbFolder, dbName)
if err != nil {
log.Printf("Deleting (forked) database stars failed for database '%s%s%s': %v\n", dbOwner, dbFolder,
dbName, err)
return err
}
// Generate a random string to be used in the deleted database's name field, so if the user adds a database with
// the deleted one's name then the unique constraint on the database won't reject it
newName := "deleted-database-" + RandomString(20)
// Replace the database entry in sqlite_databases with a stub
dbQuery = `
UPDATE sqlite_databases AS db
SET is_deleted = true, public = false, db_name = $4, last_modified = now()
WHERE user_id = (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($1)
)
AND folder = $2
AND db_name = $3`
commandTag, err = tx.Exec(dbQuery, dbOwner, dbFolder, dbName, newName)
if err != nil {
log.Printf("Deleting (forked) database entry failed for database '%s%s%s': %v\n", dbOwner, dbFolder,
dbName, err)
return err
}
if numRows := commandTag.RowsAffected(); numRows != 1 {
log.Printf(
"Wrong number of rows (%v) affected when deleting (forked) database '%s%s%s'\n", numRows, dbOwner,
dbFolder, dbName)
}
// Update the fork count for the root database
dbQuery = `
WITH root_db AS (
SELECT root_database AS id
FROM sqlite_databases
WHERE user_id = (
SELECT user_id
FROM users
WHERE lower(user_name) = lower($1)
)
AND folder = $2
AND db_name = $3
), new_count AS (
SELECT count(*) AS forks
FROM sqlite_databases AS db, root_db
WHERE db.root_database = root_db.id
AND db.is_deleted = false
)
UPDATE sqlite_databases
SET forks = new_count.forks - 1
FROM new_count, root_db
WHERE sqlite_databases.db_id = root_db.id`
commandTag, err = tx.Exec(dbQuery, dbOwner, dbFolder, newName)
if err != nil {
log.Printf("Updating fork count for '%s%s%s' in PostgreSQL failed: %v\n", dbOwner, dbFolder, dbName,
err)
return err
}
if numRows := commandTag.RowsAffected(); numRows != 1 {
log.Printf("Wrong number of rows (%v) affected (spot 2) when updating fork count for database '%s%s%s'\n",
numRows, dbOwner, dbFolder, dbName)
}
// Commit the transaction
err = tx.Commit()
if err != nil {
return err
}
// Log the database deletion
log.Printf("(Forked) database '%s%s%s' deleted\n", dbOwner, dbFolder, dbName)
return nil
}
// DeleteLicence removes a (user supplied) database licence from the system
func DeleteLicence(userName, licenceName string) (err error) {
// Begin a transaction
tx, err := pdb.Begin()
if err != nil {
return err
}
// Set up an automatic transaction roll back if the function exits without committing
defer tx.Rollback()
// Don't allow deletion of the default licences
switch licenceName {
case "Not specified":
case "CC0":
case "CC-BY-4.0":
case "CC-BY-SA-4.0":
case "CC-BY-NC-4.0":
case "CC-BY-IGO-3.0":
case "ODbL-1.0":
case "UK-OGL-3":
return errors.New("Default licences can't be removed")
}
// Retrieve the SHA256 for the licence
licSHA, err := GetLicenceSha256FromName(userName, licenceName)
if err != nil {
return err
}
// * Check if there are databases present which use this licence. If there are, then abort. *
// TODO: Get around to adding appropriate GIN indexes
// Note - This uses the JsQuery extension for PostgreSQL, which needs compiling on the server and adding in.
// However, this seems like it'll be much more straight forward and usable for writing queries with than
// than to use straight PG SQL
// JsQuery repo: https://github.com/postgrespro/jsquery
// Some useful examples: https://postgrespro.ru/media/2017/04/04/jsonb-pgconf.us-2017.pdf
dbQuery := `
SELECT DISTINCT count(*)
FROM sqlite_databases AS db