-fix DetachedInstanceError

- init project then create folder

.gitignore

@@ -7,3 +7,4 @@
 /.vscode
 /media
 python/biopro/sever/_identify.py
+*.log

README.md

@@ -158,8 +158,8 @@ reboot
 
 ###### Set pi password
 ```
-$cd ~
-$passwd
+cd ~
+passwd
 Current password:raspberry
 New password:5k6zj454778225
 Retype new password:5k6zj454778225
@@ -198,11 +198,11 @@ exit 0
 ### MQTT broker install
 
 ###### 1.Install mosquitto
-$sudo apt-get install mosquitto mosquitto-clients
+sudo apt-get install mosquitto mosquitto-clients
 
 
 ###### 2.Enable Remote Access
-$sudo vim /etc/mosquitto/conf.d/default.conf
+sudo vim /etc/mosquitto/conf.d/default.conf
 
 add the code following as:
 ```
@@ -213,13 +213,13 @@ protocol websockets
 allow_anonymous true
 ```
 ###### 3.restart mqtt broker
-$sudo systemctl restart mosquitto
+sudo systemctl restart mosquitto
 
 ###### 4.Robust MQTT
 create mosquitto_restart.sh
 
-$cd ~
-$sudo vim mosquitto_restart.sh
+cd ~
+sudo vim mosquitto_restart.sh
 
 
 add the code following as:
@@ -236,16 +236,16 @@ echo "$SERVICE is currently running" >> /home/ubuntu/cron.log
 exit 0
 ```
 ###### 5.add mosquitto_restart.sh to root routing
-$sudo chmod +x mosquitto_restart.sh
-$sudo  -i
-$crontab -e
+sudo chmod +x mosquitto_restart.sh
+sudo  -i
+crontab -e
 
 add the code following as:
 ```
 */5 * * * * /home/pi/mosquitto_restart.sh
 ```
 ###### 6.Auto run MQTT broker when when pi-start
-$sudo vim /etc/rc.local
+sudo vim /etc/rc.local
 
 add the code following as:
 ```
@@ -254,31 +254,31 @@ sudo nohup systemctl restart mosquitto
 
 ### Setting up a Routed Wireless Access Point
 ###### 1.Install AP and Management Software
-$sudo apt install hostapd
-$sudo systemctl unmask hostapd
-$sudo systemctl enable hostapd
-$sudo apt install dnsmasq
-$sudo DEBIAN_FRONTEND=noninteractive apt install -y netfilter-persistent iptables-persistent
+sudo apt install hostapd
+sudo systemctl unmask hostapd
+sudo systemctl enable hostapd
+sudo apt install dnsmasq
+sudo DEBIAN_FRONTEND=noninteractive apt install -y netfilter-persistent iptables-persistent
 
 ###### 2.Set up the Network Router
-$sudo nano /etc/dhcpcd.conf
+sudo nano /etc/dhcpcd.conf
 ```
 interface wlan0
     static ip_address=192.168.2.1/24
     nohook wpa_supplicant
 ```
-$sudo nano /etc/sysctl.d/routed-ap.conf
+sudo nano /etc/sysctl.d/routed-ap.conf
 ```
 # Enable IPv4 routing
 net.ipv4.ip_forward=1
 ```
 
-$sudo iptables -t nat -A POSTROUTING -o eth0 -j MASQUERADE
-$sudo netfilter-persistent save
-$sudo mv /etc/dnsmasq.conf /etc/dnsmasq.conf.orig
+sudo iptables -t nat -A POSTROUTING -o eth0 -j MASQUERADE
+sudo netfilter-persistent save
+sudo mv /etc/dnsmasq.conf /etc/dnsmasq.conf.orig
 
 
-$sudo nano /etc/dnsmasq.conf
+sudo nano /etc/dnsmasq.conf
 
 ```
 interface=wlan0 # Listening interface
@@ -289,10 +289,10 @@ address=/gw.wlan/192.168.2.1
                 # Alias for this router
 ```
 ###### 3.Ensure Wireless Operation
-$sudo rfkill unblock wlan
+sudo rfkill unblock wlan
 
 ###### 4.Configure the AP Software
-$sudo nano /etc/hostapd/hostapd.conf
+sudo nano /etc/hostapd/hostapd.conf
 
 ```
 country_code=TW
@@ -317,11 +317,13 @@ wme_enabled=1
 ht_capab=[HT40+][SHORT-GI-40]
 ```
 ###### 5.Running the new Wireless AP
-$sudo systemctl reboot
+sudo systemctl reboot
 
+### Install Apache2
+sudo apt install apache2
 
-### Set web site
-$sudo vim ./sites-available/000-default.conf
+### Deploy web site
+sudo vim /etc/apache2/sites-available/000-default.conf
 ```
 <VirtualHost *:8080> #80------>8080
         # The ServerName directive sets the request scheme, hostname and port that
@@ -363,6 +365,11 @@ $sudo vim ./sites-available/000-default.conf
 > https://www.raspberrypi.org/documentation/configuration/wireless/access-point-routed.md
 
 # ChangeLog
+[2022.12.28]
+change meta_project_info to project_metas
+change project_report to project_reports
+alter table project_meta column cycle type string to jsonb
+
 [2022.11.16]
 add python package SQLAlchemy
 add new column project(int4) in postgresSQL table recording_meta_datas

python/biopro/db/collection.py

