title,body,link,tags,view_count,is_answered,score,answer_count
Liquibase - Generating Change Logs,"<p>I want Liquibase, to generate a changelog, from this DB 'testing'. Is it possible?</p>

<p>I have an existing database already, with its tables and data inside.</p>

<pre><code>jdbc:mysql://localhost:3306/testing
</code></pre>

<p>Now, I want Liquibase, to generate a changelog, from this DB 'testing'. Is it possible?</p>

<p>This is my command, but it doesn't work.</p>

<pre><code>liquibase --driver=com.mysql.jdbc.Driver --classpath=C:\mysql-connector-java-5.1.47.jar 
--changeLogFile=C:\db.changelog.xml --url=""jdbc:mysql://localhost:3306/testing""
--username=root generateChangeLog
</code></pre>

<p>I don't use any password.</p>

<p>The error is related to --changeLogFile=C:\db.changelog.xml</p>

<p>I thought, Liquibase will refer to my DB 'testing', and generate changelog, with name 'db.changelog.xml' in folder C.</p>

<p>Which part I'm wrong? Do I miss something?</p>

<p>Or maybe, Liquibase is not intended, to generate changelog, from existing DB?</p>

<p>Or maybe, Liquibase is intended, to generate DB, from changelog only? And not vice versa?</p>
",https://stackoverflow.com/questions/58462643/liquibase-generating-change-logs,"['liquibase', 'liquibase-hibernate', 'liquibase-sql']",2840,True,3,1
How to access multiple dataset using dbfit?,"<p>My procedure is returning 3 dataset? For DB testing we are using DBFit. How Can i retrieve all dataset?</p>

<p>Using this to get first dataset</p>

<pre><code>!|Store Query|CALL `USP_dbfit_tes`()|outtable|
------------
!|Query|&lt;&lt;outtable|
</code></pre>

<p>Sample proc</p>

<pre><code>CREATE PROCEDURE `USP_dbfit_tes`()
BEGIN
select 1;
select 2;
select 3;
END
</code></pre>
",https://stackoverflow.com/questions/56356968/how-to-access-multiple-dataset-using-dbfit,"['mysql', 'dbfit']",39,False,0,0
schema owner is not able to create table,"<p>i am new to SQL DW, but have some experience with DB's</p>

<p>I am trying to create users (3) with different rights, but want to restrict them to one schema ""dep"". so i did following.</p>

<p>1) login to DB with super admin id. then ran following commands. all of them worked fine</p>

<pre><code>enter code here---main db
CREATE LOGIN dep_ingestion_owner_login WITH PASSWORD = 'XXXXXXXXXXXXXXX';
CREATE LOGIN dep_ingestion_rw_login WITH PASSWORD = 'XXXXXXXXXXXXXXX';
CREATE LOGIN dep_ingestion_ro_login WITH PASSWORD = 'XXXXXXXXXXXXXXX';
go

---inside db ""db-testing""
CREATE SCHEMA dep;
create user dep_ingestion_owner_user for login dep_ingestion_owner_login;
create user dep_ingestion_rw_user for login dep_ingestion_rw_login;
create user dep_ingestion_ro_user for login dep_ingestion_ro_login;
ALTER AUTHORIZATION ON SCHEMA::dep to dep_ingestion_owner_user;
go

GRANT SELECT,INSERT,UPDATE,DELETE ON SCHEMA :: dep TO dep_ingestion_rw_user WITH GRANT OPTION;
GRANT SELECT ON SCHEMA :: dep TO dep_ingestion_ro_user WITH GRANT OPTION;
go
</code></pre>

<p>I am able to see schema name and users via SSMS.</p>

<p>Now i am trying to login with userid ""<strong>dep_ingestion_owner_login</strong>"" and selected db as => <strong>db-testing</strong></p>

<p>login works perfectly fine. but following command is giving me error.</p>

<pre><code>CREATE TABLE depletions_ke.Sizes (ChainID int, width dec(10,2));
</code></pre>

