create table

Using a SQLite date/time field with Flutter and Dart

As a brief note, SQLite doesn’t have date/time (datetime) fields, so when you want to use a datetime field with Flutter (and Dart), you have to do something else.

My choice was to store my datetime data as a SQLite INTEGER field. I did this so I can query and sort that field easily myself. (The benefit of storing a datetime field as a TEXT field is that you can read it easier, but I think an INTEGER field is a little easier to work with, though YMMV.)

MySQL syntax examples - create table, primary key, foreign key

Just a quick note here today that if you need some example MySQL database tables, you can use these. I created them for some experiments I ran last night. They show the MySQL create table, primary key, and foreign key syntax:

A MySQL “create table” syntax example

I used to use MySQL every day for years, but over the last two years I haven't used it much. Today I needed to create a MySQL database table, and had to wonder for a few moments what the MySQL CREATE TABLE syntax was. Fortunately I have plenty of examples out here.

Here's a quick example of a MySQL "users" table:

SQLite alter table syntax examples

SQLite FAQ: Can you show me how the SQLite ALTER TABLE syntax works?

At the time of this writing you can use the SQLite ALTER TABLE syntax for two purposes:

  1. Add a column to the end of an existing SQLite database table
  2. Change the name of a database table.

For other changes you'll have to follow some workaround procedures (discussed below).

A MySQL foreign keys “drop table” and re-create table example

MySQL FAQ: How can I drop a series of database tables that have foreign key relationships between them?

If you ever have a problem where you need to drop a series of MySQL database tables that have foreign key relationships between them, the key to doing this is setting the FOREIGN_KEY_CHECKS variable before and after your MySQL DROP TABLE queries.

For example, something like this should work:

A MySQL Vacuum database tables script (auto_increment reset)

MySQL vacuum FAQ: How do I vacuum a MySQL database table, or an entire MySQL database?

This may be an unusual situation, but as I'm creating a new application that uses a MySQL database, I've found that I want a "MySQL vacuum" operation; I want to reset all my MySQL auto_increment counters back to 1 without having to completely drop and then re-create my MySQL database.

Specifically what I'm doing is this:

MySQL drop table and foreign keys tip - the FOREIGN_KEY_CHECKS variable

MySQL “DROP TABLE” FAQ: Help, my MySQL database tables have a lot of foreign keys, and as a result it's a pain to use the MySQL DROP TABLE command in my scripts; they keep failing because of all the foreign keys. Is there something I can do to work around this DROP TABLE foreign keys problem?

How I fixed my JDBC MySQL transaction problem

A JDBC MySQL transactions tip: If your transactions aren't working with your new MySQL database you may have the same problem I just had. I created my database tables with the default MySQL storage engine (MyISAM), and guess what? MyISAM doesn't support transactions.

HSQLDB timestamp - How to specify a default date/time

Here's a quick example of how to set a default value for an HSQLDB TIMESTAMP field:

create cached table directories (
  dir_id identity NOT NULL,
  directory varchar(255) NOT NULL,
  time timestamp default 'now'
);

There are other ways to do this, but the important line that sets the default timestamp in the above SQL is this:

time timestamp default 'now'

This is where I'm creating a timestamp field named "time" that automatically defaults to the current date/time when a record is created.