问:
master-datasources.xml content always revert back to initial configuration when I start wso2server 5.9
I am beginner to WSO2, and I'm trying to configure Identity server data-source to PostgreSQL, using the documentation.

JDBC driver used

My latest master-datasources.xml is

```<code><datasources-configuration xmlns:svns="http://org.wso2.securevault/configuration">

    <providers>
        <provider>org.wso2.carbon.ndatasource.rdbms.RDBMSDataSourceReader</provider>
    </providers>

    <datasources>

        <datasource>
            <name>WSO2_CARBON_DB</name>
            <description>The datasource used for registry and user manager</description>
            <jndiConfig>
                <name>jdbc/WSO2CarbonDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:postgresql://localhost:5432/wso2_db</url>
                    <username>postgres</username>
                    <password>root</password>
                    <driverClassName>org.postgresql.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1; COMMIT</validationQuery>
                    <validationInterval>30000</validationInterval>
                    <defaultAutoCommit>true</defaultAutoCommit>
                    <commitOnReturn>true</commitOnReturn>
                </configuration>
            </definition>
        </datasource>
        <datasource>
            <name>WSO2_SHARED_DB</name>
            <description>Shared Database for user and registry data</description>
            <jndiConfig>
                <name>jdbc/SHARED_DB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:postgresql://localhost:5432/wso2_db</url>
                    <username>postgres</username>
                    <password>root</password>
                    <driverClassName>org.postgresql.Driver</driverClassName>
                    <testOnBorrow>true</testOnBorrow>
                    <maxWait>60000</maxWait>
                    <defaultAutoCommit>true</defaultAutoCommit>
                    <validationInterval>30000</validationInterval>
                    <maxActive>50</maxActive>
                    <jmxEnabled>false</jmxEnabled>
                 </configuration>
            </definition>
        </datasource>

     <datasource>
 <name>WSO2_IDENTITY_DB</name>
 <description>Shared database for identity data</description>
 <jndiConfig>
 <name>jdbc/WSO2IdentityDB</name>
 </jndiConfig>
 <definition type="RDBMS">
 <configuration>
 <url>jdbc:postgresql://localhost:5432/wso2_db</url>
 <username>postgres</username>
 <password>root</password>
 <driverClassName>org.postgresql.Driver</driverClassName>
 </configuration>
 </definition>
 </datasource>
 </datasources>

</datasources-configuration>
</code>```

When I start running WSO2 server , master-datasources.xml revertback to initial H2 configuration.

I modified deployment.toml based on the suggestion from @Piraveena Paralogarajah.

```<code>[server]
hostname = "localhost"
node_ip = "127.0.0.1"
base_path = "https://$ref{server.hostname}:${carbon.management.port}"

[super_admin]
username = "admin"
password = "admin"
create_admin_account = true

[user_store]
type = "read_write_ldap"
connection_url = "ldap://localhost:${Ports.EmbeddedLDAP.LDAPServerPort}"
connection_name = "uid=admin,ou=system"
connection_password = "admin"
base_dn = "dc=wso2,dc=org"      #refers the base dn on which the user and group search bases will be generated

[database.identity_db]
type = "postgre"
hostname = "localhost"
name = "wso2_db"
username = "postgres"
password = "root"
port = "5432"

[database.shared_db]
type = "postgre"
hostname = "localhost"
name = "wso2_db"
username = "postgres"
password = "root"
port = "5432"

[keystore.primary]
name = "wso2carbon.jks"
password = "wso2carbon"
</code>```

executed Query

```<code><IS-HOME>/dbscripts/identity/postgresql.sql
<IS-HOME>/dbscripts/identity/uma/postgresql.sql
<IS-HOME>/dbscripts/consent/postgresql.sql
</code>```

this time master-datasources.xml updated for postgress. But got exception while running server.

```<code>2020-02-19 16:44:35,247] [] ERROR {org.wso2.carbon.user.core.common.DefaultRealm} - nullType class java.lang.reflect.InvocationTargetException org.wso2.carbon.user.core.UserStoreException: nullType class java.lang.reflect.InvocationTargetException
        at org.wso2.carbon.user.core.common.DefaultRealm.createObjectWithOptions(DefaultRealm.java:397)
        at org.wso2.carbon.user.core.common.DefaultRealm.initializeObjects(DefaultRealm.java:224)
        at org.wso2.carbon.user.core.common.DefaultRealm.init(DefaultRealm.java:129)
        at org.wso2.carbon.user.core.common.DefaultRealmService.initializeRealm(DefaultRealmService.java:264)
        at org.wso2.carbon.user.core.common.DefaultRealmService.<init>(DefaultRealmService.java:102)
        at org.wso2.carbon.user.core.common.DefaultRealmService.<init>(DefaultRealmService.java:115)
        at org.wso2.carbon.user.core.internal.Activator.startDeploy(Activator.java:72)
        at org.wso2.carbon.user.core.internal.BundleCheckActivator.start(BundleCheckActivator.java:61)
        at org.eclipse.osgi.internal.framework.BundleContextImpl$3.run(BundleContextImpl.java:842)
        at org.eclipse.osgi.internal.framework.BundleContextImpl$3.run(BundleContextImpl.java:1)
        at java.security.AccessController.doPrivileged(Native Method)
        at org.eclipse.osgi.internal.framework.BundleContextImpl.startActivator(BundleContextImpl.java:834)
        at org.eclipse.osgi.internal.framework.BundleContextImpl.start(BundleContextImpl.java:791)
        at org.eclipse.osgi.internal.framework.EquinoxBundle.startWorker0(EquinoxBundle.java:1013)
        at org.eclipse.osgi.internal.framework.EquinoxBundle$EquinoxModule.startWorker(EquinoxBundle.java:365)
        at org.eclipse.osgi.container.Module.doStart(Module.java:598)
        at org.eclipse.osgi.container.Module.start(Module.java:462)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel$1.run(ModuleContainer.java:1820)
        at org.eclipse.osgi.internal.framework.EquinoxContainerAdaptor$2$1.execute(EquinoxContainerAdaptor.java:150)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.incStartLevel(ModuleContainer.java:1813)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.incStartLevel(ModuleContainer.java:1770)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.doContainerStartLevel(ModuleContainer.java:1735)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.dispatchEvent(ModuleContainer.java:1661)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.dispatchEvent(ModuleContainer.java:1)
        at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:234)
        at org.eclipse.osgi.framework.eventmgr.EventManager$EventThread.run(EventManager.java:345)
Caused by: java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at org.wso2.carbon.user.core.common.DefaultRealm.createObjectWithOptions(DefaultRealm.java:351)
        ... 25 more
Caused by: org.wso2.carbon.user.core.UserStoreException: Error occurred while checking is existing domain : PRIMARY for tenant : -1234
        at org.wso2.carbon.user.core.util.UserCoreUtil.persistDomain(UserCoreUtil.java:860)
        at org.wso2.carbon.user.core.common.AbstractUserStoreManager.persistDomain(AbstractUserStoreManager.java:6190)
        at org.wso2.carbon.user.core.ldap.ReadOnlyLDAPUserStoreManager.<init>(ReadOnlyLDAPUserStoreManager.java:240)
        at org.wso2.carbon.user.core.ldap.ReadWriteLDAPUserStoreManager.<init>(ReadWriteLDAPUserStoreManager.java:120)
        ... 30 more
Caused by: org.wso2.carbon.user.core.UserStoreException: DB error occurred while checking is existing domain : PRIMARY & tenant id : -1234
        at org.wso2.carbon.user.core.util.UserCoreUtil.isExistingDomain(UserCoreUtil.java:1009)
        at org.wso2.carbon.user.core.util.UserCoreUtil.persistDomain(UserCoreUtil.java:849)
        ... 33 more
Caused by: org.postgresql.util.PSQLException: ERROR: relation "um_domain" does not exist
  Position: 26
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2510)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2245)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:311)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:447)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:368)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:159)
        at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:109)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
        at com.sun.proxy.$Proxy53.executeQuery(Unknown Source)
        at org.wso2.carbon.user.core.util.UserCoreUtil.isExistingDomain(UserCoreUtil.java:998)
        ... 34 more

[2020-02-19 16:44:35,275] [] ERROR {org.wso2.carbon.user.core.internal.Activator} - Cannot start User Manager Core bundle org.wso2.carbon.user.core.UserStoreException: Cannot initialize the realm.
        at org.wso2.carbon.user.core.common.DefaultRealmService.initializeRealm(DefaultRealmService.java:274)
        at org.wso2.carbon.user.core.common.DefaultRealmService.<init>(DefaultRealmService.java:102)
        at org.wso2.carbon.user.core.common.DefaultRealmService.<init>(DefaultRealmService.java:115)
        at org.wso2.carbon.user.core.internal.Activator.startDeploy(Activator.java:72)
        at org.wso2.carbon.user.core.internal.BundleCheckActivator.start(BundleCheckActivator.java:61)
        at org.eclipse.osgi.internal.framework.BundleContextImpl$3.run(BundleContextImpl.java:842)
        at org.eclipse.osgi.internal.framework.BundleContextImpl$3.run(BundleContextImpl.java:1)
        at java.security.AccessController.doPrivileged(Native Method)
        at org.eclipse.osgi.internal.framework.BundleContextImpl.startActivator(BundleContextImpl.java:834)
        at org.eclipse.osgi.internal.framework.BundleContextImpl.start(BundleContextImpl.java:791)
        at org.eclipse.osgi.internal.framework.EquinoxBundle.startWorker0(EquinoxBundle.java:1013)
        at org.eclipse.osgi.internal.framework.EquinoxBundle$EquinoxModule.startWorker(EquinoxBundle.java:365)
        at org.eclipse.osgi.container.Module.doStart(Module.java:598)
        at org.eclipse.osgi.container.Module.start(Module.java:462)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel$1.run(ModuleContainer.java:1820)
        at org.eclipse.osgi.internal.framework.EquinoxContainerAdaptor$2$1.execute(EquinoxContainerAdaptor.java:150)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.incStartLevel(ModuleContainer.java:1813)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.incStartLevel(ModuleContainer.java:1770)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.doContainerStartLevel(ModuleContainer.java:1735)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.dispatchEvent(ModuleContainer.java:1661)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.dispatchEvent(ModuleContainer.java:1)
        at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:234)
        at org.eclipse.osgi.framework.eventmgr.EventManager$EventThread.run(EventManager.java:345)
Caused by: org.wso2.carbon.user.core.UserStoreException: nullType class java.lang.reflect.InvocationTargetException
        at org.wso2.carbon.user.core.common.DefaultRealm.initializeObjects(DefaultRealm.java:318)
        at org.wso2.carbon.user.core.common.DefaultRealm.init(DefaultRealm.java:129)
        at org.wso2.carbon.user.core.common.DefaultRealmService.initializeRealm(DefaultRealmService.java:264)
        ... 22 more
Caused by: org.wso2.carbon.user.core.UserStoreException: nullType class java.lang.reflect.InvocationTargetException
        at org.wso2.carbon.user.core.common.DefaultRealm.createObjectWithOptions(DefaultRealm.java:397)
        at org.wso2.carbon.user.core.common.DefaultRealm.initializeObjects(DefaultRealm.java:224)
        ... 24 more
Caused by: java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at org.wso2.carbon.user.core.common.DefaultRealm.createObjectWithOptions(DefaultRealm.java:351)
        ... 25 more
Caused by: org.wso2.carbon.user.core.UserStoreException: Error occurred while checking is existing domain : PRIMARY for tenant : -1234
        at org.wso2.carbon.user.core.util.UserCoreUtil.persistDomain(UserCoreUtil.java:860)
        at org.wso2.carbon.user.core.common.AbstractUserStoreManager.persistDomain(AbstractUserStoreManager.java:6190)
        at org.wso2.carbon.user.core.ldap.ReadOnlyLDAPUserStoreManager.<init>(ReadOnlyLDAPUserStoreManager.java:240)
        at org.wso2.carbon.user.core.ldap.ReadWriteLDAPUserStoreManager.<init>(ReadWriteLDAPUserStoreManager.java:120)
        ... 30 more
Caused by: org.wso2.carbon.user.core.UserStoreException: DB error occurred while checking is existing domain : PRIMARY & tenant id : -1234
        at org.wso2.carbon.user.core.util.UserCoreUtil.isExistingDomain(UserCoreUtil.java:1009)
        at org.wso2.carbon.user.core.util.UserCoreUtil.persistDomain(UserCoreUtil.java:849)
        ... 33 more
Caused by: org.postgresql.util.PSQLException: ERROR: relation "um_domain" does not exist
  Position: 26
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2510)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2245)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:311)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:447)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:368)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:159)
        at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:109)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
        at com.sun.proxy.$Proxy53.executeQuery(Unknown Source)
        at org.wso2.carbon.user.core.util.UserCoreUtil.isExistingDomain(UserCoreUtil.java:998)
        ... 34 more
</code>```