<p>i tried different permutations too, but getting same error.</p>

<pre><code>CREATE TABLE [db-testing].depletions_ke.Sizes (ChainID int, width dec(10,2));
</code></pre>

<p><strong>Msg 6004, Level 14, State 9, Line 1
User does not have permission to perform this action.
Changed database context to 'db-testing'.</strong></p>
",https://stackoverflow.com/questions/58604040/schema-owner-is-not-able-to-create-table,"['azure', 'azure-sql-database', 'azure-synapse']",1448,True,0,1
"When I set Config database, it is reseting my db connection","<p>I wanna use php unit tests to test an endpoint.</p>

<p>I'm calling an endpoint from my unit test.</p>

<p>Within my endpoint when I say</p>

<pre><code>public function __construct()
{
    \Config::set('database.default', 'pgsql-master');
     print_r(\Config::get('database.connections.pgsql-master.username')); // toters_testing_usr
}
</code></pre>

<p>I believe it's resetting my db connection and my db testing is empty.</p>

<p>because when I say </p>

<pre><code> $env = config('app.env');
    if ($env == 'production')
    {
        \Config::set('database.default', 'pgsql-master');
    }
</code></pre>

<p>it worrks.
Why is this happening?</p>

<p>this is my phpunit.xml</p>

<pre><code>&lt;?xml version=""1.0"" encoding=""UTF-8""?&gt;
&lt;phpunit backupGlobals=""false""
         backupStaticAttributes=""false""
         bootstrap=""bootstrap/autoload.php""
         colors=""true""
         convertErrorsToExceptions=""true""
         convertNoticesToExceptions=""true""
         convertWarningsToExceptions=""true""
         processIsolation=""false""
         stopOnFailure=""false""
         syntaxCheck=""false""&gt;
    &lt;filter&gt;
        &lt;whitelist&gt;
            &lt;directory suffix="".php""&gt;app/&lt;/directory&gt;
        &lt;/whitelist&gt;
    &lt;/filter&gt;
     &lt;listeners&gt;
        &lt;listener class=""JohnKary\PHPUnit\Listener\SpeedTrapListener""&gt;&lt;/listener&gt;
    &lt;/listeners&gt;
    &lt;php&gt;
        &lt;env name=""APP_ENV"" value=""testing""/&gt;
        &lt;env name=""API_STRICT"" value=""false""/&gt;
        &lt;env name=""CACHE_DRIVER"" value=""array""/&gt;
        &lt;env name=""SESSION_DRIVER"" value=""array""/&gt;
        &lt;env name=""QUEUE_DRIVER"" value=""sync""/&gt;
        &lt;env name=""RAVEN_LEVEL"" value=""none""/&gt;
        &lt;env name=""MAIL_DRIVER"" value=""log""/&gt;
        &lt;ini name=""display_errors"" value=""On"" /&gt;
        &lt;ini name=""display_startup_errors"" value=""On"" /&gt;
        &lt;ini name=""memory_limit"" value=""2048M"" /&gt;
    &lt;/php&gt;
&lt;/phpunit&gt;
</code></pre>
",https://stackoverflow.com/questions/54074513/when-i-set-config-database-it-is-reseting-my-db-connection,"['php', 'database', 'laravel', 'phpunit']",58,False,2,0
My docker-compose.yml couldn&#39;t build mysql5.7 container,"<p>I'm trying to build docker container for laravel with docker-compose.yml.</p>

<p>I hove to build database container for mysql5.7.
Mysql8 cannot be used on my server witch connected.</p>

<p>There is my docker-compose.yml file.</p>

