Salient Soft

I was using Spring Data JPA with Oracle as part of an export process from CouchDB to Oracle. The CouchDB export application first exports my existing Places data from CouchDB, then maps it to the entity model I am using for JPA/Oracle, then persists it into Oracle, and finally performs some rudimentary listing of the Oracle data by logging it.

Some issues/points came to light during this excercise which I am noting here:-

1/ My connect string to Oracle was using a Pluggable database in Oracle XE. This needs to use a service name (xepdb1) rather than a SID (xe) for the connection. To do this I needed to use a slash before the service name in the application properties, to indicate a service name, rather than the colon I would normally use for a SID:

# Oracle settings
spring.datasource.url = jdbc:oracle:thin:@localhost:1521/xepdb1
spring.datasource.username = places
spring.datasource.password = xxxxxxxx

2/ I changed the Hibernate naming strategy in the application properties to prevent unwanted snake casing of Oracle column names:

# Hibernate settings
spring.jpa.hibernate.naming.physical-strategy = org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

3/ For persisting to Oracle, I had the choice of using Spring Data JPA, calling save() or saveAll() on the Individual Repository interfaces that extended CrudRepository (such as PlacesRepository), or I could use the Entity Manager. The Spring Data methods would persist or update fine, and could also save an iterable in one call. However, I had some small additional logic as I was passing and returning a Map. As I chose not to put this logic in the service layer, I therefore used an ordinary JPA Dao (repository) class, and autowired the entity manager via the @PersistenceContext annotation. I could therefore incorporate my logic in a single call and use entityManager.merge() to persist. I felt that situations vary and that it was not always appropriate to use Spring Data Interfaces for some use cases. However the Spring Data find queries were useful and clever for querying the database as they allowed for example a find by key with a like, and order by, just by declaring the appropriate method calls in the interface. I also used the @Query annotation for a custom query, which again was just annotated on a method in the interface.

4/ I had issues whereby the foreign keys on dependant entities were not being auto populated and threw up as null, even when I set nullable=false on the @JoinColumn annotations. In the end it was a simple case of not populating the objects in my model on both sides of the join before persisting – i.e. I was populating a list in a parent object for a one-to-many, but not populating the parent in the dependant object. Doing this resolved the problem and everything persisted automatically with a single parent persist/merge, with hibernate allocating primary keys via Oracle sequences, and then auto populating them in the foreign keys. This stack overflow post here pointed me in the right direction on this. I kept the nullable=false annotations where appropriate to correctly match the relationship I was using, and what was in Oracle.

5/ Once I was at the point of listing everything I had persisted, using Spring Data queries and some simple logging, I was initially hit with Hibernate multiple bag fetch exceptions when listing the data. At this stage I had annotated my repository methods as @Transactional rather than the service layer, and was therefore using explicit eager loading annotations as I needed everything to load before the methods returned. This post here details the issue, and it appears that Hibernate can only eagerly load one collection at a time. My solution to this was to take the more normal route of annotating my service methods as @Transactional rather than the repository, which then allowed a number of repository calls to be made from a single service method, all in the same transaction. Whilst this did expose the service layer to the transaction architecture and javax.transaction package, it is the more normal approach and gives flexibility in the service layer. In my case I could then just revert to the default lazy loading and perform the logging in the same service layer method (which I was happy to do as this was a very basic example just to demonstrate fetching the imported data from Oracle). Everything then worked fine.

Comments Off on Using Spring Data JPA with Oracle

Having got CouchDB working under Windows 11 with the legacy places data as per this post here, I then wanted to run the legacy places angular and ionic apps if possible, just for reference and to consider my options going forward.

The angular app was in v6.1.7 which I did not want to revisit and upgrade. Similarly, the ionic app was an earlier legacy version which could be tricky to reinstate under windows 11.

However, I did have already built distributions for both apps, and both were able to run successfully under Windows 11 via http-server without having to install anything else. For angular I used the existing build under the dist subdirectory, and for Ionic I the build is under the www subdirectory. Note that whilst the index.html for the Ionic version did specify that cordova.js was required, and caused a load error in the web console, this was not an issue when using from a browser as it was not needed.

I did want to be able to run them remotely as well, from other PCs and mobiles. I tried installing the distributions under Zen hosting, with remote access to CouchDB running on the local PC. However, this would not run under HTTPS as it meant having mixed content – the access to CouchDB was not HTTPS and I did not want to go to the trouble of installing a self signed cert locally to get it all to work – this excercise was not worth the effort. I could not find an easy way under Zen cpanel of allowing just this app to be HTTP only, with everything else defaulting to HTTPS. If I turned off forced defaulting to HTTPS in cpanel, the app worked fine under HTTP but other access was also allowed to be HTTP only which I did not like, so I dumped the idea of hosting under Zen directly. I did hit a spate of nasty looking cpanel issues when I did this and for a while thought I had broken the domain/ssl/cpanel access entirely whilst messing with domain/alias/redirect settings in cpanel, but in the end it all worked fine again.

I therefore continued to run/host locally via http-server. I did want to be able to potentially remote boot my local PC via Fritz and access the app from anywhere, which would mean auto running the http-servers at boot time. The easiest way to do this turned out to be to use the windows task scheduler, which unlike services can run batch files without any other tools such as srvany which is commonly used to do this for services. Whilst the scheduler is often used with time based triggers, it is perfectly possible to specify a trigger as ‘run at boot time’. I also specified that it should run without any user logged in, therefore with local access only/no user authentication. This worked fine and I could boot the PC and then immediately access the places apps from a mobile without logging in.

Another trick needed was to add some remote port sharing in the Fritz box for both apps, via my static IP addresses. When doing this I also had to add a port share for the CouchDB access, otherwise CouchDB also complained about mixing private CORS stuff with the remote access. Once I did this, and changed the app config to use the remote URL for CouchDB access, it all worked fine. Fortunately, I could also continue to access the same apps locally on the hosting PC even though they were still using the remote URL for CouchDB access – it all worked fine.

I then set up redirects under cpanel from Zen to make the app access look a bit more friendly with salient soft urls, and this all ran fine.

Comments Off on Running Legacy Places Guide under Windows 11 for reference

Update 9/5/23

I reinstalled the current latest version, V3.3.2, to try to solve an issue where I could not connect to Fauxton (In fact this was my mistake – I was effectively using 127.0.0.1:5984 rather than the actual IP address i.e. nodename:5984, having forgotton I had changed the binding in local.ini).

After reinstalling, I edited local.ini and used my previous trick below to add a plaintext password and let couchDB encode it. However, couchDB seemed to ignore anything I placed in local.ini, and instead was using a file etc\local.d\10-admins.ini. When I editied this latter file, I could add the password correctly and it was honored (then encoded by CouchDB and replaced in the file).

When reinstalling, I had simply uninstalled the old version via control panel, then installed the new one on top without removing any other existing files (in particular I left the existing database in place). It appears that the installer took my old custom settings and placed them in the above 10-admins.ini file.

I have left this as it is at present but may change this later. This page here gives a good intro to the configuration files, their precedence order and how to override stuff. This section on authentication mentions the 10-admins.ini file which it says is present if you intall from a package like I did.

Original Post

I had a zip backup of a complete old version with data, but needed to locate the old release to reinstall on a new PC and reinstate the data

MSIs of old releases can be found in the apache archive here 

If you look under the releases subfolder in the old installation there is a RELEASES text file which details the release information. Mine was release 2.0.0.

The latest release at the time of posting is 3.2.2, but I was not clear that the data structures would be forwards compatible if I just restored all the previous data I had (this is one way that backup is proposed, as opposed to replicating to another server which I didn’t want to do).

On issue was that I had temporarily forgotten the admin password. This post here details how to change an admin password manually if you forget – you can just change the hashed password against an admin user with a plaintext one, and CouchDB will detect this and swap it for the hashed version when it is next restarted.

After installing v2.0.0, it would not fully start on Windows 11 – the service paused during startup. It did however run on an older Windows 10 pc, and also ran correctly when I restored all the data.

I then tried the latest 3.2.2 version on the Windows 11 pc, and again restored the data. I had a couple of issues – one was an incorrect server name in the app config url which prevented access to the database. A second issue peculiar to V3.2.2 was that initially I got errors when trying to access the database from the app with no authentication (this simple legacy Proof of Concept was not authenticated). I then found in the user settings in V3 that by default there were admin roles specified which were preventing simple access with no authentication. Once I removed these, this solved the problem and access from the application worked correctly without authentication.

This then worked fine – there were no compatibility issues with V2.0.0 data when restored under v3.2.2.

Comments Off on Reinstalling Apache CouchDB

So far when emitting multiple docs I have used arrayed keys containing ordinals to clearly define the order, as per this post here, where the key was complex and already arrayed.

In another example, I was just searching a view on a string ID and returning a couple of docs (via _include_docs), so I had the option of returning the same key for each doc, which is supported. This would allow me to search the view by key – if an arrayed key is used, then a range of start and end key is needed per the trick in the above post, otherwise not all the docs are returned.

In the end I stuck with the original arrayed pattern. Either way would need only a single pass through the B-Tree index. In addition, this recent bug report here seems to indicate problems in returned results when duplicate keys are present, so it looks like duplicate keys are best avoided. The arrayed pattern also gives deterministic order of documents in the results.

Comments Off on CouchDB view – duplicate keys vs arrayed keys

I had a bug whereby my view was emitting the same place document twice. This was strange as the database clearly did not contain 2 copies of the document. I had seen what appeared to be a similar issue before bud at the time did not track down the cause.

It turned out to be a bug in the view definition. The original (buggy) version follows:-

function (doc) {
  if (doc.type == ‘place’) {
      emit([doc.address.postTown, doc.address.locality, doc.name, doc._id, 0], null);
      emit([doc.address.postTown, doc.address.locality, doc.name, doc._id, 1], {_id: doc.placeFeaturesId});
  }
}