I tried this but it is not working.

答:
With the 4.5.0 carbon-kernel release, all WSO2 products such as APIM 3.0.0, IS 5.9.0 introduced a new config model. According to the new config model, there is a centralized configuration file (deployment.toml) where users add the configurations, then those configurations will be added to the respective .xml files.

So if you want to do some changes in the master-datasources.xml file, you have to add the relevant configs in deployment.toml file according to the new config model. With the new config model, all the changes made by you in the xml config files will be overridden by the toml configs during the server startup.

Please follow this documentation to refer further information on this new config model

Related documents:

https://wso2.com/blogs/thesource/2019/10/simplifying-configuration-with-WSO2-identity-server

Please follow this documentation if you are using trying to configure WSO2 Identity server with postgres db.
https://is.docs.wso2.com/en/next/setup/changing-to-postgresql/

[updated according to the new issue]

Please execute this script also

/dbscripts/postgresql.sql

. From the error logs it says "um_domain" does not exist. That table creation happens from this script and you haven't executed this particular script.

Body:
I am beginner to WSO2, and I'm trying to configure Identity server data-source to PostgreSQL, using the documentation.

JDBC driver used

My latest master-datasources.xml is

<datasources-configuration xmlns:svns="http://org.wso2.securevault/configuration">

    <providers>
        <provider>org.wso2.carbon.ndatasource.rdbms.RDBMSDataSourceReader</provider>
    </providers>

    <datasources>

        <datasource>
            <name>WSO2_CARBON_DB</name>
            <description>The datasource used for registry and user manager</description>
            <jndiConfig>
                <name>jdbc/WSO2CarbonDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:postgresql://localhost:5432/wso2_db</url>
                    <username>postgres</username>
                    <password>root</password>
                    <driverClassName>org.postgresql.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1; COMMIT</validationQuery>
                    <validationInterval>30000</validationInterval>
                    <defaultAutoCommit>true</defaultAutoCommit>
                    <commitOnReturn>true</commitOnReturn>
                </configuration>
            </definition>
        </datasource>
        <datasource>
            <name>WSO2_SHARED_DB</name>
            <description>Shared Database for user and registry data</description>
            <jndiConfig>
                <name>jdbc/SHARED_DB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:postgresql://localhost:5432/wso2_db</url>
                    <username>postgres</username>
                    <password>root</password>
                    <driverClassName>org.postgresql.Driver</driverClassName>
                    <testOnBorrow>true</testOnBorrow>
                    <maxWait>60000</maxWait>
                    <defaultAutoCommit>true</defaultAutoCommit>
                    <validationInterval>30000</validationInterval>
                    <maxActive>50</maxActive>
                    <jmxEnabled>false</jmxEnabled>
                 </configuration>
            </definition>
        </datasource>

     <datasource>
 <name>WSO2_IDENTITY_DB</name>
 <description>Shared database for identity data</description>
 <jndiConfig>
 <name>jdbc/WSO2IdentityDB</name>
 </jndiConfig>
 <definition type="RDBMS">
 <configuration>
 <url>jdbc:postgresql://localhost:5432/wso2_db</url>
 <username>postgres</username>
 <password>root</password>
 <driverClassName>org.postgresql.Driver</driverClassName>
 </configuration>
 </definition>
 </datasource>
 </datasources>

</datasources-configuration>

When I start running WSO2 server , master-datasources.xml revertback to initial H2 configuration.

I modified deployment.toml based on the suggestion from @Piraveena Paralogarajah.

[server]
hostname = "localhost"
node_ip = "127.0.0.1"
base_path = "https://$ref{server.hostname}:${carbon.management.port}"

[super_admin]
username = "admin"
password = "admin"
create_admin_account = true

[user_store]
type = "read_write_ldap"
connection_url = "ldap://localhost:${Ports.EmbeddedLDAP.LDAPServerPort}"
connection_name = "uid=admin,ou=system"
connection_password = "admin"
base_dn = "dc=wso2,dc=org"      #refers the base dn on which the user and group search bases will be generated

[database.identity_db]
type = "postgre"
hostname = "localhost"
name = "wso2_db"
username = "postgres"
password = "root"
port = "5432"

[database.shared_db]
type = "postgre"
hostname = "localhost"
name = "wso2_db"
username = "postgres"
password = "root"
port = "5432"

[keystore.primary]
name = "wso2carbon.jks"
password = "wso2carbon"

executed Query

<IS-HOME>/dbscripts/identity/postgresql.sql
<IS-HOME>/dbscripts/identity/uma/postgresql.sql
<IS-HOME>/dbscripts/consent/postgresql.sql

this time master-datasources.xml updated for postgress. But got exception while running server.

2020-02-19 16:44:35,247] [] ERROR {org.wso2.carbon.user.core.common.DefaultRealm} - nullType class java.lang.reflect.InvocationTargetException org.wso2.carbon.user.core.UserStoreException: nullType class java.lang.reflect.InvocationTargetException
        at org.wso2.carbon.user.core.common.DefaultRealm.createObjectWithOptions(DefaultRealm.java:397)
        at org.wso2.carbon.user.core.common.DefaultRealm.initializeObjects(DefaultRealm.java:224)
        at org.wso2.carbon.user.core.common.DefaultRealm.init(DefaultRealm.java:129)
        at org.wso2.carbon.user.core.common.DefaultRealmService.initializeRealm(DefaultRealmService.java:264)
        at org.wso2.carbon.user.core.common.DefaultRealmService.<init>(DefaultRealmService.java:102)
        at org.wso2.carbon.user.core.common.DefaultRealmService.<init>(DefaultRealmService.java:115)
        at org.wso2.carbon.user.core.internal.Activator.startDeploy(Activator.java:72)
        at org.wso2.carbon.user.core.internal.BundleCheckActivator.start(BundleCheckActivator.java:61)
        at org.eclipse.osgi.internal.framework.BundleContextImpl$3.run(BundleContextImpl.java:842)
        at org.eclipse.osgi.internal.framework.BundleContextImpl$3.run(BundleContextImpl.java:1)
        at java.security.AccessController.doPrivileged(Native Method)
        at org.eclipse.osgi.internal.framework.BundleContextImpl.startActivator(BundleContextImpl.java:834)
        at org.eclipse.osgi.internal.framework.BundleContextImpl.start(BundleContextImpl.java:791)
        at org.eclipse.osgi.internal.framework.EquinoxBundle.startWorker0(EquinoxBundle.java:1013)
        at org.eclipse.osgi.internal.framework.EquinoxBundle$EquinoxModule.startWorker(EquinoxBundle.java:365)
        at org.eclipse.osgi.container.Module.doStart(Module.java:598)
        at org.eclipse.osgi.container.Module.start(Module.java:462)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel$1.run(ModuleContainer.java:1820)
        at org.eclipse.osgi.internal.framework.EquinoxContainerAdaptor$2$1.execute(EquinoxContainerAdaptor.java:150)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.incStartLevel(ModuleContainer.java:1813)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.incStartLevel(ModuleContainer.java:1770)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.doContainerStartLevel(ModuleContainer.java:1735)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.dispatchEvent(ModuleContainer.java:1661)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.dispatchEvent(ModuleContainer.java:1)
        at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:234)
        at org.eclipse.osgi.framework.eventmgr.EventManager$EventThread.run(EventManager.java:345)
Caused by: java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at org.wso2.carbon.user.core.common.DefaultRealm.createObjectWithOptions(DefaultRealm.java:351)
        ... 25 more
Caused by: org.wso2.carbon.user.core.UserStoreException: Error occurred while checking is existing domain : PRIMARY for tenant : -1234
        at org.wso2.carbon.user.core.util.UserCoreUtil.persistDomain(UserCoreUtil.java:860)
        at org.wso2.carbon.user.core.common.AbstractUserStoreManager.persistDomain(AbstractUserStoreManager.java:6190)
        at org.wso2.carbon.user.core.ldap.ReadOnlyLDAPUserStoreManager.<init>(ReadOnlyLDAPUserStoreManager.java:240)
        at org.wso2.carbon.user.core.ldap.ReadWriteLDAPUserStoreManager.<init>(ReadWriteLDAPUserStoreManager.java:120)
        ... 30 more
Caused by: org.wso2.carbon.user.core.UserStoreException: DB error occurred while checking is existing domain : PRIMARY & tenant id : -1234
        at org.wso2.carbon.user.core.util.UserCoreUtil.isExistingDomain(UserCoreUtil.java:1009)
        at org.wso2.carbon.user.core.util.UserCoreUtil.persistDomain(UserCoreUtil.java:849)
        ... 33 more
Caused by: org.postgresql.util.PSQLException: ERROR: relation "um_domain" does not exist
  Position: 26
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2510)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2245)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:311)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:447)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:368)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:159)
        at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:109)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
        at com.sun.proxy.$Proxy53.executeQuery(Unknown Source)
        at org.wso2.carbon.user.core.util.UserCoreUtil.isExistingDomain(UserCoreUtil.java:998)
        ... 34 more

[2020-02-19 16:44:35,275] [] ERROR {org.wso2.carbon.user.core.internal.Activator} - Cannot start User Manager Core bundle org.wso2.carbon.user.core.UserStoreException: Cannot initialize the realm.
        at org.wso2.carbon.user.core.common.DefaultRealmService.initializeRealm(DefaultRealmService.java:274)
        at org.wso2.carbon.user.core.common.DefaultRealmService.<init>(DefaultRealmService.java:102)
        at org.wso2.carbon.user.core.common.DefaultRealmService.<init>(DefaultRealmService.java:115)
        at org.wso2.carbon.user.core.internal.Activator.startDeploy(Activator.java:72)
        at org.wso2.carbon.user.core.internal.BundleCheckActivator.start(BundleCheckActivator.java:61)
        at org.eclipse.osgi.internal.framework.BundleContextImpl$3.run(BundleContextImpl.java:842)
        at org.eclipse.osgi.internal.framework.BundleContextImpl$3.run(BundleContextImpl.java:1)
        at java.security.AccessController.doPrivileged(Native Method)
        at org.eclipse.osgi.internal.framework.BundleContextImpl.startActivator(BundleContextImpl.java:834)
        at org.eclipse.osgi.internal.framework.BundleContextImpl.start(BundleContextImpl.java:791)
        at org.eclipse.osgi.internal.framework.EquinoxBundle.startWorker0(EquinoxBundle.java:1013)
        at org.eclipse.osgi.internal.framework.EquinoxBundle$EquinoxModule.startWorker(EquinoxBundle.java:365)
        at org.eclipse.osgi.container.Module.doStart(Module.java:598)
        at org.eclipse.osgi.container.Module.start(Module.java:462)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel$1.run(ModuleContainer.java:1820)
        at org.eclipse.osgi.internal.framework.EquinoxContainerAdaptor$2$1.execute(EquinoxContainerAdaptor.java:150)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.incStartLevel(ModuleContainer.java:1813)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.incStartLevel(ModuleContainer.java:1770)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.doContainerStartLevel(ModuleContainer.java:1735)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.dispatchEvent(ModuleContainer.java:1661)
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.dispatchEvent(ModuleContainer.java:1)
        at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:234)
        at org.eclipse.osgi.framework.eventmgr.EventManager$EventThread.run(EventManager.java:345)
Caused by: org.wso2.carbon.user.core.UserStoreException: nullType class java.lang.reflect.InvocationTargetException
        at org.wso2.carbon.user.core.common.DefaultRealm.initializeObjects(DefaultRealm.java:318)
        at org.wso2.carbon.user.core.common.DefaultRealm.init(DefaultRealm.java:129)
        at org.wso2.carbon.user.core.common.DefaultRealmService.initializeRealm(DefaultRealmService.java:264)
        ... 22 more
Caused by: org.wso2.carbon.user.core.UserStoreException: nullType class java.lang.reflect.InvocationTargetException
        at org.wso2.carbon.user.core.common.DefaultRealm.createObjectWithOptions(DefaultRealm.java:397)
        at org.wso2.carbon.user.core.common.DefaultRealm.initializeObjects(DefaultRealm.java:224)
        ... 24 more
Caused by: java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at org.wso2.carbon.user.core.common.DefaultRealm.createObjectWithOptions(DefaultRealm.java:351)
        ... 25 more
Caused by: org.wso2.carbon.user.core.UserStoreException: Error occurred while checking is existing domain : PRIMARY for tenant : -1234
        at org.wso2.carbon.user.core.util.UserCoreUtil.persistDomain(UserCoreUtil.java:860)
        at org.wso2.carbon.user.core.common.AbstractUserStoreManager.persistDomain(AbstractUserStoreManager.java:6190)
        at org.wso2.carbon.user.core.ldap.ReadOnlyLDAPUserStoreManager.<init>(ReadOnlyLDAPUserStoreManager.java:240)
        at org.wso2.carbon.user.core.ldap.ReadWriteLDAPUserStoreManager.<init>(ReadWriteLDAPUserStoreManager.java:120)
        ... 30 more
Caused by: org.wso2.carbon.user.core.UserStoreException: DB error occurred while checking is existing domain : PRIMARY & tenant id : -1234
        at org.wso2.carbon.user.core.util.UserCoreUtil.isExistingDomain(UserCoreUtil.java:1009)
        at org.wso2.carbon.user.core.util.UserCoreUtil.persistDomain(UserCoreUtil.java:849)
        ... 33 more
Caused by: org.postgresql.util.PSQLException: ERROR: relation "um_domain" does not exist
  Position: 26
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2510)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2245)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:311)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:447)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:368)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:159)
        at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:109)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
        at com.sun.proxy.$Proxy53.executeQuery(Unknown Source)
        at org.wso2.carbon.user.core.util.UserCoreUtil.isExistingDomain(UserCoreUtil.java:998)
        ... 34 more

I tried this but it is not working.


来源: stackexchange

问:
SELECT DISTINCT in DataView's RowFilter
I'm trying to narrow down the rows that are in my DataView based on a relation with another table, and the RowFilter I'm using is as follows;

```<code>dv = new DataView(myDS.myTable,
                 "id IN (SELECT DISTINCT parentID FROM myOtherTable)",
                 "name asc",
                 DataViewRowState.CurrentRows);
</code>```

"myTable" and "myOther" table are related via myTable.ID and myOtherTable.parentID, and so the idea is that the DataView should only contain rows from "myTable" which have corresponding child rows in "myOtherTable".

Unfortunately, I'm getting this error;

Syntax error: Missing operand after
  'DISTINCT' operator.

The SQL is fine as far as I am aware, so I'm wondering is there some limitation on using the DISTINCT keyword as part of RowFilter's SQL? Anyone have any idea?

答:
Unfortunately, I don't think you can perform a subquery in a DataView's filter expression. You're only allowed to use a subset of SQL in some expressions (documented here).

You'll probably need to perform your subquery (SELECT DISTINCT parentID FROM myOtherTable) separately.

This article describes the problem and a possible solution.

OwnerDisplayName: El Cristoir

ContentLicense: CC BY-SA 2.5

Title: SELECT DISTINCT in DataView's RowFilter

Body:
I'm trying to narrow down the rows that are in my DataView based on a relation with another table, and the RowFilter I'm using is as follows;

dv = new DataView(myDS.myTable,
                 "id IN (SELECT DISTINCT parentID FROM myOtherTable)",
                 "name asc",
                 DataViewRowState.CurrentRows);

"myTable" and "myOther" table are related via myTable.ID and myOtherTable.parentID, and so the idea is that the DataView should only contain rows from "myTable" which have corresponding child rows in "myOtherTable".

Unfortunately, I'm getting this error;

Syntax error: Missing operand after
  'DISTINCT' operator.

The SQL is fine as far as I am aware, so I'm wondering is there some limitation on using the DISTINCT keyword as part of RowFilter's SQL? Anyone have any idea?

LastEditorDisplayName: Chris Ballance

OwnerDisplayName: Matt Peterson

ContentLicense: CC BY-SA 3.0


问:
Hiding multiple tag with same name or class
I have using prototype 1.5. Can you please tell me, how can i hide/show the div tag with same name or class. </p>

```<code>Element.hide('indicate')
</code>```

<p>But, the above line only satisfy to hide the first element only. "indicate" is the id. </p>

答:
ID's have to be unique. Select with a class instead.

```<code>$$('div.indicate').hide();
</code>```

or with its name attribute

```<code>$$('div[name=indicate]').hide();
</code>```

Body:
I have using prototype 1.5. Can you please tell me, how can i hide/show the div tag with same name or class.

Element.hide('indicate')

But, the above line only satisfy to hide the first element only. "indicate" is the id.

Body:
ID's have to be unique. Select with a class instead.

$$('div.indicate').hide();

or with its name attribute

$$('div[name=indicate]').hide();


问:
From Google Site manipulate a Google Sheet (as a simpler page visit counter)
I am aware, that I should track my Google Site with Analytics, but I am (for certain reasons) not able to use.
Therefore I would like to add a VERY SIMPLE page counter for the Google Site.
So for every page displayed, I would like to have a counter in Google Sheets (so add +1). [A plus to display the page counter]

Anyone done this?

Thanks

答:
I don't know how robust this is gonna be you could give it a try

Option 1: Storing data in Spreadsheet

It would be very simple to get a cell and sum it on on every request that you get.

```<code>function doGet(e){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  var cellCounter = sheet.getRange(1, 1);

  cellCounter.setValue(cellCounter.getValue() + 1);

  return HtmlService.createHtmlOutput("<b>current sum is at " + cellCounter.getValue() + "</b>");

}
</code>```

Option 2: Storing data in script Properties

Another option is to use the PropertiesService  to store the data "inside the script".

```<code>function doGet(e){
  var prop = PropertiesService.getScriptProperties();

  var key = "COUNTER";
  var counter = prop.getProperty(key);

  if(counter){
    prop.setProperty(key, parseInt(counter) + 1)
  }else(
    prop.setProperty(key, 1)
  )

  return HtmlService.createHtmlOutput("<b>current sum is at " + prop.getProperty(key) + "</b>");

}
</code>```

