:PROPERTIES:
:ID:       6ffe3a57-b7b8-4334-8d49-f4586d2943ae
:END:
#+title: Sqlite3
#+filetags: personal sql
Examiner les tables et lister l'une d'entre elleS
#+begin_src sql
sqlite3 test.db
sqlite> .tables
sqlite> .schema files
sqlite> select * from files;
#+end_src

* TODO SQL
:PROPERTIES:
:ID:       87a191ea-a2aa-41f7-873d-a54676523a77
:END:
On utilise persistent. Pour se connecter à une base existante, il faut connaître le type de colonnes (avec `.schema`, voir [[id:6ffe3a57-b7b8-4334-8d49-f4586d2943ae][Sqlite3]]).
Puis définir un type.
Exemple: pour org-roam, on regarde la table files
#+begin_src sql
sqlite> .schema files
CREATE TABLE files (file UNIQUE PRIMARY KEY, title , hash NOT NULL, atime NOT NULL, mtime NOT NULL);
#+end_src
#+begin_src haskell
{-# LANGUAGE TemplateHaskell            #-}
{-# LANGUAGE QuasiQuotes                #-}
{-# LANGUAGE TypeFamilies               #-}
{-# LANGUAGE MultiParamTypeClasses      #-}
{-# LANGUAGE GADTs                      #-}
{-# LANGUAGE GeneralizedNewtypeDeriving #-}
{-# LANGUAGE RecordWildCards            #-}
{-# LANGUAGE FlexibleInstances          #-}
{-# LANGUAGE OverloadedStrings          #-}
{-# LANGUAGE DerivingStrategies         #-}
{-# LANGUAGE StandaloneDeriving         #-}
{-# LANGUAGE UndecidableInstances       #-}
{-# LANGUAGE DataKinds #-}

import qualified Database.Persist.TH as PTH
import Database.Persist (Entity(..))
import Database.Persist.Sql (toSqlKey)
import Data.Text
import Database.Persist.Sqlite
import Control.Monad.IO.Class
import Control.Monad.Logger
PTH.share [PTH.mkPersist PTH.sqlSettings, PTH.mkMigrate "migrateAll"] [PTH.persistLowerCase|
  File sql=files
    file Text
    Primary file
    title Text
    hask Text
    atime Text
    mtime Text
    deriving Show
|]
path =  "/home/alex/.emacs.d/.local/cache/org-roam.db"
main :: IO ()
main = runSqlite path $ do
    test <- selectList [] [LimitTo 1]
    liftIO $ print (test :: [Entity File])
#+end_src
Note: il faut définir une autre clé primaire, voir https://hackage.haskell.org/package/persistent-2.14.5.0/docs/Database-Persist-Quasi.html
#+begin_src haskell
    file Text
    Primary file
#+end_src
Si la clé primaire est une chaîne de caractères
#+begin_src haskell
    Id Text sql=id
#+end_src