@@ -0,0 +1,75 @@
+from sqlalchemy import Table, Column, String, MetaData, ForeignKey, JSON
+from sqlalchemy.sql import select, func
+from sqlalchemy.types import Integer, BigInteger, String, Boolean, TIMESTAMP, Numeric
+from sqlalchemy.dialects.postgresql import JSONB
+from biopro.db.base import Session
+
+from .base import Base
+
+class Collection(Base):
+    __tablename__ = "collections"
+
+    id = Column(Integer, primary_key=True)
+    name = Column(String(255))
+    parent = Column(JSONB)
+    controller_id = Column(Integer)
+    type = Column(String(255))
+    description = Column(String(255))
+    deleted = Column(Boolean)
+    created_at = Column(TIMESTAMP(timezone=True), server_default=func.now())
+    updated_at = Column(TIMESTAMP(timezone=True), onupdate=func.now())
+
+    @classmethod
+    def create_collection(cls, collection_name, parent, duplicate=False):
+        with Session() as session:
+            name = cls.check_name_duplicate(collection_name, parent, 0, duplicate=duplicate)
+            if name == None:
+                return session.query(Collection).filter(Collection.name == collection_name, Collection.parent == parent).first()
+            else:
+                collection = Collection(
+                    name = name,
+                    parent = parent,
+                    type= "folder",
+                )
+                session.add(collection)
+                session.commit()
+                session.refresh(collection)
+                return collection
+
+    @classmethod
+    def check_name_duplicate(cls, collection_name, parent, n, _session = None, duplicate=False):
+        if _session == None:
+            with Session() as session:
+                result = session.query(Collection).filter(Collection.name == cls.generate_name(collection_name, n), Collection.parent == parent).first()
+                if result is None:
+                    return cls.generate_name(collection_name, n)
+                else:
+                    if duplicate == True:
+                        return None
+                    else:
+                        new_num = n + 1
+                        # new_name =  f"{collection_name}({new_num})" 
+                        return cls.check_name_duplicate(collection_name, parent, new_num, session)
+        else:
+            result = _session.query(Collection).filter(Collection.name == cls.generate_name(collection_name, n), Collection.parent == parent).first()
+            if result is None:
+                return cls.generate_name(collection_name, n)
+            else:
+                new_num = n + 1
+                # new_name =  f"{collection_name}({new_num})" 
+                return cls.check_name_duplicate(collection_name, parent, new_num, _session)
+
+    @classmethod
+    def generate_name(cls, collection_name, n):
+        if n==0:
+            return collection_name
+        else:
+            return f"{collection_name}({n})" 
+    
+    @classmethod
+    def find_collection(cls, collection_name, parent):
+        with Session() as session:
+            result = session.query(Collection).filter(Collection.name == collection_name, Collection.parent == parent).first()
+            return result
+    
+        

python/biopro/db/meta.py

@@ -0,0 +1,24 @@
+from sqlalchemy import Table, Column, String, MetaData, ForeignKey, JSON
+from sqlalchemy.sql import select, func
+from sqlalchemy.types import Integer, BigInteger, String, Boolean, TIMESTAMP, Numeric
+from sqlalchemy.dialects.postgresql import JSONB
+
+from .base import Base, Session
+
+class RecordingDataMeta(Base):
+    __tablename__ = "recording_data_metas"
+
+    id = Column(Integer, primary_key=True)
+    path = Column(String(255))
+    name = Column(String(255))
+    parent = Column(JSONB)
+    size = Column(String(255))
+    time_duration = (String(255))
+    raw_data = Column(JSONB)
+    project = Column(Integer)
+    deleted = Column(Boolean, default = False)
+    created_at = Column(TIMESTAMP(timezone=True), server_default=func.now())
+    updated_at = Column(TIMESTAMP(timezone=True), onupdate=func.now())
+
+    # def __repr__(self):
+    #     return f"User(id={self.id!r}, name={self.name!r}, fullname={self.task!r})"

python/biopro/db/project.py

@@ -5,14 +5,17 @@ from sqlalchemy.dialects.postgresql import JSONB
 
 from .base import Base
 
-class MetaProjectInfo(Base):
-    __tablename__ = "meta_project_info"
+class Project(Base):
+    __tablename__ = "project"
 
     id = Column(Integer, primary_key=True)
-    project = Column(String(36))
-    cycle = Column(String)
+    name = Column(String)
+    desc = Column(String)
     task = Column(JSONB)
-    serial_number = Column(Integer)
+    cycle = Column(JSONB)
+    device = Column(JSONB)
+    uuid = Column(String(36))
+    user_auth = Column(JSONB)
     deleted = Column(Boolean, default = False)
     created_at = Column(TIMESTAMP(timezone=True), server_default=func.now())
     updated_at = Column(TIMESTAMP(timezone=True), onupdate=func.now())

python/biopro/db/project_meta.py

@@ -0,0 +1,29 @@
+from sqlalchemy import Table, Column, String, MetaData, ForeignKey, JSON
+from sqlalchemy.sql import select, func
+from sqlalchemy.types import Integer, BigInteger, String, Boolean, TIMESTAMP, Numeric
+from sqlalchemy.dialects.postgresql import JSONB
+
+from .base import Base, Session
+
+class MetaProjectInfo(Base):
+    __tablename__ = "project_metas"
+
+    id = Column(Integer, primary_key=True)
+    project = Column(String(36))
+    cycle = Column(JSONB)
+    task = Column(JSONB)
+    serial_number = Column(Integer)
+    deleted = Column(Boolean, default = False)
+    created_at = Column(TIMESTAMP(timezone=True), server_default=func.now())
+    updated_at = Column(TIMESTAMP(timezone=True), onupdate=func.now())
+
+    @classmethod
+    def create_project_meta(cls, project):
+        with Session() as session:
+            project_meta = MetaProjectInfo(project = project['project'], cycle= project['cycle'], task=project['task'], serial_number=int(project['serial_number']))
+            session.add(project_meta)
+            session.commit()
+            return project_meta.id
+
+    # def __repr__(self):
+    #     return f"User(id={self.id!r}, name={self.name!r}, fullname={self.task!r})"

python/biopro/db/project_report.py

@@ -6,7 +6,7 @@ from sqlalchemy.dialects.postgresql import JSONB
 from .base import Base
 
 class ProjectReport(Base):
-    __tablename__ = "project_report"
+    __tablename__ = "project_reports"
 
     id = Column(Integer, primary_key=True)
     name = Column(String)

python/biopro/db/recording_data.py