Although both options are not very robust, although depending on the traffic you expect it could work very well.

ContentLicense: CC BY-SA 4.0

Body:
I don't know how robust this is gonna be you could give it a try

Option 1: Storing data in Spreadsheet

It would be very simple to get a cell and sum it on on every request that you get.

function doGet(e){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  var cellCounter = sheet.getRange(1, 1);

  cellCounter.setValue(cellCounter.getValue() + 1);

  return HtmlService.createHtmlOutput("<b>current sum is at " + cellCounter.getValue() + "</b>");

}

Option 2: Storing data in script Properties

Another option is to use the PropertiesService  to store the data "inside the script".

function doGet(e){
  var prop = PropertiesService.getScriptProperties();

  var key = "COUNTER";
  var counter = prop.getProperty(key);

  if(counter){
    prop.setProperty(key, parseInt(counter) + 1)
  }else(
    prop.setProperty(key, 1)
  )

  return HtmlService.createHtmlOutput("<b>current sum is at " + prop.getProperty(key) + "</b>");

}

Although both options are not very robust, although depending on the traffic you expect it could work very well.


问:
How to find the start time and end time of an event group by class in python?
I have a data frame consists of column 1 i.e event and column 2 is Datetime:

Sample data

```<code> Class Event    Time
0   A   0   2020-02-19 11:00:00
1   A   0   2020-02-19 11:30:00
2   B   1   2020-02-19 11:00:00
3   B   1   2020-02-19 11:30:00
4   B   0   2020-02-19 12:00:00
5   B   0   2020-02-19 12:30:00
6   A   0   2020-02-19 14:00:00
7   B   1   2020-02-19 13:30:00
8   A   1   2020-02-19 15:00:00
9   B   1   2020-02-19 15:30:00
10  A   0   2020-02-19 15:30:00
11  B   0   2020-02-19 16:00:00
12  A   1   2020-02-19 16:30:00
</code>```

And I want to find start time and end time of each event by Class:
I have tried the below code from the answer to my previous question but I am getting an empty dataframe:

```<code>current_event = None
result = []
grouped=df.groupby(['Class'])
for name, group in grouped:
    for class, event, time in zip(data['Class'],data['Event'], data['Time']):
        if event != current_event:
           if current_event is not None:
                result.append([Class,current_event, start_time, time])
            class, current_event, start_time = class, event, time
data = pandas.DataFrame(result, columns=['Class','Event','EventStartTime','EventEndTime'])
</code>```

Desired Data

```<code>    Class   Event   EventStartTime       EventEndTime
0    A        0    2020-02-19 11:00:00  2020-02-19 15:00:00
1    A        1    2020-02-19 15:00:00  2020-02-10 15:30:00
2    A        0    2020-02-19 15:30:00  2020-02-10 16:30:00
3    B        1    2020-02-19 11:00:00  2020-02-10 12:00:00
4    B        0    2020-02-19 12:00:00  2020-02-19 13:30:00
5    B        1    2020-02-19 13:30:00  2020-02-19 16:00:00
</code>```

Note: EventEndTime is time when the event changes the value say from value 1 to got change to 0 or any other value of a particular class

Body:
I have a data frame consists of column 1 i.e event and column 2 is Datetime:

Sample data

 Class Event    Time
0   A   0   2020-02-19 11:00:00
1   A   0   2020-02-19 11:30:00
2   B   1   2020-02-19 11:00:00
3   B   1   2020-02-19 11:30:00
4   B   0   2020-02-19 12:00:00
5   B   0   2020-02-19 12:30:00
6   A   0   2020-02-19 14:00:00
7   B   1   2020-02-19 13:30:00
8   A   1   2020-02-19 15:00:00
9   B   1   2020-02-19 15:30:00
10  A   0   2020-02-19 15:30:00
11  B   0   2020-02-19 16:00:00
12  A   1   2020-02-19 16:30:00

And I want to find start time and end time of each event by Class:
I have tried the below code from the answer to my previous question but I am getting an empty dataframe:

current_event = None
result = []
grouped=df.groupby(['Class'])
for name, group in grouped:
    for class, event, time in zip(data['Class'],data['Event'], data['Time']):
        if event != current_event:
           if current_event is not None:
                result.append([Class,current_event, start_time, time])
            class, current_event, start_time = class, event, time
data = pandas.DataFrame(result, columns=['Class','Event','EventStartTime','EventEndTime'])

Desired Data

    Class   Event   EventStartTime       EventEndTime
0    A        0    2020-02-19 11:00:00  2020-02-19 15:00:00
1    A        1    2020-02-19 15:00:00  2020-02-10 15:30:00
2    A        0    2020-02-19 15:30:00  2020-02-10 16:30:00
3    B        1    2020-02-19 11:00:00  2020-02-10 12:00:00
4    B        0    2020-02-19 12:00:00  2020-02-19 13:30:00
5    B        1    2020-02-19 13:30:00  2020-02-19 16:00:00

Note: EventEndTime is time when the event changes the value say from value 1 to got change to 0 or any other value of a particular class

Body:
Try:

dfg = df.groupby('Class')
df_new = pd.DataFrame()
for c,d in dfg:
    dfx = d[~(d.Event == d.shift().Event)]
    dfx['EventEndTime'] = d[~(d.Event == d.shift().Event)].shift(-1)['Time']
    dfx = dfx.dropna()
    df_new = pd.concat([df_new, dfx], ignore_index=True)
df_new.columns = ['Class','Event','EventStartTime','EventEndTime']
print(df_new)

  Class  Event      EventStartTime        EventEndTime
0     A      0 2020-02-19 11:00:00 2020-02-19 15:00:00
1     A      1 2020-02-19 15:00:00 2020-02-19 15:30:00
2     A      0 2020-02-19 15:30:00 2020-02-19 16:30:00
3     B      1 2020-02-19 11:00:00 2020-02-19 12:00:00
4     B      0 2020-02-19 12:00:00 2020-02-19 13:30:00
5     B      1 2020-02-19 13:30:00 2020-02-19 16:00:00


问:
merge columns: merge on column has truncated digits
I need to merge to data frames on a common column ID_key. A problem is that for one of the tables, the column ID_key has had the last digits truncated.

```<code>dataframe_a: ID_key     dataframe_b: ID_key
1002                    10020000000
1002                    10020000000
1002                    10020000000
1002                    10020000000
32410001                32410001001
32410001                32410001001
32410001                32410001001
</code>```

End result would be that dataframe_a and dataframe_b merge on column ID_key.

Is there a way to make this happen?

Body:
I need to merge to data frames on a common column ID_key. A problem is that for one of the tables, the column ID_key has had the last digits truncated.

dataframe_a: ID_key     dataframe_b: ID_key
1002                    10020000000
1002                    10020000000
1002                    10020000000
1002                    10020000000
32410001                32410001001
32410001                32410001001
32410001                32410001001

End result would be that dataframe_a and dataframe_b merge on column ID_key.

Is there a way to make this happen?

Body:
You can use regexpr and regmatches to truncate the ID_key from dataframe_b to those found in dataframe_b and then use merge.

dataframe_b$ID_key_tr  <- regmatches(dataframe_b$ID_key, regexpr(paste0("^", unique(dataframe_a$ID_key), collapse = "|"),  dataframe_b$ID_key))
dataframe_b
#       ID_key ID_key_tr
#1 10020000000      1002
#2 10020000000      1002
#3 10020000000      1002
#4 10020000000      1002
#5 32410001001  32410001
#6 32410001001  32410001
#7 32410001001  32410001

merge(dataframe_a, dataframe_b, by.x="ID_key", by.y="ID_key_tr")

Data:

dataframe_a <- data.frame(ID_key=c("1002","1002","1002","1002","32410001","32410001","32410001"))
dataframe_b <- data.frame(ID_key=c("10020000000","10020000000","10020000000","10020000000","32410001001","32410001001","32410001001"))


问:
Edit request (Add the correct join left) in oracle
i have a query, it works fine but i want to add another column!

```<code>SELECT
 typ.name as Silo_name,
 tr.DEVID,
 tr.name as dev_name
 FROM
   HANGINGTHREAD_SILO ev
   LEFT JOIN SILO typ
       ON ev.ID_SILO = typ.id
   LEFT JOIN IOT_DEVICES  tr
       ON ev.DEVICES_ID = tr.id
</code>```

But I want to add another column maximum temperature, the data for this column is in the table TEMPR_SILO.
And here's a query that I use to find the data I need for each tr.DEVID.

```<code>select MAX(to_char(TEMP,'99.99')) from TEMPR_SILO where ID_TRANS in (select max(ID_TRANS) from TEMPR_SILO)   and NAME in (select NAME from SILO_SENSOR where DEVICES_ID = tr.DEVID)
</code>```

I tried it this way, but I got the error

"ORA-20999: Failed to parse SQL query!
 ORA-06550: line 17, column 3: ORA-00933: SQL command not properly ended"

```<code>SELECT
 typ.name as Silo_name,
 tr.DEVID,
 tr.name as dev_name
 ,max(ts.TEMP)  AS "Lowest salary"
 FROM
   HANGINGTHREAD_SILO ev
   LEFT JOIN SILO typ
       ON ev.ID_SILO = typ.id
   LEFT JOIN IOT_DEVICES  tr
       ON ev.DEVICES_ID = tr.id
   LEFT JOIN  TEMPR_SILO  ts
     ON ts.name  in (select NAME from SILO_SENSOR where DEVICES_ID = tr.DEVID)
     and  ts.ID_TRANS in(select max(ID_TRANS) from TEMPR_SILO)
GROUP BY
 ev.id,
 typ.name as Silo_name,
 tr.DEVID,
 tr.name as dev_name ;
</code>```

答:
I am not sure in which query you are facing the issue.

If you are facing the issue in the last query from your question then I must say that there are multiple issues that I have highlighted in the following query:

