java – Why my data saved in SQLite cannot be displayed on other Activity?

What I was trying to do here is to display the data in SQLite on my Profile page. But I can’t seem to display the data saved to the Profile page. I don’t know what’s the problem here, there are no errors in my codes and the application can be run perfectly. I just can’t display the data in the profile page. Can you all help me with this problem?

This is my MainActivity.Java

public class MainActivity extends AppCompatActivity {


private DatabaseHelper dbHelper;
private ImageView pImageView;
private EditText Pemail, Pname, PAge, Pphone, Ppassword;
private TextView loginacclink, Ppreferenceselected;
Button createacc, preferencebtn;
ActionBar actionBar;
String() categories;
boolean() checkeditems;
ArrayList<Integer> mUserItems = new ArrayList<>();
private static final int CAMERA_REQUEST_CODE = 100;
private static final int STORAGE_REQUEST_CODE = 101;
private static final int IMAGE_PICK_CAMERA_CODE = 102;
private static final int IMAGE_PICK_GALLERY_CODE = 103;
private String() cameraPermissions;
private String() storagePermissions;
private Uri imageUri;
private String name, age, phone;
private String preferenceselected;



@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    // this is the status bar
    actionBar = getSupportActionBar();
    actionBar.setTitle("Create Account");
    // this is for the check list
    categories = getResources().getStringArray(R.array.user_categories);
    checkeditems = new boolean(categories.length);
    // this is for the items in the xml file
    pImageView =  findViewById(R.id.personImage);
    Pemail = findViewById(R.id.email);
    Pname = findViewById(R.id.name);
    PAge = findViewById(R.id.age);
    Pphone = findViewById(R.id.phone);
    Ppassword = findViewById(R.id.password);
    createacc = findViewById(R.id.createacc);
    preferencebtn = findViewById(R.id.preferencebtn);
    loginacclink = findViewById(R.id.loginacclink);
    Ppreferenceselected = (TextView) findViewById(R.id.preferenceselected);
    dbHelper = new DatabaseHelper(this);

    cameraPermissions = new String(){Manifest.permission.CAMERA, Manifest.permission.WRITE_EXTERNAL_STORAGE};
    storagePermissions = new String(){Manifest.permission.WRITE_EXTERNAL_STORAGE};


    pImageView.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            imagePickDialog();

        }
    });

    preferencebtn.setOnClickListener(new View.OnClickListener(){
        @Override
        public void onClick (View view){
            AlertDialog.Builder mBuilder = new AlertDialog.Builder(MainActivity.this);
            mBuilder.setTitle(R.string.dialog_title);
            mBuilder.setMultiChoiceItems(categories, checkeditems, new DialogInterface.OnMultiChoiceClickListener() {
                @Override
                public void onClick(DialogInterface dialogInterface, int position, boolean isChecked) {
                    if (isChecked) {
                        if (!mUserItems.contains(position)) {
                            mUserItems.add(position);
                        }
                    } else if (mUserItems.contains(position)) {
                        mUserItems.remove(position);
                    }
                }

            });

            mBuilder.setCancelable(false);
            mBuilder.setPositiveButton(R.string.ok_label, new DialogInterface.OnClickListener() {
                @Override
                public void onClick(DialogInterface dialogInterface, int which) {
                    String item = "";
                    for (int i = 0; i < mUserItems.size(); i++) {
                        item = item + categories(mUserItems.get(i));
                        if (i != mUserItems.size() - 1) {
                            item = item + ",";
                        }
                    }
                    Ppreferenceselected.setText(item);
                }
            });

            mBuilder.setNegativeButton(R.string.dismiss_label, new DialogInterface.OnClickListener() {
                @Override
                public void onClick(DialogInterface dialogInterface, int i) {
                    dialogInterface.dismiss();
                }
            });
            mBuilder.setNeutralButton(R.string.clear_all_label, new DialogInterface.OnClickListener() {
                @Override
                public void onClick(DialogInterface dialogInterface, int which) {
                    for (int i = 0; i < checkeditems.length; i++) {
                        checkeditems(i) = false;
                        mUserItems.clear();
                        Ppreferenceselected.setText("");
                    }
                }
            });
            AlertDialog mDialog = mBuilder.create();
            mDialog.show();

        }
    });


    loginacclink.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View view) {
            Intent intent = new Intent(MainActivity.this, login.class);
            startActivity(intent);        }
    });



    createacc.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            String emailString = Pemail.getText().toString();
            String nameString = Pname.getText().toString();
            String ageString = PAge.getText().toString();
            String phoneString = Pphone.getText().toString();
            String preferencesString = Ppreferenceselected.getText().toString();
            String passwordString = Ppassword.getText().toString();

            boolean insertData = dbHelper.AddData(emailString, nameString,ageString, phoneString,preferencesString,passwordString);

            if (insertData == true) {
                Toast.makeText(MainActivity.this, "Account Created", Toast.LENGTH_LONG).show();
                Intent goappsetting = new Intent(MainActivity.this,profile.class);
                startActivity(goappsetting);

            }else {
                Toast.makeText(MainActivity.this, "Please do not leave any blanks or user exist", Toast.LENGTH_LONG).show();
                getData();
            }


        }

    });
}