@@ -0,0 +1,88 @@
+from sqlalchemy import Table, Column, String, MetaData, ForeignKey, JSON
+from sqlalchemy.sql import select, func
+from sqlalchemy.types import Integer, BigInteger, String, Boolean, TIMESTAMP, Numeric, Text
+from sqlalchemy.dialects.postgresql import JSONB
+from biopro.db.base import Session
+
+from .base import Base
+
+# build a model class with a specific table name
+def get_raw_model(channel):
+    tablename = str(channel) + '_recording_data_raws'  # dynamic table name
+    class_name = 'RECORDING_DATA_RAWS' # dynamic class name
+    print('get_raw_model', tablename)
+
+    for mapper in Base.registry.mappers:
+        cls = mapper.class_
+        classname = cls.__name__
+        tblname = cls.__tablename__
+        print(cls, classname, tblname)
+        if (classname == class_name):
+            if tblname == tablename:
+                return cls
+
+    Model = type(class_name, (RECORDING_DATA_RAWS,), {
+        '__tablename__': tablename
+    })
+    return Model
+
+
+class RECORDING_DATA_RAWS(Base):
+    __abstract__ = True
+
+    id = Column(Integer, primary_key=True)
+    name = Column(String(255))
+    parent = Column(Integer)
+    size = Column(String(255))
+    path = Column(JSONB)
+    uuid = Column(String(255))
+    serial_number = Column(String(255))
+    data_format = Column(String(255))
+    channel = Column(Integer)
+    start_time = Column(String(255))
+    end_time = Column(String(255))
+    data = Column(Text)
+    compressed = Column(Boolean)
+    deleted = Column(Boolean)
+    created_at = Column(TIMESTAMP(timezone=True), server_default=func.now())
+    updated_at = Column(TIMESTAMP(timezone=True), onupdate=func.now())
+
+    # @classmethod
+    # def create_subject_data(cls, subject_id, project, meta, data):
+    #     with Session() as session:
+    #         subject = Subject(
+    #             subject_id = subject_id,
+    #             project = project,
+    #             meta= meta,
+    #             data = data
+    #         )
+    #         session.add(subject)
+    #         session.commit()
+    #         return subject
+        
+    # @classmethod
+    # def check_name_duplicate(cls, collection_name, parent, n):
+    #     with Session() as session:
+    #         result = session.query(Collection).filter(Collection.name == cls.generate_name(collection_name, n), Collection.parent == parent).first()
+    #         if result is None:
+    #             return cls.generate_name(collection_name, n)
+    #         else:
+    #             new_num = n + 1
+    #             # new_name =  f"{collection_name}({new_num})" 
+    #             return cls.check_name_duplicate(collection_name, parent, new_num)
+
+    # @classmethod
+    # def generate_name(cls, collection_name, n):
+    #     if n==0:
+    #         return collection_name
+    #     else:
+    #         return f"{collection_name}({n})" 
+    
+    # @classmethod
+    # def find_data(cls, id):
+    #     with Session() as session:
+
+    #         result = session.query(RECORDING_DATA_RAWS).first()
+    #         return result
+    
+        

python/biopro/db/subject_data.py

@@ -0,0 +1,60 @@
+from sqlalchemy import Table, Column, String, MetaData, ForeignKey, JSON
+from sqlalchemy.sql import select, func
+from sqlalchemy.types import Integer, BigInteger, String, Boolean, TIMESTAMP, Numeric
+from sqlalchemy.dialects.postgresql import JSONB
+from biopro.db.base import Session
+
+from .base import Base
+
+class SubjectData(Base):
+    __tablename__ = "subject_datas"
+
+    id = Column(Integer, primary_key=True)
+    subject_id = Column(Integer)
+    mode = Column(JSONB)
+    data = Column(JSONB)
+    user_auth = Column(JSONB)
+    meta = Column(String(255))
+    project = Column(String(255))
+    deleted = Column(Boolean)
+    created_at = Column(TIMESTAMP(timezone=True), server_default=func.now())
+    updated_at = Column(TIMESTAMP(timezone=True), onupdate=func.now())
+
+    @classmethod
+    def create_subject_data(cls, subject_id, project, meta, data):
+        with Session() as session:
+            subject = SubjectData(
+                subject_id = subject_id,
+                project = project,
+                meta= meta,
+                data = data
+            )
+            session.add(subject)
+            session.commit()
+            return subject
+        
+    # @classmethod
+    # def check_name_duplicate(cls, collection_name, parent, n):
+    #     with Session() as session:
+    #         result = session.query(Collection).filter(Collection.name == cls.generate_name(collection_name, n), Collection.parent == parent).first()
+    #         if result is None:
+    #             return cls.generate_name(collection_name, n)
+    #         else:
+    #             new_num = n + 1
+    #             # new_name =  f"{collection_name}({new_num})" 
+    #             return cls.check_name_duplicate(collection_name, parent, new_num)
+
+    # @classmethod
+    # def generate_name(cls, collection_name, n):
+    #     if n==0:
+    #         return collection_name
+    #     else:
+    #         return f"{collection_name}({n})" 
+    
+    # @classmethod
+    # def find_collection(cls, collection_name, parent):
+    #     with Session() as session:
+    #         result = session.query(Collection).filter(Collection.name == collection_name, Collection.parent == parent).first()
+    #         return result
+    
+        

python/biopro/device/manager.py

@@ -832,6 +832,9 @@ class DeviceManager(MasterDevice, Synchronized):
 
         elif func == InternalInstruction.PREDEFINED_DISABLE_CACHE:
             self._device_disable_cache(device, *para)
+        
+        elif func == InternalInstruction.PREDEFINED_IDLE:
+            self._idle(device, *para)
 
         elif isinstance(device, DebugDevice):
             if func == InternalInstruction.PREDEFINED_NOTIFY:
@@ -851,6 +854,11 @@ class DeviceManager(MasterDevice, Synchronized):
                                               InternalInstruction.PREDEFINED_DATA_FORMAT,
                                               f)
 
+    def _idle(self, device: Device, expr: AnyStr):
+        self._handler.device_internal_command(device.device_id,
+                                                InternalInstruction.PREDEFINED_IDLE,
+                                                None)
+
     def _device_data_format_cali(self, device: Device, expr: str, cali: bytes = None):
         if cali is None:
             cali = device.calibration_info(expr)

python/biopro/devlib/data.py

@@ -1360,7 +1360,6 @@ class EISZeroOneDataDecoder(RecDataDecoder):
         self._mode_stop = 0
 
         self._cycle_start_time = []
-        self._ac_amp: int = 0
         self._mode: int = 0
         self._last_phase = 0
         self._first_phase_flag = 1
@@ -1488,14 +1487,14 @@ class EISZeroOneDataDecoder(RecDataDecoder):
             phase_coeff[3][g] = struct.unpack('>i', cali_coeff[index+9:index+13])[0]
             phase_offset[3][g] = struct.unpack('>i', cali_coeff[index+13:index+17])[0]
 
-            # print('cutoff_freq', cutoff_freq)
-            # print('hsrtia_a', hsrtia_a)
-            # print('hsrtia_b', hsrtia_b)
-            # print('hsrtia_c', hsrtia_c)
-            # print('phase_coeff')
-            # print(phase_coeff)
-            # print('phase_offset')
-            # print(phase_offset)
+            print('cutoff_freq', cutoff_freq)
+            print('hsrtia_a', hsrtia_a)
+            print('hsrtia_b', hsrtia_b)
+            print('hsrtia_c', hsrtia_c)
+            print('phase_coeff')
+            print(phase_coeff)
+            print('phase_offset')
+            print(phase_offset)
 
             cali_table.append((cutoff_freq, phase_coeff, phase_offset, hsrtia_a, hsrtia_b, hsrtia_c, hsrtia_d))
 