```<code>SELECT
    TYP.NAME   AS SILO_NAME,
    TR.DEVID,
    TR.NAME    AS DEV_NAME,
    MAX(TS.TEMP) AS MAX_TEMP --"Lowest salary" -- just changed the name, not necessary in your case
FROM
    HANGINGTHREAD_SILO EV
    LEFT JOIN SILO TYP ON EV.ID_SILO = TYP.ID
    LEFT JOIN IOT_DEVICES TR ON EV.DEVICES_ID = TR.ID
    LEFT JOIN SILO_SENSOR SS ON SS.DEVICES_ID = TR.DEVID -- added this left join as JOIN of TS with it is not correct in your query
    LEFT JOIN TEMPR_SILO TS ON TS.NAME = SS.NAME -- added this join condition
                               AND TS.ID_TRANS IN (
        SELECT
            MAX(ID_TRANS)
        FROM
            TEMPR_SILO
    )
GROUP BY
    EV.ID,
    TYP.NAME, --as Silo_name, -- "as" is not allowed here
    TR.DEVID,
    TR.NAME; -- as dev_name -- "as" is not allowed here
</code>```

Body:
i have a query, it works fine but i want to add another column!

SELECT
 typ.name as Silo_name,
 tr.DEVID,
 tr.name as dev_name
 FROM
   HANGINGTHREAD_SILO ev
   LEFT JOIN SILO typ
       ON ev.ID_SILO = typ.id
   LEFT JOIN IOT_DEVICES  tr
       ON ev.DEVICES_ID = tr.id

But I want to add another column maximum temperature, the data for this column is in the table TEMPR_SILO.
And here's a query that I use to find the data I need for each tr.DEVID.

select MAX(to_char(TEMP,'99.99')) from TEMPR_SILO where ID_TRANS in (select max(ID_TRANS) from TEMPR_SILO)   and NAME in (select NAME from SILO_SENSOR where DEVICES_ID = tr.DEVID)

I tried it this way, but I got the error

"ORA-20999: Failed to parse SQL query!
 ORA-06550: line 17, column 3: ORA-00933: SQL command not properly ended"

SELECT
 typ.name as Silo_name,
 tr.DEVID,
 tr.name as dev_name
 ,max(ts.TEMP)  AS "Lowest salary"
 FROM
   HANGINGTHREAD_SILO ev
   LEFT JOIN SILO typ
       ON ev.ID_SILO = typ.id
   LEFT JOIN IOT_DEVICES  tr
       ON ev.DEVICES_ID = tr.id
   LEFT JOIN  TEMPR_SILO  ts
     ON ts.name  in (select NAME from SILO_SENSOR where DEVICES_ID = tr.DEVID)
     and  ts.ID_TRANS in(select max(ID_TRANS) from TEMPR_SILO)
GROUP BY
 ev.id,
 typ.name as Silo_name,
 tr.DEVID,
 tr.name as dev_name ;

Body:
I am not sure in which query you are facing the issue.

If you are facing the issue in the last query from your question then I must say that there are multiple issues that I have highlighted in the following query:

SELECT
    TYP.NAME   AS SILO_NAME,
    TR.DEVID,
    TR.NAME    AS DEV_NAME,
    MAX(TS.TEMP) AS MAX_TEMP --"Lowest salary" -- just changed the name, not necessary in your case
FROM
    HANGINGTHREAD_SILO EV
    LEFT JOIN SILO TYP ON EV.ID_SILO = TYP.ID
    LEFT JOIN IOT_DEVICES TR ON EV.DEVICES_ID = TR.ID
    LEFT JOIN SILO_SENSOR SS ON SS.DEVICES_ID = TR.DEVID -- added this left join as JOIN of TS with it is not correct in your query
    LEFT JOIN TEMPR_SILO TS ON TS.NAME = SS.NAME -- added this join condition
                               AND TS.ID_TRANS IN (
        SELECT
            MAX(ID_TRANS)
        FROM
            TEMPR_SILO
    )
GROUP BY
    EV.ID,
    TYP.NAME, --as Silo_name, -- "as" is not allowed here
    TR.DEVID,
    TR.NAME; -- as dev_name -- "as" is not allowed here


问:
Detecting AdBlocking software?
I was recently visiting a site and noticed that the page had a section that said it noticed that I was using AdBlocking software and could I kindly turn it off to help support a small site like that.
I was just wondering how you would do that?  Would it be best done client-side or server-side?</p>

答:
You can add javascript-code to your page, that is only executed if there's no adblocker, e.g. use "ad" as variable-name, use "ad.js" as file-name.
This code sends an ajax-event to the server, saying "this user doesn't use an adlocker". So if you don't receive that event, you know, that this user is blocking ads or even javascript altogether. </p>

Title: Detecting AdBlocking software?


问:
Filling database with some data (Users, Invoices, etc...)
I would like to fill my database with some data to let frontend developers start working with my API. I don't want this data to go live in production and so, doesn't feel it falls into the category of "Seeded data". For me, seeded data is data that goes into production and needs to be in the database before my application starts. For example, list of countries, etc.

In my case, I would like to add some users, some invoices linked to the users, some products, etc. And would be nice if this is done when doing thte deployment when we are in development stage.

Any suggestion would be really appreciated.