private void getData() {

    name = "" + Pname.getText().toString().trim();
    age = "" + PAge.getText().toString().trim();
    phone = "" + Pphone.getText().toString().trim();
    preferenceselected = "" + Ppreferenceselected.getText().toString().trim();

    String timeStamp = "" + System.currentTimeMillis();

    long id = dbHelper.insertInfo(
            "" + imageUri,
            "" + name,
            "" + age,
            "" + phone,
           "" + preferenceselected,
            "" + timeStamp,
            "" + timeStamp
    );
}



private void imagePickDialog() {

    String() options = {"Camera", "Gallery"};

    AlertDialog.Builder builder = new AlertDialog.Builder(this);
    builder.setTitle("Pick Image From");

    builder.setItems(options, new DialogInterface.OnClickListener() {
        @Override
        public void onClick(DialogInterface dialog, int which) {

            if (which == 0) {
                if (!checkCameraPermission()) {
                    requestCameraPermission();
                }
                else {
                    pickFromCamera();
                }
            }
            else if (which == 1) {
                if (!checkStoragePermission()) {
                    requestStoragePermission();
                }
                else {
                    pickFromGallery();
                }
            }

        }
    });

    builder.create().show();
}

private boolean checkCameraPermission() {

    boolean result = ContextCompat.checkSelfPermission(this, Manifest.permission.CAMERA)
            == (PackageManager.PERMISSION_GRANTED);

    boolean result1 = ContextCompat.checkSelfPermission(this, Manifest.permission.WRITE_EXTERNAL_STORAGE)
            == (PackageManager.PERMISSION_GRANTED);

    return result && result1;
}

private void requestCameraPermission() {

    ActivityCompat.requestPermissions(this, cameraPermissions, CAMERA_REQUEST_CODE);
}
private void pickFromGallery() {

    Intent galleryIntent = new Intent(Intent.ACTION_PICK);
    galleryIntent.setType("image/*");
    startActivityForResult(galleryIntent, IMAGE_PICK_GALLERY_CODE);
}

private void pickFromCamera() {

    ContentValues values = new ContentValues();
    values.put(MediaStore.Images.Media.TITLE, "Image title");
    values.put(MediaStore.Images.Media.DESCRIPTION, "Image description");

    imageUri = getContentResolver().insert(MediaStore.Images.Media.EXTERNAL_CONTENT_URI, values);

    Intent cameraIntent = new Intent(MediaStore.ACTION_IMAGE_CAPTURE);
    cameraIntent.putExtra(MediaStore.EXTRA_OUTPUT, imageUri);
    startActivityForResult(cameraIntent, IMAGE_PICK_CAMERA_CODE);

}