@@ -1528,8 +1527,13 @@ class EISZeroOneDataDecoder(RecDataDecoder):
         cycle_number = struct.unpack('>H', data[17+3:19+3])[0]
         d19 = data[19+3]
         gain = data[20+3]
-        finishMode = (d19 & 0x80) >> 7        
+        finishMode = (d19 & 0x80) >> 7  
+        ch4 = struct.unpack('<i', data[21+3:25+3])[0] # Amp[uV]
         
+        notify_one = struct.unpack('<i', data[25+3:29+3])[0]
+        notify_two = struct.unpack('<i', data[29+3:33+3])[0]
+        notify_three = struct.unpack('<i', data[33+3:37+3])[0]
+
         if time_stamp == 0:
             self._start_return_data = True
 
@@ -1553,13 +1557,12 @@ class EISZeroOneDataDecoder(RecDataDecoder):
                     hsrtia_d = []
                     cutoff_freq, phase_coeff, phase_offset, hsrtia_a, hsrtia_b, hsrtia_c, hsrtia_d = self.cali_coeff[0]
 
-                    voltage_amp = round(self._ac_amp * 800 / 2047) # use UI value
-
                 if (self._mode == 0 or self._mode == 5):
                     img = ch1
                     real = ch2
                     freq = ch3
                     fre_idx = 0
+                    voltage_amp = round(ch4 / 1000) # Amp[mV]
 
                     # rolloff_cali = cutoff_freq/1e5
                     rolloff_cali = hsrtia_c[gain]
@@ -1573,15 +1576,9 @@ class EISZeroOneDataDecoder(RecDataDecoder):
                     # current = voltage_mag ** 2 * hsrtia_a[gain] + voltage_mag * hsrtia_b[gain] + hsrtia_c[gain]
                     
 
-                    # print(current)
-                    # print(voltage_mag)
-                    # print(hsrtia_a[gain])
-                    # print(hsrtia_b[gain])
-                    # print(hsrtia_c[gain])
-
                     if (current != 0):
-                        # impedance = voltage_amp * 1000_000 / 1.414213 / current 
-                        impedance = voltage_amp * 707106.78 / current 
+                        # impedance[mOhm] = voltage_amp[mv] * 1000000 / 1.414213 / current[nA]       #RMS=amp*SQRT(2), SQRT(2)=1.414213
+                        impedance = voltage_amp * 707106.78 / current
                     else:
                         impedance = 0
 
@@ -1612,25 +1609,33 @@ class EISZeroOneDataDecoder(RecDataDecoder):
                     #     phase = self._last_phase + diff
                     # self._last_phase = phase
 
-                    imag_after_cal = impedance * math.sin(round(phase) * math.pi / 180)
-                    real_after_cal = impedance * math.cos(round(phase) * math.pi / 180)
+                    imag_after_cal = impedance * math.sin(phase * math.pi / 180)
+                    real_after_cal = impedance * math.cos(phase * math.pi / 180)
 
                 if self._show_data:
                     if (self._mode == 0 or self._mode == 5):
                         print('|', '{:10}'.format(time_stamp),
-                            '|', '{:5}'.format(delta),
-                            '|', '{:6}'.format(ch1),
-                            '|', '{:6}'.format(ch2),
-                            '|', '{:8}'.format(ch3 / 100),
-                            '|', '{:6}'.format(round(voltage_mag)),
-                            '|', '{:5}'.format(int(imag_after_cal)),
-                            '|', '{:5}'.format(int(real_after_cal)),
-                            '|', '{:5}'.format(round(impedance)),
-                            '|', '{:5}'.format(round(phase, 1)),
-                            '|', '{:5}'.format(round(current, 3)),
-                            '|', '{:1}'.format(gain),
-                            '|', '{:1}'.format(finishMode),
-                            '@', str(self.device), '|', flush = True)
+                              '|', '{:5}'.format(delta),
+                              '|', '{:5}'.format(ch1),                            #raw_img
+                              '|', '{:5}'.format(ch2),                            #raw_real
+                              '|', '{:8}'.format(ch3 * 10), '[mHz]',              #Frequency [mHz]
+                              '|', '{:5}'.format(cycle_number),                   #cycle
+                              '|', '{:5}'.format(round(imag_after_cal)), '[Ohm]', #Z_imag [Ohm]
+                              '|', '{:5}'.format(round(real_after_cal)), '[Ohm]', #Z_real [Ohm]
+                              '|', '{:5}'.format(round(impedance)), '[Ohm]',      #Impedance [Ohm]
+                              '|', '{:5}'.format(round(phase*1000)), '[mdegree]', #Phase [millidegree]
+                              '|', '{:5}'.format(round(current)), '[nA]',         #Current [nA]
+                              '|', '{:1}'.format(gain),                           #gain
+                              '|', '{:1}'.format(finishMode),                     #finishMode
+                              '@', str(self.device), '|', flush = True)
+
+                        print('|', '{:10}'.format(time_stamp),
+                              '|', '{:5}'.format(delta),
+                              '|', '{:5}'.format(notify_one),
+                              '|', '{:5}'.format(notify_two),
+                              '|', '{:5}'.format(notify_three),
+                              '|', '{:5}'.format(voltage_amp),                    #amp[mV]
+                              '|', flush = True)
                         pass
                     else:
                         print('|', '{:10}'.format(time_stamp),
@@ -1652,18 +1657,32 @@ class EISZeroOneDataDecoder(RecDataDecoder):
 
         ret = RecordingData(self.device, int(time_stamp * 1000 / 2), 0)
         if self._mode == 0 or self._mode == 5:                           #EIS Mode
-            ret.append_data(0, ch1)                     #Raw Imag  
-            ret.append_data(1, ch2)                     #Raw Real
+            ret.append_data(0, ch1)                     #raw_img
+            ret.append_data(1, ch2)                     #raw_real
             ret.append_data(2, ch3 * 10)                #Frequency [mHz]
-            ret.append_data(3, cycle_number)
+            ret.append_data(3, cycle_number)            #cycle
             ret.append_data(4, round(imag_after_cal))   #Z_imag [Ohm]
             ret.append_data(5, round(real_after_cal))   #Z_real [Ohm]
             ret.append_data(6, round(impedance))        #Impedance [Ohm]
-            ret.append_data(7, round(phase))            #Phase [degree]
+            ret.append_data(7, round(phase*1000))       #Phase [millidegree]
             ret.append_data(8, round(current))          #Current [nA]
-            ret.append_data(9, gain)                    #Gain Level
+            ret.append_data(9, gain)                    #gain
+            
+            #debug data
+            ret.append_data(10, notify_one)
+            ret.append_data(11, notify_two)
+            ret.append_data(12, notify_three)
+            ret.append_data(13, voltage_amp)            #amp[mV]
 
-        else:                                           #CV Mode
+
+
+        else:
+            if (self._mode == 1 or self._mode == 2 or self._mode == 3):
+                ch1 = ch1 * (-1)
+            if (self._mode == 4):
+                ch1 = ch1 * (-1)
+                ch3 = ch3 * (-1)
+                                                           #CV Mode
             ret.append_data(0, ch1)                     #Iin     [nA]
             ret.append_data(1, ch2)                     #Vset    [nV]
             ret.append_data(2, ch3)                     #Vout    [nV]

python/biopro/devlib/instruction.py

@@ -283,6 +283,7 @@ class InternalInstruction(SingleInstruction):
     PREDEFINED_SET = '_set'
     PREDEFINED_CDR = '_cdr'
     PREDEFINED_DISABLE_CACHE = '_disable_cache'
+    PREDEFINED_IDLE = '_idle'
 
     PREDEFINED = (
         PREDEFINED_SLEEP,
@@ -294,6 +295,7 @@ class InternalInstruction(SingleInstruction):
         PREDEFINED_SET,
         PREDEFINED_CDR,
         PREDEFINED_DISABLE_CACHE,
+        PREDEFINED_IDLE,
     )
 
     __slots__ = ('_expr', '_para')

python/biopro/devlib/library.py

@@ -1292,6 +1292,9 @@ class CompletedDevice(Device):
 
     def central_version_get(self) -> Optional[list]:
         return self._device.central_version_get()
+    
+    def save_data_to_subject(self, project, meta, device, subject_id):
+        self._master._handler.save_data_to_subject(project, meta, device, subject_id)
 
     # utility method
 

python/biopro/project/project.py

@@ -1,6 +1,7 @@
 import sys
 import json
 import threading
+import logging
 
 from time import time, sleep
 from datetime import datetime
@@ -14,6 +15,9 @@ from .instruction import Instruction
 from biopro.device.manager import DeviceManager
 from biopro.text import *
 
+from biopro.db.base import Session
+from biopro.db.collection import Collection
+
 key_list = {
   'deviceList': 'device',
 }
@@ -45,11 +49,35 @@ class Project(threading.Thread):
 
     self._count = 1 #流水號
 
+    self._project_meta_id = -1
+    self._subject = []
+
     self.log_verbose = log_verbose
+    self._logger = logging.getLogger('project')
+    self._logger.setLevel('DEBUG')
+
+    self._formatter = logging.Formatter('[%(asctime)s.%(msecs)03d] %(message)s', datefmt='%Y-%m-%d %H:%M:%S')
+    self._formatter_with_nothing = logging.Formatter('%(message)s')
 
     self.setup_project(project)
     self.setup_device(self._device)
-  
+
+    # create log file handler
+    fh = logging.FileHandler(f'/home/pi/logger/project/{self.uuid}.log', mode="w")
+    fh.setFormatter(self._formatter)
+    self._logger.addHandler(fh)
+
+    default_name = 'admin'
+    default_parent = {"folder": [1]}
+    collection = Collection.find_collection(default_name, default_parent)
+    parent = {"folder": [collection.id]}
+    # create project folder
+    folder_name_duplicate = False
+    if len(self._subject) > 0:
+      folder_name_duplicate = True
+    collection = Collection.create_collection(self.name, parent, folder_name_duplicate)
+    self.setup_collection(collection)
+    
   def setup_project(self, project):
     for (key, value) in project.items():
       if key in key_list.keys():
@@ -68,6 +96,9 @@ class Project(threading.Thread):
       complete_device = self._device_manager.get_device(mac_address)
       complete_device.occupied_by_project = self._uuid
       self._complete_device[device] = complete_device
+    
+  def setup_collection(self, collection):
+    self._task_manager.create_collection(collection)
 
   @property
   def id(self) -> int:
@@ -76,6 +107,22 @@ class Project(threading.Thread):
   @id.setter
   def id(self, new_id):
     self._id = new_id
+
+  @property
+  def project_meta_id(self) -> int:
+    return self._project_meta_id
+  
+  @project_meta_id.setter
+  def project_meta_id(self, new_project_meta_id):
+    self._project_meta_id = new_project_meta_id
+
+  @property
+  def subject(self) -> object:
+    return self._subject
+  
+  @subject.setter
+  def subject(self, new_subject):
+    self._subject = new_subject
   
   @property
   def uuid(self) -> str:
@@ -141,9 +188,19 @@ class Project(threading.Thread):
     parameter_set = self._task_manager.running_task.parameter_set
     return next((x for x in parameter_set if parameter_set[x]['target'] == device_address), None)
 
+  def get_cycle(self, task):
+      for index, _cycle in enumerate(self.cycle):
+        if task.uuid in _cycle['range']:
+          return _cycle
+
   def run(self):
+      # project status change running (1)
       self._status = 1
+      # save start time
       self._start_time = time()
+
+      # saving & broadcast message
+      self._logger.info('Project ' + self.name + ' start')
       self.log_verbose('Project ' + self.name + ' start')
       self.mqtt_thread.broadcast_command('project:' + self._name + ' start at ' + datetime.now().strftime('%Y-%m-%d %H:%M:%S.%f')[:-3])
       
@@ -171,8 +228,12 @@ class Project(threading.Thread):
                 if action.type == 'cycle' and condition.type == 'previous_task_done':
                   self.mqtt_thread.broadcast_command('project:task ' + task.name + ' start at ' + datetime.now().strftime('%Y-%m-%d %H:%M:%S.%f')[:-3])
                   self._task_manager.set_running_task(task)
+                  cycle_info = self.get_cycle(task)
+                  if cycle_info['useID'] == False:
+                    self._logger.info(f'---------- Cycle {task.name} Round {cycle_info["count"]} -----------')
                 if action.type == 'cycle' and condition.type == 'until_button_trigger':
                   self._task_manager.running_task.stop()
+                  self._logger.info(f'---------- Cycle {cycle_info["id"]} Round {cycle_info["count"]} -----------')
 
                 if self._task_manager.running_task != None:
                   if self._task_manager.running_task.uuid == task.uuid and action.type == 'cycle' and condition.type == 'after_task_run':
@@ -181,6 +242,7 @@ class Project(threading.Thread):
                 if action.type == 'start':
                   if task.status != 1:
                     self._task_manager.set_running_task(task)
+                    self._logger.info(f'Task {str(self._task_manager.running_task.name)} start')
                     self.mqtt_thread.broadcast_command('project:task ' + task.name + ' start at ' + datetime.now().strftime('%Y-%m-%d %H:%M:%S.%f')[:-3])
                 # elif action.type == 'stop':
                   # self.mqtt_thread.broadcast_command('project:task ' + str(self._task_manager.running_task.name) + ' stop at ' + datetime.now().strftime('%Y-%m-%d %H:%M:%S.%f')[:-3])
@@ -198,6 +260,7 @@ class Project(threading.Thread):
                 if action.target is not None:
                   device = self._complete_device[action.target]
                   if ((action.type == 'start' and device.status == 0) or (action.type == 'stop' and device.status == 1)):
+                    self._logger.info(f'Device {device.mac_address_in_str} {action.type}')
                     self.log_verbose('Project' + self.name + 'Task ' + task.name + ' match_condition ' + condition.type + ' trigger_action ' + action.type + ' ' + str(action.target))
                     task_info = task.get_task_info(action)
                     instruction_set = getattr(self._instruction_set, action.type, None)
@@ -232,26 +295,31 @@ class Project(threading.Thread):
                     self.log_verbose('file name ' + task_info['file_name'])
                     
                   if instruction_set != None and self.get_device_parameter_set(action.target) != None:
-                    if action.type == 'start':
-                      self._count += 1
                     for instruction in instruction_set:
                       print('instruction 1', device, instruction, datetime.now())
                       args = list(map(lambda arg: task_info[arg], instruction['arguments']))
                       target=getattr(device, instruction['method'])(*args)
                       print('instruction 2', device, instruction, datetime.now())
+                    if (action.type == 'stop'):
+                      if (len(self._subject) > 0):
+                         target=getattr(device, "save_data_to_subject")(self.id, self.project_meta_id, device, self._subject[0])
+                    if action.type == 'start':
+                      self._count += 1
             
             delay_time += (time() - now)
 
         # check task not running then stop
         if self.check_running_task_not_run() == True:
+          # self._logger.info(f'taskType {self._task_manager.running_task.name} {self._task_manager.running_task.type}')
           if self._task_manager.running_task.type == '':
+            self._logger.info(f'Task {self._task_manager.running_task.name} stop')
             self.mqtt_thread.broadcast_command('project:task ' + str(self._task_manager.running_task.name) + ' stop at ' + datetime.now().strftime('%Y-%m-%d %H:%M:%S.%f')[:-3])
           self._task_manager.running_task.stop()
         
         # check project done then close project
         if self.check_project_done() == True:
           print('project stop at', datetime.now())
-          self.close()
+          self.stop()
         
         if self._time_interval - delay_time > 0:
           sleep(self._time_interval - delay_time)
@@ -268,6 +336,7 @@ class Project(threading.Thread):
     self._status = 2
     self._end_time = time()
     self._stop_flag  = True
+    self._logger.info('Project ' + self.name + ' stop')
     self.mqtt_thread.broadcast_command('project:project ' + str(self._name) + ' stop at ' + datetime.now().strftime('%Y-%m-%d %H:%M:%S.%f')[:-3])
 
   def close(self):

python/biopro/project/task_manager.py

@@ -5,6 +5,7 @@ from xml.dom.expatbuilder import parseString
 import paho.mqtt.client as mqtt
 from biopro.text import *
 from .task import Task
+from biopro.db.collection import Collection
 
 _RUNTIME_COMPILE = False
 
@@ -56,12 +57,24 @@ class TaskManager():
     
     def get_index_by_uuid(self, uuid):
       return next((i for i, x in enumerate(self.task_list) if x.uuid == uuid), None)
+    
+    def check_task_is_cycle_start(self, task):
+      for index, cycle in enumerate(self._cycle_list):
+        if cycle['range'][0] == task.uuid:
+          return index
 
     def check_task_is_cycle_end(self, task):
       for index, cycle in enumerate(self._cycle_list):
         if cycle['range'][1] == task.uuid:
           return index
 
+    def check_task_in_cycle(self, task_index):
+      for index, cycle in enumerate(self._cycle_list):
+        if self.get_index_by_uuid(cycle['range'][0]) <= task_index:
+          if self.get_index_by_uuid(cycle['range'][1]) >= task_index:
+            return True
+      return False
+
     @property
     def check_list(self):
       return [self._running_task, *self._next_task]
@@ -121,6 +134,7 @@ class TaskManager():
           if self._prev_task.status == 1:
             self._prev_task.stop()
         # if reach cycle end, count < max, then go to cycle start
+        cycle_start_index = self.check_task_is_cycle_start(task)
         cycle_index = self.check_task_is_cycle_end(task)
         if cycle_index is None:
           # print('next', self._running_task.name, self._running_task.next)
@@ -135,18 +149,33 @@ class TaskManager():
           cycle = self._cycle_list[cycle_index]
           if int(cycle['count']) < int(cycle['max']):
             self._next_task.append(self.get_task_by_uuid(cycle['range'][0]))
-            cycle['count'] += 1
+            # cycle['count'] += 1
           else:
             for task_uuid in self._running_task.next:
               _task = next((task for task in self._task_list if task.uuid == task_uuid), None)
               if _task != None:
                 _task.reset()
                 self._next_task.append(_task)
-            cycle['count'] = 1
+            # cycle['count'] = 1
+
+        if cycle_start_index != None:
+            cycle = self._cycle_list[cycle_start_index]
+            if int(cycle['count']) < int(cycle['max']):
+              cycle['count'] += 1
+            else:
+              cycle['count'] = 1
 
       except RuntimeError as e:
         print(e)
 
     def get_task(self, task_id):
       return self._task_list[task_id]
-  
+  
+    def create_collection(self, parent):
+      for index, task in enumerate(self._task_list):
+        if self.check_task_in_cycle(index) == True:
+          if task.type == '':
+            collection = Collection.create_collection(task.name, {"folder": [parent.id]}, False)
+            task.parent = {"folder": [collection.id]}
+        else:
+          task.parent = {"folder": [parent.id]}

python/biopro/server/data.py

@@ -362,14 +362,14 @@ class DataServer(SocketServer, DataAPI):
         self.log_verbose('device ID', device_id)
 
         # project binding meta file
-        project_id = None
-        _project = None
-        if project_info != None:
-            _project = json.loads(project_info)
-            self.database_process.put_queue(['project_insert', device_id, _project])
-            result = self._queue_ds_dict[int(device_id)].get()
-            if result[0] == 'project_id':
-                project_id =  result[1]
+        # project_id = None
+        # _project = None
+        # if project_info != None:
+        #     _project = json.loads(project_info)
+        #     self.database_process.put_queue(['project_insert', device_id, _project])
+        #     result = self._queue_ds_dict[int(device_id)].get()
+        #     if result[0] == 'project_id':
+        project_id =  project_info
 
         # while len(self._configurations) <= device_id:
         #     self._configurationsappend(None)

python/biopro/server/database.py

@@ -222,7 +222,8 @@ class DataBaseProcess(Process):
 
                 try:
                     sql_cursor.execute(sql_str, sql_set)
-                except:
+                except BaseException as e:
+                    print('meta create error', e)
                     self._psql_conn.commit()
                     sql_cursor.close()
                     self._queue_error.put(device_id)
@@ -249,7 +250,8 @@ class DataBaseProcess(Process):
 
                 try:
                     sql_cursor.execute(sql_str, sql_set)
-                except:
+                except BaseException as e:
+                    print('meta update error', e)
                     self._psql_conn.commit()
                     sql_cursor.close()
                     self._queue_error.put(device_id)
@@ -553,7 +555,7 @@ class DataBaseProcess(Process):
         return True
     
     def project_insert(self, device_id, _data):
-        sql_str = 'INSERT INTO "public"."meta_project_info" ('
+        sql_str = 'INSERT INTO "public"."project_metas" ('
         sql_set = []
         for item in _data.keys():
             sql_str = sql_str + str(item) + ', '

python/biopro/server/main.py

@@ -34,8 +34,12 @@ from biopro.api.device import DeviceAPI
 from biopro.project.project_manager import ProjectManager
 from biopro.db.base import Base, Session, engine
 from biopro.db.project_report import ProjectReport
-from biopro.db.meta_project_info import MetaProjectInfo
-
+from biopro.db.project_meta import MetaProjectInfo
+# from biopro.db.device import Device
+from biopro.db.recording_data import get_raw_model
+from biopro.db.subject_data import SubjectData
+from biopro.db.meta import RecordingDataMeta
+import random
 
 _RUNTIME_COMPILE = False
 
@@ -328,6 +332,18 @@ class ControlServer(SocketServer, ControlServerAPI):
         finally:
             self.led_thread.set_state(LED.AVAILABLE)
 
+            # version_info = [0, 2, 1, 7, 23, 2]
+            # serial_number = DeviceSerialNumber(version_info[0],
+            #                             version_info[1],
+            #                             version_info[2],
+            #                             version_info[3],
+            #                             version_info[4],
+            #                             version_info[5])
+            # response = DeviceResponseInfo('Elite-EDC', serial_number, (164, 218, 50, 212, 231, 12), addr_type=0)
+            # print('setup done', response)
+            # ret = self.device_manager.connect(response)
+            # print('ret', ret)
+
     def _setup_get_available_channel(self) -> Optional[List[int]]:
         client = self.data_server.client()
 
@@ -557,8 +573,15 @@ class ControlServer(SocketServer, ControlServerAPI):
             response = content
         else:
             response = to_device_info(content)
-
         try:
+            # version_info = [0, 2, 1, 7, 23, 2]
+            # serial_number = DeviceSerialNumber(version_info[0],
+            #                             version_info[1],
+            #                             version_info[2],
+            #                             version_info[3],
+            #                             version_info[4],
+            #                             version_info[5])
+            # response = DeviceResponseInfo('Elite-EDC', serial_number, (164, 218, 50, 212, 231, 12))
             ret = self.device_manager.connect(response)
         except DeviceInstructionError as e:
             '''when device reset fail error'''
@@ -1083,6 +1106,10 @@ class ControlServer(SocketServer, ControlServerAPI):
 
         elif oper == InternalInstruction.PREDEFINED_DISABLE_CACHE:
             return True
+        
+        elif oper == InternalInstruction.PREDEFINED_IDLE:
+            device.status = 1
+            return True
 
         else:
             return False
@@ -1098,6 +1125,7 @@ class ControlServer(SocketServer, ControlServerAPI):
 
         client = self.data_server.client()
         project = None
+        _project_meta_id = None
         if client is not None:
             info = self.file_manager.use(device)
 
@@ -1110,10 +1138,13 @@ class ControlServer(SocketServer, ControlServerAPI):
                 info = self.file_manager.save(device, filename)
             
             if device.occupied_by_project != None:
-                project = json.dumps(self.project_manager.get(device.occupied_by_project).info_pass_data_server())
-
+                project = self.project_manager.get(device.occupied_by_project).info_pass_data_server()
+                _project_meta_id = MetaProjectInfo.create_project_meta(project)
+                new_project = self.project_manager.get(device.occupied_by_project)
+                new_project.project_meta_id = _project_meta_id
+                
             with client:
-                client.update_device_configuration(device, info.meta_file, value, project)
+                client.update_device_configuration(device, info.meta_file, value, _project_meta_id)
 
     def _device_set_disable_cache(self, device: CompletedDevice, disable):
         if disable:
@@ -1343,6 +1374,57 @@ class ControlServer(SocketServer, ControlServerAPI):
                 } for pkg in PipPackage.list()]
 
             return ret