The problem with this is that when doc.placeFeaturesId is null/undefined, the second emit emits its key plus an “_id” with an undefined/null value in the generated json. The result of this is that when using ?include_docs=true to include the documents, CouchDB applies a default behaviour due to the null, and emits the place document again a second time. The following fix prevents the second emit when there are no placeFeatures present, which prevents the place document being emitted a second time. This solves the problem:-

function (doc) {
  if (doc.type == ‘place’) {
      emit([doc.address.postTown, doc.address.locality, doc.name, doc._id, 0], null);
      if (doc.placeFeaturesId) {
        emit([doc.address.postTown, doc.address.locality, doc.name, doc._id, 1], {_id: doc.placeFeaturesId});
      }
  }
}

The lesson here is to be careful to handle undefined or null values in the view code when emitting entries in a view definition as this can cause strange behaviour such as the duplicates above.

No Comments »

In my case the log had grown to nearly 1GB, and there does not appear to be any log rotation set up by default!

It turns out that this will occur if you have not configured the node after installation – either for single node operation, or as a cluster.

This can easily be done via the configuration option in Fauxton, and at the same time it allows you to set credentials for the admin account.

Doing this prevented the log spamming and log growth was then minimal.

This post here discusses the issue.

No Comments »

I was unable to access a couchDb from another computer on the local network.

There were 2 issues that needed resolving to address this:-

1/ By default, CouchDb binds to 127.0.0.1:5984, and therefore is only accessible if you use this address (i.e. the loopback adapter) explicitly. Other adapters, such as the lan adapter which has the local ip address, will not work, so you can’t enter the server machine’s own IP address as this won’t work. To fix this, edit local.ini in <couchdb home dir>/etc, and bind to 0.0.0.0 instead of 127.0.0.1, as  follows:-

[chttpd]
port = 5984
; Options for the MochiWeb HTTP server.
;server_options = [{backlog, 128}, {acceptor_pool_size, 16}]
; For more socket options, consult Erlang’s module ‘inet’ man page.
;socket_options = [{recbuf, 262144}, {sndbuf, 262144}, {nodelay, true}]
bind_address = 0.0.0.0

Note the use of the [chttpd] section in the ini file, not [httpd]

Editing local.ini will override settings in default.ini, and these settings will also be preserved across upgrades, whereas edits to default.ini will not.

This is described in this post here. Note that you can also edit configuration parameters in fauxton in the configuration section. Clicking on the value of a parameter will expose an edit box to allow changes.

Once changes have been made you need to bounce the service.

2/ My other issue was to do with Kaspersky firewall settings. To ensure that you can access a machine from elsewhere on the lan, make sure your network type for the lan adapter is set to trusted and not public. Public will force you to enter explicit rules for every address/application/port etc. that you use, which is a complete pain on a lan. Trusted just allows all machines on the lan to interoperate freely. This is detailed here under how to change the network status.

No Comments »

There is no specific feature for doing this in Fauxton – you can upload attachments but not specifically delete them.

You can delete the attachment programmatically by issuing an HTTP DELETE to the attachment’s URL

This post here states that removing the stub reference to the attachment in “_attachments” in the document actually does the same as deleting programmatically as above.

In both cases, the stub is deleted, and the actual attachment is marked for delete. It will only actually be deleted when the database is compacted. However, the important point is that both methods apparently do the same thing.

Therefore, in Fauxton, you can just edit the JSON for the document to remove the attachment stub, and save the change.

No Comments »

It is useful to enable CORS when using CouchDB for development, as this avoids the need for workarounds such as installing e.g. the CORS plugin in Chrome to bypass CORS issues.

As CORS is enabled/allowed on the Server Side, this needs to be enabled in CouchDB. This post details approaches to this. The easiest way (which is mentioned in the post comments) is just to do it in Fauxton. Just pick configuration on the left, and select CORS. You can enable for all domains (simplest for a closed development environment) or create a list. This github commit here can also do it but appears long winded compared to just doing it in Fauxton for simple cases.

No Comments »

Here is an example of a range query. The keys are arrayed, of the form

[postTown, locality, name, id, 0] for the place document, and:-

[postTown, locality, name, id, 1] for the immediately following placeFeatures document.

(Assuming ?include_docs=true is used to include the documents)

http://localhost:5984/places-guide/_design/places/_view/placesByLocationName?include_docs=true&start_key=["Norwich","Neatishead"]&end_key=["Norwich","Ranworth",{}]

Here is one with the same start and end keys:-

http://localhost:5984/places-guide/_design/places/_view/placesByLocationName?include_docs=true&start_key=["Norwich","Neatishead"]&end_key=["Norwich","Neatishead",{}]

Note that in this case, we have had to append the null object as the next element in the key, or the query would return nothing at all. This works because the null object comes last in the collation sequence, i.e. after all possible values for the name element of the key, so this allows all places for Neatishead to be included.

The opposite of this is “”, the empty string (or a null), both of which come first in the collation sequence.

The trick is documented in this Stack Overflow post here

No Comments »