private boolean checkStoragePermission() {

    boolean result = ContextCompat.checkSelfPermission(this, Manifest.permission.WRITE_EXTERNAL_STORAGE)
            == (PackageManager.PERMISSION_GRANTED);

    return result;
}

private void requestStoragePermission() {

    ActivityCompat.requestPermissions(this, storagePermissions, STORAGE_REQUEST_CODE);
}


@Override
public void onRequestPermissionsResult(int requestCode, @NonNull String() permissions, @NonNull int() grantResults) {
    super.onRequestPermissionsResult(requestCode, permissions, grantResults);

    switch (requestCode) {

        case CAMERA_REQUEST_CODE: {

            if (grantResults.length>0) {

                boolean cameraAccepted = grantResults(0) == PackageManager.PERMISSION_GRANTED;
                boolean storageAccepted = grantResults(1) == PackageManager.PERMISSION_GRANTED;

                if (cameraAccepted && storageAccepted) {
                    pickFromCamera();
                }

                else {
                    Toast.makeText(this, "Camera permission required!", Toast.LENGTH_SHORT).show();
                }

            }

        }
        break;
        case STORAGE_REQUEST_CODE:{

            if (grantResults.length>0) {

                boolean storageAccepted = grantResults(0) == PackageManager.PERMISSION_GRANTED;

                if (storageAccepted) {
                    pickFromGallery();
                }
                else {
                    Toast.makeText(this, "Storage permission required!", Toast.LENGTH_SHORT).show();
                }

            }
        }
        break;
    }
}

@Override
protected void onActivityResult(int requestCode, int resultCode, @Nullable Intent data) {

    if (resultCode == RESULT_OK) {

        if (requestCode == IMAGE_PICK_GALLERY_CODE) {

            CropImage.activity(data.getData())
                    .setGuidelines(CropImageView.Guidelines.ON)
                    .setAspectRatio(1, 1)
                    .start(this);
        }

        else if (requestCode == IMAGE_PICK_CAMERA_CODE) {

            CropImage.activity(imageUri)
                    .setGuidelines(CropImageView.Guidelines.ON)
                    .setAspectRatio(1, 1)
                    .start(this);
        }

        else if (requestCode == CropImage.CROP_IMAGE_ACTIVITY_REQUEST_CODE) {

            CropImage.ActivityResult result = CropImage.getActivityResult(data);

            if (resultCode == RESULT_OK) {
                Uri resultUri = result.getUri();
                imageUri = resultUri;
                pImageView.setImageURI(resultUri);
            }

            else if (resultCode == CropImage.CROP_IMAGE_ACTIVITY_RESULT_ERROR_CODE) {

                Exception error = result.getError();
                Toast.makeText(this, ""+error, Toast.LENGTH_SHORT).show();
            }
        }
    }

    super.onActivityResult(requestCode, resultCode, data);
}


}

This is my DatabaseHelper.Java

public class DatabaseHelper extends SQLiteOpenHelper {

    public DatabaseHelper(@Nullable Context context) {
        super(context, constants.DB_NAME, null, constants.DB_VERSION);
    }