+        
+        def save_data_to_subject(self, project, meta, device, subject):
+            mode =  device.get_parameter('MODE')
+            library = device.library_name
+            device = {
+                "library": library,
+                "mode": mode
+            }
+
+                # "pattern": {
+                #     "id": number,
+                #     "name": string,
+                #     "parameter": object,
+                # },
+                # "data": {
+                #     "id": list[int],
+                #     "channel: list[int]
+                # }
+
+            with Session() as session:
+                meta_result = session.query(RecordingDataMeta).filter(RecordingDataMeta.project == meta).first()
+                self.mqtt_thread.publish('', json.dumps({
+                    "pattern": {
+                        "id": 4,
+                    },
+                    "data": {
+                        "id": [meta_result.id],
+                        "channel": ['Time', 6]
+                    },
+                    "others": {
+                        "subject": subject,
+                        "device": device,
+                        "project": project,
+                        "project_meta": meta,
+                    }
+                }), analysis= True)
+                # result = session.query(recording_raws).order_by(recording_raws.id.desc()).first()
+                # value = result.data.split(' ')[1:-1:2]
+                # _value = list(map(int, value))
+                # sum_value = sum(_value)
+                # average_value = sum_value / len(_value)
+                # subject_data = SubjectData(
+                #     subject_id= subject['id'],
+                #     project = project,
+                #     meta = meta,
+                #     mode= device,
+                #     data= average_value
+                # )
+                # # Execute the update query
+                # session.add(subject_data)
+                # session.commit()
 
         def _hardware_send_test_hardware(self, section: Optional[str] = None) -> Dict[str, Any]:
             if section is not None:
