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!
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.
I think for the most part I have everything working with the new database. Here's how it went.
Total time to convert project 15:15. Whew that was a long day!
@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.
@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 )
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 ;-)
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.
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")