<pre><code>version: ""3""
services:
  app:
    build:
      context: ./docker/php
      args:
        - TZ=${TZ}
    ports:
      - ${APP_PORT}:8000
    volumes:
      - ${PROJECT_PATH}:/work
      - ./docker/ash:/etc/profile.d
      - ./docker/php/psysh:/root/.config/psysh
      - ./logs:/var/log/php
      - ./docker/php/php.ini:/usr/local/etc/php/php.ini
    working_dir: /work
    environment:
      - DB_CONNECTION=mysql
      - DB_HOST=db
      - DB_DATABASE=${DB_NAME}
      - DB_USERNAME=${DB_USER}
      - DB_PASSWORD=${DB_PASS}
      - TZ=${TZ}
      - MAIL_HOST=${MAIL_HOST}
      - MAIL_PORT=${MAIL_PORT}
      - CACHE_DRIVER=redis
      - SESSION_DRIVER=redis
      - QUEUE_DRIVER=redis
      - REDIS_HOST=redis

  web:
    image: nginx:1.17-alpine
    depends_on:
      - app
    ports:
      - ${WEB_PORT}:80
    volumes:
      - ${PROJECT_PATH}:/work
      - ./logs:/var/log/nginx
      - ./docker/nginx/default.conf:/etc/nginx/conf.d/default.conf
    environment:
      - TZ=${TZ}

  db:
    image: mysql:5.7
    volumes:
      - db-store:/var/lib/mysql
      - ./logs:/var/log/mysql
      - ./docker/mysql/my.cnf:/etc/mysql/conf.d/my.cnf
    environment:
      - MYSQL_DATABASE=${DB_NAME}
      - MYSQL_USER=${DB_USER}
      - MYSQL_PASSWORD=${DB_PASS}
      - MYSQL_ROOT_PASSWORD=${DB_PASS}
      - TZ=${TZ}
    ports:
      - ${DB_PORT}:3306

  db-testing:
    image: mysql:5.7
    volumes:
      - ./docker/mysql/my.cnf:/etc/mysql/conf.d/my.cnf
    tmpfs:
      - /var/lib/mysql
      - /var/log/mysql
    environment:
      - MYSQL_DATABASE=${DB_NAME}
      - MYSQL_USER=${DB_USER}
      - MYSQL_PASSWORD=${DB_PASS}
      - MYSQL_ROOT_PASSWORD=${DB_PASS}
      - TZ=${TZ}
    ports:
      - ${DB_TESTING_PORT}:3306

  node:
    image: node:12.13-alpine
    tty: true
    volumes:
      - ${PROJECT_PATH}:/work
    working_dir: /work

  redis:
    image: redis:5.0-alpine
    volumes:
      - redis-store:/data

  mail:
    image: mailhog/mailhog
    ports:
      - ${MAILHOG_PORT}:8025

volumes:
  db-store:
  redis-store:
</code></pre>

<p>When I execute ""docker-compose build"" in terminal, it's successfully done, but db container and db-testing container has status ""EXIT: 1"" or ""EXIT: 2"".
So, Could you teach me what's wrong.</p>
",https://stackoverflow.com/questions/59331673/my-docker-compose-yml-couldnt-build-mysql5-7-container,"['docker', 'docker-compose', 'yaml', 'mysql-5.7']",631,False,1,0
Spring Boot testing with H2 - Table &quot;OAUTH_ACCESS_TOKEN&quot; not found,"<p>I'm thinking I need to setup the db testing environment (e.g. create tables, seed users so that token can be issued with credentials) before I can run tests but not sure how to. </p>

<pre><code>@RunWith(SpringRunner.class)
@WebAppConfiguration
@SpringBootTest(classes = Application.class)
public class UsersControllerTest {

    // ...

    private MockMvc mockMvc;

    @Before
    public void setup() {
        this.mockMvc = MockMvcBuilders.webAppContextSetup(this.wac)
          .addFilter(springSecurityFilterChain).build();
    }

