Converting to PostgreSQL from SQLite

  1. ‹ Older
  2. 3 years ago

    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!

  3. Jean-Yves P

    26 Dec 2016 Pre-Release Testers, Xojo Pro Europe (France, Besançon)
    Edited 3 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.

  4. 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

  5. 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

  6. 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.

  7. 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!

  8. Jean-Yves P

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

    + 1:00 writing and explaining on xojo forum !

  9. 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...

  10. Kem T

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

    @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.

  11. Neil B

    27 Dec 2016 Pre-Release Testers

    @Kem T ...making CamelCase impractical.

    Big tears.. :(

  12. 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.

  13. 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.

  14. 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.

  15. James D

    27 Dec 2016 Pre-Release Testers, Xojo Pro Europe (Switzerland)
    Edited 3 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 ;-)

  16. Neil B

    27 Dec 2016 Pre-Release Testers

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

  17. 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")
  18. Emile S

    6 Jan 2017 Europe (France, Strasbourg)

    So, for PostgreSQL, empty = NULL ?

  19. Jean-Yves P

    6 Jan 2017 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    for sqlite also empty=null
    but sqlite accept any value in a field, so if you put '' in an integer sqlite will accept it, and not postgres
    you will then consider that '' is empty, but it is not null.

  20. Kem T

    6 Jan 2017 Pre-Release Testers, Xojo Pro, XDC Speakers Connecticut

    Right, NULL means "the absence of any value" whereas and empty string is still a string, and thus a value.

    To underscore Jean-Yves' point, SQLite does not do type checking, or even length checking. You can assign any value at all to any column, and extract any column as any type, even if it doesn't make sense. You can define a column as VARCHAR(2), then assign the full text of War and Peace to it. PostgreSQL won't allow any of that.

  21. Neil B

    6 Jan 2017 Pre-Release Testers

    Yes PostgreSQL lets you inserts a text representation of a number but not actual text that can't equal the data type.

    'this works
    dim s as string
    s = "1"
    db.SQLExecute("UPDATE Table SET IntegerColumn = '" + s + "' WHERE ID = 1")
    
    'this causes an error
    s = ""
    db.SQLExecute("UPDATE Table SET IntegerColumn = '" + s + "' WHERE ID = 1")

or Sign Up to reply!