Converting to PostgreSQL from SQLite

  1. 2 years ago

    Neil B

    26 Dec 2016 Pre-Release Testers

    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. Kem T

    26 Dec 2016 Pre-Release Testers, Xojo Pro, XDC Speakers New York

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

  3. Kem T

    26 Dec 2016 Pre-Release Testers, Xojo Pro, XDC Speakers New York

    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. Neil B

    26 Dec 2016 Pre-Release Testers

    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. Neil B

    26 Dec 2016 Pre-Release Testers

    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. Jean-Yves P

    26 Dec 2016 Pre-Release Testers, Xojo Pro Europe (France, Besançon)
    Edited 2 years ago

    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. Neil B

    27 Dec 2016 Pre-Release Testers

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

    SELECT * FROM Orders GROUP BY Customer

  8. Jean-Yves P

    27 Dec 2016 Pre-Release Testers, Xojo Pro Europe (France, Besançon)
    SELECT * FROM "Orders" GROUP BY "Customer"

    will work in postgres

  9. Neil B

    27 Dec 2016 Pre-Release Testers

    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.

    @Jean-YvesPochez make a method to lowercase all the names in the postgres database.

    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. Neil B

    27 Dec 2016 Pre-Release Testers

    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. Jean-Yves P

    27 Dec 2016 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    + 1:00 writing and explaining on xojo forum !

  12. Neil B

    27 Dec 2016 Pre-Release Testers

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

    Instead of this ;) (no offence)

    @James Dooley No in that case it should be just a case of choosing the correct data types to match the ones you are using...

  13. Kem T

    27 Dec 2016 Pre-Release Testers, Xojo Pro, XDC Speakers New York

    @Neil B I switched to all CamelCase a while back because of a severe hatred that I have for under_scores.

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

  14. Neil B

    27 Dec 2016 Pre-Release Testers

    @Kem T ...making CamelCase impractical.

    Big tears.. :(

  15. Jens K

    27 Dec 2016 Pre-Release Testers, Xojo Pro Europe (Germany)

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

  16. Joost R

    27 Dec 2016 Pre-Release Testers, Xojo Pro The Netherlands

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

  17. Neil B

    27 Dec 2016 Pre-Release Testers

    @Jens K Thanks I will check that out.

    @Joost R If you make use of triggers in SQLite, you'll have to write triggers AND functions for Postgres.

    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. James D

    27 Dec 2016 Pre-Release Testers, Xojo Pro Europe (Switzerland)
    Edited 2 years ago

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

    Instead of this ;) (no offence)

    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 ;-)

  19. Neil B

    27 Dec 2016 Pre-Release Testers

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

  20. Neil B

    6 Jan 2017 Pre-Release Testers

    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")
  21. Newer ›

or Sign Up to reply!