github.com/code-to-go/safepool.lib@v0.0.0-20221205180519-ee25e63c226e/api/sqlite.sql (about)

     1  -- INIT
     2  CREATE TABLE IF NOT EXISTS identity (
     3      id VARCHAR(256),
     4      i64 VARCHAR(1024),
     5      trusted INTEGER,
     6      PRIMARY KEY(id)
     7  );
     8  
     9  -- INIT
    10  CREATE INDEX IF NOT EXISTS idx_identity_trust ON identity(trusted);
    11  
    12  -- GET_IDENTITY
    13  SELECT i64 FROM identity
    14  
    15  -- GET_TRUSTED
    16  SELECT i64 FROM identity WHERE trusted
    17  
    18  -- SET_TRUSTED
    19  UPDATE identity SET trusted=:trusted WHERE id=:id
    20  
    21  -- SET_IDENTITY
    22  INSERT INTO identity(id,i64) VALUES(:id,:i64)
    23      ON CONFLICT(id) DO UPDATE SET i64=:i64
    24  	WHERE id=:id
    25  
    26  -- INIT
    27  CREATE TABLE IF NOT EXISTS config (
    28      pool VARCHAR(128) NOT NULL, 
    29      k VARCHAR(64) NOT NULL, 
    30      s VARCHAR(64) NOT NULL,
    31      i INTEGER NOT NULL,
    32      b TEXT,
    33      CONSTRAINT pk_safe_key PRIMARY KEY(pool,k)
    34  );
    35  
    36  -- GET_CONFIG
    37  SELECT s, i, b FROM config WHERE pool=:pool AND k=:key
    38  
    39  -- SET_CONFIG
    40  INSERT INTO config(pool,k,s,i,b) VALUES(:pool,:key,:s,:i,:b)
    41  	ON CONFLICT(pool,k) DO UPDATE SET s=:s,i=:i,b=:b
    42  	WHERE pool=:pool AND k=:key
    43  
    44  -- INIT
    45  CREATE TABLE IF NOT EXISTS heads (
    46      pool VARCHAR(128) NOT NULL, 
    47      id INTEGER NOT NULL,
    48      name VARCHAR(8192) NOT NULL, 
    49      modtime INTEGER,
    50      size INTEGER,
    51      hash VARCHAR(128) NOT NULL, 
    52      ts INTEGER,
    53      CONSTRAINT pk_safe_id PRIMARY KEY(pool,id)
    54  )
    55  
    56  -- INIT
    57  CREATE INDEX IF NOT EXISTS idx_heads_id ON heads(id);
    58  
    59  -- GET_HEADS
    60  SELECT id, name, modtime, size, hash, ts FROM heads WHERE pool=:pool AND id > :after AND ts > :afterTime ORDER BY id
    61  
    62  -- SET_HEAD
    63  INSERT INTO heads(pool,id,name,modtime,size,hash,ts) VALUES(:pool,:id,:name,:modtime,:size,:hash,:ts)
    64  
    65  -- INIT
    66  CREATE TABLE IF NOT EXISTS keystore (
    67      pool VARCHAR(128) NOT NULL, 
    68      keyId INTEGER, 
    69      keyValue VARCHAR(128),
    70      CONSTRAINT pk_safe_keyId PRIMARY KEY(pool,keyId)
    71  );
    72  
    73  -- GET_KEYSTORE
    74  SELECT keyId, keyValue FROM keystore WHERE pool=:pool
    75  
    76  -- GET_KEY
    77  SELECT keyValue FROM keystore WHERE pool=:pool AND keyId=:keyId
    78  
    79  -- SET_KEY
    80  INSERT INTO keystore(pool,keyId,keyValue) VALUES(:pool,:keyId,:keyValue)
    81      ON CONFLICT(pool,keyId) DO UPDATE SET keyValue=:keyValue
    82  	    WHERE pool=:pool AND keyId=:keyId
    83  
    84  -- INIT
    85  CREATE TABLE IF NOT EXISTS pool (
    86      name VARCHAR(128),
    87      configs BLOB,
    88      PRIMARY KEY(name)
    89  );
    90  
    91  -- GET_POOL
    92  SELECT configs FROM pool WHERE name=:name
    93  
    94  -- LIST_POOL
    95  SELECT name FROM pool
    96  
    97  -- SET_POOL
    98  INSERT INTO pool(name,configs) VALUES(:name,:configs)
    99      ON CONFLICT(name) DO UPDATE SET configs=:configs
   100  	    WHERE name=:name
   101  
   102  -- INIT
   103  CREATE TABLE IF NOT EXISTS pool_identity (
   104      pool VARCHAR(128),
   105      id VARCHAR(256),
   106      since INTEGER,
   107      ts INTEGER,
   108      CONSTRAINT pk_safe_sig_enc PRIMARY KEY(pool,id)
   109  );
   110  
   111  -- GET_TRUSTED_ON_POOL
   112  SELECT i.i64, ts FROM identity i INNER JOIN pool_identity s WHERE s.pool=:pool AND i.id = s.id AND i.trusted
   113  
   114  -- GET_IDENTITY_ON_POOL
   115  SELECT i.i64,since,ts FROM identity i INNER JOIN pool_identity s WHERE s.pool=:pool AND i.id = s.id 
   116  
   117  -- SET_IDENTITY_ON_POOL
   118  INSERT INTO pool_identity(pool,id,since,ts) VALUES(:pool,:id,:since,:ts)
   119      ON CONFLICT(pool,id) DO NOTHING
   120  
   121  -- DEL_IDENTITY_ON_POOL
   122  DELETE FROM pool_identity WHERE id=:id AND pool=:pool
   123  
   124  -- INIT
   125  CREATE TABLE IF NOT EXISTS chat (
   126      pool VARCHAR(128),
   127      id INTEGER,
   128      author string,
   129      message BLOB,
   130      ts INTEGER,
   131      CONSTRAINT pk_pool_id_author PRIMARY KEY(pool,id,author)
   132  );
   133  
   134  -- SET_CHAT_MESSAGE
   135  INSERT INTO chat(pool,id,author,message, ts) VALUES(:pool,:id,:author,:message, :ts)
   136      ON CONFLICT(pool,id,author) DO UPDATE SET message=:message
   137  	    WHERE pool=:pool AND id=:id AND author=:author
   138  
   139  -- GET_CHAT_MESSAGES
   140  SELECT message FROM chat WHERE pool=:pool AND id > :afterId AND id < :beforeId ORDER BY id DESC LIMIT :limit
   141  
   142  -- GET_CHAT_OFFSET 
   143  SELECT max(ts) FROM chat WHERE pool=:pool