@@ -1387,7 +1469,6 @@ class ControlServer(SocketServer, ControlServerAPI):
             if client is not None:
                 with client:
                     client.show_data(device)
-
 class _RandomCrashThread(ServerThread):
     def __init__(self):
         super().__init__('Crash')

python/biopro/server/mqtt.py

@@ -170,9 +170,11 @@ class MqttThread(threading.Thread):
         self.sleep(3)
         self._mqtt_client_local.reconnect()
 
-    def publish(self, topic: str, payload: str, inter = False, qos = 2):
+    def publish(self, topic: str, payload: str, inter = False, analysis=False, qos = 2):
         if inter:
             _topic = self.__controller_ID + '_user'
+        elif analysis:
+            _topic = self.__controller_ID + '_data_analysis/get_analysis_data'
         else:
             _topic = self.__controller_ID + '/' + topic
         if self._mqtt_client_local is not None:

python/biopro/server/recording.py

@@ -158,7 +158,6 @@ class RecordingProcess(Process):
         elif isinstance(decoder, EISZeroOneDataDecoder):
             # get amp_gain from meta file
             decoder._mode = self._meta_file.configuration.get_parameter('MODE')
-            decoder._ac_amp = self._meta_file.configuration.get_parameter('EIS_AC_AMP')
 
         return decoder
 

