Converting to PostgreSQL from SQLite

Databases
#1

This a continuation from this topic .
I decided to start a new topic with a title a little more relevant to the discussion.

Here is the summary I had posted with several of the conversion steps that were needed in my case.

  • Convert database - 3 hours to write conversion utility.
  • Change all prepared statements to use $1,$2 instead of ?,?.
  • changed CURRENT_TIMESTAMP to ‘now()’
  • changed some problems with data types ie. Round aggregate doesn’t work with type real.
  • multi-table queries need more of the fields added to the ORDER BY clause
  • PostgreSQL enforces strict data types. SQLite made me sloppy so I had to do some cleanup.
  • Switched to BigSerial type instead of AUTOINCREMENT.
  • Does not increment from max(id) so sequence value must be edited after converting database.
  • PostgreSQL does not allow using single quotes or back ticks around table names. Double quotes or nothing.
#2

Another difference. Unless you use double quotes, all identifiers are in lowercase no matter how you type them.

#3

Having said that, don’t use identifiers that require double quotes unless you hate your life and are looking for ways to make it worse.

#4

I also noticed that I had to use exact case for the database name. I have a db ‘Main’, but specifying ‘main’ in the connection will result in an error no such database.

#5

For some reason I must have thought it was fun to surround table or column names with single quotes in some cases. Now there is now way to even search for that. Yeah… try to search for ’ when that is the default comment character!

#6

make a method to lowercase all the names in the postgres database.
if you have foreign keys, you must run it 2 times . you will get an error at the first run.

#7

For some reason I thought this was proper SQL. It actually works with SQLite.

SELECT * FROM Orders GROUP BY Customer

#8 
SELECT * FROM "Orders" GROUP BY "Customer"

will work in postgres

#9

For me it doesn’t. It doesn’t allow returning any fields not used in an aggregate (sum, avg, count, etc.) I guess SQLite returns the first value in this case.

06:18:32 Kernel error: ERROR: column “orders.id” must appear in the GROUP BY clause or be used in an aggregate function.

My conversion program removed all quotes and back ticks from schema. This resulted in all lowercase identifiers. Fortunately as Kem mentioned, I didn’t hate my life enough to use identifiers with spaces. I switched to all CamelCase a while back because of a severe hatred that I have for under_scores.

#10

I think for the most part I have everything working with the new database. Here’s how it went.

  • 3:00 - Write database conversion program and convert to new PostgreSQL database
  • 2:00 - Convert my main server program
  • 1:45 - Convert my client program.
  • 8:30 - Test every single thing my program does and fix things that broke. Hopefully I didn’t miss too many.

Total time to convert project 15:15. Whew that was a long day!

#11
  • 1:00 writing and explaining on xojo forum !
#12

Yeah. It would have been nice to have something like this to read before I started.

Instead of this :wink: (no offence)

#13

Snake_case is popular in postgres because it lowercases everything, making CamelCase impractical.

#14

Big tears… :frowning:

#15

For me the book " I Wish I Knew How to…Program PostgreSQL with Xojo Desktop" from Eugene Dakin was very helpful.

#16

If you make use of triggers in SQLite, you’ll have to write triggers AND functions for Postgres.

#17

@Jens Knaack Thanks I will check that out.

That was one of the next things I wanted to try out. I have a status field in my orders table that I use to tell my server to import or modify the order in QuickBooks. I would like to replace that with a trigger and a notification.

I know I can set up a notification to occur it the table changes but I haven’t checked to see if I can trigger when a field becomes a certain value. Although querying every time the table changes would be better than my current method of querying at every timer interval.

#18

[quote=305682:@Neil Burkholder]Yeah. It would have been nice to have something like this to read before I started.

Instead of this :wink: (no offence)[/quote]

Hi Man, It’s Christmas and the one thing I learned from the Swiss is that you don’t on Christmas!

My base class for modelling data access is called: TModelBase (I’m an old TPW programmer what can I say…) Anyways it has a method called GetTableName:

	Dim tableName As String = OnGetTableName
	
	If tableName.Trim.Len = 0 Then
			Dim ti As Introspection.TypeInfo = Introspection.GetType( Self )
			
			Dim rex As New RegEx
			rex.SearchPattern = "([a-z])([A-Z])"
			rex.ReplacementPattern = "$1_$2"
			rex.Options.CaseSensitive = true
			rex.Options.ReplaceAllMatches = true
			
			tableName = rex.Replace( ti.Name ).Lowercase
			
			If tableName.BeginsWith( "t" ) Then
					tableName = tableName.ChopLeft( 1 )
			End If
	End If
	
	Return tableName.Lowercase

Which raises an event to allow you to provide any table name you want. Otherwise it converts your camelCase TModelBase into model_base for use with PostgreSQL.

Now all we need is someone cooler than me to optimise it and then we can steel it back :wink:

#19

Hey that’s pretty neat. I never used RegEx. I’ll have to try it out.

#20

Another thing that needed to be replaced.

This works in SQLite but is bad practice I guess:

db.SQLExecute("UPDATE Table SET IntegerColum = '' WHERE ID = 1")

With PostgreSQL I had to do this instead:

db.SQLExecute("UPDATE Table SET IntegerColum = NULL WHERE ID = 1")