答:
On your startup class you will want to check based on the environment (if it's in development or not) and call the appropriate seed method.

The command to check that is.

```<code>if (Environment.IsDevelopment())
</code>```

For this task I would create a separate project to do it where I would only seed the data. It all depends on your architecture.

Body:
On your startup class you will want to check based on the environment (if it's in development or not) and call the appropriate seed method.

The command to check that is.

if (Environment.IsDevelopment())

For this task I would create a separate project to do it where I would only seed the data. It all depends on your architecture.


问:
Pygame PiTFT touch events - main loop sits waiting until touches are detected
I am using a raspberry pi and PiTFT to measure sensors and display the results.
Using the tips from https://stackoverflow.com/a/54986161 all is working fine but one problem:
the main loop sits waiting until the lcd is touched.
I removed all non trivial parts from the program just to test the main loop.
This piece of code is printing the state after touch and release.
As long as the lcd is being touched the main loop keeps printing, releasing the lcd stops the loop. How can I rewrite the loop so it's keeps running even when there is no keypress?</p>

```<code>#!/usr/bin/python3
import subprocess, pygame, evdev, select
pygame.init()
surfaceSize = (320, 240)
##
# Everything that follows is for handling the touchscreen touch events via evdev
##

# Used to map touch event from the screen hardware to the pygame surface pixels.
# (Those values have been found empirically, but I'm working on a simple interactive calibration tool
tftOrig = (3750, 180)
tftEnd = (150, 3750)
tftDelta = (tftEnd [0] - tftOrig [0], tftEnd [1] - tftOrig [1])
tftAbsDelta = (abs(tftEnd [0] - tftOrig [0]), abs(tftEnd [1] - tftOrig [1]))

# We use evdev to read events from our touchscreen
# (The device must exist and be properly installed for this to work)
touch = evdev.InputDevice('/dev/input/touchscreen')

# We make sure the events from the touchscreen will be handled only by this program
# (so the mouse pointer won't move on X when we touch the TFT screen)
touch.grab()
# Prints some info on how evdev sees our input device
print(touch)
# Even more info for curious people
#print(touch.capabilities())

# Here we convert the evdev "hardware" touch coordinates into pygame surface pixel coordinates
def getPixelsFromCoordinates(coords):
    # TODO check divide by 0!
    if tftDelta [0] < 0:
        x = float(tftAbsDelta [0] - coords [0] + tftEnd [0]) / float(tftAbsDelta [0]) * float(surfaceSize [0])
    else:
        x = float(coords [0] - tftOrig [0]) / float(tftAbsDelta [0]) * float(surfaceSize [0])
    if tftDelta [1] < 0:
        y = float(tftAbsDelta [1] - coords [1] + tftEnd [1]) / float(tftAbsDelta [1]) * float(surfaceSize [1])
    else:
        y = float(coords [1] - tftOrig [1]) / float(tftAbsDelta [1]) * float(surfaceSize [1])
    return (int(x), int(y))

# Was useful to see what pieces I would need from the evdev events
def printEvent(event):
    print(evdev.categorize(event))
    print("Value: {0}".format(event.value))
    print("Type: {0}".format(event.type))
    print("Code: {0}".format(event.code))

while True:

    r,w,x = select.select([touch], [], [])
    for event in touch.read():
        if event.type == evdev.ecodes.EV_ABS:
            if event.code == 1:
                X = event.value
            elif event.code == 0:
                Y = event.value
        elif event.type == evdev.ecodes.EV_KEY:
            if event.code == 330 and event.value == 1:
                printEvent(event)
                p = getPixelsFromCoordinates((X, Y))
                if 12 <= p[0] <= 72 and 210 <= p[1] <= 230:
                    num = 0
                if 92 <= p[0] <= 154 and 210 <= p[1] <= 230:
                    num = 1
                if 173 <= p[0] <= 234 and 210 <= p[1] <= 230:
                    num = 2
                if 255 <= p[0] <= 317 and 210 <= p[1] <= 230:
                    num = 3
                if 290 <= p[0] <= 320 and 0 <= p[1] <= 30:
                    pygame.quit()
                    quit()

    print(num)

pygame.quit()
quit()
</code>```

答:
Solved!
With the comments from @sloth and @Kingsley I rewrote the code to:

```<code>    r,w,x = select.select([touch], [], [], 0)
    if ( touch in r ):
        for event in touch.read():
            if event.type == evdev.ecodes.EV_ABS:
                if event.code == 1:
                    X = event.value
                elif event.code == 0:
                    Y = event.value
            elif event.type == evdev.ecodes.EV_KEY:
                if event.code == 330 and event.value == 1:
                    printEvent(event)
</code>```

Now the loop runs even if there is no input, and input is working ok also.

Body:
I am using a raspberry pi and PiTFT to measure sensors and display the results.
Using the tips from https://stackoverflow.com/a/54986161 all is working fine but one problem:
the main loop sits waiting until the lcd is touched.
I removed all non trivial parts from the program just to test the main loop.
This piece of code is printing the state after touch and release.
As long as the lcd is being touched the main loop keeps printing, releasing the lcd stops the loop. How can I rewrite the loop so it's keeps running even when there is no keypress?

#!/usr/bin/python3
import subprocess, pygame, evdev, select
pygame.init()
surfaceSize = (320, 240)
##
# Everything that follows is for handling the touchscreen touch events via evdev
##

# Used to map touch event from the screen hardware to the pygame surface pixels.
# (Those values have been found empirically, but I'm working on a simple interactive calibration tool
tftOrig = (3750, 180)
tftEnd = (150, 3750)
tftDelta = (tftEnd [0] - tftOrig [0], tftEnd [1] - tftOrig [1])
tftAbsDelta = (abs(tftEnd [0] - tftOrig [0]), abs(tftEnd [1] - tftOrig [1]))

# We use evdev to read events from our touchscreen
# (The device must exist and be properly installed for this to work)
touch = evdev.InputDevice('/dev/input/touchscreen')

# We make sure the events from the touchscreen will be handled only by this program
# (so the mouse pointer won't move on X when we touch the TFT screen)
touch.grab()
# Prints some info on how evdev sees our input device
print(touch)
# Even more info for curious people
#print(touch.capabilities())

# Here we convert the evdev "hardware" touch coordinates into pygame surface pixel coordinates
def getPixelsFromCoordinates(coords):
    # TODO check divide by 0!
    if tftDelta [0] < 0:
        x = float(tftAbsDelta [0] - coords [0] + tftEnd [0]) / float(tftAbsDelta [0]) * float(surfaceSize [0])
    else:
        x = float(coords [0] - tftOrig [0]) / float(tftAbsDelta [0]) * float(surfaceSize [0])
    if tftDelta [1] < 0:
        y = float(tftAbsDelta [1] - coords [1] + tftEnd [1]) / float(tftAbsDelta [1]) * float(surfaceSize [1])
    else:
        y = float(coords [1] - tftOrig [1]) / float(tftAbsDelta [1]) * float(surfaceSize [1])
    return (int(x), int(y))

# Was useful to see what pieces I would need from the evdev events
def printEvent(event):
    print(evdev.categorize(event))
    print("Value: {0}".format(event.value))
    print("Type: {0}".format(event.type))
    print("Code: {0}".format(event.code))

while True:

    r,w,x = select.select([touch], [], [])
    for event in touch.read():
        if event.type == evdev.ecodes.EV_ABS:
            if event.code == 1:
                X = event.value
            elif event.code == 0:
                Y = event.value
        elif event.type == evdev.ecodes.EV_KEY:
            if event.code == 330 and event.value == 1:
                printEvent(event)
                p = getPixelsFromCoordinates((X, Y))
                if 12 <= p[0] <= 72 and 210 <= p[1] <= 230:
                    num = 0
                if 92 <= p[0] <= 154 and 210 <= p[1] <= 230:
                    num = 1
                if 173 <= p[0] <= 234 and 210 <= p[1] <= 230:
                    num = 2
                if 255 <= p[0] <= 317 and 210 <= p[1] <= 230:
                    num = 3
                if 290 <= p[0] <= 320 and 0 <= p[1] <= 30:
                    pygame.quit()
                    quit()

    print(num)

pygame.quit()
quit()

Body:
Solved!
With the comments from @sloth and @Kingsley I rewrote the code to:

    r,w,x = select.select([touch], [], [], 0)
    if ( touch in r ):
        for event in touch.read():
            if event.type == evdev.ecodes.EV_ABS:
                if event.code == 1:
                    X = event.value
                elif event.code == 0:
                    Y = event.value
            elif event.type == evdev.ecodes.EV_KEY:
                if event.code == 330 and event.value == 1:
                    printEvent(event)

Now the loop runs even if there is no input, and input is working ok also.


问:
How to choose the data structure for finding sentences with only known words
I am working on a language learning program. The program tracks the words it thinks a user knows, and I want to add a feature where I show the user sentences that they should be able to read.

I have a large data set of tokenized sentences, but I'm struggling to come up with the right data structure to make filtering this list fast enough. My first pass was just iterating through it all every time a user learns a new word, but this is proving to be too slow, especially since a user often learns several words during a session. Furthermore, the list of sentences changes over time, either as the user adds sentences of their own or my global database of sentences has sentences added/removed/updated.

What is a good data structure for making this search fast while supporting the dynamic nature of the data? That is, given the set of words the user knows, and a whole ton of tokenized and perhaps further pre-processed sentences, I want to quickly find the sentences that the user should be able to read all of the words in.

答: For this kind of problem, the best way to go is to use a tree. I would recommend you to take a look to Tries or to Radix trees. They allow reducing the search to a logarithmic time.


问:
C# How do I interact with two generated controls?
I have a Checkbox, when marked it will create a Listbox, Button and a Textbox.
The generated Button, should have the Click event to fill the generated Listbox with the value of the generated Textbox.

But I get compile time error inside public System.Windows.Forms.Button AddNewButton() :

The name Txb does nost exist in the current context

The name lb does nost exist in the current context

Here is the code:

```<code> private void cbDd_CheckedChanged(object sender, EventArgs e)
    {
        AddNewListBox();
        AddNewTextBox();
        AddNewButton();
    }

    public System.Windows.Forms.ListBox AddNewListBox()
    {
        System.Windows.Forms.ListBox lb = new System.Windows.Forms.ListBox();
        this.Controls.Add(lb);
        lb.Top = 74;
        lb.Left = 500;
        cLeft = cLeft + 1;
        return lb;
    }

    public System.Windows.Forms.TextBox AddNewTextBox()
    {
        System.Windows.Forms.TextBox txb = new System.Windows.Forms.TextBox();
        this.Controls.Add(txb);
        txb.Top = 180;
        txb.Left = 500;
        txb.Text = "item name";
        cLeft = cLeft + 1;
        return txb;
    }

    public System.Windows.Forms.Button AddNewButton()
    {
        System.Windows.Forms.Button btn = new System.Windows.Forms.Button();
        this.Controls.Add(btn);
        btn.Top = 210;
        btn.Left = 500;
        btn.Text = "Add item";
        btn.Click += (s, e) => { if (string.IsNullOrEmpty(txb.Text)) return;
                };
        lb.Items.Add(cbTxb.Text);
        return btn;
    }
</code>```

答:
In addition to AddNew(ListBox|TextBox|Button)

```<code>public System.Windows.Forms.ListBox AddNewListBox()
{
    return new System.Windows.Forms.ListBox() {
      Location = new Point(500, 74),
      parent   = this, // instead of this.Controls.Add(...)
    };
}

public System.Windows.Forms.TextBox AddNewTextBox()
{
    return new System.Windows.Forms.TextBox() {
      Location = new Point(500, 180),
      Text     = "item name",
      Parent   = this,
    };
}

public System.Windows.Forms.Button AddNewButton()
{
    return new System.Windows.Forms.Button() {
      Location = new Point(500, 210),
      Text     = "Add item",
      Parent   = this,
    };
}
</code>```

I suggest implementing AddNewControls() where created controls can interact with one another:

```<code>private void AddNewControls() {
  var lb  = AddNewListBox();
  var txb = AddNewTextBox();
  var btn = AddNewButton();

  btn.Click += (s, e) => {
    // now btn (button) can use txb (TextBox)
    if (string.IsNullOrEmpty(txb.Text))
      return;

    //TODO: put relevant code here
  }

  cLeft += 3;

  //TODO: check lb and cbTxb.Text
  lb.Items.Add(cbTxb.Text);
}
</code>```

Then you can put

```<code>private void cbDd_CheckedChanged(object sender, EventArgs e)
{
    AddNewControls();
}
</code>```

Body:
I have a Checkbox, when marked it will create a Listbox, Button and a Textbox.
The generated Button, should have the Click event to fill the generated Listbox with the value of the generated Textbox.

But I get compile time error inside public System.Windows.Forms.Button AddNewButton() :

The name Txb does nost exist in the current context

The name lb does nost exist in the current context

Here is the code:

 private void cbDd_CheckedChanged(object sender, EventArgs e)
    {
        AddNewListBox();
        AddNewTextBox();
        AddNewButton();
    }

    public System.Windows.Forms.ListBox AddNewListBox()
    {
        System.Windows.Forms.ListBox lb = new System.Windows.Forms.ListBox();
        this.Controls.Add(lb);
        lb.Top = 74;
        lb.Left = 500;
        cLeft = cLeft + 1;
        return lb;
    }

    public System.Windows.Forms.TextBox AddNewTextBox()
    {
        System.Windows.Forms.TextBox txb = new System.Windows.Forms.TextBox();
        this.Controls.Add(txb);
        txb.Top = 180;
        txb.Left = 500;
        txb.Text = "item name";
        cLeft = cLeft + 1;
        return txb;
    }

    public System.Windows.Forms.Button AddNewButton()
    {
        System.Windows.Forms.Button btn = new System.Windows.Forms.Button();
        this.Controls.Add(btn);
        btn.Top = 210;
        btn.Left = 500;
        btn.Text = "Add item";
        btn.Click += (s, e) => { if (string.IsNullOrEmpty(txb.Text)) return;
                };
        lb.Items.Add(cbTxb.Text);
        return btn;
    }

Body:
In addition to AddNew(ListBox|TextBox|Button)

public System.Windows.Forms.ListBox AddNewListBox()
{
    return new System.Windows.Forms.ListBox() {
      Location = new Point(500, 74),
      parent   = this, // instead of this.Controls.Add(...)
    };
}

public System.Windows.Forms.TextBox AddNewTextBox()
{
    return new System.Windows.Forms.TextBox() {
      Location = new Point(500, 180),
      Text     = "item name",
      Parent   = this,
    };
}

public System.Windows.Forms.Button AddNewButton()
{
    return new System.Windows.Forms.Button() {
      Location = new Point(500, 210),
      Text     = "Add item",
      Parent   = this,
    };
}

I suggest implementing AddNewControls() where created controls can interact with one another:

private void AddNewControls() {
  var lb  = AddNewListBox();
  var txb = AddNewTextBox();
  var btn = AddNewButton();

  btn.Click += (s, e) => {
    // now btn (button) can use txb (TextBox)
    if (string.IsNullOrEmpty(txb.Text))
      return;

    //TODO: put relevant code here
  }

  cLeft += 3;

  //TODO: check lb and cbTxb.Text
  lb.Items.Add(cbTxb.Text);
}

Then you can put

private void cbDd_CheckedChanged(object sender, EventArgs e)
{
    AddNewControls();
}


问:
VLookup between dates, in combination with sumproduct
This is the spreadsheet in question.

I need to calculate the FTEs (employee full time equivalents) for multiple projects, over time. Employees keep changing number of week-hours over time (sheet "Employees Import"), with maximum week hours is 40. So someone working 40 hrs a week has a FTE of 1.

I'm trying to write a formula that would fill the yellow cells. I don't think it's possible to be achieved using array formulas, so I don't have a problem with copying the formula for each week and each project. But the challenge for me is to do the sumproduct operation that includes a VLOOKUP with Employee match AND date between match at the same time. Maybe someone can help out.

EDIT: I've had a bit success with this formula:

```<code>=SUMPRODUCT({K19:K21}; ARRAYFORMULA(VLOOKUP($F19:$F21;    SORT(   FILTER({'Employees Import'!$A$1:$A\'Employees Import'!$E$1:$E\'Employees Import'!$F$1:$F\'Employees Import'!$G$1:$G}; ('Employees Import'!$F$1:$F<K$4)*(  IF('Employees Import'!$G$1:$G=""; DATE(2030;12;31); 'Employees Import'!$G$1:$G)    >K$4) ) )     ; 2)))/40
</code>```

答:
But again you need a unique value, the one which will act like a primary key for sure not the date probably the man number

Below is a simple formula

```<code>VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
</code>```

I hope this will help

Body:
This is the spreadsheet in question.

I need to calculate the FTEs (employee full time equivalents) for multiple projects, over time. Employees keep changing number of week-hours over time (sheet "Employees Import"), with maximum week hours is 40. So someone working 40 hrs a week has a FTE of 1.

I'm trying to write a formula that would fill the yellow cells. I don't think it's possible to be achieved using array formulas, so I don't have a problem with copying the formula for each week and each project. But the challenge for me is to do the sumproduct operation that includes a VLOOKUP with Employee match AND date between match at the same time. Maybe someone can help out.

EDIT: I've had a bit success with this formula:

=SUMPRODUCT({K19:K21}; ARRAYFORMULA(VLOOKUP($F19:$F21;    SORT(   FILTER({'Employees Import'!$A$1:$A\'Employees Import'!$E$1:$E\'Employees Import'!$F$1:$F\'Employees Import'!$G$1:$G}; ('Employees Import'!$F$1:$F<K$4)*(  IF('Employees Import'!$G$1:$G=""; DATE(2030;12;31); 'Employees Import'!$G$1:$G)    >K$4) ) )     ; 2)))/40

Body:
But again you need a unique value, the one which will act like a primary key for sure not the date probably the man number

Below is a simple formula

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

I hope this will help


问:
Print index of row and column in adjacency matrix
I have a function to build adjacency matrix. I want to improve matrix readability for humans, so I decided to print row index like this:

Now I want to print column index in the same way, but I can't do it properly. best result I get is this:

Any Ideas and suggestions how i can print column indexes neatly?

Source code here.

def generate_adjacency_matrix(vertices):
    # Create empty Matrix
    matrix = [['.' for _ in range(len(vertices))] for _ in range(len(vertices))]
    # Fill Matrix
    for row in range(len(matrix)):
        for num in range(len(matrix)):
            if num in vertices[row]:
                matrix[row][num] = '1'
    # Print column numbers
    numbers = list(range(len(matrix)))
    for i in range(len(numbers)):
        numbers[i] = str(numbers[i])
    print('  ', numbers)

    #Print matrix and row numbers
    for i in range(len(matrix)):
        if len(str(i)) == 1:
            print(str(i) + ' ', matrix[i])
        else:
            print(i, matrix[i])
```

<p>If it matters Parameter in my function is a dictionary that looks like:</p>

```{0:[1],
 1:[0,12,8],
 2:[3,8,15]
 ....
 20:[18]
}
```

答:
If you know you're only going to 20, then just pad everything to 2 chars:

For the header row:

```<code>numbers[i] = str(numbers[i].zfill(2))
</code>```

For the other rows, set to ". " or ".1" or something else that looks neat.

That would seem to be the easiest way.

Alternative way is to have 2 column headers, one above the other, first one is the tens value, second is the unit value. That allows you to keep the width of 1 in the table as well, which maybe you need.

Body:
I have a function to build adjacency matrix. I want to improve matrix readability for humans, so I decided to print row index like this:

Now I want to print column index in the same way, but I can't do it properly. best result I get is this:

Any Ideas and suggestions how i can print column indexes neatly?

Source code here.

def generate_adjacency_matrix(vertices):
    # Create empty Matrix
    matrix = [['.' for _ in range(len(vertices))] for _ in range(len(vertices))]
    # Fill Matrix
    for row in range(len(matrix)):
        for num in range(len(matrix)):
            if num in vertices[row]:
                matrix[row][num] = '1'
    # Print column numbers
    numbers = list(range(len(matrix)))
    for i in range(len(numbers)):
        numbers[i] = str(numbers[i])
    print('  ', numbers)

    #Print matrix and row numbers
    for i in range(len(matrix)):
        if len(str(i)) == 1:
            print(str(i) + ' ', matrix[i])
        else:
            print(i, matrix[i])

If it matters Parameter in my function is a dictionary that looks like:

{0:[1],
 1:[0,12,8],
 2:[3,8,15]
 ....
 20:[18]
}

Body:
If you know you're only going to 20, then just pad everything to 2 chars:

For the header row:

numbers[i] = str(numbers[i].zfill(2))

For the other rows, set to ". " or ".1" or something else that looks neat.

That would seem to be the easiest way.

Alternative way is to have 2 column headers, one above the other, first one is the tens value, second is the unit value. That allows you to keep the width of 1 in the table as well, which maybe you need.


问:
How can I set edge jump over another edge?
I'm using cytoscape.js to draw diagram. I want to the edge jump over as picture below. Is it possible?

答: As you can see in this example, cytpscape.js does not support this kind of edge-line. In order to get this feature into cytoscape.js, consider creating a feature request in the official repository.</p>


问:
How to determine unique years within date range?
I'm trying to determine in what years clients make use of healthcare. The data:

```<code>Clientnumber   Date start  Date end
1              01-03-2017  31-10-2017
1              01-02-2018  07-08-2018
1              01-11-2018  01-03-2019
1              25-03-2019  01-07-2020
</code>```

For this one client I want to know in what unique years he/she is registered. Thus, the result should be:
2017, 2018, 2019, 2020 and additonally a count of unique years: 4.

Is there a way to do this in either Excel or R?

Thanks in advance.

答:
One dplyr and purrr option could be:

```<code>df %>%
 group_by(Clientnumber) %>%
 summarise(Years = map_chr(list(c(Date_start, Date_end)),
                           ~ toString(unique(substr(., 7, 10)))))

  Clientnumber Years
         <int> <chr>
1            1 2017, 2018, 2019, 2020
</code>```

If you want also the count, with the addition of stringr:

```<code>df %>%
 group_by(Clientnumber) %>%
 summarise(Years = map_chr(list(c(Date_start, Date_end)),
                           ~ toString(unique(substr(., 7, 10)))),
           n = str_count(Years, ",")+1)

  Clientnumber Years                      n
         <int> <chr>                  <dbl>
1            1 2017, 2018, 2019, 2020     4
</code>```

If the situation is slightly more complicated, meaning you want all years between the first and the last one, even if they are not present in data:

```<code>df %>%
 group_by(Clientnumber) %>%
 summarise(Years = map_chr(list(c(Date_start, Date_end)),
                           ~ toString(reduce(range(as.numeric(substr(., 7, 10))), `:`))),
           n = str_count(Years, ",")+1)
</code>```

Body:
I'm trying to determine in what years clients make use of healthcare. The data:

Clientnumber   Date start  Date end
1              01-03-2017  31-10-2017
1              01-02-2018  07-08-2018
1              01-11-2018  01-03-2019
1              25-03-2019  01-07-2020

For this one client I want to know in what unique years he/she is registered. Thus, the result should be:
2017, 2018, 2019, 2020 and additonally a count of unique years: 4.

Is there a way to do this in either Excel or R?

Thanks in advance.

Body:
One dplyr and purrr option could be:

df %>%
 group_by(Clientnumber) %>%
 summarise(Years = map_chr(list(c(Date_start, Date_end)),
                           ~ toString(unique(substr(., 7, 10)))))

  Clientnumber Years
         <int> <chr>
1            1 2017, 2018, 2019, 2020

If you want also the count, with the addition of stringr:

df %>%
 group_by(Clientnumber) %>%
 summarise(Years = map_chr(list(c(Date_start, Date_end)),
                           ~ toString(unique(substr(., 7, 10)))),
           n = str_count(Years, ",")+1)

  Clientnumber Years                      n
         <int> <chr>                  <dbl>
1            1 2017, 2018, 2019, 2020     4

If the situation is slightly more complicated, meaning you want all years between the first and the last one, even if they are not present in data:

df %>%
 group_by(Clientnumber) %>%
 summarise(Years = map_chr(list(c(Date_start, Date_end)),
                           ~ toString(reduce(range(as.numeric(substr(., 7, 10))), `:`))),
           n = str_count(Years, ",")+1)


问:
Laravel validation rules custom message with second level of array
Here my code :

```<code>$rules = [
        'name' => 'required|string|max:255',
        'price' => 'required|numeric|min:0',
        'unit' => 'required|in:piece,kg,m',
        'price_type' =>'required|string',
        'service' => [
            'string',
            'required',
            Rule::in($services_ids->all()),
        ],
        'facility' => [
            'string',
            'required',
            Rule::in($facilities_ids->all()),
        ],
        'conciergeries' => [
            'array',
            'required',
            Rule::in($conciergeries_ids->all()),
        ],
    ];

    $custom_messages = [
        'required' => 'Vous devez sélectionner un(e) :attribute.'
    ];

    $validated = request()->validate($rules, $custom_messages);
</code>```

The problem is that my custom_messages only works with 'name', 'price', 'unit', 'price_type' but not with 'service', 'facility' and 'conciergeries'.

Questions :

How to apply my custom messages with 'service', 'facility' and 'conciergeries' too ?

How to create a custom message for specifically one field ?

Thank's !

答:
You just need to specify for which field you want to change the message

Try it like:-

```<code>$custom_messages = [
    'service.required' => 'Your custom message for required service',
    'service.string' => 'Your custom message of service should be string',];
</code>```

And same process for facility and conciergeries.

Body:
Here my code :

$rules = [
        'name' => 'required|string|max:255',
        'price' => 'required|numeric|min:0',
        'unit' => 'required|in:piece,kg,m',
        'price_type' =>'required|string',
        'service' => [
            'string',
            'required',
            Rule::in($services_ids->all()),
        ],
        'facility' => [
            'string',
            'required',
            Rule::in($facilities_ids->all()),
        ],
        'conciergeries' => [
            'array',
            'required',
            Rule::in($conciergeries_ids->all()),
        ],
    ];

    $custom_messages = [
        'required' => 'Vous devez sélectionner un(e) :attribute.'
    ];

    $validated = request()->validate($rules, $custom_messages);

The problem is that my custom_messages only works with 'name', 'price', 'unit', 'price_type' but not with 'service', 'facility' and 'conciergeries'.

Questions :

How to apply my custom messages with 'service', 'facility' and 'conciergeries' too ?

How to create a custom message for specifically one field ?

Thank's !

Body:
You just need to specify for which field you want to change the message

Try it like:-

$custom_messages = [
    'service.required' => 'Your custom message for required service',
    'service.string' => 'Your custom message of service should be string',];

And same process for facility and conciergeries.


问:
When to use if (document.all&&document.getElementById) condition
Would like to know when to use, This applies for what browser ?</p>

```<code>if (document.all&&document.getElementById) {
// Some code block
}
</code>```

答:
I don't think there's any significant browser left that doesn't support document.getElementById. Even IE6 supports it (although it gets it wrong — details — but it does the same thing with document.all). I'd just use document.getElementById rather than document.all.

If you're using this just to detect the browser, rather than specifically because you want to use document.all or document.getElementById, browser detection is generally a bad idea. Instead, look at doing feature detection, testing for the actual features you want. There are some great examples of feature detection here and here.

Off-topic: If you're looking to do any significant cross-browser work, you might consider using a library like jQuery, Prototype, YUI, Closure, or any of several others. They'll smooth over browser differences for you, provide some very useful utility functions, and even (in some cases) fix browser bugs like IE's broken handling of document.getElementById (jQuery does that, for instance, some others may as well).

Body:
Would like to know when to use, This applies for what browser ?

if (document.all&&document.getElementById) {
// Some code block
}


问:
Error: setSecret: Unable to log into database with previous, current, or pending secret when configuring credential rotation in AWS
I created a rotation Lambda manually by using MySQL template, after kicking off the rotation, it gave me error:

```<code>[ERROR] ValueError: Unable to log into database with previous, current, or pending secret of secret
</code>```

I found this solution and tried but doesn't work (I'm not very sure about step 2 though, about where to store the file 'latest RDS CA Bundle'), what I did was put in the folder and zip again, uploaded to Lambda.

In addition, I don't know if I'm using the right Lambda template for rotation, what's the difference between 'Single user'and 'Multiple user'? I thought my secret only contain one user and one password so I used the template for one single user, is that correct?

I've spent a very long time on this whole rotation thing, this has already took me way too much time than I expected! Really exhausted... Can anyone help me... Thank you.

答:
Make sure that your RDS and Lambda rotation function are in the same VPC and your RDS instance allows access to your lambda function. What you need is the following to ensure the connectivity between your Rotation lambda and RDS:

Create a security group in the same vpc with no inbound rules and default outbound rules and assign it to the secrets rotation lambda function.

White-list this recently created lambda security group in the inbound of your RDS security group against port 3306.

Body:
I created a rotation Lambda manually by using MySQL template, after kicking off the rotation, it gave me error:

[ERROR] ValueError: Unable to log into database with previous, current, or pending secret of secret

I found this solution and tried but doesn't work (I'm not very sure about step 2 though, about where to store the file 'latest RDS CA Bundle'), what I did was put in the folder and zip again, uploaded to Lambda.

In addition, I don't know if I'm using the right Lambda template for rotation, what's the difference between 'Single user'and 'Multiple user'? I thought my secret only contain one user and one password so I used the template for one single user, is that correct?

I've spent a very long time on this whole rotation thing, this has already took me way too much time than I expected! Really exhausted... Can anyone help me... Thank you.


问:
Extract unique constraint fields from sql exception
Is there any way to extract field names comming from sql exception? In the first example i get Geto in the second: Rest, 1

Example 1:

```<code>Violation of UNIQUE KEY constraint 'UQ__T_Client_NameSector'. Cannot insert duplicate key in object 'dbo.T_Clients'.
The duplicate key value is (Geto).
The statement has been terminated.
</code>```

Example 2:

```<code>Violation of UNIQUE KEY constraint 'UQ__T_Client_NameSector'. Cannot insert duplicate key in object 'dbo.T_Clients'.
The duplicate key value is (Rest, 1).
The statement has been terminated.
</code>```

答:
You can run the query below (from the site) to get all the constraints in your database.  If you need to consume this in your application, you can create a stored proc or a view and call it in your catch block.  I hope this helps.</p>

```<code>select table_view,
object_type,
constraint_type,
constraint_name,
details
from (
    select schema_name(t.schema_id) + '.' + t.[name] as table_view,
        case when t.[type] = 'U' then 'Table'
            when t.[type] = 'V' then 'View'
            end as [object_type],
        case when c.[type] = 'PK' then 'Primary key'
            when c.[type] = 'UQ' then 'Unique constraint'
            when i.[type] = 1 then 'Unique clustered index'
            when i.type = 2 then 'Unique index'
            end as constraint_type,
        isnull(c.[name], i.[name]) as constraint_name,
        substring(column_names, 1, len(column_names)-1) as [details]
    from sys.objects t
        left outer join sys.indexes i
            on t.object_id = i.object_id
        left outer join sys.key_constraints c
            on i.object_id = c.parent_object_id
            and i.index_id = c.unique_index_id
       cross apply (select col.[name] + ', '
                        from sys.index_columns ic
                            inner join sys.columns col
                                on ic.object_id = col.object_id
                                and ic.column_id = col.column_id
                        where ic.object_id = t.object_id
                            and ic.index_id = i.index_id
                                order by col.column_id
                                for xml path ('') ) D (column_names)
    where is_unique = 1
    and t.is_ms_shipped <> 1
    union all
    select schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
        'Table',
        'Foreign key',
        fk.name as fk_constraint_name,
        schema_name(pk_tab.schema_id) + '.' + pk_tab.name
    from sys.foreign_keys fk
        inner join sys.tables fk_tab
            on fk_tab.object_id = fk.parent_object_id
        inner join sys.tables pk_tab
            on pk_tab.object_id = fk.referenced_object_id
        inner join sys.foreign_key_columns fk_cols
            on fk_cols.constraint_object_id = fk.object_id
    union all
    select schema_name(t.schema_id) + '.' + t.[name],
        'Table',
        'Check constraint',
        con.[name] as constraint_name,
        con.[definition]
    from sys.check_constraints con
        left outer join sys.objects t
            on con.parent_object_id = t.object_id
        left outer join sys.all_columns col
            on con.parent_column_id = col.column_id
            and con.parent_object_id = col.object_id
    union all
    select schema_name(t.schema_id) + '.' + t.[name],
        'Table',
        'Default constraint',
        con.[name],
        col.[name] + ' = ' + con.[definition]
    from sys.default_constraints con
        left outer join sys.objects t
            on con.parent_object_id = t.object_id
        left outer join sys.all_columns col
            on con.parent_column_id = col.column_id
            and con.parent_object_id = col.object_id) a
order by table_view, constraint_type, constraint_name
</code>```

Body:
Is there any way to extract field names comming from sql exception? In the first example i get Geto in the second: Rest, 1

Example 1:

Violation of UNIQUE KEY constraint 'UQ__T_Client_NameSector'. Cannot insert duplicate key in object 'dbo.T_Clients'.
The duplicate key value is (Geto).
The statement has been terminated.

Example 2:

Violation of UNIQUE KEY constraint 'UQ__T_Client_NameSector'. Cannot insert duplicate key in object 'dbo.T_Clients'.
The duplicate key value is (Rest, 1).
The statement has been terminated.

Body:
You can run the query below (from the site) to get all the constraints in your database.  If you need to consume this in your application, you can create a stored proc or a view and call it in your catch block.  I hope this helps.

select table_view,
object_type,
constraint_type,
constraint_name,
details
from (
    select schema_name(t.schema_id) + '.' + t.[name] as table_view,
        case when t.[type] = 'U' then 'Table'
            when t.[type] = 'V' then 'View'
            end as [object_type],
        case when c.[type] = 'PK' then 'Primary key'
            when c.[type] = 'UQ' then 'Unique constraint'
            when i.[type] = 1 then 'Unique clustered index'
            when i.type = 2 then 'Unique index'
            end as constraint_type,
        isnull(c.[name], i.[name]) as constraint_name,
        substring(column_names, 1, len(column_names)-1) as [details]
    from sys.objects t
        left outer join sys.indexes i
            on t.object_id = i.object_id
        left outer join sys.key_constraints c
            on i.object_id = c.parent_object_id
            and i.index_id = c.unique_index_id
       cross apply (select col.[name] + ', '
                        from sys.index_columns ic
                            inner join sys.columns col
                                on ic.object_id = col.object_id
                                and ic.column_id = col.column_id
                        where ic.object_id = t.object_id
                            and ic.index_id = i.index_id
                                order by col.column_id
                                for xml path ('') ) D (column_names)
    where is_unique = 1
    and t.is_ms_shipped <> 1
    union all
    select schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
        'Table',
        'Foreign key',
        fk.name as fk_constraint_name,
        schema_name(pk_tab.schema_id) + '.' + pk_tab.name
    from sys.foreign_keys fk
        inner join sys.tables fk_tab
            on fk_tab.object_id = fk.parent_object_id
        inner join sys.tables pk_tab
            on pk_tab.object_id = fk.referenced_object_id
        inner join sys.foreign_key_columns fk_cols
            on fk_cols.constraint_object_id = fk.object_id
    union all
    select schema_name(t.schema_id) + '.' + t.[name],
        'Table',
        'Check constraint',
        con.[name] as constraint_name,
        con.[definition]
    from sys.check_constraints con
        left outer join sys.objects t
            on con.parent_object_id = t.object_id
        left outer join sys.all_columns col
            on con.parent_column_id = col.column_id
            and con.parent_object_id = col.object_id
    union all
    select schema_name(t.schema_id) + '.' + t.[name],
        'Table',
        'Default constraint',
        con.[name],
        col.[name] + ' = ' + con.[definition]
    from sys.default_constraints con
        left outer join sys.objects t
            on con.parent_object_id = t.object_id
        left outer join sys.all_columns col
            on con.parent_column_id = col.column_id
            and con.parent_object_id = col.object_id) a
order by table_view, constraint_type, constraint_name