    private String obtainAccessToken(String username, String password) throws Exception {

        MultiValueMap&lt;String, String&gt; params = new LinkedMultiValueMap&lt;&gt;();
        params.add(""client_id"", CLIENTID);
        params.add(""grant_type"", CLIENTPASSWORD);
        params.add(""username"", username);
        params.add(""password"", password);

        ResultActions result = mockMvc.perform(post(""/oauth/token"")
            .params(params)
            .with(httpBasic(CLIENTID, CLIENTPASSWORD))
            .accept(""application/json;charset=UTF-8""))
            .andExpect(status().isOk())
            .andExpect(content().contentType(""application/json;charset=UTF-8""));

        String resultString = result.andReturn().getResponse().getContentAsString();

        JacksonJsonParser jsonParser = new JacksonJsonParser();
        return jsonParser.parseMap(resultString).get(""access_token"").toString();
    }

    @Test
    public void givenNoToken_whenGetAllUsers_thenUnauthorized() throws Exception {

        mockMvc.perform(
            get(""/users"")
            ).andExpect(status().isUnauthorized());
    }

    @Test
    public void givenToken_whenGetAllUsers_thenOk() throws Exception {
        String accessToken = obtainAccessToken(""martyn"", ""secret"");
        mockMvc.perform(
            get(""/users"")
                .header(""Authorization"", ""Bearer "" + accessToken)
            ).andExpect(status().isOk());
    }

    // ...
</code></pre>

<p>Here is a typical Entity for this app:</p>

<pre><code>@Entity(name = ""users"")
public class User implements Serializable {

    /**
     *
     */
    private static final long serialVersionUID = -8507204786382662588L;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(nullable = false)
    private String firstName;

    @Column(nullable = false)
    private String surname;

    @Column(nullable = false, unique = true)
    private String email;

    @Column(nullable = false, unique = true)
    private String username;

    @Column(nullable = false)
    @JsonIgnore
    private String password;

    @OneToMany
    @JoinColumn(name=""user_id"") // cascade = CascadeType.ALL, orphanRemoval = true
    private List&lt;Fund&gt; funds;