    @Override
    public void onCreate(SQLiteDatabase db) {

        db.execSQL(constants.CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    public long insertInfo(String image, String name, String age, String phone, String preference, String addTimeStamp, String updateTimeStamp) {

        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(C_NAME, name);
        values.put(constants.C_AGE, age);
        values.put(C_PHONE, phone);
        values.put(C_PREFERENCES, preference);
        values.put(C_IMAGE, image);
        values.put(constants.C_Add_TIMESTAMP, addTimeStamp);
        values.put(constants.C_UPDATED_TIMESTAMP, updateTimeStamp);

        long id = db.insert(TABLE_NAME, null, values);
        db.close();
        return id;
    }


    public boolean AddData(String email,  String name,String age, String phone, String preferences,String password) {
        SQLiteDatabase dbHelper = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(C_EMAIL, email);
        contentValues.put(C_NAME, name);
        contentValues.put(C_AGE, age);
        contentValues.put(C_PHONE, phone);
        contentValues.put(C_PREFERENCES, preferences);
        contentValues.put(C_PASSWORD, password);




        long result = dbHelper.insert(TABLE_NAME,null, contentValues);

        if (result == -1) {
            return false;
        }else {
            return true;
        }

    }


    public boolean checkUser(String email, String password){
        String () columns = {C_EMAIL,C_PASSWORD};
        SQLiteDatabase db = getReadableDatabase();
        String selection = C_EMAIL + "=?" + " and " + C_PASSWORD + "=?"  ;
        String selectionArgs ()= { email, password };
        Cursor cursor = db.query(TABLE_NAME,columns,selection,selectionArgs,null,null,null);
        int count = cursor.getCount();
        cursor.close();
        db.close();

        if (count > 0)
            return true;
        else
            return false;


    }

    public Cursor viewData(){
        SQLiteDatabase dbHelper = this.getReadableDatabase();
        Cursor cursor = dbHelper.rawQuery("select * from "+ TABLE_NAME,null);
        return cursor;
    }


}

This is my profile.Java

public class profile extends AppCompatActivity {
DatabaseHelper dbHelper;
TextView pemail, pname, pAge, phoneno, ppreferences, ppassword;
ImageView profilepic;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_profile);

    dbHelper = new DatabaseHelper(this);
    pemail= findViewById(R.id.pemail);
    pname = findViewById(R.id.pname);
    pAge = findViewById(R.id.pAge);
    phoneno = findViewById(R.id.phoneno);
    ppreferences = findViewById(R.id.ppreferences);
    ppassword = findViewById(R.id.ppassword);
    profilepic = findViewById(R.id.profilepic);

    Cursor cursor =dbHelper.viewData();
    StringBuilder stringBuilder = new StringBuilder();

    while (cursor.moveToNext()){
        stringBuilder.append(""+cursor.getString(0)
                + ""+ cursor.getString(1)
                + ""+ cursor.getString(2)
                + ""+ cursor.getString(3)
                + ""+ cursor.getString(4)
                + ""+ cursor.getString(5)
                + ""+ cursor.getString(6));

    }
    return;

}
}

android sqlite – Connecting to existing database in Kotlin

I try to use precreated database for my android app written in Kotlin, so in my databaseHelper class I tried to use the following code for that:

    override fun onCreate(db: SQLiteDatabase)
    {
        db = SQLiteDatabase.openDatabase(DATABASE_PATH + DATABASE_NAME,null,SQLiteDatabase.OPEN_READWRITE)

    }

I thought this will worm, but I get the message val cannot be reassigned.

Then in the next methods, which are supposed to perform actions on database (execute all needed queries) I reffer to the database like that:

        val db = this.writableDatabase
        val cursor = db.rawQuery(query, null)

(or just with db.execSQL(query) i don’t need the results of selection; of course query is a string with SQL query)

The point is how can I access the precreated database because when I would just create new “global” variable (in companion object?) then I would have to reffer to it in diffent way… at least I think so… well maybe using the line val db = this.writableDatabase also in onCreate would do, but wouldn’t it connect just “localy”(as I overrwrite the refference for db in parameter by local variable) instead of putting this precreated database into what is returned by this.writableDatabase?

design – Designing a sqlite component in C++

I work on a small component in an embedded device (sensor). This component :

  • Every 5 seconds, sends requests to other components using sockets
    (get health check status, operating status etc) and stores the
    response in memory.
  • Creates a socket, listens for incoming requests from UI. And sends the stored stats.

To keep the design simple, component is single threaded and asynchronous. I use boost asio, timers. Some components take a while to respond but the async calls (socket create/listen/connect/send/receive) make sure none of the operations block the main thread. This has been working really well.

