sqlite.go 3.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143
  1. package database
  2. import (
  3. "database/sql"
  4. "errors"
  5. "os"
  6. "path/filepath"
  7. _ "github.com/mattn/go-sqlite3" // need to blank import this package
  8. )
  9. // == sqlite ==
  10. const dbFilename = "netmaker.db"
  11. // SqliteDB is the db object for sqlite database connections
  12. var SqliteDB *sql.DB
  13. // SQLITE_FUNCTIONS - contains a map of the functions for sqlite
  14. var SQLITE_FUNCTIONS = map[string]interface{}{
  15. INIT_DB: initSqliteDB,
  16. CREATE_TABLE: sqliteCreateTable,
  17. INSERT: sqliteInsert,
  18. INSERT_PEER: sqliteInsertPeer,
  19. DELETE: sqliteDeleteRecord,
  20. DELETE_ALL: sqliteDeleteAllRecords,
  21. FETCH_ALL: sqliteFetchRecords,
  22. CLOSE_DB: sqliteCloseDB,
  23. isConnected: sqliteConnected,
  24. }
  25. func initSqliteDB() error {
  26. // == create db file if not present ==
  27. if _, err := os.Stat("data"); os.IsNotExist(err) {
  28. os.Mkdir("data", 0700)
  29. }
  30. dbFilePath := filepath.Join("data", dbFilename)
  31. if _, err := os.Stat(dbFilePath); os.IsNotExist(err) {
  32. os.Create(dbFilePath)
  33. }
  34. // == "connect" the database ==
  35. var dbOpenErr error
  36. SqliteDB, dbOpenErr = sql.Open("sqlite3", dbFilePath)
  37. if dbOpenErr != nil {
  38. return dbOpenErr
  39. }
  40. SqliteDB.SetMaxOpenConns(1)
  41. return nil
  42. }
  43. func sqliteCreateTable(tableName string) error {
  44. statement, err := SqliteDB.Prepare("CREATE TABLE IF NOT EXISTS " + tableName + " (key TEXT NOT NULL UNIQUE PRIMARY KEY, value TEXT)")
  45. if err != nil {
  46. return err
  47. }
  48. defer statement.Close()
  49. _, err = statement.Exec()
  50. if err != nil {
  51. return err
  52. }
  53. return nil
  54. }
  55. func sqliteInsert(key string, value string, tableName string) error {
  56. if key != "" && value != "" && IsJSONString(value) {
  57. insertSQL := "INSERT OR REPLACE INTO " + tableName + " (key, value) VALUES (?, ?)"
  58. statement, err := SqliteDB.Prepare(insertSQL)
  59. if err != nil {
  60. return err
  61. }
  62. defer statement.Close()
  63. _, err = statement.Exec(key, value)
  64. if err != nil {
  65. return err
  66. }
  67. return nil
  68. }
  69. return errors.New("invalid insert " + key + " : " + value)
  70. }
  71. func sqliteInsertPeer(key string, value string) error {
  72. if key != "" && value != "" && IsJSONString(value) {
  73. err := sqliteInsert(key, value, PEERS_TABLE_NAME)
  74. if err != nil {
  75. return err
  76. }
  77. return nil
  78. }
  79. return errors.New("invalid peer insert " + key + " : " + value)
  80. }
  81. func sqliteDeleteRecord(tableName string, key string) error {
  82. deleteSQL := "DELETE FROM " + tableName + " WHERE key = \"" + key + "\""
  83. statement, err := SqliteDB.Prepare(deleteSQL)
  84. if err != nil {
  85. return err
  86. }
  87. defer statement.Close()
  88. if _, err = statement.Exec(); err != nil {
  89. return err
  90. }
  91. return nil
  92. }
  93. func sqliteDeleteAllRecords(tableName string) error {
  94. deleteSQL := "DELETE FROM " + tableName
  95. statement, err := SqliteDB.Prepare(deleteSQL)
  96. if err != nil {
  97. return err
  98. }
  99. defer statement.Close()
  100. if _, err = statement.Exec(); err != nil {
  101. return err
  102. }
  103. return nil
  104. }
  105. func sqliteFetchRecords(tableName string) (map[string]string, error) {
  106. row, err := SqliteDB.Query("SELECT * FROM " + tableName + " ORDER BY key")
  107. if err != nil {
  108. return nil, err
  109. }
  110. records := make(map[string]string)
  111. defer row.Close()
  112. for row.Next() { // Iterate and fetch the records from result cursor
  113. var key string
  114. var value string
  115. row.Scan(&key, &value)
  116. records[key] = value
  117. }
  118. if len(records) == 0 {
  119. return nil, errors.New(NO_RECORDS)
  120. }
  121. return records, nil
  122. }
  123. func sqliteCloseDB() {
  124. SqliteDB.Close()
  125. }
  126. func sqliteConnected() bool {
  127. stats := SqliteDB.Stats()
  128. return stats.OpenConnections > 0
  129. }