python/res/devlib/json/Elite_EDC_1.5re.devlib.json

@@ -951,7 +951,7 @@
           "10": "cali_dac_test",
           "11": "cali_adc_test",
           "12": "",
-          "17": "start_data_with_empty_ins",
+          "17": "idle",
           "*": "start_data"
         }
       }
@@ -979,10 +979,8 @@
       "VIS_STI",
       "_cdr('1X;4X>ADC_VALUE_I')"
     ],
-    "start_data_with_empty_ins": [
-      "data_format",
-      "_sync(True)",
-      "VIS_STI"
+    "idle": [
+      "_idle()"
     ],
     "start_data": [
       "data_format",

python/res/devlib/json/Elite_EIS_1.1.devlib.json

@@ -34,6 +34,7 @@
         0,
         1,
         1,
+        1,
         1
       ],
       "domain": {
@@ -98,7 +99,8 @@
         "V-T Graph",
         "R-T Graph",
         "EIS constant frequency",
-        "Dev Mode"
+        "Dev Mode",
+        "Idle"
       ]
     },
     "GENERAL_HS_RTIA": {
@@ -370,10 +372,14 @@
       {
         "expression": "MODE",
         "when": {
+          "7": "idle",
           "*": "start_data"
         }
       }
     ],
+    "idle": [
+      "_idle()"
+    ],
     "start_data": [
       "data_format_cali",
       "_notify(True)",

python/res/devlib/json/Elite_EIS_MINI_1.0.devlib.json

@@ -34,6 +34,7 @@
         0,
         1,
         1,
+        1,
         1
       ],
       "domain": {
@@ -98,7 +99,8 @@
         "V-T Graph",
         "R-T Graph",
         "EIS constant frequency",
-        "Dev Mode"
+        "Dev Mode",
+        "Idle"
       ]
     },
     "GENERAL_HS_RTIA": {
@@ -370,10 +372,14 @@
       {
         "expression": "MODE",
         "when": {
+          "7": "idle",
           "*": "start_data"
         }
       }
     ],
+    "idle": [
+      "_idle()"
+    ],
     "start_data": [
       "data_format_cali",
       "_notify(True)",

python/res/devlib/json/Elite_TRIG_0.1.devlib.json

@@ -9,9 +9,20 @@
     "minor_version_number": 0
   },
   "constant": {
-
+    "TIME_MAX": 100000
   },
   "parameters": {
+    "TIME_DURATION": {
+      "description": "timer",
+      "record_meta": true,
+      "initial": 0,
+      "domain": [
+        "TIME_MAX"
+      ],
+      "value": {
+        "expression": "VALUE"
+      }
+    },
     "ACC_a_out0": {
       "description": "Switch of analog current channel 1",
       "record_meta": true,

update_db_package.sh

@@ -7,8 +7,26 @@ sudo apt-get install python3-numpy
 # install python module SQLAlchemy
 pip3 install SQLAlchemy
 
+# create folder to save project logger
+mkdir -p /home/pi/logger/project
+
 # add column cycle in projects
 sudo su -c "psql -d postgres -c \"ALTER TABLE projects ADD COLUMN IF NOT EXISTS cycle JSONB;\"" postgres
 
 # add column project in recording_data_metas
 sudo su -c "psql -d postgres -c \"ALTER TABLE recording_data_metas ADD COLUMN IF NOT EXISTS project Int4;\"" postgres
+
+# remove default controller info
+sudo su -c "psql -d postgres -c \"DELETE FROM controllers WHERE name='WTP_NONE';\"" postgres
+
+# change table name from meta_project_info to project_metas
+sudo su -c "psql -d postgres -c \"ALTER TABLE IF EXISTS meta_project_info RENAME TO project_metas;\"" postgres
+
+# change table name from project_report to project_reports
+sudo su -c "psql -d postgres -c \"ALTER TABLE IF EXISTS project_report RENAME TO project_reports;\"" postgres
+
+# change table project_meta column cycle to type jsonb
+sudo su -c "psql -d postgres -c \"ALTER TABLE project_metas ALTER COLUMN cycle type jsonb USING (cycle::jsonb);\"" postgres
+
+# add column subject in project
+sudo su -c "psql -d postgres -c \"ALTER TABLE projects ADD COLUMN IF NOT EXISTS subject JSONB;\"" postgres