I am working on a new task:

  • Talk to a new component(every 5 secs) and get a list of active events. There could be thousands of active events at any point of time.
  • Store this info in a database. Hundreds of thousands of records in a month.
component_id integer
event_starttime integer // timestamp
event_endtime integer // timestamp
event_info text
  • Send this information to the UI.

There will be a single writer and multiple readers. I chose sqlite because it fits my needs: simple, fast, suitable for embedded system. Here are some questions that I have:

  1. Indices: UI will query things like list of active events (endtime is null), amount of events per component in a data range. I could make component_id an index. Would it make sense to make a text column an index if it’s used in searching?
  2. UI can query historical stats and close the connection. It can also get continuous feed of active events in a single http connection. These db read operations could block the main thread from talking to other components. Would it make sense to:

    a. Use a separate thread and boost io_context to listen for incoming db requests from UI? io_context will handle the listening/accepting connections asynchronously. The new thread will handle db reads synchronously. Even if it takes time, it won’t block the main thread. Cons: Short queries might be delayed by longer ones since they run in the same thread, share db connection.

    b. Spawn a new thread per connection? Also, share/create a new db connection?

  3. In async_receive(), need to store the event info into database. I can’t find any sqlite3 async methods to write this info in the database. The events are stored in a vector and it will insert/update each item in a foreach loop.
    This will block the main thread which I want to avoid. Any suggestions?

  4. I have seen many recommending WAL mode. Will it be suitable for my
    use case?

sqlite – How to query/join tables where foreign key constraint is set in schema

I’m relatively new to sqlite and databases in general.
I’ve successfully setup a schema of 4 tables, each having a foreign key pointing to its parent table. The schema also has the foreign key constraint set on the child keys.

Performing a query where every table is joined with the SELECT * FROM…JOIN…ON ……… works without issue, but it is laborious manually connecting the keys.

My question is: Is it possible to perform a query/join of the tables without the ON statement which links each parent/child key relationship? Since this was already established in the schema definition, this seems to be redundant. I’m trying to simplify the sql command and make use of the parent/child relationship already defined. When I perform said query but omit the ON statements, the results are not what I expected. Any guidance is appreciated.

c# – SQLite One or more errors occurred. (near “?”: syntax error)

Hola amigo estoy trantado de hacer un query asi, pero me sale este error.

One or more errors occurred. (near “?”: syntax error)

este es mi query

      public bool GetPokemonLikeExist(string p)
    {

        var pokes = DBSQLite.Database.Table<PokemonModel>()
                     .Where(c => c.Name.Contains(p, StringComparison.CurrentCultureIgnoreCase))
                     .ToListAsync();

        if (pokes.Result.Count() > 0) { return true; }
        else { return false; }

    }

sqlite – Offloading database joins to IOT devices

Solution as it is right now

I have this solution where I gather information from a proprietary product of a different company in various sites. The solution is based on a single go binary that contains everything needed to run the application (even an embedded sqlite) and is deployed to Windows computers running the proprietary software. All those Windows computers are connected to the Internet and sit behind a firewall that allows all outgoing traffic but incoming traffic is blocked and the owners of the computers don’t have the knowledge to configure their firewalls (to be honest for most of them it is already a demanding task to install the go program)

Users can access the data that is stored in a sqlite database using their mobile device or a different computer (1). The components needed for this are installed on a server that provides its services over the Internet. I created REST webservices (2) that send graphql queries to the computers on the respective site via RPC over NATS (3). The go program installed on the site computers (4) runs those queries against the local sqlite (5) and sends the result back to the NATS queue (6) (7). The result is taken from the NATS queue and returned to the caller by the same REST service that processed the incoming call (8)

enter image description here

Improvement I’m looking for

This setup works fine when I query single sites. But I should also be able to query several sites in parallel and retrieve a single “recordset”.

Here’s a made up example:

Lets assume there is a Persons table available on each site. I can query that table by running SELECT SiteNumber, PersonName FROM Persons

I need to run that query on for example 3 sites and merge/join them into one result that would look like this:

