Salient Soft

I just installed the latest 21c version of XE, and having created the system user, I connected to this via SQL developer, using XE as the SID. All fine so far.

When I then went to create a new schema via the CREATE USER command, I received ORA-65096: invalid common user or role name. Upon researching this I found that Oracle now uses a hierarchy of pluggable databases or PDBs inside a container database or CDB. Schemas aka users can then sit inside a PDB as normal. Inside the CDB, where I was logged in, the rules are different and common users are required which have their own rules and naming conventions.

I just wanted a simple local develpment solution for JPA use, so did not especially want to learn all the ins and outs of this. I found that an XE installation creates a default PDB called XEPDB1, and managed to create an SQL developer connection to this using the already created system account. I tried using XEPDB1 as the SID rather than XE, but this failed as invalid. However when I tried XEPDB1 as a service name rather than a SID it all worked fine. I did not need to make any other changes to the connection settings – user (system), password, and port (1521) were identical.

Having then connected via this new connection, I was successfully able to create a new schema in the default PDB. As expected, to connect to this new schema I again had to use the service name of XEPDB1 and I was able to connect, once I had granted the CREATE SESSION privilege to the new user after creating it to allow me to log on. I could then proceed to grant the necessary privileges to the user and create/populate tables etc. Initially, in addition to granting CREATE SESSION, I just simply granted UNLIMITED TABLESPACE to allow the schema user to allow tables to be populated. With this I was successfully able to perform inserts, upates, deletes, and selects on all the tables present.

Comments Off on Oracle XE 21c – Container vs Pluggable Databases – CDBs vs PDBs

I had a legacy EA 12 model for the places guide database which I had used as the basis for the CouchDB implementation.

I wanted to explore a relational database as an alternative route, with the aim of not bothering with completely offline access due to the devlopment effort needed, and the lessening need due to improvements in mobile data access in the last few years. Initially I created an Oracle version of the model file, and tweaked the PK and FK  data types from UUID to long and checked a few other things.

I then tried selecting the domain model in the explorer, and using Package/Database Engineering/Generate Package DDL from the toolbar. This brought up the relevant dialogue to do the export, but try as I might it could not find any entities to export as DDL.

I then found this post here, which points out that you need to transform the relevant entities to DDL first to enable this to work. I did this using Package/Model Transformation (MDA)/Transform Current Package. You then select the entities to transform and select DDL on the right under transformations, and then hit Do Transform. This creates a DDL diagram. The original Generate Package DDL option above then worked – I was able to find all the desired entities and transform them. I had to visit each entity to select the target database DDL format, in my case Oracle, or else I got an error, but once this was done, it all worked fine.

I elected to create a single DDL SQL file and did not worry about the entity ordering – you can reorder when doing the DDL generation but I did not bother. The output DDL was pretty good as a starter and was clear. It also appeared to have been created in a way that was order independent – the tables were all created first, followed by all the foreign key contraints, so that all the tables existed prior to creating the constraints, making it order independent.

Some tweaking of the model will be needed, especially in the area of Place Features related tables, and also sequence generation, as I would likely use Oracle sequences for PK generation, via JPA/Hibernate. However, I had reached a good trial starting point which avoided the need to create all the DDL manually from scratch.

Comments Off on Exporting and Enterprise Architect 12 model as SQL/DDL statements

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