    public Long getId() {
        return id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    // standard getters and setters

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getSurname() {
        return surname;
    }

    public void setSurname(String surname) {
        this.surname = surname;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public List&lt;Fund&gt; getFunds() {
        return funds;
    }
}
</code></pre>

<p>But also, as the error indicates, I'd need to generate these oauth* tables too.</p>

<p>Here is my src/test/resources/application.properties</p>

<pre><code>spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:db;DB_CLOSE_DELAY=-1
spring.datasource.username=sa
spring.datasource.password=sa
</code></pre>

<p>So I guess I want to generate the tables (entities, and oauth*) in the H2 database prior to running tests and populate with a single user(?) but can't seem to figure how this is done in Spring Boot. Or should I not be hitting any database and mocking JDBC altogether? Could someone point me in the correct direction as to how to prepare a test environment here? I'm at a bit of a loss.</p>

<p>UPDATE </p>

<p>Here is how dataSource is configured:</p>

<pre><code>@Configuration
public class JDBCTokenConfig {

    @Value(""${spring.datasource.url}"")
    private String datasourceUrl;

    @Value(""${spring.datasource.username}"")
    private String dbUsername;

    @Value(""${spring.datasource.password}"")
    private String dbPassword;

    @Bean
    public DataSource dataSource() {
        final DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUrl(datasourceUrl);
        dataSource.setUsername(dbUsername);
        dataSource.setPassword(dbPassword);
        return dataSource;
    }

    @Bean
    public TokenStore tokenStore(DataSource dataSource) {
        return new JdbcTokenStore(dataSource);
    }

    // @Bean
    // public TokenStore tokenStore() {
    //     return new InMemoryTokenStore();
    // }
}
</code></pre>

<p>pom.xml</p>

<pre><code>&lt;dependencies&gt;
    ...
    &lt;dependency&gt;
        &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
        &lt;artifactId&gt;spring-boot-starter-data-jpa&lt;/artifactId&gt;
    &lt;/dependency&gt;
    &lt;dependency&gt;
        &lt;groupId&gt;mysql&lt;/groupId&gt;
        &lt;artifactId&gt;mysql-connector-java&lt;/artifactId&gt;
        &lt;scope&gt;runtime&lt;/scope&gt;
    &lt;/dependency&gt;
    &lt;dependency&gt;
        &lt;groupId&gt;com.h2database&lt;/groupId&gt;
        &lt;artifactId&gt;h2&lt;/artifactId&gt;
        &lt;scope&gt;test&lt;/scope&gt;
    &lt;/dependency&gt;
    &lt;dependency&gt;
        &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
        &lt;artifactId&gt;spring-boot-starter-test&lt;/artifactId&gt;
        &lt;scope&gt;test&lt;/scope&gt;
    &lt;/dependency&gt;
    ...
&lt;/dependencies&gt;
</code></pre>
",https://stackoverflow.com/questions/59535862/spring-boot-testing-with-h2-table-oauth-access-token-not-found,"['spring-boot', 'junit', 'h2']",1549,False,0,1
org.postgresql.util.PSQLException - 2019,"<p>I am trying to automate my API &amp; DB testing(postgresql) via Cucumber(Java).
How ever i have written my code and trying to execute below queries:</p>

<pre class=""lang-java prettyprint-override""><code>resultSet=statement.executeQuery(""select * from rules_data.postcode_leadtime where postcode = ""+postcodedistrict.toUpperCase()+""  and order_type = ""+ordertype.toUpperCase()+"""");
</code></pre>

<p>Now, if i run the direct query i.e. - </p>

<pre class=""lang-sql prettyprint-override""><code>select * 
from rules_data.postcode_leadtime 
where postcode = 'MK9'  
  and order_type = 'ADSI' 
</code></pre>

<p>it runs perfectly fine but when I run the above query with parameters it gives the below error:</p>

<blockquote>
  <p>org.postgresql.util.PSQLException: ERROR: column ""mk9"" does not exist</p>
</blockquote>

<p>I googled this up, found out that it has to be in Uppercase when executing the command, so then i made sure that its being converted into uppercase as you can see in the parameter in the above query, BUT STILL I AM GETTING THE SAME ISSUE. Happening for my other commands too.</p>

<p>Note: I have tried printing the values of parameters, they are coming in Upper cases.</p>

<p>Another issue i am getting is with the below query:</p>

<pre class=""lang-java prettyprint-override""><code>resultSet=statement.executeQuery(""select day from rules_data.hub_workdays where hub_id = "" + hub + "" and available = 'N'"");
</code></pre>

<p>Over here hub is an integer value, it is coming fine on printing but when executing via Java gives an error below:</p>

<blockquote>
  <p>org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = integer</p>
</blockquote>

<p>But when i run with value it works fine.</p>

<pre class=""lang-sql prettyprint-override""><code>select day 
from rules_data.hub_workdays 
where hub_id = '593' 
and available = 'N'
</code></pre>

<p>Already mentioned in my first explanation.</p>
",https://stackoverflow.com/questions/57277924/org-postgresql-util-psqlexception-2019,"['java', 'postgresql', 'jdbc', 'resultset']",292,True,-1,1
Nodejs Express for web aplication JWT with database or sessions to authenticate users,"<p>I'm developing a NodeJs with Express server and I have many problems with the JWT authentication that is suposed to be the new standard.</p>

<p>The main problem is when I have an admin users manager section in my app. I don't know a ""good"" way to delete or avoid client tokens access.</p>

<p>I read about doing a blacklist DB with tokens with a short expiration time defined, but the idea is to allow users to be logged for months and don't have to rewrite the login data (something like facebook). In this mode I think that the blacklist DB will be a huge database.</p>

<p>Another option could be to keep all tokens in a DB and verify all requests against that DB deleting tokens when an user is deleted.</p>

<p>or simply validate all requests against users DB testing if the users exists. This method uses lots of DB interactions.</p>

<p>I could have a problem about the same user whith tokens in different devices.</p>

<p>On the other side, in the frontend I will have to navigate via ajax or adding the token dynamically to all href links. (Probably a better way?)</p>

<p>Must I use any of thes methods? Are there a better solution? Could be better the use of sessions? </p>
",https://stackoverflow.com/questions/54445273/nodejs-express-for-web-aplication-jwt-with-database-or-sessions-to-authenticate,"['node.js', 'express', 'session', 'jwt']",78,True,2,2
How to load/retrieve In Memory Database (H2 Database) using karate Framework as a prerequisite step before Microservice REST API is called,"<p>Problem Statement :</p>
<ol>
<li>Microservices retrieves data from DB2 Database as part of Production</li>
<li>Different Set of data feasible in Different Environment</li>
<li>As part of it, it needs to ensure Retrieval logic is accurate</li>
<li>java Code Converts DB2 Data into JSON structure</li>
<li>Microservices logic doesnt control creating Database,its only limited to retrieval</li>
<li>As part of above requirement , In memory DB (H2)  is being discussed for Testing where data is loaded using DDL/DML and Expected JSON response to be generated manually as part of unit Testing</li>
</ol>
<p>Test Validation Objective : test &quot;Retrial Logic&quot; so that irrespective of Environment, same values are retrieved .When Microservices is invoked, internally Test Harness URL calls to In Memory Database to retrieve Actual response</p>
<p>Test Automation Framework in Place :</p>
<ol>
<li>Karate Framework</li>
</ol>
<p>Test Automation Approach:</p>
<ol>
<li>Load data into in Memory Database using DDL/DML (file) as part of Test Execution</li>
<li>Invoke Microservice Request using karate</li>
<li>Compare Actual Response against Expected JSON response captured in Step</li>
<li>Delete the Database as part of Cleanup</li>
</ol>
<p>Note - As it would be very tedious to write same  Test Automation logic which helps to convert Data into DB ( In Memory ) to JSON (Expected Response), Expected JSON response to be captured as base expected response</p>
<p>Its Expected to have Test Automation Maintenance to Update DDL/DML or expected JSON is there is any change in actual Retrieval Logic.</p>
<p>Refernce for In memory DB Testing :</p>
<p><a href=""https://www.baeldung.com/spring-jpa-test-in-memory-database"" rel=""nofollow noreferrer"">https://www.baeldung.com/spring-jpa-test-in-memory-database</a></p>
<p>#xyz-service-response.json is generated as part of unit testing while In memory DB is Created
@TestRetrivalLogicusingInMemoryRequirnments
Feature: demo reading files and using in a test</p>
<p>Background:</p>
<pre><code>* url 'http://localhost:8080/api/sample/v1.0/'
    * header Accept = 'application/json'
    * configure logPrettyResponse = true
    * configure ssl = true 
</code></pre>
<p>Scenario: using json as a string
Load data into In Memory Database ----&gt;&gt; This is expected Gherkin Statement. Need to understand how we can do in Karate</p>
<pre><code>Given path 'getDerivedRules'
    And request { &quot;pricingVerIds&quot;: [{ &quot;AncestorVersionId&quot;: 123456123, &quot;kidsVersionIds&quot;: [3432432,345324324] }],&quot;executionFlow&quot;: &quot;Cheque&quot;,&quot;PriceType&quot;: &quot;dollarinINYIELD&quot;, &quot;validationLevel&quot;: &quot;Aggrement_LEVEL&quot;}
    When  method POST 
    Then status 200 
    Then match $ == read('xyz-service-response.json')
</code></pre>
<p>Challenge and Query  :</p>
<p>As karate Gherkin statement doesn't have Java Glue Code , and it has own DSL, can this be achieved using Karate or we need to stick to RESTAssured or similar Framework</p>
<p>Request for Help :</p>
<p>Any suggestion or code snippet to handle above would be helpful</p>
",https://stackoverflow.com/questions/56498777/how-to-load-retrieve-in-memory-database-h2-database-using-karate-framework-as,"['microservices', 'karate', 'in-memory-database']",956,False,-1,1
