You are right John.
But SQLite does not enforce NOT NULL (not the Column Types, etc.).
Ah, right. I’ve been using a lot of different SQL implementations in the last year, though it does look like I’ll be settling on SQLite soon. Time to brush up on the details!
SQLite has been my goto for a long time now. I do do some cubeSQL (which is the same as SQLite). I try to avoid MySQL due to licensing issues so I have been able to minimize its use. PostgreSQL is the non-SQLite db that I am starting to use more and more.
After two hours of intense reflexion, I may have to remove these NOT NULL: if one day a migration (out of SQLite) may start troubles.
And your opinion is ?
[quote=307513:@Emile Schwarz]
But SQLite does not enforce NOT NULL (not the Column Types, etc.).[/quote]
Yes it does
If you define a table & set a column constraint to be NOT NULL inserting a null will cause a constraint violation
If you know the date is stored as YYYYMMDD then you should use LIKE ‘2016%’
The version above with % on both sides means ‘2016 occurs anywhere in the string’
I appreciate I am stretching the point, but if the year was 2012 and someone stored the date as DDMMYYYY , this would pick up dates of 20122017
So, I never tried to insert a NULL.
Sorry for the mistake.
YOU ARE RIGHT !
Edit: it cannot be otherwise, but today. What will it be tomorrow ?
This is subject to errors (a door open to an error).
In the w3schools web page they explain correctly / Ive made a mistake (here too). I will change that asap (before I forgot)…
for most of my application, i want the abilities to change from single to multiple user without any changes, that is why i am using SQLite and CubeSQL.
Does anyone know if PostgreSQL can do the same??
[quote=307564:@Emile Schwarz]So, I never tried to insert a NULL.
Sorry for the mistake.[/quote]
i think i never ever set any columns to NOT NULL.
i do “INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE” for primary key and “BOOLEAN NOT NULL DEFAULT 0” for boolean column
PostgreSQL is a multi user dbase like Oracle/MySQL/SQL Server. There is no “single user mode”. Migrating from SQLite to PostgreSQL should be fairly easy. BUT SQLite is a lot more lax in its SQL & operations than the others do (including PostgreSQL). If you are using an abstraction layer or ORM like ActiveRecord, the switch is fairly smooth. SQLite will allow you to place any data into any data type (data types are more of a strong suggestion over restrictions). So you will need to make sure the data and such is valid with PostgreSQL.
I just switched to PostgreSQL. I don’t think it would be very easy to make an app that use either a local SQLite or PostgreSQL server database. The reason is that there are too many differences and the database object is a different type. Some of these issues could be overcome, but I would say a project of any size or complexity would be difficult to make truly universal. You would need to change the connection information and the database types before compiling. I’m not aware of any way to change a global object type at runtime. Here are some details from my recent switch. Switch to PostgreSQL
you can make it (relatively) easy to switch between the two. Define your database as type “database”, then you can instance it as either one. In your SQL code you just need a switch to either build/make/use the SQLite version or PostgreSQL version at run time. Using an ORM can make it even easier. The connection part will be dependent on which database type you connect to. But the rest of the code is generic enough to not need to which one you are connected to. Now you can write code with AR that makes it very DB specific. Any of us can paint ourselves in a corner if we so wish.
I use a wrapper class that implements all the necessary methods/properties and passes them through to the appropriate db type, translating as necessary. Bury all the differences under a unified api.