2, Daisy
2, Eve
2, Adam
5, Bob
7, Alice

The SQLs I need to run are much more complicated than this, I would need to do GROUP BY and `ORDER BY´ for example. This excludes approaches where I would for example create three maps and join them into one.

So far I intentionally don’t store or accumulate data on the server. Which are my options to postprocess the data ? I would rather not INSERT all subresults into a temporary table on the server. I found no distributed database that can be embedded into go and works across firewall borders.

Getting proper output of my SELECT clause. SQLite

I am coding a bus connection search engine. My goal is to see the time when the bus is departing, while the stop_from and stop_to names are provided.

I have created the STOP table

CREATE TABLE STOP (
ID INTEGER PRIMARY KEY,
STOP_NAME TEXT NOT NULL,
ROUTE_ID INTEGER NOT NULL
);

and the TIMETABLE table

CREATE TABLE TIMETABLE (
ID INTEGER PRIMARY KEY, 
TIME TEXT NOT NULL,
STOP_ID INTEGER NOT NULL,
FOREIGN KEY (STOP_ID) REFERENCES STOP (ID)
);

The route starts in Lidzbark Warmiński and leads via Smolajny and Dobre Miasto to Olsztyn. There are four connections per day. And I have also inserted all the possible routes from Lidzbark Warmiński to Olsztyn

INSERT INTO STOP VALUES
(1,'Lidzbark Warmiński', 1),
(2, 'Smolajny', 1),
(3, 'Dobre Miasto', 1),
(4, 'Olsztyn', 1),
(5, 'Lidzbark Warmiński', 2),
(6, 'Smolajny', 2),
(7, 'Dobre Miasto', 2),
(8, 'Lidzbark Warmiński', 3),
(9, 'Smolajny', 3),
(10, 'Smolajny', 4),
(11, 'Dobre Miasto', 4),
(12, 'Smolajny', 5),
(13, 'Olsztyn', 5),
(14, 'Dobre Miasto', 6),
(15, 'Olsztyn', 6),
(16, 'Smolajny', 7),
(17, 'Dobre Miasto', 7),
(18, 'Olsztyn', 7);

and the timetables regarding them

INSERT INTO TIMETABLE VALUES
--towards Olsztyn
(1, '08:00', 1),
(2, '12:00', 1),
(3, '15:00', 1),
(4, '19:00', 1),
(5, '08:00', 2),
(6, '12:00', 2),
(7, '15:00', 2),
(8, '19:00', 2),
(9, '08:00', 3),
(10, '12:00', 3),
(11, '15:00', 3),
(12, '19:00', 3),
(13, '08:20', 4),
(14, '12:20', 4),
(15, '15:20', 4),
(16, '19:20', 4),
(17, '08:20', 5),
(18, '12:20', 5),
(19, '15:20', 5),
(20, '19:20', 5),
(21, '08:30', 6),
(22, '12:30', 6),
(23, '15:30', 6),
(24, '19:30', 6),
(25, '08:20', 7),
(26, '12:20', 7),
(27, '15:20', 7),
(28, '19:20', 7);

I am developing under python 3.8 and this is a function with my SELECT clause

def fetch_hours(cursor, stop_from_name, stop_to_name):
    cursor.execute(
        '''SELECT T.TIME FROM TIMETABLE T
             JOIN STOP S1 ON T.STOP_ID = S1.ID 
             JOIN STOP S2 ON S2.ROUTE_ID = S1.ROUTE_ID 
             WHERE S1.STOP_NAME = ? 
             AND S2.STOP_NAME = ? ORDER BY T.TIME ASC''', 
        (stop_from_name, stop_to_name)) 
    return cursor.fetchall()

However, I have encountered some problems. For a wide range of bus stops this code doesn’t work well. Providing stop_from Smolajny and stop_to Dobre Miasto, the result is

Departures: 08:00, 08:30, 12:00, 12:30, 15:00, 15:30, 19:00, 19:30

While I would like to see only four hours. For some connections it doesn’t work at all and for some it is OK.

The desired effect is to see four departure hours for any stop_from and stop_to on this route (from Lidzbark Warmiński to Olsztyn). The reverse route is now commented, and if it would be helpful, the rest of my code can be achieved here: https://github.com/wwmarkositrawe/autobusy-backend

While looking for help,
Wishing you best regards,
marasecki

java – Erro ao criar várias tabelas no SQLite

Estou desenvolvendo uma aplicação no Android Studio, e estou tendo problemas ao criar múltiplas tabelas no banco de dados.
Quando eu executo o código de criação, aparentemente ele cria a tabela, mas quando eu tento inserir um dado pela tela ou então fazer um select, ele mostra a mensagem abaixo

E/AndroidRuntime: FATAL EXCEPTION: main
    Process: br.com.bellaapp, PID: 6003
    java.lang.RuntimeException: Unable to resume activity {br.com.bellaapp/br.com.bellaapp.lista.ListaClienteActivity}: android.database.sqlite.SQLiteException: no such table: cliente (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM cliente;
        at android.app.ActivityThread.performResumeActivity(ActivityThread.java:4205)
        at android.app.ActivityThread.handleResumeActivity(ActivityThread.java:4237)
        at android.app.servertransaction.ResumeActivityItem.execute(ResumeActivityItem.java:52)
        at android.app.servertransaction.TransactionExecutor.executeLifecycleState(TransactionExecutor.java:176)
        at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:97)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2016)
        at android.os.Handler.dispatchMessage(Handler.java:107)
        at android.os.Looper.loop(Looper.java:214)
        at android.app.ActivityThread.main(ActivityThread.java:7356)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:492)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:930)
     Caused by: android.database.sqlite.SQLiteException: no such table: cliente (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM cliente;
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:986)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:593)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:590)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:61)
        at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
        at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46)
        at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1443)
        at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1382)
        at br.com.bellaapp.persistence.ClienteDAO.listarClientes(ClienteDAO.java:62)
        at br.com.bellaapp.lista.ListaClienteActivity.carregarListaCli(ListaClienteActivity.java:62)
        at br.com.bellaapp.lista.ListaClienteActivity.onResume(ListaClienteActivity.java:72)
        at android.app.Instrumentation.callActivityOnResume(Instrumentation.java:1446)
        at android.app.Activity.performResume(Activity.java:7939) 

Já fiz várias tentativas. Usei o comando commit no final da execução de cada tabela, já coloquei na execução da última tabela, mas nada adiantou.

Na minha classe, possuo métodos de select, insert, delete, update, etc. Quando eu comento tudo, ele cria todas as tabelas e eu consigo inserir os dados, porém quando eu tiro os comentários, para ele executar o select por exemplo, aparece o erro abaixo.

Criei uma classe para cada tabela.

Seguem algumas das minhas classes:

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.util.ArrayList;
import java.util.List;
import br.com.bellaapp.model.Venda;

public class VendaDAO extends SQLiteOpenHelper {

    public VendaDAO(Context context) {
        super(context, "AppBella", null, 8);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String vendaSql = "CREATE TABLE venda" +
                "(id INTEGER PRIMARY KEY, idCliente INTEGER, valorBruto REAL,  valorDesconto REAL," +
                " valorLiquido REAL," +
                "FOREIGN KEY (idCliente) REFERENCES Cliente (id), FOREIGN KEY (idSerico) REFERENCES Servico (id) )";
        db.execSQL(vendaSql);
        db.execSQL("commit");

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String sql = "DROP TABLE IF EXISTS venda";
        db.execSQL(sql);
        onCreate(db);
    }

Classe completa com os métodos:

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.util.ArrayList;
import java.util.List;
import br.com.bellaapp.model.Servico;

public class ServicoDAO extends SQLiteOpenHelper {

    public ServicoDAO(Context context) {
        super(context, "AppBella", null, 8);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
       String servSql = "CREATE TABLE servico" +
                "(id INTEGER PRIMARY KEY, categoria TEXT, nome TEXT NOT NULL, preco REAL NOT NULL);";
        db.execSQL(servSql);
        //db.execSQL("commit");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String sql = "DROP TABLE IF EXISTS servico";
        db.execSQL(sql);
        onCreate(db);
    }

    public void add(Servico servico) {
        SQLiteDatabase db = getWritableDatabase();

        ContentValues dadosServ = pegaDadosServico(servico);

        db.insert("servico", null, dadosServ);
    }

    private ContentValues pegaDadosServico(Servico servico) {
        ContentValues dadosServ = new ContentValues();
        dadosServ.put("nome", servico.getNome());
        dadosServ.put("categoria", servico.getCategoria());
        dadosServ.put("preco", servico.getPreco());
        return dadosServ;
    }

    public List<Servico> listarServicos() {
        String sql = "SELECT * FROM servico;" ;
        SQLiteDatabase db = getReadableDatabase();
        Cursor c = db.rawQuery(sql, null);

        List<Servico> servicos = new ArrayList<Servico>();
        while (c.moveToNext()){
            Servico servico = new Servico();
            servico.setId(c.getLong(c.getColumnIndex("id")));
            servico.setNome(c.getString(c.getColumnIndex("nome")));
            servico.setPreco(c.getDouble(c.getColumnIndex("preco")));

            servicos.add(servico);
        }
        c.close();
        return servicos;
    }

    public void apaga(Servico servico) {
        SQLiteDatabase db = getWritableDatabase();

        String() parametros = {servico.getId().toString()};
        db.delete("servico", "id = ?", parametros);
    }

    public void edita(Servico servico) {
        SQLiteDatabase db = getWritableDatabase();

        ContentValues dadosServ = pegaDadosServico(servico);

        String() parametros = {servico.getId().toString()};
        db.update("servico", dadosServ,"id = ?", parametros);
    }
}

sqlite – In the iPhone message list, where does the date come from?

It may be a strange question. In the "Messages" application of the iPhone, the first view is a list of all cats, sorted by the most recent messages of each cat. To the right of each chat entry is a date (circled in my screenshot) which is the essence of my question. This date seems to be the date of the latest message received in the chat, even if this message was later deleted from the chat.

In the end, I was playing in the sqlite database to see how the messages were represented and I can't understand that. Chat list appears to be sorted by most recent current in every discussion, but this displayed timestamp may be the timestamp of a deleted message, which seems strange.

So why does the Messages app display a time stamp for a message that no longer exists, and where is that time stamp in the sqlite message database?

enter description of image here

Index Order Help SQLite – Database administrators stack Exchange

I have a very large sqlite table (70 million rows) on a low end machine that I am trying to index properly.

My columns are timestamp, machineid, ioid and value.

There are probably around 200 ioids (on each machine), a handful of machineids, and the timestamp is one sample per second for each ioid.

My requests are as such:

select * from AnalogInput where SampleInfo_source_timestamp>=0 and SampleInfo_source_timestamp<=1587481620000000000 and ioId in (1,9,10,11,5,6,7,8,12,13,14,15,179,203,2,3,27,28,29,31,32,33,34,35,36,37,30,38,42,48,41,49,53,59,52,60,64,70,63,79,89,82,134,144,137,71,74,75,78,16,479,490,20,26,19,90,94,100,93,101,105,111,104,133,112,115,116,122,181,182,233,177,171,504,505,173,172,174,175,211,212,217,213,214,215,216,176,234,235,236,237,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,219,220,221,222,223,237,238,239,240,241,225,226,227,230,231,232,228,229,506,507) and machineid=1111 group by ioId;

I am currently using an index of: CREATE INDEX "index1 $ AnalogInput" ON "AnalogInput" ("machineId", "ioId", "SampleInfo_source_timestamp")

Is it the most efficient command?