search

andpor / react-native-sqlite-storage

Full featured SQLite3 Native Plugin for React Native (Android and iOS)
MIT License
2.76k stars 519 forks source link

What version is Sqlite using and should I be able to Upsert? #321

Open aidanw opened 5 years ago

aidanw commented 5 years ago

I am creating an app and I would like to be able to use the upsert syntax https://sqlite.org/lang_UPSERT.html

The UPSERT syntax was added to SQLite with version 3.24.0 (2018-06-04).

When I run my code (detailed below) I get a syntax error.

Is it possible for me to update (for android and ios) the SQLite version? or am I making some other mistake?

Expected Behavior

I am running this code

 return this.getDatabase()
            .then(db =>
                db.executeSql(
                    `Insert into Field (
                        FarmId,
                        "Field-Unique-Id",
                        Name,
                        Coordinates,
                        Soil,
                        Crop,
                        "Previous-Crop",
                        "Soil-Test-P",
                        "Soil-Test-K",
                        "Regular-Manure",
                        "Recent-Grass",
                        Size) values(?,?,?,?,?,?,?,?,?,?,?,?)
                     ON CONFLICT("Field-Unique-Id")
                     Do
                     UPDATE SET
                     FarmId = excluded.FarmId,
                     Name = excluded.Name,
                     Coordinates = excluded.Coordinates,
                     Soil = excluded.Soil,
                     Crop = excluded.Crop,
                     "Previous-Crop" = excluded."Previous-Crop",
                     "Soil-Test-P" = excluded."Soil-Test-P",
                     "Soil-Test-K" = excluded."Soil-Test-K",
                     "Regular-Manure" = excluded."Regular-Manure",
                     "Recent-Grass" = excluded."Recent-Grass",
                     Size = excluded.Size`,

                    [
                        field.farmKey,
                        field.key,
                        field.name,
                        JSON.stringify(field.fieldCoordinates),
                        field.soilType,
                        field.cropType,
                        field.prevCropType,
                        field.soilTestP,
                        field.soilTestK,
                        field.organicManure,
                        field.recentGrass,
                        field.area
                    ]
                )
            )

and I wish to insert my object!

Current Behavior

The error I get is

[Info] 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Possible Unhandled Promise Rejection (id: 0): 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Object { 02-04 13:34:30.590 6722 6762 W ReactNativeJS: "code": 0, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: "message": "near \"ON\": syntax error (code 1): , while compiling: Insert into Field ( 02-04 13:34:30.590 6722 6762 W ReactNativeJS: FarmId, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: \"Field-Unique-Id\", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Name, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Coordinates, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Soil, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Crop, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: \"Previous-Crop\", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: \"Soil-Test-P\", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: \"Soil-Test-K\", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: \"Regular-Manure\", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: \"Recent-Grass\", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Size) values(?,?,?,?,?,?,?,?,?,?,?,?) 02-04 13:34:30.590 6722 6762 W ReactNativeJS: ON CONFLICT(\"Field-Unique-Id\") 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Do 02-04 13:34:30.590 6722 6762 W ReactNativeJS: UPDATE SET 02-04 13:34:30.590 6722 6762 W ReactNativeJS: FarmId = excluded.FarmId, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Name = excluded.Name, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Coordinates = excluded.Coordinates, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Soil = excluded.Soil, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Crop = excluded.Crop, 02-04 13:34:30.590 6722 6762 W ReactNativeJS: \"Previous-Crop\" = excluded.\"Previous-Crop\", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: \"Soil-Test-P\" = excluded.\"Soil-Test-P\", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: \"Soil-Test-K\" = excluded.\"Soil-Test-K\", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: \"Regular-Manure\" = excluded.\"Regular-Manure\", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: \"Recent-Grass\" = excluded.\"Recent-Grass\", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: Size = excluded.Size", 02-04 13:34:30.590 6722 6762 W ReactNativeJS: }

Context

I expect I can reproduce the Upsert behaviour in multiple hits on the database, but it is a nice syntax and I would like to use it.

Your Environment

"dependencies": {
    "@expo/vector-icons": "^9.0.0",
    "@types/react-native-sqlite-storage": "^3.3.1",
    "global": "4.3.2",
    "mobx": "4.5.0",
    "mobx-react": "5.2.8",
    "moment": "^2.24.0",
    "native-base": "^2.11.0",
    "react": "16.6.1",
    "react-native": "0.57.7",
    "react-native-datepicker": "1.7.2",
    "react-native-maps": "https://github.com/react-community/react-native-maps.git",
    "react-native-simple-store": "1.3.0",
    "react-native-sqlite-storage": "^3.3.9",
    "react-navigation": "2.14.0"
  },

Debug logs

See above!

creambyemute commented 5 years ago

For android it's listed here: https://github.com/andpor/react-native-sqlite-storage/tree/master/src/android-native/libs

It's using 3.8.10.2 (updated 3 years ago) at the moment

aidanw commented 5 years ago

Oh. that is old!

I guess that upgrading is more than dropping in new files from www.sqlite.org nightlies!

If someone will hold my hand I would consider attempting a pull request, but I have a deadline first so not for a few weeks on my side.

If others find this in some hopless google search in the future my current work around looks like this horror


  return this.getDatabase()
            .then(db =>
                db.executeSql(
                    `Insert or Ignore Into Field (
                        FarmId,
                        "Field-Unique-Id",
                        Name,
                        Coordinates,
                        Soil,
                        Crop,
                        "Previous-Crop",
                        "Soil-Test-P",
                        "Soil-Test-K",
                        "Regular-Manure",
                        "Recent-Grass",
                        Size) values(?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12);
                        UPDATE Field SET
                        FarmId = ?1,
                        Name = ?3,
                        Coordinates= ?4,
                        Soil= ?5,
                        Crop= ?6,
                        "Previous-Crop"= ?7,
                        "Soil-Test-P"= ?8,
                        "Soil-Test-K"= ?9,
                        "Regular-Manure"= ?10,
                        "Recent-Grass"= ?11,
                        Size= ?12
                        where changes() = 0 and "Field-Unique-Id" = ?2;
                        `,

                    [
                        field.farmKey,
                        field.key,
                        field.name,
                        JSON.stringify(field.fieldCoordinates),
                        field.soilType,
                        field.cropType,
                        field.prevCropType,
                        field.soilTestP,
                        field.soilTestK,
                        field.organicManure,
                        field.recentGrass,
                        field.area
                    ]
                )
            )
jdanbrown commented 5 years ago

Here's a manual workaround to upgrade sqlite:

andpor commented 5 years ago

I will upgrade Android native libs to recent version shortly...armeabi will be eliminated though...

andpor commented 5 years ago

Can you test new-native-sqlite-libs branch (new native libs) and let me know how it works for you. This library does not provide custom sqlite libs and relies on what's on the device.

reactdevrocks commented 5 years ago

ON CONFLICT("Field-Unique-Id") Do UPDATE SET

working fine on ios but giving error on android

aweffr commented 5 years ago

changes

your work around is awesome! Now you saved me! ;)

Shan7anu commented 4 years ago

@aidanw Your workaround horror is awesome, working flawlessly! :heart_eyes: