No problem! Note that moor will generate all the query methods in each UseMoor/UseDao class that uses them. So if you define the upsertMessages query in tables.moor, the implementation would be generated in both the dao and the database class. If you want to keep the modularity, you can use multiple moor files:
tables.moorcould contain table and index definitions, it would be included by the main database and each daomessage_queries.moorcontains animport 'tables.moor';at the top and all the queries you need inMessageDao. Then, onlyMessageDaoincludes that specific file.
If you need to you can also split table definitions across multiple moor files. The main database would include all of them, each dao would only get the tables it needs.
Thank you, Simon.
Suppose we have a table (a bit artificial, BTW), which have field(s) which are optional:
CREATE TABLE objects
(
id INT NOT NULL PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
description TEXT,
picture_path TEXT
)then I would like to do something like:
addObject: INSERT INTO objects (name, ?description, ?picture_path) values (:name, :description, :picturePath)Where ? means something I would like to be optional.
Hm, I see. For that instance (inserting with different columns set / not set), it might be easier to just the Dart api. So you could do
into(objects).insert(ObjectsCompanion(...)), in ObjectsCompanion you can choose which columns to set. Companions also have an insert constructor where all fields that you really need (like name for your table) are marked as @required.
Supporting those kinds of queries in moor files isn't an easy thing - we'd have to rewrite sql at runtime, based on which parameters are set. Generating code for that will be tricky I suppose. So for this use case I'd advise to use
insert from the Dart api, it's great for these kinds of slightly-dynamic queries. If there's a query that's still annoying to write we can see how moor could be improved to handle that :)
Hi @simolus3 I just made a PR please check it out:
And thanks for this amazing ORM :)
A quick qn. I want to a batch version of an upsert query (generated code with customInsert). Can I use it like in the doc https://moor.simonbinder.eu/docs/getting-started/writing_queries/
should I use insertAll or replaceAll
Moor doesn't support running a generated insert statement in a batch at the moment. There is simolus3/moor#367 to track upserts from Dart. For now,
insertAll would be the best option.
This is the test:
test('notUploadedStoryImageAssets emits null when all assets are uploaded', () async {
// Arrange
final storyAsset = StoryImageAsset(identifier: 'id1', name: 'name1', originalWidth: 1, originalHeight: 1);
final sut = StoryAssetRepositoryImpl(_database);
final expectation = expectLater(sut.notUploadedStoryImageAssets, emitsInOrder([isNull, isNotNull, isNull]));
// Act
await sut.storeAndQueueForUpload(storyAsset);
await sut.setUploaded(storyAsset.id, true);
// Assert
await expectation;
});
This is how the stream in
notUploadedStoryImageAssets is created:(select(table)
..where((a) => a.isUploaded.equals(false))
..limit(1))
.watchSingle();
The test keeps hanging on the
expect method
If I remove the first
isNull from the emitsInOrder the test doesn’t time out anymore because it detects that it has failed (it says that it expected something not null, bot got null
It looks like the expectLater method keeps listening to the stream for some reason...
You could check what the stream is emitting with
final stream = sut.notUploadedStoryImageAssets.asBroadcastStream();
stream.listen(print);
final expectation = expectLater(stream, ...);Does the stream emit what you expect? That would help to diagnose whether this is something wrong with moor or some stream matcher weirdness.
Actually the
asBroadcastStream might not even be necessary
I added the print statement:
final expectation = expectLater(sut.notUploadedStoryImageAssets, emitsInOrder([isNull, isNotNull, isNull])); //TODO: further specify
sut.notUploadedStoryImageAssets.listen((e) => print('Stream output: $e'));
this is what I get in the logs (look for Stream output as this also contains my SQL queries):
SQL query:
CREATE TABLE IF NOT EXISTS story_image_assets_table (id VARCHAR NOT NULL, is_uploaded INTEGER NOT NULL CHECK (is_uploaded in (0, 1)), identifier VARCHAR unique not null, name VARCHAR NULL, original_width INTEGER NULL, original_height INTEGER NULL, PRIMARY KEY (id));
Arguments:
SQL query:
CREATE TABLE IF NOT EXISTS story_header_table (id VARCHAR NOT NULL, is_uploaded INTEGER NOT NULL CHECK (is_uploaded in (0, 1)), name VARCHAR NULL, PRIMARY KEY (id));
Arguments:
SQL query:
CREATE TABLE IF NOT EXISTS story_content_table (story_header VARCHAR NOT NULL, story_image_asset VARCHAR NOT NULL, PRIMARY KEY (story_header, story_image_asset));
Arguments:
SQL query:
SELECT * FROM story_image_assets_table WHERE is_uploaded = ? LIMIT 1;
Arguments: 0
SQL query:
SELECT * FROM story_image_assets_table WHERE identifier = ?;
Arguments: id1
Stream output: null
SQL query:
INSERT INTO story_image_assets_table (id, is_uploaded, identifier, name, original_width, original_height) VALUES (?, ?, ?, ?, ?, ?)
Arguments: 0dc4f69a-11e9-4dcd-8308-5a12140c1b80, 0, id1, name1, 1, 1
SQL query:
SELECT * FROM story_image_assets_table WHERE is_uploaded = ? LIMIT 1;
Arguments: 0
SQL query:
UPDATE story_image_assets_table SET is_uploaded = ? WHERE id = ?;
Arguments: 1, 0dc4f69a-11e9-4dcd-8308-5a12140c1b80
Stream output: Instance of 'StoryImageAsset'
SQL query:
SELECT * FROM story_image_assets_table WHERE is_uploaded = ? LIMIT 1;
Arguments: 0
Stream output: null
TimeoutException after 0:00:30.000000: Test timed out after 30 seconds.
dart:async _startMicrotaskLoop
✖ notUploadedStoryImageAssets notUploadedStoryImageAssets emits null when all assets are uploaded
Exited (1)
this is the end of the log now:
before expectation
Stream output: Instance of 'StoryImageAsset'
SQL query:
SELECT * FROM story_image_assets_table WHERE is_uploaded = ? LIMIT 1;
Arguments: 0
Stream output: null
after expectation
ERROR: TimeoutException after 0:00:30.000000: Test timed out after 30 seconds.
dart:async _startMicrotaskLoop
sot it looks like it gets past the expect, but then the test method itself keeps hanging?
Is a code snippet missing here? @simolus3
Yes, good point. The snippet is under the group by section, but I'll copy it as an example for
count as well . Thanks for pointing it out :+1:
I have a statement I'm trying to use in moor, but I think moor might be unable to handle it.
SELECT SUM(*) FROM (SELECT COUNT(*) FROM table1 UNION ALL SELECT COUNT(*) from table2)It complains there is no bracket before union. I assume moor can't handle subqueries with unions in them?
for example i have two fields in table FirstName and LastName and lets say for some reason im only getting FirstName from my API, and now i only wanted to save FirstName using fromJson method......But the problem is it will ask for LastName also because its being also returned fromJson method
@eyecreate Good catch! I've fixed this in simolus3/moor@af5333d, this will be released in the next version.
@TheStarkster I think this should work if you make the
LastName column nullable? Then we'd set the field to null, which I think is what you want?
@simolus3 https://github.com/simolus3/moor/blob/master/moor/lib/src/runtime/query_builder/components/join.dart#L65
I think this should say "When set to false" or "When false" or something along those lines.
I think this should say "When set to false" or "When false" or something along those lines.
@simolus3 Hey, I've been trying to use the CountAll function again, I'm trying to count how many incomplete tasks remain for a given day but I haven't gotten it to work.
final filter = tasks.completed.equals(false) & tasks.due.equals(date);
final count = countAll(filter: filter);
final query = selectOnly(tasks)..addColumns([count]);
return query.map((row) => row.read(count)).watchSingle();Am I doing something wrong or could I be on a version of sqlite lower than 3.3? Is there any way to check?
Thanks a ton for this insane set of packages!!
